WORKLOAD REPOSITORY report for

DB NameDB IdUnique NameRoleEditionReleaseRACCDB
ORCL12C776972821orcl12cPRIMARYEE12.2.0.1.0NOYES

InstanceInst NumStartup Time
orcl12c114-Apr-18 02:04

Container DB IdContainer NameOpen Time
776972821CDB$ROOT14-Apr-18 02:04
4079644691ORCL14-Apr-18 02:04

Host NamePlatformCPUsCoresSocketsMemory (GB)
localhost.localdomainLinux x86 64-bit 12 12 1 24.29

Snap IdSnap TimeSessionsCursors/SessionPluggable Databases Open
Begin Snap:2723-Apr-18 01:12:2450 .71
End Snap:2823-Apr-18 01:30:1346 .71
Elapsed:  17.81 (mins)   
DB Time:  17.82 (mins)   

Report Summary

Top ADDM Findings by Average Active Sessions

Finding NameAvg active sessions of the taskPercent active sessions of findingTask NameBegin Snap TimeEnd Snap Time
Top SQL Statements1.0090.65ADDM:776972821_1_2823-Apr-18 01:1223-Apr-18 01:30

Load Profile

Per SecondPer TransactionPer ExecPer Call
DB Time(s): 1.0 0.2 0.00 0.70
DB CPU(s): 1.0 0.1 0.00 0.70
Background CPU(s): 0.1 0.0 0.00 0.00
Redo size (bytes): 13,476,925.0 1,963,742.7  
Logical read (blocks): 56,799.7 8,276.4  
Block changes: 47,080.6 6,860.2  
Physical read (blocks): 0.1 0.0  
Physical write (blocks): 1,297.1 189.0  
Read IO requests: 0.1 0.0  
Write IO requests: 968.9 141.2  
Read IO (MB): 0.0 0.0  
Write IO (MB): 10.1 1.5  
IM scan rows: 0.0 0.0  
Session Logical Read IM: 0.0 0.0  
User calls: 1.4 0.2  
Parses (SQL): 1.8 0.3  
Hard parses (SQL): 0.0 0.0  
SQL Work Area (MB): 0.1 0.0  
Logons: 0.3 0.1  
Executes (SQL): 6,843.8 997.2  
Rollbacks: 0.0 0.0  
Transactions: 6.9   

Instance Efficiency Percentages (Target 100%)

Buffer Nowait %: 100.00Redo NoWait %: 100.00
Buffer Hit %: 100.00In-memory Sort %: 100.00
Library Hit %: 100.00Soft Parse %: 99.80
Execute to Parse %: 99.97Latch Hit %: 100.00
Parse CPU to Parse Elapsd %: 105.00% Non-Parse CPU: 99.98
Flash Cache Hit %: 0.00

Top 10 Foreground Events by Total Wait Time

EventWaitsTotal Wait Time (sec)Avg Wait% DB timeWait Class
DB CPU 1057.1 98.9 
log file switch (checkpoint incomplete)759.9131.70ms.9Configuration
log file switch completion471.429.04ms.1Configuration
log file switch (private strand flush incomplete)18.739.80ms.1Configuration
undo segment extension27.28.34ms.0Configuration
log file sync14.211.23ms.0Commit
PX Deq: Slave Session Stats248.1398.20us.0Other
library cache: mutex X350799.20us.0Concurrency
db file sequential read980259.38us.0User I/O
PGA memory operation562011.19us.0Other

Wait Classes by Total Wait Time

Wait ClassWaitsTotal Wait Time (sec)Avg Wait Time% DB timeAvg Active Sessions
DB CPU 1,057 98.91.0
System I/O17,25939022.62ms36.50.4
Other5,144132.61ms1.30.0
Configuration1671272.96ms1.10.0
User I/O1,9312794.67us.10.0
Commit21011.07ms.00.0
Concurrency510576.00us.00.0
Network1701.00us.00.0

Host CPU

CPUsCoresSocketsLoad Average BeginLoad Average End%User%System%WIO%Idle
12 12 1 0.44 1.26 6.2 0.4 0.4 93.3

Instance CPU

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

IO Profile

Read+Write Per SecondRead per SecondWrite Per Second
Total Requests: 995.9 6.0 990.0
Database Requests: 969.0 0.1 968.9
Optimized Requests: 0.0 0.0 0.0
Redo Requests: 17.1 0.1 16.9
Total (MB): 23.6 0.1 23.5
Database (MB): 10.1 0.0 10.1
Optimized Total (MB): 0.0 0.0 0.0
Redo (MB): 13.3 0.0 13.3
Database (blocks): 1,297.2 0.1 1,297.1
Via Buffer Cache (blocks): 1,297.2 0.1 1,297.1
Direct (blocks): 0.0 0.0 0.0

Memory Statistics

BeginEnd
Host Mem (MB): 24,868.7 24,868.7
SGA use (MB): 10,240.0 10,240.0
PGA use (MB): 340.3 331.8
% Host Mem used for SGA+PGA: 42.54 42.51

Cache Sizes

BeginEnd
Buffer Cache: 7,424M 7,424MStd Block Size: 8K
Shared Pool Size: 1,721M 1,721MLog Buffer: 20,712K
In-Memory Area: 0M 0M

Shared Pool Statistics

BeginEnd
Memory Usage %: 11.10 11.10
% SQL with executions>1: 90.92 89.46
% Memory for SQL w/exec>1: 80.63 77.08

Main Report


Back to Top

Wait Events Statistics

Back to Top

Time Model Statistics

Statistic NameTime (s)% of DB Time% of Total CPU Time
sql execute elapsed time1,067.6599.85 
DB CPU1,057.1298.8694.31
PL/SQL execution elapsed time270.5225.30 
parse time elapsed0.110.01 
hard parse elapsed time0.010.00 
connection management call elapsed time0.010.00 
PL/SQL compilation elapsed time0.010.00 
repeated bind elapsed time0.000.00 
DB time1,069.29  
background elapsed time444.52  
background cpu time63.72 5.69
total CPU time1,120.84  


Back to Wait Events Statistics
Back to Top

Operating System Statistics

StatisticValueEnd Value
FREE_MEMORY_BYTES165,478,400184,811,520
INACTIVE_MEMORY_BYTES3,744,174,0802,109,632,512
SWAP_FREE_BYTES4,289,433,6004,289,466,368
BUSY_TIME83,854 
IDLE_TIME1,162,089 
IOWAIT_TIME4,381 
SYS_TIME5,477 
USER_TIME77,245 
LOAD01
VM_IN_BYTES65,536 
VM_OUT_BYTES0 
PHYSICAL_MEMORY_BYTES26,076,708,864 
NUM_CPUS12 
NUM_CPU_CORES12 
NUM_CPU_SOCKETS1 
GLOBAL_RECEIVE_SIZE_MAX4,194,304 
GLOBAL_SEND_SIZE_MAX1,048,576 
TCP_RECEIVE_SIZE_DEFAULT87,380 
TCP_RECEIVE_SIZE_MAX6,291,456 
TCP_RECEIVE_SIZE_MIN4,096 
TCP_SEND_SIZE_DEFAULT16,384 
TCP_SEND_SIZE_MAX4,194,304 
TCP_SEND_SIZE_MIN4,096 


Back to Wait Events Statistics
Back to Top

Operating System Statistics - Detail

Snap TimeLoad%busy%user%sys%idle%iowait
23-Apr 01:12:240.44     
23-Apr 01:30:131.266.736.200.4493.270.35


Back to Wait Events Statistics
Back to Top

Foreground Wait Class

Wait ClassWaits%Time -outsTotal Wait Time (s)Avg wait%DB time
DB CPU  1,057 98.86
Configuration167161272.96ms1.14
Commit140011.23ms0.01
Other83510132.57us0.01
Concurrency4400657.80us0.00
User I/O13000203.68us0.00
System I/O148005.93us0.00
Network14001.14us0.00


Back to Wait Events Statistics
Back to Top

Foreground Wait Events

EventWaits%Time -outsTotal Wait Time (s)Avg waitWaits /txn% DB time
log file switch (checkpoint incomplete)75 10131.70ms0.010.92
log file switch completion47 129.04ms0.010.13
log file switch (private strand flush incomplete)18 139.80ms0.000.07
undo segment extension2710008.34ms0.000.02
log file sync14 011.23ms0.000.01
PX Deq: Slave Session Stats248 0398.20us0.030.01
library cache: mutex X35 0799.20us0.000.00
db file sequential read98 0259.38us0.010.00
PGA memory operation562 011.19us0.080.00
enq: BF - allocation contention8 0465.13us0.000.00
Disk file operations I/O32 033.13us0.000.00
watchdog main loop714 2,1433000.76ms0.10 
jobq slave wait84099417496.62ms0.11 
PX Deq: Execution Msg1,263 21.38ms0.17 
PX Deq: Table Q Normal144 0913.03us0.02 
PX Deq: Table Q Sample76 01.49ms0.01 
PX Deq Credit: send blkd204 0225.73us0.03 
SQL*Net message from client14 0701.36us0.00 
PX Deq Credit: need buffer8 0153.88us0.00 


Back to Wait Events Statistics
Back to Top

Background Wait Events

EventWaits%Time -outsTotal Wait Time (s)Avg waitWaits /txn% bg time
log file parallel write8,781020723.56ms1.2046.53
db file async I/O submit9120176192.72ms0.1239.54
LGWR worker group ordering4720816.38ms0.061.74
control file parallel write1,875084.07ms0.261.72
oracle thread bootstrap1070217.46ms0.010.42
LGWR all worker groups262026.97ms0.040.41
db file single write693012.08ms0.090.32
LGWR any worker group136016.13ms0.020.19
enq: CF - contention200019.55ms0.000.09
os thread creation107001.54ms0.010.04
PX Deq: Join ACK49500297.77us0.070.03
reliable message16400876.48us0.020.03
log file single write15400906.23us0.020.03
log file sync70010.76ms0.000.02
control file sequential read5,2350012.26us0.710.01
PX Deq: Slave Join Frag49600122.47us0.070.01
PX Deq: Slave Session Stats24800211.91us0.030.01
db file sequential read7320067.97us0.100.01
latch free4600835.78us0.010.01
PX Deq: Signal ACK EXT24800107.74us0.030.01
PGA memory operation1,4070013.86us0.190.00
ADR block file read14001.23ms0.000.00
Disk file operations I/O3750041.84us0.050.00
db file scattered read3001.24ms0.000.00
enq: PS - contention1500232.47us0.000.00
latch: redo allocation700478.86us0.000.00
LGWR wait for redo copy900363.11us0.000.00
log file sequential read1540021.20us0.020.00
ADR block file write300789.00us0.000.00
PX Idle Wait250051,286205.14 s0.03 
rdbms ipc message30,1193916,518548.44ms4.11 
Space Manager: slave idle wait2,430011,2004609.08ms0.33 
pmon timer790872,1382706.30ms0.11 
DIAG idle wait2,1351002,1361000.68ms0.29 
LGWR worker group idle8,98501,922213.91ms1.23 
smon timer1501,32288.15 s0.00 
Data Guard: Timer201,200600.00 s0.00 
dispatcher timer181001,08060.00 s0.00 
AQPC idle361001,08030.00 s0.00 
Data Guard: Gap Manager1801,08060.00 s0.00 
OFS idle3571001,0713000.91ms0.05 
pman timer3571001,0713000.39ms0.05 
heartbeat redo informer1,06801,0691001.06ms0.15 
lreg timer3551001,0693011.20ms0.05 
wait for unread message on broadcast channel1,0681001,0691000.68ms0.15 
Streams AQ: qmn slave idle wait3901,06427.28 s0.01 
Streams AQ: qmn coordinator idle wait7701,06413.82 s0.01 
PX Deq: Parse Reply248001.66ms0.03 
PX Deq: Execute Reply1,11000184.96us0.15 
class slave wait9100381.52us0.01 
SQL*Net message from client400671.50us0.00 
PX Deq Credit: send blkd250068.64us0.00 


Back to Wait Events Statistics
Back to Top

Wait Event Histogram

  % of Total Waits
EventTotal Waits<8us<16us<32us<64us<128us<256us<512us>=512us
ADR block file read14      21.478.6
ADR block file write3       100.0
ADR file lock450.0  25.025.0   
AWR Flush2100.0       
Disk file operations I/O4082.916.724.843.410.01.5.5.2
LGWR all worker groups262  .45.728.227.56.531.7
LGWR any worker group136   .7.74.46.687.5
LGWR wait for redo copy9   11.122.211.122.233.3
LGWR worker group ordering472   .2.61.93.094.3
PGA memory operation197142.250.33.41.51.4.7.4.1
PX Deq: Join ACK49518.0 1.010.98.915.822.423.0
PX Deq: Signal ACK EXT24849.2 .4.48.928.611.7.8
PX Deq: Slave Join Frag49650.0 6.512.77.97.35.89.9
PX Deq: Slave Session Stats49625.4 .21.24.816.331.021.0
PX qref latch560.0  40.0    
SQL*Net message to client17100.0       
asynch descriptor resize5064.032.0 2.02.0   
buffer busy waits333.3 33.3 33.3   
control file parallel write1875       100.0
control file sequential read538352.926.916.52.11.1.4.1.0
db file async I/O submit914       100.0
db file scattered read3   33.3   66.7
db file sequential read854.733.057.73.21.8.5.52.7
db file single write693      9.590.5
enq: BF - allocation contention8    25.0 37.537.5
enq: CF - contention20      5.095.0
enq: PS - contention15  6.7 13.333.340.06.7
latch free472.1  2.112.86.421.355.3
latch: active service list2     50.050.0 
latch: cache buffers lru chain3   33.366.7   
latch: call allocation1     100.0  
latch: messages2   50.0  50.0 
latch: object queue header operation1       100.0
latch: redo allocation8    37.512.537.512.5
latch: shared pool5  20.020.0 40.0 20.0
library cache: bucket mutex X3100.0       
library cache: mutex X3669.42.8 5.62.85.62.811.1
log file parallel write8788      .199.9
log file sequential read15432.530.526.63.25.21.3.6 
log file single write154      28.671.4
log file switch (checkpoint incomplete)75       100.0
log file switch (private strand flush incomplete)18       100.0
log file switch completion47       100.0
log file sync22      4.595.5
oracle thread bootstrap107       100.0
os thread creation107       100.0
reliable message164   9.81.823.220.145.1
row cache mutex4  75.025.0    
undo segment extension27 40.7  3.7  55.6
AQPC idle36       100.0
DIAG idle wait2136       100.0
Data Guard: Gap Manager18       100.0
Data Guard: Timer2       100.0
LGWR worker group idle8990  .1.2.2.3.299.0
OFS idle356       100.0
PX Deq Credit: need buffer8   12.562.512.5 12.5
PX Deq Credit: send blkd2291.3.45.224.539.317.07.94.4
PX Deq: Execute Reply111032.31.23.68.919.516.810.67.0
PX Deq: Execution Msg1264.6.41.44.48.816.121.347.0
PX Deq: Parse Reply24840.3   .82.84.451.6
PX Deq: Table Q Normal14422.2  9.07.620.815.325.0
PX Deq: Table Q Sample76     2.648.748.7
PX Idle Wait248      6.094.0
SQL*Net message from client18    22.233.311.133.3
Space Manager: slave idle wait2431     .0 100.0
Streams AQ: qmn coordinator idle wait771.3   41.63.92.650.6
Streams AQ: qmn slave idle wait392.6      97.4
class slave wait91    1.127.557.114.3
dispatcher timer18       100.0
heartbeat redo informer1068       100.0
jobq slave wait842       100.0
lreg timer356       100.0
pman timer357       100.0
pmon timer791     .1.199.7
rdbms ipc message30.1K1.2.0.3.4.6.4.396.7
smon timer15       100.0
wait for unread message on broadcast channel1068       100.0
watchdog main loop712       100.0


Back to Wait Events Statistics
Back to Top

Wait Event Histogram (up to 64 us)

  % of Total Waits
EventWaits 1us - 64us <1us <2us <4us<8us<16us<32us<64us>=64us
ADR file lock3 25.0 25.0  25.025.0
AWR Flush250.0 50.0     
Disk file operations I/O358 .71.7.516.724.843.412.3
LGWR all worker groups16     .45.793.9
LGWR any worker group1      .799.3
LGWR wait for redo copy1      11.188.9
LGWR worker group ordering1      .299.8
PGA memory operation1920  2.040.250.33.41.52.6
PX Deq: Join ACK1481.611.35.1  1.010.970.1
PX Deq: Signal ACK EXT124 19.428.21.6 .4.450.0
PX Deq: Slave Join Frag34333.116.9   6.512.730.8
PX Deq: Slave Session Stats1336.317.31.8  .21.273.2
PX qref latch5   60.0  40.0 
SQL*Net message to client1729.458.85.95.9    
asynch descriptor resize49 34.018.012.032.0 2.02.0
buffer busy waits2   33.3 33.3 33.3
control file sequential read5298  .752.226.916.52.11.6
db file scattered read1      33.366.7
db file sequential read808   .733.057.73.25.4
enq: PS - contention1     6.7 93.3
latch free2   2.1  2.195.7
latch: cache buffers lru chain1      33.366.7
latch: messages1      50.050.0
latch: shared pool2     20.020.060.0
library cache: bucket mutex X3   100.0    
library cache: mutex X28  5.663.92.8 5.622.2
log file sequential read143   32.530.526.63.27.1
reliable message16      9.890.2
row cache mutex4     75.025.0 
undo segment extension11    40.7  59.3


Back to Wait Events Statistics
Back to Top

Wait Event Histogram (up to 32 ms)

  % of Total Waits
EventWaits 1ms to 32ms <512us <1ms <2ms <4ms<8ms<16ms<32ms>=32ms
ADR block file read1121.435.735.7 7.1   
ADR block file write3 100.0      
Disk file operations I/O199.8.2      
LGWR all worker groups5768.33.11.1 1.96.59.29.9
LGWR any worker group11912.59.614.714.016.924.38.1 
LGWR wait for redo copy366.733.3      
LGWR worker group ordering4115.74.22.51.912.524.241.77.2
PGA memory operation299.9 .1     
PX Deq: Join ACK11477.022.4.2.4    
PX Deq: Signal ACK EXT299.2.8      
PX Deq: Slave Join Frag4990.19.5.4     
PX Deq: Slave Session Stats10479.019.41.6     
control file parallel write1869  33.047.68.17.33.7.3
control file sequential read1100.0.0      
db file async I/O submit187 .4.4.42.55.810.879.5
db file scattered read233.3 33.333.3    
db file sequential read2397.3.6.7.7.4.4  
db file single write6279.548.619.011.16.34.21.2 
enq: BF - allocation contention362.525.012.5     
enq: CF - contention175.0  10.0 10.065.010.0
enq: PS - contention193.36.7      
latch free2644.736.212.84.32.1   
latch: object queue header operation1 100.0      
latch: redo allocation187.5 12.5     
latch: shared pool180.020.0      
library cache: mutex X488.9 2.8 2.85.6  
log file parallel write7536.1.71.01.15.315.462.214.1
log file single write11028.662.35.21.9 1.9  
log file switch (checkpoint incomplete)49     40.025.334.7
log file switch (private strand flush incomplete)12     27.838.933.3
log file switch completion38     36.244.719.1
log file sync204.54.54.54.513.654.59.14.5
oracle thread bootstrap107     19.680.4 
os thread creation107  94.45.6    
reliable message7454.918.320.73.01.21.8  
undo segment extension1444.4    44.47.43.7


Back to Wait Events Statistics
Back to Top

Wait Event Histogram (up to 2 sec)

  % of Total Waits
EventWaits 64ms to 2s<32ms<64ms<1/8s<1/4s<1/2s <1s <2s>=2s
LGWR all worker groups2690.19.9      
LGWR worker group ordering3492.86.8.4     
control file parallel write699.7.3      
db file async I/O submit72720.517.620.16.629.85.5  
enq: CF - contention290.010.0      
log file parallel write124285.913.8.3.0    
log file switch (checkpoint incomplete)2665.36.76.74.09.36.71.3 
log file switch (private strand flush incomplete)666.711.116.75.6    
log file switch completion980.910.68.5     
log file sync195.54.5      
undo segment extension196.33.7      


Back to Wait Events Statistics
Back to Top

Wait Event Histogram (up to 2 min)

No data exists for this section of the report.

Back to Wait Events Statistics
Back to Top

Wait Event Histogram (up to 1 hr)

No data exists for this section of the report.

Back to Wait Events Statistics
Back to Top

Service Statistics

Service NameDB Time (s)DB CPU (s)Physical Reads (K)Logical Reads (K)
orcl1,0671,054060,699
SYS$BACKGROUND12018
orcl12c1105
SYS$USERS0001
orcl12cXDB0000


Back to Wait Events Statistics
Back to Top

Service Wait Class Stats

Service NameUser I/O Total WtsUser I/O Wt TimeConcurcy Total WtsConcurcy Wt TimeAdmin Total WtsAdmin Wt TimeNetwork Total WtsNetwork Wt Time
orcl 1006000110
SYS$BACKGROUND 189824500000
orcl12c 470000030
SYS$USERS 200000000


Back to Wait Events Statistics
Back to Top

Top 10 Channel Waits

ChannelWaitsTotal Wait Time (s)Avg Wait
RBR channel 1640869.59us


Back to Wait Events Statistics
Back to Top

SQL Statistics

Back to Top

SQL ordered by Elapsed Time

Elapsed Time (s)Executions Elapsed Time per Exec (s) %Total%CPU%IO SQL IdSQL ModulePDB NameSQL Text
1,068.670 99.9498.820.003cjmgxvhv3n2p SQL*Plus ORCL DECLARE TYPE ID_t IS TABLE OF ...
550.067,313,8080.0051.4499.040.008kpwagy566cc6 SQL*Plus ORCL INSERT INTO DELUDING_TAB VALUE...
2.4112.410.2399.980.00g6px76dmjv1jy   ORCL select count(*) from wri$_opts...
1.9211.920.18100.000.00g6px76dmjv1jy   CDB$ROOT select count(*) from wri$_opts...
0.7410.740.0796.204.11akhh0ydabdahf SQL*Plus CDB$ROOT BEGIN :snap# := dbms_workload_...
0.5360.090.05100.200.0022356bkgsdcnh   CDB$ROOT SELECT COUNT(*) FROM X$KSPPI A...
0.24360.010.02100.560.003zmzdajzvjkfw   CDB$ROOT select /*+ no_monitor no_state...
0.2120.110.0299.620.73fhf8upax5cxsz MMON_SLAVE CDB$ROOT BEGIN sys.dbms_auto_report_int...
0.20560.000.0299.160.0049s332uhbnsma   CDB$ROOT declare vsn varchar2(20); beg...
0.1820.090.0299.030.820w26sk6t6gq98 MMON_SLAVE CDB$ROOT SELECT XMLTYPE(DBMS_REPORT.GET...


Back to SQL Statistics
Back to Top

SQL ordered by CPU Time

CPU Time (s)Executions CPU per Exec (s)%TotalElapsed Time (s)%CPU%IO SQL IdSQL ModulePDB NameSQL Text
1,056.030 99.901,068.6798.820.003cjmgxvhv3n2p SQL*Plus ORCL DECLARE TYPE ID_t IS TABLE OF ...
544.817,313,8080.0051.54550.0699.040.008kpwagy566cc6 SQL*Plus ORCL INSERT INTO DELUDING_TAB VALUE...
2.4112.410.232.4199.980.00g6px76dmjv1jy   ORCL select count(*) from wri$_opts...
1.9211.920.181.92100.000.00g6px76dmjv1jy   CDB$ROOT select count(*) from wri$_opts...
0.7110.710.070.7496.204.11akhh0ydabdahf SQL*Plus CDB$ROOT BEGIN :snap# := dbms_workload_...
0.5360.090.050.53100.200.0022356bkgsdcnh   CDB$ROOT SELECT COUNT(*) FROM X$KSPPI A...
0.24360.010.020.24100.560.003zmzdajzvjkfw   CDB$ROOT select /*+ no_monitor no_state...
0.2120.110.020.2199.620.73fhf8upax5cxsz MMON_SLAVE CDB$ROOT BEGIN sys.dbms_auto_report_int...
0.20560.000.020.2099.160.0049s332uhbnsma   CDB$ROOT declare vsn varchar2(20); beg...
0.1720.090.020.1899.030.820w26sk6t6gq98 MMON_SLAVE CDB$ROOT SELECT XMLTYPE(DBMS_REPORT.GET...


Back to SQL Statistics
Back to Top

SQL ordered by User I/O Wait Time

User I/O Time (s)Executions UIO per Exec (s)%TotalElapsed Time (s)%CPU%IO SQL IdSQL ModulePDB NameSQL Text
0.0310.031.970.7496.204.11akhh0ydabdahf SQL*Plus CDB$ROOT BEGIN :snap# := dbms_workload_...
0.0310.031.740.0551.0751.216xa9pr89f6u0f   CDB$ROOT insert into wrh$_latch (dbid, ...
0.0010.000.170.0493.807.29b13g21mgg8y98   CDB$ROOT insert /* KSXM:TAKE_SNPSHOT */...
0.0020.000.100.2199.620.73fhf8upax5cxsz MMON_SLAVE CDB$ROOT BEGIN sys.dbms_auto_report_int...
0.0020.000.090.1899.030.820w26sk6t6gq98 MMON_SLAVE CDB$ROOT SELECT XMLTYPE(DBMS_REPORT.GET...
0.0020.000.080.1299.861.021y4f4wtu63797 MMON_SLAVE CDB$ROOT WITH MONITOR_DATA AS (SELECT I...
0.0090.000.040.0186.546.480qbzfjt00pbsx DBMS_SCHEDULER ORCL BEGIN dbms_isched.log_dbms_out...
0.0010.000.020.05101.450.697ugy3sxj9aw5t   CDB$ROOT insert into WRH$_SYSSTAT (dbid...
0.000 0.021,068.6798.820.003cjmgxvhv3n2p SQL*Plus ORCL DECLARE TYPE ID_t IS TABLE OF ...
0.007,313,8080.000.02550.0699.040.008kpwagy566cc6 SQL*Plus ORCL INSERT INTO DELUDING_TAB VALUE...


Back to SQL Statistics
Back to Top

SQL ordered by Gets

Buffer Gets ExecutionsGets per Exec %TotalElapsed Time (s) %CPU %IO SQL IdSQL ModulePDB NameSQL Text
60,707,8600 100.011,068.6798.803cjmgxvhv3n2p SQL*Plus ORCL DECLARE TYPE ID_t IS TABLE OF ...
60,707,4367,313,8088.30100.01550.069908kpwagy566cc6 SQL*Plus ORCL INSERT INTO DELUDING_TAB VALUE...
11,456111,456.000.020.7496.24.1akhh0ydabdahf SQL*Plus CDB$ROOT BEGIN :snap# := dbms_workload_...
3,80313,803.000.010.05101.5.77ugy3sxj9aw5t   CDB$ROOT insert into WRH$_SYSSTAT (dbid...
3,4951,7472.000.010.07103.2087gaftwrm2h68   CDB$ROOT select o.owner#, o.name, o.nam...
1,24411,244.000.000.0551.151.26xa9pr89f6u0f   CDB$ROOT insert into wrh$_latch (dbid, ...
9931417.040.000.04100.9.53dbzmtf9ahvzt   CDB$ROOT merge /* KSXM:OPTIM_DML_INF */...
9791979.000.000.06100.3.36ajkhukk78nsr   CDB$ROOT begin prvt_hdm.auto_execute( :...
7662383.000.000.2199.6.7fhf8upax5cxsz MMON_SLAVE CDB$ROOT BEGIN sys.dbms_auto_report_int...
5722286.000.000.1899.80w26sk6t6gq98 MMON_SLAVE CDB$ROOT SELECT XMLTYPE(DBMS_REPORT.GET...


Back to SQL Statistics
Back to Top

SQL ordered by Reads

Physical ReadsExecutionsReads per Exec %TotalElapsed Time (s)%CPU%IO SQL IdSQL ModulePDB NameSQL Text
85185.0055.560.7496.204.11akhh0ydabdahf SQL*Plus CDB$ROOT BEGIN :snap# := dbms_workload_...
22122.0014.380.0551.0751.216xa9pr89f6u0f   CDB$ROOT insert into wrh$_latch (dbid, ...
18118.0011.760.05101.450.697ugy3sxj9aw5t   CDB$ROOT insert into WRH$_SYSSTAT (dbid...
10110.006.540.06100.330.306ajkhukk78nsr   CDB$ROOT begin prvt_hdm.auto_execute( :...
80 5.231,068.6798.820.003cjmgxvhv3n2p SQL*Plus ORCL DECLARE TYPE ID_t IS TABLE OF ...
87,313,8080.005.23550.0699.040.008kpwagy566cc6 SQL*Plus ORCL INSERT INTO DELUDING_TAB VALUE...
616.003.920.0493.807.29b13g21mgg8y98   CDB$ROOT insert /* KSXM:TAKE_SNPSHOT */...
515.003.270.1098.280.092qdkzww4mdpvx   CDB$ROOT insert into wrh$_parameter (db...
212.001.310.0079.390.630b6pnaramchk3   CDB$ROOT insert into WRH$_IOSTAT_FILETY...
221.001.310.2199.620.73fhf8upax5cxsz MMON_SLAVE CDB$ROOT BEGIN sys.dbms_auto_report_int...


Back to SQL Statistics
Back to Top

SQL ordered by Physical Reads (UnOptimized)

UnOptimized Read ReqsPhysical Read ReqsExecutionsUnOptimized Reqs per Exec%Opt%Total SQL IdSQL ModulePDB NameSQL Text
3133131313.000.00221.99akhh0ydabdahf SQL*Plus CDB$ROOT BEGIN :snap# := dbms_workload_...
7070170.000.0049.650b6pnaramchk3   CDB$ROOT insert into WRH$_IOSTAT_FILETY...
6868168.000.0048.234dy540fw5qm7s   CDB$ROOT SELECT COUNT(*) FROM (SELECT F...
2222122.000.0015.606xa9pr89f6u0f   CDB$ROOT insert into wrh$_latch (dbid, ...
1818118.000.0012.777ugy3sxj9aw5t   CDB$ROOT insert into WRH$_SYSSTAT (dbid...
161628.000.0011.35fhf8upax5cxsz MMON_SLAVE CDB$ROOT BEGIN sys.dbms_auto_report_int...
141427.000.009.930w26sk6t6gq98 MMON_SLAVE CDB$ROOT SELECT XMLTYPE(DBMS_REPORT.GET...
141427.000.009.931y4f4wtu63797 MMON_SLAVE CDB$ROOT WITH MONITOR_DATA AS (SELECT I...
1414560.250.009.9349s332uhbnsma   CDB$ROOT declare vsn varchar2(20); beg...
880 0.005.673cjmgxvhv3n2p SQL*Plus ORCL DECLARE TYPE ID_t IS TABLE OF ...
887,313,8080.000.005.678kpwagy566cc6 SQL*Plus ORCL INSERT INTO DELUDING_TAB VALUE...
6623.000.004.2628bgqbzpa87xf   CDB$ROOT declare policy varchar2(512);...
5515.000.003.552qdkzww4mdpvx   CDB$ROOT insert into wrh$_parameter (db...
4414.000.002.84b13g21mgg8y98   CDB$ROOT insert /* KSXM:TAKE_SNPSHOT */...


Back to SQL Statistics
Back to Top

SQL ordered by Executions

Executions Rows ProcessedRows per ExecElapsed Time (s) %CPU %IO SQL IdSQL ModulePDB NameSQL Text
7,313,8087,313,8081.00550.069908kpwagy566cc6 SQL*Plus ORCL INSERT INTO DELUDING_TAB VALUE...
1,74710.000.07103.2087gaftwrm2h68   CDB$ROOT select o.owner#, o.name, o.nam...
1411411.000.04100.9.53dbzmtf9ahvzt   CDB$ROOT merge /* KSXM:OPTIM_DML_INF */...
81811.000.0141.51.43y6pgnk2ubw7g   CDB$ROOT insert into wrm$_snapshot_deta...
70701.000.00100.901rpgk59t8pvs6   CDB$ROOT begin dbms_output.get_line(:l...
56561.000.2099.2049s332uhbnsma   CDB$ROOT declare vsn varchar2(20); beg...
3600.000.24100.603zmzdajzvjkfw   CDB$ROOT select /*+ no_monitor no_state...
36361.000.1678.106nas5twtqzkk0   CDB$ROOT select /*+ no_monitor no_state...
3600.000.00460f3yqgm464ygss   CDB$ROOT SELECT /*+ NO_STATEMENT_QUEUIN...
3600.000.0072.90f3yqgm464ygss   ORCL SELECT /*+ NO_STATEMENT_QUEUIN...


Back to SQL Statistics
Back to Top

SQL ordered by Parse Calls

Parse CallsExecutions % Total Parses SQL IdSQL ModulePDB NameSQL Text
180369.143zmzdajzvjkfw   CDB$ROOT select /*+ no_monitor no_state...
1411417.163dbzmtf9ahvzt   CDB$ROOT merge /* KSXM:OPTIM_DML_INF */...
108365.496nas5twtqzkk0   CDB$ROOT select /*+ no_monitor no_state...
81814.113y6pgnk2ubw7g   CDB$ROOT insert into wrm$_snapshot_deta...
70703.561rpgk59t8pvs6   CDB$ROOT begin dbms_output.get_line(:l...
56562.8449s332uhbnsma   CDB$ROOT declare vsn varchar2(20); beg...
36361.83f3yqgm464ygss   CDB$ROOT SELECT /*+ NO_STATEMENT_QUEUIN...
36361.83f3yqgm464ygss   ORCL SELECT /*+ NO_STATEMENT_QUEUIN...
36361.83fjfm1sbw3k2y6   CDB$ROOT SELECT /*+ NO_STATEMENT_QUEUIN...
36361.83fjfm1sbw3k2y6   ORCL SELECT /*+ NO_STATEMENT_QUEUIN...
36361.83gjaap3w3qbf8c   CDB$ROOT select count(*) from ilmobj$ w...
36361.83gjaap3w3qbf8c   ORCL select count(*) from ilmobj$ w...
351,7471.7887gaftwrm2h68   CDB$ROOT select o.owner#, o.name, o.nam...
26261.323dbzmtf9ahvzt   ORCL merge /* KSXM:OPTIM_DML_INF */...
20201.026n2qqv1brfhpp   CDB$ROOT select ind.type#, ind.property...


Back to SQL Statistics
Back to Top

SQL ordered by Sharable Memory

Sharable Mem (b)Executions % Total SQL IdSQL ModulePDB NameSQL Text
4,441,47020.251y4f4wtu63797 MMON_SLAVE CDB$ROOT WITH MONITOR_DATA AS (SELECT I...


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

Complete List of SQL Text

SQL IdSQL Text
0b6pnaramchk3 insert into WRH$_IOSTAT_FILETYPE (dbid, per_pdb, con_dbid, snap_id, instance_number, filetype_id, small_read_megabytes, small_write_megabytes, large_read_megabytes, large_write_megabytes, small_read_reqs, small_write_reqs, small_sync_read_reqs, large_read_reqs, large_write_reqs, small_read_servicetime, small_write_servicetime, small_sync_read_latency, large_read_servicetime, large_write_servicetime, retries_on_error ) select :dbid, if.con_id, con_id_to_dbid(if.con_id) con_dbid, :snap_id, :instance_number, filetype_id, sum(small_read_megabytes) small_read_megabytes, sum(small_write_megabytes) small_write_megabytes, sum(large_read_megabytes) large_read_megabytes, sum(large_write_megabytes) large_write_megabytes, sum(small_read_reqs) small_read_reqs, sum(small_write_reqs) small_write_reqs, sum(small_sync_read_reqs) small_sync_read_reqs, sum(large_read_reqs) large_read_ reqs, sum(large_write_reqs) large_write_reqs, sum(small_read_servicetime) small_read_servicetime, sum(small_write_servicetime) small_write_servicetime, sum(small_sync_read_latency) small_sync_read_latency, sum(large_read_servicetime) large_read_servicetime, sum(large_write_servicetime) large_write_servicetime, sum(retries_on_error) retries_on_error FROM v$iostat_file if WHERE (1 = 1) GROUP BY if.con_id, filetype_id
0qbzfjt00pbsxBEGIN dbms_isched.log_dbms_output( :logid, :errs, :outputWasEnabled ); END;
0w26sk6t6gq98SELECT XMLTYPE(DBMS_REPORT.GET_REPORT_WITH_SUMMARY(:B1 )) FROM DUAL
1rpgk59t8pvs6 begin dbms_output.get_line(:line, :status); end;
1y4f4wtu63797 WITH MONITOR_DATA AS (SELECT INST_ID, KEY, NVL2(PX_QCSID, NULL, STATUS) STATUS, FIRST_REFRESH_TIME, LAST_REFRESH_TIME, REFRESH_COUNT, PROCESS_NAME, SID, SQL_ID, SQL_EXEC_START, SQL_EXEC_ID, DBOP_NAME, DBOP_EXEC_ID, SQL_PLAN_HASH_VALUE, SQL_FULL_PLAN_HASH_VALUE, SESSION_SERIAL#, SQL_TEXT, IS_FULL_SQLTEXT, PX_SERVER#, PX_SERVER_GROUP, PX_SERVER_SET, PX_QCINST_ID, PX_QCSID, CASE WHEN ELAPSED_TIME < (CPU_TIME+ APPLICATION_WAIT_TIME+ CONCURRENCY_WAIT_TIME+ CLUSTER_WAIT_TIME+ USER_IO_WAIT_TIME+ QUEUING_TIME) THEN (CPU_TIME+ APPLICATION_WAIT_TIME+ CONCURRENCY_WAIT_TIME+ CLUSTER_WAIT_TIME+ USER_IO_WAIT_TIME+ QUEUING_TIME) ELSE ELAPSED_TIME END ELAPSED_TIME, QUEUING_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+ QUEUING_TIME) THEN 0 ELSE ELAPSED_TIME - (CPU_TIME+ APPLICATION_WAIT_TIME+ C ONCURRENCY_WAIT_TIME+ CLUSTER_WAIT_TIME+ USER_IO_WAIT_TIME+ QUEUING_TIME) END OTHER_WAIT_TIME, PLSQL_EXEC_TIME, JAVA_EXEC_TIME, FETCHES, BUFFER_GETS, IO_INTERCONNECT_BYTES IO_INTER_BYTES, PHYSICAL_READ_REQUESTS READ_REQS, PHYSICAL_READ_BYTES READ_BYTES, PHYSICAL_WRITE_REQUESTS WRITE_REQS, PHYSICAL_WRITE_BYTES WRITE_BYTES, NVL(PHYSICAL_READ_BYTES, 0) + NVL(PHYSICAL_WRITE_BYTES, 0) IO_BYTES, NVL(PHYSICAL_READ_REQUESTS, 0) + NVL(PHYSICAL_WRITE_REQUESTS, 0) IO_REQS, IO_CELL_UNCOMPRESSED_BYTES IO_UNC_BYTES, IO_CELL_OFFLOAD_ELIGIBLE_BYTES IO_ELIG_BYTES, IO_CELL_OFFLOAD_RETURNED_BYTES IO_RET_BYTES, USER# USERID, USERNAME, MODULE, ACTION, SERVICE_NAME, CLIENT_IDENTIFIER, CLIENT_INFO, PROGRAM, PLSQL_OBJECT_ID PL_OID, PLSQL_SUBPROGRAM_ID PL_PROGID, PLSQL_ENTRY_OBJECT_ID PL_ENTRY_OID, PLSQL_ENTRY_SUBPROGRAM_ID PL_ENTRY_PROGID, PX_MAXDOP MAX_DOP, PX_IS_CROSS_INSTANCE, PX_MAXDOP_INSTANCES MAX_DOP_INSTANCES, PX_SERVERS_REQUESTED SERVERS_REQUESTED, PX_SERVERS_ALLOCA TED SERVERS_ALLOCATED, ERROR_NUMBER, ERROR_FACILITY, ERROR_MESSAGE, NVL2(OTHER_XML, 'Y', NULL) HAS_OTHER_XML, NVL2(BINDS_XML, 'Y', NULL) HAS_BINDS_XML, NVL2(RM_CONSUMER_GROUP, NVL2(RM_LAST_ACTION_TIME, TO_CHAR(RM_LAST_ACTION_TIME, :B12 ), '00/00/0000 00:00:01') || XMLELEMENT( "rminfo", XMLATTRIBUTES( RM_LAST_ACTION AS "rmlastact", RM_LAST_ACTION_REASON AS "rmlastreason", TO_CHAR(RM_LAST_ACTION_TIME, :B12 ) AS "rmlasttime", RM_CONSUMER_GROUP AS "rmcg")).GETSTRINGVAL(), '00/00/0000 00:00:00') RM_INFO, CON_NAME, CON_ID FROM GV$SQL_MONITOR MO1 WHERE MO1.INST_ID BETWEEN :B11 AND :B10 AND MO1.SQL_ID = NVL(:B9 , MO1.SQL_ID) AND MO1.SQL_EXEC_START = NVL(:B8 , MO1.SQL_EXEC_START) AND MO1.SQL_EXEC_ID = NVL(:B7 , MO1.SQL_EXEC_ID) OR (MO1.DBOP_NAME = NVL(:B6 , MO1.DBOP_NAME) AND MO1.DBOP_EXEC_ID = NVL(:B5 , MO1.DBOP_EXEC_ID)) AND ((:B4 = 1 AND MO1.PX_QCSID IS NULL) OR (MO1.PX_SERVER_GROUP = NVL(:B3 , MO1.PX_SERVER_GROUP) AND MO1.PX_SERVER_SET = NVL(:B2 , MO1.PX_SERVER_SET) AND MO1.PX_SERVER# = NVL(:B1 , MO1.PX_SERVER#)))), MONITOR_AGG AS (SELECT MAX_PX_QCSID, MAX_KEY, MAX_INST_ID, MAX_SESSION_ID, MAX_SESSION_SERIAL, MAX_PX_DOP, MAX_PX_DOP_INSTANCES, MAX_PX_IS_CROSS_INSTANCE, SUM_SERVERS_REQUESTED, SUM_SERVERS_ALLOCATED, DIST_INST_COUNT, DIST_PX_GROUP_COUNT, DIST_PX_SET_COUNT, MAX_PLAN_HASH_VALUE, MAX_FULL_PLAN_HASH_VALUE, MAX_USERID, MAX_PROGRAM, MAX_USERNAME, MAX_MODULE, MAX_ACTION, MAX_SERVICE_NAME, MAX_CLIENT_ID, MAX_CLIENT_INFO, MAX_ERROR_NUMBER, MAX_ERROR_FACILITY, MAX_ERROR_MESSAGE, QC_HAS_OTHER_XML, QC_HAS_BINDS_XML, MAX_PL_OID, MAX_PL_PROGID, MAX_PL_ENTRY_OID, MAX_PL_ENTRY_PROGID, MAX_SQL_ID, MAX_SQL_EXEC_START, MAX_SQL_EXEC_ID, MAX_LAST_REFRESH_TIME, MAX_DBOP_NAME, MAX_DBOP_EXEC_ID, CASE WHEN MAX_PL_OID IS NOT NULL THEN NVL((SELECT P.OWNER || '.' || P.OBJECT_NAME || DECODE(P.PROCEDURE_NAME, NULL, '', '.' || P.PROCEDURE_NAME) FROM DBA_PROCEDURES P WH ERE P.OBJECT_ID = MAX_PL_OID AND P.SUBPROGRAM_ID = MAX_PL_PROGID AND ROWNUM = 1), 'Unavailable') END MAX_PL_NAME, CASE WHEN MAX_PL_ENTRY_OID IS NOT NULL THEN NVL((SELECT P.OWNER || '.' || P.OBJECT_NAME || DECODE(P.PROCEDURE_NAME, NULL, '', '.' || P.PROCEDURE_NAME) FROM DBA_PROCEDURES P WHERE P.OBJECT_ID = MAX_PL_ENTRY_OID AND P.SUBPROGRAM_ID = MAX_PL_ENTRY_PROGID AND ROWNUM = 1), 'Unavailable') END MAX_PL_ENTRY_NAME, MAX_STATUS, SUM_REFRESH_COUNT, MIN_FIRST_REFRESH_TIME, IS_FULL_TEXT, SQLMON_TEXT, SUM_ELAPSED_TIME, MAX_ELAPSED_TIME, MAX_QUEUING_TIME, SUM_CPU_TIME, SUM_USER_IO_WAIT_TIME, SUM_APPLICATION_WAIT_TIME, SUM_CONCURRENCY_WAIT_TIME, SUM_CLUSTER_WAIT_TIME, SUM_PLSQL_EXEC_TIME, SUM_JAVA_EXEC_TIME, SUM_OTHER_WAIT_TIME, SUM_FETCHES, SUM_BUFFER_GETS, SUM_READ_REQS, SUM_READ_BYTES, SUM_WRITE_REQS, SUM_WRITE_BYTES, SUM_IO_BYTES, SUM_IO_INTER_BYTES, SUM_IO_UNC_BYTES, SUM_IO_ELIG_BYTES, SUM_IO_RET_BYTES, DECODE(:B14 , 1, 'db_name', DB.DB_UNIQUE_NAME) DB_UNIQUE_NAME, DECODE(:B14 , 1, 'platform_name', DB.PLATFORM_NAME) PLATFORM_NAME, DECODE(:B14 , 1, 'host_name', INST.HOST_NAME) HOST_NAME, AGG_RM_INFO MAX_RM_INFO, MAX_CON_NAME, DECODE(MAX_CON_ID, 0, NULL, MAX_CON_ID) MAX_CON_ID FROM (SELECT MAX(PX_QCSID) MAX_PX_QCSID, MAX(CASE WHEN PX_QCSID IS NULL THEN KEY ELSE NULL END) MAX_KEY, MAX(CASE WHEN PX_QCSID IS NULL THEN INST_ID ELSE NULL END) MAX_INST_ID, MAX(CASE WHEN PX_QCSID IS NULL THEN SID ELSE NULL END) MAX_SESSION_ID, MAX(CASE WHEN PX_QCSID IS NULL THEN SESSION_SERIAL# ELSE NULL END) MAX_SESSION_SERIAL, MAX(MAX_DOP) MAX_PX_DOP, MAX(MAX_DOP_INSTANCES) MAX_PX_DOP_INSTANCES, MAX(PX_IS_CROSS_INSTANCE) MAX_PX_IS_CROSS_INSTANCE, SUM(SERVERS_REQUESTED) SUM_SERVERS_REQUESTED, SUM(SERVERS_ALLOCATED) SUM_SERVERS_ALLOCATED, COUNT(DISTINCT INST_ID) DIST_INST_COUNT, COUNT(DISTINCT PX_SERVER_GROUP) DIST_PX_GROUP_COUNT, COUNT(DISTINCT PX_SERVER_SET) DIST_PX_SET_COUNT, MAX(SQL_PLAN_HASH_VALUE) MAX_PLAN_HASH_VALUE, MAX( SQL_FULL_PLAN_HASH_VALUE) MAX_FULL_PLAN_HASH_VALUE, MAX(USERID) MAX_USERID, MAX(PROGRAM) MAX_PROGRAM, MAX(USERNAME) MAX_USERNAME, MAX(MODULE) MAX_MODULE, MAX(ACTION) MAX_ACTION, MAX(SERVICE_NAME) MAX_SERVICE_NAME, MAX(CLIENT_IDENTIFIER) MAX_CLIENT_ID, MAX(CLIENT_INFO) MAX_CLIENT_INFO, MAX(ERROR_NUMBER) MAX_ERROR_NUMBER, MAX(ERROR_FACILITY) MAX_ERROR_FACILITY, MAX(ERROR_MESSAGE) MAX_ERROR_MESSAGE, MAX(NVL2(PX_QCSID, HAS_OTHER_XML, NULL)) QC_HAS_OTHER_XML, MAX(HAS_BINDS_XML) QC_HAS_BINDS_XML, MAX(PL_OID) MAX_PL_OID, MAX(PL_PROGID) MAX_PL_PROGID, MAX(PL_ENTRY_OID) MAX_PL_ENTRY_OID, MAX(PL_ENTRY_PROGID) MAX_PL_ENTRY_PROGID, MAX(SQL_ID) MAX_SQL_ID, MAX(SQL_EXEC_START) MAX_SQL_EXEC_START, MAX(SQL_EXEC_ID) MAX_SQL_EXEC_ID, MAX(LAST_REFRESH_TIME) MAX_LAST_REFRESH_TIME, MAX(STATUS) MAX_STATUS, SUM(REFRESH_COUNT) SUM_REFRESH_COUNT, MIN(FIRST_REFRESH_TIME) MIN_FIRST_REFRESH_TIME, MAX(DBOP_NAME) MAX_DBOP_NAME, MAX(DBOP_EXEC_ID) MAX_DBOP_EXEC_ID, CASE WHEN :B13 = 0 THEN NULL ELSE MAX(SQL_TEXT) END SQLMON_TEXT, MAX(IS_FULL_SQLTEXT) IS_FULL_TEXT, SUM(ELAPSED_TIME) SUM_ELAPSED_TIME, MAX(ELAPSED_TIME) MAX_ELAPSED_TIME, MAX(QUEUING_TIME) MAX_QUEUING_TIME, SUM(CPU_TIME) SUM_CPU_TIME, SUM(USER_IO_WAIT_TIME) SUM_USER_IO_WAIT_TIME, SUM(APPLICATION_WAIT_TIME) SUM_APPLICATION_WAIT_TIME, SUM(CONCURRENCY_WAIT_TIME) SUM_CONCURRENCY_WAIT_TIME, SUM(CLUSTER_WAIT_TIME) SUM_CLUSTER_WAIT_TIME, SUM(PLSQL_EXEC_TIME) SUM_PLSQL_EXEC_TIME, SUM(JAVA_EXEC_TIME) SUM_JAVA_EXEC_TIME, SUM(OTHER_WAIT_TIME) SUM_OTHER_WAIT_TIME, SUM(FETCHES) SUM_FETCHES, SUM(BUFFER_GETS) SUM_BUFFER_GETS, SUM(READ_REQS) SUM_READ_REQS, SUM(READ_BYTES) SUM_READ_BYTES, SUM(WRITE_REQS) SUM_WRITE_REQS, SUM(WRITE_BYTES) SUM_WRITE_BYTES, NVL(SUM(READ_BYTES), 0) + NVL(SUM(WRITE_BYTES), 0) SUM_IO_BYTES, SUM(IO_INTER_BYTES) SUM_IO_INTER_BYTES, SUM(IO_UNC_BYTES) SUM_IO_UNC_BYTES, SUM(IO_ELIG_BYTES) SUM_IO_ELIG_BYTES, SUM(IO_RET_BYTES) SUM_IO_RET_BYTES, MAX(RM_INFO) AGG_RM_INFO, MAX(CON _NAME) MAX_CON_NAME, MAX(CON_ID) MAX_CON_ID FROM MONITOR_DATA) MD, (SELECT HOST_NAME FROM V$INSTANCE) INST, (SELECT DB_UNIQUE_NAME, PLATFORM_NAME FROM V$DATABASE) DB), ASH_DATA AS (SELECT AD0.INST_ID, AD0.SESSION_ID, AD0.PLAN_LINE_ID, AD0.PLSQL_OBJECT_ID, AD0.PLSQL_SUBPROGRAM_ID, AD0.ACTIVITY_BUCKET_NUM, AD0.PLAN_ACTIVITY_BUCKET_NUM, AD0.SQL, AD0.TOP_LEVEL_SQL_ID, AD0.DBOP_NAME, AD0.IS_MONITORED_SQL, AD0.IS_PX_SLAVE, AD0.BUCKET_ACTIVITY_START, AD0.ACTIVITY_START, AD0.BUCKET_ACTIVITY_END, AD0.ACTIVITY_END, AD0.ACTIVITY_COUNT, AD0.ACTIVITY_TYPE, AD0.OTHER_SQL_ACTIVITY_TYPE, AD0.EVENT_NAME, AD0.IMQ_COUNT, AD0.WAIT_COUNT, AD0.CPU_COUNT, AD0.OTHER_SQL_COUNT, AD0.PX_SERVER_SET, AD0.PX_DFO_ACTIVITY_COUNT, AD0.DFO_MOST_ACTIVE_IID, AD0.DFO_MOST_ACTIVE_SID, (CASE WHEN AD0.DFO_MOST_ACTIVE_IID = AD0.INST_ID AND AD0.DFO_MOST_ACTIVE_SID = AD0.SESSION_ID AND (((AD0.PX_DFO_ACTIVITY_COUNT / AD0.DFO_MOST_ACTIVE_COUNT) >= AD0.PX_DOP * 1.05) OR ((AD0.PX_DFO_ACTIVITY_COU NT / AD0.DFO_MOST_ACTIVE_COUNT) <= AD0.PX_DOP *0.95)) AND (AD0.DFO_DURATION * 100) > :B32 THEN AD0.ACTIVITY_COUNT ELSE NULL END) DFO_MOST_ACTIVE_COUNT, AD0.BUCKET_DISTINCT_SAMPLES, AD0.SQL_BUCKET_DISTINCT_SAMPLES, CASE WHEN AD0.PX_SERVER_GROUP IS NULL AND AD0.IS_PX_SLAVE = 1 THEN 1 ELSE AD0.PX_SERVER_GROUP END PX_SERVER_GROUP, AD0.PX_STEP_ID, AD0.PX_DFO_PAIR_TYPE, AD0.PX_DFO_PAIR_ID, AD0.PX_STEP_ARG, AD0.PX_DOP, CASE WHEN AD0.PX_DOP IS NOT NULL AND AD0.PX_DOP <> AD0.PX_MIN_DOP AND AD0.PX_MIN_DOP != 0 THEN PX_MIN_DOP ELSE NULL END PX_MIN_DOP FROM (SELECT /*+ use_hash(ash) leading(mo) */ ASH.INST_ID, ASH.IS_PX_SLAVE, ASH.SESSION_ID, ASH.PLAN_LINE_ID, ASH.PLSQL_OBJECT_ID, ASH.PLSQL_SUBPROGRAM_ID, ASH.ACTIVITY_BUCKET_NUM, ASH.PLAN_ACTIVITY_BUCKET_NUM, ASH.SQL, ASH.TOP_LEVEL_SQL_ID, ASH.DBOP_NAME, ASH.IS_MONITORED_SQL, ASH.BUCKET_ACTIVITY_START, ASH.ACTIVITY_START, ASH.BUCKET_ACTIVITY_END, ASH.ACTIVITY_END, ASH.ACTIVITY_COUNT, ASH.ACTIVITY_TYPE, ASH.OT HER_SQL_ACTIVITY_TYPE, ASH.EVENT_NAME, ASH.IMQ_COUNT, ASH.WAIT_COUNT, ASH.CPU_COUNT, ASH.OTHER_SQL_COUNT, MO.PX_SERVER_SET, ASH.PX_DFO_ACTIVITY_COUNT, TRUNC(ASH.MOST_ACTIVE_IN_DFO / 10000000000) DFO_MOST_ACTIVE_COUNT, MOD(TRUNC(ASH.MOST_ACTIVE_IN_DFO / 1000000), 10000) DFO_MOST_ACTIVE_IID, MOD(ASH.MOST_ACTIVE_IN_DFO, 1000000) DFO_MOST_ACTIVE_SID, ASH.DFO_DURATION, ASH.BUCKET_DISTINCT_SAMPLES, ASH.SQL_BUCKET_DISTINCT_SAMPLES, MO.PX_SERVER_GROUP, ASH.PX_STEP_ID, ASH.PX_DFO_PAIR_TYPE, ASH.PX_DFO_PAIR_ID, ASH.PX_STEP_ARG, ASH.PX_DOP, ASH.PX_MIN_DOP FROM (SELECT /*+ no_merge */ MD.INST_ID, MD.SID, MD.SESSION_SERIAL#, MD.PX_SERVER_SET, MD.PX_SERVER_GROUP FROM MONITOR_DATA MD WHERE MD.SID IS NOT NULL OR :B31 = 1) MO, (SELECT /*+ no_merge */ GVTF.INST_ID, GVTF.GLOBAL_SAMPLE_ID, GVTF.IS_PX_SLAVE, GVTF.SESSION_ID, GVTF.PLAN_LINE_ID, GVTF.PLSQL_OBJECT_ID, GVTF.PLSQL_SUBPROGRAM_ID, GVTF.ACTIVITY_BUCKET_NUM, GVTF.PLAN_ACTIVITY_BUCKET_NUM, GVTF.SQL, GVTF.TOP_LEVE L_SQL_ID, GVTF.DBOP_NAME, GVTF.IS_MONITORED_SQL, GVTF.BUCKET_ACTIVITY_START, GVTF.ACTIVITY_START, GVTF.BUCKET_ACTIVITY_END, GVTF.ACTIVITY_END, GVTF.ACTIVITY_COUNT, GVTF.ACTIVITY_TYPE, GVTF.OTHER_SQL_ACTIVITY_TYPE, GVTF.EVENT_NAME, GVTF.IMQ_COUNT, GVTF.WAIT_COUNT, GVTF.CPU_COUNT, GVTF.OTHER_SQL_COUNT, MAX(GVTF.PER_SERVER_DFO_COUNT * 10000000000 + GVTF.INST_ID * 1000000 + GVTF.SESSION_ID) OVER(PARTITION BY GVTF.PX_DFO_PAIR_TYPE, GVTF.PX_DFO_PAIR_ID) MOST_ACTIVE_IN_DFO, SUM(GVTF.ACTIVITY_COUNT) OVER(PARTITION BY GVTF.PX_DFO_PAIR_TYPE, GVTF.PX_DFO_PAIR_ID) PX_DFO_ACTIVITY_COUNT, GVTF.DFO_DURATION, GVTF.PX_STEP_ID, GVTF.PX_DFO_PAIR_TYPE, GVTF.PX_DFO_PAIR_ID, GVTF.PX_STEP_ARG, GVTF.PX_DOP, GVTF.PX_MIN_DOP, COUNT(DISTINCT GVTF.GLOBAL_SAMPLE_ID) OVER(PARTITION BY GVTF.ACTIVITY_BUCKET_NUM) BUCKET_DISTINCT_SAMPLES, COUNT(DISTINCT GVTF.GLOBAL_SAMPLE_ID) OVER(PARTITION BY GVTF.SQL, GVTF.ACTIVITY_BUCKET_NUM) SQL_BUCKET_DISTINCT_SAMPLES FROM TABLE(GV$(CURSOR( (SELECT USER ENV('INSTANCE') INST_ID, ASH2.GLOBAL_SAMPLE_ID, CASE WHEN :B30 = 1 AND :B27 > 1 THEN BUCKET_NUM ELSE NULL END ACTIVITY_BUCKET_NUM, CASE WHEN :B29 = 1 AND :B27 > 1 THEN BUCKET_NUM ELSE NULL END PLAN_ACTIVITY_BUCKET_NUM, ASH2.SQL, ASH2.TOP_LEVEL_SQL_ID, ASH2.DBOP_NAME, ASH2.IS_MONITORED_SQL, ASH2.PLAN_LINE_ID, ASH2.PLSQL_OBJECT_ID, ASH2.PLSQL_SUBPROGRAM_ID, ASH2.ACTIVITY_TYPE, ASH2.OTHER_SQL_ACTIVITY_TYPE, ASH2.EVENT_NAME, ASH2.IS_PX_SLAVE, ASH2.SESSION_ID, ASH2.PX_STEP_ID, ASH2.PX_DFO_PAIR_TYPE, ASH2.PX_DFO_PAIR_ID, ASH2.PX_STEP_ARG, CASE WHEN ASH2.PX_DFO_PAIR_ID IS NOT NULL THEN DECODE(ASH2.PX_DOP, 0, :B28 , ASH2.PX_DOP) ELSE NULL END PX_DOP, ASH2.PX_MIN_DOP, :B20 + NUMTODSINTERVAL(:B26 * (ASH2.BUCKET_NUM-1), 'SECOND') BUCKET_ACTIVITY_START, :B20 + NUMTODSINTERVAL( :B26 * ASH2.BUCKET_NUM - 1, 'SECOND') BUCKET_ACTIVITY_END, ASH2.ACTIVITY_START, ASH2.ACTIVITY_END, ASH2.ACTIVITY_COUNT, ASH2.IMQ_COUNT, ASH2.WAIT_COUNT, ASH2.CPU_COUNT, ASH2.OTHER_SQL _COUNT, SUM(ASH2.ACTIVITY_COUNT) OVER(PARTITION BY ASH2.PX_DFO_PAIR_ID, ASH2.PX_DFO_PAIR_TYPE, DECODE(ASH2.PX_DFO_PAIR_ID, NULL, NULL, ASH2.SESSION_ID)) PER_SERVER_DFO_COUNT, CEIL((MAX(ASH2.MAX_SAMPLE_DATE) OVER(PARTITION BY ASH2.PX_DFO_PAIR_ID, ASH2.PX_DFO_PAIR_TYPE) - MIN(ASH2.MIN_SAMPLE_DATE) OVER(PARTITION BY ASH2.PX_DFO_PAIR_ID, ASH2.PX_DFO_PAIR_TYPE)) * 3600 * 24) DFO_DURATION FROM (SELECT ASH1.BUCKET_NUM, ASH1.GLOBAL_SAMPLE_ID, ASH1.PLAN_LINE_ID, ASH1.PLSQL_OBJECT_ID, ASH1.PLSQL_SUBPROGRAM_ID, ASH1.ACTIVITY_TYPE, ASH1.OTHER_SQL_ACTIVITY_TYPE, ASH1.EVENT_NAME, ASH1.SESSION_ID, ASH1.PX_STEP_ID, ASH1.PX_STEP_ARG, MAX(ASH1.SQL) SQL, MAX(ASH1.IS_MONITORED_SQL) IS_MONITORED_SQL, MAX(ASH1.PX_DFO_PAIR_TYPE) PX_DFO_PAIR_TYPE, MAX(ASH1.PX_DFO_PAIR_ID) PX_DFO_PAIR_ID, MIN(SAMPLE_DATE) MIN_SAMPLE_DATE, MAX(SAMPLE_DATE) MAX_SAMPLE_DATE, MAX(ASH1.IS_PX_SLAVE) IS_PX_SLAVE, MAX(ASH1.PX_DOP) PX_DOP, MIN(ASH1.PX_DOP) PX_MIN_DOP, MIN(ASH1.SAMPLE_DATE) ACTIVITY_START, MAX(ASH1.SAMPLE_DATE) ACTIVITY_END, COUNT(ASH1.SQL) ACTIVITY_COUNT, COUNT(CASE WHEN ASH1.ACTIVITY_TYPE = 'Cpu' AND ASH1.EVENT_NAME = 'in memory' THEN 1 ELSE NULL END) IMQ_COUNT, COUNT(CASE WHEN ASH1.ACTIVITY_TYPE != 'Other SQL Execution' AND ASH1.ACTIVITY_TYPE != 'Non SQL' AND ASH1.ACTIVITY_TYPE != 'Cpu' THEN 1 ELSE NULL END) WAIT_COUNT, COUNT(CASE WHEN ASH1.ACTIVITY_TYPE = 'Cpu' AND ASH1.EVENT_NAME IS NULL THEN 1 ELSE NULL END) CPU_COUNT, COUNT(CASE WHEN ASH1.ACTIVITY_TYPE = 'Other SQL Execution' AND ASH1.ACTIVITY_TYPE != 'Non SQL' THEN 1 ELSE NULL END) OTHER_SQL_COUNT, MAX(ASH1.TOP_LEVEL_SQL_ID) TOP_LEVEL_SQL_ID, MAX(ASH1.DBOP_NAME) DBOP_NAME FROM ( SELECT (CASE WHEN :B27 > 1 THEN (TRUNC(DELTA_TIME_SECONDS/ :B26 ) + 1) ELSE 1 END) BUCKET_NUM, ASH00.SQL, ASH00.SAMPLE_DATE, NVL2(DUP.C2, TRUNC(DELTA_TIME_SECONDS/ (:B25 )) + 1, NULL) GLOBAL_SAMPLE_ID, NVL2(DUP.C2, NULL, ASH00.IS_MONITORED_SQL) IS_MONITORED_SQL, NVL2(DUP.C2, NULL, ASH00.IN_INMEMORY_QUERY) IN_INMEMORY_ QUERY, NVL2(DUP.C2, NULL, ASH00.WAIT_CLASS) WAIT_CLASS, NVL2(DUP.C2, NULL, ASH00.ACTIVITY_TYPE) ACTIVITY_TYPE, NVL2(DUP.C2, NULL, ASH00.OTHER_SQL_ACTIVITY_TYPE) OTHER_SQL_ACTIVITY_TYPE, NVL2(DUP.C2, NULL, ASH00.EVENT_NAME) EVENT_NAME, NVL2(DUP.C2, NULL, ASH00.TOP_LEVEL_SQL_ID) TOP_LEVEL_SQL_ID, NVL2(DUP.C2, NULL, ASH00.DBOP_NAME) DBOP_NAME, NVL2(DUP.C2, NULL, ASH00.IS_PX_SLAVE) IS_PX_SLAVE, NVL2(DUP.C2, NULL, ASH00.SESSION_ID) SESSION_ID, NVL2(DUP.C2, NULL, ASH00.PLSQL_OBJECT_ID) PLSQL_OBJECT_ID, NVL2(DUP.C2, NULL, ASH00.PLSQL_SUBPROGRAM_ID) PLSQL_SUBPROGRAM_ID, NVL2(DUP.C2, NULL, ASH00.PLAN_LINE_ID) PLAN_LINE_ID, NVL2(DUP.C2, NULL, ASH00.PX_STEP_ID) PX_STEP_ID, NVL2(DUP.C2, NULL, ASH00.PX_STEP_ARG) PX_STEP_ARG, NVL2(DUP.C2, NULL, ASH00.PX_DFO_PAIR_ID) PX_DFO_PAIR_ID, NVL2(DUP.C2, NULL, ASH00.PX_DFO_PAIR_TYPE) PX_DFO_PAIR_TYPE, NVL2(DUP.C2, NULL, ASH00.PX_DOP) PX_DOP FROM (SELECT 1 C1, NULL C2 FROM V$TIMER UNION ALL SELECT 1 C1, 1 C2 FR OM V$TIMER) DUP, (SELECT /*+ no_merge */ 1 C1, ASH000.SAMPLE_DATE, ASH000.IS_MONITORED_SQL, ((EXTRACT(SECOND FROM(DELTA_TIME)) + EXTRACT(MINUTE FROM(DELTA_TIME)) * 60 + EXTRACT(HOUR FROM(DELTA_TIME)) * 3600 + EXTRACT(DAY FROM(DELTA_TIME)) * 86400)) DELTA_TIME_SECONDS, ASH000.IN_INMEMORY_QUERY, ASH000.WAIT_CLASS, DECODE(ASH000.IS_MONITORED_SQL, 1, NVL(ASH000.WAIT_CLASS, 'Cpu'), DECODE(SQL_ID, NULL, 'Non SQL', 'Other SQL Execution')) ACTIVITY_TYPE, NVL(ASH000.WAIT_CLASS, 'Cpu') OTHER_SQL_ACTIVITY_TYPE, DECODE(:B24 , 1, CASE WHEN ASH000.IS_MONITORED_SQL = 1 THEN NVL(ASH000.EVENT, DECODE(ASH000.IN_INMEMORY_QUERY, 'Y', 'in memory', ASH000.EVENT)) WHEN ASH000.SQL_ID IS NOT NULL THEN 'sql_id: ' || ASH000.SQL_ID WHEN ASH000.CALL_NAME IS NOT NULL THEN 'call: ' || ASH000.CALL_NAME ELSE 'anonymous: '|| ASH000.EVENT END, NULL) EVENT_NAME, CASE WHEN ASH000.IS_MONITORED_SQL = 1 AND (NVL(ASH000.SQL_ID, :B9 ) = :B9 OR NVL(ASH000.DBOP_NAME, :B6 ) = :B6 ) THEN 'this' WHEN ASH0 00.IS_PX_SLAVE = 1 AND ASH000.SQL_ID IS NOT NULL AND ASH000.TOP_LEVEL_SQL_ID != ASH000.SQL_ID THEN ASH000.TOP_LEVEL_SQL_ID WHEN ASH000.SQL_ID IS NOT NULL THEN ASH000.SQL_ID ELSE NVL(CALL_NAME, 'anonymous') END SQL, CASE WHEN ASH000.IS_PX_SLAVE = 0 AND (ASH000.SQL_ID IS NULL OR ASH000.TOP_LEVEL_SQL_ID != ASH000.SQL_ID) THEN ASH000.TOP_LEVEL_SQL_ID END TOP_LEVEL_SQL_ID, ASH000.DBOP_NAME, ASH000.IS_PX_SLAVE, CASE WHEN ASH000.IS_PX_SLAVE = 1 AND ASH000.IS_MONITORED_SQL != 1 THEN 65536 ELSE ASH000.SESSION_ID END SESSION_ID, DECODE(ASH000.IS_MONITORED_SQL, 1, ASH000.PLSQL_OBJECT_ID, NULL) PLSQL_OBJECT_ID, DECODE(ASH000.IS_MONITORED_SQL, 1, ASH000.PLSQL_SUBPROGRAM_ID, NULL) PLSQL_SUBPROGRAM_ID, DECODE(ASH000.IS_MONITORED_SQL, 1, ASH000.SQL_PLAN_LINE_ID, NULL) PLAN_LINE_ID, DECODE(ASH000.IS_MONITORED_SQL, 1, ASH000.PX_STEP_ID, NULL) PX_STEP_ID, CASE WHEN ASH000.IS_PX_SLAVE = 1 AND ASH000.IS_MONITORED_SQL = 1 AND ASH000.PX_STEP_ID IN (1, 2, 3) THEN ASH000.PX_STEP_ARG ELS E NULL END PX_DFO_PAIR_ID, CASE WHEN ASH000.IS_PX_SLAVE = 0 OR ASH000.IS_MONITORED_SQL != 1 THEN NULL WHEN ASH000.PX_STEP_ID = 1 THEN 1 WHEN ASH000.PX_STEP_ID IN (2, 3) THEN 0 ELSE NULL END PX_DFO_PAIR_TYPE, DECODE(ASH000.IS_MONITORED_SQL, 1, ASH000.PX_STEP_ARG, NULL) PX_STEP_ARG, DECODE(ASH000.IS_MONITORED_SQL, 1, ASH000.PX_DOP, NULL) PX_DOP FROM (SELECT ASH0.*, CASE WHEN ASH0.IS_TARGET_SQL = 1 OR (ASH0.IS_PX_SLAVE = 1 AND ((NVL(ASH0.TOP_LEVEL_SQL_ID, ASH0.SQL_ID) IS NOT NULL AND NVL(ASH0.TOP_LEVEL_SQL_ID, ASH0.SQL_ID) = :B9 ) OR (SQL_ID IS NULL AND :B23 = 'Y'))) THEN 1 ELSE 0 END IS_MONITORED_SQL FROM (SELECT (CASE WHEN (ASH.SQL_ID = :B9 AND ASH.SQL_EXEC_ID = :B7 AND ASH.SQL_EXEC_START = :B8 ) THEN 1 ELSE 0 END) IS_TARGET_SQL, ASH.SQL_ID, ASH.SQL_PLAN_LINE_ID, ASH.PLSQL_OBJECT_ID, ASH.PLSQL_SUBPROGRAM_ID, ASH.TOP_LEVEL_SQL_ID, DECODE(ASH.SQL_ID, NULL, ASH.TOP_LEVEL_CALL_NAME, NULL) CALL_NAME, ASH.EVENT, ASH.IN_INMEMORY_QUERY, ASH.WAIT_CLASS, ASH.SQL_EXEC_ID, ASH.SQL_EXEC_START, ASH.DBOP_NAME, ASH.DBOP_EXEC_ID, ASH.SESSION_ID, ASH.SESSION_SERIAL#, CASE WHEN QC_INSTANCE_ID IS NOT NULL AND (ASH.SESSION_ID != ASH.QC_SESSION_ID OR ASH.SESSION_SERIAL# != ASH.QC_SESSION_SERIAL# OR USERENV('instance') != ASH.QC_INSTANCE_ID) THEN 1 ELSE 0 END IS_PX_SLAVE, SAMPLE_TIME - CAST(:B20 AS TIMESTAMP) DELTA_TIME, CAST(FROM_TZ(ASH.SAMPLE_TIME, DBTIMEZONE) AS DATE) SAMPLE_DATE, TRUNC(MOD(PX_FLAGS/65536, 32)) PX_STEP_ID, MOD(PX_FLAGS, 65536) PX_STEP_ARG, TRUNC(PX_FLAGS/2097152) PX_DOP FROM V$ACTIVE_SESSION_HISTORY ASH WHERE ((ASH.SESSION_ID = :B19 AND ASH.SESSION_SERIAL# = :B18 AND USERENV('INSTANCE') = :B17 ) OR (ASH.QC_SESSION_ID IS NOT NULL AND ASH.QC_SESSION_ID = :B19 AND ASH.QC_SESSION_SERIAL# = :B18 AND ASH.QC_INSTANCE_ID = :B17 )) AND SAMPLE_TIME BETWEEN :B16 AND :B15 ) ASH0 WHERE (ASH0.SAMPLE_DATE BETWEEN :B20 + 1/24/3600 AND :B22 - 1/24/3600 OR (ASH0.SQL_ID = :B9 AND ASH0.SQL_EXEC_START = :B8 AND ASH0.SQL_EXEC_ID = :B7 ) OR (ASH0.DBOP_N AME = :B6 AND ASH0.DBOP_EXEC_ID = :B5 )) AND (:B21 IS NULL OR ASH0.SQL_PLAN_LINE_ID = :B21 ) AND (ASH0.IS_PX_SLAVE = 0 OR ASH0.SQL_ID IS NOT NULL)) ASH000 ) ASH00 WHERE ASH00.C1 = DUP.C1) ASH1 WHERE ASH1.BUCKET_NUM > 0 AND ASH1.BUCKET_NUM <= :B27 GROUP BY USERENV('INSTANCE'), ASH1.GLOBAL_SAMPLE_ID, ASH1.BUCKET_NUM, ASH1.SESSION_ID, ASH1.PLAN_LINE_ID, ASH1.PLSQL_OBJECT_ID, ASH1.PLSQL_SUBPROGRAM_ID, ASH1.ACTIVITY_TYPE, ASH1.EVENT_NAME, ASH1.OTHER_SQL_ACTIVITY_TYPE, ASH1.PX_STEP_ID, ASH1.PX_STEP_ARG) ASH2)))) GVTF WHERE GVTF.INST_ID BETWEEN :B11 AND :B10 ) ASH WHERE ASH.GLOBAL_SAMPLE_ID IS NULL AND ASH.SESSION_ID = MO.SID(+) AND ASH.INST_ID = MO.INST_ID(+)) AD0), RESPONSE_TIME_DATA AS (SELECT ADH.BUCKET_NUM, ADH.SQL_ROWNUM, ADH.SQL, ADH.TOP_LEVEL_SQL_ID, ADH.DBOP_NAME, ADH.PX_ID, DECODE( ADH.PX_STEP_ID, NULL, NULL, 0, NULL, 1, 'PX Server(s) - Executing Parent DFO', 2, 'PX Server(s) - Executing Child DFO', 3, 'PX Server(s) - Sampling Child DFO', 4, 'PX S erver(s) - Joining Group', 5, 'QC - Scheduling Child DFO', 6, 'QC - Scheduling Parent DFO', 7, 'QC - Initializing Objects', 8, 'QC - Flushing Objects', 9, 'QC - Allocating Slaves', 10, 'QC - Initializing Granules', 11, 'PX Server(s) - Parsing Cursor', 12, 'PX Server(s) - Executing Cursor', 13, 'PX Server(s) - Preparing Transaction', 14, 'PX Server(s) - Joining Transaction', 15, 'PX Server(s) - Load Commit', 16, 'PX Server(s) - Aborting Transaction', 17, 'QC - Executing Child DFO', 18, 'QC - Executing Parent DFO', 'PX Step - ' || PX_STEP_ID) PX_STEP_ID, ADH.PX_STEP_ARG, ADH.PX_DFO_PAIR_ID, ADH.PX_DOP, ADH.PX_MIN_DOP, ADH.DFO_MOST_ACTIVE_IID, ADH.DFO_MOST_ACTIVE_SID, ADH.DFO_MOST_ACTIVE_COUNT, ADH.ACTIVITY_START, ADH.ACTIVITY_END, ADH.ACTIVITY_TYPE, ADH.OTHER_SQL_ACTIVITY_TYPE, ADH.EVENT_NAME, ADH.PLAN_LINE_ID, ADH.PLSQL_OBJECT_ID, ADH.PLSQL_SUBPROGRAM_ID, CASE WHEN PLSQL_ROWNUM = 1 AND ADH.PLSQL_OBJECT_ID IS NOT NULL THEN NVL((SELECT P.OWNER || '.' || P.OBJECT_NAME || DECODE(P.PROCEDURE_NAME, NULL, '', '.' || P.PROCEDURE_NAME) FROM DBA_PROCEDURES P WHERE P.OBJECT_ID = ADH.PLSQL_OBJECT_ID AND P.SUBPROGRAM_ID = ADH.PLSQL_SUBPROGRAM_ID), 'Unavailable') ELSE NULL END PLSQL_NAME, ADH.ACTIVITY_COUNT, ADH.BUCKET_ACTIVE_SECONDS, ADH.BUCKET_IDLE_SECONDS, (CASE WHEN ADH.IS_MONITORED_SQL = 0 THEN ADH.ACTIVE_SECONDS WHEN ADH.PX_DFO_PAIR_ID IS NOT NULL AND ADH.DFO_PAIR_CPU_HEIGHT >= :B34 THEN DECODE(ADH.ACTIVITY_TYPE, 'Cpu', (ADH.DFO_PAIR_ACTIVITY_HEIGHT / ADH.DFO_PAIR_CPU_HEIGHT) * ADH.DFO_PAIR_TOTAL_SECONDS, 0) WHEN ADH.PX_DFO_PAIR_ID IS NOT NULL AND ADH.PX_DOP > :B34 THEN (ADH.DFO_PAIR_TOTAL_SECONDS * DECODE(ADH.ACTIVITY_TYPE, 'Cpu', ADH.DFO_PAIR_ACTIVITY_HEIGHT, (ADH.DFO_PAIR_ACTIVITY_HEIGHT * (:B34 - ADH.DFO_PAIR_CPU_HEIGHT)) / (ADH.DFO_PAIR_TOTAL_HEIGHT_ADJ - ADH.DFO_PAIR_CPU_HEIGHT))) / :B34 WHEN ADH.PX_DFO_PAIR_ID IS NOT NULL THEN (ADH.DFO_PAIR_TOTAL_SECONDS * ADH.DFO_PAIR_ACTIVITY_HEIGHT) / ADH.PX_DOP ELSE ADH.ACTIVE_SECONDS END) RESP_TIME, (CASE WHEN ADH.PX_DFO_PAIR_ID IS NOT NULL AND ADH.DFO_PAIR_IDLE_HEIGHT > 0 AND ADH.PX_DOP > :B34 THEN (ADH.DFO_PAIR_TOTAL_SECONDS * (ADH.DFO_PAIR_IDLE_HEIGHT * (:B34 - ADH.DFO_PAIR_CPU_HEIGHT)) / (ADH.DFO_PAIR_TOTAL_HEIGHT_ADJ - ADH.DFO_PAIR_CPU_HEIGHT)) / :B34 WHEN ADH.PX_DFO_PAIR_ID IS NOT NULL AND ADH.DFO_PAIR_IDLE_HEIGHT > 0 THEN (ADH.DFO_PAIR_TOTAL_SECONDS * ADH.DFO_PAIR_IDLE_HEIGHT) / ADH.PX_DOP ELSE 0 END) DFO_PAIR_IDLE_RESP_TIME, ADH.DFO_PAIR_ACTIVITY_HEIGHT, ADH.DFO_PAIR_CPU_HEIGHT, ADH.DFO_PAIR_IDLE_HEIGHT, ADH.DFO_PAIR_TOTAL_HEIGHT, ADH.DFO_PAIR_CPU_ACTIVITY, ADH.DFO_PAIR_TOTAL_SECONDS FROM (SELECT ADH_1.*, (ROW_NUMBER() OVER(PARTITION BY ADH_1.PLSQL_OBJECT_ID, ADH_1.PLSQL_SUBPROGRAM_ID ORDER BY ADH_1.BUCKET_NUM, ADH_1.ACTIVITY_TYPE, ADH_1.EVENT_NAME)) PLSQL_ROWNUM, (ROW_NUMBER() OVER(PARTITION BY ADH_1.SQL ORDER BY ADH_1.BUCKET_NUM, ADH_1.ACTIVITY_TYPE, ADH_1.EVENT_NAME)) SQL_ROWNUM, (CASE WHEN ADH_1.PX_DFO_PAIR_ID IS NOT NULL AND (ADH_1.DFO_PAIR_TOTAL_HEIGHT < ADH_1.PX_DOP) AND (ADH_1.DFO_PAIR_CPU_HEIGHT < :B34 ) THEN ADH_1.PX_DOP - ADH_1.DFO_PAIR_TOTAL_HEIGHT ELSE 0 END) DFO_PAIR_IDLE_HEIGHT, (CASE WHEN ADH_1.PX_DFO_PAIR_ID IS NOT NULL AND (ADH_1.DFO_PAIR_TOTAL_HEIGHT < ADH_1.PX_DOP) AND (ADH_1.DFO_PAIR_CPU_HEIGHT < :B34 ) THEN ADH_1.PX_DOP ELSE ADH_1.DFO_PAIR_TOTAL_HEIGHT END) DFO_PAIR_TOTAL_HEIGHT_ADJ FROM (SELECT ADH_0.*, (CASE WHEN ADH_0.DFO_PAIR_TOTAL_SECONDS > 0 THEN (ADH_0.DFO_PAIR_TOTAL_ACTIVITY * :B25 ) / ADH_0.DFO_PAIR_TOTAL_SECONDS ELSE 0 END) DFO_PAIR_TOTAL_HEIGHT, (CASE WHEN ADH_0.DFO_PAIR_TOTAL_SECONDS > 0 THEN (ADH_0.DFO_PAIR_CPU_ACTIVITY * :B25 ) / ADH_0.DFO_PAIR_TOTAL_SECONDS ELSE 0 END) DFO_PAIR_CPU_HEIGHT, (CASE WHEN ADH_0.PX_DFO_PAIR_ID IS NOT NULL AND ADH_0.DFO_PAIR_TOTAL_SECONDS > 0 THEN (ADH_0.ACTIVITY_COUNT * :B25 ) / ADH_0.DFO_PAIR_TOTAL_SECONDS ELSE 0 END) DFO_PAIR_ACTIVITY_HEIGHT FROM (SELECT AD3.*, (SUM(DECODE(AD3.PX_DFO_PAIR_ID, NULL, 0, AD3.ACTIVE_SECONDS)) OVER(PARTITION BY DECODE(AD3.PX_DFO_PAIR_ID, NULL, NULL, AD3.BUCKET_NUM), DECODE(AD3.PX_DFO_PAIR_ID, NULL, NULL, AD3.PX_STEP_ARG))) DFO_PAIR_TOTAL_SECONDS FROM (SELECT AD2.*, CASE WHEN AD2.IS_MONITORED_SQL = 0 THEN SQL_BUCKET_DISTINCT_SAMPLES * :B25 WHEN AD2.PX_ID IS NULL THEN AD2.ACTIVITY_COUNT * :B25 WHEN AD2.BUCKET_PARALLEL_MON_ACTIVITY > 0 THEN (AD2.ACTIVITY_COUNT * AD2.BUCKET_PARALLEL_MON_SECONDS) / AD2.BUCKET_PARALLEL_MON_ACTIVITY ELSE 0 END ACTIVE_SECONDS, CASE WHEN AD2.BUCKET_INTERVAL > BUCKET_ACTIVE_SECONDS THEN AD2.BUCKET_INTERVAL - BUCKET_ACTIVE_SECONDS ELSE 0 END BUCKET_IDLE_SECONDS FROM (SELECT AD1.*, (AD1.BUCKET_SERIAL_MON_ACTIVITY * :B25 ) BUCKET_SERIAL_MON_SECONDS, (AD1.BUCKET_TOTAL_MON_ACTIVITY - AD1.BUCKET_SERIAL_MON_ACTIVITY) BUCKET_PARALLEL_MON_ACTIVITY, (AD1.BUCKET_ACTIVE_SECONDS - (AD1.BUCKET_OTHER_ACTIVITY + AD1.BUCKET_SERIAL_MON_ACTIVITY) * :B25 ) BUCKET_PARALLEL_MON_SECONDS, (AD1.BUCKET_OTHER_ACTIVITY * :B25 ) BUCKET_OTH ER_SECONDS, DECODE(AD1.PX_DFO_PAIR_ID, NULL, NULL, SUM(AD1.ACTIVITY_COUNT) OVER(PARTITION BY DECODE(AD1.PX_DFO_PAIR_ID, NULL, NULL, AD1.BUCKET_NUM), AD1.PX_DFO_PAIR_ID)) DFO_PAIR_TOTAL_ACTIVITY, DECODE(AD1.PX_DFO_PAIR_ID, NULL, NULL, SUM(DECODE(AD1.ACTIVITY_TYPE, 'Cpu', AD1.ACTIVITY_COUNT, 0)) OVER(PARTITION BY DECODE(AD1.PX_DFO_PAIR_ID, NULL, NULL, AD1.BUCKET_NUM), AD1.PX_DFO_PAIR_ID)) DFO_PAIR_CPU_ACTIVITY FROM (SELECT AD01.*, NVL((SUM(DECODE(AD01.IS_MONITORED_SQL, 1, AD01.ACTIVITY_COUNT, NULL)) OVER(PARTITION BY AD01.BUCKET_NUM)), 0) BUCKET_TOTAL_MON_ACTIVITY, (NVL(SUM(CASE WHEN AD01.IS_MONITORED_SQL = 1 AND AD01.PX_ID IS NULL THEN AD01.ACTIVITY_COUNT ELSE NULL END) OVER(PARTITION BY AD01.BUCKET_NUM), 0)) BUCKET_SERIAL_MON_ACTIVITY, (NVL((SUM(DECODE(AD01.IS_MONITORED_SQL, 0, AD01.SQL_BUCKET_DISTINCT_SAMPLES, NULL)) OVER(PARTITION BY AD01.BUCKET_NUM)), 0)) BUCKET_OTHER_ACTIVITY, (NVL(AD01.BUCKET_DISTINCT_SAMPLES, 0) * :B25 ) BUCKET_ACTIVE_SECONDS, DE CODE(AD01.BUCKET_NUM, :B27 , MOD(:B32 , :B26 ), :B26 ) BUCKET_INTERVAL FROM (SELECT AD0.ACTIVITY_BUCKET_NUM BUCKET_NUM, AD0.PX_ID, AD0.ACTIVITY_TYPE, AD0.OTHER_SQL_ACTIVITY_TYPE, AD0.EVENT_NAME, AD0.PLAN_LINE_ID, AD0.PX_STEP_ID, AD0.PX_STEP_ARG, AD0.PLSQL_OBJECT_ID, AD0.PLSQL_SUBPROGRAM_ID, SUM(AD0.ACTIVITY_COUNT) ACTIVITY_COUNT, MIN(AD0.ACTIVITY_START) ACTIVITY_START, MAX(AD0.ACTIVITY_END) ACTIVITY_END, MAX(AD0.IS_MONITORED_SQL) IS_MONITORED_SQL, MAX(AD0.SQL) SQL, MAX(AD0.TOP_LEVEL_SQL_ID) TOP_LEVEL_SQL_ID, MAX(AD0.DBOP_NAME) DBOP_NAME, MAX(DECODE(AD0.DFO_MOST_ACTIVE_COUNT, NULL, NULL, AD0.DFO_MOST_ACTIVE_IID)) DFO_MOST_ACTIVE_IID, MAX(DECODE(AD0.DFO_MOST_ACTIVE_COUNT, NULL, NULL, AD0.DFO_MOST_ACTIVE_SID)) DFO_MOST_ACTIVE_SID, SUM(AD0.DFO_MOST_ACTIVE_COUNT) DFO_MOST_ACTIVE_COUNT, MAX(PX_DFO_PAIR_TYPE) PX_DFO_PAIR_TYPE, MAX(PX_DFO_PAIR_ID) PX_DFO_PAIR_ID, MAX(AD0.PX_DOP) PX_DOP, MIN(AD0.PX_MIN_DOP) PX_MIN_DOP, MAX(AD0.BUCKET_DISTINCT_SAMPLES) BUCKET_DI STINCT_SAMPLES, MAX(AD0.SQL_BUCKET_DISTINCT_SAMPLES) SQL_BUCKET_DISTINCT_SAMPLES FROM (SELECT AD00.*, (CASE WHEN AD00.IS_MONITORED_SQL = 1 AND (AD00.SESSION_ID != :B19 OR AD00.INST_ID != :B17 ) THEN AD00.PX_SERVER_GROUP END) PX_ID FROM ASH_DATA AD00 WHERE :B33 = 1 ) AD0 GROUP BY AD0.ACTIVITY_BUCKET_NUM, AD0.PX_ID, AD0.ACTIVITY_TYPE, AD0.EVENT_NAME, AD0.OTHER_SQL_ACTIVITY_TYPE, AD0.PLAN_LINE_ID, AD0.PLSQL_OBJECT_ID, AD0.PLSQL_SUBPROGRAM_ID, AD0.PX_STEP_ID, AD0.PX_STEP_ARG ) AD01) AD1) AD2) AD3) ADH_0) ADH_1) ADH) SELECT /*+ no_monitor no_xml_query_rewrite */ XMLELEMENT( "sql_monitor_report", XMLATTRIBUTES(:B59 AS "version", TO_CHAR(:B58 , :B12 ) AS "sysdate"), XMLELEMENT( "report_parameters", NULL, CASE WHEN :B57 IS NOT NULL THEN XMLFOREST( :B57 AS "dbop_name", :B56 AS "dbop_exec_id") ELSE XMLFOREST( :B55 AS "sql_id", :B54 AS "sql_exec_id") END, XMLFOREST( :B53 AS "session_id", :B52 AS "session_serial", TO_CHAR(:B51 , :B12 ) AS "sql_exec_start", :B27 AS "bucket_count", TO_CHAR(:B50 , :B12 ) AS "interval_start", TO_CHAR(:B22 , :B12 ) AS "interval_end", DECODE(:B49 , 'Y', :B48 , NULL) AS "auto_refresh", :B47 AS "base_path"), CASE WHEN :B23 = 'Y' AND :B46 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), CASE WHEN :B14 = 1 THEN XMLELEMENT( "derived_parameters_testing", NULL, XMLFOREST(:B11 AS "instance_id_low", :B10 AS "instance_id_high", :B26 AS "bucket_interval_sec", :B32 AS "interval_second", :B27 AS "bucket_calc_count", :B45 AS "bucket_calc_max_co unt", :B13 AS "sel_sqltext")) ELSE NULL END, (SELECT XMLCONCAT( CASE WHEN :B46 IS NULL AND :B60 IS NULL THEN XMLELEMENT( "target", XMLATTRIBUTES(:B17 AS "instance_id", :B19 AS "session_id", :B18 AS "session_serial", NVL2(:B6 , NULL, :B9 ) AS "sql_id", NVL2(:B6 , NULL, TO_CHAR(:B8 , :B12 )) AS "sql_exec_start", NVL2(:B6 , NULL, :B7 ) AS "sql_exec_id", NVL2(:B6 , :B6 , NULL) AS "dbop_name", NVL2(:B6 , :B5 , NULL) AS "dbop_exec_id", NVL2(:B6 , TO_CHAR(:B8 , :B12 ), NULL) AS "dbop_exec_start", NVL2(:B6 , NULL, MAX_PLAN_HASH_VALUE) AS "sql_plan_hash", NVL2(:B6 , NULL, MAX_FULL_PLAN_HASH_VALUE) AS "sql_full_plan_hash", MAGG.DB_UNIQUE_NAME AS "db_unique_name", MAGG.PLATFORM_NAME AS "db_platform_name", MAGG.HOST_NAME AS "report_host_name"), NVL2(SUM_SERVERS_REQUESTED, XMLELEMENT( "servers_re quested", NULL, SUM_SERVERS_REQUESTED), NULL), NVL2(SUM_SERVERS_ALLOCATED, XMLELEMENT( "servers_allocated", NULL, SUM_SERVERS_ALLOCATED), NULL), NVL2(MAX_USERID, XMLELEMENT( "user_id", NULL, MAX_USERID), NULL), NVL2(MAX_USERNAME, XMLELEMENT( "user", NULL, MAX_USERNAME), NULL), NVL2(MAX_CON_ID, XMLELEMENT( "con_id", NULL, MAX_CON_ID), NULL), NVL2(MAX_CON_NAME, XMLELEMENT( "con_name", NULL, MAX_CON_NAME), NULL), NVL2(MAX_PROGRAM, XMLELEMENT( "program", NULL, MAX_PROGRAM), NULL), NVL2(MAX_MODULE, XMLELEMENT( "module", NULL, MAX_MODULE), NULL), NVL2(MAX_ACTION, XMLELEMENT( "action", NULL, MAX_ACTION), NULL), NVL2(MAX_SERVICE_NAME, XMLELEMENT( "service", NULL, MAX_SERVICE_NAME), NULL), NVL2(MAX_CLIENT_ID, XMLELEMENT( "client_id", NULL, MAX_CLIENT_ID), NULL), NVL2(MAX_CLIENT_INFO, XMLELEMENT( "client_info", NULL, MAX_CLIEN T_INFO), NULL), NVL2(MAX_PL_ENTRY_OID, XMLELEMENT( "plsql_entry_object_id", NULL, MAX_PL_ENTRY_OID), NULL), NVL2(MAX_PL_ENTRY_PROGID, XMLELEMENT( "plsql_entry_subprogram_id", NULL, MAX_PL_ENTRY_PROGID), NULL), NVL2(MAX_PL_ENTRY_NAME, XMLELEMENT( "plsql_entry_name", NULL, MAX_PL_ENTRY_NAME), NULL), NVL2(MAX_PL_OID, XMLELEMENT( "plsql_object_id", NULL, MAX_PL_OID), NULL), NVL2(MAX_PL_PROGID, XMLELEMENT( "plsql_subprogram_id", NULL, MAX_PL_PROGID), NULL), NVL2(MAX_PL_NAME, XMLELEMENT( "plsql_name", NULL, MAX_PL_NAME), NULL), CASE WHEN (:B13 = 0 OR :B6 IS NOT NULL) THEN NULL ELSE XMLELEMENT( "sql_fulltext", XMLATTRIBUTES(NVL2(SQL_VTEXT, 'Y', IS_FULL_TEXT) AS "is_full"), NVL2(SQL_VTEXT, SQL_VTEXT, SQLMON_TEXT)) END, XMLELEMENT( "status", NULL, MAX_STATUS), XMLELEMENT( "refresh_count", NULL, SUM_REFRESH_COUNT), XMLELEMENT( "first_refresh_time ", NULL, TO_CHAR(MIN_FIRST_REFRESH_TIME, :B12 )), XMLELEMENT( "last_refresh_time", NULL, TO_CHAR(:B58 , :B12 )), XMLELEMENT( "duration", NULL, GREATEST(:B65 , LEAST(MAX_ELAPSED_TIME/1000000, 1), CEIL(MAX_QUEUING_TIME/1000000))), DECODE(MAX_RM_INFO, '00/00/0000 00:00:00', NULL, XMLTYPE(SUBSTR(MAX_RM_INFO, 20))), CASE WHEN (:B63 = 'Y') THEN XMLELEMENT( "adaptive_plan", XMLATTRIBUTES(:B64 AS "is_final"), :B63 ) ELSE NULL END, NVL((SELECT XMLFOREST( XMLAGG( XMLELEMENT( "param", XMLATTRIBUTES(E.NAME AS "name"), DECODE(:B14 , 1, 'XXXX', E.VALUE)) ORDER BY E.NAME) AS "optimizer_env") SQL_ENV FROM GV$SQL_OPTIMIZER_ENV E WHERE E.INST_ID = :B17 AND E.SQL_ID = :B9 AND E.CHILD_ADDRESS = HEXTORAW(:B66 ) AND (DECODE(:B14 , 1, 'YES', E.ISDEFAULT) = 'NO' OR E.ID IN (2, 12, 24, 35, 36, 37, 246, 256, 257, 274, 275, 289, 290))), (SELECT XMLELEMENT( "optimizer_env", XMLATTRIBUTE S('sys' AS "type"), XMLAGG( XMLELEMENT( "param", XMLATTRIBUTES(E.NAME AS "name"), DECODE(:B14 , 1, 'XXXX', E.VALUE)) ORDER BY E.NAME)) SYS_ENV FROM V$SYS_OPTIMIZER_ENV E WHERE (DECODE(:B14 , 1, 'YES', E.ISDEFAULT) = 'NO' OR E.ID IN (2, 12, 24, 35, 36, 37, 246, 256, 257, 274, 275, 289, 290)))) ) END, 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(MAX_QUEUING_TIME, 0), 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('queuing_time' AS "name"), MAX_QUEUING_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_READ_REQS, 0), 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('disk_reads' AS "name"), SUM_READ_REQS)), DECODE(NVL(SUM_WRITE_REQS, 0), 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('direct_writes' AS "name"), SUM_WRITE_REQS)), DECODE(NVL(SUM_READ_REQS, 0), 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('read_reqs' AS "name"), SUM_READ_REQS)), DECODE(NVL(SUM_READ_BYTES, 0), 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('read_bytes' AS "name"), SUM_READ_BYTES)), DECODE(NVL(SUM_WRITE_REQS, 0), 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('write_reqs' AS "name"), SUM_WRITE_REQS)), DECODE(NVL(SUM_WRITE_BYTES, 0), 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('write_bytes' AS "name"), SUM_WRITE_BYTES)), DECODE(NVL(SUM_IO_UNC_BYTES, 0), 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('unc_bytes' AS "name"), SUM_IO_UNC_BYTES)), DECODE(NVL(SUM_IO_ELIG_BYTES, 0), 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('elig_bytes' AS "name"), SUM_IO_ELIG_BYTES)), DECODE(NVL(SUM_IO_RET_BYTES, 0), 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('ret_bytes' AS "name"), SUM_IO_RET_BYTES)), CASE WHEN SUM_IO_INTER_BYTES IS NULL OR SUM_IO_BYTES = 0 OR SUM_IO_INTER_BYTES = SUM_IO_BYTES OR NVL(SUM_IO_ELIG_BYTES, 0) = 0 THEN NULL ELSE XMLELEMENT( "stat", XMLATTRIBUTES('cell_offload_efficiency' AS "name"), ROUND(SUM_IO_BYTES / DECODE(SUM_IO_INTER_BYTES, 0, 1, SUM_IO_INTER_BYTES), 2)) END, CASE WHEN NVL(SUM_IO_ELIG_BYTES, 0) = 0 OR SUM_IO_UNC_BYTES = SUM_IO_RET_BYTES THEN NULL ELSE XMLELEMENT( "stat", XMLATTRIBUTES('cell_offload_efficiency2' AS "name"), GREATEST(0, ROUND(100* (SUM_IO_UNC_BYTES - SUM_IO_RET_BYTES) / SUM_IO_ELIG_BYTES, 2))) END), CASE WHEN :B33 = 1 THEN (SELECT CASE WHEN SUM(ACTIVITY_COUNT) > 0 THEN XMLELEMENT( "activity_sampled", 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)) ELSE NULL END FROM (SELECT AD0.ACTIVITY_TYPE, AD0.EVENT_NAME, SUM(AD0.ACTIVITY_COUNT) ACTIVITY_COUNT FROM ASH_DATA AD0 GROUP BY AD0.ACTIVITY_TYPE, AD0.EVENT_NAME) AD1) ELSE NULL END, NVL2(MAX_ERROR_NUMBER, XMLELEMENT( "error", XMLATTRIBUTES(MAX_ERROR_NUMBER AS "number", MAX_ERROR_FACILITY AS "facility"), MAX_ERROR_MESSAGE), NULL), CASE WHEN :B62 = 1 AND MAGG.QC_HAS_BINDS_XML = 'Y' THEN (SEL ECT XMLTYPE(BINDS_XML) FROM GV$SQL_MONITOR MON WHERE MON.INST_ID = :B17 AND MON.KEY = MAGG.MAX_KEY AND MON.SID = MAGG.MAX_SESSION_ID AND MON.SQL_ID = :B9 AND MON.SQL_EXEC_START = :B8 AND MON.SQL_EXEC_ID = :B7 AND ROWNUM = 1) ELSE NULL END, CASE WHEN :B61 = 1 AND MAGG.QC_HAS_OTHER_XML = 'Y' THEN (SELECT XMLTYPE(OTHER_XML) FROM GV$SQL_MONITOR MON WHERE MON.INST_ID = MAGG.MAX_INST_ID AND MON.KEY = MAGG.MAX_KEY AND MON.SID = MAGG.MAX_SESSION_ID AND MON.SQL_ID = :B9 AND MON.SQL_EXEC_START = :B8 AND MON.SQL_EXEC_ID = :B7 AND ROWNUM = 1) ELSE NULL END) FROM (SELECT V.*, CASE WHEN :B13 = 2 AND IS_FULL_TEXT = 'N' AND :B46 IS NULL AND :B60 IS NULL THEN (SELECT SQL_FULLTEXT FROM GV$SQL SQ WHERE SQ.INST_ID BETWEEN :B11 AND :B10 AND SQ.SQL_ID = :B9 AND ROWNUM = 1) ELSE NULL END SQL_VTEXT FROM MONITOR_AGG V) MAGG), CASE WHEN :B44 = 1 THEN (SELECT CASE WHEN AT.ACTIVITY_COUNT > 0 THEN XMLELEMENT( "activity_sampled", XMLATTRIBUTES( :B41 AS "ash_missing_seconds", TO_CHAR(AT.A CTIVITY_START, :B12 ) AS "first_sample_time", TO_CHAR(AT.ACTIVITY_END, :B12 ) AS "last_sample_time", ROUND((AT.ACTIVITY_END - AT.ACTIVITY_START) * 3600 * 24) + 1 AS "duration", AT.ACTIVITY_COUNT AS "count", AT.IMQ_COUNT AS "imq_count", AT.WAIT_COUNT AS "wait_count", AT.CPU_COUNT AS "cpu_count", DECODE(AT.OTHER_SQL_COUNT, 0, NULL, AT.OTHER_SQL_COUNT) AS "other_sql_count", :B40 AS "cpu_cores", :B39 AS "hyperthread"), AT.ACTIVITY_TOTAL, AH.GLOB_ACTIVITY_HISTO) WHEN :B41 IS NOT NULL THEN XMLELEMENT( "activity_sampled", XMLATTRIBUTES( DECODE( :B41 , -1, 'all', TO_CHAR( :B41 )) AS "ash_missing_seconds")) 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.IMQ_COUNT) IMQ_COUNT, SUM(AD1.WAIT_COUNT) WAIT_COUNT, SUM(AD1.CPU_COUNT) CPU_COUNT, SUM(AD1.OTHER _SQL_COUNT) OTHER_SQL_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.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.IMQ_COUNT) IMQ_COUNT, SUM(AD0.WAIT_COUNT) WAIT_COUNT, SUM(AD0.CPU_COUNT) CPU_COUNT, SUM(AD0.OTHER_SQL_COUNT) OTHER_SQL_COUNT FROM ASH_DATA AD0 GROUP BY AD0.ACTIVITY_TYPE, AD0.EVENT_NAME) AD1) AT, (SELECT CASE WHEN :B30 = 1 AND :B27 > 1 THEN XMLELEMENT( "activity_histogram", XMLATTRIBUTES( :B26 AS "bucket_interval", :B27 AS "bucket_count", TO_CHAR( :B20 , :B12 ) AS "start_time", TO_CHAR( :B22 , :B12 ) AS "end_time", ROUND(( :B 22 - :B20 ) *3600*24) + 1 AS "duration"), XMLAGG( XMLELEMENT( "bucket", XMLATTRIBUTES( AD2.BUCKET_NUM AS "number"), ACTIVITY_BUCKET) ORDER BY AD2.BUCKET_NUM)) ELSE NULL END GLOB_ACTIVITY_HISTO FROM (SELECT AD1.BUCKET_NUM, SUM(ACTIVITY_COUNT) ACTIVITY_COUNT, SUM(IMQ_COUNT) IMQ_COUNT, SUM(WAIT_COUNT) WAIT_COUNT, SUM(CPU_COUNT) CPU_COUNT, SUM(OTHER_SQL_COUNT) OTHER_SQL_COUNT, MIN(AD1.ACTIVITY_START) ACTIVITY_START, MAX(AD1.ACTIVITY_END) ACTIVITY_END, MIN(AD1.BUCKET_ACTIVITY_START) BUCKET_ACTIVITY_START, MAX(AD1.BUCKET_ACTIVITY_END) BUCKET_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.ACTIVITY_BUCKET_NUM BUCKET_NUM, AD0.ACTIVITY_TYPE, AD0.EVENT_NAME, MIN(AD0.ACTIVI TY_START) ACTIVITY_START, MAX(AD0.ACTIVITY_END) ACTIVITY_END, SUM(AD0.ACTIVITY_COUNT) ACTIVITY_COUNT, SUM(AD0.IMQ_COUNT) IMQ_COUNT, SUM(AD0.WAIT_COUNT) WAIT_COUNT, SUM(AD0.CPU_COUNT) CPU_COUNT, SUM(AD0.OTHER_SQL_COUNT) OTHER_SQL_COUNT, MIN(AD0.BUCKET_ACTIVITY_START) BUCKET_ACTIVITY_START, MAX(AD0.BUCKET_ACTIVITY_END) BUCKET_ACTIVITY_END FROM ASH_DATA AD0 GROUP BY AD0.ACTIVITY_BUCKET_NUM, AD0.ACTIVITY_TYPE, AD0.EVENT_NAME) AD1 GROUP BY AD1.BUCKET_NUM) AD2) AH) ELSE NULL END, CASE WHEN :B33 = 1 THEN (SELECT CASE WHEN AH.ACTIVITY_COUNT > 0 THEN XMLELEMENT( "activity_detail", XMLATTRIBUTES( TO_CHAR( :B20 , :B12 ) AS "start_time", TO_CHAR( :B22 , :B12 ) AS "end_time", :B41 AS "ash_missing_seconds", TO_CHAR(AH.ACTIVITY_START, :B12 ) AS "first_sample_time", TO_CHAR(AH.ACTIVITY_END, :B12 ) AS "last_sample_time", ROUND((AH.ACTIVITY_END - AH.ACTIVITY_START) * 3600 * 24) + 1 AS "duration", :B25 AS "sample_interval", :B26 AS "bucket_interval", :B27 AS "bucket_count", ROUND((:B22 - :B20 ) *3600*24) + 1 AS "bucket_duration", :B40 AS "cpu_cores", :B34 AS "total_cpu_cores", :B39 AS "hyperthread"), AH.GLOB_ACTIVITY_HISTO) WHEN :B41 IS NOT NULL THEN XMLELEMENT( "activity_detail", XMLATTRIBUTES( DECODE( :B41 , -1, 'all', TO_CHAR( :B41 )) AS "ash_missing_seconds")) ELSE NULL END FROM (SELECT MIN(AD2.ACTIVITY_START) ACTIVITY_START, MAX(AD2.ACTIVITY_END) ACTIVITY_END, SUM(AD2.ACTIVITY_COUNT) ACTIVITY_COUNT, XMLAGG( XMLELEMENT( "bucket", XMLATTRIBUTES( AD2.BUCKET_NUM AS "number"), ACTIVITY_BUCKET_XML) ORDER BY AD2.BUCKET_NUM) GLOB_ACTIVITY_HISTO FROM (SELECT AD1.BUCKET_NUM, MIN(AD1.ACTIVITY_START) ACTIVITY_START, MAX(AD1.ACTIVITY_END) ACTIVITY_END, SUM(AD1.ACTIVITY_COUNT) ACTIVITY_COUNT, MAX(AD1.BUCKET_IDLE_SECONDS) BUCKET_IDLE_SECONDS, XMLAGG( XMLCONCAT( CASE W HEN AD1.DFO_PAIR_IDLE_RESP_TIME != 0 AND DFO_PAIR_ROWNUM = 1 THEN XMLELEMENT( "activity", XMLATTRIBUTES( 'Parallel Skew' AS "class", AD1.PX_STEP_ARG AS "line", AD1.PX_ID AS "px", ROUND(AD1.DFO_PAIR_IDLE_RESP_TIME, 2) AS "rt"), 0) ELSE NULL END, XMLELEMENT( "activity", XMLATTRIBUTES( NVL(AD1.OTHER_SQL, AD1.RPI) AS "sql", AD1.NON_SQL AS "non_sql", AD1.CLASS AS "class", AD1.OTHER_SQL_CLASS AS "other_sql_class", AD1.EVENT AS "event", AD1.PLAN_LINE_ID AS "line", NVL2(AD1.PLSQL_OBJECT_ID, AD1.PLSQL_OBJECT_ID||'.'|| AD1.PLSQL_SUBPROGRAM_ID, NULL) AS "plsql_id", AD1.PLSQL_NAME AS "plsql_name", CASE WHEN AD1.SQL_ROWNUM = 1 THEN AD1.TOP_LEVEL_SQL_ID END AS "top_sql_id", CASE WHEN AD1.DBOP_NAME IS NOT NULL THEN AD1.DBOP_NAME END AS "dbop_name", CASE WHEN AD1.DFO_MOST_ACTIVE_IID IS NOT NULL AND :B67 = 'Y' THEN AD1.DFO_M OST_ACTIVE_IID END AS "skew_iid", DECODE(AD1.DFO_MOST_ACTIVE_COUNT, NULL, NULL, AD1.DFO_MOST_ACTIVE_SID) AS "skew_sid", AD1.DFO_MOST_ACTIVE_COUNT AS "skew_count", DECODE(AD1.PX_DOP, :B28 , NULL, AD1.PX_DOP) AS "dop", DECODE(AD1.PX_DOP, AD1.PX_MIN_DOP, NULL, AD1.PX_MIN_DOP) AS "min_dop", AD1.PX_ID AS "px", AD1.PX_STEP_ID AS "step", AD1.PX_STEP_ARG AS "arg", DECODE(AD1.ACTIVITY_COUNT, AD1.RESP_TIME, NULL, ROUND(AD1.RESP_TIME, 2)) AS "rt"), AD1.ACTIVITY_COUNT)) ORDER BY AD1.PX_STEP_ID, AD1.PX_STEP_ARG, AD1.DFO_PAIR_ROWNUM) ACTIVITY_BUCKET_XML FROM (SELECT AD01.*, CASE WHEN AD01.ACTIVITY_TYPE != 'Other SQL Execution' AND AD01.ACTIVITY_TYPE != 'Non SQL' THEN AD01.ACTIVITY_TYPE END CLASS, CASE WHEN (AD01.ACTIVITY_TYPE = 'Other SQL Execution' OR AD01.ACTIVITY_TYPE = 'Non SQL') THEN AD01.OTHER_SQL_ACTIVITY_TYPE END OTHER_SQL_CLASS, CASE WHEN AD01.ACTIVITY_TYPE != 'Other SQL Execu tion' AND AD01.ACTIVITY_TYPE != 'Non SQL' THEN AD01.EVENT_NAME END EVENT, CASE WHEN AD01.SQL IN ('this', 'anonymous') THEN NULL ELSE AD01.SQL END RPI, DECODE(AD01.ACTIVITY_TYPE, 'Other SQL Execution', SUBSTR(AD01.EVENT_NAME, 9), NULL) OTHER_SQL, DECODE(AD01.ACTIVITY_TYPE, 'Non SQL', AD01.EVENT_NAME, NULL) NON_SQL, ROW_NUMBER() OVER(PARTITION BY AD01.BUCKET_NUM, AD01.PX_DFO_PAIR_ID ORDER BY AD01.ACTIVITY_TYPE, AD01.EVENT_NAME, AD01.PLAN_LINE_ID) DFO_PAIR_ROWNUM FROM RESPONSE_TIME_DATA AD01) AD1 GROUP BY AD1.BUCKET_NUM) AD2) AH) ELSE NULL END, CASE WHEN :B23 = 'Y' THEN (SELECT XMLELEMENT( "parallel_info", XMLATTRIBUTES( :B17 AS "qc_instance_id", MAX_PX_QCSID AS "qc_session_id", MAX_PX_IS_CROSS_INSTANCE AS "is_cross_instance", MAX_PX_DOP AS "dop", MAX_PX_DOP_INSTANCES AS "max_dop_instances", DIST_INST_COUNT AS "inst_count", DIST_PX_GROUP_COUNT AS "server_group_count", DIST_PX_SET_COUNT AS "server_set_count"), CASE WHEN :B69 = 1 THEN PX_SESSIONS ELSE NULL END, CASE WHEN :B67 = 'Y' THEN DECODE(:B68 , 1, PX_INSTANCES, NULL) ELSE NULL END) FROM (SELECT MAX_PX_QCSID, MAX_PX_DOP, MAX_PX_DOP_INSTANCES, MAX_PX_IS_CROSS_INSTANCE, SUM_SERVERS_REQUESTED, SUM_SERVERS_ALLOCATED, DIST_INST_COUNT, DIST_PX_GROUP_COUNT, DIST_PX_SET_COUNT, (SELECT XMLELEMENT( "sessions", XMLATTRIBUTES(MAX(PX_SESSION.ACTIVITY_COUNT) AS "max_activity_count", MAX(PX_SESSION.IMQ_COUNT) AS "max_imq_count", MAX(PX_SESSION.CPU_COUNT) AS "max_cpu_count", MAX(PX_SESSION.WAIT_COUNT) AS "max_wait_count", MAX(PX_SESSION.OTHER_SQL_COUNT) AS "max_other_sql_count", MAX(PX_SESSION.MAX_IO_REQS) AS "max_io_reqs", MAX(PX_SESSION.MAX_IO_BYTES) AS "max_io_bytes", 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_REQS) MAX_IO_REQS, MAX(PI.MAX_IO_BYTES) MAX_IO_BYTES, MAX(PI.MAX_BUFFER_GETS) MAX_BUFFER_GETS, SUM(PI.ACTIVITY_COUNT) ACTIVITY_COUNT, SUM(PI.IMQ_COUNT) IMQ_COUNT, SUM(PI.WAIT_COUNT) WAIT_COUNT, SUM(PI.CPU_COUNT) CPU_COUNT, SUM(PI.OTHER_SQL_COUNT) OTHER_SQL_COUNT, XMLELEMENT( "session", XMLATTRIBUTES( INST_ID AS "inst_id", PROCESS_NAME AS "process_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(EL APSED_TIME)), NULL), NVL2(MAX(QUEUING_TIME), XMLELEMENT( "stat", XMLATTRIBUTES('queuing_time' AS "name"), MAX(QUEUING_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_exe c_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(READ_REQS), XMLELEMENT( "stat", XMLATTRIBUTES('disk_reads' AS "name"), MAX(READ_REQS)), NULL), NVL2(MAX(WRITE_REQS), XMLELEMENT( "stat", XMLATTRIBUTES('direct_writes' AS "name"), MAX(WRITE_REQS)), NULL), NVL2(MAX(READ_REQS), XMLELEMENT( "stat", XMLATTRIBUTES('read_reqs' AS "name"), MAX(READ_REQS)), NULL), NVL2(MAX(READ_BYTES), XMLELEMENT( "stat", XMLATTRIBUTES('read_bytes' AS "name"), MAX(READ_BYTES)), NULL), NVL2(MAX(WRITE_REQS), XMLELEMENT( "stat", XMLATTRIBUTES('write_reqs' AS "name"), MAX(WRITE_REQS)), NULL), NVL2(MAX(WRITE_BYTES), XMLELEMENT( "stat", XMLATTRIBUTES('write_bytes' AS "name"), MAX(WRITE_BYTES)), NULL), NVL2(MAX(IO_UNC_BYTES), XMLELEMENT( "stat", XMLATTRIBUTES('unc_bytes' AS "name"), MAX(IO_UNC_BYTES)), NULL), NVL2(MAX(IO_ELIG_BYTES), XMLELEMENT( "stat", XMLATTRIBUTES('elig_bytes' AS "name"), MAX(IO_ELIG_BYTES)), NULL), NVL2(MAX(IO_RET_BYTES), XMLELEMENT( "stat", XMLATTRIBUTES('ret_bytes' AS "name"), MAX(IO_RET_BYTES)), NULL), CASE WHEN MAX(IO_INTER_BYTES) IS NULL OR NVL(MAX(IO_BYTES), 0) = 0 OR MAX(IO_INTER_BYTES) = MAX(IO_BYTES) OR NVL(MAX(IO_ELIG_BYTES), 0) = 0 THEN NULL ELSE XMLELEMENT( "stat", XM LATTRIBUTES('cell_offload_efficiency' AS "name"), ROUND(MAX(IO_BYTES) / DECODE(MAX(IO_INTER_BYTES), 0, 1, MAX(IO_INTER_BYTES)), 2)) END, CASE WHEN NVL(MAX(IO_ELIG_BYTES), 0) = 0 OR MAX(IO_UNC_BYTES) = MAX(IO_RET_BYTES) THEN NULL ELSE XMLELEMENT( "stat", XMLATTRIBUTES('cell_offload_efficiency2' AS "name"), GREATEST(0, ROUND(100* (MAX(IO_UNC_BYTES) - MAX(IO_RET_BYTES)) / MAX(IO_ELIG_BYTES), 2))) END), CASE WHEN SUM(PI.ACTIVITY_COUNT) > 0 AND :B33 = 1 THEN XMLELEMENT( "activity_sampled", XMLATTRIBUTES( TO_CHAR(MIN(PI.ACTIVITY_START), :B12 ) AS "first_sample_time", TO_CHAR(MAX(PI.ACTIVITY_END), :B12 ) AS "last_sample_time", ROUND((MAX(PI.ACTIVITY_END) - MIN(PI.ACTIVITY_START)) * 3600 * 24) + 1 AS "duration", SUM(PI.ACTIVITY_COUNT) AS "count", SUM(PI.IMQ_COUNT) AS "imq_count", SUM(PI.CPU_COUNT) AS "cpu_count", SUM(PI.WAIT_COUNT) AS "wait_count", SUM(PI.OTHER _SQL_COUNT)AS "other_sql_count", :B40 AS "cpu_cores", :B39 AS "hyperthread"), 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)) ELSE NULL END, CASE WHEN :B61 = 1 AND PI.HAS_OTHER_XML = 'Y' THEN (SELECT XMLTYPE(OTHER_XML) FROM GV$SQL_MONITOR MON WHERE MON.INST_ID = PI.INST_ID AND MON.KEY = PI.KEY AND MON.SID = PI.SID AND MON.SQL_ID = :B9 AND MON.SQL_EXEC_START = :B8 AND MON.SQL_EXEC_ID = :B7 AND ROWNUM = 1) ELSE NULL END) PX_SESSION_XML FROM (SELECT MO.HAS_OTHER_XML, MO.KEY, 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_REQS) MAX_IO_REQS, MAX(MO.IO_BYTES) MAX_IO_BYTES, MAX(MO.BUFFER_GETS) MAX_BU FFER_GETS, MAX(MO.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.IMQ_COUNT)) IMQ_COUNT, SUM(DECODE(ASH0.ACTIVITY_TYPE, NULL, NULL, ASH0.WAIT_COUNT)) WAIT_COUNT, SUM(DECODE(ASH0.ACTIVITY_TYPE, NULL, NULL, ASH0.CPU_COUNT)) CPU_COUNT, SUM(DECODE(ASH0.ACTIVITY_TYPE, NULL, NULL, ASH0.OTHER_SQL_COUNT)) OTHER_SQL_COUNT, MIN(ASH0.ACTIVITY_START) ACTIVITY_START, MAX(ASH0.ACTIVITY_END) ACTIVITY_END, MAX(DECODE(MO.ELAPSED_TIME, 0, NULL, MO.ELAPSED_TIME)) ELAPSED_TIME, MAX(DECODE(MO.QUEUING_TIME, 0, NULL, MO.QUEUING_TIME)) QUEUING_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.IO_INTER_BYTES, 0, NULL, MO.IO_INTER_BYTES)) IO_INTER_BYTES, MAX(DECODE(MO.READ_REQS, 0, NULL, MO.READ_REQS)) READ_REQS , MAX(DECODE(MO.READ_BYTES, 0, NULL, MO.READ_BYTES)) READ_BYTES, MAX(DECODE(MO.WRITE_REQS, 0, NULL, MO.WRITE_REQS)) WRITE_REQS, MAX(DECODE(MO.WRITE_BYTES, 0, NULL, MO.WRITE_BYTES)) WRITE_BYTES, MAX(DECODE(MO.IO_BYTES, 0, NULL, MO.IO_BYTES)) IO_BYTES, MAX(DECODE(MO.IO_UNC_BYTES, 0, NULL, MO.IO_UNC_BYTES)) IO_UNC_BYTES, MAX(DECODE(MO.IO_ELIG_BYTES, 0, NULL, MO.IO_ELIG_BYTES)) IO_ELIG_BYTES, MAX(DECODE(MO.IO_RET_BYTES, 0, NULL, MO.IO_RET_BYTES)) IO_RET_BYTES, 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_TIM E)) JAVA_EXEC_TIME, MAX(DECODE(MO.OTHER_WAIT_TIME, 0, NULL, MO.OTHER_WAIT_TIME)) OTHER_WAIT_TIME FROM MONITOR_DATA MO, (SELECT ASH1.INST_ID, ASH1.SESSION_ID, ASH1.ACTIVITY_TYPE, ASH1.EVENT_NAME, SUM(ASH1.ACTIVITY_COUNT) ACTIVITY_COUNT, SUM(ASH1.IMQ_COUNT) IMQ_COUNT, SUM(ASH1.WAIT_COUNT) WAIT_COUNT, SUM(ASH1.CPU_COUNT) CPU_COUNT, SUM(ASH1.OTHER_SQL_COUNT)OTHER_SQL_COUNT, MIN(ASH1.ACTIVITY_START) ACTIVITY_START, MAX(ASH1.ACTIVITY_END) ACTIVITY_END FROM ASH_DATA ASH1 GROUP BY ASH1.INST_ID, ASH1.SESSION_ID, ASH1.ACTIVITY_TYPE, ASH1.EVENT_NAME) ASH0 WHERE MO.INST_ID = ASH0.INST_ID(+) AND MO.SID = ASH0.SESSION_ID(+) AND (:B69 = 1 OR :B68 = 1) GROUP BY MO.INST_ID, MO.KEY, MO.HAS_OTHER_XML, 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 (:B69 = 1) GROUP BY PI.INST_ID, PI.KEY, PI.HAS_OTHER_XML, PI.SID, PI.PROCESS_NAME, PI.SESSION_SERIAL#, PI.PX_SERVER_GROUP, PI.PX_ SERVER_SET, PI.PX_SERVER#) PX_SESSION) PX_SESSIONS, (SELECT XMLELEMENT( "instances", XMLATTRIBUTES( MAX(PX_INSTANCE.ACTIVITY_COUNT) AS "max_activity_count", MAX(PX_INSTANCE.IMQ_COUNT) AS "max_imq_count", MAX(PX_INSTANCE.CPU_COUNT) AS "max_cpu_count", MAX(PX_INSTANCE.WAIT_COUNT) AS "max_wait_count", MAX(PX_INSTANCE.OTHER_SQL_COUNT) AS "max_other_sql_count", MAX(PX_INSTANCE.ELAPSED_TIME) AS "max_elapsed_time", MAX(PX_INSTANCE.BUFFER_GETS) AS "max_buffer_gets", MAX(PX_INSTANCE.IO_REQS) AS "max_io_reqs", MAX(PX_INSTANCE.IO_BYTES) AS "max_io_bytes"), XMLAGG(PX_INSTANCE.PX_INSTANCES_XML ORDER BY INST_ID)) FROM (SELECT PI.INST_ID, MAX(PI.ELAPSED_TIME) ELAPSED_TIME, MAX(PI.IO_REQS) IO_REQS, MAX(PI.IO_BYTES) IO_BYTES, MAX(PI.BUFFER_GETS) BUFFER_GETS, SUM(PI.ACTIVITY_COUNT) ACTIVITY_COUNT, SUM(PI.IMQ_COUNT) IMQ_COUNT, SUM(PI.WAIT_COUNT) WAIT_COUNT, SUM(PI.CPU_COUNT) CPU_COUNT, SUM(PI.OTHER_SQL_COUNT) OTHER_SQL_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(QUEUING_TIME), XMLELEMENT( "stat", XMLATTRIBUTES('queuing_time' AS "name"), MAX(QUEUING_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(READ_REQS), XMLELEMENT( "stat", XMLATTRI BUTES('disk_reads' AS "name"), MAX(READ_REQS)), NULL), NVL2(MAX(WRITE_REQS), XMLELEMENT( "stat", XMLATTRIBUTES('direct_writes' AS "name"), MAX(WRITE_REQS)), NULL), NVL2(MAX(READ_REQS), XMLELEMENT( "stat", XMLATTRIBUTES('read_reqs' AS "name"), MAX(READ_REQS)), NULL), NVL2(MAX(READ_BYTES), XMLELEMENT( "stat", XMLATTRIBUTES('read_bytes' AS "name"), MAX(READ_BYTES)), NULL), NVL2(MAX(WRITE_REQS), XMLELEMENT( "stat", XMLATTRIBUTES('write_reqs' AS "name"), MAX(WRITE_REQS)), NULL), NVL2(MAX(WRITE_BYTES), XMLELEMENT( "stat", XMLATTRIBUTES('write_bytes' AS "name"), MAX(WRITE_BYTES)), NULL), NVL2(MAX(IO_UNC_BYTES), XMLELEMENT( "stat", XMLATTRIBUTES('unc_bytes' AS "name"), MAX(IO_UNC_BYTES)), NULL), NVL2(MAX(IO_ELIG_BYTES), XMLELEMENT( "stat", XMLATTRIBUTES('elig_bytes' AS "name"), MAX(IO_ELIG_BYTES)), NULL), NVL 2(MAX(IO_RET_BYTES), XMLELEMENT( "stat", XMLATTRIBUTES('ret_bytes' AS "name"), MAX(IO_RET_BYTES)), NULL), CASE WHEN MAX(IO_INTER_BYTES) IS NULL OR NVL(MAX(IO_BYTES), 0) = 0 OR MAX(IO_INTER_BYTES) = MAX(IO_BYTES) OR NVL(MAX(IO_ELIG_BYTES), 0) = 0 THEN NULL ELSE XMLELEMENT( "stat", XMLATTRIBUTES('cell_offload_efficiency' AS "name"), ROUND(MAX(IO_BYTES)/ DECODE(MAX(IO_INTER_BYTES), 0, 1, MAX(IO_INTER_BYTES)), 2)) END, CASE WHEN NVL(MAX(IO_ELIG_BYTES), 0) = 0 OR MAX(IO_UNC_BYTES) = MAX(IO_RET_BYTES) THEN NULL ELSE XMLELEMENT( "stat", XMLATTRIBUTES('cell_offload_efficiency2' AS "name"), GREATEST(0, ROUND(100* (MAX(IO_UNC_BYTES) - MAX(IO_RET_BYTES)) / MAX(IO_ELIG_BYTES), 2))) END), CASE WHEN :B33 = 1 AND SUM(PI.ACTIVITY_COUNT) > 0 THEN XMLELEMENT( "activity_sampled", XMLATTRIBUTES( TO_CHAR(MIN(PI.ACTIVITY_START), :B12 ) AS "start_time", TO_CHAR(MAX(PI.ACTIVITY_END), :B12 ) AS "end_ time", ROUND((MAX(PI.ACTIVITY_END) - MIN(PI.ACTIVITY_START)) * 3600 * 24) + 1 AS "duration", SUM(PI.ACTIVITY_COUNT) AS "count", SUM(PI.IMQ_COUNT) AS "imq_count", SUM(PI.WAIT_COUNT) AS "wait_count", SUM(PI.CPU_COUNT) AS "cpu_count", SUM(PI.OTHER_SQL_COUNT) AS "other_sql_count", :B40 AS "cpu_cores", :B39 AS "hyperthread"), 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)) ELSE NULL END) PX_INSTANCES_XML FROM (SELECT MO.INST_ID, ASH.ACTIVITY_TYPE, ASH.EVENT_NAME, ASH.ACTIVITY_COUNT, ASH.IMQ_COUNT, ASH.WAIT_COUNT, ASH.CPU_COUNT, ASH.OTHER_SQL_COUNT, ASH.ACTIVITY_START, ASH.ACTIVITY_END, MO.ELAPSED_TIME, MO.QUEUING_TIME, MO.CPU_TIME, MO.APPLICATION_WAIT_TIME, MO.CONCURRENCY_WAIT_TIME, MO.CLUSTER_WAIT_TIME, MO.U SER_IO_WAIT_TIME, MO.PLSQL_EXEC_TIME, MO.JAVA_EXEC_TIME, MO.OTHER_WAIT_TIME, MO.FETCHES, MO.BUFFER_GETS, MO.IO_INTER_BYTES, MO.IO_BYTES, MO.READ_REQS, MO.READ_BYTES, MO.WRITE_REQS, MO.WRITE_BYTES, MO.IO_REQS, MO.IO_UNC_BYTES, MO.IO_ELIG_BYTES, MO.IO_RET_BYTES FROM (SELECT MO0.INST_ID, SUM(MO0.ELAPSED_TIME) ELAPSED_TIME, SUM(MO0.QUEUING_TIME) QUEUING_TIME, SUM(MO0.CPU_TIME) CPU_TIME, SUM(MO0.FETCHES) FETCHES, SUM(MO0.BUFFER_GETS) BUFFER_GETS, SUM(MO0.IO_INTER_BYTES) IO_INTER_BYTES, SUM(MO0.IO_BYTES) IO_BYTES, SUM(MO0.READ_REQS) READ_REQS, SUM(MO0.READ_BYTES) READ_BYTES, SUM(MO0.WRITE_REQS) WRITE_REQS, SUM(MO0.WRITE_BYTES) WRITE_BYTES, SUM(MO0.IO_REQS) IO_REQS, SUM(MO0.IO_UNC_BYTES) IO_UNC_BYTES, SUM(MO0.IO_ELIG_BYTES) IO_ELIG_BYTES, SUM(MO0.IO_RET_BYTES) IO_RET_BYTES, 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.IMQ_COUNT) IMQ_COUNT, SUM(ASH0.WAIT_COUNT) WAIT_COUNT, SUM(ASH0.CPU_COUNT) CPU_COUNT, SUM(ASH0.OTHER_SQL_COUNT) OTHER_SQL_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, MONITOR_AGG WHERE MO.INST_ID = ASH.INST_ID(+) AND MONITOR_AGG.DIST_INST_COUNT > 0 ) PI GROUP BY PI.INST_ID)PX_INSTANCE) PX_INSTANCES FROM MONITOR_AGG)) ELSE NULL END, XPLAN_XML, CASE WHEN :B43 = 1 THEN (SELECT XMLELEMENT( "plan_monitor", XMLATTRIBUTES(MAX(PLI.MAX_LINE_ACTIVITY_COUNT) AS "max_activity_count", MAX(PLI.OVERALL_MAX_IO_REQS) AS "max_io_reqs", MAX(PLI .OVERALL_MAX_IO_BYTES) AS "max_io_bytes", MAX(PLI.MAX_LINE_IMQ_COUNT) AS "max_imq_count", MAX(PLI.MAX_LINE_CPU_COUNT) AS "max_cpu_count", MAX(PLI.MAX_LINE_WAIT_COUNT) AS "max_wait_count", MAX(PLI.MAX_LINE_OTHER_SQL_COUNT) AS "max_other_sql_count"), XMLAGG( XMLELEMENT( "operation", XMLATTRIBUTES( PLI.PLAN_LINE_ID AS "id", PLI.PARENT_ID AS "parent_id", PLI.OPERATION AS "name", PLI.OPTIONS AS "options", PLI.DEPTH AS "depth", PLI.POSITION AS "position", PLI.INACTIVE AS "skp", PLI.PX_TYPE AS "px_type"), NVL2(PLI.OBJECT_NAME, XMLELEMENT( "object", XMLATTRIBUTES(PLI.OBJECT_TYPE AS "type"), XMLFOREST(PLI.OBJECT_OWNER AS "owner"), XMLFOREST(PLI.OBJECT_NAME AS "name")), NULL), XMLFOREST(PLI.PARTITION_START AS "partition_start", PLI.PARTITION_STOP AS "partition_stop"), CASE WHEN PLI.CARDINALITY IS NULL AND PLI.BYTES IS NULL AND PLI.COST IS NULL AND PLI.TEMP_SPACE IS NULL AND PLI.TIME IS NULL THEN NULL ELSE XMLELEMENT( "optimizer", NULL, NVL2(PLI.CARDINALITY, XMLFOREST(PLI.CARDINALITY AS "cardinality"), NULL), NVL2(PLI.BYTES, XMLFOREST(PLI.BYTES AS "bytes"), NULL), NVL2(PLI.COST, XMLFOREST(PLI.COST AS "cost"), NULL), NVL2(PLI.CPU_COST, XMLFOREST(PLI.CPU_COST AS "cpu_cost"), NULL), NVL2(PLI.IO_COST, XMLFOREST(PLI.IO_COST AS "io_cost"), NULL), NVL2(PLI.TEMP_SPACE, XMLFOREST(PLI.TEMP_SPACE AS "temp"), NULL), NVL2(PLI.TIME, XMLFOREST(PLI.TIME AS "time"), NULL)) END, XMLELEMENT( "stats", XMLATTRIBUTES('plan_monitor' AS "type"), NVL2(PLI.FIRST_MOVE_TIME, XMLELEMENT( "stat", XMLATTRIBUTES('first_active' AS "name"), TO_CHAR(FIRST_MOVE_TIME, :B12 )), NULL), CASE WHEN PLI.FIRST_MOVE_TIME != PLI.FIRST_CHANGE_TIME THEN XMLE LEMENT( "stat", XMLATTRIBUTES('first_row' AS "name"), TO_CHAR(FIRST_CHANGE_TIME, :B12 )) ELSE NULL END, NVL2(PLI.LAST_MOVE_TIME, XMLELEMENT( "stat", XMLATTRIBUTES('last_active' AS "name"), TO_CHAR(LAST_MOVE_TIME, :B12 )), NULL), CASE WHEN (PLI.FIRST_MOVE_TIME IS NULL OR PLI.LAST_MOVE_TIME IS NULL) THEN NULL ELSE XMLELEMENT( "stat", XMLATTRIBUTES('duration' AS "name"), ROUND((LAST_MOVE_TIME - FIRST_MOVE_TIME) * 3600 * 24)+1) END, CASE WHEN (PLI.OVERALL_LAST_MOVE_TIME IS NULL OR PLI.LAST_MOVE_TIME IS NULL) THEN NULL ELSE XMLELEMENT( "stat", XMLATTRIBUTES('from_most_recent' AS "name"), ROUND((PLI.OVERALL_LAST_MOVE_TIME - PLI.LAST_MOVE_TIME) * 3600 * 24)) END, NVL2(PLI.LAST_MOVE_TIME, XMLELEMENT( "stat", XMLATTRIBUTES( 'from_sql_exec_start' AS "name"), ROUND( (FIRST_MOVE_TIME - :B8 ) * 3600*24)), NULL), NVL2(PLI.PERCENT_COMPLETE, XMLELEMENT( "stat", XMLATTRIB UTES('percent_complete' AS "name"), PLI.PERCENT_COMPLETE), NULL), NVL2(PLI.TIME_REMAINING, XMLELEMENT( "stat", XMLATTRIBUTES('time_left' AS "name"), PLI.TIME_REMAINING), NULL), CASE WHEN PLI.HAS_EXECUTED = 1 THEN XMLELEMENT( "stat", XMLATTRIBUTES('starts' AS "name"), PLI.STARTS) ELSE NULL END, CASE WHEN PLI.DOP > 0 AND PLI.HAS_EXECUTED = 1 THEN XMLELEMENT( "stat", XMLATTRIBUTES( 'max_starts' AS "name", DECODE(:B67 , 'Y', MOD(TRUNC(MAX_STARTS/1000000), 10000), NULL) AS "iid", MOD(MAX_STARTS, 1000000) AS "sid"), TRUNC(PLI.MAX_STARTS/10000000000)) ELSE NULL END, CASE WHEN PLI.DOP > 0 AND PLI.HAS_EXECUTED = 1 THEN XMLELEMENT( "stat", XMLATTRIBUTES('dop' AS "name"), PLI.DOP) ELSE NULL END, CASE WHEN NEED_ROWS IS NOT NULL AND PLI.FIRST_MOVE_TIME IS NOT NULL THEN XMLELEMENT( "stat", XMLATTRIBUTES('cardinality' AS "name"), PLI.OUTPUT_ ROWS) ELSE NULL END, CASE WHEN PLI.NEED_ROWS IS NOT NULL AND PLI.DOP > 0 AND PLI.MAX_OUTPUT_ROWS IS NOT NULL AND (PLI.FIRST_MOVE_TIME IS NOT NULL) THEN XMLELEMENT( "stat", XMLATTRIBUTES( 'max_card' AS "name", DECODE(:B67 , 'Y', MOD(TRUNC(MAX_OUTPUT_ROWS/1000000), 10000), NULL) AS "iid", MOD(MAX_OUTPUT_ROWS, 1000000) AS "sid"), TRUNC(PLI.MAX_OUTPUT_ROWS/10000000000)) ELSE NULL END, CASE WHEN PLI.MEM > 0 THEN XMLELEMENT( "stat", XMLATTRIBUTES('memory' AS "name"), PLI.MEM) ELSE NULL END, CASE WHEN PLI.MAX_MEM > 0 THEN XMLELEMENT( "stat", XMLATTRIBUTES('max_memory' AS "name"), PLI.MAX_MEM) ELSE NULL END, CASE WHEN PLI.DOP > 0 AND PLI.MIN_MAX_MEM IS NOT NULL AND PLI.MAX_MEM > 0 THEN XMLELEMENT( "stat", XMLATTRIBUTES( 'min_max_mem' AS "name", DECODE(:B67 , 'Y', MOD(TRUNC(MIN_MAX_MEM/1000000), 10000), NULL) AS "iid", MOD(MIN_MAX_MEM, 1000000) AS "sid"), TRUNC(PLI.MIN_MAX_MEM/10000000000)) ELSE NULL END, CASE WHEN PLI.TEMP > 0 THEN XMLELEMENT( "stat", XMLATTRIBUTES('temp' AS "name"), PLI.TEMP) ELSE NULL END, CASE WHEN PLI.MAX_TEMP > 0 THEN XMLELEMENT( "stat", XMLATTRIBUTES('max_temp' AS "name"), PLI.MAX_TEMP) ELSE NULL END, CASE WHEN PLI.MAX_TEMP > 0 THEN XMLELEMENT( "stat", XMLATTRIBUTES('spill_count' AS "name"), PLI.SPILL_COUNT) ELSE NULL END, CASE WHEN PLI.DOP > 0 AND PLI.MAX_MAX_TEMP IS NOT NULL AND PLI.MAX_TEMP > 0 THEN XMLELEMENT( "stat", XMLATTRIBUTES( 'max_max_temp' AS "name", DECODE(:B67 , 'Y', MOD(TRUNC(MAX_MAX_TEMP/1000000), 10000), NULL) AS "iid", MOD(MAX_MAX_TEMP, 1000000) AS "sid"), TRUNC(PLI.MAX_MAX_TEMP/10000000000)) ELSE NULL END, CASE WHEN PLI.READ_REQS > 0 THEN XMLELEMENT( "stat", XMLATTRIBUTES('read_reqs' AS "name"), PLI.READ_REQS) ELSE NU LL END, CASE WHEN PLI.DOP > 0 AND PLI.MAX_READ_REQS IS NOT NULL AND PLI.READ_REQS > 0 THEN XMLELEMENT( "stat", XMLATTRIBUTES( 'max_read_reqs' AS "name", DECODE(:B67 , 'Y', MOD(TRUNC(PLI.MAX_READ_REQS/1000000), 10000), NULL) AS "iid", MOD(PLI.MAX_READ_REQS, 1000000) AS "sid"), TRUNC(PLI.MAX_READ_REQS/10000000000)) ELSE NULL END, CASE WHEN PLI.READ_BYTES > 0 THEN XMLELEMENT( "stat", XMLATTRIBUTES('read_bytes' AS "name"), PLI.READ_BYTES) ELSE NULL END, CASE WHEN PLI.DOP > 0 AND PLI.MAX_READ_BYTES IS NOT NULL AND PLI.READ_BYTES > 0 THEN XMLELEMENT( "stat", XMLATTRIBUTES( 'max_read_bytes' AS "name", DECODE(:B67 , 'Y', MOD(TRUNC(PLI.MAX_READ_BYTES/1000000), 10000), NULL) AS "iid", MOD(PLI.MAX_READ_BYTES, 1000000) AS "sid"), TRUNC(PLI.MAX_READ_BYTES/10000000000)) ELSE NULL END, CASE WHEN PLI.WRITE_REQS > 0 THEN XMLELEMENT( "stat", XMLATTRIBUT ES('write_reqs' AS "name"), PLI.WRITE_REQS) ELSE NULL END, CASE WHEN PLI.DOP > 0 AND PLI.MAX_WRITE_REQS IS NOT NULL AND PLI.WRITE_REQS > 0 THEN XMLELEMENT( "stat", XMLATTRIBUTES( 'max_write_reqs' AS "name", DECODE(:B67 , 'Y', MOD(TRUNC(PLI.MAX_WRITE_REQS/1000000), 10000), NULL) AS "iid", MOD(PLI.MAX_WRITE_REQS, 1000000) AS "sid"), TRUNC(PLI.MAX_WRITE_REQS/10000000000)) ELSE NULL END, CASE WHEN PLI.WRITE_BYTES > 0 THEN XMLELEMENT( "stat", XMLATTRIBUTES('write_bytes' AS "name"), PLI.WRITE_BYTES) ELSE NULL END, CASE WHEN PLI.DOP > 0 AND PLI.MAX_WRITE_BYTES IS NOT NULL AND PLI.WRITE_BYTES > 0 THEN XMLELEMENT( "stat", XMLATTRIBUTES( 'max_write_bytes' AS "name", DECODE(:B67 , 'Y', MOD(TRUNC(PLI.MAX_WRITE_BYTES/1000000), 10000), NULL) AS "iid", MOD(PLI.MAX_WRITE_BYTES, 1000000) AS "sid"), TRUNC(PLI.MAX_WRITE_BYTES/10000000000)) ELSE NULL END, CA SE WHEN PLI.IO_INTER_BYTES IS NOT NULL AND PLI.IO_BYTES > 0 AND PLI.IO_BYTES != PLI.IO_INTER_BYTES THEN XMLELEMENT( "stat", XMLATTRIBUTES('io_inter_bytes' AS "name"), PLI.IO_INTER_BYTES) ELSE NULL END, CASE WHEN PLI.DOP > 0 AND PLI.MAX_IO_INTER_BYTES IS NOT NULL AND PLI.IO_BYTES > 0 AND PLI.IO_BYTES != PLI.IO_INTER_BYTES THEN XMLELEMENT( "stat", XMLATTRIBUTES( 'max_io_inter_bytes' AS "name", DECODE(:B67 , 'Y', MOD(TRUNC(MAX_IO_INTER_BYTES/1000000), 10000), NULL) AS "iid", MOD(MAX_IO_INTER_BYTES, 1000000) AS "sid"), TRUNC(PLI.MAX_IO_INTER_BYTES/10000000000)) ELSE NULL END, CASE WHEN PLI.IO_INTER_BYTES IS NOT NULL AND PLI.IO_BYTES > 0 AND PLI.IO_BYTES != PLI.IO_INTER_BYTES THEN XMLELEMENT( "stat", XMLATTRIBUTES('cell_offload_efficiency' AS "name"), ROUND(PLI.IO_BYTES / DECODE(PLI.IO_INTER_BYTES, 0, 1, PLI.IO_INTER_BYTES), 2)) ELSE NULL END), NVL2(STAT_GID, XMLELEMENT( "rws stats", XMLATTRIBUTES( PLI.STAT_GID AS "group_id"), DECODE(GID_ROWNUM, 1, (SELECT XMLELEMENT( "metadata", NULL, XMLAGG( XMLELEMENT( "stat", XMLATTRIBUTES( ROWNUM AS "id", NAME AS "name", DESCRIPTION AS "desc", TYPE AS "type", DECODE(FLAGS, 0, NULL, FLAGS) AS "flags"), NULL) ORDER BY ID)) FROM V$SQL_MONITOR_STATNAME WHERE GROUP_ID = PLI.STAT_GID), NULL), NVL2(STAT1_VALUE, XMLELEMENT( "stat", XMLATTRIBUTES(1 AS "id"), STAT1_VALUE), NULL), NVL2(STAT2_VALUE, XMLELEMENT( "stat", XMLATTRIBUTES(2 AS "id"), STAT2_VALUE), NULL), NVL2(STAT3_VALUE, XMLELEMENT( "stat", XMLATTRIBUTES(3 AS "id"), STAT3_VALUE), NULL), NVL2(STAT4_VALUE, XMLELEMENT( "stat", XMLATTRIBUTES(4 AS "id"), STAT4_VALUE), NULL), NVL2(STAT5_VALUE, XMLELEMENT( "stat", XMLATTRIBUTES(5 AS "id"), STAT5_VALUE), NULL), NVL2(STAT6_VALUE, XMLELEMENT( "stat", XMLATTRIBUTES(6 AS "id"), STAT6_VALUE), NULL), NVL2(STAT7_VALUE, XMLELEMENT( "stat", XMLATTRIBUTES(7 AS "id"), STAT7_VALUE), NULL), NVL2(STAT8_VALUE, XMLELEMENT( "stat", XMLATTRIBUTES(8 AS "id"), STAT8_VALUE), NULL), NVL2(STAT9_VALUE, XMLELEMENT( "stat", XMLATTRIBUTES(9 AS "id"), STAT9_VALUE), NULL), NVL2(STAT10_VALUE, XMLELEMENT( "stat", XMLATTRIBUTES(10 AS "id"), STAT10_VALUE), NULL)), NULL), CASE WHEN PLI.LINE_ACTIVITY_COUNT > 0 AND :B33 = 1 THEN XMLELEMENT( "activity_sampled", XMLATTRIBUTES( TO_CHAR(PLI.LINE_ACTIVITY_START, :B12 ) AS "start_time", TO_CHAR(PLI.LINE_ACTIVITY_END, :B12 ) AS "end_time", ROUND((PLI.LINE_ACTIVITY_END - PLI.LINE_ACTIVITY_START) * 3600*24) + 1 AS "duration", PLI.LINE_ACTIVITY_COUNT AS "count", PLI.LINE_IMQ_COUNT AS "imq_cou nt", PLI.LINE_WAIT_COUNT AS "wait_count", PLI.LINE_CPU_COUNT AS "cpu_count", PLI.LINE_OTHER_SQL_COUNT AS "other_sql_count", :B40 AS "cpu_cores", :B39 AS "hyperthread"), PLI.ACTIVITY_TOTAL, PLI.PLAN_ACTIVITY_HISTO) ELSE NULL END) ORDER BY PLI.PLAN_LINE_ID) ) FROM (SELECT AT.MAX_LINE_ACTIVITY_COUNT, PM.OVERALL_MAX_IO_REQS, PM.OVERALL_MAX_IO_BYTES, AT.MAX_LINE_IMQ_COUNT, AT.MAX_LINE_CPU_COUNT, AT.MAX_LINE_WAIT_COUNT, AT.LINE_OTHER_SQL_COUNT, AT.MAX_LINE_OTHER_SQL_COUNT, PM.PLAN_LINE_ID, PM.PARENT_ID, PM.OPERATION, PM.OPTIONS, PM.DEPTH, PM.POSITION, PM.INACTIVE, CASE WHEN PM.OPERATION = 'PX COORDINATOR' AND :B23 = 'Y' AND (PM.PX_SERVER_SET IS NOT NULL OR AH.PX_SERVER_SET IS NOT NULL) THEN 'QC' 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 (:B23 = 'N' OR (PM.LAST_CHANGE_TIME IS NULL AND AT.LINE_ACTIVITY_END IS NULL)) THEN NULL ELSE 'QC' END PX_TYPE, PM.FIRST_CHANGE_TIME, AT.LINE_ACTIVITY_START, PM.LAST_CHANGE_TIME, PM.OVERALL_LAST_CHANGE_TIME, AT.LINE_ACTIVITY_END, AT.OVERALL_LINE_ACTIVITY_END, LEAST(NVL(AT.LINE_ACTIVITY_START, PM.FIRST_CHANGE_TIME), NVL(PM.FIRST_CHANGE_TIME, AT.LINE_ACTIVITY_START)) FIRST_MOVE_TIME, GREATEST(NVL(AT.LINE_ACTIVITY_END, PM.LAST_CHANGE_TIME), NVL(PM.LAST_CHANGE_TIME, AT.LINE_ACTIVITY_END)) LAST_MOVE_TIME, GREATEST(NVL(AT.OVERALL_LINE_ACTIVITY_END, PM.OVERALL_LAST_CHANGE_TIME), NVL(PM.OVERALL_LAST_CHANGE_TIME, AT.OVERALL_LINE_ACTIVITY_END)) OVERALL_LAST_MOVE_TIME, CASE WHEN PM.STARTS IS NOT NULL AND PM.STARTS > 0 THEN 1 ELSE 0 END HAS_EXECUTED, PM.OBJECT_NAME, PM.OBJECT_TYPE, PM.OBJECT_OWNER, PM.PARTITION_START, PM.PARTITION_STOP, PM.CARDINALITY, PM.BYTES, PM.COST, PM.TEMP_SPACE, PM.TIME, PM.CPU_COST, PM.IO_COST, LO.PERCENT_COMPLETE, LO.TIME_REMAINING, PM.STARTS, PM.DOP, PM.MAX_STARTS, PM.OUTPUT_ROWS, PM.NEED_ROWS, PM.MAX_OUTPUT_ROWS, P M.MEM, PM.MAX_MEM, PM.MIN_MAX_MEM, PM.TEMP, PM.MAX_TEMP, PM.SPILL_COUNT, PM.MAX_MAX_TEMP, PM.READ_REQS, PM.MAX_READ_REQS, PM.READ_BYTES, PM.MAX_READ_BYTES, PM.WRITE_REQS, PM.MAX_WRITE_REQS, PM.WRITE_BYTES, PM.MAX_WRITE_BYTES, PM.IO_INTER_BYTES, PM.IO_BYTES, PM.MAX_IO_INTER_BYTES, AT.LINE_ACTIVITY_COUNT, AT.LINE_IMQ_COUNT, AT.LINE_WAIT_COUNT, AT.LINE_CPU_COUNT, AT.ACTIVITY_TOTAL, AH.PLAN_ACTIVITY_HISTO, PM.STAT_GID, PM.GID_ROWNUM, PM.STAT1_VALUE, PM.STAT2_VALUE, PM.STAT3_VALUE, PM.STAT4_VALUE, PM.STAT5_VALUE, PM.STAT6_VALUE, PM.STAT7_VALUE, PM.STAT8_VALUE, PM.STAT9_VALUE, PM.STAT10_VALUE FROM (SELECT AT0.*, MAX(LINE_ACTIVITY_END) OVER() OVERALL_LINE_ACTIVITY_END, MAX(LINE_ACTIVITY_COUNT) OVER() MAX_LINE_ACTIVITY_COUNT, MAX(LINE_IMQ_COUNT) OVER() MAX_LINE_IMQ_COUNT, MAX(LINE_CPU_COUNT) OVER() MAX_LINE_CPU_COUNT, MAX(LINE_WAIT_COUNT) OVER() MAX_LINE_WAIT_COUNT, MAX(LINE_OTHER_SQL_COUNT) OVER() MAX_LINE_OTHER_SQL_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.IMQ_COUNT) LINE_IMQ_COUNT, SUM(AD1.WAIT_COUNT) LINE_WAIT_COUNT, SUM(AD1.OTHER_SQL_COUNT) LINE_OTHER_SQL_COUNT, SUM(AD1.CPU_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_TYPE, AD0.EVENT_NAME, MIN(AD0.ACTIVITY_START) LINE_ACTIVITY_START, MAX(AD0.ACTIVITY_END) LINE_ACTIVITY_END, SUM(AD0.ACTIVITY_COUNT) ACTIVITY_COUNT, SUM(AD0.IMQ_COUNT) IMQ_COUNT, SUM(AD0.WAIT_COUNT) WAIT_COUNT, SUM(AD0.CPU_COUNT) CPU_COUNT, SUM(AD0.OTHER_SQL_COUNT) OTHER_SQL_COUNT, MAX(AD0.DFO_MOST_ACTIVE_IID) DFO_MOST_ACTIVE_IID, MAX(AD0.DFO _MOST_ACTIVE_SID) DFO_MOST_ACTIVE_SID, SUM(AD0.DFO_MOST_ACTIVE_COUNT) DFO_MOST_ACTIVE_COUNT FROM ASH_DATA AD0 WHERE AD0.PLAN_LINE_ID IS NOT NULL 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.IMQ_COUNT) IMQ_COUNT, SUM(AD2.WAIT_COUNT) WAIT_COUNT, SUM(AD2.CPU_COUNT) CPU_COUNT, SUM(AD2.OTHER_SQL_COUNT) OTHER_SQL_COUNT, CASE WHEN :B29 = 1 AND SUM(AD2.ACTIVITY_COUNT) > 0 THEN XMLELEMENT( "activity_histogram", XMLATTRIBUTES( :B26 AS "bucket_interval", :B27 AS "bucket_count", TO_CHAR( :B20 , :B12 ) AS "start_time", TO_CHAR(:B22 , :B12 ) AS "end_time", ROUND((:B22 -:B20 ) *3600*24) + 1 AS "duration"), XMLAGG(XMLELEMENT( "bucket", XMLATTRIBUTE S( AD2.BUCKET_NUM AS "number"), AD2.ACTIVITY_BUCKET) ORDER BY AD2.BUCKET_NUM)) ELSE NULL END PLAN_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.IMQ_COUNT) IMQ_COUNT, SUM(AD1.WAIT_COUNT) WAIT_COUNT, SUM(AD1.CPU_COUNT) CPU_COUNT, SUM(AD1.OTHER_SQL_COUNT) OTHER_SQL_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.ACTIVITY_TYPE, AD1.EVENT_NAME) ACTIVITY_BUCKET FROM (SELECT AD0.PLAN_LINE_ID, AD0.PLAN_ACTIVITY_BUCKET_NUM 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, MAX(AD0.BUCKET_ACTIVITY_END) BUCKET_ACTIVITY_END, SUM(AD0.IMQ_COUNT) IMQ_COUNT, SUM(AD0.CPU_COUNT) CPU_COUNT, SUM(AD0.WAIT_COUNT) WAIT_COUNT, SUM(AD0.OTHER_SQL_COUNT) OTHER_SQL_COUNT, SUM(AD0.ACTIVITY_COUNT) ACTIVITY_COUNT FROM ASH_DATA AD0 GROUP BY AD0.PLAN_LINE_ID, AD0.PLAN_ACTIVITY_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 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 (:B49 = 'Y' OR :B70 = 'DONE (ERROR)') AND LO.SQL_ID = :B9 AND LO.SQL_EXEC_START = :B8 AN D LO.SQL_EXEC_ID = :B7 AND LO.INST_ID = MO.INST_ID AND LO.SID = MO.SID GROUP BY LO.SQL_PLAN_LINE_ID) LO, (SELECT PM0.*, CASE WHEN PM0.STARTS IS NULL OR PM0.STARTS = 0 OR PM0.OUTPUT_ROWS IS NULL THEN NULL ELSE 1 END NEED_ROWS, ROW_NUMBER() OVER(PARTITION BY PM0.STAT_GID ORDER BY PM0.PLAN_LINE_ID ) GID_ROWNUM, MAX(LAST_CHANGE_TIME) OVER() OVERALL_LAST_CHANGE_TIME, MAX(MAX_IO_REQS) OVER() OVERALL_MAX_IO_REQS, MAX(MAX_IO_BYTES) OVER() OVERALL_MAX_IO_BYTES FROM (SELECT /*+ leading(mo) use_hash(plm) */ PLM.PLAN_LINE_ID PLAN_LINE_ID, PLM.PLAN_OPERATION OPERATION, PLM.PLAN_OPTIONS OPTIONS, MAX(PLM.PLAN_PARENT_ID) PARENT_ID, MAX(PLM.PLAN_DEPTH) DEPTH, MAX(PLM.PLAN_POSITION) POSITION, MAX(PLM.PLAN_OPERATION_INACTIVE) INACTIVE, MAX(PLM.PLAN_OBJECT_OWNER) OBJECT_OWNER, MAX(PLM.PLAN_OBJECT_NAME) OBJECT_NAME, MAX(PLM.PLAN_OBJECT_TYPE) OBJECT_TYPE, MAX(PLM.PLAN_COST) COST, MAX(PLM.PLAN_CARDINALITY) CARDINALITY, MAX(PLM.PLAN_BYTES) BYTES, MAX(PLM.PLAN_CPU_COST) CPU_COST, MAX(PLM.PL AN_IO_COST) IO_COST, MAX(PLM.PLAN_TEMP_SPACE) TEMP_SPACE, MAX(PLM.PLAN_TIME) TIME, MAX(PLM.PLAN_PARTITION_START) PARTITION_START, MAX(PLM.PLAN_PARTITION_STOP) PARTITION_STOP, MIN(PLM.FIRST_CHANGE_TIME) FIRST_CHANGE_TIME, MAX(PLM.LAST_CHANGE_TIME) LAST_CHANGE_TIME, MIN(PLM.LAST_CHANGE_TIME) MIN_LAST_CHANGE_TIME, MIN(NVL2(PLM.FIRST_CHANGE_TIME, MO.PX_SERVER_SET, NULL)) PX_SERVER_SET, COUNT(CASE WHEN PLM.PAR IS NOT NULL AND PLM.STARTS IS NOT NULL AND PLM.STARTS > 0 AND PLM.PLAN_LINE_ID != 0 AND PLM.PLAN_OPERATION != 'PX COORDINATOR' THEN 1 ELSE NULL END) DOP, SUM(PLM.STARTS) STARTS, MAX(NVL2(PAR, PLM.STARTS * 10000000000 + PLM.ES, NULL)) MAX_STARTS, SUM(PLM.OUTPUT_ROWS) OUTPUT_ROWS, MAX(NVL2(PAR, PLM.OUTPUT_ROWS * 10000000000 +PLM.ES, NULL)) MAX_OUTPUT_ROWS, SUM(PLM.WORKAREA_MEM) MEM, SUM(PLM.WORKAREA_MAX_MEM) MAX_MEM, MIN(NVL2(PAR, PLM.WORKAREA_MAX_MEM * 10000000000 + PLM.ES, NULL)) MIN_MAX_MEM, SUM(PLM.WORKAREA_TEMPSEG) TEMP, SUM(PLM.WORKAREA_MAX_TEMPSEG) MA X_TEMP, MAX(NVL2(PAR, PLM.WORKAREA_MAX_TEMPSEG * 10000000000 + PLM.ES, NULL)) MAX_MAX_TEMP, COUNT(PLM.WORKAREA_MAX_TEMPSEG) SPILL_COUNT, SUM(PLM.PHYSICAL_READ_REQUESTS) READ_REQS, MAX(NVL2(PAR, PLM.PHYSICAL_READ_REQUESTS * 10000000000 + PLM.ES, NULL)) MAX_READ_REQS, SUM(PLM.PHYSICAL_READ_BYTES) READ_BYTES, MAX(NVL2(PAR, PLM.PHYSICAL_READ_BYTES * 10000000000 + PLM.ES, NULL)) MAX_READ_BYTES, SUM(PLM.PHYSICAL_WRITE_REQUESTS) WRITE_REQS, MAX(NVL2(PAR, PLM.PHYSICAL_WRITE_REQUESTS * 10000000000 + PLM.ES, NULL)) MAX_WRITE_REQS, SUM(PLM.PHYSICAL_WRITE_BYTES) WRITE_BYTES, MAX(NVL2(PAR, PLM.PHYSICAL_WRITE_BYTES * 10000000000 + PLM.ES, NULL)) MAX_WRITE_BYTES, NVL(SUM(PLM.PHYSICAL_READ_BYTES), 0) + NVL(SUM(PLM.PHYSICAL_WRITE_BYTES), 0) IO_BYTES, SUM(NVL(PLM.PHYSICAL_READ_REQUESTS, 0) + NVL(PLM.PHYSICAL_WRITE_REQUESTS, 0)) MAX_IO_REQS, SUM(NVL(PLM.PHYSICAL_READ_BYTES, 0) + NVL(PLM.PHYSICAL_WRITE_BYTES, 0)) MAX_IO_BYTES, SUM(PLM.IO_INTERCONNECT_BYTES) IO_INTER_BYTES, M AX(NVL2(PAR, PLM.IO_INTERCONNECT_BYTES * 10000000000 + PLM.ES, NULL)) MAX_IO_INTER_BYTES, MAX(OTHERSTAT_GROUP_ID) STAT_GID, NVL(DECODE(MAX(OTHERSTAT_1_TYPE), 3, MAX(OTHERSTAT_1_VALUE), 4, MAX(OTHERSTAT_1_VALUE), 6, MIN(OTHERSTAT_1_VALUE), 7, MIN(OTHERSTAT_1_VALUE), NULL), SUM(DECODE(OTHERSTAT_1_TYPE, 1, OTHERSTAT_1_VALUE, 2, OTHERSTAT_1_VALUE, NULL))) STAT1_VALUE, NVL(MOD((DECODE(MAX(OTHERSTAT_2_TYPE), 3, MAX(OTHERSTAT_2_VALUE), 5, DECODE(MAX(OTHERSTAT_1_TYPE), 4, MAX(RMAX1 + OTHERSTAT_2_VALUE), 7, MIN(RMAX1 + OTHERSTAT_2_VALUE), NULL), 6, MIN(OTHERSTAT_2_VALUE), NULL)), 10000000000000000), SUM(DECODE(OTHERSTAT_2_TYPE, 1, OTHERSTAT_2_VALUE, 2, OTHERSTAT_2_VALUE, NULL))) STAT2_VALUE, NVL(MOD((DECODE(MAX(OTHERSTAT_3_TYPE), 3, MAX(OTHERSTAT_3_VALUE), 5, DECODE(MAX(OTHERSTAT_1_TYPE), 4, MAX(RMAX1 + OTHERSTAT_3_VALUE), 7, MIN(RMAX1 + OTHERSTAT_3_VALUE), NULL), 6, MIN(OTHERSTAT_3_VALUE), NULL)), 10000000000000000), SUM(DECODE(OTHERSTAT_3_ TYPE, 1, OTHERSTAT_3_VALUE, 2, OTHERSTAT_3_VALUE, NULL))) STAT3_VALUE, NVL(MOD((DECODE(MAX(OTHERSTAT_4_TYPE), 3, MAX(OTHERSTAT_4_VALUE), 5, DECODE(MAX(OTHERSTAT_1_TYPE), 4, MAX(RMAX1 + OTHERSTAT_4_VALUE), 7, MIN(RMAX1 + OTHERSTAT_4_VALUE), NULL), 6, MIN(OTHERSTAT_4_VALUE), NULL)), 10000000000000000), SUM(DECODE(OTHERSTAT_4_TYPE, 1, OTHERSTAT_4_VALUE, 2, OTHERSTAT_4_VALUE, NULL))) STAT4_VALUE, NVL(MOD((DECODE(MAX(OTHERSTAT_5_TYPE), 3, MAX(OTHERSTAT_5_VALUE), 5, DECODE(MAX(OTHERSTAT_1_TYPE), 4, MAX(RMAX1 + OTHERSTAT_5_VALUE), 7, MIN(RMAX1 + OTHERSTAT_5_VALUE), NULL), 6, MIN(OTHERSTAT_5_VALUE), NULL)), 10000000000000000), SUM(DECODE(OTHERSTAT_5_TYPE, 1, OTHERSTAT_5_VALUE, 2, OTHERSTAT_5_VALUE, NULL))) STAT5_VALUE, NVL(MOD((DECODE(MAX(OTHERSTAT_6_TYPE), 3, MAX(OTHERSTAT_6_VALUE), 5, DECODE(MAX(OTHERSTAT_1_TYPE), 4, MAX(RMAX1 + OTHERSTAT_6_VALUE), 7, MIN(RMAX1 + OTHERSTAT_6_VALUE), NULL), 6, MIN(OTHERSTAT_6_VALUE), NULL)), 100000000 00000000), SUM(DECODE(OTHERSTAT_6_TYPE, 1, OTHERSTAT_6_VALUE, 2, OTHERSTAT_6_VALUE, NULL))) STAT6_VALUE, NVL(MOD((DECODE(MAX(OTHERSTAT_7_TYPE), 3, MAX(OTHERSTAT_7_VALUE), 5, DECODE(MAX(OTHERSTAT_1_TYPE), 4, MAX(RMAX1 + OTHERSTAT_7_VALUE), 7, MIN(RMAX1 + OTHERSTAT_7_VALUE), NULL), 6, MIN(OTHERSTAT_7_VALUE), NULL)), 10000000000000000), SUM(DECODE(OTHERSTAT_7_TYPE, 1, OTHERSTAT_7_VALUE, 2, OTHERSTAT_7_VALUE, NULL))) STAT7_VALUE, NVL(MOD((DECODE(MAX(OTHERSTAT_8_TYPE), 3, MAX(OTHERSTAT_8_VALUE), 5, DECODE(MAX(OTHERSTAT_1_TYPE), 4, MAX(RMAX1 + OTHERSTAT_8_VALUE), 7, MIN(RMAX1 + OTHERSTAT_8_VALUE), NULL), 6, MIN(OTHERSTAT_8_VALUE), NULL)), 10000000000000000), SUM(DECODE(OTHERSTAT_8_TYPE, 1, OTHERSTAT_8_VALUE, 2, OTHERSTAT_8_VALUE, NULL))) STAT8_VALUE, NVL(MOD((DECODE(MAX(OTHERSTAT_9_TYPE), 3, MAX(OTHERSTAT_9_VALUE), 5, DECODE(MAX(OTHERSTAT_1_TYPE), 4, MAX(RMAX1 + OTHERSTAT_9_VALUE), 7, MIN(RMAX1 + OTHERSTAT_9_VALUE), NULL), 6, MIN(OTHE RSTAT_9_VALUE), NULL)), 10000000000000000), SUM(DECODE(OTHERSTAT_9_TYPE, 1, OTHERSTAT_9_VALUE, 2, OTHERSTAT_9_VALUE, NULL))) STAT9_VALUE, NVL(MOD((DECODE(MAX(OTHERSTAT_10_TYPE), 3, MAX(OTHERSTAT_10_VALUE), 5, DECODE(MAX(OTHERSTAT_1_TYPE), 4, MAX(RMAX1 + OTHERSTAT_10_VALUE), 7, MIN(RMAX1 + OTHERSTAT_10_VALUE), NULL), 6, MIN(OTHERSTAT_10_VALUE), NULL)), 10000000000000000), SUM(DECODE(OTHERSTAT_10_TYPE, 1, OTHERSTAT_10_VALUE, 2, OTHERSTAT_10_VALUE, NULL))) STAT10_VALUE FROM (SELECT A.*, OTHERSTAT_1_VALUE * 10000000000000000 RMAX1, A.INST_ID * 1000000 + A.SID ES, DECODE(A.PROCESS_NAME, 'ora', NULL, 1) PAR FROM GV$SQL_PLAN_MONITOR A) PLM, MONITOR_DATA MO WHERE PLM.SQL_ID = :B9 AND PLM.SQL_EXEC_START = :B8 AND PLM.SQL_EXEC_ID = :B7 AND PLM.INST_ID = MO.INST_ID AND PLM.INST_ID BETWEEN :B11 AND :B10 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 LO.PLAN_LINE_ID(+) = PM.PLAN_LINE_ID AND (:B21 IS NULL OR PM.PLAN_LINE_ID = :B21 )) PLI) ELSE NULL END, CASE WHEN :B42 = 1 THEN DBMS_SQLTUNE.BUILD_STASH_XML( SESSION_ID=>:B19 , SESSION_SERIAL=>:B18 , SESSION_INST_ID=>:B17 , PX_MODE=>'yes', START_TIME=>:B20 , END_TIME=>:B22 , MISSING_SECONDS=> :B41 , INSTANCE_LOW_FILTER=>:B11 , INSTANCE_HIGH_FILTER=>:B10 , BUCKET_MAX_COUNT=>NULL, BUCKET_INTERVAL=>:B26 , REPORT_LEVEL=>'typical', CPU_CORES=>:B40 , IS_HYPER=>:B39 ) ELSE NULL END), (CASE WHEN :B38 = 1 THEN (SELECT XMLELEMENT( "skewed_sessions", XMLATTRIBUTES( DECODE(MIN(INST_ID), NULL, :B17 , MIN(INST_ID)) AS "min_iid", DECODE(MAX(INST_ID), NULL, :B17 , MAX(INST_ID)) AS "max_iid"), XMLAGG( XMLELEMENT( "s", XMLATTRIBUTES( INST_ID AS "i"), SESSION_ID) ORDER BY INST_ID, SESSION_ID)) FROM (SELECT SESS.* FROM (SELECT DECODE(AD.DFO_MOST_ACTIVE_IID, :B17 , NULL, AD.DFO_MOST_ACTIVE_IID) INST_ID, AD.DFO_MOST_ACTIVE_SID SESSION_ID FROM ASH_DATA AD WHERE AD.DFO_MOST_ACTIVE_COUNT IS NOT NULL GROUP BY AD.DFO_MOST_ACTIVE_IID, AD.DFO_MOST_ACTIVE_SID ORDER BY MAX(AD.DFO_MOST_ACTIVE_COUNT) DESC) SESS WHERE ROWNUM <= 100)) ELSE NULL END), (CASE WHEN :B37 = 1 THEN (SELECT XMLELEMENT( "report_repository_summary", CASE WHEN MA.MAX_DBOP_NAME IS NOT NULL THEN XMLELEMENT( "dbop", XMLATTRIBUTES('F' AS "detail", MA.MAX_DBOP_NAME AS "dbop_name", TO_CHAR(MA.MIN_FIRST_REFRESH_TIME, :B12 ) AS "dbop_exec_start", MA.MAX_DBOP_EXEC_ID AS "dbop_exec_id"), XMLFOREST( MA.MAX_STATUS AS "status", TO_CHAR(NVL(MA.MAX_SQL_EXEC_START, MA.MIN_FIRST_REFRESH_TIME), :B12 )AS "first_refresh_time", TO_CHAR(MA.MAX_LAST_REFRESH_TIME, :B12 )AS "last_refresh_time", MA.SUM_REFRESH_COUNT AS "refresh_count", MA.MAX_INST_ID AS "inst_id", MA.MAX_SESSION_ID A S "session_id", MA.MAX_SESSION_SERIAL AS "session_serial", MA.MAX_USERID AS "user_id", MA.MAX_USERNAME AS "user", MA.MAX_CON_ID AS "con_id", MA.MAX_CON_NAME AS "con_name", MA.MAX_MODULE AS "module", MA.MAX_ACTION AS "action", MA.MAX_SERVICE_NAME AS "service", MA.MAX_CLIENT_ID AS "client_id", MA.MAX_CLIENT_INFO AS "client_info", MA.MAX_PROGRAM AS "program", MA.MAX_PL_ENTRY_OID AS "plsql_entry_object_id", MA.MAX_PL_ENTRY_PROGID AS "plsql_entry_subprogram_id", MA.MAX_PL_OID AS "plsql_object_id", MA.MAX_PL_PROGID AS "plsql_subprogram_id", MA.MAX_PX_IS_CROSS_INSTANCE AS "is_cross_instance", MA.MAX_PX_DOP AS "dop", MA.MAX_PX_DOP_INSTANCES AS "instances"), CASE WHEN MA.MAX_ERROR_NUMBER IS NULL THEN NULL ELSE XMLELEMENT( "error", XMLATTRIBUTES(MA.MAX_ERROR_NUMBER AS "numbe r", MA.MAX_ERROR_FACILITY AS "facility"), MA.MAX_ERROR_MESSAGE) END, XMLELEMENT( "stats", XMLATTRIBUTES('monitor' AS "type"), XMLELEMENT( "stat", XMLATTRIBUTES('duration' AS "name"), GREATEST(ROUND((MA.MAX_LAST_REFRESH_TIME- NVL(MA.MAX_SQL_EXEC_START, MA.MIN_FIRST_REFRESH_TIME))*86400), LEAST(MA.SUM_ELAPSED_TIME/1000000, 1), CEIL(MA.MAX_QUEUING_TIME/1000000))), DECODE(MA.SUM_ELAPSED_TIME, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('elapsed_time' AS "name"), MA.SUM_ELAPSED_TIME)), DECODE(MA.MAX_QUEUING_TIME, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('queuing_time' AS "name"), MA.MAX_QUEUING_TIME)), DECODE(MA.SUM_CPU_TIME, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('cpu_time' AS "name"), MA.SUM_CPU_TIME)), DECODE(MA.SUM_USER_IO_WAIT_TIME, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('user_io_wait_time' AS "name"), MA.SUM_ USER_IO_WAIT_TIME)), DECODE(MA.SUM_APPLICATION_WAIT_TIME, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('application_wait_time' AS "name"), MA.SUM_APPLICATION_WAIT_TIME)), DECODE(MA.SUM_CONCURRENCY_WAIT_TIME, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('concurrency_wait_time' AS "name"), MA.SUM_CONCURRENCY_WAIT_TIME)), DECODE(MA.SUM_CLUSTER_WAIT_TIME, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('cluster_wait_time' AS "name"), MA.SUM_CLUSTER_WAIT_TIME)), DECODE(MA.SUM_PLSQL_EXEC_TIME, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('plsql_exec_time' AS "name"), MA.SUM_PLSQL_EXEC_TIME)), DECODE(MA.SUM_JAVA_EXEC_TIME, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('java_exec_time' AS "name"), MA.SUM_JAVA_EXEC_TIME)), DECODE(MA.SUM_OTHER_WAIT_TIME, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('other_wait_time' AS "name"), MA.SUM_OTHER_WAIT_TIME)), DEC ODE(MA.SUM_FETCHES, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('user_fetch_count' AS "name"), MA.SUM_FETCHES)), DECODE(MA.SUM_BUFFER_GETS, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('buffer_gets' AS "name"), MA.SUM_BUFFER_GETS)), DECODE(MA.SUM_READ_REQS, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('read_reqs' AS "name"), MA.SUM_READ_REQS)), DECODE(MA.SUM_READ_BYTES, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('read_bytes' AS "name"), MA.SUM_READ_BYTES)), DECODE(MA.SUM_WRITE_REQS, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('write_reqs' AS "name"), MA.SUM_WRITE_REQS)), DECODE(MA.SUM_WRITE_BYTES, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('write_bytes' AS "name"), MA.SUM_WRITE_BYTES)), CASE WHEN MA.SUM_IO_INTER_BYTES IS NULL OR MA.SUM_IO_INTER_BYTES = MA.SUM_IO_BYTES OR MA.SUM_IO_BYTES = 0 OR NVL(MA.SUM_IO_ELIG_BYTES, 0) = 0 THEN NULL E LSE XMLELEMENT( "stat", XMLATTRIBUTES('cell_offload_efficiency' AS "name"), ROUND(MA.SUM_IO_BYTES / DECODE(MA.SUM_IO_INTER_BYTES, 0, 1, MA.SUM_IO_INTER_BYTES), 2)) END, CASE WHEN NVL(MA.SUM_IO_ELIG_BYTES, 0) = 0 OR MA.SUM_IO_UNC_BYTES = MA.SUM_IO_RET_BYTES THEN NULL ELSE XMLELEMENT( "stat", XMLATTRIBUTES('cell_offload_efficiency2' AS "name"), GREATEST(0, ROUND(100 * (MA.SUM_IO_UNC_BYTES - MA.SUM_IO_RET_BYTES) / MA.SUM_IO_ELIG_BYTES, 2))) END) ) ELSE XMLELEMENT( "sql", XMLATTRIBUTES( MA.MAX_SQL_ID AS "sql_id", TO_CHAR(MA.MAX_SQL_EXEC_START, :B12 ) AS "sql_exec_start", MA.MAX_SQL_EXEC_ID AS "sql_exec_id"), XMLFOREST(MA.MAX_STATUS AS "status", SUBSTR(MA.SQLMON_TEXT, 1, 100) AS "sql_text", TO_CHAR(MA.MIN_FIRST_REFRESH_TIME, :B12 ) AS "first_refresh_time", TO_CHAR(MA.MAX_LAST_REFRESH_TIME, :B12 ) AS "last_refresh_time", MA.SUM_REFRESH_COUNT AS "refresh_count", MA.MAX_INST_ID AS "inst_id", MA.MAX_SESSION_ID AS "session_id", MA.MAX_SESSION_SERIAL AS "session_serial", MA.MAX_USERID AS "user_id", MA.MAX_USERNAME AS "user", MA.MAX_CON_ID AS "con_id", MA.MAX_CON_NAME AS "con_name", MA.MAX_MODULE AS "module", MA.MAX_ACTION AS "action", MA.MAX_SERVICE_NAME AS "service", MA.MAX_CLIENT_ID AS "client_id", MA.MAX_CLIENT_INFO AS "client_info", MA.MAX_PROGRAM AS "program", MA.MAX_PLAN_HASH_VALUE AS "plan_hash", MA.MAX_PL_ENTRY_OID AS "plsql_entry_object_id", MA.MAX_PL_ENTRY_PROGID AS "plsql_entry_subprogram_id", MA.MAX_PL_OID AS "plsql_object_id", MA.MAX_PL_PROGID AS "plsql_subprogram_id", MA.MAX_PX_IS_CROSS_INSTANCE AS "is_cross_instance", MA.MAX_PX_DOP AS "dop", MA.MAX_PX_DOP_INSTANCES AS "instances" , MA.SUM_SERVERS_REQUESTED AS "px_servers_requested", MA.SUM_SERVERS_ALLOCATED AS "px_servers_allocated"), XMLELEMENT( "stats", XMLATTRIBUTES('monitor' AS "type"), XMLELEMENT( "stat", XMLATTRIBUTES('duration' AS "name"), GREATEST(ROUND((MA.MAX_LAST_REFRESH_TIME- MA.MAX_SQL_EXEC_START) * 86400), LEAST(MA.SUM_ELAPSED_TIME/1000000, 1), CEIL(MA.MAX_QUEUING_TIME/1000000))), DECODE(MA.SUM_ELAPSED_TIME, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('elapsed_time' AS "name"), MA.SUM_ELAPSED_TIME)), DECODE(MA.MAX_QUEUING_TIME, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('queuing_time' AS "name"), MA.MAX_QUEUING_TIME)), DECODE(MA.SUM_CPU_TIME, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('cpu_time' AS "name"), MA.SUM_CPU_TIME)), DECODE(MA.SUM_USER_IO_WAIT_TIME, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('user_io_wait_time' AS "name "), MA.SUM_USER_IO_WAIT_TIME)), DECODE(MA.SUM_APPLICATION_WAIT_TIME, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('application_wait_time' AS "name"), MA.SUM_APPLICATION_WAIT_TIME)), DECODE(MA.SUM_CONCURRENCY_WAIT_TIME, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('concurrency_wait_time' AS "name"), MA.SUM_CONCURRENCY_WAIT_TIME)), DECODE(MA.SUM_CLUSTER_WAIT_TIME, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('cluster_wait_time' AS "name"), MA.SUM_CLUSTER_WAIT_TIME)), DECODE(MA.SUM_PLSQL_EXEC_TIME, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('plsql_exec_time' AS "name"), MA.SUM_PLSQL_EXEC_TIME)), DECODE(MA.SUM_JAVA_EXEC_TIME, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('java_exec_time' AS "name"), MA.SUM_JAVA_EXEC_TIME)), DECODE(MA.SUM_OTHER_WAIT_TIME, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('other_wait_time' AS "name"), MA.SUM_OTHER_ WAIT_TIME)), DECODE(MA.SUM_FETCHES, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('user_fetch_count' AS "name"), MA.SUM_FETCHES)), DECODE(MA.SUM_BUFFER_GETS, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('buffer_gets' AS "name"), MA.SUM_BUFFER_GETS)), DECODE(MA.SUM_READ_REQS, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('read_reqs' AS "name"), MA.SUM_READ_REQS)), DECODE(MA.SUM_READ_BYTES, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('read_bytes' AS "name"), MA.SUM_READ_BYTES)), DECODE(MA.SUM_WRITE_REQS, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('write_reqs' AS "name"), MA.SUM_WRITE_REQS)), DECODE(MA.SUM_WRITE_BYTES, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('write_bytes' AS "name"), MA.SUM_WRITE_BYTES)), DECODE(MA.SUM_IO_UNC_BYTES, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('unc_bytes' AS "name"), MA.SUM_IO_UNC_BYT ES)), DECODE(MA.SUM_IO_ELIG_BYTES, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('elig_bytes' AS "name"), MA.SUM_IO_ELIG_BYTES)), DECODE(MA.SUM_IO_RET_BYTES, 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('ret_bytes' AS "name"), MA.SUM_IO_RET_BYTES)), CASE WHEN MA.SUM_IO_INTER_BYTES IS NULL OR MA.SUM_IO_INTER_BYTES = MA.SUM_IO_BYTES OR MA.SUM_IO_BYTES = 0 OR NVL(MA.SUM_IO_ELIG_BYTES, 0) = 0 THEN NULL ELSE XMLELEMENT( "stat", XMLATTRIBUTES('cell_offload_efficiency' AS "name"), ROUND(MA.SUM_IO_BYTES / DECODE(MA.SUM_IO_INTER_BYTES, 0, 1, MA.SUM_IO_INTER_BYTES), 2)) END, CASE WHEN NVL(MA.SUM_IO_ELIG_BYTES, 0) = 0 OR MA.SUM_IO_UNC_BYTES = MA.SUM_IO_RET_BYTES THEN NULL ELSE XMLELEMENT( "stat", XMLATTRIBUTES('cell_offload_efficiency2' AS "name"), GREATEST(0, ROUND(100 * (MA.SUM_IO_UNC_BYTES - MA.SUM_IO_RET_BYTES) / MA.SUM_IO_ELIG_BYTES, 2))) END) ) END) FROM MONITOR_AGG MA) ELSE NULL END) FROM ( SELECT CASE WHEN V1.XPLAN_XML IS NULL OR V1.XPLAN_XML.EXISTSNODE('/error') > 0 THEN NULL ELSE V1.XPLAN_XML END XPLAN_XML FROM (SELECT CASE WHEN :B36 = 1 THEN DBMS_XPLAN.BUILD_PLAN_XML( TABLE_NAME=>'gv$sql_plan', PLAN_TAG=>'plan', FILTER_PREDS=>:B35 , FORMAT=>'-PROJECTION +ALIAS +ADAPTIVE') ELSE NULL END XPLAN_XML FROM DUAL) V1) CONST_VIEW
22356bkgsdcnhSELECT COUNT(*) FROM X$KSPPI A, X$KSPPCV2 B WHERE A.INDX + 1 = KSPFTCTXPN AND KSPPINM = '_deferred_log_dest_is_valid' AND UPPER(KSPFTCTXVL)='TRUE'
28bgqbzpa87xf declare policy varchar2(512); alldest number := dbms_rcvman.FALSE#; rlgscn number; begin if (:alldest > 0) then alldest := dbms_rcvman.TRUE#; end if; dbms_rcvman.setRecoveryDestFile(TRUE); dbms_rcvman.getRedoLogDeletionPolicy(policy => policy); dbms_rcvman.setRedoLogDeletionPolicy(policy => policy, alldest => alldest); if ((policy like '%APPLIED%' OR policy like '%SHIPPED%') AND policy not like '%BACKED UP%') then dbms_rcvman.getSCNForAppliedPolicy( minscn => :applied_scn , rlgscn => rlgscn); else :applied_scn := 0; end if; end;
2qdkzww4mdpvxinsert into wrh$_parameter (dbid, per_pdb, con_dbid, snap_id, instance_number, parameter_hash, value, isdefault, ismodified) select :dbid, sp.con_id, con_id_to_dbid(sp.con_id) con_dbid, :snap_id, :instance_number, hash, substr(value, 1, 512), isdefault, case when ismodified <> 'FALSE' or isadjusted <> 'FALSE' then 'MODIFIED' else 'FALSE' end from v$system_parameter sp
3cjmgxvhv3n2p DECLARE TYPE ID_t IS TABLE OF deluding_tab.id%TYPE INDEX BY PLS_INTEGER; TYPE FOO_t IS TABLE OF deluding_tab.foo%TYPE INDEX BY PLS_INTEGER; TYPE BAR_t IS TABLE OF deluding_tab.bar%TYPE INDEX BY PLS_INTEGER; TYPE FOOBAR_t IS TABLE OF deluding_tab.foobar%TYPE INDEX BY PLS_INTEGER; TYPE TYPECODE_t IS TABLE OF deluding_tab.type_code%TYPE INDEX BY PLS_INTEGER; TYPE VALBEGIN_DATE_t IS TABLE OF deluding_tab.validated_begin_date%TYPE INDEX BY PLS_INTEGER; TYPE VALEND_DATE_t IS TABLE OF deluding_tab.validated_end_date%TYPE INDEX BY PLS_INTEGER; IDs ID_t; FOOs FOO_t; BARs BAR_t; FOOBARs FOOBAR_t; TYPECODEs TYPECODE_t; VALBEGIN_DATEs VALBEGIN_DATE_t; VALEND_DATEs VALEND_DATE_t; k PLS_INTEGER := 1; BEGIN FOR i IN 1..8000000 LOOP IDs(k) := TO_CHAR(i, 'FM0000000000000000'); FOOs(k) := 'FOO-'||IDs(k); BARs(k) := RPAD('BAR-'||IDs(k), 320, '-'); FOOBARs(k) := RPAD('FOOBAR-'||IDs(k), 500, '*'); TYPEC ODEs(k) := TO_CHAR(MOD(i, 3), 'FM000'); VALBEGIN_DATEs(k) := TO_DATE('20180101', 'yyyymmdd'); --??????????????????????????? VALEND_DATEs(k) := TO_DATE('20180630', 'yyyymmdd'); --??????????????????????????? k := k + 1; IF MOD(i, 1000) = 0 THEN FOR l in 1..1000 LOOP INSERT INTO deluding_tab VALUES( IDs(l) , FOOs(l) , BARs(l) , FOOBARs(l) , TYPECODEs(l) , VALBEGIN_DATEs(l) , VALEND_DATEs(l) ); END LOOP; COMMIT; k := 1; END IF; END LOOP; END;
3dbzmtf9ahvzt merge /* KSXM:OPTIM_DML_INF */ into sys.mon_mods_all$ m using dual on (m.obj# = :objn and (:pobjn = 0 or m.obj# = :pobjn or m.obj# in (select obj# from tabpart$ where bo# = :pobjn and obj# = :objn union all select obj# from tabcompart$ where bo# = :pobjn and obj# = :objn union all select ts p.obj# from tabsubpart$ tsp, tabcompart$ tcp where tsp.pobj# = tcp.obj# and (tcp.bo# = :pobjn or tcp.obj# = :pobjn) and tsp.obj# = :objn))) when matched then update set inserts = decode(:reset, 1, 0, inserts) + :ins, updates = decode(:reset, 1, 0, updates) + :upd, deletes = decode(:reset, 1, 0, deletes) + :del, flags = decode(:reset, 1, 0, flags - bitand(flags, :flag)) + :flag, drop_segments = decode(:reset, 1, 0, drop_segments) + :dropseg, timestamp = :time when NOT matched then insert (obj#, inserts, updates, deletes, timestamp, flags, drop_segments) values (:objn, :ins, :upd, :del, :time, :flag, :dropseg) where :ins != 0 or :upd != 0 or :del != 0 or :flag != 0 or :dropseg !=0
3y6pgnk2ubw7ginsert into wrm$_snapshot_details (snap_id, dbid, instance_number, table_id, begin_time, end_time) values (:snap_id, :dbid, :instance_number, :bind1, :bind2, :bind3)
3zmzdajzvjkfwselect /*+ no_monitor no_statement_queuing */ job, nvl2(last_date, 1, 0), con_id from sys.job$_cdb where (((:1 <= next_date) and (next_date <= :2)) or ((last_date is null) and (next_date < :3))) and (field1 = :4 or (field1 = 0 and 'Y' = :5)) and (this_date is null) and ((dbms_logstdby.db_is_logstdby = 0 and job < 1000000000) or (dbms_logstdby.db_is_logstdby = 1 and job >= 1000000000)) order by next_date, job
49s332uhbnsma declare vsn varchar2(20); begin vsn := dbms_rcvman.getPackageVersion; :pkg_vsn:pkg_vsn_i := vsn; if vsn is not null then :pkg_vsnub4 := to_number(substr(vsn, 1, 2) || substr(vsn, 4, 2) || substr(vsn, 7, 2) || nvl(substr(vsn, 10, 2), '00')); end if; end;
4dy540fw5qm7sSELECT COUNT(*) FROM (SELECT FILETYPE_ID, FILETYPE_NAME FROM V$IOSTAT_FILE NT MINUS SELECT FILETYPE_ID, FILETYPE_NAME FROM WRH$_IOSTAT_FILETYPE_NAME WHERE dbid = :dbid)
6ajkhukk78nsrbegin prvt_hdm.auto_execute( :dbid, :inst_num , :end_snap_id ); end;
6n2qqv1brfhppselect ind.type#, ind.property, base_obj.obj#, base_owner.name, base_obj.name from sys.ind$ ind, sys.user$ base_owner, sys.obj$ base_obj where ind.obj# = :oid and ind.dataobj# = :doid and ind.bo# = base_obj.obj# and base_obj.owner# = base_owner.user#
6nas5twtqzkk0select /*+ no_monitor no_statement_queuing */ count(*) from sys.job$_cdb where next_date < :1 and (field1 = :2 or (field1 = 0 and 'Y' = :3)) and ((dbms_logstdby.db_is_logstdby = 0 and job < 1000000000) or (dbms_logstdby.db_is_logstdby = 1 and job >= 1000000000))
6xa9pr89f6u0finsert into wrh$_latch (dbid, per_pdb, con_dbid, snap_id, instance_number, latch_hash, level#, gets, misses, sleeps, immediate_gets, immediate_misses, spin_gets, sleep1, sleep2, sleep3, sleep4, wait_time) select :dbid, ltch.con_id, con_id_to_dbid(ltch.con_id) con_dbid, :snap_id, :instance_number, hash, level#, gets, misses, sleeps, immediate_gets, immediate_misses, spin_gets, sleep1, sleep2, sleep3, sleep4, wait_time FROM v$latch ltch WHERE (1 = 1)
7ugy3sxj9aw5tinsert into WRH$_SYSSTAT (dbid, per_pdb, con_dbid, snap_id, instance_number, stat_id, value) select /*+ use_hash(v$sysstat) */ :dbid, sysstat.con_id, con_id_to_dbid(sysstat.con_id) con_dbid, :snap_id, :instance_number, stat_id, value FROM v$sysstat sysstat WHERE (1 = 1)
87gaftwrm2h68select o.owner#, o.name, o.namespace, o.remoteowner, o.linkname, o.subname from obj$ o where o.obj#=:1
8kpwagy566cc6INSERT INTO DELUDING_TAB VALUES( :B1 , :B2 , :B3 , :B4 , :B5 , :B6 , :B7 )
akhh0ydabdahfBEGIN :snap# := dbms_workload_repository.create_snapshot; END;
b13g21mgg8y98 insert /* KSXM:TAKE_SNPSHOT */ into sys.optstat_snapshot$ (obj#, inserts, updates, deletes, timestamp, flags) (select m.obj#, m.inserts, m.updates, m.deletes, systimestamp, dbms_stats_advisor.compute_volatile_flag( m.obj#, m.flags, :flags, m.inserts, m.updates, m.deletes, s.inserts, s.updates, s.deletes, null, nvl(to_number(p.valchar), :global_stale_pcnt), s.gather) flags from sys.mon_mods_all$ m, (select si.obj#, max(si.inserts) inserts, max(si.updates) updates, max(si.deletes) deletes, decode(bitand(max(si.flags), :gather_flag), 0, 'NO_GATHER', 'GATHER') gather, max(si.timestamp) timestamp from sys.optstat_snapshot$ si, (select obj#, max(timestamp) ts from sys.optstat_snapshot$ group by obj#) sm where si.obj# = sm.obj# and si.timestamp = sm.ts group by si.obj#) s, sys.optstat_user_prefs$ p where m.obj# = s.obj#(+) and m.obj# = p.obj#(+) and pname(+) = 'STALE_PERCENT' and dbms_stats_advisor.check_ mmon_policy_violation(rownum, 6, 2) = 0)
f3yqgm464ygssSELECT /*+ NO_STATEMENT_QUEUING RESULT_CACHE (SYSOBJ=FALSE) */ JOB, LAST_DATE, THIS_DATE, NEXT_DATE, FIELD1 FROM "SYS"."JOB$_REDUCED" "JOB$_REDUCED" WHERE 1=1
fhf8upax5cxszBEGIN sys.dbms_auto_report_internal.i_save_report (:rep_ref, :snap_id, :pr_class, :rep_id, :suc); END;
fjfm1sbw3k2y6SELECT /*+ NO_STATEMENT_QUEUING RESULT_CACHE (SYSOBJ=FALSE) */ JOB, NEXT_DATE, FIELD1 FROM "SYS"."JOB$_REDUCED" "JOB$_REDUCED" WHERE 1=1
g6px76dmjv1jyselect count(*) from wri$_optstat_opr o, wri$_optstat_opr_tasks t where o.id = t.op_id(+) and o.operation = 'gather_database_stats (auto)' and (not regexp_like( extract(xmltype('<notes>' || o.notes || '</notes>'), '//error'), '^<error>ORA-200[0-9][0-9]') or not regexp_like( extract(xmltype('<notes>' || t.notes || '</notes>'), '//error'), '^<error>ORA-200[0-9][0-9]'))
gjaap3w3qbf8cselect count(*) from ilmobj$ where rownum = 1

Back to SQL Statistics
Back to Top

Instance Activity Statistics

Back to Top

Key Instance Activity Stats

StatisticTotalper Secondper Trans
db block changes50,312,54347,080.566,860.18
execute count7,313,6096,843.80997.22
logons cumulative3660.340.05
opened cursors cumulative7,320,5226,850.27998.16
parse count (total)1,9691.840.27
parse time elapsed200.020.00
physical reads1530.140.02
physical writes1,386,1111,297.07189.00
redo size14,402,088,89213,476,924.951,963,742.69
session cursor cache hits7,319,5856,849.39998.03
session logical reads60,698,83856,799.668,276.36
user calls1,5191.420.21
user commits7,3346.861.00
user rollbacks00.000.00
workarea executions - optimal3530.330.05


Back to Instance Activity Statistics
Back to Top

Instance Activity Stats

StatisticTotalper Secondper Trans
ASSM bg: segment fix monitor10.000.00
ASSM cbk:blocks examined1,077,7001,008.47146.95
ASSM cbk:blocks marked full81,83276.5811.16
ASSM gsp:L1 bitmaps examined1,064,671996.28145.17
ASSM gsp:L2 bitmap full7800.730.11
ASSM gsp:L2 bitmaps examined114,166106.8315.57
ASSM gsp:Optimized reject DB470.040.01
ASSM gsp:Optimized reject l110.000.00
ASSM gsp:Search all5300.500.07
ASSM gsp:Search hint1,4181.330.19
ASSM gsp:Search steal5300.500.07
ASSM gsp:bump HWM5300.500.07
ASSM gsp:get free block995,761931.80135.77
ASSM gsp:get free data block2190.200.03
ASSM gsp:get free index block690.060.01
ASSM gsp:good hint884,453827.64120.60
ASSM gsp:reject L110.000.00
ASSM gsp:reject db1170.110.02
ASSM rsv:alloc from reserve00.000.00
ASSM rsv:alloc from reserve succ00.000.00
ASSM rsv:clear reserve00.000.00
ASSM rsv:fill reserve220.020.00
ASSM wasted db state change20.000.00
Batched IO (bound) vector count00.000.00
Batched IO block miss count00.000.00
Batched IO buffer defrag count00.000.00
Batched IO double miss count00.000.00
Batched IO same unit count00.000.00
Batched IO single block count00.000.00
Batched IO vector block count00.000.00
Batched IO vector read count00.000.00
Block Cleanout Optim referenced860.080.01
CCursor + sql area evicted20.000.00
CPU used by this session106,71199.8614.55
CPU used when call started1600.150.02
CR blocks created360.030.00
DBWR checkpoint buffers written1,386,1111,297.07189.00
DBWR checkpoints770.070.01
DBWR object drop buffers written00.000.00
DBWR revisited being-written buffer00.000.00
DBWR thread checkpoint buffers written1,386,1111,297.07189.00
DBWR transaction table writes4300.400.06
DBWR undo block writes408,970382.7055.76
DFO trees parallelized820.080.01
HSC Heap Segment Block Changes7,310,2656,840.67996.76
Heap Segment Array Inserts1530.140.02
Heap Segment Array Updates70.010.00
Heatmap BlkLevel Flushed00.000.00
Heatmap BlkLevel Flushed to BF00.000.00
Heatmap BlkLevel Ranges Flushed00.000.00
Heatmap Blklevel Flush Task Count20.000.00
IMU CR rollbacks00.000.00
IMU Flushes6,9376.490.95
IMU Redo allocation size18,62017.422.54
IMU commits210.020.00
IMU ktichg flush6,9336.490.95
IMU pool not allocated7130.670.10
IMU recursive-transaction flush10.000.00
IMU undo allocation size905,944847.75123.53
IMU- failed to get a private strand7130.670.10
KTFB alloc req6680.630.09
KTFB alloc space (block)9,486,794,7528,877,380.351,293,536.24
KTFB alloc time (ms)38,55736.085.26
KTFB apply req3070.290.04
KTFB apply time (ms)7,6627.171.04
KTFB commit req00.000.00
KTFB commit time (ms)00.000.00
KTFB free req1640.150.02
KTFB free space (block)140,968131.9119.22
KTFB free time (ms)2620.250.04
LOB table id lookup cache misses00.000.00
PX local messages recv'd2,5942.430.35
PX local messages sent2,5942.430.35
Parallel operations not downgraded820.080.01
Requests to/from client130.010.00
SMON posted for undo segment shrink150.010.00
SQL*Net roundtrips to/from client140.010.00
TBS Extension: bytes extended00.000.00
TBS Extension: files extended00.000.00
TBS Extension: tasks created00.000.00
TBS Extension: tasks executed00.000.00
active txn count during cleanout945,565884.82128.93
auto extends on undo tablespace00.000.00
background checkpoints completed770.070.01
background checkpoints started770.070.01
background timeouts11,71110.961.60
blocks cleaned out using minact4330.410.06
branch node splits3440.320.05
buffer is not pinned count5,3515.010.73
buffer is pinned count17,70216.562.41
bytes received via SQL*Net from client6,3265.920.86
bytes sent via SQL*Net to client5,8445.470.80
calls to get snapshot scn: kcmgss93,23387.2412.71
calls to kcmgas98,99992.6413.50
calls to kcmgcs2,159,5592,020.83294.46
cell physical IO interconnect bytes26,399,578,62424,703,717.803,599,615.30
change write time13,15212.311.79
cleanout - number of ktugct calls988,781925.26134.82
cleanouts and rollbacks - consistent read gets00.000.00
cleanouts only - consistent read gets20.000.00
cluster key scan block gets710.070.01
cluster key scans710.070.01
commit batch/immediate performed470.040.01
commit batch/immediate requested470.040.01
commit cleanout failures: block lost10.000.00
commit cleanout failures: buffer being written10.000.00
commit cleanout failures: callback failure19,11117.882.61
commit cleanout failures: cannot pin00.000.00
commit cleanouts1,228,0041,149.12167.44
commit cleanouts successfully completed1,208,8911,131.23164.83
commit immediate performed470.040.01
commit immediate requested470.040.01
commit txn count during cleanout123,201115.2916.80
consistent changes1170.110.02
consistent gets1,135,7831,062.82154.87
consistent gets examination991,941928.22135.25
consistent gets examination (fastpath)991,837928.12135.24
consistent gets from cache1,135,7831,062.82154.87
consistent gets pin143,842134.6019.61
consistent gets pin (fastpath)143,741134.5119.60
cursor authentications00.000.00
data blocks consistent reads - undo records applied190.020.00
db block changes50,312,54347,080.566,860.18
db block gets59,563,16655,736.948,121.51
db block gets from cache59,563,16755,736.948,121.51
db block gets from cache (fastpath)43,531,47440,735.095,935.57
deferred (CURRENT) block cleanout applications85,17779.7111.61
enqueue conversions7,3876.911.01
enqueue releases393,834368.5353.70
enqueue requests393,849368.5553.70
enqueue timeouts120.010.00
enqueue waits430.040.01
execute count7,313,6096,843.80997.22
failed probes on index block reclamation00.000.00
file io service time00.000.00
free buffer inspected596,600558.2881.35
free buffer requested1,184,9811,108.86161.57
global undo segment hints helped00.000.00
global undo segment hints were stale00.000.00
heap block compress140.010.00
hot buffers moved to head of LRU90,38184.5812.32
immediate (CR) block cleanout applications20.000.00
immediate (CURRENT) block cleanout applications211,518197.9328.84
index crx upgrade (positioned)00.000.00
index fast full scans (full)00.000.00
index fetch by key1,0290.960.14
index reclamation/extension switch30.000.00
index scans kdiixs13,3123.100.45
leaf node 90-10 splits39,66737.125.41
leaf node splits81,43676.2011.10
lob reads80.010.00
lob writes160.010.00
lob writes unaligned160.010.00
logical read bytes from cache497,245,888,512465,303,718.8267,800,093.88
logons cumulative3660.340.05
max cf enq hold time00.000.00
messages received10,2269.571.39
messages sent10,2269.571.39
no work - consistent read gets4,8384.530.66
non-idle wait count26,71625.003.64
opened cursors cumulative7,320,5226,850.27998.16
parse count (describe)00.000.00
parse count (failures)00.000.00
parse count (hard)40.000.00
parse count (total)1,9691.840.27
parse time cpu210.020.00
parse time elapsed200.020.00
physical read IO requests1410.130.02
physical read bytes1,253,3761,172.86170.90
physical read total IO requests6,3835.970.87
physical read total bytes95,302,65689,180.5912,994.64
physical read total multi block requests00.000.00
physical reads1530.140.02
physical reads cache1530.140.02
physical reads cache prefetch120.010.00
physical write IO requests1,035,409968.90141.18
physical write bytes11,355,021,31210,625,595.441,548,271.25
physical write total IO requests1,057,916989.96144.25
physical write total bytes26,304,275,96824,614,537.223,586,620.67
physical write total multi block requests37,76635.345.15
physical writes1,386,1111,297.07189.00
physical writes direct00.000.00
physical writes direct (lob)00.000.00
physical writes from cache1,386,1111,297.07189.00
physical writes non checkpoint1,160,1071,085.58158.18
prefetched blocks aged out before use00.000.00
process last non-idle time1,0691.000.15
queries parallelized00.000.00
recursive calls7,347,1926,875.221,001.80
recursive cpu usage76,53171.6110.44
redo blocks checksummed by FG (exclusive)12,222,15111,437.021,666.51
redo blocks written29,066,52127,199.343,963.26
redo blocks written (group 0)28,878,66327,023.553,937.64
redo blocks written (group 1)46,64343.656.36
redo buffer allocation retries1770.170.02
redo entries27,418,85425,657.523,738.59
redo log space requests2780.260.04
redo ordering marks00.000.00
redo size14,402,088,89213,476,924.951,963,742.69
redo subscn max counts200.020.00
redo synch long waits00.000.00
redo synch time250.020.00
redo synch time (usec)249,586233.5534.03
redo synch time overhead (usec)2,407,455,1912,252,804.66328,259.50
redo synch time overhead count ( 2ms)220.020.00
redo synch time overhead count ( 8ms)00.000.00
redo synch time overhead count ( 32ms)00.000.00
redo synch time overhead count (128ms)00.000.00
redo synch time overhead count (inf)50.000.00
redo synch writes330.030.00
redo wastage2,251,2042,106.59306.95
redo write active strands9,1018.521.24
redo write finish time211,220,467197,652.0528,800.17
redo write gather time4,019,6653,761.45548.09
redo write info find330.030.00
redo write info find fail60.010.00
redo write schedule time4,088,0503,825.44557.41
redo write size count ( 4KB)2320.220.03
redo write size count ( 8KB)670.060.01
redo write size count ( 16KB)1020.100.01
redo write size count ( 32KB)1050.100.01
redo write size count ( 128KB)1610.150.02
redo write size count ( 256KB)560.050.01
redo write size count ( 512KB)1000.090.01
redo write size count (1024KB)6480.610.09
redo write size count (inf)7,3156.851.00
redo write time21,14619.792.88
redo write time (usec)211,461,258197,877.3728,833.00
redo write total time219,366,702205,274.9829,910.92
redo write worker delay (usec)2,915,8932,728.58397.59
redo write worker delay count8,7128.151.19
redo writes8,7868.221.20
redo writes (group 0)8,0977.581.10
redo writes (group 1)6150.580.08
redo writes adaptive all8,7868.221.20
redo writes adaptive worker8,7128.151.19
rollback changes - undo records applied640.060.01
rollbacks only - consistent read gets140.010.00
root node splits30.000.00
rows fetched via callback5160.480.07
securefile allocation bytes00.000.00
securefile allocation chunks00.000.00
securefile bytes non-transformed12,18311.401.66
securefile direct write bytes00.000.00
securefile direct write ops00.000.00
securefile number of non-transformed flushes50.000.00
segment cfs allocations00.000.00
segment chunks allocation from dispenser00.000.00
segment dispenser allocations00.000.00
segment dispenser load empty00.000.00
segment dispenser load tasks00.000.00
segment prealloc bytes00.000.00
segment prealloc ops00.000.00
segment prealloc tasks00.000.00
segment prealloc time (ms)00.000.00
segment total chunk allocation00.000.00
session connect time40.000.00
session cursor cache hits7,319,5856,849.39998.03
session logical reads60,698,83856,799.668,276.36
shared hash latch upgrades - no wait1270.120.02
shared io pool buffer get success00.000.00
sorts (memory)4460.420.06
sorts (rows)32,20430.144.39
sql area evicted10.000.00
sql area purged10.000.00
switch current caused by our pin430.040.01
switch current to new buffer430.040.01
table fetch by rowid8,1447.621.11
table fetch continued row00.000.00
table scan blocks gotten2,0881.950.28
table scan disk non-IMC rows gotten87,42381.8111.92
table scan rows gotten87,42381.8111.92
table scans (short tables)4390.410.06
total cf enq hold time7,9807.471.09
total number of cf enq holders3930.370.05
total number of times SMON posted150.010.00
transaction rollbacks470.040.01
transaction tables consistent read rollbacks00.000.00
transaction tables consistent reads - undo records applied00.000.00
undo change vector size2,679,953,3322,507,798.01365,414.96
user calls1,5191.420.21
user commits7,3346.861.00
user logons cumulative10.000.00
user logouts cumulative10.000.00
user rollbacks00.000.00
workarea executions - optimal3530.330.05
write clones created in background10.000.00
write clones created in foreground2190.200.03


Back to Instance Activity Statistics
Back to Top

Instance Activity Stats - Absolute Values

StatisticBegin ValueEnd Value
logons current5046
opened cursors current3330
session cursor cache count16,85417,894
session pga memory142,125,312114,821,984
session pga memory max228,929,088217,223,328
session uga memory645,411,712689,895,640
session uga memory max3,176,812,7443,427,588,088


Back to Instance Activity Statistics
Back to Top

Instance Activity Stats - Thread Activity

StatisticTotalper Hour
log switches (derived)77259.39


Back to Instance Activity Statistics
Back to Top

IO Stats

Back to Top

IOStat by Function summary

Function NameReads: DataReqs per secData per secWrites: DataReqs per secData per secWaits: CountAvg Time
LGWR29M1.82.027M13.9G18.3613.31M11.7K29.81us
DBWR0M0.000M10.6G968.9010.133M914180.52us
Others61M4.02.057M40M2.72.037M6094244.41us
Buffer Cache Reads2M0.17.002M0M0.000M180518.78us
TOTAL:92M6.01.086M24.5G989.9723.481M18.9K111.16us


Back to IO Stats
Back to Top

IOStat by Filetype summary

Filetype NameReads: DataReqs per secData per secWrites: DataReqs per secData per secSmall ReadLarge Read
Log File1M0.14.001M13.9G16.9213.287M.00ns 
Data File6M0.83.006M10.6G969.5410.137M89.27us 
Control File84M5.04.079M58M3.51.054M185.77ns 
TOTAL:91M6.01.085M24.5G989.9723.478M12.46us 


Back to IO Stats
Back to Top

IOStat by Function/Filetype summary

Function/File NameReads: DataReqs per secData per secWrites: DataReqs per secData per secWaits: CountAvg Time
LGWR 29M1.82.027M13.9G18.3613.31M210072.92us
LGWR (Log File) 1M0.14.001M13.9G16.9213.287M308444.81us
LGWR (Control File) 28M1.68.026M25M1.44.023M17929.00us
DBWR (Data File) 0M0.000M10.6G968.9010.133M0 
DBWR 0M0.000M10.6G968.9010.133M0 
Others 61M4.02.057M40M2.72.037M4989298.53us
Others (Control File) 56M3.36.052M35M2.07.033M359111.10us
Others (Data File) 5M0.66.005M5M0.65.005M13981.04ms
Buffer Cache Reads (Data File) 2M0.17.002M0M0.000M179504.73us
Buffer Cache Reads 2M0.17.002M0M0.000M179504.73us
TOTAL: 92M6.01.086M24.5G989.9723.481M7268238.42us


Back to IO Stats
Back to Top

Tablespace IO Stats

No data exists for this section of the report.

Back to IO Stats
Back to Top

File IO Stats

No data exists for this section of the report.

Back to IO Stats
Back to Top

Buffer Pool Statistics

Back to Top

Buffer Pool Statistics

PNumber of BuffersPool Hit%Buffer GetsPhysical ReadsPhysical WritesFree Buff WaitWrit Comp WaitBuffer Busy Waits
D909,67210060,696,4291501,386,111003


Back to Buffer Pool Statistics
Back to Top

Checkpoint Activity

MTTR WritesLog Size WritesLog Ckpt WritesOther Settings WritesAutotune Ckpt WritesThread Ckpt Writes
01,365,64000020,471


Back to Buffer Pool Statistics
Back to Top

Advisory Statistics

Back to Top

Instance Recovery Stats

Targt MTTR (s) Estd MTTR (s)Recovery Estd IOsActual RedoBlksTarget RedoBlksLog Sz RedoBlksLog Ckpt Timeout RedoBlksLog Ckpt Interval RedoBlksOpt Log Sz(M)Estd RAC Avail Time
B04565514441706635526635521556092   
E046322947224856635526635521914083   


Back to Advisory Statistics
Back to Top

MTTR Advisory

No data exists for this section of the report.

Back to Advisory Statistics
Back to Top

Buffer Pool Advisory

PSize for Est (M)Size FactorBuffers (thousands)Est Phys Read FactorEstimated Phys Reads (thousands)Est Phys Read TimeEst %DBtime for Rds
D7360.10901.0173129.00
D1,4720.201801.0173129.00
D2,2080.302711.0173129.00
D2,9440.403611.0173129.00
D3,6800.504511.0173129.00
D4,4160.595411.0173128.00
D5,1520.696311.0073128.00
D5,8880.797211.0072128.00
D6,6240.898121.0072128.00
D7,3600.999021.0072128.00
D7,4241.009101.0072128.00
D8,0961.099921.0072128.00
D8,8321.191,0821.0072128.00
D9,5681.291,1721.0072128.00
D10,3041.391,2630.9972127.00
D11,0401.491,3530.9972127.00
D11,7761.591,4430.9972127.00
D12,5121.691,5330.9972127.00
D13,2481.781,6230.9972127.00
D13,9841.881,7130.9972127.00
D14,7201.981,8040.9972127.00


Back to Advisory Statistics
Back to Top

PGA Aggr Summary

PGA Cache Hit %W/A MB ProcessedExtra W/A MB Read/Written
100.00620


Back to Advisory Statistics
Back to Top

PGA Aggr Target Stats

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 MemGlobal Mem Bound(K)
B3,0722,568340.300.000.000.000.00314,570
E3,0722,568331.840.000.000.000.00314,570


Back to Advisory Statistics
Back to Top

PGA Aggr Target Histogram

Low Optimal High OptimalTotal ExecsOptimal Execs1-Pass ExecsM-Pass Execs
2K4K28028000
64K128K9900
128K256K6600
256K512K4400
512K1024K262600
1M2M282800


Back to Advisory Statistics
Back to Top

PGA Memory Advisory

PGA Target Est (MB)Size FactrW/A MB ProcessedEstd Extra W/A MB Read/ Written to Disk Estd PGA Cache Hit %Estd PGA Overalloc CountEstd Time
3840.134,008.050.00100.0006,012
7680.254,008.050.00100.0006,012
1,5360.504,008.050.00100.0006,012
2,3040.754,008.050.00100.0006,012
3,0721.004,008.050.00100.0006,012
3,6861.204,008.050.00100.0006,012
4,3011.404,008.050.00100.0006,012
4,9151.604,008.050.00100.0006,012
5,5301.804,008.050.00100.0006,012
6,1442.004,008.050.00100.0006,012
9,2163.004,008.050.00100.0006,012
12,2884.004,008.050.00100.0006,012
18,4326.004,008.050.00100.0006,012
24,5768.004,008.050.00100.0006,012


Back to Advisory Statistics
Back to Top

Shared Pool Advisory

Shared Pool Size(M)SP Size FactrEst LC Size (M)Est LC Mem ObjEst LC Time Saved (s)Est LC Time Saved FactrEst LC Load Time (s)Est LC Load Time FactrEst LC Mem Obj Hits (K)
4800.231055,72046,5941.001315.0418,418
7040.3422711,76946,6071.001184.5418,420
9280.4522711,76946,6071.001184.5418,420
1,1520.5622711,76946,6071.001184.5418,420
1,3760.6722711,76946,6071.001184.5418,420
1,6000.7822711,76946,6071.001184.5418,420
1,7280.8422711,76946,6071.001184.5418,420
1,7600.8622711,76946,6071.001184.5418,420
1,7920.8822711,76946,6071.001184.5418,420
1,8240.8922711,76946,6071.001184.5418,420
1,8560.9122711,76946,6071.001184.5418,420
1,8880.9222711,76946,6071.001184.5418,420
1,9200.9422711,76946,6131.001124.3118,420
1,9520.9522711,76946,6321.00933.5818,421
1,9840.9722711,76946,6541.00712.7318,422
2,0160.9822711,76946,6751.00501.9218,423
2,0481.0022711,76946,6991.00261.0018,423
2,0801.0222711,76946,6991.00261.0018,423
2,1121.0322711,76946,6991.00261.0018,423
2,1441.0522711,76946,6991.00261.0018,423
2,1761.0622711,76946,6991.00261.0018,423
2,2081.0822711,76946,6991.00261.0018,423
2,2401.0922711,76946,6991.00261.0018,423
2,2721.1122711,76946,6991.00261.0018,423
2,3041.1322711,76946,6991.00261.0018,423
2,3361.1422711,76946,6991.00261.0018,423
2,4961.2222711,76946,6991.00261.0018,423
2,7201.3322711,76946,6991.00261.0018,423
2,9441.4422711,76946,6991.00261.0018,423
3,1681.5522711,76946,6991.00261.0018,423
3,3921.6622711,76946,6991.00261.0018,423
3,6161.7722711,76946,6991.00261.0018,423
3,8401.8822711,76946,6991.00261.0018,423
4,0641.9822711,76946,6991.00261.0018,423
4,2882.0922711,76946,6991.00261.0018,423


Back to Advisory Statistics
Back to Top

SGA Target Advisory

SGA Target Size (M)SGA Size FactorEst DB Time (s)Est Physical Reads
1,2800.138,69872,253
2,5600.258,87173,106
3,8400.388,77973,106
5,1200.508,77972,911
6,4000.638,77872,715
7,6800.758,77872,513
8,9600.888,77872,434
10,2401.008,77872,253
11,5201.138,77872,051
12,8001.258,77771,856
14,0801.388,77771,747
15,3601.508,77771,588
16,6401.638,77771,552
17,9201.758,77771,480
19,2001.888,77771,480
20,4802.008,77771,480


Back to Advisory Statistics
Back to Top

Streams Pool Advisory

No data exists for this section of the report.

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

Back to Top

Buffer Wait Statistics

ClassWaitsTotal Wait Time (s)Avg Time (ms)
undo header300


Back to Wait Statistics
Back to Top

Enqueue Activity

Enqueue Type (Request Reason)RequestsSucc GetsFailed GetsWaitsWt Time (s)Av Wt Time(ms)
CF-Controlfile Transaction 1,8351,834120019.35
BF-BLOOM FILTER (allocation contention) 665610800.38
PS-PX Process Reservation 1,0661,06601500.07
JG-Job Scheduler1 111,726111,726000 
JG-Job Scheduler1 (queue lock) 111,726111,726000 
TX-Transaction 89,70289,702000 
FB-Format Block 62,41962,419000 
JG-Job Scheduler1 (q mem clnup lck) 10,63810,638000 
TM-DML 7,8617,861000 
CR-Reuse Block Range (block range reuse ckpt) 984984000 
HW-Segment High Water Mark 757757000 
TT-Tablespace 726726000 
KA-Kernel Service ACL (ACL control status) 712712000 
SE-Session Migration 386386000 
PR-Process Startup 107107000 
AE-Edition Lock (lock) 9999000 
PV-KSV slave startup (syncstart) 9191000 
IS-Instance State 8181000 
RC-Result Cache: Enqueue (Result Cache: Contention) 6969000 
MR-Media Recovery 5858000 
US-Undo Segment 5656000 
SJ-KTSJ Slave Task Cancel (Slave Task Cancel) 4343000 
JD-Job Queue Date 3636000 
WF-AWR Flush 2322100 
WT-AWR CDB-Wide Table Lock 2121000 
TH-Threshold Chain (metric threshold evaluation) 1818000 
TO-Temp Object 88000 
AF-Advisor Framework (task serialization) 77000 
TA-Instance Undo 77000 
UL-User-defined 55000 
FE-KTFA Recovery 44000 
SH-Active Session History Flushing 44000 
TD-KTF map table enqueue (KTF dump entries) 44000 
TG-In Memory Temp Object (IMCDT global resource) 44000 
TI-In Memory Temp Object HT (IMCDT object HT) 44000 
CU-Cursor 33000 
DR-Distributed Recovery 22000 
FH-Flush Stat 22000 
RS-Reclaimable Space (read alert level) 22000 
MW-MWIN Schedule 11000 
PA-Privilege Capture API (read a privilege capture) 11000 


Back to Wait Statistics
Back to Top

Undo Statistics

Back to Top

Undo Segment Summary

Undo TS#Num Undo Blocks (K)Number of TransactionsMax Qry Len (s)Max Tx ConcurcyMin/Max TR (mins)STO/ OOS uS/uR/uU/ eS/eR/eU
50.19292933415/26.60/00/0/0/0/0/0


Back to Undo Statistics
Back to Top

Undo Segment Stats

End TimeNum Undo BlocksNumber of TransactionsMax Qry Len (s)Max Tx ConcyTun Ret (mins)STO/ OOS uS/uR/uU/ eS/eR/eU
23-Apr 01:2841002150/00/0/0/0/0/0
23-Apr 01:181892829334270/00/0/0/0/0/0


Back to Undo Statistics
Back to Top

Latch Statistics

Back to Top

Latch Activity

Latch NameGet RequestsPct Get MissAvg Slps /MissWait Time (s)NoWait RequestsPct NoWait Miss
AQ Background: interrupt command latch10.00 0380.00
AQ Coord jobx_kwsbgsgn latch360.00 00 
AQ Sharded master pool latch360.00 00 
AQ deq hash table latch10.00 00 
ASM db client latch9510.00 00 
ASM map operation hash table10.00 00 
ASM network state latch1160.00 00 
ASM remote client latch1160.00 00 
AWR Alerted Metric Element list8,4590.00 00 
Bloom filter list latch300.00 00 
Change Notification Hash table latch3560.00 00 
Column stats entry latch10.00 00 
Consistent RBA8,8640.010.0000 
DML lock allocation15,7250.00 00 
Event Group Locks4750.00 00 
File State Object Pool Parent Latch10.00 00 
GCS logfile block10.00 00 
GCS logfile write queue10.00 00 
Hang Manager wait event statistics690.00 00 
I/O Staticstics latch10.00 00 
ILM Stats Stripe Latch30.00 00 
ILM Stats main anchor latch57,7200.00 00 
ILM access tracking extent10.00 00 
ILM activity tracking latch30.00 00 
IM Global dictionary latch10.00 00 
IM Global dictionary queue latch10.00 00 
IM Global dictionary rw latch10.00 00 
IM area sb latch10.00 00 
IM area scb latch10.00 00 
IM emb latch10.00 00 
IM seg hdr latch10.00 00 
IMFS defer write list10.00 00 
IPC stats buffer allocation latch10.00 00 
In memory undo latch31,1500.00 012,6750.00
JS Sh mem access5,3260.00 00 
JS mem alloc latch370.00 00 
JS queue access latch380.00 00 
JS queue state obj latch223,4520.00 00 
JS slv state obj latch450.00 00 
KCNIBR - invalid block range tree10.00 00 
KDCStatHash latch10.00 00 
KDMADO action list latch10.00 00 
KDMADO bll latch10.00 00 
KDMADO latch10.00 00 
KFC FX Hash Latch10.00 00 
KFC Hash Latch10.00 00 
KFCL LE Freelist10.00 00 
KFIAS client latch10.00 00 
KFIAS cluster latch10.00 00 
KFIAS endpoint latch10.00 00 
KFIAS node latch10.00 00 
KFIAS process latch10.00 00 
KFIAS queue latch10.00 00 
KGNFS-NFS:SHM structure10.00 00 
KGNFS-NFS:SVR LIST10.00 00 
KJC message pool free list10.00 00 
KJC message pool pending avail list10.00 00 
KJCT flow control latch10.00 00 
KMG MMAN ready and startup request latch3570.00 00 
KSFS OFS ctx level parent latch10.00 00 
KSFS OFS req layer parent latch10.00 00 
KSFS OFS sess layer parent latch10.00 00 
KSFS Receive and Send Queue latch10.00 00 
KSFS id table parent latch10.00 00 
KSFS ksfs_node latch10.00 00 
KSFSD name cache parent latch10.00 00 
KSGL LS Segment latch10.00 00 
KSGL SGA anchor latch10.00 00 
KSIPC SGA allocation parent latch1170.00 00 
KSK PDB IO ON2140.00 00 
KSK PDB IO STAT3220.00 00 
KSRMA SGA anchor latch10.00 00 
KSRMF SGA anchor latch10.00 00 
KSXR hang analysis phase 1 latch10.00 00 
KSXR hang analysis phase 2 latch10.00 00 
KTF sga latch120.00 03600.00
KTILM latch10.00 00 
KTU in-memory txn table latch10.00 00 
Latch for NonceHistory10.00 00 
Locator state objects pool parent latch10.00 00 
Lsod array latch10.00 00 
MMON per PDB bit map latch20.00 00 
MQL Tracking Latch0  0400.00
Memory Management Latch0  03570.00
Memory Queue10.00 00 
Memory Queue Message Subscriber #110.00 00 
Memory Queue Message Subscriber #210.00 00 
Memory Queue Message Subscriber #310.00 00 
Memory Queue Message Subscriber #410.00 00 
Memory Queue Subscriber10.00 00 
Message cache latch10.00 00 
MinActiveScn Latch720.00 00 
Mutex10.00 00 
Mutex Stats10.00 00 
Nologging Standby SCN Time Map cache3560.00 00 
OS process1,3270.00 00 
OS process allocation2,7670.040.0000 
OS process: request allocation2170.00 00 
PDB Hash Table Latch6100.00 00 
PDB LRU structure10.00 00 
PL/SQL warning settings3780.00 00 
PX hash array latch10.00 00 
Parent latch for dependency tracking10.00 00 
Parent latch for query hash table access50.00 00 
Parent latch for segments scanning2290.00 00 
QMT10.00 00 
Real-time descriptor latch2,3280.00 00 
Report Request stats latch740.00 00 
Report Request struct latch40.00 00 
Request holder compeltion list latch2150.00 00 
Result Cache: RC Latch2130.00 00 
Result Cache: SO Latch720.00 00 
Retry Ht elm latch10.00 00 
Retry bkt latch10.00 00 
SGA Blackbox latch20.00 00 
SGA IO buffer pool latch9,0730.00 036,2270.00
SGA Logging Bkt Latch10.00 00 
SGA Logging Log Latch7,2010.00 00 
SGA blob parent10.00 00 
SGA bucket locks10.00 00 
SGA heap locks10.00 00 
SGA pool locks10.00 00 
SQL memory manager latch10.00 03560.00
SQL memory manager workarea list latch24,3680.010.0000 
SR Stats Stripe Latch10.00 00 
SR Stats main anchor latch1280.00 00 
Sched IM Job latch1170.00 00 
Sched InMem Job Cache20.00 00 
Shared B-Tree390.00 00 
Shared context latch230.00 00 
Shared context root latch80.00 00 
Streams Generic10.00 00 
Subscriber Ht elm latch10.00 00 
Testing10.00 00 
Token Manager10.00 00 
Txn Ht elm latch10.00 00 
Txn bkt latch10.00 00 
WCR: sync10.00 00 
Write State Object Pool Parent Latch10.00 00 
X$KSFQP10.00 00 
XDB NFS Security Latch10.00 00 
XDB unused session pool10.00 00 
XDB used session pool10.00 00 
active checkpoint queue latch1,7470.00 00 
active service list104,6790.020.1006190.00
archive destination360.00 00 
asr alloc latch40.00 00 
buffer pool10.00 00 
business card10.00 00 
cache buffer handles744,8860.00 00 
cache buffers chains203,264,3380.000.0001,184,8510.00
cache buffers lru chain1,385,7630.020.0102,653,8150.02
cache table scan latch30.00 030.00
call allocation1,2071.410.0600 
change notification client cache latch30.00 00 
channel handle pool latch4350.00 00 
channel operations parent latch1,8740.00 00 
checkpoint queue latch1,462,5690.000.0001,411,7680.00
client/application info1,8230.050.0000 
compile environment latch3670.00 00 
corrupted undo seg latch2390.00 00 
cp handoff latch10.00 00 
cp pool latch10.00 00 
cp server hash latch10.00 00 
cp sga latch1160.00 00 
cp srv type state latch1,0720.00 00 
cp srv type wait latch10.00 00 
cr slave free list10.00 00 
cvmap freelist lock10.00 00 
deferred cleanup latch1160.00 00 
dispatcher info180.00 00 
dml lock allocation1160.00 00 
done queue latch10.00 00 
dtp latch1090.00 00 
dummy allocation7370.540.0000 
eighth spare latch - X parent10.00 00 
eleventh spare latch - children10.00 00 
enqueue freelist latch10.00 0591,9640.00
enqueue hash chains905,8480.000.0000 
error message lists5782.600.0000 
fifteenth spare latch - children10.00 00 
file cache latch63,8160.00 00 
first Audit Vault latch10.00 00 
flash file access latch10.00 00 
flashback copy10.00 00 
fourteenth spare latch - children10.00 00 
fourth Audit Vault latch10.00 00 
gc element10.00 00 
gcs commit scn state10.00 00 
gcs nodemap pool10.00 00 
gcs opaque info freelist10.00 00 
gcs partitioned table hash10.00 00 
gcs pcm hashed value bucket hash10.00 00 
gcs resource freelist10.00 00 
gcs resource hash10.00 00 
gcs resource scan list10.00 00 
gcs shadows freelist10.00 00 
ges cached resource lists10.00 00 
ges domain table10.00 00 
ges enqueue table freelist10.00 00 
ges group table10.00 00 
ges process hash list10.00 00 
ges process parent latch10.00 00 
ges resource hash list10.00 00 
ges resource scan list10.00 00 
ges resource table freelist10.00 00 
ges timeout list10.00 00 
ges value block free list10.00 00 
global KZLD latch for auth type mem in SGA10.00 00 
global tx hash mapping10.00 00 
granule from data transfer cache10.00 00 
gws:Sharding global descriptor latch720.00 00 
hash table Sql Plan Finding latch140.00 00 
hash table column usage latch130.00 0170.00
hash table expression usage tracking latch10.00 00 
heartbeat check10.00 00 
imc buf hdl lat10.00 00 
imc cr clone buf10.00 00 
imc hash lat10.00 00 
imc sj lat10.00 00 
imc srchsp lat10.00 00 
imc-txn-wrk-lat10.00 00 
in-memory area latch10.00 00 
in-memory columnar segment hash table latch10.00 00 
in-memory columnar ts extent map chunk latch10.00 00 
in-memory global pool latch10.00 00 
interrupt manipulation1,0700.090.0000 
intra txn parallel recovery10.00 00 
io pool granule metadata list10.00 00 
job workq parent latch130.00 0120.00
job_queue_processes free list latch490.00 00 
job_queue_processes parameter latch30.00 00 
jslv pdb context latch240.00 00 
k2q lock allocation10.00 00 
kcb DW scan objtemp hash table latch10.00 00 
kcbtsemkid latch770.00 00 
kcn buffer chains10.00 00 
kdlx hb parent latch10.00 00 
kgb parent10.00 00 
kgnfs mount latch10.00 00 
kjci objects freelist latch10.00 00 
kjci process context latch30.00 02,7540.00
kjci process list latch10.00 00 
kjcipctx state object freelist latch10.00 00 
kjoedcso state object freelist latch1160.00 00 
kjoeq omni enqueue hash bucket latch10.00 00 
kjoer owner hash bucket10.00 00 
kjsca protect pkey hash table bucket10.00 00 
kjsca protect service hash table bucket10.00 00 
kokc descriptor allocation latch80.00 00 
krso process370.00 00 
ksevn object root latch3560.00 00 
ksfv messages10.00 00 
ksi resource reuse count10.00 00 
ksim group membership cache10.00 00 
kss move lock1830.00 00 
ksuosstats global area790.00 04080.00
ksv allocation latch4850.00 00 
ksv class latch3750.00 00 
ksv msg queue latch10.00 00 
ksz_so allocation latch2170.00 00 
ktfbn latch5130.00 00 
ktm adg jrnls10.00 00 
ktm adg lookuptable10.00 00 
ktm adg space10.00 00 
ktm adg stages10.00 00 
ktm global data1140.00 00 
ktm prv jrnls10.00 00 
ktmpj HT ls10.00 00 
kwqbsn:qsga380.00 00 
kwslbmdl: metadata latch360.00 00 
kwslbql: queue latch10.00 00 
kwsptQcachLt: queue cache latch10.00 00 
kwsptTrncTsksLt: trunc task latch10.00 00 
kwsptjobAdPtLt: AdPt list latch260.00 00 
kwsslLat: skiplist latch10.00 00 
kxfxscanrate_latch10.00 00 
lgwr LWN SCN8,8760.050.2500 
list of block allocation89,9360.00 00 
loader state object freelist40.00 00 
lob segment dispenser latch10.00 00 
lob segment hash table latch90.00 00 
lob segment query latch10.00 00 
lock DBA buffer during media recovery10.00 00 
log write info0  08,8620.00
log write worker phase9450.00 00 
logical standby cache10.00 00 
logminer context allocation10.00 00 
logminer local10.00 00 
logminer work area10.00 00 
longop free list parent10.00 00 
managed standby350.00 00 
message bitmap latch10.00 00 
message pool operations parent latch4930.00 00 
messages72,0300.020.1700 
msg queue latch10.00 00 
multiblock read objects60.00 00 
name-service namespace bucket10.00 00 
ncodef allocation latch1160.00 00 
ninth spare latch - X parent10.00 00 
object queue header freelist3240.00 00 
object queue header operation5,988,8740.000.0000 
object queue memory10.00 00 
object stats modification180.00 00 
parallel query alloc buffer2,9770.200.0001320.00
parallel query stats4890.410.0000 
parameter table management9600.00 00 
pdb enqueue hash chains10.00 00 
peshm10.00 00 
pesom_free_list10.00 00 
pesom_hash_node10.00 00 
pkey global statistics10.00 00 
pmon dead latch170.00 00 
post/wait queue3,6420.250.1103,6470.16
process allocation3250.00 00 
process group creation2170.00 00 
process pkey statistics list10.00 00 
process queue1,8310.00 00 
process queue reference145,0580.010.00011,9380.04
qmn task queue latch1550.00 00 
query server freelists1,9130.050.0000 
query server process9500.00 00 
queued dump request40.00 00 
queuing load statistics10.00 00 
recovery domain hash bucket10.00 00 
redo allocation72,6070.340.03027,416,6080.00
redo copy10.00 027,416,8320.00
redo gen encryption key structure770.00 00 
redo transport task20.00 00 
redo writing29,3700.00 00 
remote tool request latch3700.00 00 
resmgr group change latch3020.00 00 
resmgr:active threads7640.00 0180.00
resmgr:actses change group4010.00 00 
resmgr:actses change state250.00 00 
resmgr:free threads list7350.950.0000 
resmgr:plan CPU method10.00 00 
resmgr:plan change alloc latch10.00 00 
resmgr:plan change latch30.00 00 
resmgr:resource group CPU method10.00 00 
resmgr:schema config100.00 0180.00
resmgr:session queuing10.00 00 
row cache objects10.00 00 
second Audit Vault latch10.00 00 
sequence cache1300.00 00 
service drain list1,0680.00 00 
session allocation9,0280.00 08,4700.00
session idle bit19,6310.00 00 
session queue latch10.00 00 
session state list latch7910.630.0000 
session statistics7370.270.5000 
session switching1270.00 00 
session timer3570.00 00 
seventh spare latch - X parent10.00 00 
sga hash table parent latch10.00 00 
shard latch10.00 00 
shared pool7,368,5690.010.0100 
shared pool sim alloc10.00 00 
shared pool simulator80.00 00 
shared server configuration3570.00 00 
sim partition latch10.00 00 
simulator hash latch4,647,6900.000.0000 
simulator lru latch1,385,4270.030.0103,133,0290.01
sixth spare latch - X parent10.00 00 
sort extent pool2410.00 00 
space background state object latch170.00 00 
space background task latch47612.180.6707180.00
state object free list20.00 00 
statistics aggregation1740.00 00 
subscriber Ht bkt10.00 00 
tablespace key chain10.00 00 
temp lob duration state obj allocation230.00 00 
temporary table state object allocation80.00 00 
tenth spare latch - X parent10.00 00 
test excl. parent l010.00 00 
test excl. parent2 l010.00 00 
test excl. parent2 lmid cln10.00 00 
test mode exclusive10.00 00 
test open exclusive30.00 00 
test pdb exclusive30.00 00 
test shared parent2 lmid10.00 00 
thirteenth spare latch - children10.00 00 
threshold alerts latch430.00 00 
transaction allocation5700.00 00 
twelfth spare latch - children10.00 00 
twenty-fifth spare latch - S par10.00 00 
twenty-first spare latch - S par10.00 00 
twenty-fourth spare latch - S par10.00 00 
twenty-second spare latch - S par10.00 00 
twenty-third spare latch - S par10.00 00 
undo global data1,673,2810.00 00 
virtual circuit buffers10.00 00 
virtual circuit holder10.00 00 
virtual circuit queues10.00 00 


Back to Latch Statistics
Back to Top

Latch Sleep Breakdown

Latch NameGet RequestsMissesSleepsSpin GetsPDB Name
shared pool7,368,5699905985 CDB$ROOT
simulator lru latch1,385,4274026396 CDB$ROOT
cache buffers lru chain1,385,7633003297 CDB$ROOT
redo allocation72,6072448236 CDB$ROOT
object queue header operation5,988,8742341233 CDB$ROOT
space background task latch476583920 CDB$ROOT
active service list104,67920218 CDB$ROOT
call allocation1,20717116 CDB$ROOT
messages72,03012210 CDB$ROOT
post/wait queue3,642918 CDB$ROOT
lgwr LWN SCN8,876413 CDB$ROOT
session statistics737211 CDB$ROOT


Back to Latch Statistics
Back to Top

Latch Miss Sources

Latch NameWhereNoWait Misses SleepsWaiter SleepsPDB Name
active service listkswslogon: session logout021 CDB$ROOT
cache buffers lru chainkcbzgws030 CDB$ROOT
call allocationksuxds011 CDB$ROOT
messagesksarcv020 CDB$ROOT
mostly latch-free SCNkcs024010 CDB$ROOT
object queue header operationkcbo_switch_q_bg010 CDB$ROOT
post/wait queueksliwat:add:nowait010 CDB$ROOT
redo allocationkcrfw_redo_gen: redo allocation 1070 CDB$ROOT
redo allocationkcrfw_redo_write: before write012 CDB$ROOT
session statisticsksu_allocate_session_stats011  
simulator lru latchkcbs_simulate: simulate set060 CDB$ROOT
space background task latchktsj_grab_task02839 CDB$ROOT
space background task latchktsj_detach_task0100 CDB$ROOT
unknown latchkghalo030 CDB$ROOT
unknown latchNo latch010 CDB$ROOT
unknown latchkghupr1015 CDB$ROOT
unknown latchspmemrm_use_free_mem010 CDB$ROOT


Back to Latch Statistics
Back to Top

Mutex Sleep Summary

Mutex TypeLocationSleepsWait Time (ms)
Library Cachekglhdgn2 1061017
Library Cachekglpin1 4100
Library Cachekglpnal1 90610
Library Cachekgllkdl1 8551
Library CachekglScanDS 13240
Library Cachekglget2 240
Library Cachekgllkc1 5740
Row Cache[19] kqrpre30
Row Cache[13] kqreqd10
Library CachekglReleaseHandleReference 12410
Library CachekglScanDS2 14610
Library Cachekglhdgn1 6210
Library Cachekglpndl1 9510


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

Back to Top

Segments by Logical Reads

OwnerTablespace NameObject NameSubobject NameObj. TypeObj#Dataobj#Logical Reads%TotalPDB Name
SCOTTUSERS IX1_DELUDING_TAB INDEX796787984022,384,81636.88 ORCL
SCOTTUSERS PK_DELUDING_TAB INDEX795467984122,304,25636.75 ORCL
SCOTTUSERS DELUDING_TAB TABLE795457984214,009,15223.08 ORCL
SYSSYSTEM I_OBJ1 INDEX36363,6000.01 CDB$ROOT
SYSSYSAUX WRH$_SYSSTAT_PKWRH$_SYSSTAT_776972821_0INDEX PARTITION73384733843,5840.01 CDB$ROOT


Back to Segment Statistics
Back to Top

Segments by Physical Reads

OwnerTablespace NameObject NameSubobject NameObj. TypeObj#Dataobj#Physical Reads%TotalPDB Name
SYSSYSAUX WRH$_SYSSTAT_PKWRH$_SYSSTAT_776972821_0INDEX PARTITION73384733841811.76 CDB$ROOT
SYSSYSAUX WRH$_LATCH_PKWRH$_LATCH_776972821_0INDEX PARTITION7335673356138.50 CDB$ROOT
SYSSYSAUX WRH$_EVENT_HISTOGRAM_PKWRH$_EVENT_HISTOGRAM_776972821_0INDEX PARTITION7342873428117.19 CDB$ROOT
SYSSYSAUX SMON_SCN_TIME TABLE40640495.88 ORCL
SYSSYSAUX WRH$_LATCHWRH$_LATCH_776972821_0TABLE PARTITION733537335395.88 CDB$ROOT


Back to Segment Statistics
Back to Top

Segments by Physical Read Requests

OwnerTablespace NameObject NameSubobject NameObj. TypeObj#Dataobj#Phys Read Requests%TotalPDB Name
SYSSYSAUX WRH$_SYSSTAT_PKWRH$_SYSSTAT_776972821_0INDEX PARTITION73384733841812.77 CDB$ROOT
SYSSYSAUX WRH$_LATCH_PKWRH$_LATCH_776972821_0INDEX PARTITION7335673356139.22 CDB$ROOT
SYSSYSAUX WRH$_EVENT_HISTOGRAM_PKWRH$_EVENT_HISTOGRAM_776972821_0INDEX PARTITION7342873428117.80 CDB$ROOT
SYSSYSAUX WRH$_LATCHWRH$_LATCH_776972821_0TABLE PARTITION733537335396.38 CDB$ROOT
SYSSYSAUX WRH$_EVENT_HISTOGRAMWRH$_EVENT_HISTOGRAM_776972821_0TABLE PARTITION734257342564.26 CDB$ROOT


Back to Segment Statistics
Back to Top

Segments by UnOptimized Reads

OwnerTablespace NameObject NameSubobject NameObj. TypeObj#Dataobj#UnOptimized Reads%TotalPDB Name
SYSSYSAUX WRH$_SYSSTAT_PKWRH$_SYSSTAT_776972821_0INDEX PARTITION73384733841812.77 CDB$ROOT
SYSSYSAUX WRH$_LATCH_PKWRH$_LATCH_776972821_0INDEX PARTITION7335673356139.22 CDB$ROOT
SYSSYSAUX WRH$_EVENT_HISTOGRAM_PKWRH$_EVENT_HISTOGRAM_776972821_0INDEX PARTITION7342873428117.80 CDB$ROOT
SYSSYSAUX WRH$_LATCHWRH$_LATCH_776972821_0TABLE PARTITION733537335396.38 CDB$ROOT
SYSSYSAUX WRH$_EVENT_HISTOGRAMWRH$_EVENT_HISTOGRAM_776972821_0TABLE PARTITION734257342564.26 CDB$ROOT


Back to Segment Statistics
Back to Top

Segments by Optimized Reads

No data exists for this section of the report.

Back to Segment Statistics
Back to Top

Segments by Direct Physical Reads

No data exists for this section of the report.

Back to Segment Statistics
Back to Top

Segments by Physical Writes

OwnerTablespace NameObject NameSubobject NameObj. TypeObj#Dataobj#Physical Writes%TotalPDB Name
SCOTTUSERS DELUDING_TAB TABLE7954579842894,81464.56 ORCL
SCOTTUSERS IX1_DELUDING_TAB INDEX796787984041,6653.01 ORCL
SCOTTUSERS PK_DELUDING_TAB INDEX795467984139,3762.84 ORCL
SYSSYSAUX WRH$_EVENT_HISTOGRAMWRH$_EVENT_HISTOGRAM_776972821_0TABLE PARTITION7342573425480.00 CDB$ROOT
SYSSYSAUX WRH$_ACTIVE_SESSION_HISTORYWRH$_ACTIVE_SESSION_HISTORY_776972821_0TABLE PARTITION7340173401280.00 CDB$ROOT


Back to Segment Statistics
Back to Top

Segments by Physical Write Requests

OwnerTablespace NameObject NameSubobject NameObj. TypeObj#Dataobj#Phys Write Requests%TotalPDB Name
SCOTTUSERS DELUDING_TAB TABLE7954579842669,25364.64 ORCL
SCOTTUSERS IX1_DELUDING_TAB INDEX796787984031,6213.05 ORCL
SCOTTUSERS PK_DELUDING_TAB INDEX795467984128,7392.78 ORCL
SYSSYSAUX WRH$_EVENT_HISTOGRAMWRH$_EVENT_HISTOGRAM_776972821_0TABLE PARTITION7342573425480.00 CDB$ROOT
SYSSYSAUX WRH$_SYSSTAT_PKWRH$_SYSSTAT_776972821_0INDEX PARTITION7338473384180.00 CDB$ROOT


Back to Segment Statistics
Back to Top

Segments by Direct Physical Writes

No data exists for this section of the report.

Back to Segment Statistics
Back to Top

Segments by Table Scans

No data exists for this section of the report.

Back to Segment Statistics
Back to Top

Segments by DB Blocks Changes

OwnerTablespace NameObject NameSubobject NameObj. TypeObj#Dataobj#DB Block Changes% of CapturePDB Name
SCOTTUSERS DELUDING_TAB TABLE795457984211,959,88843.61 ORCL
SCOTTUSERS IX1_DELUDING_TAB INDEX79678798407,788,76828.40 ORCL
SCOTTUSERS PK_DELUDING_TAB INDEX79546798417,674,36827.98 ORCL
SYSSYSAUX WRH$_SYSSTAT_PKWRH$_SYSSTAT_776972821_0INDEX PARTITION73384733841,4080.01 CDB$ROOT
SYSSYSAUX WRH$_LATCH_PKWRH$_LATCH_776972821_0INDEX PARTITION73356733566080.00 CDB$ROOT


Back to Segment Statistics
Back to Top

Segments by Row Lock Waits

No data exists for this section of the report.

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

Dictionary Cache Stats

CacheGet RequestsPct MissScan ReqsPct MissMod ReqsFinal Usage
dc_awr_control310.000 22
dc_global_oids270.000 094
dc_histogram_data10.000 04,519
dc_histogram_defs20.000 06,749
dc_objects3,4350.000 137,001
dc_profiles220.000 02
dc_props9180.000 06
dc_rollback_segments1,2070.000 048
dc_segments1,2110.000 131,097
dc_sequences20.000 213
dc_tablespaces2410.000 014
dc_users2,8310.00760.000317
outstanding_alerts30.000 03
sch_lj_oids300.000 043



Back to Top

Library Cache Activity

NamespaceGet RequestsPct MissPin RequestsPct MissReloadsInvali- dations
ACCOUNT_STATUS40.000 00
AUDIT POLICY300.00300.0000
BODY2370.004850.0000
DBLINK50.000 00
EDITION990.001910.0000
INDEX80.000 00
SCHEMA130.000 00
SQL AREA1,0240.397,313,7550.0021
SQL AREA BUILD366.670 00
SQL AREA STATS450.00450.0000
TABLE/PROCEDURE4070.001,6280.0000
TRIGGER60.0060.0000



Back to Top

Memory Statistics

Back to Top

Memory Dynamic Components

ComponentBegin Snap Size (Mb)Current Size (Mb)Min Size (Mb)Max Size (Mb)Oper CountLast Op Typ/Mod
ASM Buffer Cache0.000.000.000.000STA/
DEFAULT 16K buffer cache0.000.000.000.000STA/
DEFAULT 2K buffer cache0.000.000.000.000STA/
DEFAULT 32K buffer cache0.000.000.000.000STA/
DEFAULT 4K buffer cache0.000.000.000.000STA/
DEFAULT 8K buffer cache0.000.000.000.000STA/
DEFAULT buffer cache7,424.007,424.007,424.007,936.000SHR/IMM
Data Transfer Cache0.000.000.000.000STA/
In Memory RO Extension Area0.000.000.000.000STA/
In Memory RW Extension Area0.000.000.000.000STA/
In-Memory Area0.000.000.000.000STA/
KEEP buffer cache0.000.000.000.000STA/
PGA Target3,072.003,072.003,072.003,072.000STA/
RECYCLE buffer cache0.000.000.000.000STA/
SGA Target10,240.0010,240.0010,240.0010,240.000STA/
Shared IO Pool512.00512.000.00512.000GRO/IMM
java pool64.0064.0064.0064.000STA/
large pool160.00160.00160.00288.000SHR/DEF
shared pool2,048.002,048.002,048.002,048.000STA/
streams pool0.000.000.000.000STA/


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

PDB NameCategoryAlloc (MB)Used (MB)Avg Alloc (MB)Std Dev Alloc (MB)Max Alloc (MB)Hist Max Alloc (MB)Num ProcNum Alloc
Instance BFreeable97.440.004.6412.2555 2121
Instance Other237.02 1.933.183131123123
Instance PL/SQL0.590.440.010.06005751
Instance SQL0.430.330.010.06053012
Instance Total335.480.776.5915.568736231207
Instance EFreeable112.940.005.9417.2875 1919
Instance Other210.23 1.771.971919119119
Instance PL/SQL0.580.440.010.06005145
Instance SQL0.110.030.000.0105237
Instance Total323.850.477.7319.329524212190
CDB$ROOT BFreeable0.500.000.500.001 11
CDB$ROOT Other3.76 3.760.004411
CDB$ROOT PL/SQL0.050.040.050.000011
CDB$ROOT SQL0.680.650.680.001211
CDB$ROOT Total4.990.694.990.005644
CDB$ROOT EFreeable0.880.000.880.001 11
CDB$ROOT Other3.22 1.610.252222
CDB$ROOT PL/SQL0.020.010.010.010122
CDB$ROOT SQL0.000.000.000.000010
CDB$ROOT Total4.110.012.490.273365
ORCL Freeable0.810.000.810.001 11
ORCL Other1.87 1.870.002211
ORCL PL/SQL1.241.231.240.001111
ORCL SQL0.010.000.010.000111
ORCL Total3.931.233.930.004444


Back to Memory Statistics
Back to Top

SGA Memory Summary

SGA regionsBegin Size (Bytes)End Size (Bytes) (if different)
Database Buffers8,321,499,136 
Fixed Size12,343,032 
Redo Buffers21,209,088 
Variable Size2,382,366,984 

Back to Memory Statistics
Back to Top

SGA breakdown difference

PoolNameBegin MBEnd MB% Diff
javafree memory64.0064.000.00
largePX msg pool117.00117.000.00
largefree memory43.0043.000.00
sharedASH buffers24.0024.000.00
sharedKGLH026.7527.452.63
sharedKGLH027.4426.78-2.42
sharedKGLH026.7526.780.10
sharedKGLH027.4427.450.04
sharedSQLA44.2844.290.04
shareddb_block_hash_buckets44.5044.500.00
sharedfree memory1,529.731,530.000.02
sharedobject queue hash buckets24.0024.000.00
sharedrow cache0.020.020.00
 buffer_cache7,424.007,424.000.00
 fixed_sga11.7711.770.00
 log_buffer20.2320.230.00
 shared_io_pool512.00512.000.00


Back to Memory Statistics
Back to Top

Replication Statistics (GoldenGate, XStream)

Back to Top

Replication System Resource Usage

No data exists for this section of the report.

Back to Replication Statistics (GoldenGate, XStream)
Back to Top

Replication SGA Usage

No data exists for this section of the report.

Back to Replication Statistics (GoldenGate, XStream)
Back to Top

GoldenGate Capture

No data exists for this section of the report.

Back to Replication Statistics (GoldenGate, XStream)
Back to Top

GoldenGate Capture Rate

No data exists for this section of the report.

Back to Replication Statistics (GoldenGate, XStream)
Back to Top

GoldenGate Apply Reader

No data exists for this section of the report.

Back to Replication Statistics (GoldenGate, XStream)
Back to Top

GoldenGate Apply Coordinator

No data exists for this section of the report.

Back to Replication Statistics (GoldenGate, XStream)
Back to Top

GoldenGate Apply Server

No data exists for this section of the report.

Back to Replication Statistics (GoldenGate, XStream)
Back to Top

GoldenGate Apply Coordinator Rate

No data exists for this section of the report.

Back to Replication Statistics (GoldenGate, XStream)
Back to Top

GoldenGate Apply Reader and Server Rate

No data exists for this section of the report.

Back to Replication Statistics (GoldenGate, XStream)
Back to Top

XStream Capture

No data exists for this section of the report.

Back to Replication Statistics (GoldenGate, XStream)
Back to Top

XStream Capture Rate

No data exists for this section of the report.

Back to Replication Statistics (GoldenGate, XStream)
Back to Top

XStream Apply Reader

No data exists for this section of the report.

Back to Replication Statistics (GoldenGate, XStream)
Back to Top

XStream Apply Coordinator

No data exists for this section of the report.

Back to Replication Statistics (GoldenGate, XStream)
Back to Top

XStream Apply Server

No data exists for this section of the report.

Back to Replication Statistics (GoldenGate, XStream)
Back to Top

XStream Apply Coordinator Rate

No data exists for this section of the report.

Back to Replication Statistics (GoldenGate, XStream)
Back to Top

XStream Apply Reader and Server Rate

No data exists for this section of the report.

Back to Replication Statistics (GoldenGate, XStream)
Back to Top

Table Statistics by DML Operations

No data exists for this section of the report.

Back to Replication Statistics (GoldenGate, XStream)
Back to Top

Table Statistics by Conflict Resolutions

No data exists for this section of the report.

Back to Replication Statistics (GoldenGate, XStream)
Back to Top

Replication Large Transaction Statistics

No data exists for this section of the report.

Back to Replication Statistics (GoldenGate, XStream)
Back to Top

Replication Long Running Transaction Statistics

No data exists for this section of the report.

Back to Replication Statistics (GoldenGate, XStream)
Back to Top

Streams Statistics

Back to Top

Streams CPU/IO Usage

No data exists for this section of the report.

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 Capture Rate

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

Streams Apply Rate

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 Queue Subscribers

No data exists for this section of the report.

Back to Streams Statistics
Back to Top

Rule Set

No data exists for this section of the report.

Back to Streams Statistics
Back to Top

Persistent Queues

No data exists for this section of the report.

Back to Streams Statistics
Back to Top

Persistent Queues Rate

No data exists for this section of the report.

Back to Streams Statistics
Back to Top

Persistent Queue Subscribers

No data exists for this section of the report.

Back to Streams Statistics
Back to Top

Resource Limit Stats

No data exists for this section of the report.


Back to Top

Shared Server Statistics

Back to Top

Shared Servers Activity

Avg Total ConnectionsAvg Active ConnectionsAvg Total Shared SrvrsAvg Active Shared SrvrsAvg Total DispatchersAvg Active Dispatchers
0025010


Back to Shared Server Statistics
Back to Top

Shared Servers Rates

Common Queue Per SecDisp Queue Per SecServer Msgs/SecServer KB/SecCommon Queue TotalDisp Queue TotalServer Total MsgsServer Total(KB)
0000.000000


Back to Shared Server Statistics
Back to Top

Shared Servers Utilization

Total Server Time (s)%Busy%IdleIncoming Net %Outgoing Net %
26,7190.00100.000.000.00


Back to Shared Server Statistics
Back to Top

Shared Servers Common Queue

No data exists for this section of the report.

Back to Shared Server Statistics
Back to Top

Shared Servers Dispatchers

NameAvg ConnsTotal Disp Time (s)%Busy%IdleTotal QueuedTotal Queue Wait (s)Avg Queue Wait (ms)
D0000.001,0690.00100.0000 


Back to Shared Server Statistics
Back to Top

init.ora Parameters

Back to Top

init.ora Parameters

Parameter NameBegin valueEnd value (if different)PDB Name
_ash_size25165824   CDB$ROOT
audit_file_dest/u01/app/oracle/admin/orcl12c/adump   CDB$ROOT
audit_trailDB   CDB$ROOT
compatible12.2.0   CDB$ROOT
connection_brokers((TYPE=DEDICATED)(BROKERS=1)), ((TYPE=EMON)(BROKERS=1))   CDB$ROOT
control_files/u01/app/oracle/oradata/orcl12c/control01.ctl, /u01/app/oracle/fast_recovery_area/orcl12c/control02.ctl   CDB$ROOT
db_block_size8192   CDB$ROOT
db_nameorcl12c   CDB$ROOT
db_recovery_file_dest/u01/app/oracle/fast_recovery_area/orcl12c   CDB$ROOT
db_recovery_file_dest_size5368709120   CDB$ROOT
diagnostic_dest/u01/app/oracle   CDB$ROOT
dispatchers(PROTOCOL=TCP) (SERVICE=orcl12cXDB)   CDB$ROOT
enable_pluggable_databaseTRUE   CDB$ROOT
local_listenerLISTENER_ORCL12C   CDB$ROOT
max_string_sizeEXTENDED   CDB$ROOT
nls_languageAMERICAN   CDB$ROOT
nls_territoryAMERICA   CDB$ROOT
open_cursors300   CDB$ROOT
pga_aggregate_limit6442450944   CDB$ROOT
pga_aggregate_target3221225472   CDB$ROOT
plsql_warningsDISABLE:ALL   CDB$ROOT
processes300   CDB$ROOT
remote_login_passwordfileEXCLUSIVE   CDB$ROOT
sga_max_size10737418240   CDB$ROOT
sga_target10737418240   CDB$ROOT
sga_target0    
sga_target0   ORCL
shared_pool_size2147483648   CDB$ROOT
shared_pool_size0    
shared_pool_size0   ORCL
shared_servers25   CDB$ROOT
undo_tablespace      
undo_tablespaceUNDOTBS1   ORCL
undo_tablespaceUNDOTBS2   CDB$ROOT


Back to init.ora Parameters
Back to Top

init.ora Multi-Valued Parameters

Parameter NameBegin valueEnd value (if different)PDB Name
connection_brokers((TYPE=DEDICATED)(BROKERS=1))   CDB$ROOT
((TYPE=EMON)(BROKERS=1))   CDB$ROOT
control_files/u01/app/oracle/fast_recovery_area/orcl12c/control02.ctl   CDB$ROOT
/u01/app/oracle/oradata/orcl12c/control01.ctl   CDB$ROOT


Back to init.ora Parameters
Back to Top

Active Session History (ASH) Report

Back to Top

Top SQL with Top Events

SQL IDPlan HashExecutions% ActivityEvent% EventTop Row Source% Row SourceSQL TextContainer Name
8kpwagy566cc6 5639.10 CPU + Wait for CPU37.82** Row Source Not Available **37.82 INSERT INTO DELUDING_TAB VALUE...ORCL
 5639.10 log file switch (checkpoint incomplete)1.28** Row Source Not Available **1.28 INSERT INTO DELUDING_TAB VALUE...ORCL
3cjmgxvhv3n2p 123.08 CPU + Wait for CPU23.08** Row Source Not Available **23.08 DECLARE TYPE ID_t IS TABLE OF ...ORCL


Back to Active Session History (ASH) Report
Back to Top

Top SQL with Top Row Sources

SQL IDPlan HashExecutions% ActivityRow Source% Row SourceTop Event% EventSQL TextContainer Name
8kpwagy566cc6 5639.10 ** Row Source Not Available **39.10CPU + Wait for CPU37.82 INSERT INTO DELUDING_TAB VALUE...ORCL
3cjmgxvhv3n2p 123.08 ** Row Source Not Available **23.08CPU + Wait for CPU23.08 DECLARE TYPE ID_t IS TABLE OF ...ORCL


Back to Active Session History (ASH) Report
Back to Top

Top Sessions

Sid, Serial#% ActivityEvent% EventUserProgram# Samples ActiveXIDs
47,4849767.95CPU + Wait for CPU66.67 UserID: 121sqlplus@localh...n (TNS V1-V3)104/107 [ 97%]81
log file switch (checkpoint incomplete)1.28 2/107 [ 2%]2
402,1389314.10log file parallel write14.10 SYSoracle@localho...domain (LG00)22/107 [ 21%]0
282,3125412.82db file async I/O submit12.18 SYSoracle@localho...domain (DBW0)19/107 [ 18%]0
2,194961.28LGWR worker group ordering0.64 SYSoracle@localho...domain (LG01)1/107 [ 1%]0
161,166571.28CPU + Wait for CPU1.28 SYSoracle@localho...domain (PSP0)2/107 [ 2%]0


Back to Active Session History (ASH) Report
Back to Top

Top Blocking Sessions

Blocking Sid (Inst)% ActivityEvent Caused% EventUserProgram# Samples ActiveXIDs
322,36341( 1)1.28log file switch (checkpoint incomplete)1.28 ** NOT FOUND **BLOCKING SESSION NOT FOUND0/107 [ 0%] 


Back to Active Session History (ASH) Report
Back to Top

Top PL/SQL Procedures

No data exists for this section of the report.

Back to Active Session History (ASH) Report
Back to Top

Top Events

EventEvent ClassSession Type% ActivityAvg Active Sessions
CPU + Wait for CPUCPUFOREGROUND 66.670.97
log file parallel writeSystem I/OBACKGROUND 14.740.22
db file async I/O submitSystem I/OBACKGROUND 12.180.18
CPU + Wait for CPUCPUBACKGROUND 2.560.04
control file parallel writeSystem I/OBACKGROUND 1.280.02


Back to Active Session History (ASH) Report
Back to Top

Top Event P1/P2/P3 Values

Event% EventP1, P2, P3 Values% ActivityParameter 1Parameter 2Parameter 3
log file parallel write14.74"1","43","1"0.64 filesblocksrequests
db file async I/O submit12.18"3616","0","0"1.92 requestsinterrupttimeout
control file parallel write1.28"2","3","2"0.64 filesblock#requests


Back to Active Session History (ASH) Report
Back to Top

Top DB Objects

No data exists for this section of the report.

Back to Active Session History (ASH) Report
Back to Top

Activity Over Time

Slot Time (Duration)Slot CountEventEvent Count% Event
01:12:24 (2.6 min)23CPU + Wait for CPU1610.26
db file async I/O submit31.92
log file parallel write21.28
01:15:00 (5.0 min)40CPU + Wait for CPU3019.23
log file parallel write63.85
db file async I/O submit31.92
01:20:00 (5.0 min)47CPU + Wait for CPU3119.87
log file parallel write95.77
db file async I/O submit63.85
01:25:00 (5.0 min)45CPU + Wait for CPU3019.23
db file async I/O submit74.49
log file parallel write63.85
01:30:00 (13 secs)1CPU + Wait for CPU10.64


Back to Active Session History (ASH) Report
Back to Top

ADDM Task ADDM:776972821_1_28

          ADDM Report for Task 'ADDM:776972821_1_28'
          ------------------------------------------

Analysis Period
---------------
AWR snapshot range from 27 to 28.
Time period starts at 23-APR-18 01.12.24 AM
Time period ends at 23-APR-18 01.30.13 AM

Analysis Target
---------------
Database 'ORCL12C' with DB ID 776972821.
Database version 12.2.0.1.0.
ADDM performed an analysis of instance orcl12c, numbered 1 and hosted at
localhost.localdomain.

Activity During the Analysis Period
-----------------------------------
Total database time was 1069 seconds.
The average number of active sessions was 1.

Summary of Findings
-------------------
   Description         Active Sessions      Recommendations
                       Percent of Activity
   ------------------  -------------------  ---------------
1  Top SQL Statements  .91 | 90.65          2


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


          Findings and Recommendations
          ----------------------------

Finding 1: Top SQL Statements
Impact is .91 active sessions, 90.65% of total activity.
--------------------------------------------------------
SQL statements consuming significant database time were found. These
statements offer a good opportunity for performance improvement.

   Recommendation 1: SQL Tuning
   Estimated benefit is .55 active sessions, 55.14% of total activity.
   -------------------------------------------------------------------
   Action
      Run SQL Tuning Advisor on the INSERT statement with SQL_ID
      "8kpwagy566cc6".
      Related Object
         SQL statement with SQL_ID 8kpwagy566cc6.
         INSERT INTO DELUDING_TAB VALUES( :B1 , :B2 , :B3 , :B4 , :B5 , :B6 ,
         :B7 )
   Rationale
      The SQL statement executed in container ORCL with database ID
      4079644691.
   Rationale
      The SQL spent 96% of its database time on CPU, I/O and Cluster waits.
      This part of database time may be improved by the SQL Tuning Advisor.
   Rationale
      Database time for this SQL was divided as follows: 100% for SQL
      execution, 0% for parsing, 0% for PL/SQL execution and 0% for Java
      execution.
   Rationale
      SQL statement with SQL_ID "8kpwagy566cc6" was executed 7313808 times and
      had an average elapsed time of 0.000075 seconds.
   Rationale
      Top level calls to execute the PL/SQL statement with SQL_ID
      "3cjmgxvhv3n2p" are responsible for 100% of the database time spent on
      the INSERT statement with SQL_ID "8kpwagy566cc6".
      Related Object
         SQL statement with SQL_ID 3cjmgxvhv3n2p.
         DECLARE
         TYPE ID_t IS TABLE OF deluding_tab.id%TYPE INDEX BY PLS_INTEGER;
         TYPE FOO_t IS TABLE OF deluding_tab.foo%TYPE INDEX BY PLS_INTEGER;
         TYPE BAR_t IS TABLE OF deluding_tab.bar%TYPE INDEX BY PLS_INTEGER;
         TYPE FOOBAR_t IS TABLE OF deluding_tab.foobar%TYPE INDEX BY
         PLS_INTEGER;
         TYPE TYPECODE_t IS TABLE OF deluding_tab.type_code%TYPE INDEX BY
         PLS_INTEGER;
         TYPE VALBEGIN_DATE_t IS TABLE OF
         deluding_tab.validated_begin_date%TYPE INDEX BY PLS_INTEGER;
         TYPE VALEND_DATE_t IS TABLE OF deluding_tab.validated_end_date%TYPE
         INDEX BY PLS_INTEGER;
         IDs ID_t;
         FOOs FOO_t;
         BARs BAR_t;
         FOOBARs FOOBAR_t;
         TYPECODEs TYPECODE_t;
         VALBEGIN_DATEs VALBEGIN_DATE_t;
         VALEND_DATEs VALEND_DATE_t;
         k PLS_INTEGER := 1;
         BEGIN
         FOR i IN 1..8000000 LOOP
         IDs(k) := TO_CHAR(i, 'FM0000000000000000');
         FOOs(k) := 'FOO-'||IDs(k);
         BARs(k) := RPAD('BAR-'||IDs(k),320,'-');
         FOOBARs(k) := RPAD('FOOBAR-'||IDs(k),500,'*');
         TYPECODEs(k) := TO_CHAR(MOD(i,3), 'FM000');
         VALBEGIN_DATEs(k) := TO_DATE('20180101','yyyymmdd');
         --???????????????????????????
         VALEND_DATEs(k) := TO_DATE('20180630','yyyymmdd');
         --???????????????????????????
         k := k + 1;
         IF MOD(i, 1000) = 0 THEN
         FOR l in 1..1000 LOOP
         INSERT INTO deluding_tab
         VALUES(
         IDs(l)
         , FOOs(l)
         , BARs(l)
         , FOOBARs(l)
         , TYPECODEs(l)
         , VALBEGIN_DATEs(l)
         , VALEND_DATEs(l)
         );
         END LOOP;
         COMMIT;
         k := 1;
         END IF;
         END LOOP;
         END;

   Recommendation 2: SQL Tuning
   Estimated benefit is .34 active sessions, 33.64% of total activity.
   -------------------------------------------------------------------
   Action
      Investigate the PL/SQL statement with SQL_ID "3cjmgxvhv3n2p" for
      possible performance improvements. You can supplement the information
      given here with an ASH report for this SQL_ID.
      Related Object
         SQL statement with SQL_ID 3cjmgxvhv3n2p.
         DECLARE
         TYPE ID_t IS TABLE OF deluding_tab.id%TYPE INDEX BY PLS_INTEGER;
         TYPE FOO_t IS TABLE OF deluding_tab.foo%TYPE INDEX BY PLS_INTEGER;
         TYPE BAR_t IS TABLE OF deluding_tab.bar%TYPE INDEX BY PLS_INTEGER;
         TYPE FOOBAR_t IS TABLE OF deluding_tab.foobar%TYPE INDEX BY
         PLS_INTEGER;
         TYPE TYPECODE_t IS TABLE OF deluding_tab.type_code%TYPE INDEX BY
         PLS_INTEGER;
         TYPE VALBEGIN_DATE_t IS TABLE OF
         deluding_tab.validated_begin_date%TYPE INDEX BY PLS_INTEGER;
         TYPE VALEND_DATE_t IS TABLE OF deluding_tab.validated_end_date%TYPE
         INDEX BY PLS_INTEGER;
         IDs ID_t;
         FOOs FOO_t;
         BARs BAR_t;
         FOOBARs FOOBAR_t;
         TYPECODEs TYPECODE_t;
         VALBEGIN_DATEs VALBEGIN_DATE_t;
         VALEND_DATEs VALEND_DATE_t;
         k PLS_INTEGER := 1;
         BEGIN
         FOR i IN 1..8000000 LOOP
         IDs(k) := TO_CHAR(i, 'FM0000000000000000');
         FOOs(k) := 'FOO-'||IDs(k);
         BARs(k) := RPAD('BAR-'||IDs(k),320,'-');
         FOOBARs(k) := RPAD('FOOBAR-'||IDs(k),500,'*');
         TYPECODEs(k) := TO_CHAR(MOD(i,3), 'FM000');
         VALBEGIN_DATEs(k) := TO_DATE('20180101','yyyymmdd');
         --???????????????????????????
         VALEND_DATEs(k) := TO_DATE('20180630','yyyymmdd');
         --???????????????????????????
         k := k + 1;
         IF MOD(i, 1000) = 0 THEN
         FOR l in 1..1000 LOOP
         INSERT INTO deluding_tab
         VALUES(
         IDs(l)
         , FOOs(l)
         , BARs(l)
         , FOOBARs(l)
         , TYPECODEs(l)
         , VALBEGIN_DATEs(l)
         , VALEND_DATEs(l)
         );
         END LOOP;
         COMMIT;
         k := 1;
         END IF;
         END LOOP;
         END;
   Rationale
      The SQL statement executed in container ORCL with database ID
      4079644691.
   Rationale
      The SQL Tuning Advisor cannot operate on PL/SQL statements.
   Rationale
      Database time for this SQL was divided as follows: 17% for SQL
      execution, 0% for parsing, 83% for PL/SQL execution and 0% for Java
      execution.



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

          Additional Information
          ----------------------

Miscellaneous Information
-------------------------
Wait class "Application" was not consuming significant database time.
Wait class "Commit" was not consuming significant database time.
Wait class "Concurrency" was not consuming significant database time.
Wait class "Configuration" was not consuming significant database time.
CPU was not a bottleneck for the instance.
Wait class "Network" was not consuming significant database time.
Wait class "User I/O" was not consuming significant database time.
Session connect and disconnect calls were not consuming significant database
time.
Hard parsing of SQL statements was not consuming significant database time.


Back to Top

End of Report