ASH Report For ORCL/orcl

DB Name DB Id Instance Inst num Release RAC Host

ORCL 1166960197 orcl 1 11.1.0.6.0 NO 11gR1NF

DB Name	DB Id	Instance	Inst num	Release	RAC	Host
ORCL	1166960197	orcl	1	11.1.0.6.0	NO	11gR1NF

CPUs SGA Size Buffer Cache Shared Pool ASH Buffer Size

1 407M (100%) 132M (32.4%) 240M (59.0%) 2.0M (0.5%)

CPUs	SGA Size	Buffer Cache	Shared Pool	ASH Buffer Size
1	407M (100%)	132M (32.4%)	240M (59.0%)	2.0M (0.5%)

Sample Time Data Source

Analysis Begin Time: 26-May-08 16:44:48 V$ACTIVE_SESSION_HISTORY

Analysis End Time: 26-May-08 16:45:57 V$ACTIVE_SESSION_HISTORY

Elapsed Time: 1.2 (mins)

Sample Count: 216

Average Active Sessions: 3.13

Avg. Active Session per CPU: 3.13

Report Target: None specified

	Sample Time	Data Source
Analysis Begin Time:	26-May-08 16:44:48	V$ACTIVE_SESSION_HISTORY
Analysis End Time:	26-May-08 16:45:57	V$ACTIVE_SESSION_HISTORY
Elapsed Time:	1.2 (mins)
Sample Count:	216
Average Active Sessions:	3.13
Avg. Active Session per CPU:	3.13
Report Target:	None specified

Top Events

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

Top User Events

Event	Event Class	% Event	Avg Active Sessions
CPU + Wait for CPU	CPU	46.30	1.45
db file sequential read	User I/O	11.57	0.36
buffer deadlock	Other	11.11	0.35
cursor: pin S wait on X	Concurrency	5.09	0.16
log file sync	Commit	5.09	0.16

Top Background Events

Event	Event Class	% Activity	Avg Active Sessions
log file parallel write	System I/O	3.24	0.10
CPU + Wait for CPU	CPU	1.85	0.06
control file parallel write	System I/O	1.85	0.06

Top Event P1/P2/P3 Values

Event	% Event	P1 Value, P2 Value, P3 Value	% Activity	Parameter 1	Parameter 2	Parameter 3
db file sequential read	11.57	"1","14419","1"	1.39	file#	block#	blocks
buffer deadlock	11.11	"20972126","12","8192"	2.78	dba	class*10+mode	flag
		"20972864","12","8192"	1.85
		"20972162","12","8192"	1.39
cursor: pin S wait on X	5.09	"175268898","7274496","327696"	0.46	idn	value	where\|sleeps
log file sync	5.09	"4964","0","0"	0.93	buffer#	NOT DEFINED	NOT DEFINED
latch: shared pool	3.70	"537530160","277","0"	3.70	address	number	tries

Load Profile

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

Top Service/Module

Service	Module	% Activity	Action	% Action
BATCH_STD	LoadGenerator	40.28	BuildInvoiceDetail	36.11
			BuildInvoices	4.17
SYS$USERS	Admin Connection	17.13	UNNAMED	17.13
BATCH_LOW	LoadGenerator	15.28	BuildInvoiceDetail	13.89
			BuildInvoices	1.39
SYS$BACKGROUND	UNNAMED	7.41	UNNAMED	7.41
DSS	wrc@11gR1NF (TNS V1-V3)	4.63	UNNAMED	4.63

Back to Load Profile
Back to Top

Top Client IDs

Client ID	% Activity	Avg Active Sessions	User	Program	Service
SYS@127.0.0.1@Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.8.0.	17.13	0.54	SYS	OMS	SYS$USERS

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	17	30.56	0.96
PL/SQL EXECUTE	10	27.31	0.86
INSERT	1	22.69	0.71

Back to Load Profile
Back to Top

Top Phases of Execution

Phase of Execution	% Activity	Avg Active Sessions
SQL Execution	81.94	2.57
Parse	24.07	0.75
Hard Parse	17.59	0.55
PLSQL Execution	16.67	0.52
Connection Management	5.09	0.16

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
1dmv3vasxtjtu	2076861007	34	21.76	buffer deadlock	10.19	Row Source Not Available	10.19	INSERT INTO AP.INVOICE_ITEMS (...
				db file sequential read	4.17	Row Source Not Available	4.17
				enq: TX - index contention	3.24	Row Source Not Available	3.24
0wvttswphs2ky		3	9.72	CPU + Wait for CPU	9.26	Row Source Not Available	9.26	BEGIN AP.PKG_LOAD_GENERATO...
4ymp71aaj3bud		3	6.48	CPU + Wait for CPU	5.56	Row Source Not Available	5.56	BEGIN AP.PKG_LOAD_GENERATO...
9cgwmxs9wdnx4	2076861007	9	6.02	CPU + Wait for CPU	5.09	SELECT STATEMENT	2.78	SELECT PRODUCT_ID FROM (SELECT...
abchp6whju3fy	330949284	0	6.02	cursor: pin S wait on X	4.63	Row Source Not Available	4.63	SELECT CUSTOMER_ID FROM (SELEC...

Top SQL with Top Row Sources

SQL ID	PlanHash	Sampled # of Executions	% Activity	Row Source	% RwSrc	Top Event	% Event	SQL Text
1dmv3vasxtjtu	2076861007	34	21.76	Row Source Not Available	21.76	buffer deadlock	10.19	INSERT INTO AP.INVOICE_ITEMS (...
0wvttswphs2ky		3	9.72	Row Source Not Available	9.72	CPU + Wait for CPU	9.26	BEGIN AP.PKG_LOAD_GENERATO...
4ymp71aaj3bud		3	6.48	Row Source Not Available	6.48	CPU + Wait for CPU	5.56	BEGIN AP.PKG_LOAD_GENERATO...
9cgwmxs9wdnx4	2076861007	9	6.02	SELECT STATEMENT	3.70	CPU + Wait for CPU	2.78	SELECT PRODUCT_ID FROM (SELECT...
				SORT - ORDER BY STOPKEY	1.85	CPU + Wait for CPU	1.85
abchp6whju3fy	330949284	0	6.02	Row Source Not Available	6.02	cursor: pin S wait on X	4.63	SELECT CUSTOMER_ID FROM (SELEC...

Top SQL using literals

No data exists for this section of the report.

Top Parsing Module/Action

Module	Action	% Activity	Event	% Event
Admin Connection		13.89	CPU + Wait for CPU	11.57
			db file sequential read	2.31
SQL*Plus		4.17	cursor: pin S wait on X	1.85
			db file sequential read	1.39
LoadGenerator	BuildInvoices	3.24	cursor: pin S wait on X	2.78
wrc@11gR1NF (TNS V1-V3)		1.85	CPU + Wait for CPU	0.46

Complete List of SQL Text

SQL Id SQL Text

0wvttswphs2ky BEGIN AP.PKG_LOAD_GENERATOR.RandomDML(250); COMMIT; END;

1dmv3vasxtjtu INSERT INTO AP.INVOICE_ITEMS ( INVOICE_ID , LINE_ITEM_NBR , ACTIVE_IND , PRODUCT_ID , QTY , EXTENDED_AMT , TAXABLE_IND ) VALUES( :B6 , :B5 , 'Y' , :B4 , :B3 , :B2 , :B1 )

4ymp71aaj3bud BEGIN AP.PKG_LOAD_GENERATOR.RandomDML(500); COMMIT; END;

9cgwmxs9wdnx4 SELECT PRODUCT_ID FROM (SELECT PRODUCT_ID FROM OE.PRODUCT_INFORMATION SAMPLE(3) WHERE LIST_PRICE IS NOT NULL ORDER BY DBMS_RANDOM.VALUE) WHERE ROWNUM < 2

abchp6whju3fy SELECT CUSTOMER_ID FROM (SELECT CUSTOMER_ID FROM OE.CUSTOMERS SAMPLE(3) ORDER BY DBMS_RANDOM.VALUE) WHERE ROWNUM < 2

SQL Id	SQL Text
0wvttswphs2ky	BEGIN AP.PKG_LOAD_GENERATOR.RandomDML(250); COMMIT; END;
1dmv3vasxtjtu	INSERT INTO AP.INVOICE_ITEMS ( INVOICE_ID , LINE_ITEM_NBR , ACTIVE_IND , PRODUCT_ID , QTY , EXTENDED_AMT , TAXABLE_IND ) VALUES( :B6 , :B5 , 'Y' , :B4 , :B3 , :B2 , :B1 )
4ymp71aaj3bud	BEGIN AP.PKG_LOAD_GENERATOR.RandomDML(500); COMMIT; END;
9cgwmxs9wdnx4	SELECT PRODUCT_ID FROM (SELECT PRODUCT_ID FROM OE.PRODUCT_INFORMATION SAMPLE(3) WHERE LIST_PRICE IS NOT NULL ORDER BY DBMS_RANDOM.VALUE) WHERE ROWNUM < 2
abchp6whju3fy	SELECT CUSTOMER_ID FROM (SELECT CUSTOMER_ID FROM OE.CUSTOMERS SAMPLE(3) ORDER BY DBMS_RANDOM.VALUE) WHERE ROWNUM < 2

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
AP.PKG_LOAD_GENERATOR.RANDOMDML	54.63	SQL	38.43
		SYS.DBMS_RANDOM.SEED#2	11.57
		AP.PKG_LOAD_GENERATOR.RANDOMDML	4.63
SYS.DBMS_WORKLOAD_REPLAY.REPORT	17.13	SQL	16.20
AP.PKG_LOAD_GENERATOR.RANDOMQUERY	3.24	AP.PKG_LOAD_GENERATOR.RANDOMQUERY	1.85
		SQL	1.39
SYS.DBMS_WORKLOAD_REPLAY.START_REPLAY	1.39	UNKNOWN_PLSQL_ID <7749,28>	0.93
SYS.DBMS_WORKLOAD_REPLAY.CLIENT_CONNECT	1.39	UNKNOWN_PLSQL_ID <7749,28>	1.39

Top Java Workload

No data exists for this section of the report.

Top Sessions

Top Sessions
Top Blocking Sessions
Top Sessions running PQs

Top Sessions

'# Samples Active' shows the number of ASH samples in which the session was found waiting for that particular event. The percentage shown in this column is calculated with respect to wall clock time and not total database activity.
'XIDs' shows the number of distinct transaction IDs sampled in ASH when the session was waiting for that particular event
For sessions running Parallel Queries, this section will NOT aggregate the PQ slave activity into the session issuing the PQ. Refer to the 'Top Sessions running PQs' section for such statistics.

Sid, Serial#	% Activity	Event	% Event	User	Program	# Samples Active	XIDs
132, 134	17.13	CPU + Wait for CPU	12.50	SYS	OMS	27/69 [ 39%]	0
		db file sequential read	4.17			9/69 [ 13%]	0
106, 15	16.20	CPU + Wait for CPU	8.33	OE	wrc@11gR1NF (TNS V1-V3)	18/69 [ 26%]	1
		buffer deadlock	2.31			5/69 [ 7%]	1
		db file sequential read	1.85			4/69 [ 6%]	1
123, 115	16.20	CPU + Wait for CPU	9.26	OE	wrc@11gR1NF (TNS V1-V3)	20/69 [ 29%]	1
		buffer deadlock	1.39			3/69 [ 4%]	1
99, 20	13.43	buffer deadlock	5.56	SH	wrc@11gR1NF (TNS V1-V3)	12/69 [ 17%]	1
		CPU + Wait for CPU	5.09			11/69 [ 16%]	1
104, 18	6.94	CPU + Wait for CPU	4.63	OE	wrc@11gR1NF (TNS V1-V3)	10/69 [ 14%]	1
		buffer deadlock	1.85			4/69 [ 6%]	1

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	% Activity	Event Caused	% Event	User	Program	# Samples Active	XIDs
149, 20	4.63	cursor: pin S wait on X	4.63	SH	wrc@11gR1NF (TNS V1-V3)	5/69 [ 7%]	0
162, 1	2.31	log file sync	2.31	SYS	oracle@11gR1NF (LGWR)	7/69 [ 10%]	0
104, 18	1.85	enq: TX - index contention	1.85	OE	wrc@11gR1NF (TNS V1-V3)	15/69 [ 22%]	1
123, 115	1.85	buffer busy waits	0.93	OE	wrc@11gR1NF (TNS V1-V3)	35/69 [ 51%]	1
99, 20	1.39	library cache load lock	0.93	SH	wrc@11gR1NF (TNS V1-V3)	30/69 [ 43%]	1

Top Sessions running PQs

No data exists for this section of the report.

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
526	3.70	db file sequential read	3.70	SYS.SYS_C00643 (INDEX)	SYSTEM
73377	1.39	buffer busy waits	0.93	AP.INVOICE_ITEMS_PK_IDX (INDEX)	EXAMPLE

Top DB Files

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

File ID	% Activity	Event	% Event	File Name	Tablespace
1	3.70	db file sequential read	3.70	/u01/app/oracle/oradata/orcl/system01.dbf	SYSTEM
3	3.24	db file sequential read	3.24	/u01/app/oracle/oradata/orcl/undotbs01.dbf	UNDOTBS1
5	1.85	db file sequential read	1.85	/u01/app/oracle/oradata/orcl/example01.dbf	EXAMPLE

Top Latches

Latch	% Latch	Blocking Sid	% Activity	Max Sampled Wait secs	# Waits Sampled	# Sampled Wts < 10ms	# Sampled Wts 10ms - 100ms	# Sampled Wts 100ms - 1s	# Sampled Wts > 1s
latch: shared pool	3.70	Held Shared	2.31	0.47	5	0	0	5	0
		132, 134	1.39	0.18	3	0	2	1	0

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
16:44:48 (12 secs)	56	CPU + Wait for CPU	19	8.80
		cursor: pin S wait on X	11	5.09
		db file sequential read	6	2.78
16:45:00 (57 secs)	160	CPU + Wait for CPU	85	39.35
		buffer deadlock	24	11.11
		db file sequential read	19	8.80

End of Report