ASH Report For BHDB/BHDB4

(1 Report Target Specified)

DB Name DB Id Instance Inst num Release RAC Host

BHDB 391220044 BHDB4 4 10.2.0.3.0 YES acsdb803

DB Name	DB Id	Instance	Inst num	Release	RAC	Host
BHDB	391220044	BHDB4	4	10.2.0.3.0	YES	acsdb803

CPUs SGA Size Buffer Cache Shared Pool ASH Buffer Size

8 15,472M (100%) 12,000M (77.6%) 1,672M (10.8%) M (%)

CPUs	SGA Size	Buffer Cache	Shared Pool	ASH Buffer Size
8	15,472M (100%)	12,000M (77.6%)	1,672M (10.8%)	M (%)

Sample Time Data Source

Analysis Begin Time: 12-Feb-08 15:08:55 DBA_HIST_ACTIVE_SESS_HISTORY
in AWR snapshot 7419

Analysis End Time: 12-Feb-08 15:38:55 DBA_HIST_ACTIVE_SESS_HISTORY
in AWR snapshot 7419

Elapsed Time: 30.0 (mins)

Sample Count: 882

Average Active Sessions: 4.90

Avg. Active Session per CPU: 0.61

Report Target: SQL_ID like 'cx476us09t8z2' 40.5% of total database activity

	Sample Time	Data Source
Analysis Begin Time:	12-Feb-08 15:08:55	DBA_HIST_ACTIVE_SESS_HISTORY in AWR snapshot 7419
Analysis End Time:	12-Feb-08 15:38:55	DBA_HIST_ACTIVE_SESS_HISTORY in AWR snapshot 7419
Elapsed Time:	30.0 (mins)
Sample Count:	882
Average Active Sessions:	4.90
Avg. Active Session per CPU:	0.61
Report Target:	SQL_ID like 'cx476us09t8z2'	40.5% of total database activity

Top Events

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

Top User Events

Event	Event Class	% Activity	Avg Active Sessions
enq: TX - row lock contention	Application	99.43	4.87

Top Background Events

No data exists for this section of the report.

Top Event P1/P2/P3 Values

Event	% Event	P1 Value, P2 Value, P3 Value	% Activity	Parameter 1	Parameter 2	Parameter 3
enq: TX - row lock contention	99.43	"1415053316","17694744","226920"	34.69	name\|mode	usn<<16 \| slot	sequence
		"1415053316","1245215","1041198"	28.00
		"1415053316","23134213","37506"	16.33

Load Profile

Top Service/Module
Top Client IDs
Top SQL Command Types

Top Service/Module

Service	Module	% Activity	Action	% Action
BHDB.oracle.com	EmailServiceInstance	100.00	es_access:expunge_folder	100.00

Back to Load Profile
Back to Top

Top Client IDs

Client ID	% Activity	Avg Active Sessions	User	Service
141.146.40.81:92479:1202857406761:1216167,0	18.14	0.89	OCS_CODE	BHDB.oracle.com
141.146.40.81:7083:1202857696977:2461864,0	17.35	0.85	OCS_CODE	BHDB.oracle.com
141.146.40.80:19553:1202857555357:2636671,0	13.83	0.68	OCS_CODE	BHDB.oracle.com
141.146.40.81:25611:1202857992120:2583449,0	12.93	0.63	OCS_CODE	BHDB.oracle.com
141.146.40.70:65207:1202857916314:1542699,0	10.77	0.53	OCS_CODE	BHDB.oracle.com

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
INSERT	1	100.00	4.90

Back to Load Profile
Back to Top

Top SQL

Top SQL Statements
Top SQL using literals
Complete List of SQL Text

Top SQL Statements

SQL ID	Planhash	% Activity	Event	% Event	SQL Text
cx476us09t8z2	2548684123	100.00	enq: TX - row lock contention	99.43	INSERT INTO ES_REMOVAL_HISTORY...

Top SQL using literals

No data exists for this section of the report.

Complete List of SQL Text

SQL Id SQL Text

cx476us09t8z2 INSERT INTO ES_REMOVAL_HISTORY (EID , PARENT_EID , CACHE_TS , CACHE_SQ, MESSAGE_UID) SELECT N.EID , N.PARENT_EID , N.CACHE_TS , N.CACHE_SQ, N.MESSAGE_UID FROM ES_INSTANCES N WHERE N.EID = :B1

SQL Id	SQL Text
cx476us09t8z2	INSERT INTO ES_REMOVAL_HISTORY (EID , PARENT_EID , CACHE_TS , CACHE_SQ, MESSAGE_UID) SELECT N.EID , N.PARENT_EID , N.CACHE_TS , N.CACHE_SQ, N.MESSAGE_UID FROM ES_INSTANCES N WHERE N.EID = :B1

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
OCS_CODE.ES_ACCESS.EXPUNGE_FOLDER	100.00	SQL	64.51
		OCS_CODE.ES_UTIL.RECORD_REMOVE	35.49

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	# Samples Active	XIDs
1733,16211	18.14	enq: TX - row lock contention	18.03	OCS_CODE	159/180 [ 88%]	14
1707, 3503	17.35	enq: TX - row lock contention	17.23	OCS_CODE	152/180 [ 84%]	17
1597, 7838	13.83	enq: TX - row lock contention	13.72	OCS_CODE	121/180 [ 67%]	15
1569,30154	12.93	enq: TX - row lock contention	12.81	OCS_CODE	113/180 [ 63%]	14
1625, 5658	10.77	enq: TX - row lock contention	10.66	OCS_CODE	94/180 [ 52%]	15

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	# Samples Active	XIDs
1597, 7838	16.33	enq: TX - row lock contention	16.33	OCS_CODE	179/180 [ 99%]	17
1625, 5658	9.18	enq: TX - row lock contention	9.18	OCS_CODE	161/180 [ 89%]	16
1733,16211	1.13	enq: TX - row lock contention	1.13	OCS_CODE	179/180 [ 99%]	18

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
228578	46.94	enq: TX - row lock contention	46.37	OCS_DATA.ES_REMOVAL_HISTORY$I_1.SYS_P9831 (INDEX PARTITION)	OCS_INDEX
204032	9.41	enq: TX - row lock contention	9.41	OCS_DATA.ES_INSTANCES.SYS_P9073 (TABLE PARTITION)	OCS_DATA
204035	6.69	enq: TX - row lock contention	6.69	OCS_DATA.ES_INSTANCES.SYS_P9076 (TABLE PARTITION)	OCS_DATA
204033	3.85	enq: TX - row lock contention	3.85	OCS_DATA.ES_INSTANCES.SYS_P9074 (TABLE PARTITION)	OCS_DATA
204034	3.85	enq: TX - row lock contention	3.85	OCS_DATA.ES_INSTANCES.SYS_P9075 (TABLE PARTITION)	OCS_DATA

Top DB Files

No data exists for this section of the report.

Top Latches

No data exists for this section of the report.

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:08:55 (1.1 min)	18	enq: TX - row lock contention	18	2.04
15:10:00 (5.0 min)	119	enq: TX - row lock contention	119	13.49
15:15:00 (5.0 min)	110	enq: TX - row lock contention	110	12.47
15:20:00 (5.0 min)	104	enq: TX - row lock contention	99	11.22
		gc buffer busy	4	0.45
		gc current block busy	1	0.11
15:25:00 (5.0 min)	117	enq: TX - row lock contention	117	13.27
15:30:00 (5.0 min)	270	enq: TX - row lock contention	270	30.61
15:35:00 (3.9 min)	144	enq: TX - row lock contention	144	16.33

End of Report