WORKLOAD REPOSITORY report for

DB Name DB Id Instance Inst num Startup Time Release RAC

DEMO 3430975138 DEMO1 1 26-Jun-09 00:06 11.1.0.7.0 YES

DB Name	DB Id	Instance	Inst num	Startup Time	Release	RAC
DEMO	3430975138	DEMO1	1	26-Jun-09 00:06	11.1.0.7.0	YES

Host Name Platform CPUs Cores Sockets Memory (GB)

host101.us.oracle.com Linux x86 64-bit 8 8 2 31.44

Host Name	Platform	CPUs	Cores	Sockets	Memory (GB)
host101.us.oracle.com	Linux x86 64-bit	8	8	2	31.44

Snap Id Snap Time Sessions Cursors/Session

Begin Snap: 910 27-Jun-09 17:16:56 45 1.6

End Snap: 911 27-Jun-09 17:21:53 56 1.3

Elapsed: 4.96 (mins)

DB Time: 52.43 (mins)

	Snap Id	Snap Time	Sessions	Cursors/Session
Begin Snap:	910	27-Jun-09 17:16:56	45	1.6
End Snap:	911	27-Jun-09 17:21:53	56	1.3
Elapsed:		4.96 (mins)
DB Time:		52.43 (mins)

Report Summary

Cache Sizes

Begin End

Buffer Cache: 4,736M 4,736M Std Block Size: 8K

Shared Pool Size: 1,088M 1,088M Log Buffer: 35,328K

	Begin	End
Buffer Cache:	4,736M	4,736M	Std Block Size:	8K
Shared Pool Size:	1,088M	1,088M	Log Buffer:	35,328K

Load Profile

Per Second Per Transaction Per Exec Per Call

DB Time(s): 10.6 46.3 1.22 1.22

DB CPU(s): 4.8 21.1 0.56 0.56

Redo size: 6,476.9 28,332.8

Logical reads: 25,800.6 112,863.9

Block changes: 40.0 174.8

Physical reads: 45,116.2 197,359.3

Physical writes: 19,496.9 85,288.8

User calls: 8.7 37.9

Parses: 5.4 23.8

Hard parses: 0.7 3.3

W/A MB processed: 251,314.9 1,099,369.4

Logons: 1.4 5.9

Executes: 8.7 37.9

Rollbacks: 0.1 0.5

Transactions: 0.2

	Per Second	Per Transaction	Per Exec	Per Call
DB Time(s):	10.6	46.3	1.22	1.22
DB CPU(s):	4.8	21.1	0.56	0.56
Redo size:	6,476.9	28,332.8
Logical reads:	25,800.6	112,863.9
Block changes:	40.0	174.8
Physical reads:	45,116.2	197,359.3
Physical writes:	19,496.9	85,288.8
User calls:	8.7	37.9
Parses:	5.4	23.8
Hard parses:	0.7	3.3
W/A MB processed:	251,314.9	1,099,369.4
Logons:	1.4	5.9
Executes:	8.7	37.9
Rollbacks:	0.1	0.5
Transactions:	0.2

Instance Efficiency Percentages (Target 100%)

Buffer Nowait %: 61.54 Redo NoWait %: 100.00

Buffer Hit %: 99.78 In-memory Sort %: 100.00

Library Hit %: 94.09 Soft Parse %: 86.32

Execute to Parse %: 37.22 Latch Hit %: 99.99

Parse CPU to Parse Elapsd %: 0.00 % Non-Parse CPU: 99.97

Shared Pool Statistics

Begin End

Memory Usage %: 78.87 79.30

% SQL with executions>1: 95.20 94.98

% Memory for SQL w/exec>1: 90.16 72.45

	Begin	End
Memory Usage %:	78.87	79.30
% SQL with executions>1:	95.20	94.98
% Memory for SQL w/exec>1:	90.16	72.45

Top 5 Timed Foreground Events

Event	Waits	Time(s)	Avg wait (ms)	% DB time	Wait Class
DB CPU		1,437		45.68
direct path read temp	186,865	1,005	5	31.95	User I/O
direct path write temp	186,851	356	2	11.30	User I/O
PX qref latch	23,428,063	63	0	2.00	Other
cell smart table scan	14,047	55	4	1.75	User I/O

Host CPU (CPUs: 8 Cores: 8 Sockets: 2)

Load Average Begin Load Average End %User %System %WIO %Idle

0.09 9.75 59.2 4.3 0.1 35.8

Instance CPU

Load Average Begin	Load Average End	%User	%System	%WIO	%Idle
0.09	9.75	59.2	4.3	0.1	35.8

%Total CPU %Busy CPU %DB time waiting for CPU (Resource Manager)

60.6 94.5 0.0

Memory Statistics

%Total CPU	%Busy CPU	%DB time waiting for CPU (Resource Manager)
60.6	94.5	0.0

Begin End

Host Mem (MB): 32,189.6 32,189.6

SGA use (MB): 6,144.0 6,144.0

PGA use (MB): 473.4 565.8

% Host Mem used for SGA+PGA: 20.56 20.56

	Begin	End
Host Mem (MB):	32,189.6	32,189.6
SGA use (MB):	6,144.0	6,144.0
PGA use (MB):	473.4	565.8
% Host Mem used for SGA+PGA:	20.56	20.56

RAC Statistics

Begin End

Number of Instances: 8 8

	Begin	End
Number of Instances:	8	8

Global Cache Load Profile

Per Second Per Transaction

Global Cache blocks received: 5.78 25.29

Global Cache blocks served: 1.55 6.78

GCS/GES messages received: 45.22 197.79

GCS/GES messages sent: 54.37 237.82

DBWR Fusion writes: 0.03 0.12

Estd Interconnect traffic (KB) 78.11

	Per Second	Per Transaction
Global Cache blocks received:	5.78	25.29
Global Cache blocks served:	1.55	6.78
GCS/GES messages received:	45.22	197.79
GCS/GES messages sent:	54.37	237.82
DBWR Fusion writes:	0.03	0.12
Estd Interconnect traffic (KB)	78.11

Global Cache Efficiency Percentages (Target local+remote 100%)

Buffer access - local cache %: 96.14

Buffer access - remote cache %: 3.64

Buffer access - disk %: 0.22

Global Cache and Enqueue Services - Workload Characteristics

Avg global enqueue get time (ms): 0.1

Avg global cache cr block receive time (ms): 0.4

Avg global cache current block receive time (ms): 0.3

Avg global cache cr block build time (ms): 0.0

Avg global cache cr block send time (ms): 0.0

Global cache log flushes for cr blocks served %: 14.0

Avg global cache cr block flush time (ms): 0.5

Avg global cache current block pin time (ms): 0.0

Avg global cache current block send time (ms): 0.0

Global cache log flushes for current blocks served %: 1.9

Avg global cache current block flush time (ms): 0.0

Global Cache and Enqueue Services - Messaging Statistics

Avg message sent queue time (ms): 3,563.3

Avg message sent queue time on ksxp (ms): 0.2

Avg message received queue time (ms): 0.0

Avg GCS message process time (ms): 0.0

Avg GES message process time (ms): 0.0

% of direct sent messages: 55.55

% of indirect sent messages: 30.34

% of flow controlled messages: 14.12

Cluster Interconnect

Interface	IP Address	Pub	Source	IP	Pub	Src
	Begin			End
bond0	172.31.88.109	N	Oracle Cluster Repository

Main Report

Report Summary
Wait Events Statistics
SQL Statistics
Instance Activity Statistics
IO Stats
Buffer Pool Statistics
Advisory Statistics
Wait Statistics
Undo Statistics
Latch Statistics
Segment Statistics
Dictionary Cache Statistics
Library Cache Statistics
Memory Statistics
Streams Statistics
Resource Limit Statistics
init.ora Parameters

Wait Events Statistics

Time Model Statistics
Operating System Statistics
Operating System Statistics - Detail
Foreground Wait Class
Foreground Wait Events
Background Wait Events
Wait Event Histogram
Service Statistics
Service Wait Class Stats

Time Model Statistics

Total time in database user-calls (DB Time): 3145.8s
Statistics including the word "background" measure background process time, and so do not contribute to the DB time statistic
Ordered by % or DB time desc, Statistic name

Statistic Name	Time (s)	% of DB Time
sql execute elapsed time	3,065.80	97.46
DB CPU	1,436.90	45.68
parse time elapsed	2.64	0.08
hard parse elapsed time	2.24	0.07
PL/SQL execution elapsed time	1.74	0.06
hard parse (sharing criteria) elapsed time	0.69	0.02
PL/SQL compilation elapsed time	0.35	0.01
failed parse elapsed time	0.20	0.01
sequence load elapsed time	0.04	0.00
connection management call elapsed time	0.03	0.00
repeated bind elapsed time	0.00	0.00
hard parse (bind mismatch) elapsed time	0.00	0.00
DB time	3,145.78
background elapsed time	20.87
background cpu time	4.17

Back to Wait Events Statistics
Back to Top

Operating System Statistics

*TIME statistic values are diffed. All others display actual values. End Value is displayed if different
ordered by statistic type (CPU Use, Virtual Memory, Hardware Config), Name

Statistic	Value	End Value
BUSY_TIME	152,559
IDLE_TIME	85,182
IOWAIT_TIME	158
NICE_TIME	0
SYS_TIME	10,143
USER_TIME	140,725
LOAD	0	10
PHYSICAL_MEMORY_BYTES	33,753,260,032
NUM_CPUS	8
NUM_CPU_CORES	8
NUM_CPU_SOCKETS	2
GLOBAL_RECEIVE_SIZE_MAX	4,194,304
GLOBAL_SEND_SIZE_MAX	2,097,152
TCP_RECEIVE_SIZE_DEFAULT	87,380
TCP_RECEIVE_SIZE_MAX	4,194,304
TCP_RECEIVE_SIZE_MIN	4,096
TCP_SEND_SIZE_DEFAULT	16,384
TCP_SEND_SIZE_MAX	4,194,304
TCP_SEND_SIZE_MIN	4,096

Back to Wait Events Statistics
Back to Top

Operating System Statistics - Detail

Snap Time	Load	%busy	%user	%sys	%idle	%iowait
27-Jun 17:16:56	0.09
27-Jun 17:21:53	9.75	64.17	59.19	4.27	0.07	35.83

Back to Wait Events Statistics
Back to Top

Foreground Wait Class

s - second, ms - millisecond - 1000th of a second
ordered by wait time desc, waits desc
%Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0
Captured Time accounts for 95.0% of Total DB time 3,145.78 (s)
Total FG Wait Time: 1,551.28 (s) DB CPU time: 1,436.90 (s)

Wait Class	Waits	%Time -outs	Total Wait Time (s)	Avg wait (ms)	%DB time
DB CPU			1,437		45.68
User I/O	397,246	0	1,423	4	45.23
Other	23,444,850	100	77	0	2.44
Concurrency	19,548	0	50	3	1.59
System I/O	1,463	0	1	1	0.03
Cluster	1,282	0	0	0	0.01
Application	102	0	0	2	0.01
Commit	3	0	0	16	0.00
Network	552	0	0	0	0.00
Configuration	2	0	0	0	0.00

Back to Wait Events Statistics
Back to Top

Foreground Wait Events

s - second, ms - millisecond - 1000th of a second
Only events with Total Wait Time (s) >= .001 are shown
ordered by wait time desc, waits desc (idle events last)
%Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0

Event	Waits	%Time -outs	Total Wait Time (s)	Avg wait (ms)	Waits /txn	% DB time
direct path read temp	186,865	0	1,005	5	2,748.01	31.95
direct path write temp	186,851	0	356	2	2,747.81	11.30
PX qref latch	23,428,063	100	63	0	344,530.34	2.00
cell smart table scan	14,047	2	55	4	206.57	1.75
buffer busy waits	18,166	0	48	3	267.15	1.54
local write wait	9,366	0	7	1	137.74	0.21
enq: PS - contention	1,842	49	3	2	27.09	0.09
DFS lock handle	35	0	2	57	0.51	0.06
reliable message	716	0	2	3	10.53	0.06
PX Deq: Slave Session Stats	511	0	2	4	7.51	0.06
enq: PV - syncstart	15	0	2	105	0.22	0.05
PX Deq: Signal ACK RSG	102	53	1	14	1.50	0.04
control file sequential read	1,463	0	1	1	21.51	0.03
latch free	318	0	1	3	4.68	0.03
cell single block physical read	109	0	1	6	1.60	0.02
latch: shared pool	74	0	1	7	1.09	0.02
enq: WF - contention	20	0	0	18	0.29	0.01
row cache lock	357	0	0	1	5.25	0.01
library cache lock	315	0	0	1	4.63	0.01
os thread startup	12	0	0	20	0.18	0.01
IPC send completion sync	1,849	99	0	0	27.19	0.01
name-service call wait	2	0	0	106	0.03	0.01
SQL*Net break/reset to client	80	0	0	3	1.18	0.01
rdbms ipc reply	541	0	0	0	7.96	0.01
PX Deq: reap credit	10,458	100	0	0	153.79	0.01
library cache pin	385	0	0	0	5.66	0.01
gc buffer busy acquire	21	0	0	5	0.31	0.00
gc cr block 3-way	310	0	0	0	4.56	0.00
gc current block 3-way	301	0	0	0	4.43	0.00
latch: ges resource hash list	189	0	0	0	2.78	0.00
PX Deq: Signal ACK EXT	58	0	0	1	0.85	0.00
log file sync	3	0	0	16	0.04	0.00
gc current grant busy	65	0	0	1	0.96	0.00
enq: XL - fault extent map	2	0	0	21	0.03	0.00
db file parallel read	1	0	0	34	0.01	0.00
gc cr block 2-way	178	0	0	0	2.62	0.00
latch: session allocation	29	0	0	1	0.43	0.00
gc current block 2-way	120	0	0	0	1.76	0.00
gc cr grant 2-way	43	0	0	0	0.63	0.00
gc cr multi block request	183	0	0	0	2.69	0.00
latch: cache buffers chains	201	0	0	0	2.96	0.00
gc current block busy	16	0	0	1	0.24	0.00
enq: RO - fast object reuse	17	0	0	1	0.25	0.00
enq: PI - contention	8	100	0	1	0.12	0.00
direct path read	3	0	0	3	0.04	0.00
cell multiblock physical read	3	0	0	3	0.04	0.00
enq: TM - contention	2	0	0	4	0.03	0.00
enq: TO - contention	4	0	0	2	0.06	0.00
latch: enqueue hash chains	28	0	0	0	0.41	0.00
gc cr block busy	7	0	0	1	0.10	0.00
enq: TX - index contention	2	0	0	2	0.03	0.00
enq: JQ - contention	2	100	0	2	0.03	0.00
gc current grant 2-way	31	0	0	0	0.46	0.00
SQL*Net more data from client	10	0	0	0	0.15	0.00
gc current multi block request	6	0	0	0	0.09	0.00
SQL*Net message to client	539	0	0	0	7.93	0.00
ASM file metadata operation	35	0	0	0	0.51	0.00
PX Deq Credit: send blkd	265,426	0	2,571	10	3,903.32
PX Deq: Table Q Normal	1,395,629	0	2,079	1	20,523.96
PX Deq: Execution Msg	3,036	20	1,569	517	44.65
SQL*Net message from client	539	0	1,484	2754	7.93
Streams AQ: waiting for messages in the queue	90	99	330	3661	1.32
PX Deq: Execute Reply	1,151	8	293	254	16.93
PX Deq Credit: need buffer	29,229	0	125	4	429.84
jobq slave wait	20	100	60	3002	0.29
KSV master wait	64	0	3	48	0.94
PX Deq: Parse Reply	340	0	1	3	5.00
PX Deq: Join ACK	611	27	0	1	8.99
PX Deq: Msg Fragment	6	0	0	0	0.09

Back to Wait Events Statistics
Back to Top

Background Wait Events

ordered by wait time desc, waits desc (idle events last)
Only events with Total Wait Time (s) >= .001 are shown
%Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0

Event	Waits	%Time -outs	Total Wait Time (s)	Avg wait (ms)	Waits /txn	% bg time
control file sequential read	685	0	5	7	10.07	24.36
db file parallel write	7,288	0	4	1	107.18	21.00
ASM file metadata operation	438	0	3	7	6.44	14.05
control file parallel write	106	0	2	17	1.56	8.39
PX Deq: reap credit	4,758	100	0	0	69.97	1.70
os thread startup	4	0	0	70	0.06	1.34
name-service call wait	3	0	0	71	0.04	1.02
log file parallel write	113	0	0	1	1.66	0.78
DFS lock handle	296	100	0	0	4.35	0.60
gc cr block 2-way	275	0	0	0	4.04	0.49
latch: session allocation	17	0	0	2	0.25	0.14
CGS wait for IPC msg	2,335	100	0	0	34.34	0.09
ksxr poll remote instances	2,294	100	0	0	33.74	0.09
gcs log flush sync	77	0	0	0	1.13	0.07
reliable message	20	0	0	1	0.29	0.06
gc current block 3-way	13	0	0	1	0.19	0.06
enq: CF - contention	12	83	0	1	0.18	0.06
gc current block 2-way	26	0	0	0	0.38	0.05
library cache pin	13	0	0	1	0.19	0.05
enq: RO - fast object reuse	6	0	0	2	0.09	0.05
latch: shared pool	15	0	0	1	0.22	0.04
latch free	7	0	0	1	0.10	0.02
enq: TD - KTF dump entries	1	0	0	4	0.01	0.02
enq: PI - contention	2	100	0	1	0.03	0.01
gc cr block 3-way	5	0	0	0	0.07	0.01
latch: ges resource hash list	2	0	0	1	0.03	0.01
row cache lock	1	0	0	2	0.01	0.01
rdbms ipc reply	10	0	0	0	0.15	0.01
PX Idle Wait	4,371	91	10,368	2372	64.28
rdbms ipc message	17,973	24	5,345	297	264.31
class slave wait	1,081	98	3,227	2985	15.90
wait for unread message on broadcast channel	594	100	595	1002	8.74
gcs remote message	16,510	84	595	36	242.79
DIAG idle wait	13,079	73	593	45	192.34
Streams AQ: qmn slave idle wait	31	0	578	18649	0.46
Streams AQ: waiting for time management or cleanup tasks	5	60	401	80293	0.07
Space Manager: slave idle wait	60	100	300	5002	0.88
dispatcher timer	5	100	300	60004	0.07
fbar timer	1	100	300	300008	0.01
ges remote message	11,912	21	297	25	175.18
pmon timer	114	87	297	2605	1.68
smon timer	59	0	295	4999	0.87
ASM background timer	130	0	295	2267	1.91
Streams AQ: qmn coordinator idle wait	23	43	289	12568	0.34
PING	57	14	275	4823	0.84
shared server idle wait	9	100	270	30008	0.13
SQL*Net message from client	14	0	0	2	0.21

Back to Wait Events Statistics
Back to Top

Wait Event Histogram

Units for Total Waits column: K is 1000, M is 1000000, G is 1000000000
% of Waits: value of .0 indicates value was <.05%. Value of null is truly 0
% of Waits: column heading of <=1s is truly <1024ms, >1s is truly >=1024ms
Ordered by Event (idle events last)

Event	Total Waits	<1ms	<2ms	<4ms	<8ms	<16ms	<32ms	<=1s	>1s
		% of Waits
ASM file metadata operation	474	80.2	5.1	4.6	1.7	.4	3.2	4.6	.2
CGS wait for IPC msg	2332	100.0
DFS lock handle	331	88.8	2.4	3.6	1.2			3.9
IPC send completion sync	1853	97.9	.9	.6	.4	.1	.1
KJC: Wait for msg sends to complete	8	100.0
LGWR wait for redo copy	2	100.0
PX Deq: Signal ACK EXT	58	82.8	3.4	5.2	6.9	1.7
PX Deq: Signal ACK RSG	102	81.4	3.9	2.9	2.0	1.0	3.9	4.9
PX Deq: Slave Session Stats	512	74.6	10.2	6.6	2.7	1.0	2.9	2.0
PX Deq: reap credit	15K	99.8	.1	.1	.0	.0	.0	.0
PX qref latch	23M	100.0	.0	.0	.0	.0
SQL*Net break/reset to client	80	85.0	5.0	6.3		1.3		2.5
SQL*Net message to client	552	100.0
SQL*Net more data from client	10	90.0	10.0
SQL*Net more data to client	3	100.0
buffer busy waits	18K	42.2	19.9	15.6	14.8	6.6	.9	.0
cell multiblock physical read	3	66.7			33.3
cell single block physical read	107	54.2	.9	9.3	29.0	5.6	.9
cell smart table scan	14K	53.7	18.4	14.7	6.2	3.7	1.0	2.4
control file parallel write	106	42.5	14.2	20.8	12.3	1.9	.9	7.5
control file sequential read	2149	83.0	6.2	4.5	2.9	.7	.5	2.3
db file parallel read	1							100.0
db file parallel write	7288	88.3	8.9	2.1	.5	.1	.0	.0
direct path read	3	66.7				33.3
direct path read temp	186K	14.4	6.7	22.2	36.9	18.4	1.1	.2
direct path write	1	100.0
direct path write temp	186K	63.3	3.2	12.0	17.5	4.0	.0	.0
enq: AF - task serialization	2	100.0
enq: CF - contention	13	84.6		7.7	7.7
enq: FB - contention	5	100.0
enq: HW - contention	2	100.0
enq: JQ - contention	2	50.0		50.0
enq: KO - fast object checkpoint	4	100.0
enq: PI - contention	10	70.0	20.0		10.0
enq: PS - contention	1843	86.7	5.7	3.6	1.7	.6	.5	1.1
enq: PV - syncstart	15						6.7	93.3
enq: RO - fast object reuse	23	73.9	4.3	13.0	8.7
enq: TA - contention	2	100.0
enq: TD - KTF dump entries	1			100.0
enq: TM - contention	2	50.0			50.0
enq: TO - contention	4	50.0	25.0		25.0
enq: TQ - TM contention	1	100.0
enq: TT - contention	1	100.0
enq: TX - index contention	1	100.0
enq: WF - contention	20	75.0			5.0		5.0	15.0
enq: XL - fault extent map	2					50.0	50.0
gc buffer busy acquire	21	57.1				42.9
gc cr block 2-way	438	94.5	1.6	3.4	.2	.2
gc cr block 3-way	312	96.8	2.6		.3	.3
gc cr block busy	6	100.0
gc cr grant 2-way	43	88.4	7.0	2.3	2.3
gc cr multi block request	185	99.5	.5
gc current block 2-way	154	97.4	1.3	.6	.6
gc current block 3-way	320	96.9	1.9	.9	.3
gc current block busy	15	80.0	20.0
gc current grant 2-way	30	100.0
gc current grant busy	62	100.0
gc current multi block request	7	100.0
gcs log flush sync	57	96.5	1.8		1.8
ksxr poll remote instances	2177	100.0
latch free	325	54.5	6.2	7.4	16.9	14.5	.6
latch: cache buffers chains	206	99.0	1.0
latch: enqueue hash chains	28	100.0
latch: ges resource hash list	191	89.5	5.8	2.6	2.1
latch: parallel query alloc buffer	8	100.0
latch: session allocation	46	41.3	34.8	23.9
latch: shared pool	89	62.9	15.7	9.0		3.4		9.0
library cache lock	211	88.6	4.7	1.9	3.8			.9
library cache pin	287	88.5	6.3	2.8	1.4	.7	.3
library cache: mutex X	36	100.0
local write wait	9366	82.1	13.5	3.8	.5	.1
log file parallel write	102	84.3	8.8	4.9	1.0			1.0
log file sync	3	33.3		33.3				33.3
name-service call wait	5							100.0
os thread startup	16					43.8	37.5	18.8
rdbms ipc reply	551	92.9	4.2	2.4	.5
reliable message	736	88.0	5.2	3.1	2.0	.7	.1	.8
row cache lock	302	81.8	11.9	3.6	2.0	.3		.3
ASM background timer	130	8.5	2.3	1.5	3.8	10.0	12.3	13.8	47.7
DIAG idle wait	13K	20.4	1.1	1.1	1.4	2.3	37.9	35.8
KSV master wait	242	81.0		.8	1.7	2.9	5.0	8.7
PING	57	78.9	1.8						19.3
PX Deq Credit: need buffer	29K	36.6	10.2	17.1	19.5	12.9	3.3	.5
PX Deq Credit: send blkd	265K	12.8	8.0	10.7	12.5	42.4	13.1	.4	.0
PX Deq: Execute Reply	1151	28.3	3.6	5.5	7.0	8.3	9.6	27.4	10.3
PX Deq: Execution Msg	3040	44.5	9.5	9.8	6.2	3.9	.9	3.7	21.5
PX Deq: Join ACK	611	86.9	7.5	3.8	1.3	.3		.2
PX Deq: Msg Fragment	6	100.0
PX Deq: Parse Reply	340	81.5	7.6	3.5	2.4	2.1	.6	2.4
PX Deq: Table Q Normal	1395K	50.4	22.1	21.6	5.1	.6	.1	.0
PX Idle Wait	4346			.3	.9	.3	.5	4.3	93.8
SQL*Net message from client	552	66.3	9.6	4.2	2.0	.5	.4	.9	16.1
Space Manager: slave idle wait	60								100.0
Streams AQ: RAC qmn coordinator idle wait	24	100.0
Streams AQ: qmn coordinator idle wait	23	47.8	8.7						43.5
Streams AQ: qmn slave idle wait	31	35.5							64.5
Streams AQ: waiting for messages in the queue	89							33.7	66.3
Streams AQ: waiting for time management or cleanup tasks	5	20.0						40.0	40.0
class slave wait	1082							.5	99.5
dispatcher timer	5								100.0
fbar timer	1								100.0
gcs remote message	16K	7.4	1.8	1.6	1.4	1.5	2.5	83.8
ges remote message	11K	47.6	5.4	4.4	4.5	4.0	4.7	29.2
jobq slave wait	20								100.0
pmon timer	117	13.7				.9			85.5
rdbms ipc message	17K	55.8	3.2	2.3	1.5	1.2	1.0	27.4	7.5
shared server idle wait	9								100.0
smon timer	59								100.0
wait for unread message on broadcast channel	594							99.7	.3

Back to Wait Events Statistics
Back to Top

Service Statistics

ordered by DB Time

Service Name	DB Time (s)	DB CPU (s)	Physical Reads (K)	Logical Reads (K)
D_ONE	3,132	1,434	13,420	7,656
SYS$USERS	15	4	0	30
SYS$BACKGROUND	0	0	0	1
DEMO	0	0	0	0
D1	0	0	0	0
DEMOXDB	0	0	0	0
D_EIGHT	0	0	0	0
D_FOUR	0	0	0	0
D_TWO	0	0	0	0

Back to Wait Events Statistics
Back to Top

Service Wait Class Stats

Wait Class info for services in the Service Statistics section.
Total Waits and Time Waited displayed for the following wait classes: User I/O, Concurrency, Administrative, Network
Time Waited (Wt Time) in seconds

Service Name	User I/O Total Wts	User I/O Wt Time	Concurcy Total Wts	Concurcy Wt Time	Network Total Wts
D_ONE	397180	1423	19080	49	3
SYS$USERS	66	0	465	1	489
SYS$BACKGROUND	0	0	30	0	0
DEMO	0	0	5	0	60
D1	0	0	0	0	0

Back to Wait Events Statistics
Back to Top

SQL Statistics

SQL ordered by Elapsed Time
SQL ordered by CPU Time
SQL ordered by Gets
SQL ordered by Reads
SQL ordered by Executions
SQL ordered by Parse Calls
SQL ordered by Sharable Memory
SQL ordered by Version Count
SQL ordered by Cluster Wait Time
Complete List of SQL Text

SQL ordered by Elapsed Time

Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
% Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100
Total DB Time (s): 3,146
Captured SQL account for 97.2% of Total
Total DB Time (s): 3,146
Captured PL/SQL account for 0.2% of Total

Elapsed Time (s)	CPU Time (s)	Executions	Elap per Exec (s)	% Total DB Time	SQL Id	SQL Module	SQL Text
3,043	1,416	1	3042.96	96.73	a6r9zzu06tudh	SQL*Plus	select /* one / /+ parallel ...
5	1	1	5.07	0.16	7yh8hrc363643	sqlplus@host101.us.oracle.com (TNS V1-V3)	select dbms_sqltune.report_s...
4	2	1	4.24	0.13	1uk5m5qbzj1vt	SQL*Plus	BEGIN dbms_workload_repository...
2	0	1	2.34	0.07	64th2cn1fhq5r	sqlplus@host101.us.oracle.com (TNS V1-V3)	SELECT /+ no_monitor / MO3.S...
2	1	1	1.65	0.05	9550qrptt8fu2	sqlplus@host101.us.oracle.com (TNS V1-V3)	WITH MONITOR_DATA AS (SELECT I...
1	0	30	0.05	0.04	7frqszzdu8sgg	racgimon@host101.us.oracle.com (TNS V1-V3)	select DECODE(UPPER(d.OPEN_MOD...
1	0	40	0.02	0.02	fgqt3gak6vv8x	perl@host101.us.oracle.com (TNS V1-V3)	SELECT instance_name ...
0	0	16	0.02	0.01	arx2rgss6k33m	perl@host101.us.oracle.com (TNS V1-V3)	SELECT s.inst_id, s.name, i...
0	0	1	0.37	0.01	bunssq950snhf		insert into wrh$_sga_target_ad...
0	0	1	0.35	0.01	6ajkhukk78nsr		begin prvt_hdm.auto_execute( :...

Back to SQL Statistics
Back to Top

SQL ordered by CPU Time

Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
% Total is the CPU Time divided into the Total CPU Time times 100
Total CPU Time (s): 1,437
Captured SQL account for 98.7% of Total
Total CPU Time (s): 1,437
Captured PL/SQL account for 0.2% of Total

CPU Time (s)	Elapsed Time (s)	Executions	CPU per Exec (s)	% Total	% Total DB Time	SQL Id	SQL Module	SQL Text
1,416	3,043	1	1415.54	98.51	96.73	a6r9zzu06tudh	SQL*Plus	select /* one / /+ parallel ...
2	4	1	1.76	0.12	0.13	1uk5m5qbzj1vt	SQL*Plus	BEGIN dbms_workload_repository...
1	2	1	0.72	0.05	0.05	9550qrptt8fu2	sqlplus@host101.us.oracle.com (TNS V1-V3)	WITH MONITOR_DATA AS (SELECT I...
1	5	1	0.55	0.04	0.16	7yh8hrc363643	sqlplus@host101.us.oracle.com (TNS V1-V3)	select dbms_sqltune.report_s...
0	0	1	0.37	0.03	0.01	bunssq950snhf		insert into wrh$_sga_target_ad...
0	1	30	0.01	0.02	0.04	7frqszzdu8sgg	racgimon@host101.us.oracle.com (TNS V1-V3)	select DECODE(UPPER(d.OPEN_MOD...
0	0	1	0.26	0.02	0.01	6ajkhukk78nsr		begin prvt_hdm.auto_execute( :...
0	0	1	0.14	0.01	0.01	88juhgrgf4zqa	emagent@host101.us.oracle.com (TNS V1-V3)	SELECT DISTINCT :B1 TASK_ID, ...
0	1	40	0.00	0.01	0.02	fgqt3gak6vv8x	perl@host101.us.oracle.com (TNS V1-V3)	SELECT instance_name ...
0	0	1	0.12	0.01	0.00	01nfcnq0mymcu	sqlplus@host101.us.oracle.com (TNS V1-V3)	select /*+ BDAGEVIL leadin...

Back to SQL Statistics
Back to Top

SQL ordered by Gets

Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
Total Buffer Gets: 7,674,748
Captured SQL account for 100.0% of Total

Buffer Gets	Executions	Gets per Exec	%Total	CPU Time (s)	Elapsed Time (s)	SQL Id	SQL Module	SQL Text
7,630,868	1	7,630,868.00	99.43	1415.54	3042.96	a6r9zzu06tudh	SQL*Plus	select /* one / /+ parallel ...
23,356	1	23,356.00	0.30	1.76	4.24	1uk5m5qbzj1vt	SQL*Plus	BEGIN dbms_workload_repository...
18,191	1	18,191.00	0.24	0.55	5.07	7yh8hrc363643	sqlplus@host101.us.oracle.com (TNS V1-V3)	select dbms_sqltune.report_s...
7,138	1	7,138.00	0.09	0.06	0.06	7qjhf5dzmazsr		SELECT snap_id , OBJ#, DATAO...
7,099	1	7,099.00	0.09	0.26	0.35	6ajkhukk78nsr		begin prvt_hdm.auto_execute( :...
5,641	1	5,641.00	0.07	0.08	0.09	7uqmyd619pj1a	emagent@host101.us.oracle.com (TNS V1-V3)	/* OracleOEM */ DECLARE ...
5,636	1	5,636.00	0.07	0.07	0.06	g12bmbazvj86x	emagent@host101.us.oracle.com (TNS V1-V3)	SELECT TASK_LIST.TASK_ID FROM ...
2,141	1	2,141.00	0.03	0.72	1.65	9550qrptt8fu2	sqlplus@host101.us.oracle.com (TNS V1-V3)	WITH MONITOR_DATA AS (SELECT I...
1,102	1	1,102.00	0.01	0.14	0.19	88juhgrgf4zqa	emagent@host101.us.oracle.com (TNS V1-V3)	SELECT DISTINCT :B1 TASK_ID, ...
914	64	14.28	0.01	0.01	0.08	32hbap2vtmf53		select position#, sequence#, l...

Back to SQL Statistics
Back to Top

SQL ordered by Reads

Total Disk Reads: 13,420,432
Captured SQL account for 99.9% of Total

Physical Reads	Executions	Reads per Exec	%Total	CPU Time (s)	Elapsed Time (s)	SQL Id	SQL Module	SQL Text
13,409,423	1	13,409,423.00	99.92	1415.54	3042.96	a6r9zzu06tudh	SQL*Plus	select /* one / /+ parallel ...
72	1	72.00	0.00	0.55	5.07	7yh8hrc363643	sqlplus@host101.us.oracle.com (TNS V1-V3)	select dbms_sqltune.report_s...
25	1	25.00	0.00	0.72	1.65	9550qrptt8fu2	sqlplus@host101.us.oracle.com (TNS V1-V3)	WITH MONITOR_DATA AS (SELECT I...
11	1	11.00	0.00	1.76	4.24	1uk5m5qbzj1vt	SQL*Plus	BEGIN dbms_workload_repository...
10	14	0.71	0.00	0.01	0.08	39m4sx9k63ba2		select /*+ index(idl_ub2$ i_id...
8	14	0.57	0.00	0.01	0.09	cvn54b7yz0s8u		select /*+ index(idl_ub1$ i_id...
7	64	0.11	0.00	0.01	0.08	32hbap2vtmf53		select position#, sequence#, l...
7	2	3.50	0.00	0.02	0.09	d1dumhajv2rr5	sqlplus@host101.us.oracle.com (TNS V1-V3)	SELECT COMPONENT_ID FROM SYS. ...
2	1	2.00	0.00	0.26	0.35	6ajkhukk78nsr		begin prvt_hdm.auto_execute( :...
1	1	1.00	0.00	0.04	0.05	084n00125zh7c		SELECT count(*) over () as to...

Back to SQL Statistics
Back to Top

SQL ordered by Executions

Total Executions: 2,574
Captured SQL account for 28.2% of Total

Executions	Rows Processed	Rows per Exec	CPU per Exec (s)	Elap per Exec (s)	SQL Id	SQL Module	SQL Text
69	69	1.00	0.00	0.00	3m8smr0v7v1m6		INSERT INTO sys.wri$_adv_messa...
64	393	6.14	0.00	0.00	32hbap2vtmf53		select position#, sequence#, l...
59	59	1.00	0.00	0.00	f80h0xb1qvbsk		SELECT sys.wri$_adv_seq_msggro...
46	46	1.00	0.00	0.00	9tgj4g8y4rwy8		select type#, blocks, extents,...
40	40	1.00	0.00	0.02	fgqt3gak6vv8x	perl@host101.us.oracle.com (TNS V1-V3)	SELECT instance_name ...
30	30	1.00	0.01	0.05	7frqszzdu8sgg	racgimon@host101.us.oracle.com (TNS V1-V3)	select DECODE(UPPER(d.OPEN_MOD...
22	22	1.00	0.00	0.00	grwydz59pu6mc		select text from view$ where r...
21	99	4.71	0.00	0.00	3ktacv9r56b51		select owner#, name, namespace...
21	78	3.71	0.00	0.00	8swypbbr0m372		select order#, columns, types ...
19	110	5.79	0.00	0.00	83taa7kaw59c1		select name, intcol#, segcol#,...

Back to SQL Statistics
Back to Top

SQL ordered by Parse Calls

Total Parse Calls: 1,616
Captured SQL account for 57.3% of Total

Parse Calls	Executions	% Total Parses	SQL Id	SQL Module	SQL Text
236	30	14.60	7frqszzdu8sgg	racgimon@host101.us.oracle.com (TNS V1-V3)	select DECODE(UPPER(d.OPEN_MOD...
69	69	4.27	3m8smr0v7v1m6		INSERT INTO sys.wri$_adv_messa...
59	59	3.65	f80h0xb1qvbsk		SELECT sys.wri$_adv_seq_msggro...
56	40	3.47	fgqt3gak6vv8x	perl@host101.us.oracle.com (TNS V1-V3)	SELECT instance_name ...
48	16	2.97	arx2rgss6k33m	perl@host101.us.oracle.com (TNS V1-V3)	SELECT s.inst_id, s.name, i...
46	46	2.85	9tgj4g8y4rwy8		select type#, blocks, extents,...
33	1	2.04	a6r9zzu06tudh	SQL*Plus	select /* one / /+ parallel ...
32	16	1.98	aqb79nn78dgyy	perl@host101.us.oracle.com (TNS V1-V3)	SELECT inst_id, service_name...
24	8	1.49	1mjhyad05m8pf	perl@host101.us.oracle.com (TNS V1-V3)	SELECT value ...
24	8	1.49	8j37z6cqf3s9h	perl@host101.us.oracle.com (TNS V1-V3)	SELECT value ...
22	22	1.36	grwydz59pu6mc		select text from view$ where r...
21	21	1.30	3ktacv9r56b51		select owner#, name, namespace...
21	21	1.30	8swypbbr0m372		select order#, columns, types ...

Back to SQL Statistics
Back to Top

SQL ordered by Sharable Memory

Only Statements with Sharable Memory greater than 1048576 are displayed

Sharable Mem (b)	Executions	% Total	SQL Id	SQL Module	SQL Text
8,008,714	1	0.70	gxxa073u093s4	emagent@host101.us.oracle.com (TNS V1-V3)	/* OracleOEM */ SELECT PROPA...
8,008,712	1	0.70	bvf3fxv3hatw7	emagent@host101.us.oracle.com (TNS V1-V3)	/* OracleOEM */ SELECT PROPA...
5,964,733	1	0.52	9550qrptt8fu2	sqlplus@host101.us.oracle.com (TNS V1-V3)	WITH MONITOR_DATA AS (SELECT I...
4,223,692	1	0.37	40u9awsftkwn4	emagent@host101.us.oracle.com (TNS V1-V3)	SELECT PROPAGATION_NAME, QUEUE...
4,199,446	1	0.37	fyddnrs5cctsu	emagent@host101.us.oracle.com (TNS V1-V3)	/* OracleOEM */ SELECT PROPA...
4,071,121	1	0.36	cxjqbfn0d3yqq	emagent@host101.us.oracle.com (TNS V1-V3)	SELECT COUNT(*) FROM SYS.DBA_P...
3,998,162	1	0.35	05xcf43d9psvm	emagent@host101.us.oracle.com (TNS V1-V3)	SELECT NVL(SUM(FAILURES), 0) ...

Back to SQL Statistics
Back to Top

SQL ordered by Version Count

No data exists for this section of the report.

Back to SQL Statistics
Back to Top

SQL ordered by Cluster Wait Time

%Ela is Cluster Wait Time as percentage of SQL Elapsed Time
%Total is Cluster Wait Time as percentage of Total Cluster Wait Time
Only SQL with Cluster Wait Time > .005 seconds is reported
Total Cluster Wait Time (s): 1
Captured Segments account for 55.9% of Total

Cluster Wait Time (s)	%Ela	%Total	Elapsed Time(s)	CPU Time(s)	Executions	SQL Id	SQL Module	SQL Text
0.20	4.65	37.35	4.24	1.76	1	1uk5m5qbzj1vt	SQL*Plus	BEGIN dbms_workload_repository...
0.10	0.00	18.94	3,042.96	1,415.54	1	a6r9zzu06tudh	SQL*Plus	select /* one / /+ parallel ...
0.07	21.27	14.17	0.35	0.26	1	6ajkhukk78nsr		begin prvt_hdm.auto_execute( :...
0.07	1.39	13.32	5.07	0.55	1	7yh8hrc363643	sqlplus@host101.us.oracle.com (TNS V1-V3)	select dbms_sqltune.report_s...
0.03	13.50	4.92	0.19	0.14	1	88juhgrgf4zqa	emagent@host101.us.oracle.com (TNS V1-V3)	SELECT DISTINCT :B1 TASK_ID, ...
0.02	14.44	3.27	0.12	0.05	1	6gvch1xu9ca3g		DECLARE job BINARY_INTEGER := ...
0.02	29.04	2.93	0.05	0.04	1	084n00125zh7c		SELECT count(*) over () as to...
0.01	15.53	2.69	0.09	0.01	14	cvn54b7yz0s8u		select /*+ index(idl_ub1$ i_id...
0.01	0.77	2.41	1.65	0.72	1	9550qrptt8fu2	sqlplus@host101.us.oracle.com (TNS V1-V3)	WITH MONITOR_DATA AS (SELECT I...
0.01	13.59	2.31	0.09	0.08	1	7uqmyd619pj1a	emagent@host101.us.oracle.com (TNS V1-V3)	/* OracleOEM */ DECLARE ...
0.01	17.02	2.08	0.06	0.07	1	g12bmbazvj86x	emagent@host101.us.oracle.com (TNS V1-V3)	SELECT TASK_LIST.TASK_ID FROM ...
0.01	72.35	1.73	0.01	0.00	4	18naypzfmabd6	OEM.BoundedPool	INSERT INTO MGMT_SYSTEM_PERFOR...
0.01	6.36	0.95	0.08	0.01	14	39m4sx9k63ba2		select /*+ index(idl_ub2$ i_id...

Back to SQL Statistics
Back to Top

Complete List of SQL Text

SQL Id SQL Text

01nfcnq0mymcu select /*+ BDAGEVIL leading(se) */ sql_id||decode(child_number, 0, '', '/'||child_number) sql_id, users_executing exec, sql_text from v$sql s WHERE 1=1 and s.users_executing > 0 and s.sql_text not like '%BDAGEVIL%'

05xcf43d9psvm SELECT NVL(SUM(FAILURES), 0) FROM SYS.DBA_QUEUE_SCHEDULES

084n00125zh7c SELECT count(*) over () as total_count, sd_xe_ash_nm.event_name, sd_xe_ash_nm.event_id, sd_xe_ash_nm.parameter1 as p1text, (CASE WHEN (sd_xe_ash_nm.parameter1 is NULL OR sd_xe_ash_nm.parameter1 = '0') THEN 0 ELSE 1 END) as p1valid, sd_xe_ash_nm.parameter2 as p2text, (CASE WHEN (sd_xe_ash_nm.parameter2 is NULL OR sd_xe_ash_nm.parameter2 = '0') THEN 0 ELSE 1 END) as p2valid, sd_xe_ash_nm.parameter3 as p3text, (CASE WHEN (sd_xe_ash_nm.parameter3 is NULL OR sd_xe_ash_nm.parameter3 = '0') THEN 0 ELSE 1 END) as p3valid, sd_xe_ash_nm.keh_evt_id, nvl(xc.class#, 0) as class_num, sd_xe_ash_nm.wait_class_id, nvl(xc.keh_id, 0) as keh_ecl_id, sd_xe_ash_nm.ash_cnt, sd_xe_ash_nm.fg_cnt, sd_xe_ash_nm.fg_wts_diff, sd_xe_ash_nm.fg_tmo_diff, sd_xe_ash_nm.fg_tim_wait_diff, sd_xe_ash_nm.tot_wts_diff, sd_xe_ash_nm.tot_tmo_diff, sd_xe_ash_nm.tot_tim_wait_diff FROM ( SELECT sd_xe_ash.*, evtname.event_name, evtname.wait_class_id, evtname.parameter1, evtname.parameter2, evtname.parameter3 FROM ( SELECT sd_xe.*, nvl(ash.cnt, 0) as ash_cnt, nvl(ash.fg_cnt, 0) as fg_cnt FROM ( SELECT nvl(xe.k eh_id, 0) as keh_evt_id, nvl(sd.event_id, xe.event_hash) as event_id, nvl(sd.fg_wts_diff, 0) as fg_wts_diff, nvl(sd.fg_tmo_diff, 0) as fg_tmo_diff, nvl(sd.fg_tim_wait_diff, 0) as fg_tim_wait_diff, nvl(sd.tot_wts_diff, 0) as tot_wts_diff, nvl(sd.tot_tmo_diff, 0) as tot_tmo_diff, nvl(sd.tot_tim_wait_diff, 0) as tot_tim_wait_diff FROM ( SELECT endsn.event_id as event_id, sum(endsn.total_waits_fg - nvl(begsn.total_waits_fg, 0)) as fg_wts_diff, sum(endsn.total_timeouts_fg - nvl(begsn.total_timeouts_fg, 0)) as fg_tmo_diff, sum(endsn.time_waited_micro_fg - nvl(begsn.time_waited_micro_fg, 0)) as fg_tim_wait_diff, sum(endsn.total_waits - nvl(begsn.total_waits, 0)) as tot_wts_diff, sum(endsn.total_timeouts - nvl(begsn.total_timeouts, 0)) as tot_tmo_diff, sum(endsn.time_waited_micro - nvl(begsn.time_waited_micro, 0)) as tot_tim_wait_diff FROM ( SELECT * FROM WRH$_SYSTEM_EVENT esi WHERE esi.dbid = :dbid AND esi.snap_id = :eid ) endsn LEFT OUTER JOIN ( SELECT * FROM WRH$_SYSTEM_EVENT bsi WHERE bsi.dbid = :dbid AND bsi.snap_id = :bid ) begsn ON (endsn.event_id = begsn.event_id AND endsn.instance_number = begsn.instance_number) GROUP BY endsn.event_id ) sd FULL OUTER JOIN X$KEHEVTMAP xe ON sd.event_id = xe.event_hash ) sd_xe LEFT OUTER JOIN (SELECT a.event_id, count(*) as cnt, sum(decode(a.session_type, 1, 1, 0)) as fg_cnt FROM WRH$_ACTIVE_SESSION_HISTORY a WHERE a.dbid = :dbid AND a.snap_id > :bid AND a.snap_id <= :eid and a.wait_time = 0 GROUP BY a.event_id) ash ON sd_xe.event_id = ash.event_id ) sd_xe_ash, WRH$_EVENT_NAME evtname WHERE evtname.event_id = sd_xe_ash.event_id and evtname.event_id > 0 and evtname.dbid = :dbid ) sd_xe_ash_nm, X$KEHECLMAP xc WHERE sd_xe_ash_nm.wait_class_id = xc.class_hash ORDER BY sd_xe_ash_nm.wait_class_id, sd_xe_ash_nm.fg_tim_wait_diff DESC, sd_xe_ash_nm.event_id

18naypzfmabd6 INSERT INTO MGMT_SYSTEM_PERFORMANCE_LOG (JOB_NAME, TIME, DURATION, MODULE, ACTION, IS_TOTAL, NAME, VALUE, CLIENT_DATA, HOST_URL) VALUES (:B9 , SYSDATE, :B8 , SUBSTR(:B7 , 1, 512), SUBSTR(:B6 , 1, 32), :B5 , SUBSTR(:B4 , 1, 128), SUBSTR(:B3 , 1, 128), SUBSTR(:B2 , 1, 128), SUBSTR(:B1 , 1, 256))

1mjhyad05m8pf SELECT value FROM gv$osstat WHERE inst_id = :p1 and stat_name='NUM_CPU_CORES'

1uk5m5qbzj1vt BEGIN dbms_workload_repository.create_snapshot; END;

32hbap2vtmf53 select position#, sequence#, level#, argument, type#, charsetid, charsetform, properties, nvl(length, 0), nvl(precision#, 0), nvl(scale, 0), nvl(radix, 0), type_owner, type_name, type_subname, type_linkname, pls_type from argument$ where obj#=:1 and procedure#=:2 order by sequence# desc

39m4sx9k63ba2 select /*+ index(idl_ub2$ i_idl_ub21) +*/ piece#, length, piece from idl_ub2$ where obj#=:1 and part=:2 and version=:3 order by piece#

3ktacv9r56b51 select owner#, name, namespace, remoteowner, linkname, p_timestamp, p_obj#, nvl(property, 0), subname, type#, d_attrs from dependency$ d, obj$ o where d_obj#=:1 and p_obj#=obj#(+) order by order#

3m8smr0v7v1m6 INSERT INTO sys.wri$_adv_message_groups (task_id, id, seq, message#, fac, hdr, lm, nl, p1, p2, p3, p4, p5) VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13)

40u9awsftkwn4 SELECT PROPAGATION_NAME, QUEUE_TABLE, SOURCE_QUEUE_OWNER, SOURCE_QUEUE_NAME FROM DBA_QUEUES, DBA_PROPAGATION WHERE OWNER=SOURCE_QUEUE_OWNER AND SOURCE_QUEUE_NAME=NAME

64th2cn1fhq5r SELECT /*+ no_monitor */ MO3.SQL_ID, MO3.SQL_EXEC_START, MO3.SQL_EXEC_ID, MO3.SQL_PLAN_HASH_VALUE, MAX(MO3.STATUS) STATUS, MAX(CASE WHEN MO3.PX_QCSID IS NULL THEN INST_ID ELSE NULL END) INSTANCE_ID, MAX(CASE WHEN MO3.PX_QCSID IS NULL THEN MO3.SID ELSE NULL END) SESSION_ID, MAX(CASE WHEN MO3.PX_QCSID IS NULL THEN MO3.SESSION_SERIAL# ELSE NULL END) SESSION_SERIAL, MAX(MO3.FIRST_REFRESH_TIME) FIRST_REFRESH_TIME, MAX(MO3.LAST_REFRESH_TIME) LAST_REFRESH_TIME, SUM(MO3.REFRESH_COUNT) REFRESH_COUNT, MAX(MO3.PX_QCINST_ID) QC_INST_ID, MAX(MO3.PX_QCSID) QC_SESSION_ID, COUNT(DISTINCT MO3.INST_ID) INST_COUNT, COUNT(DISTINCT MO3.PX_SERVER_GROUP) SERVER_GROUP_COUNT, MAX(MO3.PX_SERVER_SET) SERVER_SET_COUNT, MAX(CASE WHEN MO3.PX_SERVER_GROUP = 1 AND MO3.PX_SERVER_SET = 1 THEN MO3.PX_SERVER# ELSE NULL END) DOP, SUM(NVL2(MO3.PX_FILTER, MO3.FIXED_ELAPSED_TIME, NULL)) ELAPSED_TIME, SUM(NVL2(MO3.PX_FILTER, MO3.CPU_TIME, NULL)) CPU_TIME, SUM(NVL2(MO3.PX_FILTER, MO3.FETCHES, NULL)) FETCHES, SUM(NVL2(MO3.PX_FILTER, MO3.BUFFER_GETS, NULL)) BUFFER_GETS, SUM(NVL2(MO3.PX_FILTER, MO3.DISK_READS, NULL)) DISK_READS, SUM(NVL2(MO3.PX_FILTER, MO3.DIRECT_WRITES, NULL)) DIRECT_WRITES, SUM(NVL2(MO3.PX_FILTER, MO3.APPLICATION_WAIT_TIME, NULL)) APPLICATION_WAIT_TIME, SUM(NVL2(MO3.PX_FILTER, MO3.CONCURRENCY_WAIT_TIME, NULL)) CONCURRENCY_WAIT_TIME, SUM(NVL2(MO3.PX_FILTER, MO3.CLUSTER_WAIT_TIME, NULL)) CLUSTER_WAIT_TIME, SUM(NVL2(MO3.PX_FILTER, MO3.USER_IO_WAIT_TIME, NULL) ) USER_IO_WAIT_TIME, SUM(NVL2(MO3.PX_FILTER, MO3.PLSQL_EXEC_TIME, NULL)) PLSQL_EXEC_TIME, SUM(NVL2(MO3.PX_FILTER, MO3.JAVA_EXEC_TIME, NULL)) JAVA_EXEC_TIME, SUM(NVL2(MO3.PX_FILTER, MO3.OTHER_WAIT_TIME, NULL)) OTHER_WAIT_TIME FROM (SELECT MO1.*, CASE WHEN (NVL(CPU_TIME, 0)+ NVL(APPLICATION_WAIT_TIME, 0)+ NVL(CONCURRENCY_WAIT_TIME, 0)+ NVL(CLUSTER_WAIT_TIME, 0)+ NVL(USER_IO_WAIT_TIME, 0)) > NVL(ELAPSED_TIME, 0) THEN (NVL(CPU_TIME, 0)+ NVL(APPLICATION_WAIT_TIME, 0)+ NVL(CONCURRENCY_WAIT_TIME, 0)+ NVL(CLUSTER_WAIT_TIME, 0)+ NVL(USER_IO_WAIT_TIME, 0)) ELSE ELAPSED_TIME END FIXED_ELAPSED_TIME, CASE WHEN (NVL(CPU_TIME, 0)+ NVL(APPLICATION_WAIT_TIME, 0)+ NVL(CONCURRENCY_WAIT_TIME, 0)+ NVL(CLUSTER_WAIT_TIME, 0)+ NVL(USER_IO_WAIT_TIME, 0)) > NVL(ELAPSED_TIME, 0) THEN 0 ELSE ELAPSED_TIME - (NVL(CPU_TIME, 0)+ NVL(APPLICATION_WAIT_TIME, 0)+ NVL(CONCURRENCY_WAIT_TIME, 0)+ NVL(CLUSTER_WAIT_TIME, 0)+ NVL(USER_IO_WAIT_TIME, 0)) END OTHER_WAIT_TIME, CASE WHEN ((:B10 = 1 AND MO1.PX_QCSID IS NULL) OR ((MO1.PX_SERVER_GROUP = :B9 OR :B9 IS NULL) AND (MO1.PX_SERVER_SET = :B8 OR :B8 IS NULL) AND (MO1.PX_SERVER# = :B7 OR :B7 IS NULL))) THEN 1 ELSE NULL END PX_FILTER FROM GV$SQL_MONITOR MO1, (SELECT DISTINCT MO2.SQL_ID, MO2.SQL_EXEC_START, MO2.SQL_EXEC_ID FROM GV$SQL_MONITOR MO2, (SELECT MAX(MOD(MO3.KEY, 4294967296)) MAX_ENTRY_NUMBER FROM GV$SQL_MONITOR MO3 WHERE MO3.PX_QCSID IS NULL AND MO3.INST_ID = NVL(:B6 , MO3.INST_ID) AND MO3.SQL_ID = NVL(:B5 , MO3.SQL_ ID) AND MO3.SID = NVL(:B4 , MO3.SID) AND MO3.SESSION_SERIAL# = NVL(:B3 , MO3.SESSION_SERIAL#) AND MO3.SQL_EXEC_START = NVL(:B2 , MO3.SQL_EXEC_START) AND MO3.SQL_EXEC_ID = NVL(:B1 , MO3.SQL_EXEC_ID)) MO_LAST WHERE MO2.PX_QCSID IS NULL AND MO2.INST_ID = NVL(:B6 , MO2.INST_ID) AND MO2.SQL_ID = NVL(:B5 , MO2.SQL_ID) AND MO2.SID = NVL(:B4 , MO2.SID) AND MO2.SESSION_SERIAL# = NVL(:B3 , MO2.SESSION_SERIAL#) AND MO2.SQL_EXEC_START = NVL(:B2 , MO2.SQL_EXEC_START) AND MO2.SQL_EXEC_ID = NVL(:B1 , MO2.SQL_EXEC_ID) AND MOD(MO2.KEY, 4294967296) = MO_LAST.MAX_ENTRY_NUMBER) MO5 WHERE MO1.SQL_ID = MO5.SQL_ID AND MO1.SQL_EXEC_START = MO5.SQL_EXEC_START AND MO1.SQL_EXEC_ID = MO5.SQL_EXEC_ID) MO3 GROUP BY MO3.SQL_ID, MO3.SQL_EXEC_START, MO3.SQL_EXEC_ID, MO3.SQL_PLAN_HASH_VALUE

6ajkhukk78nsr begin prvt_hdm.auto_execute( :dbid, :inst_num , :end_snap_id ); end;

6gvch1xu9ca3g DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN := FALSE; BEGIN EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS(); :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;

7frqszzdu8sgg select DECODE(UPPER(d.OPEN_MODE), 'READ WRITE', 'R', 'N') , DECODE(UPPER(i.STATUS), 'OPEN', 'O', 'N') into :b0, :b1 from v$database d , gv$instance i where i.INSTANCE_NAME=:b2

7qjhf5dzmazsr SELECT snap_id , OBJ#, DATAOBJ# FROM (SELECT /*+ ordered use_nl(t2) index(t2) */ t2.snap_id , t1.OBJN_KEWRSEG OBJ#, t1.OBJD_KEWRSEG DATAOBJ# FROM X$KEWRTSEGSTAT t1, WRH$_SEG_STAT_OBJ t2 WHERE t2.dbid(+) = :dbid AND t2.OBJ#(+) = t1.OBJN_KEWRSEG AND t2.DATAOBJ#(+) = t1.OBJD_KEWRSEG) WHERE nvl(snap_id, 0) < :snap_id

7uqmyd619pj1a /* OracleOEM */ DECLARE instance_number NUMBER; latest_task_id NUMBER; db_elapsed_time NUMBER; start_time VARCHAR2(1024); end_time VARCHAR2(1024); db_id NUMBER; TYPE data_cursor_type IS REF CURSOR; data_cursor data_cursor_type; CURSOR get_latest_task_id IS SELECT TASK_LIST.TASK_ID FROM (SELECT /*+ NO_MERGE(T) ORDERED */ T.TASK_ID FROM (select * from dba_advisor_tasks order by task_id desc) T, dba_advisor_parameters_proj P1, dba_advisor_parameters_proj P2 WHERE T.ADVISOR_NAME='ADDM' AND T.STATUS = 'COMPLETED' AND T.EXECUTION_START >= (sysdate - 1) AND T.HOW_CREATED = 'AUTO' AND T.TASK_ID = P1.TASK_ID AND P1.PARAMETER_NAME = 'INSTANCE' AND P1.PARAMETER_VALUE = 'UNUSED' AND T.TASK_ID = P2.TASK_ID AND P2.PARAMETER_NAME = 'DB_ID' AND P2.PARAMETER_VALUE = to_char(db_id) ORDER BY T.TASK_ID DESC) TASK_LIST WHERE ROWNUM = 1; BEGIN SELECT dbid INTO db_id from v$database; OPEN get_latest_task_id; FETCH get_latest_task_id INTO latest_task_id; CLOSE get_latest_task_id; FOR param_info IN (SELECT parameter_value, parameter_name FROM dba_advisor_parameters_proj WHERE task_id= latest_task_id AND (parameter_name='START_TIME' OR parameter_name='END_TIME' OR parameter_name='DB_ELAPSED_TIME') ORDER BY 2) LOOP IF param_info.parameter_name = 'DB_ ELAPSED_TIME' THEN db_elapsed_time:= param_info.parameter_value; ELSIF param_info.parameter_name = 'END_TIME' THEN end_time := param_info.parameter_value; ELSIF param_info.parameter_name = 'START_TIME' THEN start_time := param_info.parameter_value; END IF; END LOOP; -- open the cursor to return OPEN data_cursor FOR SELECT DISTINCT latest_task_id task_id, f.finding_id finding_id, DECODE(recInfo.type, NULL, 'Uncategorized', recInfo.type) rec_type, recInfo.recCount rec_count, f.perc_active_sess impact_pct, f.message message, TO_DATE(start_time , 'MM-DD-YYYY HH24:MI:SS') start_time, TO_DATE(end_time, 'MM-DD-YYYY HH24:MI:SS') end_time, history.findingCount findingCount, f.finding_name finding_name, f.active_sessions active_sessions, instances.instance_count instCount FROM dba_addm_findings f, (SELECT finding_id, count(r.rec_id) recCount, r.type FROM dba_advisor_recommendations r WHERE task_id=latest_task_id GROUP BY r.finding_id, r.type) recInfo, (select count(DISTINCT f_all.task_id) findingCount, f_curr.finding_name FROM (select DISTINCT finding_name from dba_advisor_findings where task_id=latest_task_id AND type<>'INFORMATION' AND type<>'WARNING' AND parent=0) f_curr, (select task_id, end_time from dba_addm_tasks where end_time>sysdate -1 AND requested_analysis='DATABASE') tasks, (SELECT DISTINCT finding_name, task_id FROM dba_adviso r_findings) f_all WHERE f_all.task_id=tasks.task_id AND f_all.finding_name=f_curr.finding_name GROUP BY f_curr.finding_name) history, (SELECT f.task_id, f.finding_id, count(b.instance_number) instance_count FROM dba_addm_fdg_breakdown b, dba_addm_findings f WHERE b.task_id= latest_task_id AND b.task_id=f.task_id AND b.finding_id =f.finding_id AND f.type<>'INFORMATION' AND f.type<>'WARNING' AND f.parent=0 GROUP BY f.finding_id, f.task_id ORDER BY f.finding_id ) instances WHERE f.task_id=latest_task_id AND f.type<>'INFORMATION' AND f.type<>'WARNING' AND f.filtered<>'Y' AND f.parent=0 AND f.finding_id=recInfo.finding_id (+) AND f.finding_name=history.finding_name(+) AND f.task_id=instances.task_id(+) AND f.finding_id=instances.finding_id (+) ORDER BY f.finding_id; :2 := data_cursor; END;

7yh8hrc363643 select dbms_sqltune.report_sql_monitor(report_level=>'+histogram' ) Monitor_report from dual

83taa7kaw59c1 select name, intcol#, segcol#, type#, length, nvl(precision#, 0), decode(type#, 2, nvl(scale, -127/*MAXSB1MINAL*/), 178, scale, 179, scale, 180, scale, 181, scale, 182, scale, 183, scale, 231, scale, 0), null$, fixedstorage, nvl(deflength, 0), default$, rowid, col#, property, nvl(charsetid, 0), nvl(charsetform, 0), spare1, spare2, nvl(spare3, 0) from col$ where obj#=:1 order by intcol#

88juhgrgf4zqa SELECT DISTINCT :B1 TASK_ID, F.FINDING_ID FINDING_ID, DECODE(RECINFO.TYPE, NULL, 'Uncategorized', RECINFO.TYPE) REC_TYPE, RECINFO.RECCOUNT REC_COUNT, F.PERC_ACTIVE_SESS IMPACT_PCT, F.MESSAGE MESSAGE, TO_DATE(:B3 , 'MM-DD-YYYY HH24:MI:SS') START_TIME, TO_DATE(:B2 , 'MM-DD-YYYY HH24:MI:SS') END_TIME, HISTORY.FINDINGCOUNT FINDINGCOUNT, F.FINDING_NAME FINDING_NAME, F.ACTIVE_SESSIONS ACTIVE_SESSIONS, INSTANCES.INSTANCE_COUNT INSTCOUNT FROM DBA_ADDM_FINDINGS F, (SELECT FINDING_ID, COUNT(R.REC_ID) RECCOUNT, R.TYPE FROM DBA_ADVISOR_RECOMMENDATIONS R WHERE TASK_ID=:B1 GROUP BY R.FINDING_ID, R.TYPE) RECINFO, (SELECT COUNT(DISTINCT F_ALL.TASK_ID) FINDINGCOUNT, F_CURR.FINDING_NAME FROM (SELECT DISTINCT FINDING_NAME FROM DBA_ADVISOR_FINDINGS WHERE TASK_ID=:B1 AND TYPE<>'INFORMATION' AND TYPE<>'WARNING' AND PARENT=0) F_CURR, (SELECT TASK_ID, END_TIME FROM DBA_ADDM_TASKS WHERE END_TIME>SYSDATE -1 AND REQUESTED_ANALYSIS='DATABASE') TASKS, (SELECT DISTINCT FINDING_NAME, TASK_ID FROM DBA_ADVISOR_FINDINGS) F_ALL WHERE F_ALL.TASK_ID=TASKS.TASK_ID AND F_ALL.FINDING_NAME=F_CURR.FINDING_NAME GROUP BY F_CURR.FINDING_NAME) HISTORY, (SELECT F.TASK_ID, F.FINDING_ID, COUNT(B.INSTANCE_NUMBER) INSTANCE_COUNT FROM DBA_ADDM_FDG_BREAKDOWN B, DBA_ADDM_FINDINGS F WHERE B.TASK_ID= :B1 AND B.TASK_ID=F.TASK_ID AND B.FINDING_ID =F.FINDING_ID AND F.TYPE<>'INFORMATION' AND F.TYPE<>'WARNING' AND F.PARENT=0 GROUP BY F.FINDING_ID, F.TASK_ID ORDER BY F.FINDING_ID ) INSTA NCES WHERE F.TASK_ID=:B1 AND F.TYPE<>'INFORMATION' AND F.TYPE<>'WARNING' AND F.FILTERED<>'Y' AND F.PARENT=0 AND F.FINDING_ID=RECINFO.FINDING_ID (+) AND F.FINDING_NAME=HISTORY.FINDING_NAME(+) AND F.TASK_ID=INSTANCES.TASK_ID(+) AND F.FINDING_ID=INSTANCES.FINDING_ID (+) ORDER BY F.FINDING_ID

8j37z6cqf3s9h SELECT value FROM gv$osstat WHERE inst_id = :p1 and stat_name='NUM_CPUS'

8swypbbr0m372 select order#, columns, types from access$ where d_obj#=:1

9550qrptt8fu2 WITH MONITOR_DATA AS (SELECT INST_ID, KEY, STATUS, FIRST_REFRESH_TIME, LAST_REFRESH_TIME, REFRESH_COUNT, PROCESS_NAME, SID, SQL_ID, SQL_EXEC_START, SQL_EXEC_ID, SQL_PLAN_HASH_VALUE, SQL_CHILD_ADDRESS, SESSION_SERIAL#, PX_SERVER#, PX_SERVER_GROUP, PX_SERVER_SET, PX_QCINST_ID, PX_QCSID, MAX(ELAPSED_TIME) OVER() MAX_ELAPSED_TIME, MAX(NVL(DIRECT_WRITES, 0) + NVL(DISK_READS, 0)) OVER() MAX_IO_COUNT, MAX(NVL(BUFFER_GETS, 0)) OVER() MAX_BUFFER_GETS, CASE WHEN ELAPSED_TIME < (CPU_TIME+ APPLICATION_WAIT_TIME+ CONCURRENCY_WAIT_TIME+ CLUSTER_WAIT_TIME+ USER_IO_WAIT_TIME) THEN (CPU_TIME+ APPLICATION_WAIT_TIME+ CONCURRENCY_WAIT_TIME+ CLUSTER_WAIT_TIME+ USER_IO_WAIT_TIME) ELSE ELAPSED_TIME END ELAPSED_TIME, CPU_TIME, APPLICATION_WAIT_TIME, CONCURRENCY_WAIT_TIME, CLUSTER_WAIT_TIME, USER_IO_WAIT_TIME, CASE WHEN ELAPSED_TIME < (CPU_TIME+ APPLICATION_WAIT_TIME+ CONCURRENCY_WAIT_TIME+ CLUSTER_WAIT_TIME+ USER_IO_WAIT_TIME) THEN 0 ELSE ELAPSED_TIME - (CPU_TIME+ APPLICATION_WAIT_TIME+ CONCURRENCY_WAIT_TIME+ CLUSTER_WAIT_TIME+ USER_IO_WAIT_TIME) END OTHER_WAIT_TIME, PLSQL_EXEC_TIME, JAVA_EXEC_TIME, FETCHES, BUFFER_GETS, DISK_READS, DIRECT_WRITES, NVL(DISK_READS, 0)+NVL(DIRECT_WRITES, 0) IO_COUNT FROM GV$SQL_MONITOR MO1 WHERE MO1.INST_ID = NVL(:B8 , MO1.INST_ID) AND MO1.SQL_ID = :B7 AND MO1.SQL_EXEC_START = :B6 AND MO1.SQL_EXEC_ID = :B5 AND ((:B4 = 1 AND MO1.PX_QCSID IS NULL) OR (MO1.PX_SERVER_GROUP = NVL(:B3 , MO1.PX_SERVER_GROUP) AND MO1.PX_SERVER_SE T = NVL(:B2 , MO1.PX_SERVER_SET) AND MO1.PX_SERVER# = NVL(:B1 , MO1.PX_SERVER#)))), ASH_DATA AS (SELECT ASH2.BUCKET_NUM, ASH2.PLAN_LINE_ID, ASH2.ACTIVITY_TYPE, ASH2.EVENT_NAME, ASH2.INST_ID, ASH2.SESSION_ID, :B13 + ((:B13 - :B12 )/ :B11 ) * (ASH2.BUCKET_NUM-1) BUCKET_ACTIVITY_START, :B13 + (((:B12 - :B13 )/ :B11 ) * (ASH2.BUCKET_NUM)) - NUMTODSINTERVAL(1, 'SECOND') BUCKET_ACTIVITY_END, ASH2.ACTIVITY_START, ASH2.ACTIVITY_END, ASH2.ACTIVITY_COUNT, ASH2.WAIT_COUNT, ASH2.PX_SERVER_SET FROM (SELECT ASH1.BUCKET_NUM, ASH1.PLAN_LINE_ID, ASH1.ACTIVITY_TYPE, ASH1.EVENT_NAME, ASH1.INST_ID, ASH1.SESSION_ID, MIN(ASH1.PX_SERVER_SET) PX_SERVER_SET, MIN(ASH1.SAMPLE_TIME) ACTIVITY_START, MAX(ASH1.SAMPLE_TIME) ACTIVITY_END, COUNT(*) ACTIVITY_COUNT, COUNT(ASH1.WAIT_CLASS) WAIT_COUNT FROM ( SELECT /*+ leading(mo) use_hash(ash0) */ CAST(ASH0.SAMPLE_TIME AS DATE) SAMPLE_TIME, CASE WHEN :B11 > 1 THEN WIDTH_BUCKET(CAST(ASH0.SAMPLE_TIME AS DATE), :B13 , :B12 + NUMTODSINTERVAL(1, 'SECOND'), :B11 ) ELSE 1 END BUCKET_NUM, ASH0.WAIT_CLASS, NVL(ASH0.WAIT_CLASS, 'Cpu') ACTIVITY_TYPE, DECODE(:B10 , 1, ASH0.EVENT, NULL) EVENT_NAME, ASH0.INST_ID, ASH0.SESSION_ID, ASH0.SQL_PLAN_LINE_ID PLAN_LINE_ID, MO.PX_SERVER_SET FROM MONITOR_DATA MO, (SELECT SQL_ID, SQL_PLAN_LINE_ID, EVENT, WAIT_CLASS, SQL_EXEC_ID, INST_ID, SESSION_ID, FROM_TZ(SAMPLE_TIME, DBTIMEZONE) SAMPLE_TIME FROM GV$ACTIVE_SESSION_HISTORY) ASH0 WHERE ASH0.SQL_ID = :B7 AND (ASH0.WAIT_CLASS IS NULL O R ASH0.WAIT_CLASS != 'Queueing') AND ASH0.SAMPLE_TIME >= :B6 - NUMTODSINTERVAL(1, 'SECOND') AND ASH0.SAMPLE_TIME <= :B9 AND ASH0.SQL_EXEC_ID = :B5 AND ASH0.INST_ID = MO.INST_ID AND ASH0.SESSION_ID = MO.SID) ASH1 WHERE ASH1.BUCKET_NUM > 0 AND ASH1.BUCKET_NUM <= :B11 GROUP BY ASH1.BUCKET_NUM, ASH1.INST_ID, ASH1.SESSION_ID, ASH1.PLAN_LINE_ID, ASH1.ACTIVITY_TYPE, ASH1.EVENT_NAME) ASH2) SELECT /*+ no_monitor */ XMLELEMENT( "sql_monitor_report", NULL, XMLELEMENT( "report_parameters", NULL, XMLFOREST( :B30 AS "sql_id", :B29 AS "session_id", :B28 AS "session_serial", TO_CHAR(:B27 , :B17 ) AS "sql_exec_start", :B26 AS "sql_exec_id", :B11 AS "bucket_count", TO_CHAR(:B13 , :B17 ) AS "interval_start", TO_CHAR(:B12 , :B17 ) AS "interval_end", :B25 AS "auto_refresh"), CASE WHEN :B15 IS NOT NULL AND :B24 IS NOT NULL AND NOT (:B4 = 1 AND :B3 IS NULL AND :B2 IS NULL AND :B1 IS NULL) THEN XMLELEMENT( "parallel_filter", NULL, XMLFOREST( DECODE(:B4 , 1, 'yes', 'no') AS "qc", :B3 AS "server_group", :B2 AS "server_set", :B1 AS "server_number")) ELSE NULL END), XMLELEMENT( "target", XMLATTRIBUTES(:B23 AS "instance_id", :B22 AS "session_id", :B21 AS "session_serial", :B7 AS "sql_id", TO_CHAR(:B6 , :B17 ) AS "sql_exec_start", :B5 AS "sql_exec_id", :B20 AS "sql_plan_hash"), XMLFOREST(XMLCDATA((SELECT SUBSTR(SQL_FULLTEXT, 1, 3900) FROM GV$SQL SQ WHERE SQ.INST_ID = :B23 AND :B31 = 1 AND SQ.SQL_ID = :B7 AND ROWNUM = 1)) AS "sql_fulltext"), XMLELEMENT ( "status", NULL, :B19 ), XMLELEMENT( "refresh_count", NULL, :B18 ), XMLELEMENT( "first_refresh_time", NULL, TO_CHAR(:B16 , :B17 )), XMLELEMENT( "last_refresh_time", NULL, TO_CHAR(:B9 , :B17 )), XMLELEMENT( "duration", NULL, ROUND((:B9 - LEAST(:B6 , :B16 )) * 3600*24 + 1))), (SELECT XMLELEMENT( "stats", XMLATTRIBUTES('monitor' AS "type"), DECODE(NVL(SUM(ELAPSED_TIME), 0), 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('elapsed_time' AS "name"), SUM(ELAPSED_TIME))), DECODE(NVL(SUM(CPU_TIME), 0), 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('cpu_time' AS "name"), SUM(CPU_TIME))), DECODE(NVL(SUM(USER_IO_WAIT_TIME), 0), 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('user_io_wait_time' AS "name"), SUM(USER_IO_WAIT_TIME))), DECODE(NVL(SUM(APPLICATION_WAIT_TIME), 0), 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('application_wait_time' AS "name"), SUM(APPLICATION_WAIT_TIME))), DECODE(NVL(SUM(CONCURRENCY_WAIT_TIME), 0), 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('concurrency_wait_time' AS "name"), SUM(CONCURRENCY_WAIT_TIME))), DECODE(NVL(SUM(CLUSTER_WAIT_TIME), 0), 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('cluster_wait_time' AS "name"), SUM(CLUSTER_WAIT_TIME))), DECODE(NVL(SUM(PLSQL_EXEC_TIME), 0), 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('plsql_exec_time' AS "name"), SUM(PLSQL_EXEC_TIME))), DECODE(NVL(SUM(JAVA_EXEC_TIME), 0), 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('java_exec_time' AS "name"), SUM(JAVA_EXEC_TIME)) ), DECODE(NVL(SUM(OTHER_WAIT_TIME), 0), 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('other_wait_time' AS "name"), SUM(OTHER_WAIT_TIME))), DECODE(NVL(SUM(FETCHES), 0), 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('user_fetch_count' AS "name"), SUM(FETCHES))), DECODE(NVL(SUM(BUFFER_GETS), 0), 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('buffer_gets' AS "name"), SUM(BUFFER_GETS))), DECODE(NVL(SUM(DISK_READS), 0), 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('disk_reads' AS "name"), SUM(DISK_READS))), DECODE(NVL(SUM(DIRECT_WRITES), 0), 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('direct_writes' AS "name"), SUM(DIRECT_WRITES)))) FROM MONITOR_DATA), NVL2(1, (SELECT CASE WHEN AT.ACTIVITY_COUNT > 0 THEN XMLELEMENT( "activity_sampled", XMLATTRIBUTES( TO_CHAR(AT.ACTIVITY_START, :B17 ) AS "start_time", TO_CHAR(AT.ACTIVITY_END, :B17 ) AS "end_time", ROUND((AT.ACTIVITY_END - AT.ACTIVITY_START) * 3600 *24)+1 AS "duration", AT.ACTIVITY_COUNT AS "activity_count", AT.WAIT_COUNT AS "wait_count", AT.ACTIVITY_COUNT - AT.WAIT_COUNT AS "cpu_count", AT.MOST_ACTIVE AS "most_active"), AT.ACTIVITY_TOTAL, DECODE(:B11 , 1, NULL, AH.ACTIVITY_HISTO)) ELSE NULL END FROM (SELECT MIN(AD1.ACTIVITY_START) ACTIVITY_START, MAX(AD1.ACTIVITY_END) ACTIVITY_END, SUM(AD1.ACTIVITY_COUNT) ACTIVITY_COUNT, SUM(AD1.WAIT_COUNT) WAIT_COUNT, SUBSTR(MAX(LPAD(AD1.ACTIVITY_COUNT, 10)|| AD1.ACTIVITY_TYPE), 11) MOST_ACTIVE, XMLAGG( XMLELEMENT( "activity", XMLATTRIBUTES( AD1.AC TIVITY_TYPE AS "class", AD1.EVENT_NAME AS "event"), AD1.ACTIVITY_COUNT) ORDER BY AD1.ACTIVITY_TYPE, AD1.EVENT_NAME) ACTIVITY_TOTAL FROM (SELECT AD0.ACTIVITY_TYPE, AD0.EVENT_NAME, MIN(AD0.ACTIVITY_START) ACTIVITY_START, MAX(AD0.ACTIVITY_END) ACTIVITY_END, SUM(AD0.ACTIVITY_COUNT) ACTIVITY_COUNT, SUM(AD0.WAIT_COUNT) WAIT_COUNT FROM ASH_DATA AD0 GROUP BY AD0.ACTIVITY_TYPE, AD0.EVENT_NAME) AD1) AT, (SELECT XMLELEMENT( "activity_histogram", XMLATTRIBUTES( MAX(AD2.BUCKET_NUM) AS "bucket_count"), XMLAGG( XMLELEMENT( "bucket", XMLATTRIBUTES( AD2.BUCKET_NUM AS "bucket_number", TO_CHAR(AD2.ACTIVITY_START, :B17 ) AS "start_time", TO_CHAR(AD2.ACTIVITY_END, :B17 ) AS "end_time", ROUND((AD2.ACTIVITY_END - AD2.ACTIVITY_START) * 3600 * 24)+1 AS "duration", AD2.ACTIVITY_COUNT AS "activity_count", AD2.ACTIVITY_COUNT - AD2.WAIT_COUNT AS "cpu_count", AD2.WAIT_COUNT AS "wait_count", AD2.MOST_ACTIVE AS "most_active"), ACTIVITY_BUCKET) ORDER BY AD2.BUCKET_NUM)) ACTIVITY_HISTO FROM (SELECT AD1.BUCKET_NUM, SUM(ACTIVITY_COUNT) ACTIVITY_COUNT, SUM(WAIT_COUNT) WAIT_COUNT, MIN(AD1.ACTIVITY_START) ACTIVITY_START, MAX(AD1.ACTIVITY_END) ACTIVITY_END, SUBSTR(MAX(LPAD(AD1.ACTIVITY_COUNT, 10)|| AD1.ACTIVITY_TYPE), 11) MOST_ACTIVE, XMLAGG( XMLELEMENT( "activity", XMLATTRIBUTES( AD1.ACTIVITY_TYPE AS "class", AD1.EVENT_NAME AS "event"), AD1.ACTIVITY_COUNT) ORDER BY AD1.ACTIVITY_TYPE, AD1.EVENT_NAME) ACTIVITY_BUCKET FROM (SELECT AD0.BUCKET_NUM, AD0.ACTIVITY_TYPE, AD0.EVENT_NAME, MIN(AD0.ACTIVITY_START) ACTIVITY_START, MAX(AD0.ACTIVITY_END) ACTIVITY_END, SUM(AD0.ACTIVITY_COUNT) ACTIVITY_COUNT, SUM(AD0.WAIT_COUNT) WAIT_COUNT FROM ASH_DATA AD0 WHERE :B11 > 1 GROUP BY AD0.BUCKET_NUM, AD0.ACTIVITY_TYPE, AD0.EVENT_NAME) AD1 GROUP BY AD1.BUCKET_NUM) AD2) AH), NULL), CASE WHEN :B15 IS NOT NULL THEN (SELECT XMLELEMENT( "parallel_info", XMLATTRIBUTES( :B15 AS "qc_instance_id", :B39 AS "qc_session_id", :B38 AS "dop", :B35 AS "inst_count", :B37 AS "server_group_count", :B36 AS "server_set_count"), DECODE(:B34 , 1, PX_SESSIONS, NULL), DECODE(:B33 , 1, PX_SESSIONS, NULL), DECODE(:B32 , 1, PX_INSTANCES, NULL)) FROM (SELECT (SELECT XMLELEMENT( "sessions", XMLATTRIBUTES(MAX(PX_SESSION.ACTIVITY_COUNT) AS "max_activity_count", MAX(PX_SESSION.ACTIVITY_COUNT - PX_SESSION.WAIT_COUNT) AS "max_cpu_count", MAX(PX_SESSION.WAIT_COUNT) AS "max_wait_count", MAX(PX_SESSION.MAX_IO_COUNT) AS "max_io_count", MAX(PX_SESSION.MAX_BUFFER_GETS) AS "max_buffer_gets", MAX(PX_SESSION.MAX_ELAPSED_TIME) AS "max_elapsed_time"), XMLAGG(PX_SESSION.PX_SESSION_XML ORDER BY PX_SERVER_GROUP NULLS FIRST, PX_SERVER_SET, PX_SERVER#)) FROM (SELECT PX_SERVER_GROUP, PX_SERVER_SET, PX_SERVER#, MAX(PI.MAX_ELAPSED_TIME) MAX_ELAPSED_TIME, MAX(PI.MAX_IO_COUNT) MAX_IO_COUNT, MAX(PI.MAX_BUFFER_GETS) MAX_BUFFER_GETS, SUM(PI.ACTIVITY_COUNT) ACTIVITY_COUNT, SUM(PI.WAIT_COUNT) WAIT_COUNT, XMLELEMENT( "session", XMLATTRIBUTES( INST_ID AS "inst_id", PROCESS_NAME AS "proc ess_name", SID AS "session_id", SESSION_SERIAL# AS "session_serial", PX_SERVER_GROUP AS "server_group", PX_SERVER_SET AS "server_set", PX_SERVER# AS "server_num"), XMLELEMENT( "stats", XMLATTRIBUTES( 'monitor' AS "type"), NVL2(MAX(ELAPSED_TIME), XMLELEMENT( "stat", XMLATTRIBUTES('elapsed_time' AS "name"), MAX(ELAPSED_TIME)), NULL), NVL2(MAX(CPU_TIME), XMLELEMENT( "stat", XMLATTRIBUTES('cpu_time' AS "name"), MAX(CPU_TIME)), NULL), NVL2(MAX(USER_IO_WAIT_TIME), XMLELEMENT( "stat", XMLATTRIBUTES('user_io_wait_time' AS "name"), MAX(USER_IO_WAIT_TIME)), NULL), NVL2(MAX(APPLICATION_WAIT_TIME), XMLELEMENT( "stat", XMLATTRIBUTES('application_wait_time' AS "name"), MAX(APPLICATION_WAIT_TIME)), NULL), NVL2(MAX(CONCURRENCY_WAIT_TIME), XMLELEMENT( "stat", XMLATTRIBUTES('concurrency_wait_time' AS "name"), MAX(CONCURRENCY_WAIT_TIME)), NULL), NVL2(MAX(CLUSTER_WAIT_TIME), XMLELEMENT( "stat", XMLATTRIBUTES('cluster_wait_time' AS "name"), MAX(CLUSTER_WAIT_TIME)), NULL), NVL2(MAX(PLSQL_EXEC_TIME), XMLELEMENT( "stat", XMLATTRIBUTES('plsql_exec_time' AS "name"), MAX(PLSQL_EXEC_TIME)), NULL), NVL2(MAX(JAVA_EXEC_TIME), XMLELEMENT( "stat", XMLATTRIBUTES('java_exec_time' AS "name"), MAX(JAVA_EXEC_TIME)), NULL), NVL2(MAX(OTHER_WAIT_TIME), XMLELEMENT( "stat", XMLATTRIBUTES( 'other_wait_time' AS "name"), MAX(OTHER_WAIT_TIME)), NULL), NVL2(MAX(FETCHES), XMLELEMENT( "stat", XMLATTRIBUTES('user_fetch_count' AS "name"), MAX(FETCHES)), NULL), NVL2(MAX( BUFFER_GETS), XMLELEMENT( "stat", XMLATTRIBUTES('buffer_gets' AS "name"), MAX(BUFFER_GETS)), NULL), NVL2(MAX(DISK_READS), XMLELEMENT( "stat", XMLATTRIBUTES('disk_reads' AS "name"), MAX(DISK_READS)), NULL), NVL2(MAX(DIRECT_WRITES), XMLELEMENT( "stat", XMLATTRIBUTES('direct_writes' AS "name"), MAX(DIRECT_WRITES)), NULL)), XMLELEMENT( "activity_sampled", XMLATTRIBUTES( TO_CHAR(MIN(PI.ACTIVITY_START), :B17 ) AS "start_time", TO_CHAR(MAX(PI.ACTIVITY_END), :B17 ) AS "end_time", ROUND((MAX(PI.ACTIVITY_END) - MIN(PI.ACTIVITY_START)) * 3600 *24)+1 AS "duration", SUM(PI.ACTIVITY_COUNT) AS "activity_count", SUM(PI.ACTIVITY_COUNT - PI.WAIT_COUNT) AS "cpu_count", SUM(PI.WAIT_COUNT) AS "wait_count"), XMLAGG( NVL2(ACTIVITY_TYPE, XMLELEMENT( "activity", XMLATTRIBUTES( PI.ACTIVITY_TYPE AS "class", PI.EVENT_NAME AS "event"), ACTIVITY_COUNT), NULL) ORDER BY PI.ACTIVITY_TYPE, PI.EVENT_NAME))) PX_SESSION_XML FROM (SELECT MO.INST_ID, DECODE(MO.PROCESS_NAME, 'ora', 'PX Coordinator', MO.PROCESS_NAME) PROCESS_NAME, MO.SID, MO.SESSION_SERIAL#, MO.PX_SERVER_GROUP, MO.PX_SERVER_SET, MO.PX_SERVER#, ASH0.ACTIVITY_TYPE, ASH0.EVENT_NAME, MAX(MO.IO_COUNT) MAX_IO_COUNT, MAX(MO.BUFFER_GETS) MAX_BUFFER_GETS, MAX(MO.MAX_ELAPSED_TIME) MAX_ELAPSED_TIME, SUM(DECODE(ASH0.ACTIVITY_TYPE, NULL, NULL, ASH0.ACTIVITY_COUNT)) ACTIVITY_COUNT, SUM(DECODE(ASH0.ACTIVITY_TYPE, NULL, NULL, ASH0.WAIT_COUNT)) WAIT_COUNT, MIN(ASH0.ACTIVITY_START) ACTIVITY_START, MAX(ASH0.ACTIVIT Y_END) ACTIVITY_END, MAX(DECODE(MO.ELAPSED_TIME, 0, NULL, MO.ELAPSED_TIME)) ELAPSED_TIME, MAX(DECODE(MO.CPU_TIME, 0, NULL, CPU_TIME)) CPU_TIME, MAX(DECODE(MO.FETCHES, 0, NULL, FETCHES)) FETCHES, MAX(DECODE(MO.BUFFER_GETS, 0, NULL, MO.BUFFER_GETS)) BUFFER_GETS, MAX(DECODE(MO.DISK_READS, 0, NULL, MO.DISK_READS)) DISK_READS, MAX(DECODE(MO.DIRECT_WRITES, 0, NULL, MO.DIRECT_WRITES)) DIRECT_WRITES, MAX(DECODE(MO.APPLICATION_WAIT_TIME, 0, NULL, MO.APPLICATION_WAIT_TIME)) APPLICATION_WAIT_TIME, MAX(DECODE(MO.CONCURRENCY_WAIT_TIME, 0, NULL, MO.CONCURRENCY_WAIT_TIME)) CONCURRENCY_WAIT_TIME, MAX(DECODE(MO.CLUSTER_WAIT_TIME, 0, NULL, MO.CLUSTER_WAIT_TIME)) CLUSTER_WAIT_TIME, MAX(DECODE(MO.USER_IO_WAIT_TIME, 0, NULL, MO.USER_IO_WAIT_TIME)) USER_IO_WAIT_TIME, MAX(DECODE(PLSQL_EXEC_TIME, 0, NULL, PLSQL_EXEC_TIME)) PLSQL_EXEC_TIME, MAX(DECODE(MO.JAVA_EXEC_TIME, 0, NULL, MO.JAVA_EXEC_TIME)) JAVA_EXEC_TIME, MAX(DECODE(MO.OTHER_WAIT_TIME, 0, NULL, MO.OTHER_WAIT_TIME)) OTHER_WAIT_TIME FROM MONITOR_DATA MO, ASH_DATA ASH0 WHERE MO.INST_ID = ASH0.INST_ID(+) AND MO.SID = ASH0.SESSION_ID(+) AND (:B34 = 1 OR :B33 = 1 OR :B32 = 1) GROUP BY MO.INST_ID, MO.PROCESS_NAME, MO.SID, MO.SESSION_SERIAL#, MO.PX_SERVER_GROUP, MO.PX_SERVER_SET, MO.PX_SERVER#, ASH0.ACTIVITY_TYPE, ASH0.EVENT_NAME) PI WHERE (:B34 = 1 OR :B33 = 1) GROUP BY PI.INST_ID, PI.SID, PI.PROCESS_NAME, PI.SESSION_SERIAL#, PI.PX_SERVER_GROUP, PI.PX_SERVER_SET, PI.PX_SERVER#) PX_S ESSION) PX_SESSIONS, (SELECT XMLELEMENT( "instances", XMLATTRIBUTES( MAX(PX_INSTANCE.ACTIVITY_COUNT) AS "max_activity_count", MAX(PX_INSTANCE.WAIT_COUNT - PX_INSTANCE.ACTIVITY_COUNT) AS "max_cpu_count", MAX(PX_INSTANCE.WAIT_COUNT) AS "max_wait_count", MAX(PX_INSTANCE.ELAPSED_TIME) AS "max_elapsed_time", MAX(PX_INSTANCE.BUFFER_GETS) AS "max_buffer_gets", MAX(PX_INSTANCE.IO_COUNT) AS "max_io_count"), XMLAGG(PX_INSTANCE.PX_INSTANCES_XML ORDER BY INST_ID)) FROM (SELECT PI.INST_ID, MAX(PI.ELAPSED_TIME) ELAPSED_TIME, MAX(PI.IO_COUNT) IO_COUNT, MAX(PI.BUFFER_GETS) BUFFER_GETS, SUM(PI.ACTIVITY_COUNT) ACTIVITY_COUNT, SUM(PI.WAIT_COUNT) WAIT_COUNT, XMLELEMENT( "instance", XMLATTRIBUTES( INST_ID AS "inst_id"), XMLELEMENT( "stats", XMLATTRIBUTES( 'monitor' AS "type"), NVL2(MAX(ELAPSED_TIME), XMLELEMENT( "stat", XMLATTRIBUTES('elapsed_time' AS "name"), MAX(ELAPSED_TIME)), NULL), NVL2(MAX(CPU_TIME), XMLELEMENT( "stat", XMLATTRIBUTES('cpu_time' AS "name"), MAX(CPU_TIME)), NULL), NVL2(MAX(USER_IO_WAIT_TIME), XMLELEMENT( "stat", XMLATTRIBUTES('user_io_wait_time' AS "name"), MAX(USER_IO_WAIT_TIME)), NULL), NVL2(MAX(APPLICATION_WAIT_TIME), XMLELEMENT( "stat", XMLATTRIBUTES('application_wait_time' AS "name"), MAX(APPLICATION_WAIT_TIME)), NULL), NVL2(MAX(CONCURRENCY_WAIT_TIME), XMLELEMENT( "stat", XMLATTRIBUTES('concurrency_wait_time' AS "name"), MAX(CONCURRENCY_WAIT_TIME)), NULL), NVL2(MAX(CLUSTER_WAIT_TIME), XMLELEMENT( "stat", XMLATTRIBUTES('cluster _wait_time' AS "name"), MAX(CLUSTER_WAIT_TIME)), NULL), NVL2(MAX(PLSQL_EXEC_TIME), XMLELEMENT( "stat", XMLATTRIBUTES('plsql_exec_time' AS "name"), MAX(PLSQL_EXEC_TIME)), NULL), NVL2(MAX(JAVA_EXEC_TIME), XMLELEMENT( "stat", XMLATTRIBUTES('java_exec_time' AS "name"), MAX(JAVA_EXEC_TIME)), NULL), NVL2(MAX(OTHER_WAIT_TIME), XMLELEMENT( "stat", XMLATTRIBUTES( 'other_wait_time' AS "name"), MAX(OTHER_WAIT_TIME)), NULL), NVL2(MAX(FETCHES), XMLELEMENT( "stat", XMLATTRIBUTES('user_fetch_count' AS "name"), MAX(FETCHES)), NULL), NVL2(MAX(BUFFER_GETS), XMLELEMENT( "stat", XMLATTRIBUTES('buffer_gets' AS "name"), MAX(BUFFER_GETS)), NULL), NVL2(MAX(DISK_READS), XMLELEMENT( "stat", XMLATTRIBUTES('disk_reads' AS "name"), MAX(DISK_READS)), NULL), NVL2(MAX(DIRECT_WRITES), XMLELEMENT( "stat", XMLATTRIBUTES('direct_writes' AS "name"), MAX(DIRECT_WRITES)), NULL)), XMLELEMENT( "activity_sampled", XMLATTRIBUTES( TO_CHAR(MIN(PI.ACTIVITY_START), :B17 ) AS "start_time", TO_CHAR(MAX(PI.ACTIVITY_END), :B17 ) AS "end_time", ROUND((MAX(PI.ACTIVITY_END) - MIN(PI.ACTIVITY_START)) * 3600 *24)+1 AS "duration", SUM(PI.WAIT_COUNT) AS "wait_count", SUM(PI.ACTIVITY_COUNT - PI.WAIT_COUNT) AS "cpu_count", SUM(PI.ACTIVITY_COUNT) AS "activity_count"), XMLAGG( NVL2(ACTIVITY_TYPE, XMLELEMENT( "activity", XMLATTRIBUTES( PI.ACTIVITY_TYPE AS "class", PI.EVENT_NAME AS "event"), ACTIVITY_COUNT), NULL) ORDER BY PI.ACTIVITY_TYPE, PI.EVENT_NAME))) PX_INSTANCES_XML FROM (SELECT MO.INST_ID, ASH.ACTIVITY_TYPE, ASH.EVENT_NAME, ASH.ACTIVITY_COUNT, ASH.WAIT_COUNT, ASH.ACTIVITY_START, ASH.ACTIVITY_END, MO.ELAPSED_TIME, MO.CPU_TIME, MO.APPLICATION_WAIT_TIME, MO.CONCURRENCY_WAIT_TIME, MO.CLUSTER_WAIT_TIME, MO.USER_IO_WAIT_TIME, MO.PLSQL_EXEC_TIME, MO.JAVA_EXEC_TIME, MO.OTHER_WAIT_TIME, MO.FETCHES, MO.BUFFER_GETS, MO.DISK_READS, MO.DIRECT_WRITES, MO.IO_COUNT FROM (SELECT MO0.INST_ID, SUM(MO0.ELAPSED_TIME) ELAPSED_TIME, SUM(MO0.CPU_TIME) CPU_TIME, SUM(MO0.FETCHES) FETCHES, SUM(MO0.BUFFER_GETS) BUFFER_GETS, SUM(MO0.DISK_READS) DISK_READS, SUM(MO0.DIRECT_WRITES) DIRECT_WRITES, SUM(MO0.IO_COUNT) IO_COUNT, SUM(MO0.APPLICATION_WAIT_TIME) APPLICATION_WAIT_TIME, SUM(MO0.CONCURRENCY_WAIT_TIME) CONCURRENCY_WAIT_TIME, SUM(MO0.CLUSTER_WAIT_TIME) CLUSTER_WAIT_TIME, SUM(MO0.USER_IO_WAIT_TIME) USER_IO_WAIT_TIME, SUM(MO0.PLSQL_EXEC_TIME) PLSQL_EXEC_TIME, SUM(MO0.JAVA_EXEC_TIME) JAVA_EXEC_TIME, SUM(MO0.OTHER_WAIT_TIME) OTHER_WAIT_TIME FROM MONITOR_DATA MO0 GROUP BY MO0.INST_ID) MO, (SELECT ASH0.INST_ID, ASH0.ACTIVITY_TYPE, ASH0.EVENT_NAME, SUM(ASH0.ACTIVITY_COUNT) ACTIVITY_COUNT, SUM(ASH0.WAIT_COUNT) WAIT_COUNT, MIN(ASH0.ACTIVITY_START) ACTIVITY_START, MAX(ASH0.ACTIVITY_END) ACTIVITY_END FROM ASH_DATA ASH0 GROUP BY ASH0.INST_ID, ASH0.ACTIVITY_TYPE, ASH0.EVENT_NAME) ASH WHERE MO.INST_ID = ASH.INST_ID(+) AND :B32 = 1 AND :B35 > 1) PI GROUP BY PI.INST_ID)PX_INSTANCE) PX_INSTANCES FROM DUAL)) ELSE NULL END, CASE WHEN :B14 = 1 THEN (SELECT XMLELEMENT( "plan_monitor", XMLATTRIBUTES(MAX(MAX_LINE_ACTIVITY_COUNT) AS "max_activity_count", MAX(MAX_LINE_CPU_COUNT) AS "max_cpu_count", MAX(MAX_LINE_WAIT_COUNT) AS "max_wait_count"), XMLAGG( XMLELEMENT( "operation", XMLATTRIBUTES( PM.PLAN_LINE_ID AS "id", PL.PARENT_ID AS "parent_id", PM.OPERATION AS "name", PM.OPTIONS AS "options", PL.DEPTH AS "depth", PL.POSITION AS "position", CASE WHEN PM.PX_SERVER_SET IS NOT NULL THEN TO_CHAR(PM.PX_SERVER_SET) WHEN AH.PX_SERVER_SET IS NOT NULL THEN TO_CHAR(AH.PX_SERVER_SET) WHEN (:B15 IS NULL OR (PM.LAST_CHANGE_TIME IS NULL AND AT.LINE_ACTIVITY_END IS NULL)) THEN NULL ELSE 'QC' END AS "px_type"), NVL2(PL.OBJECT_NAME, XMLELEMENT( "object", XMLATTRIBUTES(PL.OBJECT_TYPE AS "type"), XMLFOREST(XMLCDATA(PL.OBJECT_OWNER) AS "owner"), XMLFOREST(XMLCDATA(PL.OBJECT_NAME) AS "name"), XMLFOREST(XMLCDATA(PL.OBJECT_ALIAS) AS "alias")), NULL), XMLFOREST(PL.PARTITION_START AS "partition_start", PL.PARTITION_STOP AS "partition_stop"), CASE WHEN PL.CARD IS NULL AND PL.BYTES IS NULL AND PL.COST IS NULL AND PL.TEMP_SPACE IS NULL AND PL.TIME IS NULL THEN NULL ELSE XMLELEMENT( "optimizer", NULL, NVL2(PL.CARD, XMLFOREST(PL.CARD AS "cardinality"), NULL), NVL2(PL.BYTES, XMLFOREST(PL.BYTES AS "bytes"), NULL), NVL2(PL.COST, XMLFOREST(PL.COST AS "cost"), NULL), NVL2(PL.CPU_COST, XMLFOREST(PL.CPU_COST AS "cpu_cost"), NULL), NVL2(PL.IO_COST, XMLFOREST(PL.IO_COST AS "io_cost"), NULL), NVL2(PL.TEMP_SPACE, XMLFOREST(PL.T EMP_SPACE AS "temp"), NULL), NVL2(PL.TIME, XMLFOREST(PL.TIME AS "time"), NULL)) END, XMLELEMENT( "stats", XMLATTRIBUTES('plan_monitor' AS "type"), CASE WHEN PM.FIRST_CHANGE_TIME IS NULL AND AT.LINE_ACTIVITY_START IS NULL THEN NULL ELSE XMLELEMENT( "stat", XMLATTRIBUTES('first_active' AS "name"), TO_CHAR( LEAST(NVL(AT.LINE_ACTIVITY_START, PM.FIRST_CHANGE_TIME), NVL(PM.FIRST_CHANGE_TIME, AT.LINE_ACTIVITY_START)), :B17 )) END, CASE WHEN PM.LAST_CHANGE_TIME IS NULL AND AT.LINE_ACTIVITY_END IS NULL THEN NULL ELSE XMLELEMENT( "stat", XMLATTRIBUTES('last_active' AS "name"), TO_CHAR( GREATEST(NVL(AT.LINE_ACTIVITY_END, PM.LAST_CHANGE_TIME), NVL(PM.LAST_CHANGE_TIME, AT.LINE_ACTIVITY_END)), :B17 )) END, CASE WHEN (PM.LAST_CHANGE_TIME IS NULL AND AT.LINE_ACTIVITY_END IS NULL) OR (PM.LAST_CHANGE_TIME IS NULL AND AT.LINE_ACTIVITY_END IS NULL) THEN NULL ELSE XMLELEMENT( "stat", XMLATTRIBUTES('duration' AS "name"), ROUND( (GREATEST(NVL(AT.LINE_ACTIVITY_END, PM.LAST_CHANGE_TIME), NVL(PM.LAST_CHANGE_TIME, AT.LINE_ACTIVITY_END)) - LEAST(NVL(AT.LINE_ACTIVITY_START, PM.FIRST_CHANGE_TIME), NVL(PM.FIRST_CHANGE_TIME, AT.LINE_ACTIVITY_START))) * 3600 * 24)+1) END, CASE WHEN (PM.OVERALL_LAST_CHANGE_TIME IS NULL AND AT.OVERALL_LINE_ACTIVITY_END IS NULL) OR (PM.LAST_CHANGE_TIME IS NULL AND AT.LINE_ACTIVITY_END IS NULL) THEN NULL ELSE XMLELEMENT( "stat", XMLATTRIBUTES('from_most_recent' AS "name"), ROUND( (GREATEST(NVL(AT.OVERALL_LINE_ACTIVITY_END, PM.OVERALL_LAST_CHANGE_ TIME), NVL(PM.OVERALL_LAST_CHANGE_TIME, AT.OVERALL_LINE_ACTIVITY_END)) - GREATEST(NVL(AT.LINE_ACTIVITY_END, PM.LAST_CHANGE_TIME), NVL(PM.LAST_CHANGE_TIME, AT.LINE_ACTIVITY_END))) * 3600 * 24)) END, CASE WHEN (PM.LAST_CHANGE_TIME IS NULL AND AT.LINE_ACTIVITY_END IS NULL) THEN NULL ELSE XMLELEMENT( "stat", XMLATTRIBUTES( 'from_sql_exec_start' AS "name"), ROUND( (LEAST(NVL(AT.LINE_ACTIVITY_START, PM.FIRST_CHANGE_TIME), NVL(PM.FIRST_CHANGE_TIME, AT.LINE_ACTIVITY_START)) - :B6 ) * 3600 * 24)) END, NVL2(LO.PERCENT_COMPLETE, XMLELEMENT( "stat", XMLATTRIBUTES('percent_complete' AS "name"), LO.PERCENT_COMPLETE), NULL), NVL2(LO.TIME_REMAINING, XMLELEMENT( "stat", XMLATTRIBUTES('time_left' AS "name"), LO.TIME_REMAINING), NULL), CASE WHEN PM.STARTS IS NOT NULL AND PM.STARTS > 0 THEN XMLELEMENT( "stat", XMLATTRIBUTES('starts' AS "name"), PM.STARTS) ELSE NULL END, CASE WHEN PM.STARTS IS NOT NULL AND PM.STARTS > 0 AND PM.OUTPUT_ROWS IS NOT NULL THEN XMLELEMENT( "stat", XMLATTRIBUTES('cardinality' AS "name"), CASE WHEN AT.LINE_ACTIVITY_START IS NULL AND PM.FIRST_CHANGE_TIME IS NULL THEN NULL ELSE PM.OUTPUT_ROWS END) ELSE NULL END, CASE WHEN PM.MEM > 0 THEN XMLELEMENT( "stat", XMLATTRIBUTES('memory' AS "name"), PM.MEM) ELSE NULL END, CASE WHEN PM.MAX_MEM > 0 THEN XMLELEMENT( "stat", XMLATTRIBUTES('max_memory' AS "name"), PM.MAX_MEM) ELSE NULL END, CASE WHEN PM.TEMP > 0 THEN XMLELEMENT( "stat", XMLATTRIBUTES('temp' AS "name"), PM.TEMP) ELSE NU LL END, CASE WHEN PM.MAX_TEMP > 0 THEN XMLELEMENT( "stat", XMLATTRIBUTES('max_temp' AS "name"), PM.MAX_TEMP) ELSE NULL END), NVL2(AT.ACTIVITY_TOTAL, XMLELEMENT( "activity_sampled", XMLATTRIBUTES( TO_CHAR(AT.LINE_ACTIVITY_START, :B17 ) AS "start_time", TO_CHAR(AT.LINE_ACTIVITY_END, :B17 ) AS "end_time", ROUND((AT.LINE_ACTIVITY_END - AT.LINE_ACTIVITY_START) * 3600 *24)+1 AS "duration", AT.LINE_WAIT_COUNT AS "wait_count", AT.LINE_ACTIVITY_COUNT - AT.LINE_WAIT_COUNT AS "cpu_count", AT.LINE_ACTIVITY_COUNT AS "activity_count", AT.MOST_ACTIVE AS "most_active"), AT.ACTIVITY_TOTAL, DECODE(:B11 , 1, NULL, AH.ACTIVITY_HISTO)), NULL)) ORDER BY PM.PLAN_LINE_ID)) FROM (SELECT AT0.*, MAX(LINE_ACTIVITY_END) OVER() OVERALL_LINE_ACTIVITY_END, MAX(LINE_ACTIVITY_COUNT) OVER() MAX_LINE_ACTIVITY_COUNT, MAX(LINE_CPU_COUNT) OVER() MAX_LINE_CPU_COUNT, MAX(LINE_WAIT_COUNT) OVER() MAX_LINE_WAIT_COUNT FROM (SELECT AD1.PLAN_LINE_ID, MIN(AD1.LINE_ACTIVITY_START) LINE_ACTIVITY_START, MAX(AD1.LINE_ACTIVITY_END) LINE_ACTIVITY_END, SUM(AD1.ACTIVITY_COUNT) LINE_ACTIVITY_COUNT, SUM(AD1.WAIT_COUNT) LINE_WAIT_COUNT, SUM(AD1.ACTIVITY_COUNT - AD1.WAIT_COUNT) LINE_CPU_COUNT, SUBSTR(MAX(LPAD(AD1.ACTIVITY_COUNT, 10)|| AD1.ACTIVITY_TYPE), 11) MOST_ACTIVE, XMLAGG(XMLELEMENT( "activity", XMLATTRIBUTES( AD1.ACTIVITY_TYPE AS "class", AD1.EVENT_NAME AS "event"), AD1.ACTIVITY_COUNT) ORDER BY AD1.ACTIVITY_TYPE, AD1.EVENT_NAME) ACTIVITY_TOTAL FROM (SELECT AD0.PLAN_LINE_ID, AD0.ACTIVITY_T YPE, AD0.EVENT_NAME, MIN(ACTIVITY_START) LINE_ACTIVITY_START, MAX(ACTIVITY_END) LINE_ACTIVITY_END, SUM(ACTIVITY_COUNT) ACTIVITY_COUNT, SUM(WAIT_COUNT) WAIT_COUNT FROM ASH_DATA AD0 GROUP BY AD0.PLAN_LINE_ID, AD0.ACTIVITY_TYPE, AD0.EVENT_NAME) AD1 GROUP BY AD1.PLAN_LINE_ID) AT0) AT, (SELECT AD2.PLAN_LINE_ID, MIN(AD2.PX_SERVER_SET) PX_SERVER_SET, MIN(AD2.LINE_ACTIVITY_START) LINE_ACTIVITY_START, MAX(AD2.LINE_ACTIVITY_END) LINE_ACTIVITY_END, SUM(AD2.ACTIVITY_COUNT) ACTIVITY_COUNT, SUM(AD2.WAIT_COUNT) WAIT_COUNT, XMLELEMENT( "activity_histogram", NULL, XMLAGG(XMLELEMENT( "bucket", XMLATTRIBUTES( AD2.BUCKET_NUM AS "bucket_number", AD2.WAIT_COUNT AS "wait_count", AD2.ACTIVITY_COUNT - AD2.WAIT_COUNT AS "cpu_count", AD2.ACTIVITY_COUNT AS "activity_count", AD2.MOST_ACTIVE AS "most_active"), AD2.ACTIVITY_BUCKET) ORDER BY AD2.BUCKET_NUM)) ACTIVITY_HISTO FROM (SELECT AD1.PLAN_LINE_ID, AD1.BUCKET_NUM, MIN(AD1.PX_SERVER_SET) PX_SERVER_SET, MIN(AD1.LINE_ACTIVITY_START) LINE_ACTIVITY_START, MAX(AD1.LINE_ACTIVITY_END) LINE_ACTIVITY_END, MIN(AD1.BUCKET_ACTIVITY_START) BUCKET_ACTIVITY_START, MAX(AD1.BUCKET_ACTIVITY_END) BUCKET_ACTIVITY_END, SUM(AD1.ACTIVITY_COUNT) ACTIVITY_COUNT, SUM(AD1.WAIT_COUNT) WAIT_COUNT, SUBSTR(MAX(LPAD(AD1.ACTIVITY_COUNT, 10)|| AD1.ACTIVITY_TYPE), 11) MOST_ACTIVE, XMLAGG(NVL2(AD1.ACTIVITY_TYPE, XMLELEMENT( "activity", XMLATTRIBUTES( AD1.ACTIVITY_TYPE AS "class", AD1.EVENT_NAME AS "event"), AD1.ACTIVITY_COUNT), NULL) ORDER BY AD1.ACTI VITY_TYPE, AD1.EVENT_NAME) ACTIVITY_BUCKET FROM (SELECT AD0.PLAN_LINE_ID, AD0.BUCKET_NUM, AD0.ACTIVITY_TYPE, AD0.EVENT_NAME, MIN(NVL2(AD0.ACTIVITY_START, AD0.PX_SERVER_SET, NULL)) PX_SERVER_SET, MIN(AD0.ACTIVITY_START) LINE_ACTIVITY_START, MAX(AD0.ACTIVITY_END) LINE_ACTIVITY_END, MIN(AD0.BUCKET_ACTIVITY_START) BUCKET_ACTIVITY_START, MIN(AD0.BUCKET_ACTIVITY_END) BUCKET_ACTIVITY_END, SUM(AD0.WAIT_COUNT) WAIT_COUNT, SUM(AD0.ACTIVITY_COUNT) ACTIVITY_COUNT FROM ASH_DATA AD0 WHERE :B11 > 1 GROUP BY AD0.PLAN_LINE_ID, AD0.BUCKET_NUM, AD0.ACTIVITY_TYPE, AD0.EVENT_NAME) AD1 GROUP BY AD1.PLAN_LINE_ID, AD1.BUCKET_NUM) AD2 GROUP BY AD2.PLAN_LINE_ID) AH, (SELECT PL.ID PLAN_LINE_ID, PL.PARENT_ID, PL.OPERATION, PL.OPTIONS, PL.OBJECT_OWNER, PL.OBJECT_NAME, PL.OBJECT_ALIAS, PL.OBJECT_TYPE, PL.POSITION, PL.DEPTH, PL.COST, PL.CARDINALITY CARD, PL.BYTES, PL.PARTITION_START, PL.PARTITION_STOP, PL.CPU_COST, PL.IO_COST, PL.TEMP_SPACE, PL.TIME FROM V$SQL_PLAN PL WHERE PL.SQL_ID = :B7 AND PL.PLAN_HASH_VALUE = :B20 AND PL.CHILD_ADDRESS = (SELECT MAX(SQL.CHILD_ADDRESS) FROM V$SQL SQL WHERE SQL.SQL_ID = :B7 AND SQL.PLAN_HASH_VALUE = :B20 AND SQL.LOADED_VERSIONS > 0)) PL, (SELECT LO.SQL_PLAN_LINE_ID PLAN_LINE_ID, DECODE(SUM(LO.TOTALWORK), 0, NULL, ROUND(SUM(LO.SOFAR)*100/SUM(LO.TOTALWORK))) PERCENT_COMPLETE, MAX(LO.TIME_REMAINING) TIME_REMAINING FROM GV$SESSION_LONGOPS LO, MONITOR_DATA MO WHERE LO.SQL_ID = :B7 AND LO.SQL_EXEC_START = :B6 AND LO.SQL_EXEC_ID = :B5 AND LO.INST_ID = MO.INST_ID AND LO.SID = MO.SID GROUP BY LO.SQL_PLAN_LINE_ID) LO, (SELECT PM0.*, MAX(LAST_CHANGE_TIME) OVER() OVERALL_LAST_CHANGE_TIME FROM (SELECT /*+ leading(md) use_hash(plm) */ PLM.PLAN_LINE_ID PLAN_LINE_ID, PLM.PLAN_OPERATION OPERATION, PLM.PLAN_OPTIONS OPTIONS, MIN(PLM.FIRST_CHANGE_TIME) FIRST_CHANGE_TIME, MAX(PLM.LAST_CHANGE_TIME) LAST_CHANGE_TIME, MIN(NVL2(PLM.FIRST_CHANGE_TIME, MO.PX_SERVER_SET, NULL)) PX_SERVER_SET, SUM(PLM.STARTS) STARTS, SUM(PLM.OUTPUT_ROWS) OUTPUT_ROWS, SUM(PLM.WORKAREA_MEM) MEM, SUM(PLM.WORKAREA_MAX_MEM) MAX_MEM, SUM(PLM.WORKAREA_TEMPSEG) TEMP, SUM(PLM.WORKAREA_MAX_TEMPSEG) MAX_TEMP FROM GV$SQL_PLAN_MONITOR PLM, MONITOR_DATA MO WHERE PLM.SQL_ID = :B7 AND PLM.SQL_EXEC_START = :B6 AND PLM.SQL_EXEC_ID = :B5 AND PLM.INST_ID = MO.INST_ID AND PLM.INST_ID = NVL( :B8 , PLM.INST_ID) AND PLM.KEY = MO.KEY GROUP BY PLM.PLAN_LINE_ID, PLM.PLAN_OPERATION, PLM.PLAN_OPTIONS) PM0) PM WHERE AH.PLAN_LINE_ID(+) = PM.PLAN_LINE_ID AND AT.PLAN_LINE_ID(+) = PM.PLAN_LINE_ID AND PL.PLAN_LINE_ID(+) = PM.PLAN_LINE_ID AND LO.PLAN_LINE_ID(+) = PM.PLAN_LINE_ID) ELSE NULL END) FROM DUAL

9tgj4g8y4rwy8 select type#, blocks, extents, minexts, maxexts, extsize, extpct, user#, iniexts, NVL(lists, 65535), NVL(groups, 65535), cachehint, hwmincr, NVL(spare1, 0), NVL(scanhint, 0), NVL(bitmapranges, 0) from seg$ where ts#=:1 and file#=:2 and block#=:3

a6r9zzu06tudh select /* one */ /*+ parallel (t1, 16) parallel (t2, 16) */ min(t1.BSNS_UNIT_KEY + t2.BSNS_UNIT_KEY ) , max(t1.DAY_KEY + t2.DAY_KEY), avg(t1.DAY_KEY + t2.DAY_KEY), max(t1.BSNS_UNIT_TYP_CD ), max(t2.CURR_IND) , max(t1.LOAD_DT) from retail.DWB_RTL_TRX t1 , retail.DWB_RTL_TRX t2 where t1.TRX_NBR = t2.TRX_NBR

aqb79nn78dgyy SELECT inst_id, service_name, TO_CHAR(CAST(begin_time AS TIMESTAMP) AT TIME ZONE 'GMT', 'YYYY-MM-DD HH24:MI:SS TZD') time, TO_CHAR(CAST(end_time AS TIMESTAMP) AT TIME ZONE 'GMT', 'YYYY-MM-DD HH24:MI:SS TZD') time, intsize_csec, group_id, cpupercall, dbtimepercall, callspersec, dbtimepersec FROM gv$servicemetric_history WHERE service_name = :p1 AND end_time >= SYSDATE - 5/(60*24) AND group_id = 6 ORDER BY inst_id asc, end_time DESC

arx2rgss6k33m SELECT s.inst_id, s.name, i.instance_name FROM gv$active_services s, gv$instance i WHERE s.name = :p1 AND s.inst_id = i.inst_id

bunssq950snhf insert into wrh$_sga_target_advice (snap_id, dbid, instance_number, SGA_SIZE, SGA_SIZE_FACTOR, ESTD_DB_TIME, ESTD_PHYSICAL_READS) select :snap_id, :dbid, :instance_number, SGA_SIZE, SGA_SIZE_FACTOR, ESTD_DB_TIME, ESTD_PHYSICAL_READS from v$sga_target_advice

bvf3fxv3hatw7 /* OracleOEM */ SELECT PROPAGATION_NAME, TOTAL_NUMBER, TOTAL_BYTES/1024 KBYTES FROM DBA_PROPAGATION P, DBA_QUEUE_SCHEDULES Q WHERE P.SOURCE_QUEUE_NAME = Q.QNAME AND P.SOURCE_QUEUE_OWNER = Q.SCHEMA AND MESSAGE_DELIVERY_MODE='BUFFERED'

cvn54b7yz0s8u select /*+ index(idl_ub1$ i_idl_ub11) +*/ piece#, length, piece from idl_ub1$ where obj#=:1 and part=:2 and version=:3 order by piece#

cxjqbfn0d3yqq SELECT COUNT(*) FROM SYS.DBA_PROPAGATION

d1dumhajv2rr5 SELECT COMPONENT_ID FROM SYS. "_REPORT_COMPONENT_OBJECTS" WHERE COMPONENT_NAME = LOWER(:B1 )

f80h0xb1qvbsk SELECT sys.wri$_adv_seq_msggroup.nextval FROM dual

fgqt3gak6vv8x SELECT instance_name FROM gv$instance WHERE inst_id = :p1

fyddnrs5cctsu /* OracleOEM */ SELECT PROPAGATION_NAME, NUM_MSGS READY FROM V$BUFFERED_SUBSCRIBERS, DBA_PROPAGATION WHERE SUBSCRIBER_NAME IS NULL AND SUBSCRIBER_ADDRESS = DESTINATION_DBLINK AND QUEUE_SCHEMA = SOURCE_QUEUE_OWNER AND QUEUE_NAME = SOURCE_QUEUE_NAME

g12bmbazvj86x SELECT TASK_LIST.TASK_ID FROM (SELECT /*+ NO_MERGE(T) ORDERED */ T.TASK_ID FROM (SELECT * FROM DBA_ADVISOR_TASKS ORDER BY TASK_ID DESC) T, DBA_ADVISOR_PARAMETERS_PROJ P1, DBA_ADVISOR_PARAMETERS_PROJ P2 WHERE T.ADVISOR_NAME='ADDM' AND T.STATUS = 'COMPLETED' AND T.EXECUTION_START >= (SYSDATE - 1) AND T.HOW_CREATED = 'AUTO' AND T.TASK_ID = P1.TASK_ID AND P1.PARAMETER_NAME = 'INSTANCE' AND P1.PARAMETER_VALUE = 'UNUSED' AND T.TASK_ID = P2.TASK_ID AND P2.PARAMETER_NAME = 'DB_ID' AND P2.PARAMETER_VALUE = TO_CHAR(:B1 ) ORDER BY T.TASK_ID DESC) TASK_LIST WHERE ROWNUM = 1

grwydz59pu6mc select text from view$ where rowid=:1

gxxa073u093s4 /* OracleOEM */ SELECT PROPAGATION_NAME, TOTAL_NUMBER, TOTAL_BYTES/1024 KBYTES FROM DBA_PROPAGATION P, DBA_QUEUE_SCHEDULES Q WHERE P.SOURCE_QUEUE_NAME = Q.QNAME AND P.SOURCE_QUEUE_OWNER = Q.SCHEMA AND MESSAGE_DELIVERY_MODE='PERSISTENT'

SQL Id	SQL Text
01nfcnq0mymcu	select /+ BDAGEVIL leading(se) / sql_id\|\|decode(child_number, 0, '', '/'\|\|child_number) sql_id, users_executing exec, sql_text from v$sql s WHERE 1=1 and s.users_executing > 0 and s.sql_text not like '%BDAGEVIL%'
05xcf43d9psvm	SELECT NVL(SUM(FAILURES), 0) FROM SYS.DBA_QUEUE_SCHEDULES
084n00125zh7c	SELECT count() over () as total_count, sd_xe_ash_nm.event_name, sd_xe_ash_nm.event_id, sd_xe_ash_nm.parameter1 as p1text, (CASE WHEN (sd_xe_ash_nm.parameter1 is NULL OR sd_xe_ash_nm.parameter1 = '0') THEN 0 ELSE 1 END) as p1valid, sd_xe_ash_nm.parameter2 as p2text, (CASE WHEN (sd_xe_ash_nm.parameter2 is NULL OR sd_xe_ash_nm.parameter2 = '0') THEN 0 ELSE 1 END) as p2valid, sd_xe_ash_nm.parameter3 as p3text, (CASE WHEN (sd_xe_ash_nm.parameter3 is NULL OR sd_xe_ash_nm.parameter3 = '0') THEN 0 ELSE 1 END) as p3valid, sd_xe_ash_nm.keh_evt_id, nvl(xc.class#, 0) as class_num, sd_xe_ash_nm.wait_class_id, nvl(xc.keh_id, 0) as keh_ecl_id, sd_xe_ash_nm.ash_cnt, sd_xe_ash_nm.fg_cnt, sd_xe_ash_nm.fg_wts_diff, sd_xe_ash_nm.fg_tmo_diff, sd_xe_ash_nm.fg_tim_wait_diff, sd_xe_ash_nm.tot_wts_diff, sd_xe_ash_nm.tot_tmo_diff, sd_xe_ash_nm.tot_tim_wait_diff FROM ( SELECT sd_xe_ash., evtname.event_name, evtname.wait_class_id, evtname.parameter1, evtname.parameter2, evtname.parameter3 FROM ( SELECT sd_xe., nvl(ash.cnt, 0) as ash_cnt, nvl(ash.fg_cnt, 0) as fg_cnt FROM ( SELECT nvl(xe.k eh_id, 0) as keh_evt_id, nvl(sd.event_id, xe.event_hash) as event_id, nvl(sd.fg_wts_diff, 0) as fg_wts_diff, nvl(sd.fg_tmo_diff, 0) as fg_tmo_diff, nvl(sd.fg_tim_wait_diff, 0) as fg_tim_wait_diff, nvl(sd.tot_wts_diff, 0) as tot_wts_diff, nvl(sd.tot_tmo_diff, 0) as tot_tmo_diff, nvl(sd.tot_tim_wait_diff, 0) as tot_tim_wait_diff FROM ( SELECT endsn.event_id as event_id, sum(endsn.total_waits_fg - nvl(begsn.total_waits_fg, 0)) as fg_wts_diff, sum(endsn.total_timeouts_fg - nvl(begsn.total_timeouts_fg, 0)) as fg_tmo_diff, sum(endsn.time_waited_micro_fg - nvl(begsn.time_waited_micro_fg, 0)) as fg_tim_wait_diff, sum(endsn.total_waits - nvl(begsn.total_waits, 0)) as tot_wts_diff, sum(endsn.total_timeouts - nvl(begsn.total_timeouts, 0)) as tot_tmo_diff, sum(endsn.time_waited_micro - nvl(begsn.time_waited_micro, 0)) as tot_tim_wait_diff FROM ( SELECT FROM WRH$_SYSTEM_EVENT esi WHERE esi.dbid = :dbid AND esi.snap_id = :eid ) endsn LEFT OUTER JOIN ( SELECT * FROM WRH$_SYSTEM_EVENT bsi WHERE bsi.dbid = :dbid AND bsi.snap_id = :bid ) begsn ON (endsn.event_id = begsn.event_id AND endsn.instance_number = begsn.instance_number) GROUP BY endsn.event_id ) sd FULL OUTER JOIN X$KEHEVTMAP xe ON sd.event_id = xe.event_hash ) sd_xe LEFT OUTER JOIN (SELECT a.event_id, count(*) as cnt, sum(decode(a.session_type, 1, 1, 0)) as fg_cnt FROM WRH$_ACTIVE_SESSION_HISTORY a WHERE a.dbid = :dbid AND a.snap_id > :bid AND a.snap_id <= :eid and a.wait_time = 0 GROUP BY a.event_id) ash ON sd_xe.event_id = ash.event_id ) sd_xe_ash, WRH$_EVENT_NAME evtname WHERE evtname.event_id = sd_xe_ash.event_id and evtname.event_id > 0 and evtname.dbid = :dbid ) sd_xe_ash_nm, X$KEHECLMAP xc WHERE sd_xe_ash_nm.wait_class_id = xc.class_hash ORDER BY sd_xe_ash_nm.wait_class_id, sd_xe_ash_nm.fg_tim_wait_diff DESC, sd_xe_ash_nm.event_id
18naypzfmabd6	INSERT INTO MGMT_SYSTEM_PERFORMANCE_LOG (JOB_NAME, TIME, DURATION, MODULE, ACTION, IS_TOTAL, NAME, VALUE, CLIENT_DATA, HOST_URL) VALUES (:B9 , SYSDATE, :B8 , SUBSTR(:B7 , 1, 512), SUBSTR(:B6 , 1, 32), :B5 , SUBSTR(:B4 , 1, 128), SUBSTR(:B3 , 1, 128), SUBSTR(:B2 , 1, 128), SUBSTR(:B1 , 1, 256))
1mjhyad05m8pf	SELECT value FROM gv$osstat WHERE inst_id = :p1 and stat_name='NUM_CPU_CORES'
1uk5m5qbzj1vt	BEGIN dbms_workload_repository.create_snapshot; END;
32hbap2vtmf53	select position#, sequence#, level#, argument, type#, charsetid, charsetform, properties, nvl(length, 0), nvl(precision#, 0), nvl(scale, 0), nvl(radix, 0), type_owner, type_name, type_subname, type_linkname, pls_type from argument$ where obj#=:1 and procedure#=:2 order by sequence# desc
39m4sx9k63ba2	select /+ index(idl_ub2$ i_idl_ub21) +/ piece#, length, piece from idl_ub2$ where obj#=:1 and part=:2 and version=:3 order by piece#
3ktacv9r56b51	select owner#, name, namespace, remoteowner, linkname, p_timestamp, p_obj#, nvl(property, 0), subname, type#, d_attrs from dependency$ d, obj$ o where d_obj#=:1 and p_obj#=obj#(+) order by order#
3m8smr0v7v1m6	INSERT INTO sys.wri$_adv_message_groups (task_id, id, seq, message#, fac, hdr, lm, nl, p1, p2, p3, p4, p5) VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13)
40u9awsftkwn4	SELECT PROPAGATION_NAME, QUEUE_TABLE, SOURCE_QUEUE_OWNER, SOURCE_QUEUE_NAME FROM DBA_QUEUES, DBA_PROPAGATION WHERE OWNER=SOURCE_QUEUE_OWNER AND SOURCE_QUEUE_NAME=NAME
64th2cn1fhq5r	SELECT /+ no_monitor / MO3.SQL_ID, MO3.SQL_EXEC_START, MO3.SQL_EXEC_ID, MO3.SQL_PLAN_HASH_VALUE, MAX(MO3.STATUS) STATUS, MAX(CASE WHEN MO3.PX_QCSID IS NULL THEN INST_ID ELSE NULL END) INSTANCE_ID, MAX(CASE WHEN MO3.PX_QCSID IS NULL THEN MO3.SID ELSE NULL END) SESSION_ID, MAX(CASE WHEN MO3.PX_QCSID IS NULL THEN MO3.SESSION_SERIAL# ELSE NULL END) SESSION_SERIAL, MAX(MO3.FIRST_REFRESH_TIME) FIRST_REFRESH_TIME, MAX(MO3.LAST_REFRESH_TIME) LAST_REFRESH_TIME, SUM(MO3.REFRESH_COUNT) REFRESH_COUNT, MAX(MO3.PX_QCINST_ID) QC_INST_ID, MAX(MO3.PX_QCSID) QC_SESSION_ID, COUNT(DISTINCT MO3.INST_ID) INST_COUNT, COUNT(DISTINCT MO3.PX_SERVER_GROUP) SERVER_GROUP_COUNT, MAX(MO3.PX_SERVER_SET) SERVER_SET_COUNT, MAX(CASE WHEN MO3.PX_SERVER_GROUP = 1 AND MO3.PX_SERVER_SET = 1 THEN MO3.PX_SERVER# ELSE NULL END) DOP, SUM(NVL2(MO3.PX_FILTER, MO3.FIXED_ELAPSED_TIME, NULL)) ELAPSED_TIME, SUM(NVL2(MO3.PX_FILTER, MO3.CPU_TIME, NULL)) CPU_TIME, SUM(NVL2(MO3.PX_FILTER, MO3.FETCHES, NULL)) FETCHES, SUM(NVL2(MO3.PX_FILTER, MO3.BUFFER_GETS, NULL)) BUFFER_GETS, SUM(NVL2(MO3.PX_FILTER, MO3.DISK_READS, NULL)) DISK_READS, SUM(NVL2(MO3.PX_FILTER, MO3.DIRECT_WRITES, NULL)) DIRECT_WRITES, SUM(NVL2(MO3.PX_FILTER, MO3.APPLICATION_WAIT_TIME, NULL)) APPLICATION_WAIT_TIME, SUM(NVL2(MO3.PX_FILTER, MO3.CONCURRENCY_WAIT_TIME, NULL)) CONCURRENCY_WAIT_TIME, SUM(NVL2(MO3.PX_FILTER, MO3.CLUSTER_WAIT_TIME, NULL)) CLUSTER_WAIT_TIME, SUM(NVL2(MO3.PX_FILTER, MO3.USER_IO_WAIT_TIME, NULL) ) USER_IO_WAIT_TIME, SUM(NVL2(MO3.PX_FILTER, MO3.PLSQL_EXEC_TIME, NULL)) PLSQL_EXEC_TIME, SUM(NVL2(MO3.PX_FILTER, MO3.JAVA_EXEC_TIME, NULL)) JAVA_EXEC_TIME, SUM(NVL2(MO3.PX_FILTER, MO3.OTHER_WAIT_TIME, NULL)) OTHER_WAIT_TIME FROM (SELECT MO1.*, CASE WHEN (NVL(CPU_TIME, 0)+ NVL(APPLICATION_WAIT_TIME, 0)+ NVL(CONCURRENCY_WAIT_TIME, 0)+ NVL(CLUSTER_WAIT_TIME, 0)+ NVL(USER_IO_WAIT_TIME, 0)) > NVL(ELAPSED_TIME, 0) THEN (NVL(CPU_TIME, 0)+ NVL(APPLICATION_WAIT_TIME, 0)+ NVL(CONCURRENCY_WAIT_TIME, 0)+ NVL(CLUSTER_WAIT_TIME, 0)+ NVL(USER_IO_WAIT_TIME, 0)) ELSE ELAPSED_TIME END FIXED_ELAPSED_TIME, CASE WHEN (NVL(CPU_TIME, 0)+ NVL(APPLICATION_WAIT_TIME, 0)+ NVL(CONCURRENCY_WAIT_TIME, 0)+ NVL(CLUSTER_WAIT_TIME, 0)+ NVL(USER_IO_WAIT_TIME, 0)) > NVL(ELAPSED_TIME, 0) THEN 0 ELSE ELAPSED_TIME - (NVL(CPU_TIME, 0)+ NVL(APPLICATION_WAIT_TIME, 0)+ NVL(CONCURRENCY_WAIT_TIME, 0)+ NVL(CLUSTER_WAIT_TIME, 0)+ NVL(USER_IO_WAIT_TIME, 0)) END OTHER_WAIT_TIME, CASE WHEN ((:B10 = 1 AND MO1.PX_QCSID IS NULL) OR ((MO1.PX_SERVER_GROUP = :B9 OR :B9 IS NULL) AND (MO1.PX_SERVER_SET = :B8 OR :B8 IS NULL) AND (MO1.PX_SERVER# = :B7 OR :B7 IS NULL))) THEN 1 ELSE NULL END PX_FILTER FROM GV$SQL_MONITOR MO1, (SELECT DISTINCT MO2.SQL_ID, MO2.SQL_EXEC_START, MO2.SQL_EXEC_ID FROM GV$SQL_MONITOR MO2, (SELECT MAX(MOD(MO3.KEY, 4294967296)) MAX_ENTRY_NUMBER FROM GV$SQL_MONITOR MO3 WHERE MO3.PX_QCSID IS NULL AND MO3.INST_ID = NVL(:B6 , MO3.INST_ID) AND MO3.SQL_ID = NVL(:B5 , MO3.SQL_ ID) AND MO3.SID = NVL(:B4 , MO3.SID) AND MO3.SESSION_SERIAL# = NVL(:B3 , MO3.SESSION_SERIAL#) AND MO3.SQL_EXEC_START = NVL(:B2 , MO3.SQL_EXEC_START) AND MO3.SQL_EXEC_ID = NVL(:B1 , MO3.SQL_EXEC_ID)) MO_LAST WHERE MO2.PX_QCSID IS NULL AND MO2.INST_ID = NVL(:B6 , MO2.INST_ID) AND MO2.SQL_ID = NVL(:B5 , MO2.SQL_ID) AND MO2.SID = NVL(:B4 , MO2.SID) AND MO2.SESSION_SERIAL# = NVL(:B3 , MO2.SESSION_SERIAL#) AND MO2.SQL_EXEC_START = NVL(:B2 , MO2.SQL_EXEC_START) AND MO2.SQL_EXEC_ID = NVL(:B1 , MO2.SQL_EXEC_ID) AND MOD(MO2.KEY, 4294967296) = MO_LAST.MAX_ENTRY_NUMBER) MO5 WHERE MO1.SQL_ID = MO5.SQL_ID AND MO1.SQL_EXEC_START = MO5.SQL_EXEC_START AND MO1.SQL_EXEC_ID = MO5.SQL_EXEC_ID) MO3 GROUP BY MO3.SQL_ID, MO3.SQL_EXEC_START, MO3.SQL_EXEC_ID, MO3.SQL_PLAN_HASH_VALUE
6ajkhukk78nsr	begin prvt_hdm.auto_execute( :dbid, :inst_num , :end_snap_id ); end;
6gvch1xu9ca3g	DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN := FALSE; BEGIN EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS(); :mydate := next_date; IF broken THEN :b := 1; ELSE :b := 0; END IF; END;
7frqszzdu8sgg	select DECODE(UPPER(d.OPEN_MODE), 'READ WRITE', 'R', 'N') , DECODE(UPPER(i.STATUS), 'OPEN', 'O', 'N') into :b0, :b1 from v$database d , gv$instance i where i.INSTANCE_NAME=:b2
7qjhf5dzmazsr	SELECT snap_id , OBJ#, DATAOBJ# FROM (SELECT /+ ordered use_nl(t2) index(t2) / t2.snap_id , t1.OBJN_KEWRSEG OBJ#, t1.OBJD_KEWRSEG DATAOBJ# FROM X$KEWRTSEGSTAT t1, WRH$_SEG_STAT_OBJ t2 WHERE t2.dbid(+) = :dbid AND t2.OBJ#(+) = t1.OBJN_KEWRSEG AND t2.DATAOBJ#(+) = t1.OBJD_KEWRSEG) WHERE nvl(snap_id, 0) < :snap_id
7uqmyd619pj1a	/* OracleOEM / DECLARE instance_number NUMBER; latest_task_id NUMBER; db_elapsed_time NUMBER; start_time VARCHAR2(1024); end_time VARCHAR2(1024); db_id NUMBER; TYPE data_cursor_type IS REF CURSOR; data_cursor data_cursor_type; CURSOR get_latest_task_id IS SELECT TASK_LIST.TASK_ID FROM (SELECT /+ NO_MERGE(T) ORDERED / T.TASK_ID FROM (select from dba_advisor_tasks order by task_id desc) T, dba_advisor_parameters_proj P1, dba_advisor_parameters_proj P2 WHERE T.ADVISOR_NAME='ADDM' AND T.STATUS = 'COMPLETED' AND T.EXECUTION_START >= (sysdate - 1) AND T.HOW_CREATED = 'AUTO' AND T.TASK_ID = P1.TASK_ID AND P1.PARAMETER_NAME = 'INSTANCE' AND P1.PARAMETER_VALUE = 'UNUSED' AND T.TASK_ID = P2.TASK_ID AND P2.PARAMETER_NAME = 'DB_ID' AND P2.PARAMETER_VALUE = to_char(db_id) ORDER BY T.TASK_ID DESC) TASK_LIST WHERE ROWNUM = 1; BEGIN SELECT dbid INTO db_id from v$database; OPEN get_latest_task_id; FETCH get_latest_task_id INTO latest_task_id; CLOSE get_latest_task_id; FOR param_info IN (SELECT parameter_value, parameter_name FROM dba_advisor_parameters_proj WHERE task_id= latest_task_id AND (parameter_name='START_TIME' OR parameter_name='END_TIME' OR parameter_name='DB_ELAPSED_TIME') ORDER BY 2) LOOP IF param_info.parameter_name = 'DB_ ELAPSED_TIME' THEN db_elapsed_time:= param_info.parameter_value; ELSIF param_info.parameter_name = 'END_TIME' THEN end_time := param_info.parameter_value; ELSIF param_info.parameter_name = 'START_TIME' THEN start_time := param_info.parameter_value; END IF; END LOOP; -- open the cursor to return OPEN data_cursor FOR SELECT DISTINCT latest_task_id task_id, f.finding_id finding_id, DECODE(recInfo.type, NULL, 'Uncategorized', recInfo.type) rec_type, recInfo.recCount rec_count, f.perc_active_sess impact_pct, f.message message, TO_DATE(start_time , 'MM-DD-YYYY HH24:MI:SS') start_time, TO_DATE(end_time, 'MM-DD-YYYY HH24:MI:SS') end_time, history.findingCount findingCount, f.finding_name finding_name, f.active_sessions active_sessions, instances.instance_count instCount FROM dba_addm_findings f, (SELECT finding_id, count(r.rec_id) recCount, r.type FROM dba_advisor_recommendations r WHERE task_id=latest_task_id GROUP BY r.finding_id, r.type) recInfo, (select count(DISTINCT f_all.task_id) findingCount, f_curr.finding_name FROM (select DISTINCT finding_name from dba_advisor_findings where task_id=latest_task_id AND type<>'INFORMATION' AND type<>'WARNING' AND parent=0) f_curr, (select task_id, end_time from dba_addm_tasks where end_time>sysdate -1 AND requested_analysis='DATABASE') tasks, (SELECT DISTINCT finding_name, task_id FROM dba_adviso r_findings) f_all WHERE f_all.task_id=tasks.task_id AND f_all.finding_name=f_curr.finding_name GROUP BY f_curr.finding_name) history, (SELECT f.task_id, f.finding_id, count(b.instance_number) instance_count FROM dba_addm_fdg_breakdown b, dba_addm_findings f WHERE b.task_id= latest_task_id AND b.task_id=f.task_id AND b.finding_id =f.finding_id AND f.type<>'INFORMATION' AND f.type<>'WARNING' AND f.parent=0 GROUP BY f.finding_id, f.task_id ORDER BY f.finding_id ) instances WHERE f.task_id=latest_task_id AND f.type<>'INFORMATION' AND f.type<>'WARNING' AND f.filtered<>'Y' AND f.parent=0 AND f.finding_id=recInfo.finding_id (+) AND f.finding_name=history.finding_name(+) AND f.task_id=instances.task_id(+) AND f.finding_id=instances.finding_id (+) ORDER BY f.finding_id; :2 := data_cursor; END;
7yh8hrc363643	select dbms_sqltune.report_sql_monitor(report_level=>'+histogram' ) Monitor_report from dual
83taa7kaw59c1	select name, intcol#, segcol#, type#, length, nvl(precision#, 0), decode(type#, 2, nvl(scale, -127/MAXSB1MINAL/), 178, scale, 179, scale, 180, scale, 181, scale, 182, scale, 183, scale, 231, scale, 0), null$, fixedstorage, nvl(deflength, 0), default$, rowid, col#, property, nvl(charsetid, 0), nvl(charsetform, 0), spare1, spare2, nvl(spare3, 0) from col$ where obj#=:1 order by intcol#
88juhgrgf4zqa	SELECT DISTINCT :B1 TASK_ID, F.FINDING_ID FINDING_ID, DECODE(RECINFO.TYPE, NULL, 'Uncategorized', RECINFO.TYPE) REC_TYPE, RECINFO.RECCOUNT REC_COUNT, F.PERC_ACTIVE_SESS IMPACT_PCT, F.MESSAGE MESSAGE, TO_DATE(:B3 , 'MM-DD-YYYY HH24:MI:SS') START_TIME, TO_DATE(:B2 , 'MM-DD-YYYY HH24:MI:SS') END_TIME, HISTORY.FINDINGCOUNT FINDINGCOUNT, F.FINDING_NAME FINDING_NAME, F.ACTIVE_SESSIONS ACTIVE_SESSIONS, INSTANCES.INSTANCE_COUNT INSTCOUNT FROM DBA_ADDM_FINDINGS F, (SELECT FINDING_ID, COUNT(R.REC_ID) RECCOUNT, R.TYPE FROM DBA_ADVISOR_RECOMMENDATIONS R WHERE TASK_ID=:B1 GROUP BY R.FINDING_ID, R.TYPE) RECINFO, (SELECT COUNT(DISTINCT F_ALL.TASK_ID) FINDINGCOUNT, F_CURR.FINDING_NAME FROM (SELECT DISTINCT FINDING_NAME FROM DBA_ADVISOR_FINDINGS WHERE TASK_ID=:B1 AND TYPE<>'INFORMATION' AND TYPE<>'WARNING' AND PARENT=0) F_CURR, (SELECT TASK_ID, END_TIME FROM DBA_ADDM_TASKS WHERE END_TIME>SYSDATE -1 AND REQUESTED_ANALYSIS='DATABASE') TASKS, (SELECT DISTINCT FINDING_NAME, TASK_ID FROM DBA_ADVISOR_FINDINGS) F_ALL WHERE F_ALL.TASK_ID=TASKS.TASK_ID AND F_ALL.FINDING_NAME=F_CURR.FINDING_NAME GROUP BY F_CURR.FINDING_NAME) HISTORY, (SELECT F.TASK_ID, F.FINDING_ID, COUNT(B.INSTANCE_NUMBER) INSTANCE_COUNT FROM DBA_ADDM_FDG_BREAKDOWN B, DBA_ADDM_FINDINGS F WHERE B.TASK_ID= :B1 AND B.TASK_ID=F.TASK_ID AND B.FINDING_ID =F.FINDING_ID AND F.TYPE<>'INFORMATION' AND F.TYPE<>'WARNING' AND F.PARENT=0 GROUP BY F.FINDING_ID, F.TASK_ID ORDER BY F.FINDING_ID ) INSTA NCES WHERE F.TASK_ID=:B1 AND F.TYPE<>'INFORMATION' AND F.TYPE<>'WARNING' AND F.FILTERED<>'Y' AND F.PARENT=0 AND F.FINDING_ID=RECINFO.FINDING_ID (+) AND F.FINDING_NAME=HISTORY.FINDING_NAME(+) AND F.TASK_ID=INSTANCES.TASK_ID(+) AND F.FINDING_ID=INSTANCES.FINDING_ID (+) ORDER BY F.FINDING_ID
8j37z6cqf3s9h	SELECT value FROM gv$osstat WHERE inst_id = :p1 and stat_name='NUM_CPUS'
8swypbbr0m372	select order#, columns, types from access$ where d_obj#=:1
9550qrptt8fu2	WITH MONITOR_DATA AS (SELECT INST_ID, KEY, STATUS, FIRST_REFRESH_TIME, LAST_REFRESH_TIME, REFRESH_COUNT, PROCESS_NAME, SID, SQL_ID, SQL_EXEC_START, SQL_EXEC_ID, SQL_PLAN_HASH_VALUE, SQL_CHILD_ADDRESS, SESSION_SERIAL#, PX_SERVER#, PX_SERVER_GROUP, PX_SERVER_SET, PX_QCINST_ID, PX_QCSID, MAX(ELAPSED_TIME) OVER() MAX_ELAPSED_TIME, MAX(NVL(DIRECT_WRITES, 0) + NVL(DISK_READS, 0)) OVER() MAX_IO_COUNT, MAX(NVL(BUFFER_GETS, 0)) OVER() MAX_BUFFER_GETS, CASE WHEN ELAPSED_TIME < (CPU_TIME+ APPLICATION_WAIT_TIME+ CONCURRENCY_WAIT_TIME+ CLUSTER_WAIT_TIME+ USER_IO_WAIT_TIME) THEN (CPU_TIME+ APPLICATION_WAIT_TIME+ CONCURRENCY_WAIT_TIME+ CLUSTER_WAIT_TIME+ USER_IO_WAIT_TIME) ELSE ELAPSED_TIME END ELAPSED_TIME, CPU_TIME, APPLICATION_WAIT_TIME, CONCURRENCY_WAIT_TIME, CLUSTER_WAIT_TIME, USER_IO_WAIT_TIME, CASE WHEN ELAPSED_TIME < (CPU_TIME+ APPLICATION_WAIT_TIME+ CONCURRENCY_WAIT_TIME+ CLUSTER_WAIT_TIME+ USER_IO_WAIT_TIME) THEN 0 ELSE ELAPSED_TIME - (CPU_TIME+ APPLICATION_WAIT_TIME+ CONCURRENCY_WAIT_TIME+ CLUSTER_WAIT_TIME+ USER_IO_WAIT_TIME) END OTHER_WAIT_TIME, PLSQL_EXEC_TIME, JAVA_EXEC_TIME, FETCHES, BUFFER_GETS, DISK_READS, DIRECT_WRITES, NVL(DISK_READS, 0)+NVL(DIRECT_WRITES, 0) IO_COUNT FROM GV$SQL_MONITOR MO1 WHERE MO1.INST_ID = NVL(:B8 , MO1.INST_ID) AND MO1.SQL_ID = :B7 AND MO1.SQL_EXEC_START = :B6 AND MO1.SQL_EXEC_ID = :B5 AND ((:B4 = 1 AND MO1.PX_QCSID IS NULL) OR (MO1.PX_SERVER_GROUP = NVL(:B3 , MO1.PX_SERVER_GROUP) AND MO1.PX_SERVER_SE T = NVL(:B2 , MO1.PX_SERVER_SET) AND MO1.PX_SERVER# = NVL(:B1 , MO1.PX_SERVER#)))), ASH_DATA AS (SELECT ASH2.BUCKET_NUM, ASH2.PLAN_LINE_ID, ASH2.ACTIVITY_TYPE, ASH2.EVENT_NAME, ASH2.INST_ID, ASH2.SESSION_ID, :B13 + ((:B13 - :B12 )/ :B11 ) * (ASH2.BUCKET_NUM-1) BUCKET_ACTIVITY_START, :B13 + (((:B12 - :B13 )/ :B11 ) * (ASH2.BUCKET_NUM)) - NUMTODSINTERVAL(1, 'SECOND') BUCKET_ACTIVITY_END, ASH2.ACTIVITY_START, ASH2.ACTIVITY_END, ASH2.ACTIVITY_COUNT, ASH2.WAIT_COUNT, ASH2.PX_SERVER_SET FROM (SELECT ASH1.BUCKET_NUM, ASH1.PLAN_LINE_ID, ASH1.ACTIVITY_TYPE, ASH1.EVENT_NAME, ASH1.INST_ID, ASH1.SESSION_ID, MIN(ASH1.PX_SERVER_SET) PX_SERVER_SET, MIN(ASH1.SAMPLE_TIME) ACTIVITY_START, MAX(ASH1.SAMPLE_TIME) ACTIVITY_END, COUNT() ACTIVITY_COUNT, COUNT(ASH1.WAIT_CLASS) WAIT_COUNT FROM ( SELECT /+ leading(mo) use_hash(ash0) / CAST(ASH0.SAMPLE_TIME AS DATE) SAMPLE_TIME, CASE WHEN :B11 > 1 THEN WIDTH_BUCKET(CAST(ASH0.SAMPLE_TIME AS DATE), :B13 , :B12 + NUMTODSINTERVAL(1, 'SECOND'), :B11 ) ELSE 1 END BUCKET_NUM, ASH0.WAIT_CLASS, NVL(ASH0.WAIT_CLASS, 'Cpu') ACTIVITY_TYPE, DECODE(:B10 , 1, ASH0.EVENT, NULL) EVENT_NAME, ASH0.INST_ID, ASH0.SESSION_ID, ASH0.SQL_PLAN_LINE_ID PLAN_LINE_ID, MO.PX_SERVER_SET FROM MONITOR_DATA MO, (SELECT SQL_ID, SQL_PLAN_LINE_ID, EVENT, WAIT_CLASS, SQL_EXEC_ID, INST_ID, SESSION_ID, FROM_TZ(SAMPLE_TIME, DBTIMEZONE) SAMPLE_TIME FROM GV$ACTIVE_SESSION_HISTORY) ASH0 WHERE ASH0.SQL_ID = :B7 AND (ASH0.WAIT_CLASS IS NULL O R ASH0.WAIT_CLASS != 'Queueing') AND ASH0.SAMPLE_TIME >= :B6 - NUMTODSINTERVAL(1, 'SECOND') AND ASH0.SAMPLE_TIME <= :B9 AND ASH0.SQL_EXEC_ID = :B5 AND ASH0.INST_ID = MO.INST_ID AND ASH0.SESSION_ID = MO.SID) ASH1 WHERE ASH1.BUCKET_NUM > 0 AND ASH1.BUCKET_NUM <= :B11 GROUP BY ASH1.BUCKET_NUM, ASH1.INST_ID, ASH1.SESSION_ID, ASH1.PLAN_LINE_ID, ASH1.ACTIVITY_TYPE, ASH1.EVENT_NAME) ASH2) SELECT /+ no_monitor / XMLELEMENT( "sql_monitor_report", NULL, XMLELEMENT( "report_parameters", NULL, XMLFOREST( :B30 AS "sql_id", :B29 AS "session_id", :B28 AS "session_serial", TO_CHAR(:B27 , :B17 ) AS "sql_exec_start", :B26 AS "sql_exec_id", :B11 AS "bucket_count", TO_CHAR(:B13 , :B17 ) AS "interval_start", TO_CHAR(:B12 , :B17 ) AS "interval_end", :B25 AS "auto_refresh"), CASE WHEN :B15 IS NOT NULL AND :B24 IS NOT NULL AND NOT (:B4 = 1 AND :B3 IS NULL AND :B2 IS NULL AND :B1 IS NULL) THEN XMLELEMENT( "parallel_filter", NULL, XMLFOREST( DECODE(:B4 , 1, 'yes', 'no') AS "qc", :B3 AS "server_group", :B2 AS "server_set", :B1 AS "server_number")) ELSE NULL END), XMLELEMENT( "target", XMLATTRIBUTES(:B23 AS "instance_id", :B22 AS "session_id", :B21 AS "session_serial", :B7 AS "sql_id", TO_CHAR(:B6 , :B17 ) AS "sql_exec_start", :B5 AS "sql_exec_id", :B20 AS "sql_plan_hash"), XMLFOREST(XMLCDATA((SELECT SUBSTR(SQL_FULLTEXT, 1, 3900) FROM GV$SQL SQ WHERE SQ.INST_ID = :B23 AND :B31 = 1 AND SQ.SQL_ID = :B7 AND ROWNUM = 1)) AS "sql_fulltext"), XMLELEMENT ( "status", NULL, :B19 ), XMLELEMENT( "refresh_count", NULL, :B18 ), XMLELEMENT( "first_refresh_time", NULL, TO_CHAR(:B16 , :B17 )), XMLELEMENT( "last_refresh_time", NULL, TO_CHAR(:B9 , :B17 )), XMLELEMENT( "duration", NULL, ROUND((:B9 - LEAST(:B6 , :B16 )) 360024 + 1))), (SELECT XMLELEMENT( "stats", XMLATTRIBUTES('monitor' AS "type"), DECODE(NVL(SUM(ELAPSED_TIME), 0), 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('elapsed_time' AS "name"), SUM(ELAPSED_TIME))), DECODE(NVL(SUM(CPU_TIME), 0), 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('cpu_time' AS "name"), SUM(CPU_TIME))), DECODE(NVL(SUM(USER_IO_WAIT_TIME), 0), 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('user_io_wait_time' AS "name"), SUM(USER_IO_WAIT_TIME))), DECODE(NVL(SUM(APPLICATION_WAIT_TIME), 0), 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('application_wait_time' AS "name"), SUM(APPLICATION_WAIT_TIME))), DECODE(NVL(SUM(CONCURRENCY_WAIT_TIME), 0), 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('concurrency_wait_time' AS "name"), SUM(CONCURRENCY_WAIT_TIME))), DECODE(NVL(SUM(CLUSTER_WAIT_TIME), 0), 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('cluster_wait_time' AS "name"), SUM(CLUSTER_WAIT_TIME))), DECODE(NVL(SUM(PLSQL_EXEC_TIME), 0), 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('plsql_exec_time' AS "name"), SUM(PLSQL_EXEC_TIME))), DECODE(NVL(SUM(JAVA_EXEC_TIME), 0), 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('java_exec_time' AS "name"), SUM(JAVA_EXEC_TIME)) ), DECODE(NVL(SUM(OTHER_WAIT_TIME), 0), 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('other_wait_time' AS "name"), SUM(OTHER_WAIT_TIME))), DECODE(NVL(SUM(FETCHES), 0), 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('user_fetch_count' AS "name"), SUM(FETCHES))), DECODE(NVL(SUM(BUFFER_GETS), 0), 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('buffer_gets' AS "name"), SUM(BUFFER_GETS))), DECODE(NVL(SUM(DISK_READS), 0), 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('disk_reads' AS "name"), SUM(DISK_READS))), DECODE(NVL(SUM(DIRECT_WRITES), 0), 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('direct_writes' AS "name"), SUM(DIRECT_WRITES)))) FROM MONITOR_DATA), NVL2(1, (SELECT CASE WHEN AT.ACTIVITY_COUNT > 0 THEN XMLELEMENT( "activity_sampled", XMLATTRIBUTES( TO_CHAR(AT.ACTIVITY_START, :B17 ) AS "start_time", TO_CHAR(AT.ACTIVITY_END, :B17 ) AS "end_time", ROUND((AT.ACTIVITY_END - AT.ACTIVITY_START) 3600 24)+1 AS "duration", AT.ACTIVITY_COUNT AS "activity_count", AT.WAIT_COUNT AS "wait_count", AT.ACTIVITY_COUNT - AT.WAIT_COUNT AS "cpu_count", AT.MOST_ACTIVE AS "most_active"), AT.ACTIVITY_TOTAL, DECODE(:B11 , 1, NULL, AH.ACTIVITY_HISTO)) ELSE NULL END FROM (SELECT MIN(AD1.ACTIVITY_START) ACTIVITY_START, MAX(AD1.ACTIVITY_END) ACTIVITY_END, SUM(AD1.ACTIVITY_COUNT) ACTIVITY_COUNT, SUM(AD1.WAIT_COUNT) WAIT_COUNT, SUBSTR(MAX(LPAD(AD1.ACTIVITY_COUNT, 10)\|\| AD1.ACTIVITY_TYPE), 11) MOST_ACTIVE, XMLAGG( XMLELEMENT( "activity", XMLATTRIBUTES( AD1.AC TIVITY_TYPE AS "class", AD1.EVENT_NAME AS "event"), AD1.ACTIVITY_COUNT) ORDER BY AD1.ACTIVITY_TYPE, AD1.EVENT_NAME) ACTIVITY_TOTAL FROM (SELECT AD0.ACTIVITY_TYPE, AD0.EVENT_NAME, MIN(AD0.ACTIVITY_START) ACTIVITY_START, MAX(AD0.ACTIVITY_END) ACTIVITY_END, SUM(AD0.ACTIVITY_COUNT) ACTIVITY_COUNT, SUM(AD0.WAIT_COUNT) WAIT_COUNT FROM ASH_DATA AD0 GROUP BY AD0.ACTIVITY_TYPE, AD0.EVENT_NAME) AD1) AT, (SELECT XMLELEMENT( "activity_histogram", XMLATTRIBUTES( MAX(AD2.BUCKET_NUM) AS "bucket_count"), XMLAGG( XMLELEMENT( "bucket", XMLATTRIBUTES( AD2.BUCKET_NUM AS "bucket_number", TO_CHAR(AD2.ACTIVITY_START, :B17 ) AS "start_time", TO_CHAR(AD2.ACTIVITY_END, :B17 ) AS "end_time", ROUND((AD2.ACTIVITY_END - AD2.ACTIVITY_START) 3600 * 24)+1 AS "duration", AD2.ACTIVITY_COUNT AS "activity_count", AD2.ACTIVITY_COUNT - AD2.WAIT_COUNT AS "cpu_count", AD2.WAIT_COUNT AS "wait_count", AD2.MOST_ACTIVE AS "most_active"), ACTIVITY_BUCKET) ORDER BY AD2.BUCKET_NUM)) ACTIVITY_HISTO FROM (SELECT AD1.BUCKET_NUM, SUM(ACTIVITY_COUNT) ACTIVITY_COUNT, SUM(WAIT_COUNT) WAIT_COUNT, MIN(AD1.ACTIVITY_START) ACTIVITY_START, MAX(AD1.ACTIVITY_END) ACTIVITY_END, SUBSTR(MAX(LPAD(AD1.ACTIVITY_COUNT, 10)\|\| AD1.ACTIVITY_TYPE), 11) MOST_ACTIVE, XMLAGG( XMLELEMENT( "activity", XMLATTRIBUTES( AD1.ACTIVITY_TYPE AS "class", AD1.EVENT_NAME AS "event"), AD1.ACTIVITY_COUNT) ORDER BY AD1.ACTIVITY_TYPE, AD1.EVENT_NAME) ACTIVITY_BUCKET FROM (SELECT AD0.BUCKET_NUM, AD0.ACTIVITY_TYPE, AD0.EVENT_NAME, MIN(AD0.ACTIVITY_START) ACTIVITY_START, MAX(AD0.ACTIVITY_END) ACTIVITY_END, SUM(AD0.ACTIVITY_COUNT) ACTIVITY_COUNT, SUM(AD0.WAIT_COUNT) WAIT_COUNT FROM ASH_DATA AD0 WHERE :B11 > 1 GROUP BY AD0.BUCKET_NUM, AD0.ACTIVITY_TYPE, AD0.EVENT_NAME) AD1 GROUP BY AD1.BUCKET_NUM) AD2) AH), NULL), CASE WHEN :B15 IS NOT NULL THEN (SELECT XMLELEMENT( "parallel_info", XMLATTRIBUTES( :B15 AS "qc_instance_id", :B39 AS "qc_session_id", :B38 AS "dop", :B35 AS "inst_count", :B37 AS "server_group_count", :B36 AS "server_set_count"), DECODE(:B34 , 1, PX_SESSIONS, NULL), DECODE(:B33 , 1, PX_SESSIONS, NULL), DECODE(:B32 , 1, PX_INSTANCES, NULL)) FROM (SELECT (SELECT XMLELEMENT( "sessions", XMLATTRIBUTES(MAX(PX_SESSION.ACTIVITY_COUNT) AS "max_activity_count", MAX(PX_SESSION.ACTIVITY_COUNT - PX_SESSION.WAIT_COUNT) AS "max_cpu_count", MAX(PX_SESSION.WAIT_COUNT) AS "max_wait_count", MAX(PX_SESSION.MAX_IO_COUNT) AS "max_io_count", MAX(PX_SESSION.MAX_BUFFER_GETS) AS "max_buffer_gets", MAX(PX_SESSION.MAX_ELAPSED_TIME) AS "max_elapsed_time"), XMLAGG(PX_SESSION.PX_SESSION_XML ORDER BY PX_SERVER_GROUP NULLS FIRST, PX_SERVER_SET, PX_SERVER#)) FROM (SELECT PX_SERVER_GROUP, PX_SERVER_SET, PX_SERVER#, MAX(PI.MAX_ELAPSED_TIME) MAX_ELAPSED_TIME, MAX(PI.MAX_IO_COUNT) MAX_IO_COUNT, MAX(PI.MAX_BUFFER_GETS) MAX_BUFFER_GETS, SUM(PI.ACTIVITY_COUNT) ACTIVITY_COUNT, SUM(PI.WAIT_COUNT) WAIT_COUNT, XMLELEMENT( "session", XMLATTRIBUTES( INST_ID AS "inst_id", PROCESS_NAME AS "proc ess_name", SID AS "session_id", SESSION_SERIAL# AS "session_serial", PX_SERVER_GROUP AS "server_group", PX_SERVER_SET AS "server_set", PX_SERVER# AS "server_num"), XMLELEMENT( "stats", XMLATTRIBUTES( 'monitor' AS "type"), NVL2(MAX(ELAPSED_TIME), XMLELEMENT( "stat", XMLATTRIBUTES('elapsed_time' AS "name"), MAX(ELAPSED_TIME)), NULL), NVL2(MAX(CPU_TIME), XMLELEMENT( "stat", XMLATTRIBUTES('cpu_time' AS "name"), MAX(CPU_TIME)), NULL), NVL2(MAX(USER_IO_WAIT_TIME), XMLELEMENT( "stat", XMLATTRIBUTES('user_io_wait_time' AS "name"), MAX(USER_IO_WAIT_TIME)), NULL), NVL2(MAX(APPLICATION_WAIT_TIME), XMLELEMENT( "stat", XMLATTRIBUTES('application_wait_time' AS "name"), MAX(APPLICATION_WAIT_TIME)), NULL), NVL2(MAX(CONCURRENCY_WAIT_TIME), XMLELEMENT( "stat", XMLATTRIBUTES('concurrency_wait_time' AS "name"), MAX(CONCURRENCY_WAIT_TIME)), NULL), NVL2(MAX(CLUSTER_WAIT_TIME), XMLELEMENT( "stat", XMLATTRIBUTES('cluster_wait_time' AS "name"), MAX(CLUSTER_WAIT_TIME)), NULL), NVL2(MAX(PLSQL_EXEC_TIME), XMLELEMENT( "stat", XMLATTRIBUTES('plsql_exec_time' AS "name"), MAX(PLSQL_EXEC_TIME)), NULL), NVL2(MAX(JAVA_EXEC_TIME), XMLELEMENT( "stat", XMLATTRIBUTES('java_exec_time' AS "name"), MAX(JAVA_EXEC_TIME)), NULL), NVL2(MAX(OTHER_WAIT_TIME), XMLELEMENT( "stat", XMLATTRIBUTES( 'other_wait_time' AS "name"), MAX(OTHER_WAIT_TIME)), NULL), NVL2(MAX(FETCHES), XMLELEMENT( "stat", XMLATTRIBUTES('user_fetch_count' AS "name"), MAX(FETCHES)), NULL), NVL2(MAX( BUFFER_GETS), XMLELEMENT( "stat", XMLATTRIBUTES('buffer_gets' AS "name"), MAX(BUFFER_GETS)), NULL), NVL2(MAX(DISK_READS), XMLELEMENT( "stat", XMLATTRIBUTES('disk_reads' AS "name"), MAX(DISK_READS)), NULL), NVL2(MAX(DIRECT_WRITES), XMLELEMENT( "stat", XMLATTRIBUTES('direct_writes' AS "name"), MAX(DIRECT_WRITES)), NULL)), XMLELEMENT( "activity_sampled", XMLATTRIBUTES( TO_CHAR(MIN(PI.ACTIVITY_START), :B17 ) AS "start_time", TO_CHAR(MAX(PI.ACTIVITY_END), :B17 ) AS "end_time", ROUND((MAX(PI.ACTIVITY_END) - MIN(PI.ACTIVITY_START)) * 3600 24)+1 AS "duration", SUM(PI.ACTIVITY_COUNT) AS "activity_count", SUM(PI.ACTIVITY_COUNT - PI.WAIT_COUNT) AS "cpu_count", SUM(PI.WAIT_COUNT) AS "wait_count"), XMLAGG( NVL2(ACTIVITY_TYPE, XMLELEMENT( "activity", XMLATTRIBUTES( PI.ACTIVITY_TYPE AS "class", PI.EVENT_NAME AS "event"), ACTIVITY_COUNT), NULL) ORDER BY PI.ACTIVITY_TYPE, PI.EVENT_NAME))) PX_SESSION_XML FROM (SELECT MO.INST_ID, DECODE(MO.PROCESS_NAME, 'ora', 'PX Coordinator', MO.PROCESS_NAME) PROCESS_NAME, MO.SID, MO.SESSION_SERIAL#, MO.PX_SERVER_GROUP, MO.PX_SERVER_SET, MO.PX_SERVER#, ASH0.ACTIVITY_TYPE, ASH0.EVENT_NAME, MAX(MO.IO_COUNT) MAX_IO_COUNT, MAX(MO.BUFFER_GETS) MAX_BUFFER_GETS, MAX(MO.MAX_ELAPSED_TIME) MAX_ELAPSED_TIME, SUM(DECODE(ASH0.ACTIVITY_TYPE, NULL, NULL, ASH0.ACTIVITY_COUNT)) ACTIVITY_COUNT, SUM(DECODE(ASH0.ACTIVITY_TYPE, NULL, NULL, ASH0.WAIT_COUNT)) WAIT_COUNT, MIN(ASH0.ACTIVITY_START) ACTIVITY_START, MAX(ASH0.ACTIVIT Y_END) ACTIVITY_END, MAX(DECODE(MO.ELAPSED_TIME, 0, NULL, MO.ELAPSED_TIME)) ELAPSED_TIME, MAX(DECODE(MO.CPU_TIME, 0, NULL, CPU_TIME)) CPU_TIME, MAX(DECODE(MO.FETCHES, 0, NULL, FETCHES)) FETCHES, MAX(DECODE(MO.BUFFER_GETS, 0, NULL, MO.BUFFER_GETS)) BUFFER_GETS, MAX(DECODE(MO.DISK_READS, 0, NULL, MO.DISK_READS)) DISK_READS, MAX(DECODE(MO.DIRECT_WRITES, 0, NULL, MO.DIRECT_WRITES)) DIRECT_WRITES, MAX(DECODE(MO.APPLICATION_WAIT_TIME, 0, NULL, MO.APPLICATION_WAIT_TIME)) APPLICATION_WAIT_TIME, MAX(DECODE(MO.CONCURRENCY_WAIT_TIME, 0, NULL, MO.CONCURRENCY_WAIT_TIME)) CONCURRENCY_WAIT_TIME, MAX(DECODE(MO.CLUSTER_WAIT_TIME, 0, NULL, MO.CLUSTER_WAIT_TIME)) CLUSTER_WAIT_TIME, MAX(DECODE(MO.USER_IO_WAIT_TIME, 0, NULL, MO.USER_IO_WAIT_TIME)) USER_IO_WAIT_TIME, MAX(DECODE(PLSQL_EXEC_TIME, 0, NULL, PLSQL_EXEC_TIME)) PLSQL_EXEC_TIME, MAX(DECODE(MO.JAVA_EXEC_TIME, 0, NULL, MO.JAVA_EXEC_TIME)) JAVA_EXEC_TIME, MAX(DECODE(MO.OTHER_WAIT_TIME, 0, NULL, MO.OTHER_WAIT_TIME)) OTHER_WAIT_TIME FROM MONITOR_DATA MO, ASH_DATA ASH0 WHERE MO.INST_ID = ASH0.INST_ID(+) AND MO.SID = ASH0.SESSION_ID(+) AND (:B34 = 1 OR :B33 = 1 OR :B32 = 1) GROUP BY MO.INST_ID, MO.PROCESS_NAME, MO.SID, MO.SESSION_SERIAL#, MO.PX_SERVER_GROUP, MO.PX_SERVER_SET, MO.PX_SERVER#, ASH0.ACTIVITY_TYPE, ASH0.EVENT_NAME) PI WHERE (:B34 = 1 OR :B33 = 1) GROUP BY PI.INST_ID, PI.SID, PI.PROCESS_NAME, PI.SESSION_SERIAL#, PI.PX_SERVER_GROUP, PI.PX_SERVER_SET, PI.PX_SERVER#) PX_S ESSION) PX_SESSIONS, (SELECT XMLELEMENT( "instances", XMLATTRIBUTES( MAX(PX_INSTANCE.ACTIVITY_COUNT) AS "max_activity_count", MAX(PX_INSTANCE.WAIT_COUNT - PX_INSTANCE.ACTIVITY_COUNT) AS "max_cpu_count", MAX(PX_INSTANCE.WAIT_COUNT) AS "max_wait_count", MAX(PX_INSTANCE.ELAPSED_TIME) AS "max_elapsed_time", MAX(PX_INSTANCE.BUFFER_GETS) AS "max_buffer_gets", MAX(PX_INSTANCE.IO_COUNT) AS "max_io_count"), XMLAGG(PX_INSTANCE.PX_INSTANCES_XML ORDER BY INST_ID)) FROM (SELECT PI.INST_ID, MAX(PI.ELAPSED_TIME) ELAPSED_TIME, MAX(PI.IO_COUNT) IO_COUNT, MAX(PI.BUFFER_GETS) BUFFER_GETS, SUM(PI.ACTIVITY_COUNT) ACTIVITY_COUNT, SUM(PI.WAIT_COUNT) WAIT_COUNT, XMLELEMENT( "instance", XMLATTRIBUTES( INST_ID AS "inst_id"), XMLELEMENT( "stats", XMLATTRIBUTES( 'monitor' AS "type"), NVL2(MAX(ELAPSED_TIME), XMLELEMENT( "stat", XMLATTRIBUTES('elapsed_time' AS "name"), MAX(ELAPSED_TIME)), NULL), NVL2(MAX(CPU_TIME), XMLELEMENT( "stat", XMLATTRIBUTES('cpu_time' AS "name"), MAX(CPU_TIME)), NULL), NVL2(MAX(USER_IO_WAIT_TIME), XMLELEMENT( "stat", XMLATTRIBUTES('user_io_wait_time' AS "name"), MAX(USER_IO_WAIT_TIME)), NULL), NVL2(MAX(APPLICATION_WAIT_TIME), XMLELEMENT( "stat", XMLATTRIBUTES('application_wait_time' AS "name"), MAX(APPLICATION_WAIT_TIME)), NULL), NVL2(MAX(CONCURRENCY_WAIT_TIME), XMLELEMENT( "stat", XMLATTRIBUTES('concurrency_wait_time' AS "name"), MAX(CONCURRENCY_WAIT_TIME)), NULL), NVL2(MAX(CLUSTER_WAIT_TIME), XMLELEMENT( "stat", XMLATTRIBUTES('cluster _wait_time' AS "name"), MAX(CLUSTER_WAIT_TIME)), NULL), NVL2(MAX(PLSQL_EXEC_TIME), XMLELEMENT( "stat", XMLATTRIBUTES('plsql_exec_time' AS "name"), MAX(PLSQL_EXEC_TIME)), NULL), NVL2(MAX(JAVA_EXEC_TIME), XMLELEMENT( "stat", XMLATTRIBUTES('java_exec_time' AS "name"), MAX(JAVA_EXEC_TIME)), NULL), NVL2(MAX(OTHER_WAIT_TIME), XMLELEMENT( "stat", XMLATTRIBUTES( 'other_wait_time' AS "name"), MAX(OTHER_WAIT_TIME)), NULL), NVL2(MAX(FETCHES), XMLELEMENT( "stat", XMLATTRIBUTES('user_fetch_count' AS "name"), MAX(FETCHES)), NULL), NVL2(MAX(BUFFER_GETS), XMLELEMENT( "stat", XMLATTRIBUTES('buffer_gets' AS "name"), MAX(BUFFER_GETS)), NULL), NVL2(MAX(DISK_READS), XMLELEMENT( "stat", XMLATTRIBUTES('disk_reads' AS "name"), MAX(DISK_READS)), NULL), NVL2(MAX(DIRECT_WRITES), XMLELEMENT( "stat", XMLATTRIBUTES('direct_writes' AS "name"), MAX(DIRECT_WRITES)), NULL)), XMLELEMENT( "activity_sampled", XMLATTRIBUTES( TO_CHAR(MIN(PI.ACTIVITY_START), :B17 ) AS "start_time", TO_CHAR(MAX(PI.ACTIVITY_END), :B17 ) AS "end_time", ROUND((MAX(PI.ACTIVITY_END) - MIN(PI.ACTIVITY_START)) 3600 24)+1 AS "duration", SUM(PI.WAIT_COUNT) AS "wait_count", SUM(PI.ACTIVITY_COUNT - PI.WAIT_COUNT) AS "cpu_count", SUM(PI.ACTIVITY_COUNT) AS "activity_count"), XMLAGG( NVL2(ACTIVITY_TYPE, XMLELEMENT( "activity", XMLATTRIBUTES( PI.ACTIVITY_TYPE AS "class", PI.EVENT_NAME AS "event"), ACTIVITY_COUNT), NULL) ORDER BY PI.ACTIVITY_TYPE, PI.EVENT_NAME))) PX_INSTANCES_XML FROM (SELECT MO.INST_ID, ASH.ACTIVITY_TYPE, ASH.EVENT_NAME, ASH.ACTIVITY_COUNT, ASH.WAIT_COUNT, ASH.ACTIVITY_START, ASH.ACTIVITY_END, MO.ELAPSED_TIME, MO.CPU_TIME, MO.APPLICATION_WAIT_TIME, MO.CONCURRENCY_WAIT_TIME, MO.CLUSTER_WAIT_TIME, MO.USER_IO_WAIT_TIME, MO.PLSQL_EXEC_TIME, MO.JAVA_EXEC_TIME, MO.OTHER_WAIT_TIME, MO.FETCHES, MO.BUFFER_GETS, MO.DISK_READS, MO.DIRECT_WRITES, MO.IO_COUNT FROM (SELECT MO0.INST_ID, SUM(MO0.ELAPSED_TIME) ELAPSED_TIME, SUM(MO0.CPU_TIME) CPU_TIME, SUM(MO0.FETCHES) FETCHES, SUM(MO0.BUFFER_GETS) BUFFER_GETS, SUM(MO0.DISK_READS) DISK_READS, SUM(MO0.DIRECT_WRITES) DIRECT_WRITES, SUM(MO0.IO_COUNT) IO_COUNT, SUM(MO0.APPLICATION_WAIT_TIME) APPLICATION_WAIT_TIME, SUM(MO0.CONCURRENCY_WAIT_TIME) CONCURRENCY_WAIT_TIME, SUM(MO0.CLUSTER_WAIT_TIME) CLUSTER_WAIT_TIME, SUM(MO0.USER_IO_WAIT_TIME) USER_IO_WAIT_TIME, SUM(MO0.PLSQL_EXEC_TIME) PLSQL_EXEC_TIME, SUM(MO0.JAVA_EXEC_TIME) JAVA_EXEC_TIME, SUM(MO0.OTHER_WAIT_TIME) OTHER_WAIT_TIME FROM MONITOR_DATA MO0 GROUP BY MO0.INST_ID) MO, (SELECT ASH0.INST_ID, ASH0.ACTIVITY_TYPE, ASH0.EVENT_NAME, SUM(ASH0.ACTIVITY_COUNT) ACTIVITY_COUNT, SUM(ASH0.WAIT_COUNT) WAIT_COUNT, MIN(ASH0.ACTIVITY_START) ACTIVITY_START, MAX(ASH0.ACTIVITY_END) ACTIVITY_END FROM ASH_DATA ASH0 GROUP BY ASH0.INST_ID, ASH0.ACTIVITY_TYPE, ASH0.EVENT_NAME) ASH WHERE MO.INST_ID = ASH.INST_ID(+) AND :B32 = 1 AND :B35 > 1) PI GROUP BY PI.INST_ID)PX_INSTANCE) PX_INSTANCES FROM DUAL)) ELSE NULL END, CASE WHEN :B14 = 1 THEN (SELECT XMLELEMENT( "plan_monitor", XMLATTRIBUTES(MAX(MAX_LINE_ACTIVITY_COUNT) AS "max_activity_count", MAX(MAX_LINE_CPU_COUNT) AS "max_cpu_count", MAX(MAX_LINE_WAIT_COUNT) AS "max_wait_count"), XMLAGG( XMLELEMENT( "operation", XMLATTRIBUTES( PM.PLAN_LINE_ID AS "id", PL.PARENT_ID AS "parent_id", PM.OPERATION AS "name", PM.OPTIONS AS "options", PL.DEPTH AS "depth", PL.POSITION AS "position", CASE WHEN PM.PX_SERVER_SET IS NOT NULL THEN TO_CHAR(PM.PX_SERVER_SET) WHEN AH.PX_SERVER_SET IS NOT NULL THEN TO_CHAR(AH.PX_SERVER_SET) WHEN (:B15 IS NULL OR (PM.LAST_CHANGE_TIME IS NULL AND AT.LINE_ACTIVITY_END IS NULL)) THEN NULL ELSE 'QC' END AS "px_type"), NVL2(PL.OBJECT_NAME, XMLELEMENT( "object", XMLATTRIBUTES(PL.OBJECT_TYPE AS "type"), XMLFOREST(XMLCDATA(PL.OBJECT_OWNER) AS "owner"), XMLFOREST(XMLCDATA(PL.OBJECT_NAME) AS "name"), XMLFOREST(XMLCDATA(PL.OBJECT_ALIAS) AS "alias")), NULL), XMLFOREST(PL.PARTITION_START AS "partition_start", PL.PARTITION_STOP AS "partition_stop"), CASE WHEN PL.CARD IS NULL AND PL.BYTES IS NULL AND PL.COST IS NULL AND PL.TEMP_SPACE IS NULL AND PL.TIME IS NULL THEN NULL ELSE XMLELEMENT( "optimizer", NULL, NVL2(PL.CARD, XMLFOREST(PL.CARD AS "cardinality"), NULL), NVL2(PL.BYTES, XMLFOREST(PL.BYTES AS "bytes"), NULL), NVL2(PL.COST, XMLFOREST(PL.COST AS "cost"), NULL), NVL2(PL.CPU_COST, XMLFOREST(PL.CPU_COST AS "cpu_cost"), NULL), NVL2(PL.IO_COST, XMLFOREST(PL.IO_COST AS "io_cost"), NULL), NVL2(PL.TEMP_SPACE, XMLFOREST(PL.T EMP_SPACE AS "temp"), NULL), NVL2(PL.TIME, XMLFOREST(PL.TIME AS "time"), NULL)) END, XMLELEMENT( "stats", XMLATTRIBUTES('plan_monitor' AS "type"), CASE WHEN PM.FIRST_CHANGE_TIME IS NULL AND AT.LINE_ACTIVITY_START IS NULL THEN NULL ELSE XMLELEMENT( "stat", XMLATTRIBUTES('first_active' AS "name"), TO_CHAR( LEAST(NVL(AT.LINE_ACTIVITY_START, PM.FIRST_CHANGE_TIME), NVL(PM.FIRST_CHANGE_TIME, AT.LINE_ACTIVITY_START)), :B17 )) END, CASE WHEN PM.LAST_CHANGE_TIME IS NULL AND AT.LINE_ACTIVITY_END IS NULL THEN NULL ELSE XMLELEMENT( "stat", XMLATTRIBUTES('last_active' AS "name"), TO_CHAR( GREATEST(NVL(AT.LINE_ACTIVITY_END, PM.LAST_CHANGE_TIME), NVL(PM.LAST_CHANGE_TIME, AT.LINE_ACTIVITY_END)), :B17 )) END, CASE WHEN (PM.LAST_CHANGE_TIME IS NULL AND AT.LINE_ACTIVITY_END IS NULL) OR (PM.LAST_CHANGE_TIME IS NULL AND AT.LINE_ACTIVITY_END IS NULL) THEN NULL ELSE XMLELEMENT( "stat", XMLATTRIBUTES('duration' AS "name"), ROUND( (GREATEST(NVL(AT.LINE_ACTIVITY_END, PM.LAST_CHANGE_TIME), NVL(PM.LAST_CHANGE_TIME, AT.LINE_ACTIVITY_END)) - LEAST(NVL(AT.LINE_ACTIVITY_START, PM.FIRST_CHANGE_TIME), NVL(PM.FIRST_CHANGE_TIME, AT.LINE_ACTIVITY_START))) 3600 * 24)+1) END, CASE WHEN (PM.OVERALL_LAST_CHANGE_TIME IS NULL AND AT.OVERALL_LINE_ACTIVITY_END IS NULL) OR (PM.LAST_CHANGE_TIME IS NULL AND AT.LINE_ACTIVITY_END IS NULL) THEN NULL ELSE XMLELEMENT( "stat", XMLATTRIBUTES('from_most_recent' AS "name"), ROUND( (GREATEST(NVL(AT.OVERALL_LINE_ACTIVITY_END, PM.OVERALL_LAST_CHANGE_ TIME), NVL(PM.OVERALL_LAST_CHANGE_TIME, AT.OVERALL_LINE_ACTIVITY_END)) - GREATEST(NVL(AT.LINE_ACTIVITY_END, PM.LAST_CHANGE_TIME), NVL(PM.LAST_CHANGE_TIME, AT.LINE_ACTIVITY_END))) * 3600 * 24)) END, CASE WHEN (PM.LAST_CHANGE_TIME IS NULL AND AT.LINE_ACTIVITY_END IS NULL) THEN NULL ELSE XMLELEMENT( "stat", XMLATTRIBUTES( 'from_sql_exec_start' AS "name"), ROUND( (LEAST(NVL(AT.LINE_ACTIVITY_START, PM.FIRST_CHANGE_TIME), NVL(PM.FIRST_CHANGE_TIME, AT.LINE_ACTIVITY_START)) - :B6 ) * 3600 * 24)) END, NVL2(LO.PERCENT_COMPLETE, XMLELEMENT( "stat", XMLATTRIBUTES('percent_complete' AS "name"), LO.PERCENT_COMPLETE), NULL), NVL2(LO.TIME_REMAINING, XMLELEMENT( "stat", XMLATTRIBUTES('time_left' AS "name"), LO.TIME_REMAINING), NULL), CASE WHEN PM.STARTS IS NOT NULL AND PM.STARTS > 0 THEN XMLELEMENT( "stat", XMLATTRIBUTES('starts' AS "name"), PM.STARTS) ELSE NULL END, CASE WHEN PM.STARTS IS NOT NULL AND PM.STARTS > 0 AND PM.OUTPUT_ROWS IS NOT NULL THEN XMLELEMENT( "stat", XMLATTRIBUTES('cardinality' AS "name"), CASE WHEN AT.LINE_ACTIVITY_START IS NULL AND PM.FIRST_CHANGE_TIME IS NULL THEN NULL ELSE PM.OUTPUT_ROWS END) ELSE NULL END, CASE WHEN PM.MEM > 0 THEN XMLELEMENT( "stat", XMLATTRIBUTES('memory' AS "name"), PM.MEM) ELSE NULL END, CASE WHEN PM.MAX_MEM > 0 THEN XMLELEMENT( "stat", XMLATTRIBUTES('max_memory' AS "name"), PM.MAX_MEM) ELSE NULL END, CASE WHEN PM.TEMP > 0 THEN XMLELEMENT( "stat", XMLATTRIBUTES('temp' AS "name"), PM.TEMP) ELSE NU LL END, CASE WHEN PM.MAX_TEMP > 0 THEN XMLELEMENT( "stat", XMLATTRIBUTES('max_temp' AS "name"), PM.MAX_TEMP) ELSE NULL END), NVL2(AT.ACTIVITY_TOTAL, XMLELEMENT( "activity_sampled", XMLATTRIBUTES( TO_CHAR(AT.LINE_ACTIVITY_START, :B17 ) AS "start_time", TO_CHAR(AT.LINE_ACTIVITY_END, :B17 ) AS "end_time", ROUND((AT.LINE_ACTIVITY_END - AT.LINE_ACTIVITY_START) * 3600 24)+1 AS "duration", AT.LINE_WAIT_COUNT AS "wait_count", AT.LINE_ACTIVITY_COUNT - AT.LINE_WAIT_COUNT AS "cpu_count", AT.LINE_ACTIVITY_COUNT AS "activity_count", AT.MOST_ACTIVE AS "most_active"), AT.ACTIVITY_TOTAL, DECODE(:B11 , 1, NULL, AH.ACTIVITY_HISTO)), NULL)) ORDER BY PM.PLAN_LINE_ID)) FROM (SELECT AT0., MAX(LINE_ACTIVITY_END) OVER() OVERALL_LINE_ACTIVITY_END, MAX(LINE_ACTIVITY_COUNT) OVER() MAX_LINE_ACTIVITY_COUNT, MAX(LINE_CPU_COUNT) OVER() MAX_LINE_CPU_COUNT, MAX(LINE_WAIT_COUNT) OVER() MAX_LINE_WAIT_COUNT FROM (SELECT AD1.PLAN_LINE_ID, MIN(AD1.LINE_ACTIVITY_START) LINE_ACTIVITY_START, MAX(AD1.LINE_ACTIVITY_END) LINE_ACTIVITY_END, SUM(AD1.ACTIVITY_COUNT) LINE_ACTIVITY_COUNT, SUM(AD1.WAIT_COUNT) LINE_WAIT_COUNT, SUM(AD1.ACTIVITY_COUNT - AD1.WAIT_COUNT) LINE_CPU_COUNT, SUBSTR(MAX(LPAD(AD1.ACTIVITY_COUNT, 10)\|\| AD1.ACTIVITY_TYPE), 11) MOST_ACTIVE, XMLAGG(XMLELEMENT( "activity", XMLATTRIBUTES( AD1.ACTIVITY_TYPE AS "class", AD1.EVENT_NAME AS "event"), AD1.ACTIVITY_COUNT) ORDER BY AD1.ACTIVITY_TYPE, AD1.EVENT_NAME) ACTIVITY_TOTAL FROM (SELECT AD0.PLAN_LINE_ID, AD0.ACTIVITY_T YPE, AD0.EVENT_NAME, MIN(ACTIVITY_START) LINE_ACTIVITY_START, MAX(ACTIVITY_END) LINE_ACTIVITY_END, SUM(ACTIVITY_COUNT) ACTIVITY_COUNT, SUM(WAIT_COUNT) WAIT_COUNT FROM ASH_DATA AD0 GROUP BY AD0.PLAN_LINE_ID, AD0.ACTIVITY_TYPE, AD0.EVENT_NAME) AD1 GROUP BY AD1.PLAN_LINE_ID) AT0) AT, (SELECT AD2.PLAN_LINE_ID, MIN(AD2.PX_SERVER_SET) PX_SERVER_SET, MIN(AD2.LINE_ACTIVITY_START) LINE_ACTIVITY_START, MAX(AD2.LINE_ACTIVITY_END) LINE_ACTIVITY_END, SUM(AD2.ACTIVITY_COUNT) ACTIVITY_COUNT, SUM(AD2.WAIT_COUNT) WAIT_COUNT, XMLELEMENT( "activity_histogram", NULL, XMLAGG(XMLELEMENT( "bucket", XMLATTRIBUTES( AD2.BUCKET_NUM AS "bucket_number", AD2.WAIT_COUNT AS "wait_count", AD2.ACTIVITY_COUNT - AD2.WAIT_COUNT AS "cpu_count", AD2.ACTIVITY_COUNT AS "activity_count", AD2.MOST_ACTIVE AS "most_active"), AD2.ACTIVITY_BUCKET) ORDER BY AD2.BUCKET_NUM)) ACTIVITY_HISTO FROM (SELECT AD1.PLAN_LINE_ID, AD1.BUCKET_NUM, MIN(AD1.PX_SERVER_SET) PX_SERVER_SET, MIN(AD1.LINE_ACTIVITY_START) LINE_ACTIVITY_START, MAX(AD1.LINE_ACTIVITY_END) LINE_ACTIVITY_END, MIN(AD1.BUCKET_ACTIVITY_START) BUCKET_ACTIVITY_START, MAX(AD1.BUCKET_ACTIVITY_END) BUCKET_ACTIVITY_END, SUM(AD1.ACTIVITY_COUNT) ACTIVITY_COUNT, SUM(AD1.WAIT_COUNT) WAIT_COUNT, SUBSTR(MAX(LPAD(AD1.ACTIVITY_COUNT, 10)\|\| AD1.ACTIVITY_TYPE), 11) MOST_ACTIVE, XMLAGG(NVL2(AD1.ACTIVITY_TYPE, XMLELEMENT( "activity", XMLATTRIBUTES( AD1.ACTIVITY_TYPE AS "class", AD1.EVENT_NAME AS "event"), AD1.ACTIVITY_COUNT), NULL) ORDER BY AD1.ACTI VITY_TYPE, AD1.EVENT_NAME) ACTIVITY_BUCKET FROM (SELECT AD0.PLAN_LINE_ID, AD0.BUCKET_NUM, AD0.ACTIVITY_TYPE, AD0.EVENT_NAME, MIN(NVL2(AD0.ACTIVITY_START, AD0.PX_SERVER_SET, NULL)) PX_SERVER_SET, MIN(AD0.ACTIVITY_START) LINE_ACTIVITY_START, MAX(AD0.ACTIVITY_END) LINE_ACTIVITY_END, MIN(AD0.BUCKET_ACTIVITY_START) BUCKET_ACTIVITY_START, MIN(AD0.BUCKET_ACTIVITY_END) BUCKET_ACTIVITY_END, SUM(AD0.WAIT_COUNT) WAIT_COUNT, SUM(AD0.ACTIVITY_COUNT) ACTIVITY_COUNT FROM ASH_DATA AD0 WHERE :B11 > 1 GROUP BY AD0.PLAN_LINE_ID, AD0.BUCKET_NUM, AD0.ACTIVITY_TYPE, AD0.EVENT_NAME) AD1 GROUP BY AD1.PLAN_LINE_ID, AD1.BUCKET_NUM) AD2 GROUP BY AD2.PLAN_LINE_ID) AH, (SELECT PL.ID PLAN_LINE_ID, PL.PARENT_ID, PL.OPERATION, PL.OPTIONS, PL.OBJECT_OWNER, PL.OBJECT_NAME, PL.OBJECT_ALIAS, PL.OBJECT_TYPE, PL.POSITION, PL.DEPTH, PL.COST, PL.CARDINALITY CARD, PL.BYTES, PL.PARTITION_START, PL.PARTITION_STOP, PL.CPU_COST, PL.IO_COST, PL.TEMP_SPACE, PL.TIME FROM V$SQL_PLAN PL WHERE PL.SQL_ID = :B7 AND PL.PLAN_HASH_VALUE = :B20 AND PL.CHILD_ADDRESS = (SELECT MAX(SQL.CHILD_ADDRESS) FROM V$SQL SQL WHERE SQL.SQL_ID = :B7 AND SQL.PLAN_HASH_VALUE = :B20 AND SQL.LOADED_VERSIONS > 0)) PL, (SELECT LO.SQL_PLAN_LINE_ID PLAN_LINE_ID, DECODE(SUM(LO.TOTALWORK), 0, NULL, ROUND(SUM(LO.SOFAR)100/SUM(LO.TOTALWORK))) PERCENT_COMPLETE, MAX(LO.TIME_REMAINING) TIME_REMAINING FROM GV$SESSION_LONGOPS LO, MONITOR_DATA MO WHERE LO.SQL_ID = :B7 AND LO.SQL_EXEC_START = :B6 AND LO.SQL_EXEC_ID = :B5 AND LO.INST_ID = MO.INST_ID AND LO.SID = MO.SID GROUP BY LO.SQL_PLAN_LINE_ID) LO, (SELECT PM0., MAX(LAST_CHANGE_TIME) OVER() OVERALL_LAST_CHANGE_TIME FROM (SELECT /+ leading(md) use_hash(plm) / PLM.PLAN_LINE_ID PLAN_LINE_ID, PLM.PLAN_OPERATION OPERATION, PLM.PLAN_OPTIONS OPTIONS, MIN(PLM.FIRST_CHANGE_TIME) FIRST_CHANGE_TIME, MAX(PLM.LAST_CHANGE_TIME) LAST_CHANGE_TIME, MIN(NVL2(PLM.FIRST_CHANGE_TIME, MO.PX_SERVER_SET, NULL)) PX_SERVER_SET, SUM(PLM.STARTS) STARTS, SUM(PLM.OUTPUT_ROWS) OUTPUT_ROWS, SUM(PLM.WORKAREA_MEM) MEM, SUM(PLM.WORKAREA_MAX_MEM) MAX_MEM, SUM(PLM.WORKAREA_TEMPSEG) TEMP, SUM(PLM.WORKAREA_MAX_TEMPSEG) MAX_TEMP FROM GV$SQL_PLAN_MONITOR PLM, MONITOR_DATA MO WHERE PLM.SQL_ID = :B7 AND PLM.SQL_EXEC_START = :B6 AND PLM.SQL_EXEC_ID = :B5 AND PLM.INST_ID = MO.INST_ID AND PLM.INST_ID = NVL( :B8 , PLM.INST_ID) AND PLM.KEY = MO.KEY GROUP BY PLM.PLAN_LINE_ID, PLM.PLAN_OPERATION, PLM.PLAN_OPTIONS) PM0) PM WHERE AH.PLAN_LINE_ID(+) = PM.PLAN_LINE_ID AND AT.PLAN_LINE_ID(+) = PM.PLAN_LINE_ID AND PL.PLAN_LINE_ID(+) = PM.PLAN_LINE_ID AND LO.PLAN_LINE_ID(+) = PM.PLAN_LINE_ID) ELSE NULL END) FROM DUAL
9tgj4g8y4rwy8	select type#, blocks, extents, minexts, maxexts, extsize, extpct, user#, iniexts, NVL(lists, 65535), NVL(groups, 65535), cachehint, hwmincr, NVL(spare1, 0), NVL(scanhint, 0), NVL(bitmapranges, 0) from seg$ where ts#=:1 and file#=:2 and block#=:3
a6r9zzu06tudh	select /* one / /+ parallel (t1, 16) parallel (t2, 16) */ min(t1.BSNS_UNIT_KEY + t2.BSNS_UNIT_KEY ) , max(t1.DAY_KEY + t2.DAY_KEY), avg(t1.DAY_KEY + t2.DAY_KEY), max(t1.BSNS_UNIT_TYP_CD ), max(t2.CURR_IND) , max(t1.LOAD_DT) from retail.DWB_RTL_TRX t1 , retail.DWB_RTL_TRX t2 where t1.TRX_NBR = t2.TRX_NBR
aqb79nn78dgyy	SELECT inst_id, service_name, TO_CHAR(CAST(begin_time AS TIMESTAMP) AT TIME ZONE 'GMT', 'YYYY-MM-DD HH24:MI:SS TZD') time, TO_CHAR(CAST(end_time AS TIMESTAMP) AT TIME ZONE 'GMT', 'YYYY-MM-DD HH24:MI:SS TZD') time, intsize_csec, group_id, cpupercall, dbtimepercall, callspersec, dbtimepersec FROM gv$servicemetric_history WHERE service_name = :p1 AND end_time >= SYSDATE - 5/(60*24) AND group_id = 6 ORDER BY inst_id asc, end_time DESC
arx2rgss6k33m	SELECT s.inst_id, s.name, i.instance_name FROM gv$active_services s, gv$instance i WHERE s.name = :p1 AND s.inst_id = i.inst_id
bunssq950snhf	insert into wrh$_sga_target_advice (snap_id, dbid, instance_number, SGA_SIZE, SGA_SIZE_FACTOR, ESTD_DB_TIME, ESTD_PHYSICAL_READS) select :snap_id, :dbid, :instance_number, SGA_SIZE, SGA_SIZE_FACTOR, ESTD_DB_TIME, ESTD_PHYSICAL_READS from v$sga_target_advice
bvf3fxv3hatw7	/* OracleOEM */ SELECT PROPAGATION_NAME, TOTAL_NUMBER, TOTAL_BYTES/1024 KBYTES FROM DBA_PROPAGATION P, DBA_QUEUE_SCHEDULES Q WHERE P.SOURCE_QUEUE_NAME = Q.QNAME AND P.SOURCE_QUEUE_OWNER = Q.SCHEMA AND MESSAGE_DELIVERY_MODE='BUFFERED'
cvn54b7yz0s8u	select /+ index(idl_ub1$ i_idl_ub11) +/ piece#, length, piece from idl_ub1$ where obj#=:1 and part=:2 and version=:3 order by piece#
cxjqbfn0d3yqq	SELECT COUNT(*) FROM SYS.DBA_PROPAGATION
d1dumhajv2rr5	SELECT COMPONENT_ID FROM SYS. "_REPORT_COMPONENT_OBJECTS" WHERE COMPONENT_NAME = LOWER(:B1 )
f80h0xb1qvbsk	SELECT sys.wri$_adv_seq_msggroup.nextval FROM dual
fgqt3gak6vv8x	SELECT instance_name FROM gv$instance WHERE inst_id = :p1
fyddnrs5cctsu	/* OracleOEM */ SELECT PROPAGATION_NAME, NUM_MSGS READY FROM V$BUFFERED_SUBSCRIBERS, DBA_PROPAGATION WHERE SUBSCRIBER_NAME IS NULL AND SUBSCRIBER_ADDRESS = DESTINATION_DBLINK AND QUEUE_SCHEMA = SOURCE_QUEUE_OWNER AND QUEUE_NAME = SOURCE_QUEUE_NAME
g12bmbazvj86x	SELECT TASK_LIST.TASK_ID FROM (SELECT /+ NO_MERGE(T) ORDERED / T.TASK_ID FROM (SELECT * FROM DBA_ADVISOR_TASKS ORDER BY TASK_ID DESC) T, DBA_ADVISOR_PARAMETERS_PROJ P1, DBA_ADVISOR_PARAMETERS_PROJ P2 WHERE T.ADVISOR_NAME='ADDM' AND T.STATUS = 'COMPLETED' AND T.EXECUTION_START >= (SYSDATE - 1) AND T.HOW_CREATED = 'AUTO' AND T.TASK_ID = P1.TASK_ID AND P1.PARAMETER_NAME = 'INSTANCE' AND P1.PARAMETER_VALUE = 'UNUSED' AND T.TASK_ID = P2.TASK_ID AND P2.PARAMETER_NAME = 'DB_ID' AND P2.PARAMETER_VALUE = TO_CHAR(:B1 ) ORDER BY T.TASK_ID DESC) TASK_LIST WHERE ROWNUM = 1
grwydz59pu6mc	select text from view$ where rowid=:1
gxxa073u093s4	/* OracleOEM */ SELECT PROPAGATION_NAME, TOTAL_NUMBER, TOTAL_BYTES/1024 KBYTES FROM DBA_PROPAGATION P, DBA_QUEUE_SCHEDULES Q WHERE P.SOURCE_QUEUE_NAME = Q.QNAME AND P.SOURCE_QUEUE_OWNER = Q.SCHEMA AND MESSAGE_DELIVERY_MODE='PERSISTENT'

Back to SQL Statistics
Back to Top

Instance Activity Statistics

Instance Activity Stats
Instance Activity Stats - Absolute Values
Instance Activity Stats - Thread Activity

Instance Activity Stats

Statistic	Total	per Second	per Trans
Batched IO (bound) vector count	4	0.01	0.06
Batched IO (full) vector count	0	0.00	0.00
Batched IO block miss count	53	0.18	0.78
Batched IO buffer defrag count	0	0.00	0.00
Batched IO double miss count	15	0.05	0.22
Batched IO same unit count	20	0.07	0.29
Batched IO single block count	4	0.01	0.06
Batched IO vector block count	22	0.07	0.32
Batched IO vector read count	9	0.03	0.13
Block Cleanout Optim referenced	21	0.07	0.31
CCursor + sql area evicted	1	0.00	0.01
CPU used by this session	143,614	482.79	2,111.97
CPU used when call started	287,177	965.42	4,223.19
CR blocks created	15	0.05	0.22
Cached Commit SCN referenced	0	0.00	0.00
Commit SCN cached	25	0.08	0.37
DB time	860,023	2,891.18	12,647.40
DBWR checkpoint buffers written	11	0.04	0.16
DBWR checkpoints	7	0.02	0.10
DBWR fusion writes	8	0.03	0.12
DBWR object drop buffers written	3	0.01	0.04
DBWR transaction table writes	4	0.01	0.06
DBWR undo block writes	8	0.03	0.12
DFO trees parallelized	145	0.49	2.13
DML statements parallelized	0	0.00	0.00
Effective IO time	8,605	28.93	126.54
HSC Compressed Segment Block Changes	0	0.00	0.00
HSC Heap Segment Block Changes	670	2.25	9.85
HSC IDL Compressed Blocks	0	0.00	0.00
Heap Segment Array Inserts	110	0.37	1.62
Heap Segment Array Updates	31	0.10	0.46
LOB table id lookup cache misses	0	0.00	0.00
Misses for writing mapping	0	0.00	0.00
Number of read IOs issued	3	0.01	0.04
PX local messages recv'd	2,818,693	9,475.74	41,451.37
PX local messages sent	2,818,693	9,475.74	41,451.37
PX remote messages recv'd	2,436	8.19	35.82
PX remote messages sent	2,520	8.47	37.06
Parallel operations downgraded 1 to 25 pct	0	0.00	0.00
Parallel operations downgraded to serial	1	0.00	0.01
Parallel operations not downgraded	145	0.49	2.13
RowCR attempts	6	0.02	0.09
RowCR hits	6	0.02	0.09
SMON posted for instance recovery	0	0.00	0.00
SMON posted for undo segment recovery	0	0.00	0.00
SMON posted for undo segment shrink	0	0.00	0.00
SQL*Net roundtrips to/from client	539	1.81	7.93
active txn count during cleanout	252	0.85	3.71
application wait time	24	0.08	0.35
background checkpoints completed	0	0.00	0.00
background checkpoints started	0	0.00	0.00
background timeouts	1,833	6.16	26.96
branch node splits	0	0.00	0.00
buffer is not pinned count	10,926	36.73	160.68
buffer is pinned count	30,415	102.25	447.28
bytes received via SQL*Net from client	438,449	1,473.96	6,447.78
bytes sent via SQL*Net to client	244,025	820.35	3,588.60
calls to get snapshot scn: kcmgss	6,475	21.77	95.22
calls to kcmgas	7,985	26.84	117.43
calls to kcmgcs	512	1.72	7.53
cell blocks helped by minscn optimization	7,652,952	25,727.32	112,543.41
cell blocks processed by cache layer	7,652,952	25,727.32	112,543.41
cell blocks processed by data layer	7,652,952	25,727.32	112,543.41
cell blocks processed by txn layer	7,652,952	25,727.32	112,543.41
cell physical IO bytes eligible for predicate offload	62,484,545,536	210,057,504.56	918,890,375.53
cell physical IO interconnect bytes	175,629,638,544	590,423,172.36	2,582,788,802.12
cell scans	558	1.88	8.21
change write time	2	0.01	0.03
cleanout - number of ktugct calls	197	0.66	2.90
cleanouts and rollbacks - consistent read gets	12	0.04	0.18
cleanouts only - consistent read gets	22	0.07	0.32
cluster key scan block gets	285	0.96	4.19
cluster key scans	263	0.88	3.87
cluster wait time	51	0.17	0.75
commit batch performed	0	0.00	0.00
commit batch requested	0	0.00	0.00
commit batch/immediate performed	0	0.00	0.00
commit batch/immediate requested	0	0.00	0.00
commit cleanout failures: block lost	20	0.07	0.29
commit cleanout failures: buffer being written	0	0.00	0.00
commit cleanout failures: callback failure	3	0.01	0.04
commit cleanout failures: cannot pin	0	0.00	0.00
commit cleanouts	574	1.93	8.44
commit cleanouts successfully completed	551	1.85	8.10
commit immediate performed	0	0.00	0.00
commit immediate requested	0	0.00	0.00
commit txn count during cleanout	126	0.42	1.85
concurrency wait time	5,024	16.89	73.88
consistent changes	7,332	24.65	107.82
consistent gets	7,665,099	25,768.16	112,722.04
consistent gets - examination	5,923	19.91	87.10
consistent gets direct	7,627,513	25,641.80	112,169.31
consistent gets from cache	37,586	126.35	552.74
consistent gets from cache (fastpath)	30,032	100.96	441.65
cursor authentications	96	0.32	1.41
data blocks consistent reads - undo records applied	15	0.05	0.22
db block changes	11,889	39.97	174.84
db block gets	9,649	32.44	141.90
db block gets direct	18	0.06	0.26
db block gets from cache	9,631	32.38	141.63
db block gets from cache (fastpath)	1,397	4.70	20.54
deferred (CURRENT) block cleanout applications	247	0.83	3.63
dirty buffers inspected	0	0.00	0.00
enqueue conversions	2,476	8.32	36.41
enqueue releases	55,514	186.62	816.38
enqueue requests	55,705	187.27	819.19
enqueue timeouts	191	0.64	2.81
enqueue waits	1,954	6.57	28.74
exchange deadlocks	1	0.00	0.01
execute count	2,574	8.65	37.85
failed probes on index block reclamation	0	0.00	0.00
free buffer inspected	4,924	16.55	72.41
free buffer requested	9,304	31.28	136.82
gc CPU used by this session	37	0.12	0.54
gc blocks lost	0	0.00	0.00
gc cr block build time	0	0.00	0.00
gc cr block flush time	1	0.00	0.01
gc cr block receive time	41	0.14	0.60
gc cr block send time	0	0.00	0.00
gc cr blocks received	1,060	3.56	15.59
gc cr blocks served	150	0.50	2.21
gc current block flush time	0	0.00	0.00
gc current block pin time	0	0.00	0.00
gc current block receive time	21	0.07	0.31
gc current block send time	0	0.00	0.00
gc current blocks received	660	2.22	9.71
gc current blocks served	311	1.05	4.57
gc local grants	75	0.25	1.10
gc reader bypass grants	43	0.14	0.63
gc remote grants	258	0.87	3.79
gcs messages sent	4,534	15.24	66.68
ges messages sent	11,638	39.12	171.15
global enqueue get time	892	3.00	13.12
global enqueue gets async	502	1.69	7.38
global enqueue gets sync	60,523	203.46	890.04
global enqueue releases	58,433	196.44	859.31
heap block compress	15	0.05	0.22
hot buffers moved to head of LRU	2	0.01	0.03
immediate (CR) block cleanout applications	34	0.11	0.50
immediate (CURRENT) block cleanout applications	207	0.70	3.04
index crx upgrade (positioned)	241	0.81	3.54
index crx upgrade (prefetch)	0	0.00	0.00
index fast full scans (full)	4	0.01	0.06
index fetch by key	2,669	8.97	39.25
index scans kdiixs1	5,767	19.39	84.81
leaf node 90-10 splits	0	0.00	0.00
leaf node splits	51	0.17	0.75
lob reads	1	0.00	0.01
lob writes	45	0.15	0.66
lob writes unaligned	45	0.15	0.66
logons cumulative	404	1.36	5.94
messages received	15,478	52.03	227.62
messages sent	15,478	52.03	227.62
min active SCN optimization applied to CR	0	0.00	0.00
no buffer to keep pinned count	0	0.00	0.00
no work - consistent read gets	27,559	92.65	405.28
opened cursors cumulative	2,471	8.31	36.34
parse count (describe)	11	0.04	0.16
parse count (failures)	10	0.03	0.15
parse count (hard)	221	0.74	3.25
parse count (total)	1,616	5.43	23.76
parse time cpu	36	0.12	0.53
parse time elapsed	101	0.34	1.49
physical IO disk bytes	205,015,705,600	689,211,822.61	3,014,936,847.06
physical read IO requests	274,018	921.18	4,029.68
physical read bytes	109,940,178,944	369,591,543.66	1,616,767,337.41
physical read total IO requests	276,215	928.57	4,061.99
physical read total bytes	109,975,773,184	369,711,202.65	1,617,290,782.12
physical read total multi block requests	272,024	914.48	4,000.35
physical reads	13,420,432	45,116.16	197,359.29
physical reads cache	104	0.35	1.53
physical reads cache prefetch	41	0.14	0.60
physical reads direct	13,420,328	45,115.81	197,357.76
physical reads direct (lob)	5	0.02	0.07
physical reads direct temporary tablespace	5,792,815	19,474.00	85,188.46
physical reads prefetch warmup	0	0.00	0.00
physical write IO requests	194,148	652.68	2,855.12
physical write bytes	47,510,626,304	159,718,911.55	698,685,680.94
physical write total IO requests	194,571	654.10	2,861.34
physical write total bytes	47,517,361,152	159,741,552.43	698,784,722.82
physical write total multi block requests	186,851	628.15	2,747.81
physical writes	5,799,637	19,496.94	85,288.78
physical writes direct	5,792,337	19,472.40	85,181.43
physical writes direct (lob)	0	0.00	0.00
physical writes direct temporary tablespace	5,792,326	19,472.36	85,181.26
physical writes from cache	7,300	24.54	107.35
physical writes non checkpoint	5,792,355	19,472.46	85,181.69
pinned buffers inspected	4,847	16.29	71.28
prefetch warmup blocks aged out before use	0	0.00	0.00
prefetched blocks aged out before use	0	0.00	0.00
process last non-idle time	0	0.00	0.00
queries parallelized	123	0.41	1.81
recovery blocks read	0	0.00	0.00
recursive calls	68,007	228.62	1,000.10
recursive cpu usage	142,253	478.22	2,091.96
redo blocks checksummed by FG (exclusive)	1,816	6.10	26.71
redo blocks read for recovery	0	0.00	0.00
redo blocks written	2,978	10.01	43.79
redo buffer allocation retries	0	0.00	0.00
redo entries	2,565	8.62	37.72
redo k-bytes read total	0	0.00	0.00
redo log space requests	0	0.00	0.00
redo log space wait time	0	0.00	0.00
redo ordering marks	138	0.46	2.03
redo size	1,926,632	6,476.86	28,332.82
redo size for direct writes	104	0.35	1.53
redo subscn max counts	150	0.50	2.21
redo synch time	5	0.02	0.07
redo synch writes	6	0.02	0.09
redo wastage	25,484	85.67	374.76
redo write broadcast ack time	6	0.02	0.09
redo write time	15	0.05	0.22
redo writer latching time	0	0.00	0.00
redo writes	105	0.35	1.54
rollback changes - undo records applied	0	0.00	0.00
rollbacks only - consistent read gets	3	0.01	0.04
rows fetched via callback	1,476	4.96	21.71
session connect time	0	0.00	0.00
session logical reads	7,674,748	25,800.59	112,863.94
shared hash latch upgrades - no wait	457	1.54	6.72
shared hash latch upgrades - wait	0	0.00	0.00
sorts (memory)	1,192	4.01	17.53
sorts (rows)	23,863	80.22	350.93
sql area evicted	400	1.34	5.88
sql area purged	12	0.04	0.18
summed dirty queue length	0	0.00	0.00
switch current to new buffer	6	0.02	0.09
table fetch by rowid	18,860	63.40	277.35
table fetch continued row	11	0.04	0.16
table scan blocks gotten	7,630,054	25,650.34	112,206.68
table scan rows gotten	937,301,524	3,150,974.65	13,783,845.94
table scans (direct read)	558	1.88	8.21
table scans (long tables)	558	1.88	8.21
table scans (rowid ranges)	558	1.88	8.21
table scans (short tables)	157	0.53	2.31
total number of times SMON posted	59	0.20	0.87
transaction lock background gets	0	0.00	0.00
transaction lock foreground requests	0	0.00	0.00
transaction rollbacks	0	0.00	0.00
transaction tables consistent reads - undo records applied	0	0.00	0.00
undo change vector size	715,324	2,404.74	10,519.47
user I/O wait time	142,285	478.33	2,092.43
user calls	2,576	8.66	37.88
user commits	37	0.12	0.54
user rollbacks	31	0.10	0.46
workarea executions - onepass	16	0.05	0.24
workarea executions - optimal	1,167	3.92	17.16
write clones created in background	7,278	24.47	107.03
write clones created in foreground	0	0.00	0.00

Back to Instance Activity Statistics
Back to Top

Instance Activity Stats - Absolute Values

Statistics with absolute values (should not be diffed)

Statistic	Begin Value	End Value
session uga memory max	50,742,489,184	58,367,714,240
session pga memory	242,108,365,336	242,657,297,832
session pga memory max	639,920,311,448	649,207,190,696
session cursor cache count	18,446,744,073,709,381,460	18,446,744,073,709,380,981
session uga memory	3,152,296,765,976	3,156,611,989,520
opened cursors current	72	74
workarea memory allocated	0	347
logons current	45	56

Back to Instance Activity Statistics
Back to Top

Instance Activity Stats - Thread Activity

Statistics identified by '(derived)' come from sources other than SYSSTAT

Statistic	Total	per Hour
log switches (derived)	0	0.00

Back to Instance Activity Statistics
Back to Top

IO Stats

Tablespace IO Stats
File IO Stats

Tablespace IO Stats

ordered by IOs (Reads + Writes) desc

Tablespace	Reads	Av Reads/s	Av Rd(ms)	Av Blks/Rd	Writes	Av Writes/s	Buffer Waits	Av Buf Wt(ms)
TEMP	186,711	628	2.38	31.00	194,059	652	18,161	2.68
SYSTEM	33	0	4.85	1.00	3	0	0	0.00
SYSAUX	19	0	3.68	1.21	6	0	0	0.00
UNDOTBS1	0	0	0.00	0.00	7	0	0	0.00
RETAIL_20090501	1	0	0.00	1.00	0	0	0	0.00
RETAIL_20090502	1	0	0.00	1.00	0	0	0	0.00
RETAIL_20090503	1	0	0.00	1.00	0	0	0	0.00
RETAIL_20090504	1	0	0.00	1.00	0	0	0	0.00
RETAIL_20090505	1	0	0.00	1.00	0	0	0	0.00
RETAIL_20090506	1	0	0.00	1.00	0	0	0	0.00
RETAIL_20090507	1	0	0.00	1.00	0	0	0	0.00
RETAIL_20090508	1	0	0.00	1.00	0	0	0	0.00
RETAIL_20090509	1	0	20.00	1.00	0	0	0	0.00
RETAIL_20090510	1	0	0.00	1.00	0	0	0	0.00
RETAIL_20090511	1	0	0.00	1.00	0	0	0	0.00
RETAIL_20090512	1	0	0.00	1.00	0	0	0	0.00
RETAIL_20090513	1	0	10.00	1.00	0	0	0	0.00
RETAIL_20090514	1	0	0.00	1.00	0	0	0	0.00
RETAIL_20090515	1	0	10.00	1.00	0	0	0	0.00
RETAIL_20090516	1	0	0.00	1.00	0	0	0	0.00
RETAIL_20090517	1	0	10.00	1.00	0	0	0	0.00
RETAIL_20090518	1	0	10.00	1.00	0	0	0	0.00
RETAIL_20090519	1	0	0.00	1.00	0	0	0	0.00
RETAIL_20090520	1	0	0.00	1.00	0	0	0	0.00
RETAIL_20090521	1	0	0.00	1.00	0	0	0	0.00
RETAIL_20090522	1	0	10.00	1.00	0	0	0	0.00
RETAIL_20090523	1	0	0.00	1.00	0	0	0	0.00
RETAIL_20090524	1	0	0.00	1.00	0	0	0	0.00
RETAIL_20090525	1	0	10.00	1.00	0	0	0	0.00
RETAIL_20090526	1	0	0.00	1.00	0	0	0	0.00
RETAIL_20090527	1	0	10.00	1.00	0	0	0	0.00
RETAIL_20090528	1	0	0.00	1.00	0	0	0	0.00
RETAIL_20090529	1	0	0.00	1.00	0	0	0	0.00
RETAIL_20090530	1	0	0.00	1.00	0	0	0	0.00
RETAIL_20090531	1	0	10.00	1.00	0	0	0	0.00

Back to IO Stats
Back to Top

File IO Stats

ordered by Tablespace, File

Tablespace	Filename	Reads	Av Reads/s	Av Rd(ms)	Av Blks/Rd	Writes	Av Writes/s	Buffer Waits	Av Buf Wt(ms)
RETAIL_20090501	+DGA/retail_20090501.dbf	1	0	0.00	1.00	0	0	0	0.00
RETAIL_20090502	+DGA/retail_20090502.dbf	1	0	0.00	1.00	0	0	0	0.00
RETAIL_20090503	+DGA/retail_20090503.dbf	1	0	0.00	1.00	0	0	0	0.00
RETAIL_20090504	+DGA/retail_20090504.dbf	1	0	0.00	1.00	0	0	0	0.00
RETAIL_20090505	+DGA/retail_20090505.dbf	1	0	0.00	1.00	0	0	0	0.00
RETAIL_20090506	+DGA/retail_20090506.dbf	1	0	0.00	1.00	0	0	0	0.00
RETAIL_20090507	+DGA/retail_20090507.dbf	1	0	0.00	1.00	0	0	0	0.00
RETAIL_20090508	+DGA/retail_20090508.dbf	1	0	0.00	1.00	0	0	0	0.00
RETAIL_20090509	+DGA/retail_20090509.dbf	1	0	20.00	1.00	0	0	0	0.00
RETAIL_20090510	+DGA/retail_20090510.dbf	1	0	0.00	1.00	0	0	0	0.00
RETAIL_20090511	+DGA/retail_20090511.dbf	1	0	0.00	1.00	0	0	0	0.00
RETAIL_20090512	+DGA/retail_20090512.dbf	1	0	0.00	1.00	0	0	0	0.00
RETAIL_20090513	+DGA/retail_20090513.dbf	1	0	10.00	1.00	0	0	0	0.00
RETAIL_20090514	+DGA/retail_20090514.dbf	1	0	0.00	1.00	0	0	0	0.00
RETAIL_20090515	+DGA/retail_20090515.dbf	1	0	10.00	1.00	0	0	0	0.00
RETAIL_20090516	+DGA/retail_20090516.dbf	1	0	0.00	1.00	0	0	0	0.00
RETAIL_20090517	+DGA/retail_20090517.dbf	1	0	10.00	1.00	0	0	0	0.00
RETAIL_20090518	+DGA/retail_20090518.dbf	1	0	10.00	1.00	0	0	0	0.00
RETAIL_20090519	+DGA/retail_20090519.dbf	1	0	0.00	1.00	0	0	0	0.00
RETAIL_20090520	+DGA/retail_20090520.dbf	1	0	0.00	1.00	0	0	0	0.00
RETAIL_20090521	+DGA/retail_20090521.dbf	1	0	0.00	1.00	0	0	0	0.00
RETAIL_20090522	+DGA/retail_20090522.dbf	1	0	10.00	1.00	0	0	0	0.00
RETAIL_20090523	+DGA/retail_20090523.dbf	1	0	0.00	1.00	0	0	0	0.00
RETAIL_20090524	+DGA/retail_20090524.dbf	1	0	0.00	1.00	0	0	0	0.00
RETAIL_20090525	+DGA/retail_20090525.dbf	1	0	10.00	1.00	0	0	0	0.00
RETAIL_20090526	+DGA/retail_20090526.dbf	1	0	0.00	1.00	0	0	0	0.00
RETAIL_20090527	+DGA/retail_20090527.dbf	1	0	10.00	1.00	0	0	0	0.00
RETAIL_20090528	+DGA/retail_20090528.dbf	1	0	0.00	1.00	0	0	0	0.00
RETAIL_20090529	+DGA/retail_20090529.dbf	1	0	0.00	1.00	0	0	0	0.00
RETAIL_20090530	+DGA/retail_20090530.dbf	1	0	0.00	1.00	0	0	0	0.00
RETAIL_20090531	+DGA/retail_20090531.dbf	1	0	10.00	1.00	0	0	0	0.00
SYSAUX	+DGA/demo/datafile/sysaux.417.686904815	19	0	3.68	1.21	6	0	0	0.00
SYSTEM	+DGA/demo/datafile/system.416.686904813	33	0	4.85	1.00	3	0	0	0.00
TEMP	+DGA/demo/tempfile/temp.419.686904819	186,711	628	2.38	31.00	194,059	652	18,161	2.68
UNDOTBS1	+DGA/demo/datafile/undotbs1.418.686904817	0	0			7	0	0	0.00

Back to IO Stats
Back to Top

Buffer Pool Statistics

Standard block size Pools D: default, K: keep, R: recycle
Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32k

P	Number of Buffers	Pool Hit%	Buffer Gets	Physical Reads	Physical Writes	Free Buff Wait	Writ Comp Wait	Buffer Busy Waits
D	563,066	100	47,167	105	7,300	0	0	18,166

Advisory Statistics

Instance Recovery Stats
Buffer Pool Advisory
PGA Aggr Summary
PGA Aggr Target Stats
PGA Aggr Target Histogram
PGA Memory Advisory
Shared Pool Advisory
SGA Target Advisory
Streams Pool Advisory
Java Pool Advisory

Instance Recovery Stats

B: Begin snapshot, E: End snapshot

	Targt MTTR (s)	Estd MTTR (s)	Recovery Estd IOs	Actual Redo Blks	Target Redo Blks	Log File Size Redo Blks	Log Ckpt Timeout Redo Blks	Log Ckpt Interval Redo Blks
B	0	0	205	1111	4757	1887390	4757
E	0	1	925	4071	7815	1887390	7815

Back to Advisory Statistics
Back to Top

Buffer Pool Advisory

Only rows with estimated physical reads >0 are displayed
ordered by Block Size, Buffers For Estimate

P	Size for Est (M)	Size Factor	Buffers for Estimate	Est Phys Read Factor	Estimated Physical Reads
D	448	0.09	53,263	1.89	24,326,918
D	896	0.19	106,526	1.12	14,393,308
D	1,344	0.28	159,789	1.04	13,334,199
D	1,792	0.38	213,052	1.00	12,873,371
D	2,240	0.47	266,315	1.00	12,868,776
D	2,688	0.57	319,578	1.00	12,863,201
D	3,136	0.66	372,841	1.00	12,853,842
D	3,584	0.76	426,104	1.00	12,853,643
D	4,032	0.85	479,367	1.00	12,852,295
D	4,480	0.95	532,630	1.00	12,851,805
D	4,736	1.00	563,066	1.00	12,848,420
D	4,928	1.04	585,893	1.00	12,847,578
D	5,376	1.14	639,156	1.00	12,847,532
D	5,824	1.23	692,419	1.00	12,847,394
D	6,272	1.32	745,682	1.00	12,844,484
D	6,720	1.42	798,945	1.00	12,817,081
D	7,168	1.51	852,208	1.00	12,803,909
D	7,616	1.61	905,471	0.95	12,175,422
D	8,064	1.70	958,734	0.93	11,985,215
D	8,512	1.80	1,011,997	0.86	11,044,169
D	8,960	1.89	1,065,260	0.86	11,041,504

Back to Advisory Statistics
Back to Top

PGA Aggr Summary

PGA cache hit % - percentage of W/A (WorkArea) data processed only in-memory

PGA Cache Hit %	W/A MB Processed	Extra W/A MB Read/Written
50.06	29,123	29,051

Back to Advisory Statistics
Back to Top

PGA Aggr Target Stats

B: Begin snap E: End snap (rows dentified with B or E contain data which is absolute i.e. not diffed over the interval)
Auto PGA Target - actual workarea memory target
W/A PGA Used - amount of memory used for all Workareas (manual + auto)
%PGA W/A Mem - percentage of PGA memory allocated to workareas
%Auto W/A Mem - percentage of workarea memory controlled by Auto Mem Mgmt
%Man W/A Mem - percentage of workarea memory under manual control

	PGA Aggr Target(M)	Auto PGA Target(M)	PGA Mem Alloc(M)	W/A PGA Used(M)	%PGA W/A Mem	%Auto W/A Mem	%Man W/A Mem	Global Mem Bound(K)
B	12,288	10,859	473.40	0.00	0.00	0.00	0.00	1,048,576
E	12,288	10,586	565.83	0.34	0.06	100.00	0.00	1,048,576

Back to Advisory Statistics
Back to Top

PGA Aggr Target Histogram

Optimal Executions are purely in-memory operations

Low Optimal	High Optimal	Total Execs	Optimal Execs	1-Pass Execs
2K	4K	1,075	1,075	0
64K	128K	10	10	0
256K	512K	4	4	0
512K	1024K	61	61	0
1M	2M	17	17	0
1G	2G	16	0	16

Back to Advisory Statistics
Back to Top

PGA Memory Advisory

When using Auto Memory Mgmt, minimally choose a pga_aggregate_target value where Estd PGA Overalloc Count is 0

PGA Target Est (MB)	Size Factr	W/A MB Processed	Estd Extra W/A MB Read/ Written to Disk	Estd PGA Cache Hit %	Estd Time
1,536	0.13	17,166.12	505.83	97.00	6,197,633
3,072	0.25	17,166.12	505.83	97.00	6,197,633
6,144	0.50	17,166.12	505.83	97.00	6,197,633
9,216	0.75	17,166.12	505.83	97.00	6,197,633
12,288	1.00	17,166.12	505.83	97.00	6,197,633
14,746	1.20	17,166.12	0.00	100.00	6,020,236
17,203	1.40	17,166.12	0.00	100.00	6,020,236
19,661	1.60	17,166.12	0.00	100.00	6,020,236
22,118	1.80	17,166.12	0.00	100.00	6,020,236
24,576	2.00	17,166.12	0.00	100.00	6,020,236
36,864	3.00	17,166.12	0.00	100.00	6,020,236
49,152	4.00	17,166.12	0.00	100.00	6,020,236
73,728	6.00	17,166.12	0.00	100.00	6,020,236
98,304	8.00	17,166.12	0.00	100.00	6,020,236

Back to Advisory Statistics
Back to Top

Shared Pool Advisory

SP: Shared Pool Est LC: Estimated Library Cache Factr: Factor
Note there is often a 1:Many correlation between a single logical object in the Library Cache, and the physical number of memory objects associated with it. Therefore comparing the number of Lib Cache objects (e.g. in v$librarycache), with the number of Lib Cache Memory Objects is invalid.

Shared Pool Size(M)	SP Size Factr	Est LC Size (M)	Est LC Mem Obj	Est LC Time Saved (s)	Est LC Time Saved Factr	Est LC Load Time (s)	Est LC Load Time Factr	Est LC Mem Obj Hits (K)
960	0.88	62	3,791	14,013	1.00	271	1.03	1,042
1,088	1.00	190	11,173	14,022	1.00	262	1.00	2,168
1,216	1.12	313	17,762	14,033	1.00	251	0.96	2,170
1,344	1.24	378	21,364	14,035	1.00	249	0.95	2,170
1,472	1.35	382	21,708	14,035	1.00	249	0.95	2,170
1,600	1.47	382	21,708	14,035	1.00	249	0.95	2,170
1,728	1.59	382	21,708	14,035	1.00	249	0.95	2,170
1,856	1.71	382	21,708	14,035	1.00	249	0.95	2,170
1,984	1.82	382	21,708	14,035	1.00	249	0.95	2,170
2,112	1.94	382	21,708	14,035	1.00	249	0.95	2,170
2,240	2.06	382	21,708	14,035	1.00	249	0.95	2,170

Back to Advisory Statistics
Back to Top

SGA Target Advisory

SGA Target Size (M)	SGA Size Factor	Est DB Time (s)	Est Physical Reads
2,304	0.38	86,283	14,392,724
3,072	0.50	83,568	12,872,764
3,840	0.63	83,543	12,868,909
4,608	0.75	83,518	12,853,491
5,376	0.88	83,518	12,852,207
6,144	1.00	83,510	12,848,352
6,912	1.13	83,510	12,847,067
7,680	1.25	83,477	12,817,516
8,448	1.38	83,426	12,803,383
9,216	1.50	83,393	12,175,098
9,984	1.63	83,393	11,984,943
10,752	1.75	83,393	11,041,874
11,520	1.88	83,393	11,041,874
12,288	2.00	83,393	11,041,874

Back to Advisory Statistics
Back to Top

Streams Pool Advisory

Size for Est (MB)	Size Factor	Est Spill Count	Est Spill Time (s)	Est Unspill Count	Est Unspill Time (s)
64	0.50	0	0	0	0
128	1.00	0	0	0	0
192	1.50	0	0	0	0
256	2.00	0	0	0	0
320	2.50	0	0	0	0
384	3.00	0	0	0	0
448	3.50	0	0	0	0
512	4.00	0	0	0	0
576	4.50	0	0	0	0
640	5.00	0	0	0	0
704	5.50	0	0	0	0
768	6.00	0	0	0	0
832	6.50	0	0	0	0
896	7.00	0	0	0	0
960	7.50	0	0	0	0
1,024	8.00	0	0	0	0
1,088	8.50	0	0	0	0
1,152	9.00	0	0	0	0
1,216	9.50	0	0	0	0
1,280	10.00	0	0	0	0

Back to Advisory Statistics
Back to Top

Java Pool Advisory

No data exists for this section of the report.

Back to Advisory Statistics
Back to Top

Wait Statistics

Buffer Wait Statistics
Enqueue Activity

Buffer Wait Statistics

ordered by wait time desc, waits desc

Class	Waits	Total Wait Time (s)	Avg Time (ms)
file header block	18,137	49	3
bitmap index block	25	0	0

Back to Wait Statistics
Back to Top

Enqueue Activity

only enqueues with waits are shown
Enqueue stats gathered prior to 10g should not be compared with 10g data
ordered by Wait Time desc, Waits desc

Enqueue Type (Request Reason)	Requests	Succ Gets	Failed Gets	Waits	Wt Time (s)	Av Wt Time(ms)
PS-PX Process Reservation	4,128	3,939	189	1,842	3	1.66
PV-KSV slave startup (syncstart)	16	16	0	15	2	106.67
WF-AWR Flush	21	21	0	20	0	18.50
XL-ASM Extent Fault Lock (fault extent map)	37	37	0	2	0	25.00
RO-Multiple Object Reuse (fast object reuse)	54	54	0	23	0	1.30
TO-Temp Object	7	7	0	4	0	2.50
TM-DML	951	951	0	2	0	5.00
TD-KTF map table enqueue (KTF dump entries)	1	1	0	1	0	10.00
CF-Controlfile Transaction	637	637	0	13	0	0.00
PI-Remote PX Process Spawn Status	18	18	0	10	0	0.00
FB-Format Block	5	5	0	4	0	0.00
KO-Multiple Object Checkpoint (fast object checkpoint)	9	9	0	4	0	0.00
AF-Advisor Framework (task serialization)	13	13	0	2	0	0.00
HW-Segment High Water Mark	9	9	0	2	0	0.00
JQ-Job Queue	2	1	1	2	0	0.00
TA-Instance Undo	2	2	0	2	0	0.00
TX-Transaction (index contention)	2	2	0	2	0	0.00
TQ-Queue table enqueue (TM contention)	1	1	0	1	0	0.00
TT-Tablespace	46,792	46,792	0	1	0	0.00

Back to Wait Statistics
Back to Top

Undo Statistics

Undo Segment Summary
Undo Segment Stats

Undo Segment Summary

No data exists for this section of the report.

Back to Undo Statistics
Back to Top

Undo Segment Stats

No data exists for this section of the report.

Back to Undo Statistics
Back to Top

Latch Statistics

Latch Activity
Latch Sleep Breakdown
Latch Miss Sources
Mutex Sleep Summary
Parent Latch Statistics
Child Latch Statistics

Latch Activity

"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for willing-to-wait latch get requests
"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests
"Pct Misses" for both should be very close to 0.0

Latch Name	Get Requests	Pct Get Miss	Avg Slps /Miss	Wait Time (s)	NoWait Requests	Pct NoWait Miss
ASM db client latch	332	0.00		0	0
ASM map headers	35	0.00		0	0
ASM map load waiting list	35	0.00		0	0
ASM map operation freelist	11,216	4.98	0.52	1	0
ASM map operation hash table	1,403,187	0.01	0.05	0	0
ASM network background latch	271	0.00		0	0
AWR Alerted Metric Element list	2,269	0.00		0	0
Change Notification Hash table latch	102	0.00		0	0
Consistent RBA	113	0.00		0	0
DML lock allocation	363,138	0.00	0.20	0	0
Event Group Locks	21	0.00		0	0
FOB s.o list latch	406	0.00		0	0
File State Object Pool Parent Latch	1	0.00		0	0
IPC stats buffer allocation latch	1,639	0.00		0	1,669	0.00
In memory undo latch	1	0.00		0	0
JS Sh mem access	1	0.00		0	0
JS broadcast add buf latch	69	0.00		0	0
JS broadcast autostart latch	1	0.00		0	0
JS broadcast drop buf latch	69	0.00		0	0
JS broadcast load blnc latch	60	0.00		0	0
JS queue access latch	1	0.00		0	0
JS queue state obj latch	3,960	0.00		0	0
JS slv state obj latch	7	0.00		0	0
KFC FX Hash Latch	1	0.00		0	0
KFC Hash Latch	1	0.00		0	0
KFCL LE Freelist	1	0.00		0	0
KFK SGA Libload latch	5,236	0.00		0	0
KFMD SGA	16,167	0.54	0.06	0	0
KFR redo allocation latch	1	0.00		0	0
KGNFS-NFS:SHM structure	1	0.00		0	0
KGNFS-NFS:SVR LIST	1	0.00		0	0
KJC message pool free list	2,370	0.13	0.00	0	1,074	0.00
KJCT flow control latch	26,532	0.00	0.00	0	0
KMG MMAN ready and startup request latch	99	0.00		0	0
KSXR large replies	88	0.00		0	0
KTF sga latch	5	0.00		0	93	0.00
KWQMN job cache list latch	35	0.00		0	0
KWQP Prop Status	2	0.00		0	0
KWQS pqsubs latch	21	0.00		0	0
KWQS pqueue ctx latch	9	0.00		0	0
Locator state objects pool parent latch	1	0.00		0	0
MQL Tracking Latch	0			0	6	0.00
Memory Management Latch	1	0.00		0	99	0.00
Memory Queue	1	0.00		0	0
Memory Queue Message Subscriber #1	1	0.00		0	0
Memory Queue Message Subscriber #2	1	0.00		0	0
Memory Queue Message Subscriber #3	1	0.00		0	0
Memory Queue Message Subscriber #4	1	0.00		0	0
Memory Queue Subscriber	1	0.00		0	0
MinActiveScn Latch	4,062	0.00		0	0
Mutex	1	0.00		0	0
Mutex Stats	1	0.00		0	0
OS process	118	0.00		0	0
OS process allocation	190	0.00		0	0
OS process: request allocation	27	0.00		0	0
PL/SQL warning settings	464	0.00		0	0
QMT	1	0.00		0	0
Real-time plan statistics latch	963	0.52	0.80	0	0
SGA blob parent	1	0.00		0	0
SGA bucket locks	1	0.00		0	0
SGA heap locks	1	0.00		0	0
SGA pool locks	1	0.00		0	0
SQL memory manager latch	1	0.00		0	98	0.00
SQL memory manager workarea list latch	7,044	0.00		0	0
Shared B-Tree	51	0.00		0	0
Streams Generic	1	0.00		0	0
Testing	1	0.00		0	0
Token Manager	1	0.00		0	0
Write State Object Pool Parent Latch	1	0.00		0	0
XDB NFS Security Latch	1	0.00		0	0
XDB unused session pool	1	0.00		0	0
XDB used session pool	1	0.00		0	0
active checkpoint queue latch	14,603	0.00		0	0
active service list	17,537	0.08	0.86	0	114	0.00
archive control	33	0.00		0	0
begin backup scn array	2	0.00		0	0
buffer pool	1	0.00		0	0
business card	289	0.00		0	0
cache buffer handles	197	0.51	0.00	0	0
cache buffers chains	189,246	0.39	0.31	0	20,270	0.48
cache buffers lru chain	44,494	0.10	0.00	0	11,956	0.00
cache table scan latch	3	0.00		0	3	0.00
cas latch	1	0.00		0	0
change notification client cache latch	1	0.00		0	0
channel handle pool latch	37	0.00		0	0
channel operations parent latch	226,979	0.00	0.00	0	0
checkpoint queue latch	248,047	0.00		0	733	0.00
client/application info	2,151	0.00		0	0
commit callback allocation	5	0.00		0	0
compile environment latch	405	0.00		0	0
cp cmon/server latch	1	0.00		0	0
cp pool latch	1	0.00		0	0
cp server hash latch	1	0.00		0	0
cp sga latch	6	0.00		0	0
cvmap freelist lock	1	0.00		0	0
deferred cleanup latch	6	0.00		0	0
dml lock allocation	6	0.00		0	0
done queue latch	1	0.00		0	0
dummy allocation	800	0.13	0.00	0	0
enqueue hash chains	215,269	0.44	0.03	0	4	0.00
enqueues	108,429	0.21	0.05	0	0
error message lists	2,491	0.08	0.00	0	0
fifth spare latch	1	0.00		0	0
file cache latch	229	0.00		0	0
flashback archiver latch	1	0.00		0	0
flashback copy	1	0.00		0	0
gc element	20,913	0.00	1.00	0	2	0.00
gcs commit scn state	1	0.00		0	0
gcs opaque info freelist	662	0.00		0	0
gcs partitioned table hash	828,427	0.00		0	45	0.00
gcs pcm hashed value bucket hash	1	0.00		0	0
gcs remaster request queue	38	0.00		0	0
gcs remastering latch	455	0.00		0	0
gcs resource freelist	126	0.00		0	98	0.00
gcs resource hash	20,662	0.00		0	0
gcs resource scan list	1	0.00		0	0
gcs shadows freelist	263	0.00		0	295	0.00
ges caches resource lists	54,547	0.01	0.00	0	5,849	0.00
ges deadlock list	1,402	0.00		0	0
ges domain table	115,705	0.00	0.00	0	0
ges enqueue table freelist	118,177	0.02	0.07	0	0
ges group table	176,332	0.00	0.00	0	0
ges process hash list	977	0.00		0	0
ges process parent latch	241,919	0.00		0	0
ges process table freelist	27	0.00		0	0
ges resource hash list	193,114	0.76	0.14	0	2,896	0.38
ges resource scan list	21	0.00		0	0
ges resource table freelist	4,220	0.00		0	0
ges timeout list	2,066	0.00		0	444	0.00
ges value block free list	1	0.00		0	0
global KZLD latch for mem in SGA	2	0.00		0	0
global tx hash mapping	1	0.00		0	0
granule operation	1	0.00		0	0
hash table column usage latch	0			0	5,525	0.00
hash table modification latch	15	0.00		0	0
heartbeat check	1	0.00		0	60	0.00
internal temp table object number allocation latch	12	0.00		0	0
intra txn parallel recovery	1	0.00		0	0
io pool granule metadata list	1	0.00		0	0
job workq parent latch	1	0.00		0	4	0.00
job_queue_processes parameter latch	68	0.00		0	0
k2q global data latch	198	0.00		0	0
k2q lock allocation	1	0.00		0	0
kcfis stats shared latch	16	0.00		0	0
kdlx hb parent latch	1	0.00		0	0
kgb parent	1	0.00		0	0
kks stats	1,148	0.00		0	0
kokc descriptor allocation latch	292	0.00		0	0
ksfv messages	1	0.00		0	0
ksim membership request latch	1,110	0.00		0	297	0.00
kss move lock	23	0.00		0	0
ksuosstats global area	71	0.00		0	0
ksv allocation latch	39	0.00		0	0
ksv class latch	619	0.00		0	0
ksv instance latch	19	0.00		0	0
ksv msg queue latch	36	0.00		0	35	0.00
ksxp shared latch	27	0.00		0	0
ksz_so allocation latch	71	7.04	0.20	0	0
ktm global data	3	0.00		0	0
kwqbsn:qsga	14	0.00		0	0
lgwr LWN SCN	189	0.00		0	0
list of block allocation	33	0.00		0	0
loader state object freelist	8	0.00		0	0
lob segment dispenser latch	1	0.00		0	0
lob segment hash table latch	1	0.00		0	0
lob segment query latch	1	0.00		0	0
lock DBA buffer during media recovery	1	0.00		0	0
logical standby cache	1	0.00		0	0
logminer context allocation	2	0.00		0	0
logminer work area	1	0.00		0	0
longop free list parent	77	0.00		0	76	0.00
mapped buffers lru chain	1	0.00		0	0
message pool operations parent latch	3,722	0.00		0	0
messages	61,693	0.31	0.00	0	0
mostly latch-free SCN	189	0.00		0	0
msg queue latch	1	0.00		0	0
multiblock read objects	214	0.00		0	0
name-service memory objects	1,462	0.00		0	0
name-service namespace bucket	5,356	0.00		0	0
name-service pending queue	134	0.00		0	0
name-service request	10	0.00		0	0
name-service request queue	3,469	0.00		0	0
ncodef allocation latch	6	0.00		0	0
object queue header heap	58,230	0.00		0	154	0.00
object queue header operation	98,445	0.00		0	0
object stats modification	154	0.65	0.00	0	0
parallel query alloc buffer	11,773	0.49	0.14	0	0
parallel query stats	800	0.25	0.00	0	0
parallel txn reco latch	2,106	0.00		0	0
parameter list	50	0.00		0	0
parameter table management	808	0.00		0	0
peshm	1	0.00		0	0
pesom_free_list	1	0.00		0	0
pesom_hash_node	1	0.00		0	0
post/wait queue	196	0.00		0	80	0.00
process allocation	1,365	0.00		0	20	0.00
process group creation	27	0.00		0	0
process queue	6,029	0.00		0	0
process queue reference	85,663,941	0.00	0.00	0	3,180,007	736.73
qmn task queue latch	84	2.38	0.00	0	0
query server freelists	4,604	0.43	0.15	0	0
query server process	3	0.00		0	1	0.00
queued dump request	1	0.00		0	0
recovery domain hash list	1	0.00		0	0
redo allocation	492	0.00		0	2,461	0.00
redo copy	1	0.00		0	2,461	0.08
redo writing	15,044	0.00		0	0
reid allocation latch	44	0.00		0	0
resmgr group change latch	641	0.00		0	0
resmgr:active threads	816	0.00		0	0
resmgr:actses change group	403	0.00		0	0
resmgr:actses change state	1	0.00		0	0
resmgr:free threads list	799	0.25	0.00	0	0
resmgr:plan CPU method	1	0.00		0	0
resmgr:resource group CPU method	1	0.00		0	0
resmgr:schema config	22	0.00		0	0
resmgr:session queuing	1	0.00		0	0
rm cas latch	1	0.00		0	0
row cache objects	81,651	0.00	0.00	0	90	0.00
rules engine aggregate statistics	1	0.00		0	0
rules engine rule set statistics	102	0.00		0	0
second spare latch	1	0.00		0	0
sequence cache	251	0.00		0	0
session allocation	31,820	0.29	0.67	0	0
session idle bit	5,265	0.00		0	0
session queue latch	1	0.00		0	0
session state list latch	826	0.12	1.00	0	0
session switching	7	0.00		0	0
session timer	114	0.00		0	0
shared pool	43,896	0.73	0.30	1	0
shared pool sim alloc	12	0.00		0	0
shared pool simulator	2,586	0.00		0	0
sim partition latch	1	0.00		0	0
simulator hash latch	2,304	0.00		0	0
simulator lru latch	1	0.00		0	2,221	0.00
sort extent pool	61,163	0.26	0.17	0	0
space background task latch	219	0.00		0	198	0.00
state object free list	2	0.00		0	0
statistics aggregation	3,528	0.00		0	0
storage server table manipulation latch	424	0.00		0	0
tablespace key chain	1,161	0.00		0	0
temp lob duration state obj allocation	1	0.00		0	0
temporary table state object allocation	8	0.00		0	0
test excl. parent l0	1	0.00		0	0
test excl. parent2 l0	1	0.00		0	0
third spare latch	1	0.00		0	0
threshold alerts latch	7	0.00		0	0
transaction allocation	89,600	0.00		0	0
undo global data	997	0.00		0	0
user lock	10	0.00		0	0
virtual circuit buffers	1	0.00		0	0
virtual circuit holder	1	0.00		0	0
virtual circuit queues	1	0.00		0	0

Back to Latch Statistics
Back to Top

Latch Sleep Breakdown

ordered by misses desc

Latch Name	Get Requests	Misses	Sleeps	Spin Gets
process queue reference	85,663,941	3,686	18	2,410
ges resource hash list	193,114	1,460	200	1,269
enqueue hash chains	215,269	952	29	924
cache buffers chains	189,246	732	225	524
ASM map operation freelist	11,216	559	288	313
shared pool	43,896	322	97	233
enqueues	108,429	225	12	216
sort extent pool	61,163	157	27	135
ASM map operation hash table	1,403,187	132	6	128
session allocation	31,820	91	61	47
KFMD SGA	16,167	87	5	82
parallel query alloc buffer	11,773	58	8	50
ges enqueue table freelist	118,177	27	2	25
query server freelists	4,604	20	3	17
active service list	17,537	14	12	3
DML lock allocation	363,138	5	1	4
Real-time plan statistics latch	963	5	4	1
ksz_so allocation latch	71	5	1	4
gc element	20,913	1	1	0
session state list latch	826	1	1	0

Back to Latch Statistics
Back to Top

Latch Miss Sources

only latches with sleeps are shown
ordered by name, sleeps desc

Latch Name	Where	Sleeps	Waiter Sleeps
ASM map operation freelist	kffmTranslate2	288	288
ASM map operation hash table	kffmDoDone_1	3	2
ASM map operation hash table	kffmTranslate	3	4
DML lock allocation	ktadmc	1	0
KFMD SGA	kfmdSlvJoin_LU	3	4
KFMD SGA	kfmdGetPriReidInfo	2	1
Real-time plan statistics latch	keswxAddNewPlanEntry	4	4
active service list	ksws_event: ksws event	6	10
active service list	kswsgsnp: get service name ptr	5	2
active service list	kswsgetso: get service object	1	0
active service list	kswsigsn: get service name	1	1
cache buffers chains	kcbbxsv	194	19
cache buffers chains	kcbgcur_2	49	42
cache buffers chains	kcbzwb	43	179
cache buffers chains	kcbzgb: scan from tail. nowait	3	0
cache buffers chains	kcbbic1	2	4
cache buffers chains	kcbbic2	2	6
cache buffers chains	kcbgcur_1	2	5
cache buffers chains	kcbzhngcbk1: get hash chain latch no wait	1	0
cache buffers chains	kcbzib: exchange rls	1	0
cache buffers chains	kclebs_2	1	2
enqueue hash chains	ksqgtl3	9	13
enqueue hash chains	ksqcmi: if lk mode not requested	8	7
enqueue hash chains	ksqcmi: if lk mode requested	6	5
enqueue hash chains	ksqrcl	6	4
enqueues	ksqgel: create enqueue	7	10
enqueues	ksqdel	5	2
gc element	kclchkping	1	0
ges enqueue table freelist	kjlfr: remove lock from parent object	2	0
ges resource hash list	kjuscl: lock close request	147	102
ges resource hash list	kjrmas1: lookup master node	52	4
ges resource hash list	kjrref: find matched resource	1	92
ksz_so allocation latch	ksz_gen_reid1	1	1
parallel query alloc buffer	kxfpbalo	8	8
process queue reference	kxfpqrsnd	18	0
query server freelists	kxfpobadf	3	3
session allocation	ksuxds: KSUSFCLC not set	44	24
session allocation	kxfprdp	12	20
session allocation	kxfpqidqr:1	5	17
session state list latch	kpseqd	1	1
shared pool	kghalo	79	75
shared pool	kghfre	17	21
shared pool	kghupr1	1	1
sort extent pool	ktstaddextent1	17	26
sort extent pool	ktst dump	5	0
sort extent pool	ktstallocext: current num of extents	5	0

Back to Latch Statistics
Back to Top

Mutex Sleep Summary

ordered by number of sleeps desc

Mutex Type	Location	Sleeps
Library Cache	kgllkdl1 85	6
Library Cache	kglpnal2 91	6
Library Cache	kglhdgn2 106	5
Library Cache	kgllkc1 57	4
Library Cache	kglget2 2	3
Library Cache	kgllkal1 80	3
Library Cache	kglhdgn1 62	2
Library Cache	kgllkck1 89	2
Library Cache	kglpin1 4	2
Library Cache	kglpnal1 90	2
Library Cache	kglpndl1 95	1

Back to Latch Statistics
Back to Top

Parent Latch Statistics

No data exists for this section of the report.

Back to Latch Statistics
Back to Top

Child Latch Statistics

No data exists for this section of the report.

Back to Latch Statistics
Back to Top

Segment Statistics

Segments by Logical Reads
Segments by Physical Reads
Segments by Direct Physical Reads
Segments by Physical Writes
Segments by Direct Physical Writes
Segments by Table Scans
Segments by DB Blocks Changes
Segments by Row Lock Waits
Segments by ITL Waits
Segments by Buffer Busy Waits
Segments by Global Cache Buffer Busy
Segments by CR Blocks Received
Segments by Current Blocks Received

Segments by Logical Reads

Total Logical Reads: 7,674,748
Captured Segments account for 96.6% of Total

Owner	Tablespace Name	Object Name	Subobject Name	Obj. Type	Logical Reads	%Total
RETAIL	RETAIL_20090522	DWB_RTL_TRX	R20090522	TABLE PARTITION	267,344	3.48
RETAIL	RETAIL_20090530	DWB_RTL_TRX	R20090530	TABLE PARTITION	264,624	3.45
RETAIL	RETAIL_20090517	DWB_RTL_TRX	R20090517	TABLE PARTITION	262,416	3.42
RETAIL	RETAIL_20090520	DWB_RTL_TRX	R20090520	TABLE PARTITION	261,456	3.41
RETAIL	RETAIL_20090501	DWB_RTL_TRX	R20090501	TABLE PARTITION	260,224	3.39

Back to Segment Statistics
Back to Top

Segments by Physical Reads

Total Physical Reads: 13,420,432
Captured Segments account for 55.0% of Total

Owner	Tablespace Name	Object Name	Subobject Name	Obj. Type	Physical Reads	%Total
RETAIL	RETAIL_20090531	DWB_RTL_TRX	R20090531	TABLE PARTITION	249,804	1.86
RETAIL	RETAIL_20090510	DWB_RTL_TRX	R20090510	TABLE PARTITION	249,724	1.86
RETAIL	RETAIL_20090523	DWB_RTL_TRX	R20090523	TABLE PARTITION	248,708	1.85
RETAIL	RETAIL_20090508	DWB_RTL_TRX	R20090508	TABLE PARTITION	248,360	1.85
RETAIL	RETAIL_20090526	DWB_RTL_TRX	R20090526	TABLE PARTITION	247,868	1.85

Back to Segment Statistics
Back to Top

Segments by Direct Physical Reads

Total Direct Physical Reads: 13,420,328
Captured Segments account for 55.0% of Total

Owner	Tablespace Name	Object Name	Subobject Name	Obj. Type	Direct Reads	%Total
RETAIL	RETAIL_20090531	DWB_RTL_TRX	R20090531	TABLE PARTITION	249,804	1.86
RETAIL	RETAIL_20090510	DWB_RTL_TRX	R20090510	TABLE PARTITION	249,724	1.86
RETAIL	RETAIL_20090523	DWB_RTL_TRX	R20090523	TABLE PARTITION	248,836	1.85
RETAIL	RETAIL_20090508	DWB_RTL_TRX	R20090508	TABLE PARTITION	248,360	1.85
RETAIL	RETAIL_20090526	DWB_RTL_TRX	R20090526	TABLE PARTITION	247,868	1.85

Back to Segment Statistics
Back to Top

Segments by Physical Writes

Total Physical Writes: 5,799,637
Captured Segments account for 0.0% of Total

Owner	Tablespace Name	Object Name	Subobject Name	Obj. Type	Physical Writes
SYS	SYSAUX	WRH$_ACTIVE_SESSION_HISTORY	975138_895	TABLE PARTITION	11
SYS	SYSAUX	WRH$_SYS_TIME_MODEL_PK	975138_895	INDEX PARTITION	1
SYS	SYSAUX	WRH$_TABLESPACE_STAT_PK	975138_895	INDEX PARTITION	1
SYS	SYSAUX	WRI$_ADV_PARAMETERS_PK		INDEX	1

Back to Segment Statistics
Back to Top

Segments by Direct Physical Writes

Total Direct Physical Writes: 5,792,337
Captured Segments account for 0.0% of Total

Owner	Tablespace Name	Object Name	Subobject Name	Obj. Type	Direct Writes	%Total
SYS	SYSAUX	WRH$_ACTIVE_SESSION_HISTORY	975138_895	TABLE PARTITION	11	0.00

Back to Segment Statistics
Back to Top

Segments by Table Scans

Total Table Scans: 562
Captured Segments account for 96.1% of Total

Owner	Tablespace Name	Object Name	Subobject Name	Obj. Type	Table Scans	%Total
RETAIL	RETAIL_20090501	DWB_RTL_TRX	R20090501	TABLE PARTITION	18	3.20
RETAIL	RETAIL_20090502	DWB_RTL_TRX	R20090502	TABLE PARTITION	18	3.20
RETAIL	RETAIL_20090503	DWB_RTL_TRX	R20090503	TABLE PARTITION	18	3.20
RETAIL	RETAIL_20090504	DWB_RTL_TRX	R20090504	TABLE PARTITION	18	3.20
RETAIL	RETAIL_20090505	DWB_RTL_TRX	R20090505	TABLE PARTITION	18	3.20

Back to Segment Statistics
Back to Top

Segments by DB Blocks Changes

% of Capture shows % of DB Block Changes for each top segment compared
with total DB Block Changes for all segments captured by the Snapshot

Owner	Tablespace Name	Object Name	Subobject Name	Obj. Type	DB Block Changes	% of Capture
SYS	SYSAUX	WRI$_ADV_PARAMETERS_PK		INDEX	192	13.33
SYS	SYSAUX	WRH$_EVENT_HISTOGRAM_PK	975138_895	INDEX PARTITION	144	10.00
SYS	SYSAUX	WRI$_ADV_PARAMETERS		TABLE	144	10.00
SYS	SYSAUX	WRH$_SQLSTAT_INDEX	975138_895	INDEX PARTITION	96	6.67
SYS	SYSAUX	WRH$_ACTIVE_SESSION_HISTORY_PK	975138_895	INDEX PARTITION	80	5.56

Back to Segment Statistics
Back to Top

Segments by Row Lock Waits

% of Capture shows % of row lock waits for each top segment compared
with total row lock waits for all segments captured by the Snapshot

Owner	Tablespace Name	Object Name	Subobject Name	Obj. Type	Row Lock Waits	% of Capture
SYS	SYSAUX	WRH$_SQLSTAT_INDEX	975138_895	INDEX PARTITION	1	100.00

Back to Segment Statistics
Back to Top

Segments by ITL Waits

No data exists for this section of the report.

Back to Segment Statistics
Back to Top

Segments by Buffer Busy Waits

No data exists for this section of the report.

Back to Segment Statistics
Back to Top

Segments by Global Cache Buffer Busy

No data exists for this section of the report.

Back to Segment Statistics
Back to Top

Segments by CR Blocks Received

Total CR Blocks Received: 1,060
Captured Segments account for 61.9% of Total

Owner	Tablespace Name	Object Name	Subobject Name	Obj. Type	CR Blocks Received	%Total
SYS	SYSAUX	WRH$_SYSTEM_EVENT	975138_895	TABLE PARTITION	260	24.53
SYS	SYSAUX	WRH$_SQLSTAT	975138_895	TABLE PARTITION	46	4.34
SYS	SYSAUX	WRH$_ACTIVE_SESSION_HISTORY	975138_895	TABLE PARTITION	32	3.02
SYS	SYSAUX	WRH$_PARAMETER	975138_895	TABLE PARTITION	30	2.83
SYS	SYSAUX	WRI$_ADV_ADDM_FDG		TABLE	29	2.74

Back to Segment Statistics
Back to Top

Segments by Current Blocks Received

Total Current Blocks Received: 660
Captured Segments account for 58.5% of Total

Owner	Tablespace Name	Object Name	Subobject Name	Obj. Type	Current Blocks Received	%Total
SYS	SYSAUX	WRH$_SYSTEM_EVENT	975138_895	TABLE PARTITION	148	22.42
SYS	SYSAUX	WRH$_SQLSTAT_INDEX	975138_895	INDEX PARTITION	54	8.18
SYS	SYSAUX	WRH$_ACTIVE_SESSION_HISTORY	975138_895	TABLE PARTITION	14	2.12
SYS	SYSAUX	WRH$_SQLTEXT		TABLE	12	1.82
SYS	SYSAUX	WRM$_SNAPSHOT		TABLE	12	1.82

Back to Segment Statistics
Back to Top

Dictionary Cache Statistics

Dictionary Cache Stats
Dictionary Cache Stats (RAC)

Dictionary Cache Stats

"Pct Misses" should be very low (< 2% in most cases)
"Final Usage" is the number of cache entries being used

Cache	Get Requests	Pct Miss	Mod Reqs	Final Usage
dc_awr_control	9	0.00	2	1
dc_database_links	6	0.00	0	1
dc_global_oids	834	0.84	0	249
dc_histogram_data	1,552	1.16	0	6,087
dc_histogram_defs	5,665	4.31	49	6,054
dc_object_grants	9,214	0.00	0	312
dc_objects	5,022	2.09	6	3,454
dc_profiles	3	0.00	0	1
dc_rollback_segments	4,423	0.00	0	1,055
dc_segments	639	7.36	4	7,504
dc_sequences	22	9.09	22	5
dc_tablespaces	2,116	0.00	0	51
dc_users	2,491	0.08	0	171
global database name	901	0.00	0	1
outstanding_alerts	1	0.00	1	7

Back to Dictionary Cache Statistics
Back to Top

Dictionary Cache Stats (RAC)

Cache	GES Requests	GES Conflicts	GES Releases
dc_awr_control	4	0	0
dc_global_oids	7	0	0
dc_histogram_defs	195	0	780
dc_objects	87	0	0
dc_segments	54	0	0
dc_sequences	44	1	0
dc_users	2	0	0
outstanding_alerts	1	0	0

Back to Dictionary Cache Statistics
Back to Top

Library Cache Statistics

Library Cache Activity
Library Cache Activity (RAC)

Library Cache Activity

"Pct Misses" should be very low

Namespace	Get Requests	Pct Miss	Pin Requests	Pct Miss	Reloads	Invali- dations
BODY	37	8.11	81	4.94	0	0
CLUSTER	25	0.00	19	0.00	0	0
INDEX	58	0.00	49	0.00	0	0
SQL AREA	875	7.66	5,753	7.46	33	12
TABLE/PROCEDURE	11,263	0.27	3,470	3.49	33	0

Back to Library Cache Statistics
Back to Top

Library Cache Activity (RAC)

Namespace	GES Lock Requests	GES Pin Requests
CLUSTER	19	19
INDEX	49	49
TABLE/PROCEDURE	2,136	2,539

Back to Library Cache Statistics
Back to Top

Memory Statistics

Memory Dynamic Components
Memory Resize Operations Summary
Memory Resize Ops
Process Memory Summary
SGA Memory Summary
SGA breakdown difference

Memory Dynamic Components

Min/Max sizes since instance startup
Oper Types/Modes: INItializing,GROw,SHRink,STAtic/IMMediate,DEFerred
ordered by Component

Component	Begin Snap Size (Mb)	Current Size (Mb)	Min Size (Mb)	Max Size (Mb)	Last Op Typ/Mod
ASM Buffer Cache	0.00	0.00	0.00	0.00	STA/
DEFAULT 16K buffer cache	0.00	0.00	0.00	0.00	STA/
DEFAULT 2K buffer cache	0.00	0.00	0.00	0.00	STA/
DEFAULT 32K buffer cache	0.00	0.00	0.00	0.00	STA/
DEFAULT 4K buffer cache	0.00	0.00	0.00	0.00	STA/
DEFAULT 8K buffer cache	0.00	0.00	0.00	0.00	STA/
DEFAULT buffer cache	4,736.00	4,736.00	4,736.00	4,800.00	SHR/DEF
KEEP buffer cache	0.00	0.00	0.00	0.00	STA/
PGA Target	12,288.00	12,288.00	12,288.00	12,288.00	STA/
RECYCLE buffer cache	0.00	0.00	0.00	0.00	STA/
SGA Target	6,144.00	6,144.00	6,144.00	6,144.00	STA/
Shared IO Pool	0.00	0.00	0.00	0.00	STA/
java pool	64.00	64.00	64.00	64.00	STA/
large pool	64.00	64.00	64.00	64.00	STA/
shared pool	1,088.00	1,088.00	1,024.00	1,088.00	GRO/DEF
streams pool	128.00	128.00	128.00	128.00	STA/

Back to Memory Statistics
Back to Top

Memory Resize Operations Summary

No data exists for this section of the report.

Back to Memory Statistics
Back to Top

Memory Resize Ops

No data exists for this section of the report.

Back to Memory Statistics
Back to Top

Process Memory Summary

B: Begin snap E: End snap
All rows below contain absolute values (i.e. not diffed over the interval)
Max Alloc is Maximum PGA Allocation size at snapshot time
Hist Max Alloc is the Historical Max Allocation for still-connected processes
ordered by Begin/End snapshot, Alloc (MB) desc

	Category	Alloc (MB)	Used (MB)	Avg Alloc (MB)	Std Dev Alloc (MB)	Max Alloc (MB)	Hist Max Alloc (MB)	Num Proc	Num Alloc
B	Other	412.03		3.65	7.52	51	53	113	113
	Freeable	50.31	0.00	1.86	4.18	22		27	27
	SQL	9.02	6.50	0.33	1.38	7	16	27	18
	PL/SQL	2.28	0.38	0.04	0.23	2	2	55	51
E	Other	473.37		3.73	7.04	51	53	127	127
	Freeable	80.88	0.00	1.50	2.94	22		54	54
	SQL	9.28	6.79	0.16	0.95	7	431	58	19
	PL/SQL	2.36	0.43	0.03	0.18	2	2	93	64

Back to Memory Statistics
Back to Top

SGA Memory Summary

SGA regions	Begin Size (Bytes)	End Size (Bytes) (if different)
Database Buffers	4,966,055,936
Fixed Size	2,160,112
Redo Buffers	36,175,872
Variable Size	1,409,288,720

Back to Memory Statistics
Back to Top

SGA breakdown difference

ordered by Pool, Name
N/A value for Begin MB or End MB indicates the size of that Pool/Name was insignificant, or zero in that snapshot

Pool	Name	Begin MB	End MB	% Diff
java	free memory	64.00	64.00	0.00
large	PX msg pool	54.00	54.00	0.00
large	free memory	9.63	9.63	0.00
shared	ASH buffers	15.50	15.50	0.00
shared	CCursor	33.42	32.89	-1.59
shared	FileOpenBlock	11.21	11.21	0.00
shared	KCL name table	48.00	48.00	0.00
shared	KGL handle	17.36	17.38	0.08
shared	KQR L PO	15.23	14.74	-3.24
shared	PCursor	27.78	27.69	-0.33
shared	PL/SQL DIANA	19.07	19.24	0.85
shared	PL/SQL MPCODE	19.66	19.74	0.41
shared	db_block_hash_buckets	44.50	44.50	0.00
shared	event statistics per sess	12.32	12.32	0.00
shared	free memory	229.86	225.20	-2.03
shared	gcs resources	94.83	94.83	0.00
shared	gcs shadows	63.22	63.22	0.00
shared	ges big msg buffers	22.03	22.03	0.00
shared	ges enqueues	20.03	20.03	0.00
shared	sessions	11.96	11.96	0.00
shared	sql area	177.66	180.45	1.57
shared	write state object	14.95	14.95	0.00
streams	free memory	127.97	127.97	0.00
	buffer_cache	4,736.00	4,736.00	0.00
	fixed_sga	2.06	2.06	0.00
	log_buffer	34.50	34.50	0.00

Back to Memory Statistics
Back to Top

Streams Statistics

Streams CPU/IO Usage
Streams Capture
Streams Apply
Buffered Queues
Buffered Subscribers
Rule Set
Persistent Queues
Persistent Subscribers

Streams CPU/IO Usage

Streams processes ordered by CPU usage
CPU and I/O Time in micro seconds

Session Type	CPU Time	User I/O Time	Sys I/O Time
QMON Slaves	5,999	0	0
QMON Coordinator	999	0	0

Back to Streams Statistics
Back to Top

Streams Capture

No data exists for this section of the report.

Back to Streams Statistics
Back to Top

Streams Apply

No data exists for this section of the report.

Back to Streams Statistics
Back to Top

Buffered Queues

No data exists for this section of the report.

Back to Streams Statistics
Back to Top

Buffered Subscribers

No data exists for this section of the report.

Back to Streams Statistics
Back to Top

Rule Set

Rule Sets ordered by Evaluations

Ruleset Name	Evals	Fast Evals	SQL Execs	CPU Time	Elapsed Time
SYS.ALERT_QUE_R	1	0	0	0	0

Back to Streams Statistics
Back to Top

Persistent Queues

Persistent Queue statistics ordered by enqueue rate

Queue Schema and Name	Incoming Msg per second	Outgoing Msg per second	Expired Msg per second	Ready Msg per second
SYS.ALERT_QUE	0	0	0	0
SYS.AQ$_ALERT_QT_E	0	0	0	0
SYSMAN.MGMT_NOTIFY_Q	0	0	0	0
SYSMAN.MGMT_TASK_Q	0	0	0	0

Back to Streams Statistics
Back to Top

Persistent Subscribers

Persistent Subscribers Statistics ordered by enqueue rate

Subscriber Name	Incoming Msg per second	Outgoing Msg per second	Expired Msg per second
RSTHPDBS101_U_1831_DEMO1	0	0	0
RSTHPDBS102_U_1831_DEMO2	0	0	0
RSTHPDBS103_U_1831_DEMO3	0	0	0
RSTHPDBS104_U_1831_DEMO4	0	0	0
RSTHPDBS105_U_1831_DEMO5	0	0	0
RSTHPDBS106_U_1831_DEMO6	0	0	0
RSTHPDBS107_U_1831_DEMO7	0	0	0
RSTHPDBS108_U_1831_DEMO8	0	0	0
HAE_SUB	0	0	0

Back to Streams Statistics
Back to Top

Resource Limit Stats

only rows with Current or Maximum Utilization > 80% of Limit are shown
ordered by resource name

Resource Name	Current Utilization	Maximum Utilization	Initial Allocation	Limit
gcs_resources	188,535	587,846	690516	690516

init.ora Parameters

if IP/Public/Source at End snap is different a '*' is displayed

Parameter Name	Begin value	End value (if different)
audit_file_dest	/u01/app/oracle/admin/DEMO/adump
audit_trail	DB
cluster_database	TRUE
cluster_database_instances	8
compatible	11.1.0.7
control_files	+DGA/demo/control01.ctl, +DGA/demo/control02.ctl, +DGA/demo/control03.ctl
db_block_size	8192
db_domain
db_name	DEMO
diagnostic_dest	/u01/app/oracle
dispatchers	(PROTOCOL=TCP) (SERVICE=DEMOXDB)
filesystemio_options	setall
instance_number	1
local_listener	LISTENER_DEMO1
open_cursors	300
parallel_adaptive_multi_user	FALSE
parallel_execution_message_size	16384
parallel_max_servers	128
parallel_min_servers	64
pga_aggregate_target	12884901888
processes	1024
recyclebin	OFF
remote_listener	LISTENERS_DEMO
remote_login_passwordfile	EXCLUSIVE
resource_manager_plan	FORCE:
service_names	D_EIGHT, D_FOUR, D_TWO, D_ONE, D1, DEMO
sessions	1131
sga_target	6442450944
spfile	+DGA/demo/spfiledemo.ora
thread	1
undo_tablespace	UNDOTBS1

Global Enqueue Statistics

Statistic	Total	per Second	per Trans
acks for commit broadcast(actual)	711	2.39	10.46
acks for commit broadcast(logical)	712	2.39	10.47
broadcast msgs on commit(actual)	705	2.37	10.37
broadcast msgs on commit(logical)	707	2.38	10.40
broadcast msgs on commit(wasted)	22	0.07	0.32
broadcast on commit wait time(ms)	43	0.14	0.63
broadcast on commit waits	21	0.07	0.31
dynamically allocated gcs resources	0	0.00	0.00
dynamically allocated gcs shadows	0	0.00	0.00
false posts waiting for scn acks	0	0.00	0.00
flow control messages received	3	0.01	0.04
flow control messages sent	0	0.00	0.00
gcs assume cvt	0	0.00	0.00
gcs assume no cvt	199	0.67	2.93
gcs ast xid	1	0.00	0.01
gcs blocked converts	240	0.81	3.53
gcs blocked cr converts	281	0.94	4.13
gcs compatible basts	6	0.02	0.09
gcs compatible cr basts (global)	20	0.07	0.29
gcs compatible cr basts (local)	76	0.26	1.12
gcs cr basts to PIs	0	0.00	0.00
gcs cr serve without current lock	0	0.00	0.00
gcs dbwr flush pi msgs	7	0.02	0.10
gcs dbwr write request msgs	3	0.01	0.04
gcs error msgs	0	0.00	0.00
gcs forward cr to pinged instance	0	0.00	0.00
gcs immediate (compatible) converts	10	0.03	0.15
gcs immediate (null) converts	67	0.23	0.99
gcs immediate cr (compatible) converts	0	0.00	0.00
gcs immediate cr (null) converts	13	0.04	0.19
gcs indirect ast	47	0.16	0.69
gcs indirect bidless ast	33	0.11	0.49
gcs indirect fg ast	36	0.12	0.53
gcs lms flush pi msgs	10	0.03	0.15
gcs lms write request msgs	4	0.01	0.06
gcs msgs process time(ms)	18	0.06	0.26
gcs msgs received	3,096	10.41	45.53
gcs new served by master	64	0.22	0.94
gcs out-of-order msgs	0	0.00	0.00
gcs pings refused	0	0.00	0.00
gcs pkey conflicts retry	0	0.00	0.00
gcs queued converts	0	0.00	0.00
gcs reader bypass N->Xw ping local	2	0.01	0.03
gcs reader bypass N->Xw ping remote	15	0.05	0.22
gcs reader bypass grant X on assume	15	0.05	0.22
gcs reader bypass grant ast	0	0.00	0.00
gcs reader bypass grant fg ast	40	0.13	0.59
gcs reader bypass grant immediate	23	0.08	0.34
gcs recovery claim msgs	0	0.00	0.00
gcs refuse xid	0	0.00	0.00
gcs regular cr	74	0.25	1.09
gcs retry convert request	0	0.00	0.00
gcs share recovery bast	0	0.00	0.00
gcs side channel msgs actual	189	0.64	2.78
gcs side channel msgs logical	1,794	6.03	26.38
gcs stale cr	0	0.00	0.00
gcs undo cr	1	0.00	0.01
gcs write notification msgs	0	0.00	0.00
gcs writes refused	0	0.00	0.00
ges msgs process time(ms)	111	0.37	1.63
ges msgs received	10,354	34.81	152.26
global posts dropped	0	0.00	0.00
global posts queue time	74	0.25	1.09
global posts queued	7	0.02	0.10
global posts requested	7	0.02	0.10
global posts sent	7	0.02	0.10
implicit batch messages received	104	0.35	1.53
implicit batch messages sent	263	0.88	3.87
lmd msg send time(ms)	0	0.00	0.00
lms(s) msg send time(ms)	0	0.00	0.00
messages flow controlled	1,316	4.42	19.35
messages queue sent actual	8,330	28.00	122.50
messages queue sent logical	8,911	29.96	131.04
messages received actual	12,549	42.19	184.54
messages received logical	13,450	45.22	197.79
messages sent directly	5,178	17.41	76.15
messages sent indirectly	2,828	9.51	41.59
messages sent not implicit batched	8,067	27.12	118.63
messages sent pbatched	8,475	28.49	124.63
msgs causing lmd to send msgs	4,767	16.03	70.10
msgs causing lms(s) to send msgs	417	1.40	6.13
msgs received queue time (ms)	64	0.22	0.94
msgs received queued	13,450	45.22	197.79
msgs sent queue time (ms)	30,747,584	103,365.73	452,170.35
msgs sent queue time on ksxp (ms)	2,491	8.37	36.63
msgs sent queued	8,629	29.01	126.90
msgs sent queued on ksxp	13,620	45.79	200.29
process batch messages received	6,673	22.43	98.13
process batch messages sent	6,296	21.17	92.59

Global CR Served Stats

Statistic	Total
CR Block Requests	92
CURRENT Block Requests	62
Data Block Requests	92
Undo Block Requests	1
TX Block Requests	21
Current Results	140
Private results	3
Zero Results	11
Disk Read Results	0
Fail Results	0
Fairness Down Converts	14
Fairness Clears	19
Free GC Elements	0
Flushes	21
Flushes Queued	0
Flush Queue Full	0
Flush Max Time (us)	0
Light Works	2
Errors	0

Global CURRENT Served Stats

Pins = CURRENT Block Pin Operations
Flushes = Redo Flush before CURRENT Block Served Operations
Writes = CURRENT Block Fusion Write Operations

Statistic	Total	% <1ms	% <10ms	% <1s
Pins	309	100.00	0.00	0.00
Flushes	6	66.67	33.33	0.00
Writes	8	12.50	50.00	37.50

Global Cache Transfer Stats

Global Cache Transfer Stats
Global Cache Transfer Times (ms)
Global Cache Transfer (Immediate)
Global Cache Times (Immediate)

Global Cache Transfer Stats

Immediate (Immed) - Block Transfer NOT impacted by Remote Processing Delays
Busy (Busy) - Block Transfer impacted by Remote Contention
Congested (Congst) - Block Transfer impacted by Remote System Load
ordered by CR + Current Blocks Received desc

Inst No	Block Class	Blocks Received	% Immed	% Busy	% Congst	Blocks Received	% Immed	% Busy	% Congst
		CR				Current
2	data block	176	98.86	1.14	0.00	95	98.95	1.05	0.00
3	data block	109	96.33	3.67	0.00	111	99.10	0.90	0.00
7	data block	51	96.08	3.92	0.00	132	96.21	3.79	0.00
4	data block	77	96.10	3.90	0.00	94	95.74	4.26	0.00
6	data block	78	100.00	0.00	0.00	52	96.15	3.85	0.00
8	data block	86	100.00	0.00	0.00	40	100.00	0.00	0.00
5	data block	80	98.75	1.25	0.00	34	100.00	0.00	0.00
4	undo header	59	100.00	0.00	0.00	4	75.00	25.00	0.00
3	undo header	50	100.00	0.00	0.00	6	100.00	0.00	0.00
7	undo header	50	100.00	0.00	0.00	3	100.00	0.00	0.00
2	undo header	40	100.00	0.00	0.00	2	100.00	0.00	0.00
6	undo header	38	100.00	0.00	0.00	3	100.00	0.00	0.00
5	undo header	37	100.00	0.00	0.00	4	100.00	0.00	0.00
8	undo header	38	100.00	0.00	0.00	2	100.00	0.00	0.00
4	Others	12	100.00	0.00	0.00	11	100.00	0.00	0.00
3	Others	13	100.00	0.00	0.00	9	100.00	0.00	0.00
5	Others	9	100.00	0.00	0.00	11	81.82	18.18	0.00
7	Others	14	100.00	0.00	0.00	4	100.00	0.00	0.00
2	Others	8	100.00	0.00	0.00	7	100.00	0.00	0.00
8	Others	4	100.00	0.00	0.00	4	100.00	0.00	0.00
6	Others	4	100.00	0.00	0.00	2	100.00	0.00	0.00

Back to Global Cache Transfer Stats
Back to Top

Global Cache Transfer Times (ms)

Avg Time - average time of all blocks (Immed,Busy,Congst) in ms
Immed, Busy, Congst - Average times in ms
ordered by CR + Current Blocks Received desc

Inst No	Block Class	All	Immed	Busy	All	Immed	Busy
		CR Avg Time (ms)			Current Avg Time (ms)
2	data block	0.45	0.45	0.61	0.22	0.22	0.76
3	data block	0.56	0.54	1.02	0.47	0.46	1.32
7	data block	0.25	0.23	0.71	0.30	0.28	0.95
4	data block	0.40	0.38	0.86	0.45	0.44	0.56
6	data block	0.20	0.20		0.24	0.25	0.15
8	data block	0.45	0.45		0.57	0.57
5	data block	0.49	0.49	0.50	0.19	0.19
4	undo header	0.14	0.14		0.35	0.21	0.78
3	undo header	0.41	0.41		0.32	0.32
7	undo header	0.58	0.58		0.20	0.20
2	undo header	0.33	0.33		0.50	0.50
6	undo header	0.29	0.29		0.75	0.75
5	undo header	0.30	0.30		0.13	0.13
8	undo header	0.34	0.34		0.16	0.16
4	others	0.20	0.20		0.16	0.16
3	others	0.18	0.18		0.23	0.23
5	others	0.47	0.47		0.19	0.19	0.19
7	others	0.48	0.48		0.28	0.28
2	others	0.21	0.21		0.19	0.19
8	others	0.27	0.27		0.18	0.18
6	others	0.23	0.23		0.19	0.19
3	undo block
7	undo block
4	undo block
5	undo block
8	undo block
6	undo block
2	undo block

Back to Global Cache Transfer Stats
Back to Top

Global Cache Transfer (Immediate)

Immediate (Immed) - Block Transfer NOT impacted by Remote Processing Delays
% of Blocks Received requiring 2 or 3 hops
ordered by CR + Current Blocks Received desc

Src Inst#	Block Class	Blocks Lost	Immed Blks Received	% 2hop	% 3hop	Immed Blks Received	% 2hop	% 3hop
			CR			Current
2	data block	0	174	10.34	89.66	94	30.85	69.15
3	data block	0	105	49.52	50.48	110	14.55	85.45
7	data block	0	49	26.53	73.47	127	18.11	81.89
4	data block	0	74	47.30	52.70	90	28.89	71.11
6	data block	0	78	56.41	43.59	50	42.00	58.00
8	data block	0	86	36.05	63.95	40	85.00	15.00
5	data block	0	79	40.51	59.49	34	55.88	44.12
4	undo header	0	59	100.00	0.00	3	100.00	0.00
3	undo header	0	50	100.00	0.00	6	100.00	0.00
7	undo header	0	50	100.00	0.00	3	100.00	0.00
2	undo header	0	40	100.00	0.00	2	100.00	0.00
5	undo header	0	37	100.00	0.00	4	100.00	0.00
6	undo header	0	38	100.00	0.00	3	66.67	33.33
8	undo header	0	38	100.00	0.00	2	50.00	50.00
4	others	0	12	75.00	25.00	11	72.73	27.27
3	others	0	13	46.15	53.85	9	22.22	77.78
7	others	0	14	28.57	71.43	4	25.00	75.00
5	others	0	9	66.67	33.33	9	55.56	44.44
2	others	0	8	25.00	75.00	7	57.14	42.86
8	others	0	4	0.00	100.00	4	75.00	25.00
6	others	0	4	50.00	50.00	2	0.00	100.00
3	undo block	0	0			0
7	undo block	0	0			0
4	undo block	0	0			0
5	undo block	0	0			0
8	undo block	0	0			0
6	undo block	0	0			0
2	undo block	0	0			0

Back to Global Cache Transfer Stats
Back to Top

Global Cache Times (Immediate)

Blocks Lost, 2-hop and 3-hop Average times in (ms)
ordered by CR + Current Blocks Received desc

Src Inst#	Block Class	Immed	2hop	3hop	Immed	2hop	3hop
		CR Avg Time (ms)			Current Avg Time (ms)
2	data block	0.45	0.36	0.46	0.22	0.20	0.22
3	data block	0.54	0.73	0.36	0.46	0.22	0.50
7	data block	0.23	0.12	0.27	0.28	0.16	0.30
4	data block	0.38	0.47	0.30	0.44	0.32	0.49
6	data block	0.20	0.17	0.25	0.25	0.17	0.30
8	data block	0.45	0.31	0.53	0.57	0.64	0.20
5	data block	0.49	0.63	0.40	0.19	0.17	0.21
4	undo header	0.14	0.14		0.21	0.21
3	undo header	0.41	0.41		0.32	0.32
7	undo header	0.58	0.58		0.20	0.20
2	undo header	0.33	0.33		0.50	0.50
5	undo header	0.30	0.30		0.13	0.13
6	undo header	0.29	0.29		0.75	1.01	0.25
8	undo header	0.34	0.34		0.16	0.13	0.20
4	others	0.20	0.21	0.17	0.16	0.16	0.17
3	others	0.18	0.19	0.17	0.23	0.17	0.24
7	others	0.48	0.26	0.57	0.28	0.25	0.30
5	others	0.47	0.41	0.59	0.19	0.17	0.22
2	others	0.21	0.13	0.24	0.19	0.15	0.24
8	others	0.27		0.27	0.18	0.17	0.23
6	others	0.23	0.30	0.17	0.19		0.19
3	undo block
7	undo block
4	undo block
5	undo block
8	undo block
6	undo block
2	undo block

Back to Global Cache Transfer Stats
Back to Top

Interconnect Stats

Interconnect Latency Stats
Interconnect Throughput by Client
Interconnect Device Stats

Interconnect Ping Latency Stats

Ping latency of the roundtrip of a message from this instance to
target instances.
The target instance is identified by an instance number.
Average and standard deviation of ping latency is given in miliseconds
for message sizes of 500 bytes and 8K.
Note that latency of a message from the instance to itself is used as
control, since message latency can include wait for CPU

Target Instance	500B Ping Count	Avg Latency 500B msg	Stddev 500B msg	8K Ping Count	Avg Latency 8K msg	Stddev 8K msg
1	11	0.67	0.13	11	0.66	0.13
2	11	0.58	0.16	11	0.61	0.14
3	11	0.64	0.22	11	0.69	0.24
4	11	0.51	0.14	11	0.54	0.12
5	11	0.55	0.15	11	0.58	0.14
6	11	0.56	0.16	11	0.58	0.13
7	11	0.51	0.13	11	0.54	0.12
8	11	0.61	0.32	11	0.62	0.31

Back to Interconnect Stats
Back to Top

Interconnect Throughput by Client

Throughput of interconnect usage by major consumers.
All throughput numbers are megabytes per second

Used By	Send Mbytes/sec	Receive Mbytes/sec
Global Cache	0.01	0.04
Parallel Query	0.04	0.02
DB Locks	0.01	0.01
DB Streams	0.00	0.00
Other	0.02	0.01

Back to Interconnect Stats
Back to Top

Interconnect Device Statistics

Throughput and errors of interconnect devices (at OS level).
All throughput numbers are megabytes per second

Device Name	IP Address	Public	Source	Send Mbytes/sec	Send Errors	Send Dropped	Send Buffer Overrun	Send Carrier Lost	Receive Mbytes/sec	Receive Errors	Receive Dropped	Receive Buffer Overrun	Receive Frame Errors
bond0	172.31.88.109	NO	Oracle Cluster Repository	0.01	0	0	0	0	0.01	0	0	0	0

Back to Interconnect Stats
Back to Top

End of Report

Buffer Nowait %:	61.54	Redo NoWait %:	100.00
Buffer Hit %:	99.78	In-memory Sort %:	100.00
Library Hit %:	94.09	Soft Parse %:	86.32
Execute to Parse %:	37.22	Latch Hit %:	99.99
Parse CPU to Parse Elapsd %:	0.00	% Non-Parse CPU:	99.97

Buffer access - local cache %:	96.14
Buffer access - remote cache %:	3.64
Buffer access - disk %:	0.22

Avg global enqueue get time (ms):	0.1
Avg global cache cr block receive time (ms):	0.4
Avg global cache current block receive time (ms):	0.3
Avg global cache cr block build time (ms):	0.0
Avg global cache cr block send time (ms):	0.0
Global cache log flushes for cr blocks served %:	14.0
Avg global cache cr block flush time (ms):	0.5
Avg global cache current block pin time (ms):	0.0
Avg global cache current block send time (ms):	0.0
Global cache log flushes for current blocks served %:	1.9
Avg global cache current block flush time (ms):	0.0

Avg message sent queue time (ms):	3,563.3
Avg message sent queue time on ksxp (ms):	0.2
Avg message received queue time (ms):	0.0
Avg GCS message process time (ms):	0.0
Avg GES message process time (ms):	0.0
% of direct sent messages:	55.55
% of indirect sent messages:	30.34
% of flow controlled messages:	14.12