ASH Report For ODSDB/odsdb2

DB Name DB Id Instance Inst num Release RAC Host

ODSDB 3367537179 odsdb2 2 10.2.0.4.0 YES cwgkdb2

DB Name	DB Id	Instance	Inst num	Release	RAC	Host
ODSDB	3367537179	odsdb2	2	10.2.0.4.0	YES	cwgkdb2

CPUs SGA Size Buffer Cache Shared Pool ASH Buffer Size

8 13,312M (100%) 8,992M (67.5%) 3,909M (29.4%) M (%)

CPUs	SGA Size	Buffer Cache	Shared Pool	ASH Buffer Size
8	13,312M (100%)	8,992M (67.5%)	3,909M (29.4%)	M (%)

Sample Time Data Source

Analysis Begin Time: 02-3月 -12 03:00:00 DBA_HIST_ACTIVE_SESS_HISTORY
in AWR snapshot 12890

Analysis End Time: 02-3月 -12 04:00:00 DBA_HIST_ACTIVE_SESS_HISTORY
in AWR snapshot 12891
+ V$ACTIVE_SESSION_HISTORY

Elapsed Time: 60.0 (mins)

Sample Count: 7,850

Average Active Sessions: 21.81

Avg. Active Session per CPU: 2.73

Report Target: None specified

	Sample Time	Data Source
Analysis Begin Time:	02-3月 -12 03:00:00	DBA_HIST_ACTIVE_SESS_HISTORY in AWR snapshot 12890
Analysis End Time:	02-3月 -12 04:00:00	DBA_HIST_ACTIVE_SESS_HISTORY in AWR snapshot 12891 + V$ACTIVE_SESSION_HISTORY
Elapsed Time:	60.0 (mins)
Sample Count:	7,850
Average Active Sessions:	21.81
Avg. Active Session per CPU:	2.73
Report Target:	None specified

Top Events

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

Top User Events

Event	Event Class	% Activity	Avg Active Sessions
cursor: pin S wait on X	Concurrency	66.87	14.58
row cache lock	Concurrency	13.22	2.88
library cache load lock	Concurrency	4.79	1.04
CPU + Wait for CPU	CPU	2.32	0.51

Top Background Events

Event	Event Class	% Activity	Avg Active Sessions
row cache lock	Concurrency	4.55	0.99
latch: shared pool	Concurrency	2.70	0.59
cursor: pin S wait on X	Concurrency	2.33	0.51

Top Event P1/P2/P3 Values

Event	% Event	P1 Value, P2 Value, P3 Value	% Activity	Parameter 1	Parameter 2	Parameter 3
cursor: pin S wait on X	69.20	"1232072917","1486058684416","21475124760"	0.03	idn	value	where\|sleeps
row cache lock	17.77	"16","0","3"	9.01	cache id	mode	request
		"8","0","3"	4.19
		"11","0","3"	2.59
library cache load lock	4.79	"504403171427990032","504403171624562016","76901"	2.19	object address	lock address	100*mask+namespace
		"504403169709415448","504403171624564824","101"	1.68
latch: shared pool	2.78	"504403158534902464","214","0"	1.75	address	number	tries

Load Profile

Top Service/Module
Top Client IDs
Top SQL Command Types

Top Service/Module

Service	Module	% Activity	Action	% Action
SYS$USERS	UNNAMED	48.37	UNNAMED	48.37
	JDBC Thin Client	25.92	UNNAMED	25.92
odsdb	UNNAMED	11.92	UNNAMED	11.92
SYS$BACKGROUND	UNNAMED	11.52	UNNAMED	11.52
SYS$USERS	racgimon@cwgkdb2 (TNS V1-V3)	2.24	UNNAMED	2.24

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	15	48.48	10.57
PL/SQL EXECUTE	2	2.29	0.50

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
82rgxmjf8nq0w	3675634750	15.75	cursor: pin S wait on X	15.36	select nbitask0_.ID_ as ID1_16...
c6j034dcffqaq	3761072682	6.29	cursor: pin S wait on X	6.28	select nbitask0_.ID_ as ID1_16...
b8t4pnddvjst3	3778432711	4.36	cursor: pin S wait on X	2.54	select * from ( select nbitask...
			row cache lock	1.82
5tbg1nsszw5tc		2.22	CPU + Wait for CPU	2.22	BEGIN CREATE_DATABASE_STATUS;...
0r5x6sbm1qrnr	4255310402	2.20	row cache lock	2.17	select serviceass0_.ID_ as ID1...

Top SQL using literals

No data exists for this section of the report.

Complete List of SQL Text

SQL Id SQL Text

0r5x6sbm1qrnr select serviceass0_.ID_ as ID1_1_, serviceass0_.OBJ_VERSION_ as OBJ3_1_, serviceass0_.STATE_ as STATE4_1_, serviceass0_.NAME_ as NAME5_1_, serviceass0_.DESCRIPTION_ as DESCRIPT6_1_, serviceass0_.BUNDLE_VERSION_ as BUNDLE7_1_, serviceass0_.CREATE_DATE_ as CREATE8_1_, serviceass0_.LAST_MODIFIED_DATE_ as LAST9_1_, serviceass0_.SINGLETON_ as SINGLETON11_1_ from MG_BUNDLE_INFO serviceass0_ where se rviceass0_.CLASS_='S' and serviceass0_.SINGLETON_=1

5tbg1nsszw5tc BEGIN CREATE_DATABASE_STATUS; END;

82rgxmjf8nq0w select nbitask0_.ID_ as ID1_16_, nbitask0_.OBJ_VERSION_ as OBJ2_16_, nbitask0_.TYPE_ as TYPE3_16_, nbitask0_.EXCUTE_TIMES_ as EXCUTE4_16_, nbitask0_.CONTENT_ as CONTENT5_16_, nbitask0_.MAPPING_TYPE_ as MAPPING6_16_, nbitask0_.OBJECT_ as OBJECT7_16_, nbitask0_.INT_RULE_ID_ as INT8_16_, nbitask0_.LAST_ACCESS_TIME_ as LAST9_16_, nbitask0_.CREATED_TIME_ as CREATED10_16_, nbitask0_.EXCUTED_ as EX CUTED11_16_, nbitask0_.EXCUTED_WITH_FAULT_ as EXCUTED12_16_ from NBI_TASK nbitask0_, NBI_INTEGRATION_RULE integratio1_ where nbitask0_.INT_RULE_ID_=integratio1_.ID_ and integratio1_.STOP_=0 and nbitask0_.EXCUTED_=0 and nbitask0_.EXCUTED_WITH_FAULT_=0

b8t4pnddvjst3 select * from ( select nbitask0_.ID_ as ID1_16_, nbitask0_.OBJ_VERSION_ as OBJ2_16_, nbitask0_.TYPE_ as TYPE3_16_, nbitask0_.EXCUTE_TIMES_ as EXCUTE4_16_, nbitask0_.CONTENT_ as CONTENT5_16_, nbitask0_.MAPPING_TYPE_ as MAPPING6_16_, nbitask0_.OBJECT_ as OBJECT7_16_, nbitask0_.INT_RULE_ID_ as INT8_16_, nbitask0_.LAST_ACCESS_TIME_ as LAST9_16_, nbitask0_.CREATED_TIME_ as CREATED10_16_, nbitas k0_.EXCUTED_ as EXCUTED11_16_, nbitask0_.EXCUTED_WITH_FAULT_ as EXCUTED12_16_ from NBI_TASK nbitask0_ where nbitask0_.EXCUTED_=1 or nbitask0_.EXCUTED_WITH_FAULT_=1 and nbitask0_.CREATED_TIME_<=:1 ) where rownum <= :2

c6j034dcffqaq select nbitask0_.ID_ as ID1_16_, nbitask0_.OBJ_VERSION_ as OBJ2_16_, nbitask0_.TYPE_ as TYPE3_16_, nbitask0_.CONTENT_ as CONTENT4_16_, nbitask0_.MAPPING_TYPE_ as MAPPING5_16_, nbitask0_.OBJECT_ as OBJECT6_16_, nbitask0_.INT_RULE_ID_ as INT7_16_, nbitask0_.LAST_ACCESS_TIME_ as LAST8_16_, nbitask0_.CREATED_TIME_ as CREATED9_16_, nbitask0_.EXCUTED_ as EXCUTED10_16_ from NBI_TASK nbitask0_, NBI_ INTEGRATION_RULE integratio1_ where nbitask0_.INT_RULE_ID_=integratio1_.ID_ and integratio1_.STOP_=0 and nbitask0_.EXCUTED_=0

SQL Id	SQL Text
0r5x6sbm1qrnr	select serviceass0_.ID_ as ID1_1_, serviceass0_.OBJ_VERSION_ as OBJ3_1_, serviceass0_.STATE_ as STATE4_1_, serviceass0_.NAME_ as NAME5_1_, serviceass0_.DESCRIPTION_ as DESCRIPT6_1_, serviceass0_.BUNDLE_VERSION_ as BUNDLE7_1_, serviceass0_.CREATE_DATE_ as CREATE8_1_, serviceass0_.LAST_MODIFIED_DATE_ as LAST9_1_, serviceass0_.SINGLETON_ as SINGLETON11_1_ from MG_BUNDLE_INFO serviceass0_ where se rviceass0_.CLASS_='S' and serviceass0_.SINGLETON_=1
5tbg1nsszw5tc	BEGIN CREATE_DATABASE_STATUS; END;
82rgxmjf8nq0w	select nbitask0_.ID_ as ID1_16_, nbitask0_.OBJ_VERSION_ as OBJ2_16_, nbitask0_.TYPE_ as TYPE3_16_, nbitask0_.EXCUTE_TIMES_ as EXCUTE4_16_, nbitask0_.CONTENT_ as CONTENT5_16_, nbitask0_.MAPPING_TYPE_ as MAPPING6_16_, nbitask0_.OBJECT_ as OBJECT7_16_, nbitask0_.INT_RULE_ID_ as INT8_16_, nbitask0_.LAST_ACCESS_TIME_ as LAST9_16_, nbitask0_.CREATED_TIME_ as CREATED10_16_, nbitask0_.EXCUTED_ as EX CUTED11_16_, nbitask0_.EXCUTED_WITH_FAULT_ as EXCUTED12_16_ from NBI_TASK nbitask0_, NBI_INTEGRATION_RULE integratio1_ where nbitask0_.INT_RULE_ID_=integratio1_.ID_ and integratio1_.STOP_=0 and nbitask0_.EXCUTED_=0 and nbitask0_.EXCUTED_WITH_FAULT_=0
b8t4pnddvjst3	select * from ( select nbitask0_.ID_ as ID1_16_, nbitask0_.OBJ_VERSION_ as OBJ2_16_, nbitask0_.TYPE_ as TYPE3_16_, nbitask0_.EXCUTE_TIMES_ as EXCUTE4_16_, nbitask0_.CONTENT_ as CONTENT5_16_, nbitask0_.MAPPING_TYPE_ as MAPPING6_16_, nbitask0_.OBJECT_ as OBJECT7_16_, nbitask0_.INT_RULE_ID_ as INT8_16_, nbitask0_.LAST_ACCESS_TIME_ as LAST9_16_, nbitask0_.CREATED_TIME_ as CREATED10_16_, nbitas k0_.EXCUTED_ as EXCUTED11_16_, nbitask0_.EXCUTED_WITH_FAULT_ as EXCUTED12_16_ from NBI_TASK nbitask0_ where nbitask0_.EXCUTED_=1 or nbitask0_.EXCUTED_WITH_FAULT_=1 and nbitask0_.CREATED_TIME_<=:1 ) where rownum <= :2
c6j034dcffqaq	select nbitask0_.ID_ as ID1_16_, nbitask0_.OBJ_VERSION_ as OBJ2_16_, nbitask0_.TYPE_ as TYPE3_16_, nbitask0_.CONTENT_ as CONTENT4_16_, nbitask0_.MAPPING_TYPE_ as MAPPING5_16_, nbitask0_.OBJECT_ as OBJECT6_16_, nbitask0_.INT_RULE_ID_ as INT7_16_, nbitask0_.LAST_ACCESS_TIME_ as LAST8_16_, nbitask0_.CREATED_TIME_ as CREATED9_16_, nbitask0_.EXCUTED_ as EXCUTED10_16_ from NBI_TASK nbitask0_, NBI_ INTEGRATION_RULE integratio1_ where nbitask0_.INT_RULE_ID_=integratio1_.ID_ and integratio1_.STOP_=0 and nbitask0_.EXCUTED_=0

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
FMIS0400.RUN_STICK_JOB	3.04	FMIS0400.EXECSQL	2.23

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
333,32393	2.27	CPU + Wait for CPU	2.27	FMIS0400	oracle@cwgkdb2 (J000)	178/360 [ 49%]	3
430, 1	2.23	row cache lock	1.83	SYS	oracle@cwgkdb2 (MMON)	144/360 [ 40%]	0
346, 7672	2.22	cursor: pin S wait on X	2.20	NBIXT	JDBC Thin Client	173/360 [ 48%]	0
363, 3	2.22	cursor: pin S wait on X	2.22	NBI		174/360 [ 48%]	0
378, 349	2.22	cursor: pin S wait on X	1.83	NBI		144/360 [ 40%]	0

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
370,30447	2.19	library cache load lock	2.19	FMIS0400		173/360 [ 48%]	0
353,53749	1.68	library cache load lock	1.68	SYS		132/360 [ 37%]	0

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

No data exists for this section of the report.

Top DB Files

No data exists for this section of the report.

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	2.78	Held Shared	2.78	0.29	215	1	63	151	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
03:00:00 (5.0 min)	33	gc current grant 2-way	23	0.29
		gc cr disk read	3	0.04
		CPU + Wait for CPU	2	0.03
03:05:00 (5.0 min)	30	gc current grant 2-way	21	0.27
		gc cr disk read	3	0.04
		gc current grant busy	3	0.04
03:10:00 (5.0 min)	835	cursor: pin S wait on X	573	7.30
		row cache lock	180	2.29
		CPU + Wait for CPU	28	0.36
03:15:00 (5.0 min)	635	cursor: pin S wait on X	467	5.95
		row cache lock	119	1.52
		CPU + Wait for CPU	15	0.19
03:25:00 (5.0 min)	271	cursor: pin S wait on X	198	2.52
		row cache lock	48	0.61
		library cache load lock	12	0.15
03:30:00 (5.0 min)	1,354	cursor: pin S wait on X	990	12.61
		row cache lock	222	2.83
		library cache load lock	60	0.76
03:35:00 (5.0 min)	1,366	cursor: pin S wait on X	997	12.70
		row cache lock	240	3.06
		library cache load lock	66	0.84
03:40:00 (5.0 min)	1,440	cursor: pin S wait on X	1,049	13.36
		row cache lock	240	3.06
		library cache load lock	90	1.15
03:45:00 (5.0 min)	1,525	cursor: pin S wait on X	958	12.20
		row cache lock	286	3.64
		library cache load lock	90	1.15
03:50:00 (5.0 min)	319	cursor: pin S wait on X	186	2.37
		row cache lock	60	0.76
		enq: CF - contention	18	0.23
03:55:00 (5.0 min)	42	cursor: pin S wait on X	14	0.18
		latch: library cache	11	0.14
		latch: shared pool	10	0.13

End of Report