Enter value for begin_time: 08/03 15:00:00 Report begin time specified: 08/03 15:00:00 Enter duration in minutes starting from begin time: Defaults to SYSDATE - begin_time Press Enter to analyze till current time Enter value for duration: 60 Report duration specified: 60 Using 03-Aug-12 15:00:00 as report begin time Using 03-Aug-12 16:00:00 as report end time Specify Slot Width (using ashrpti.sql) for 'Activity Over Time' section ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ -- Explanation: -- In the 'Activity Over Time' section of the ASH report, -- the analysis period is divided into smaller slots -- and top wait events are reported in each of those slots. -- Default: -- The analysis period will be automatically split upto 10 slots -- complying to a minimum slot width of -- 1 minute, if the source is V$ACTIVE_SESSION_HISTORY or -- 5 minutes, if the source is DBA_HIST_ACTIVE_SESS_HISTORY. Specify Slot Width in seconds to use in the 'Activity Over Time' section: Defaults to a value as explained above: Slot Width specified: Specify Report Targets (using ashrpti.sql) to generate the ASH report ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ -- Explanation: -- ASH Report can accept "Report Targets", -- like a particular SQL statement, or a particular SESSION, -- to generate the report on. If one or more report targets are -- specified, then the data used to generate the report will only be -- the ASH samples that pertain to ALL the specified report targets. -- Default: -- If none of the report targets are specified, -- then the target defaults to all activity in the database instance. Specify SESSION_ID (eg: from V$SESSION.SID) report target: Defaults to NULL: SESSION report target specified: Specify SQL_ID (eg: from V$SQL.SQL_ID) report target: Defaults to NULL: (% and _ wildcards allowed) SQL report target specified: Specify WAIT_CLASS name (eg: from V$EVENT_NAME.WAIT_CLASS) report target: [Enter 'CPU' to investigate CPU usage] Defaults to NULL: (% and _ wildcards allowed) WAIT_CLASS report target specified: Specify SERVICE_HASH (eg: from V$ACTIVE_SERVICES.NAME_HASH) report target: Defaults to NULL: SERVICE report target specified: Specify MODULE name (eg: from V$SESSION.MODULE) report target: Defaults to NULL: (% and _ wildcards allowed) MODULE report target specified: Specify ACTION name (eg: from V$SESSION.ACTION) report target: Defaults to NULL: (% and _ wildcards allowed) ACTION report target specified: Specify CLIENT_ID (eg: from V$SESSION.CLIENT_IDENTIFIER) report target: Defaults to NULL: (% and _ wildcards allowed) CLIENT_ID report target specified: Specify PLSQL_ENTRY name (eg: "SYS.DBMS_LOB.*") report target: Defaults to NULL: (% and _ wildcards allowed) PLSQL_ENTRY report target specified: Specify the Report Name ~~~~~~~~~~~~~~~~~~~~~~~ The default report file name is ashrpt_1_0803_1600.html. To use this name, press to continue, otherwise enter an alternative. Enter value for report_name: Using the report name ashrpt_1_0803_1600.html Summary of All User Input ------------------------- Format : HTML DB Id : 2127610945 Inst num : 1 Begin time : 03-Aug-12 15:00:00 End time : 03-Aug-12 16:00:00 Slot width : Default Report targets : 0 Report name : ashrpt_1_0803_1600.html ASH Report - From 03-Aug-12 15:00:00 To 03-Aug-12 16:00:00

ASH Report For PRODB/PRODB1

DB Name DB Id Instance Inst num Release RAC Host

PRODB 2127610945 PRODB1 1 11.2.0.1.0 YES HPPRDDB1

DB Name	DB Id	Instance	Inst num	Release	RAC	Host
PRODB	2127610945	PRODB1	1	11.2.0.1.0	YES	HPPRDDB1

CPUs SGA Size Buffer Cache Shared Pool ASH Buffer Size

128 417,456M (100%) 144,384M (34.6%) 47,918M (11.5%) M (%)

CPUs	SGA Size	Buffer Cache	Shared Pool	ASH Buffer Size
128	417,456M (100%)	144,384M (34.6%)	47,918M (11.5%)	M (%)

Sample Time Data Source

Analysis Begin Time: 03-Aug-12 15:00:00 DBA_HIST_ACTIVE_SESS_HISTORY
in AWR snapshot 18027

Analysis End Time: 03-Aug-12 16:00:00 DBA_HIST_ACTIVE_SESS_HISTORY
in AWR snapshot 18029

Elapsed Time: 60.0 (mins) Missing 114.0 mins (190%) of activity

Sample Count: 60,110

Average Active Sessions: 166.97

Avg. Active Session per CPU: 1.30

Report Target: None specified

	Sample Time	Data Source
Analysis Begin Time:	03-Aug-12 15:00:00	DBA_HIST_ACTIVE_SESS_HISTORY in AWR snapshot 18027
Analysis End Time:	03-Aug-12 16:00:00	DBA_HIST_ACTIVE_SESS_HISTORY in AWR snapshot 18029
Elapsed Time:	60.0 (mins)	Missing 114.0 mins (190%) of activity
Sample Count:	60,110
Average Active Sessions:	166.97
Avg. Active Session per CPU:	1.30
Report Target:	None specified

Top Events

Top User Events
Top Background Events
Top Cluster Events
Top Event P1/P2/P3 Values

Top User Events

Event	Event Class	% Event	Avg Active Sessions
latch: parallel query alloc buffer	Other	30.49	50.90
gc buffer busy acquire	Cluster	28.50	47.58
enq: SQ - contention	Configuration	22.69	37.89
db file sequential read	User I/O	8.74	14.60
CPU + Wait for CPU	CPU	5.34	8.92

Top Background Events

No data exists for this section of the report.

Top Cluster Events

Event	% Event	Remote Instance#	% Activity
gc buffer busy acquire	28.50		28.47
gc current request	1.25		1.25

Top Event P1/P2/P3 Values

Event	% Event	P1 Value, P2 Value, P3 Value	% Activity	Parameter 1	Parameter 2	Parameter 3
latch: parallel query alloc buffer	30.63	"13835058492516997072","378","0"	9.50	address	number	tries
		"13835058492516997336","378","0"	9.31
		"13835058492516996808","378","0"	9.30
gc buffer busy acquire	28.50	"34","3244260","1"	6.26	file#	block#	class#
		"193","48279","1"	4.95
		"34","3244270","1"	4.21
enq: SQ - contention	22.69	"1397817350","98025","0"	22.69	name\|mode	object #	0
db file sequential read	8.75	"1","83611","1"	0.00	file#	block#	blocks
gc current request	1.25	"23","3063286","33554433"	0.06	file#	block#	id#

Load Profile

Top Service/Module
Top Client IDs
Top SQL Command Types
Top Phases of Execution
Top Remote Instances

Top Service/Module

Service	Module	% Activity	Action	% Action
PRODB	JDBC Thin Client	61.78	UNNAMED	61.78
	PL/SQL Developer	31.33	SQL �� - �½�	30.85
SYS$USERS	JDBC Thin Client	5.46	UNNAMED	5.46

Back to Load Profile
Back to Top

Top Client IDs

No data exists for this section of the report.

Back to Load Profile
Back to Top

Top SQL Command Types

'Distinct SQLIDs' is the count of the distinct number of SQLIDs with the given SQL Command Type found over all the ASH samples in the analysis period

SQL Command Type	Distinct SQLIDs	% Activity	Avg Active Sessions
SELECT	2,931	56.75	94.76
UPDATE	62	26.20	43.75

Back to Load Profile
Back to Top

Top Phases of Execution

Phase of Execution	% Activity	Avg Active Sessions
SQL Execution	84.54	141.16
Sequence Load	22.75	37.99
Parse	1.06	1.78

Back to Load Profile
Back to Top

Top Remote Instances

Wait Class	% Wait Class	Remote Instance#	% Activity
Cluster	29.99		29.88

Back to Load Profile
Back to Top

Top SQL

Top SQL with Top Events
Top SQL with Top Row Sources
Top SQL using literals
Top Parsing Module/Action
Complete List of SQL Text

Top SQL with Top Events

SQL ID	Planhash	Sampled # of Executions	% Activity	Event	% Event	Top Row Source	% RwSrc	SQL Text
5ay6kfp4h2g12	2396930873	655	25.87	gc buffer busy acquire	25.07	UPDATE	25.07	UPDATE LDMAXNO SET MAXNO = MAX...
7xx90b6mb3mdp	2310482046	582	22.68	enq: SQ - contention	22.62	SEQUENCE	22.62	select SEQ_LOGNO.nextval from ...
a6cw2bq138t6c	2481148706	1	17.72	latch: parallel query alloc buffer	17.08	PX COORDINATOR	17.08	SQL Text Not Available

Top SQL with Top Row Sources

SQL ID	PlanHash	Sampled # of Executions	% Activity	Row Source	% RwSrc	Top Event	% Event	SQL Text
5ay6kfp4h2g12	2396930873	655	25.87	UPDATE	25.87	gc buffer busy acquire	25.07	UPDATE LDMAXNO SET MAXNO = MAX...
7xx90b6mb3mdp	2310482046	582	22.68	SEQUENCE	22.68	enq: SQ - contention	22.62	select SEQ_LOGNO.nextval from ...
a6cw2bq138t6c	2481148706	1	17.72	PX COORDINATOR	17.72	latch: parallel query alloc buffer	17.08	SQL Text Not Available

Top SQL using literals

FORCE_MATCHING_SIGNATURE is used to identify SQL statements that are identical except for their use of literals.
Please refer to the Oracle Database Reference to understand how the FORCE_MATCHING_SIGNATURE is derived.

FORCE_MATCHING_SIGNATURE	% Activity	# of Sampled SQL Versions	Example SQL 1	Example SQL TEXT 1	Example SQL 2	Example SQL TEXT 2
17548306286301760638	1.07	16	00rw07rqm1c2q	select n.prtno, m.contno , (se...	gzagvg5r0qhps	select n.prtno, m.contno , (se...
11761759578946131756	1.05	38

Top Parsing Module/Action

Module	Action	% Activity	Event	% Event
JDBC Thin Client		1.05	CPU + Wait for CPU	0.89

Complete List of SQL Text

SQL Id SQL Text

00rw07rqm1c2q select n.prtno, m.contno , (select appntname from lcappnt where contno= m.contno), m.amnt, m.PREM, (select count(distinct contno) from ljapayperson where ljapayperson.contno=n.contno), m.agentcode, (select name from laagent where agentcode = trim(m.agentcode)), (case when ((select codename from ldcode where codetype = 'agenttype' and code = n.selltype) is null or (select codename from ldcode where codetype = 'agenttype' and code = n.selltype) = '') then '��ֱ��' else (select co dename from ldcode where codetype = 'agenttype' and code = n.selltype) end), (select codename from ldcode where codetype = 'payintv' and code = m.payintv) from lcpol m, lccont n where m.riskcode='00358009' and m.contno=n.contno and n.cardflag='3' and n.managecom like '86270000%' and n.signdate between date'2012-06-05' and date'2012-06-06' union select n.prtno, m.contno, (select appntname from lcappnt where contno = m.contno), m.amnt, m.PREM, (select count(distinct contno) from ljapa yperson a where a.contno = n.contno and a.paycount = (select max(paycount) from ljapayperson where contno = a.contno and riskcode = a.riskcode)), m.agentcode, (select name from laagent where agentcode = trim(m.agentcode)), (select codename from ldcode where codetype = 'salechnl' and code = n.salechnl), (select codename from ldcode where codetype = 'payintv' and code = m.payintv) from lcpol m, lccont n where m.riskcode = '00358009' and m.contno = n.contno and n.cardflag = '3' and exists(selec t 1 from ljapay where othernotype = '24' and otherno = n.contno ) and m.appflag = '1' and n.managecom like '86270000%' and m.cvalidate between date '2012-06-05' and date '2012-06-06'

5ay6kfp4h2g12 UPDATE LDMAXNO SET MAXNO = MAXNO+1 WHERE NOTYPE = :B2 AND NOLIMIT = :B1 RETURNING MAXNO INTO :O0

7xx90b6mb3mdp select SEQ_LOGNO.nextval from dual

a6cw2bq138t6c ** SQL Text Not Available **

gzagvg5r0qhps select n.prtno, m.contno , (select appntname from lcappnt where contno= m.contno), m.amnt, m.PREM, (select count(distinct contno) from ljapayperson where ljapayperson.contno=n.contno), m.agentcode, (select name from laagent where agentcode = trim(m.agentcode)), (case when ((select codename from ldcode where codetype = 'agenttype' and code = n.selltype) is null or (select codename from ldcode where codetype = 'agenttype' and code = n.selltype) = '') then '��ֱ��' else (select co dename from ldcode where codetype = 'agenttype' and code = n.selltype) end), (select codename from ldcode where codetype = 'payintv' and code = m.payintv) from lcpol m, lccont n where m.riskcode='00355009' and m.contno=n.contno and n.cardflag='3' and n.managecom like '862711%' and n.signdate between date'2012-08-01' and date'2012-08-31' union select n.prtno, m.contno, (select appntname from lcappnt where contno = m.contno), m.amnt, m.PREM, (select count(distinct contno) from ljapayp erson a where a.contno = n.contno and a.paycount = (select max(paycount) from ljapayperson where contno = a.contno and riskcode = a.riskcode)), m.agentcode, (select name from laagent where agentcode = trim(m.agentcode)), (select codename from ldcode where codetype = 'salechnl' and code = n.salechnl), (select codename from ldcode where codetype = 'payintv' and code = m.payintv) from lcpol m, lccont n where m.riskcode = '00355009' and m.contno = n.contno and n.cardflag = '3' and exists(select 1 fr om ljapay where othernotype = '24' and otherno = n.contno ) and m.appflag = '1' and n.managecom like '862711%' and m.cvalidate between date '2012-08-01' and date '2012-08-31'

SQL Id	SQL Text
00rw07rqm1c2q	select n.prtno, m.contno , (select appntname from lcappnt where contno= m.contno), m.amnt, m.PREM, (select count(distinct contno) from ljapayperson where ljapayperson.contno=n.contno), m.agentcode, (select name from laagent where agentcode = trim(m.agentcode)), (case when ((select codename from ldcode where codetype = 'agenttype' and code = n.selltype) is null or (select codename from ldcode where codetype = 'agenttype' and code = n.selltype) = '') then '��ֱ��' else (select co dename from ldcode where codetype = 'agenttype' and code = n.selltype) end), (select codename from ldcode where codetype = 'payintv' and code = m.payintv) from lcpol m, lccont n where m.riskcode='00358009' and m.contno=n.contno and n.cardflag='3' and n.managecom like '86270000%' and n.signdate between date'2012-06-05' and date'2012-06-06' union select n.prtno, m.contno, (select appntname from lcappnt where contno = m.contno), m.amnt, m.PREM, (select count(distinct contno) from ljapa yperson a where a.contno = n.contno and a.paycount = (select max(paycount) from ljapayperson where contno = a.contno and riskcode = a.riskcode)), m.agentcode, (select name from laagent where agentcode = trim(m.agentcode)), (select codename from ldcode where codetype = 'salechnl' and code = n.salechnl), (select codename from ldcode where codetype = 'payintv' and code = m.payintv) from lcpol m, lccont n where m.riskcode = '00358009' and m.contno = n.contno and n.cardflag = '3' and exists(selec t 1 from ljapay where othernotype = '24' and otherno = n.contno ) and m.appflag = '1' and n.managecom like '86270000%' and m.cvalidate between date '2012-06-05' and date '2012-06-06'
5ay6kfp4h2g12	UPDATE LDMAXNO SET MAXNO = MAXNO+1 WHERE NOTYPE = :B2 AND NOLIMIT = :B1 RETURNING MAXNO INTO :O0
7xx90b6mb3mdp	select SEQ_LOGNO.nextval from dual
a6cw2bq138t6c	SQL Text Not Available
gzagvg5r0qhps	select n.prtno, m.contno , (select appntname from lcappnt where contno= m.contno), m.amnt, m.PREM, (select count(distinct contno) from ljapayperson where ljapayperson.contno=n.contno), m.agentcode, (select name from laagent where agentcode = trim(m.agentcode)), (case when ((select codename from ldcode where codetype = 'agenttype' and code = n.selltype) is null or (select codename from ldcode where codetype = 'agenttype' and code = n.selltype) = '') then '��ֱ��' else (select co dename from ldcode where codetype = 'agenttype' and code = n.selltype) end), (select codename from ldcode where codetype = 'payintv' and code = m.payintv) from lcpol m, lccont n where m.riskcode='00355009' and m.contno=n.contno and n.cardflag='3' and n.managecom like '862711%' and n.signdate between date'2012-08-01' and date'2012-08-31' union select n.prtno, m.contno, (select appntname from lcappnt where contno = m.contno), m.amnt, m.PREM, (select count(distinct contno) from ljapayp erson a where a.contno = n.contno and a.paycount = (select max(paycount) from ljapayperson where contno = a.contno and riskcode = a.riskcode)), m.agentcode, (select name from laagent where agentcode = trim(m.agentcode)), (select codename from ldcode where codetype = 'salechnl' and code = n.salechnl), (select codename from ldcode where codetype = 'payintv' and code = m.payintv) from lcpol m, lccont n where m.riskcode = '00355009' and m.contno = n.contno and n.cardflag = '3' and exists(select 1 fr om ljapay where othernotype = '24' and otherno = n.contno ) and m.appflag = '1' and n.managecom like '862711%' and m.cvalidate between date '2012-08-01' and date '2012-08-31'

Top PL/SQL Procedures

'PL/SQL entry subprogram' represents the application's top-level entry-point(procedure, function, trigger, package initialization or RPC call) into PL/SQL.
'PL/SQL current subprogram' is the pl/sql subprogram being executed at the point of sampling . If the value is 'SQL', it represents the percentage of time spent executing SQL for the particular plsql entry subprogram

PLSQL Entry Subprogram	% Activity	PLSQL Current Subprogram	% Current
LIS.CREATEMAXNO	26.23	SQL	26.23

Top Java Workload

No data exists for this section of the report.

Top Call Types

Call Type	Count	% Activity	Avg Active Sessions
V8 Bundled Exec	37,176	61.85	103.27
FAST UPI	3,151	5.24	8.75

Top Sessions

Top Sessions
Top Blocking Sessions
Top Sessions running PQs

Top Sessions

No data exists for this section of the report.

Top Blocking Sessions

Blocking session activity percentages are calculated with respect to waits on enqueues, latches and "buffer busy" only
'% Activity' represents the load on the database caused by a particular blocking session
'# Samples Active' shows the number of ASH samples in which the blocking session was found active.
'XIDs' shows the number of distinct transaction IDs sampled in ASH when the blocking session was found active.

Blocking Sid (Inst)	% Activity	Event Caused	% Event	User	Program	# Samples Active
3064,20404( 1)	22.69	enq: SQ - contention	22.69	LIS	JDBC Thin Client	35/360 [ 10%]
3103, 2873( 1)	9.50	latch: parallel query alloc buffer	9.50	LIS	oracle@HPPRDDB1 (P338)	91/360 [ 25%]
4286,28679( 1)	9.31	latch: parallel query alloc buffer	9.31	LIS	oracle@HPPRDDB1 (P371)	91/360 [ 25%]
908,21960( 1)	9.30	latch: parallel query alloc buffer	9.30	LIS	oracle@HPPRDDB1 (P405)	91/360 [ 25%]
471,57463( 1)	6.26	gc buffer busy acquire	6.26	LIS	JDBC Thin Client	36/360 [ 10%]

Top Sessions running PQs

This section aggregates all the work done by the PQ slaves into the session issuing the parallel query.

Sid,Srl# (Inst)	% Activity	SQL ID	Event	% Event	User	Program	SQL Text
1627,62240(1)	30.80	a6cw2bq138t6c	latch: parallel query alloc buffer	17.08	UserID:		SQL Text Not Available
			latch: parallel query alloc buffer	13.09	UserID:		SQL Text Not Available

Top Objects/Files/Latches

Top DB Objects
Top DB Files
Top Latches

Top DB Objects

With respect to Application, Cluster, User I/O and buffer busy waits only.

Object ID	% Activity	Event	% Event	Object Name (Type)	Tablespace
97083	26.22	gc buffer busy acquire	25.41	LIS.LDMAXNO (TABLE)	LIS
92266	2.27	db file sequential read	1.96	LIS.LCPOL (TABLE)	LIS
92359	1.35	db file sequential read	1.31	LIS.LYRETURNFROMBANKB (TABLE)	LIS
95800	1.26	gc buffer busy acquire	1.20	LIS.LWMISSION (TABLE)	LIS
94494	1.22	gc buffer busy acquire	1.04	LIS.LPEDORITEM (TABLE)	LIS

Top DB Files

With respect to Cluster and User I/O events only.

File ID	% Activity	Event	% Event	File Name	Tablespace
34	17.82	gc buffer busy acquire	17.34	+DGDATA/lisdata027	LIS
193	5.07	gc buffer busy acquire	4.95	+DGDATA/lisdata084	LIS
35	2.25	gc buffer busy acquire	1.93	+DGDATA/lisdata028	LIS
224	1.47	gc buffer busy acquire	1.26	+DGDATA/lisdata097	LIS
166	1.25	gc buffer busy acquire	1.13	+DGDATA/lisdata071	LIS

Top Latches

Latch	% Latch	Blocking Sid(Inst)	% Activity
latch: parallel query alloc buffer	30.63	3103, 2873( 1)	9.50
		4286,28679( 1)	9.31
		908,21960( 1)	9.30

Activity Over Time

Analysis period is divided into smaller time slots
Top 3 events are reported in each of those slots
'Slot Count' shows the number of ASH samples in that slot
'Event Count' shows the number of ASH samples waiting for that event in that slot
'% Event' is 'Event Count' over all ASH samples in the analysis period

Slot Time (Duration)	Slot Count	Event	Event Count	% Event
15:00:00 (5.0 min)	1,036	db file sequential read	599	1.00
		CPU + Wait for CPU	401	0.67
		gc cr grant 2-way	16	0.03
15:05:00 (5.0 min)	963	db file sequential read	544	0.91
		CPU + Wait for CPU	371	0.62
		gc cr grant 2-way	11	0.02
15:10:00 (5.0 min)	1,006	db file sequential read	572	0.95
		CPU + Wait for CPU	372	0.62
		gc cr grant 2-way	20	0.03
15:15:00 (5.0 min)	1,135	db file sequential read	636	1.06
		CPU + Wait for CPU	427	0.71
		direct path read	43	0.07
15:20:00 (5.0 min)	1,297	db file sequential read	687	1.14
		CPU + Wait for CPU	499	0.83
		direct path read	36	0.06
15:25:00 (5.0 min)	3,301	latch: parallel query alloc buffer	1,958	3.26
		db file sequential read	699	1.16
		CPU + Wait for CPU	463	0.77
15:30:00 (5.0 min)	5,099	latch: parallel query alloc buffer	3,596	5.98
		db file sequential read	671	1.12
		CPU + Wait for CPU	535	0.89
15:35:00 (5.0 min)	21,273	latch: parallel query alloc buffer	7,356	12.24
		gc buffer busy acquire	7,003	11.65
		enq: SQ - contention	5,176	8.61
15:40:00 (5.0 min)	25,000	gc buffer busy acquire	10,089	16.78
		enq: SQ - contention	8,465	14.08
		latch: parallel query alloc buffer	5,504	9.16

End of Report Report written to ashrpt_1_0803_1600.html SQL> desc LDMAXNO ERROR: ORA-04043: object LDMAXNO does not exist SQL> desc lis. LDMAXNO Usage: DESCRIBE [schema.]object[@db_link] SQL> desc lis.ldmaxno SP2-0734: unknown command beginning "desc..." - rest of line ignored. SQL> desc lis.ldmaxno Name Null? Type ---------------------------------------- -------- --------------------------- NOTYPE NOT NULL CHAR(15) NOLIMIT NOT NULL CHAR(20) MAXNO NOT NULL NUMBER(38)