ASH Report For SDSLDB/SDSLDB2

DB Name DB Id Instance Inst num Release RAC Host

SDSLDB 3951945579 SDSLDB2 2 11.2.0.3.0 YES SDSLDB2

DB Name	DB Id	Instance	Inst num	Release	RAC	Host
SDSLDB	3951945579	SDSLDB2	2	11.2.0.3.0	YES	SDSLDB2

CPUs SGA Size Buffer Cache Shared Pool ASH Buffer Size

160 249,250M (100%) 222,208M (89.2%) 23,205M (9.3%) M (%)

CPUs	SGA Size	Buffer Cache	Shared Pool	ASH Buffer Size
160	249,250M (100%)	222,208M (89.2%)	23,205M (9.3%)	M (%)

Sample Time Data Source

Analysis Begin Time: 21-Dec-12 10:00:00 DBA_HIST_ACTIVE_SESS_HISTORY
in AWR snapshot 827

Analysis End Time: 21-Dec-12 11:00:00 DBA_HIST_ACTIVE_SESS_HISTORY
in AWR snapshot 828

Elapsed Time: 60.0 (mins)

Sample Count: 492

Average Active Sessions: 1.37

Avg. Active Session per CPU: 0.01

Report Target: None specified

	Sample Time	Data Source
Analysis Begin Time:	21-Dec-12 10:00:00	DBA_HIST_ACTIVE_SESS_HISTORY in AWR snapshot 827
Analysis End Time:	21-Dec-12 11:00:00	DBA_HIST_ACTIVE_SESS_HISTORY in AWR snapshot 828
Elapsed Time:	60.0 (mins)
Sample Count:	492
Average Active Sessions:	1.37
Avg. Active Session per CPU:	0.01
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
CPU + Wait for CPU	CPU	11.99	0.16
enq: TX - row lock contention	Application	2.85	0.04

Top Background Events

Event	Event Class	% Activity	Avg Active Sessions
SQL*Net message from dblink	Network	72.36	0.99
CPU + Wait for CPU	CPU	9.76	0.13

Top Cluster 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
SQL*Net message from dblink	73.17	"0","1","0"	72.56	driver id	#bytes	NOT DEFINED
enq: TX - row lock contention	2.85	"1415053318","101449752","118"	2.03	name\|mode	usn<<16 \| slot	sequence

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
SYS$BACKGROUND	UNNAMED	83.54	UNNAMED	83.54
SDSLDB	w3wp.exe	11.38	UNNAMED	11.38
SDSLDB	Spotlight on Oracle RAC	3.05	9.0.0.421	3.05
SYS$USERS	UNNAMED	1.02	UNNAMED	1.02

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	29	11.38	0.16
UPDATE	5	3.46	0.05

Back to Load Profile
Back to Top

Top Phases of Execution

Phase of Execution	% Activity	Avg Active Sessions
SQL Execution	15.45	0.21

Back to Load Profile
Back to Top

Top Remote Instances

No data exists for this section of the report.

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
df03gsnzb7nvy	3417936307	12	2.44	CPU + Wait for CPU	2.44	FIXED TABLE - FULL	1.22	SELECT l.latch#, l.gets, l.mis...
7wm2h97qzr8n6	3639528315	8	1.63	CPU + Wait for CPU	1.63	TABLE ACCESS - BY INDEX ROWID	1.42	SELECT COUNT(*) FROM T_DPLS, T...
86rdm5vs90sd6	4210601695	2	1.63	enq: TX - row lock contention	1.63	UPDATE	1.63	UPDATE T_DPLS SET SYLX=:B4 , Z...
72449rta1ztm9	751521491	1	1.22	enq: TX - row lock contention	1.22	UPDATE	1.22	UPDATE T_DPLS SET FLOWID_DHMX ...
arwg6g27pvsw9	387434076	5	1.02	CPU + Wait for CPU	1.02	VIEW	0.61	SELECT Count(*) rowcount FROM...

Top SQL with Top Row Sources

SQL ID	PlanHash	Sampled # of Executions	% Activity	Row Source	% RwSrc	Top Event	% Event	SQL Text
df03gsnzb7nvy	3417936307	12	2.44	FIXED TABLE - FULL	1.22	CPU + Wait for CPU	1.22	SELECT l.latch#, l.gets, l.mis...
7wm2h97qzr8n6	3639528315	8	1.63	TABLE ACCESS - BY INDEX ROWID	1.42	CPU + Wait for CPU	1.42	SELECT COUNT(*) FROM T_DPLS, T...
86rdm5vs90sd6	4210601695	2	1.63	UPDATE	1.63	enq: TX - row lock contention	1.63	UPDATE T_DPLS SET SYLX=:B4 , Z...
72449rta1ztm9	751521491	1	1.22	UPDATE	1.22	enq: TX - row lock contention	1.22	UPDATE T_DPLS SET FLOWID_DHMX ...
arwg6g27pvsw9	387434076	5	1.02	VIEW	0.61	CPU + Wait for CPU	0.61	SELECT Count(*) rowcount FROM...

Top SQL using literals

No data exists for this section of the report.

Top Parsing Module/Action

No data exists for this section of the report.

Complete List of SQL Text

SQL Id SQL Text

72449rta1ztm9 UPDATE T_DPLS SET FLOWID_DHMX = NULL, XDCS=0 WHERE FLOWID_DHMX = :B1

7wm2h97qzr8n6 SELECT COUNT(*) FROM T_DPLS, T_DM_YWBM WHERE T_DPLS.DH = T_DM_YWBM.DH AND TRIM(T_DM_YWBM.YWBMBH) = :B2 AND TRIM(T_DPLS.YWSHBJ) = :B3 AND TRIM(T_DPLS.YWBMBH) = :B2 AND T_DM_YWBM.TXY = :B1 AND T_DPLS.DJLY IN ('TD', 'XP', 'YB', 'XQ', 'QH', 'QQ', 'QS', 'QP', 'LT', 'ZF') AND (ID IS NOT NULL OR ZDQH IS NOT NULL) AND ROWNUM = 1

86rdm5vs90sd6 UPDATE T_DPLS SET SYLX=:B4 , ZJXS=:B3 , ID=:B2 WHERE FLOWID_DHMX = :B1

arwg6g27pvsw9 SELECT Count(*) rowcount FROM T_KCSM T1 , T_KCSM_YWBM T WHERE T1.ID = T.ID AND T.YWBMBH =:YWBMBH And t1.sm like :sm

df03gsnzb7nvy SELECT l.latch#, l.gets, l.misses, l.sleeps, l.immediate_gets, l.spin_gets, COUNT ( * ) n_latches FROM v$latch l, v$latch_children lc WHERE l.latch# = lc.latch#(+) AND l.gets > 0 GROUP BY l.latch#, l.gets, l.misses, l.sleeps, l.immediate_gets, l.spin_gets

SQL Id	SQL Text
72449rta1ztm9	UPDATE T_DPLS SET FLOWID_DHMX = NULL, XDCS=0 WHERE FLOWID_DHMX = :B1
7wm2h97qzr8n6	SELECT COUNT(*) FROM T_DPLS, T_DM_YWBM WHERE T_DPLS.DH = T_DM_YWBM.DH AND TRIM(T_DM_YWBM.YWBMBH) = :B2 AND TRIM(T_DPLS.YWSHBJ) = :B3 AND TRIM(T_DPLS.YWBMBH) = :B2 AND T_DM_YWBM.TXY = :B1 AND T_DPLS.DJLY IN ('TD', 'XP', 'YB', 'XQ', 'QH', 'QQ', 'QS', 'QP', 'LT', 'ZF') AND (ID IS NOT NULL OR ZDQH IS NOT NULL) AND ROWNUM = 1
86rdm5vs90sd6	UPDATE T_DPLS SET SYLX=:B4 , ZJXS=:B3 , ID=:B2 WHERE FLOWID_DHMX = :B1
arwg6g27pvsw9	SELECT Count(*) rowcount FROM T_KCSM T1 , T_KCSM_YWBM T WHERE T1.ID = T.ID AND T.YWBMBH =:YWBMBH And t1.sm like :sm
df03gsnzb7nvy	SELECT l.latch#, l.gets, l.misses, l.sleeps, l.immediate_gets, l.spin_gets, COUNT ( * ) n_latches FROM v$latch l, v$latch_children lc WHERE l.latch# = lc.latch#(+) AND l.gets > 0 GROUP BY l.latch#, l.gets, l.misses, l.sleeps, l.immediate_gets, l.spin_gets

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
DBSL.PKG_DDSP_ALL.PROC_DDSP_WORK	2.85	SQL	2.85
DBSL.PROC_DHSP_ZDTJ	1.63	SQL	1.63
DBSL.PROC_DHSP_GLYJ_HF	1.22	SQL	1.22

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	64	13.01	0.18
FETCH	8	1.63	0.02

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
2209, 1	72.76	SQL*Net message from dblink	72.36	UserID: 2147483646	oracle@SDSLDB2 (RECO)	356/360 [ 99%]	0
577, 1	6.30	CPU + Wait for CPU	6.30	SYS	oracle@SDSLDB2 (LMD0)	31/360 [ 9%]	0
3985,14243	3.05	CPU + Wait for CPU	2.85	TOAD	Spotlight.exe	14/360 [ 4%]	0
7249, 253	1.22	enq: TX - row lock contention	1.22	DBSL	w3wp.exe	6/360 [ 2%]	1
481, 1	1.02	CPU + Wait for CPU	1.02	SYS	oracle@SDSLDB2 (DIA0)	5/360 [ 1%]	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 (Inst)	% Activity	Event Caused	% Event	User	Program	# Samples Active	XIDs
7057, 3357( 1)	1.83	enq: TX - row lock contention	1.83	NOT FOUND	BLOCKING SESSION NOT FOUND	0/360 [ 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

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

Object ID	% Activity	Event	% Event	Object Name (Type)	Tablespace
79565	3.05	enq: TX - row lock contention	2.85	DBSL.T_DPLS (TABLE)	DATA_TABLESPACE

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
10:00:00 (5.0 min)	38	SQL*Net message from dblink	31	6.30
10:00:00 (5.0 min)	38	CPU + Wait for CPU	7	1.42
10:05:00 (5.0 min)	39	SQL*Net message from dblink	29	5.89
10:05:00 (5.0 min)	39	CPU + Wait for CPU	7	1.42
10:05:00 (5.0 min)	39	PX Deq: Slave Session Stats	1	0.20
10:10:00 (5.0 min)	44	SQL*Net message from dblink	30	6.10
10:10:00 (5.0 min)	44	CPU + Wait for CPU	10	2.03
10:10:00 (5.0 min)	44	db file parallel write	2	0.41
10:15:00 (5.0 min)	36	SQL*Net message from dblink	29	5.89
10:15:00 (5.0 min)	36	CPU + Wait for CPU	6	1.22
10:15:00 (5.0 min)	36	enq: TX - contention	1	0.20
10:20:00 (5.0 min)	45	SQL*Net message from dblink	30	6.10
10:20:00 (5.0 min)	45	CPU + Wait for CPU	14	2.85
10:20:00 (5.0 min)	45	db file parallel write	1	0.20
10:25:00 (5.0 min)	49	SQL*Net message from dblink	30	6.10
10:25:00 (5.0 min)	49	CPU + Wait for CPU	18	3.66
10:25:00 (5.0 min)	49	gc current grant busy	1	0.20
10:30:00 (5.0 min)	35	SQL*Net message from dblink	30	6.10
10:30:00 (5.0 min)	35	CPU + Wait for CPU	5	1.02
10:35:00 (5.0 min)	37	SQL*Net message from dblink	30	6.10
10:35:00 (5.0 min)	37	CPU + Wait for CPU	7	1.42
10:40:00 (5.0 min)	40	SQL*Net message from dblink	29	5.89
10:40:00 (5.0 min)	40	CPU + Wait for CPU	10	2.03
10:40:00 (5.0 min)	40	control file sequential read	1	0.20
10:45:00 (5.0 min)	35	SQL*Net message from dblink	30	6.10
10:45:00 (5.0 min)	35	CPU + Wait for CPU	5	1.02
10:50:00 (5.0 min)	49	SQL*Net message from dblink	30	6.10
10:50:00 (5.0 min)	49	enq: TX - row lock contention	10	2.03
10:50:00 (5.0 min)	49	CPU + Wait for CPU	9	1.83
10:55:00 (5.0 min)	45	SQL*Net message from dblink	32	6.50
10:55:00 (5.0 min)	45	CPU + Wait for CPU	9	1.83
10:55:00 (5.0 min)	45	enq: TX - row lock contention	4	0.81

End of Report