WORKLOAD REPOSITORY report for

DB NameDB IdInstanceInst numStartup TimeReleaseRAC
MAC797696270MAC_1107-Jul-13 08:0712.1.0.1.0YES

Host NamePlatformCPUsCoresSocketsMemory (GB)
maclean1.oracle.comLinux x86 64-bit 4 4 1 9.55

Snap IdSnap TimeSessionsCursors/SessionInstancesPluggable Databases Open
Begin Snap:11407-Jul-13 08:25:4960 .911
End Snap:11507-Jul-13 08:30:1965 .911
Elapsed:  4.50 (mins)    
DB Time:  12.63 (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 Statements2.8196.00ADDM:797696270_1_11507-Jul-13 08:2507-Jul-13 08:30
High Watermark Waits2.812.66ADDM:797696270_1_11507-Jul-13 08:2507-Jul-13 08:30

Load Profile

Per SecondPer TransactionPer ExecPer Call
DB Time(s): 2.8 0.0 0.00 0.33
DB CPU(s): 2.6 0.0 0.00 0.31
Redo size (bytes): 8,180,730.6 545.6  
Logical read (blocks): 46,382.1 3.1  
Block changes: 60,219.5 4.0  
Physical read (blocks): 9.2 0.0  
Physical write (blocks): 61.5 0.0  
Read IO requests: 5.1 0.0  
Write IO requests: 28.8 0.0  
Read IO (MB): 0.1 0.0  
Write IO (MB): 0.5 0.0  
Global Cache blocks received: 0.0 0.0  
Global Cache blocks served: 0.0 0.0  
User calls: 8.4 0.0  
Parses (SQL): 18.2 0.0  
Hard parses (SQL): 1.1 0.0  
SQL Work Area (MB): 0.4 0.0  
Logons: 1.5 0.0  
Executes (SQL): 15,020.4 1.0  
Rollbacks: 0.0 0.0  
Transactions: 14,994.9   

Instance Efficiency Percentages (Target 100%)

Buffer Nowait %: 99.75Redo NoWait %: 100.00
Buffer Hit %: 99.98In-memory Sort %: 100.00
Library Hit %: 99.96Soft Parse %: 93.74
Execute to Parse %: 99.88Latch Hit %: 98.25
Parse CPU to Parse Elapsd %: 7.94% Non-Parse CPU: 99.93

Top 10 Foreground Events by Total Wait Time

EventWaitsTotal Wait Time (sec)Wait Avg(ms)% DB timeWait Class
DB CPU 691.3 91.2 
enq: HW - contention67620.2302.7Configuration
db file sequential read1,5069.661.3User I/O
control file sequential read2,2837.931.0System I/O
library cache: mutex X77,5266.10.8Concurrency
buffer busy waits30,0583.70.5Concurrency
KSV master wait1,1821.61.2Other
latch: ges resource hash list24,8381.40.2Other
name-service call wait12185.1Other
log file switch completion81122.1Configuration

Wait Classes by Total Wait Time

Wait ClassWaitsTotal Wait Time (sec)Avg Wait (ms)% DB timeAvg Active Sessions
DB CPU 691 91.22.6
System I/O6,9021642421.70.6
Configuration76321282.80.1
User I/O2,5181351.80.0
Other51,0271201.60.0
Concurrency109,9541001.40.0
Commit81116.10.0
Application7000.00.0
Scheduler600.00.0
Network17800.00.0

Host CPU

CPUsCoresSocketsLoad Average BeginLoad Average End%User%System%WIO%Idle
4 4 1 0.09 2.44 17.6 50.1 11.9 32.0

Instance CPU

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

IO Profile

Read+Write Per SecondRead per SecondWrite Per Second
Total Requests: 87.5 22.7 64.8
Database Requests: 33.8 5.1 28.8
Optimized Requests: 0.0 0.0 0.0
Redo Requests: 29.6 0.0 29.6
Total (MB): 18.9 0.3 18.6
Database (MB): 0.6 0.1 0.5
Optimized Total (MB): 0.0 0.0 0.0
Redo (MB): 16.2 0.0 16.2
Database (blocks): 70.7 9.2 61.5
Via Buffer Cache (blocks): 70.5 9.1 61.4
Direct (blocks): 0.2 0.1 0.1

Memory Statistics

BeginEnd
Host Mem (MB): 9,777.7 9,777.7
SGA use (MB): 6,000.0 6,000.0
PGA use (MB): 326.6 359.9
% Host Mem used for SGA+PGA: 64.70 65.05

Cache Sizes

BeginEnd
Buffer Cache: 4,544M 4,544MStd Block Size: 8K
Shared Pool Size: 1,040M 1,040MLog Buffer: 19,460K

Shared Pool Statistics

BeginEnd
Memory Usage %: 60.38 61.97
% SQL with executions>1: 88.27 89.56
% Memory for SQL w/exec>1: 86.09 85.60

Main Report

RAC Statistics


Back to Top

Wait Events Statistics

Back to Top

Time Model Statistics

Statistic NameTime (s)% of DB Time
sql execute elapsed time755.7999.76
DB CPU691.2691.24
PL/SQL execution elapsed time59.857.90
parse time elapsed4.760.63
hard parse elapsed time4.450.59
hard parse (sharing criteria) elapsed time1.760.23
sequence load elapsed time0.080.01
repeated bind elapsed time0.020.00
PL/SQL compilation elapsed time0.010.00
DB time757.64 
background elapsed time169.64 
background cpu time10.16 


Back to Wait Events Statistics
Back to Top

Operating System Statistics

StatisticValueEnd Value
FREE_MEMORY_BYTES333,729,792311,246,848
INACTIVE_MEMORY_BYTES6,261,166,0806,247,346,176
SWAP_FREE_BYTES6,241,116,160 
BUSY_TIME73,493 
IDLE_TIME34,579 
IOWAIT_TIME12,863 
SYS_TIME54,094 
USER_TIME19,037 
LOAD02
PHYSICAL_MEMORY_BYTES10,252,619,776 
NUM_CPUS4 
NUM_CPU_CORES4 
NUM_CPU_SOCKETS1 
GLOBAL_RECEIVE_SIZE_MAX4,194,304 
GLOBAL_SEND_SIZE_MAX1,048,576 
TCP_RECEIVE_SIZE_DEFAULT87,380 
TCP_RECEIVE_SIZE_MAX4,194,304 
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
07-Jul 08:25:490.09     
07-Jul 08:30:192.4468.0017.6250.0532.0011.90


Back to Wait Events Statistics
Back to Top

Foreground Wait Class

Wait ClassWaits%Time -outsTotal Wait Time (s)Avg wait (ms)%DB time
DB CPU  691 91.24
Configuration763021282.80
User I/O2,21701151.47
Concurrency108,55401001.37
System I/O2,6830831.07
Other35,6990600.82
Commit6011510.12
Application570000.00
Scheduler60000.00
Network1780000.00


Back to Wait Events Statistics
Back to Top

Foreground Wait Events

EventWaits%Time -outsTotal Wait Time (s)Avg wait (ms)Waits /txn% DB time
enq: HW - contention676 20300.002.66
db file sequential read1,506 1060.001.27
control file sequential read2,283 830.001.05
library cache: mutex X77,526 600.020.81
buffer busy waits30,058 400.010.48
KSV master wait1,182 210.000.21
latch: ges resource hash list24,838 100.010.19
name-service call wait12 1850.000.13
log file switch completion8 11220.000.13
log file sync6 11510.000.12
read by other session94 160.000.08
latch: enqueue hash chains6,282 100.000.07
db file scattered read63 070.000.06
cursor: pin S369 010.000.05
DFS lock handle100 040.000.05
Disk file operations I/O348 010.000.05
reliable message667 000.000.04
enq: US - contention484 010.000.04
PX Deq: Slave Session Stats420 010.000.04
row cache lock445 000.000.02
kfk: async disk IO100 010.000.02
enq: TX - contention14 080.000.01
cursor: mutex X77 010.000.01
latch free1,102 000.000.01
Data file init write106 010.000.01
enq: SQ - contention78 010.000.01
control file parallel write300 000.000.00
CSS initialization6 020.000.00
db file single write100 000.000.00
ASM file metadata operation391 000.000.00
enq: IV - contention31 000.000.00
CSS operation: action6 010.000.00
PX Deq: Signal ACK EXT42 000.000.00
latch: cache buffers chains73 000.000.00
latch: undo global data65 000.000.00
CSS operation: query18 000.000.00
SQL*Net break/reset to client57 000.000.00
resmgr:cpu quantum6 000.000.00
latch: call allocation18 000.000.00
SQL*Net message from client178 68038230.00 
jobq slave wait16198804960.00 
PX Deq: Execution Msg1,662 210.00 
PX Deq: Table Q Sample162 010.00 
PX Deq Credit: send blkd304 000.00 
PX Deq: Execute Reply69 000.00 
PX Deq: Join ACK84 000.00 
PX Deq: Parse Reply42 000.00 


Back to Wait Events Statistics
Back to Top

Background Wait Events

EventWaits%Time -outsTotal Wait Time (s)Avg wait (ms)Waits /txn% bg time
log file parallel write1,7020142830.0083.69
control file sequential read2,16101150.006.45
ASM file metadata operation2,3510520.002.91
db file sequential read18602110.001.26
db file parallel write8602190.000.96
control file parallel write2030270.000.89
oracle thread bootstrap1600220.000.21
KSV master wait41240010.000.15
Disk file operations I/O730020.000.08
PX Deq: Slave Join Frag3780000.000.07
os thread creation160040.000.04
CGS wait for IPC msg2,573100000.000.03
PX Deq: reap credit2,683100000.000.03
PX Deq: Slave Session Stats3360000.000.03
DFS lock handle120040.000.03
row cache process2,9140000.000.03
log file sequential read120030.000.02
PX Deq: Signal ACK EXT3360000.000.02
log file sync200130.000.02
CSS initialization80030.000.01
kfk: async disk IO120020.000.01
ksxr poll remote instances952100000.000.01
PX Deq: Table Q qref1620000.000.01
CSS operation: action120010.000.01
enq: JS - queue lock20060.000.01
libcache interrupt action by LCK1,3690000.000.01
db file scattered read20060.000.01
row cache cleanup1,9070000.000.01
Data file init write120010.000.00
enq: RO - fast object reuse130010.000.00
row cache lock280000.000.00
reliable message170000.000.00
CSS operation: data update50010.000.00
enq: IV - contention320000.000.00
LGWR wait for redo copy260000.000.00
CSS operation: query240000.000.00
latch free410000.000.00
CSS operation: data query50010.000.00
log file single write120000.000.00
latch: enqueue hash chains90000.000.00
PX qref latch3100010.000.00
db file single write120000.000.00
direct path write temp110000.000.00
direct path read10010.000.00
enq: PS - contention100000.000.00
PX Idle Wait37805,804153560.00 
rdbms ipc message9,299495,2425640.00 
class slave wait50,673961,348270.01 
Space Manager: slave idle wait585080613780.00 
wait for unread message on broadcast channel14410054137540.00 
LGWR worker group idle60540899770.00 
ges remote message5,54992540970.00 
gcs remote message17,457100539310.00 
DIAG idle wait2,7601005391950.00 
GCR sleep10710053550010.00 
dispatcher timer5100300600020.00 
Streams AQ: qmn slave idle wait100280280090.00 
Streams AQ: qmn coordinator idle wait2050280140050.00 
Streams AQ: load balancer idle14100280200060.00 
smon timer190027014220.00 
pmon timer9010027030010.00 
AQPC idle9100270300090.00 
shared server idle wait9100270300030.00 
lreg timer9010027030000.00 
heartbeat redo informer269026910000.00 
ASM background timer1,56502681710.00 
PING603326043360.00 
PX Deq: Join ACK6700000.00 
PX Deq: Execute Reply1,1580000.00 
PX Deq: Parse Reply3360000.00 


Back to Wait Events Statistics
Back to Top

Wait Event Histogram

  % of Waits
EventTotal Waits <1ms <2ms <4ms <8ms<16ms<32ms <=1s >1s
ASM background starting50100.0       
ASM file metadata operation274277.510.95.33.41.01.6.3 
CGS wait for IPC msg2568100.0       
CSS initialization1450.0  50.0    
CSS operation: action1827.872.2      
CSS operation: data query560.040.0      
CSS operation: data update520.080.0      
CSS operation: query4288.111.9      
DFS lock handle112  75.024.1 .9  
Data file init write11892.45.91.7     
Disk file operations I/O41997.9.5   .21.4 
KSV master wait159466.316.410.25.41.7.1  
LGWR all worker groups3100.0       
LGWR wait for redo copy2688.57.73.8     
PX Deq: Signal ACK EXT378100.0       
PX Deq: Slave Join Frag37899.7.3      
PX Deq: Slave Session Stats75685.214.4.4     
PX Deq: Table Q qref162100.0       
PX Deq: reap credit2673100.0       
PX qref latch475.025.0      
SQL*Net break/reset to client57100.0       
SQL*Net message to client178100.0       
asynch descriptor resize16100.0       
buffer busy waits30.1K99.9.0.0.0 .0.1 
control file parallel write50395.6.2.2.4.4.82.4 
control file sequential read444392.8.4.3.51.11.13.8 
cursor: mutex X7789.6   10.4   
cursor: pin S369 100.0      
db file async I/O submit31100.0       
db file parallel write8666.32.32.33.59.33.512.8 
db file scattered read6527.73.14.618.540.06.2  
db file sequential read151464.7.92.27.615.95.43.3 
db file single write112100.0       
direct path read1 100.0      
direct path write4100.0       
direct path write temp11100.0       
enq: FB - contention1100.0       
enq: HW - contention67672.61.0.1  9.316.9 
enq: IV - contention6395.23.21.6     
enq: JS - queue lock2   100.0    
enq: PS - contention15100.0       
enq: RO - fast object reuse13100.0       
enq: SQ - contention7894.95.1      
enq: TX - contention1485.77.1    7.1 
enq: US - contention48499.0.8    .2 
global enqueue expand wait1100.0       
kfk: async disk IO11298.2    .9.9 
ksxr poll remote instances952100.0       
latch free114399.7.2.1     
latch: cache buffers chains73100.0       
latch: call allocation18100.0       
latch: enqueue hash chains629199.4.4.1.0    
latch: ges resource hash list24.9K99.8.1.1.0.0   
latch: redo allocation8100.0       
latch: row cache objects2100.0       
latch: session allocation8100.0       
latch: shared pool6100.0       
latch: undo global data65100.0       
libcache interrupt action by LCK1369100.0       
library cache: mutex X77.5K99.3.0.0 .6   
log file parallel write170120.81.4.5.52.46.667.8 
log file sequential read1275.08.3  8.38.3  
log file single write12100.0       
log file switch completion8   12.5  87.5 
log file sync812.5   12.525.050.0 
name-service call wait12    16.78.375.0 
oracle thread bootstrap16    18.868.812.5 
os thread creation1656.36.312.56.36.312.5  
read by other session9468.13.23.28.511.71.14.3 
reliable message68499.4.1.3   .1 
resmgr:cpu quantum6100.0       
row cache cleanup1907100.0       
row cache lock47398.9.6.4     
row cache process2914100.0       
undo segment extension1100.0       
AQPC idle9       100.0
ASM background timer156553.610.96.513.93.32.63.75.6
DIAG idle wait2756    4.5 95.5 
GCR sleep107       100.0
LGWR worker group idle6       100.0
PING6066.7      33.3
PX Deq Credit: send blkd305100.0       
PX Deq: Execute Reply122799.5.4 .1    
PX Deq: Execution Msg166252.827.917.02.2    
PX Deq: Join ACK75499.9.1      
PX Deq: Parse Reply37889.76.14.2     
PX Deq: Table Q Sample16282.117.9      
PX Idle Wait3785.64.82.4   .886.5
SQL*Net message from client17864.0     12.923.0
Space Manager: slave idle wait5852.4 .5.5 2.140.254.4
Streams AQ: load balancer idle14       100.0
Streams AQ: qmn coordinator idle wait1850.0      50.0
Streams AQ: qmn slave idle wait9       100.0
class slave wait50.6K1.5.4.1.496.7.2.3.4
dispatcher timer5       100.0
gcs remote message17.4K     98.81.2 
ges remote message55392.2.1.2.5.7.995.4 
heartbeat redo informer270      100.0 
jobq slave wait164      100.0 
lreg timer90       100.0
pmon timer90       100.0
rdbms ipc message928920.29.73.2.71.21.548.115.4
shared server idle wait9       100.0
smon timer190   .5 2.141.156.3
wait for unread message on broadcast channel143       100.0


Back to Wait Events Statistics
Back to Top

Wait Event Histogram Detail (64 msec to 2 sec)

  % of Total Waits
EventWaits 64ms to 2s<32ms<64ms<1/8s<1/4s<1/2s <1s <2s>=2s
ASM file metadata operation899.7.1.1.0 .1  
Disk file operations I/O698.61.0.2.2    
buffer busy waits1699.9.0.0.0.0   
control file parallel write1297.6.21.8.4    
control file sequential read16896.21.61.4.6.1.0  
db file parallel write1187.22.35.83.51.2   
db file sequential read5096.71.6.9.6.2.1  
enq: HW - contention11483.13.76.14.32.2.6  
enq: TX - contention192.9 7.1     
enq: US - contention199.8  .2    
kfk: async disk IO199.1 .9     
log file parallel write115332.214.631.019.32.6.2  
log file switch completion712.5 25.062.5    
log file sync450.0 12.512.525.0   
name-service call wait925.0 66.78.3    
oracle thread bootstrap287.512.5      
read by other session495.72.11.11.1    
reliable message199.9  .1    


Back to Wait Events Statistics
Back to Top

Wait Event Histogram Detail (4 sec to 2 min)

No data exists for this section of the report.

Back to Wait Events Statistics
Back to Top

Wait Event Histogram Detail (4 min 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)
SYS$USERS757691212,515
SYS$BACKGROUND00011
MAC0000
MACXDB0000
macc0000


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
SYS$USERS 19161010850410001780
SYS$BACKGROUND 3012144900000


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
742.083247.3697.9592.560.1673kc99v9699nm sqlplus@maclean1.oracle.com (TNS V1-V3)   begin loop insert into lgwr_pr...
401.894,049,2090.0053.0596.020.29cfrfu4058yard sqlplus@maclean1.oracle.com (TNS V1-V3)   INSERT INTO LGWR_PRESS VALUES(...
5.3115.310.7039.1356.221uk5m5qbzj1vt sqlplus@maclean1.oracle.com (TNS V1-V3)   BEGIN dbms_workload_repository...
4.1714.170.5542.0252.226ajkhukk78nsr     begin prvt_hdm.auto_execute( :...
3.2713.270.433.9195.15adzjh275fvvx4 DBMS_SCHEDULER MACC call WWV_FLOW_WORKSHEET_API.DO...
2.3512.350.314.7393.53adzjh275fvvx4 DBMS_SCHEDULER   call WWV_FLOW_WORKSHEET_API.DO...
1.51260.060.202.7998.51cvn54b7yz0s8u     select /*+ index(idl_ub1$ i_id...
1.4211.420.192.4097.47aahsjvcnr3swn     merge /*+ dynamic_sampling(mm...
0.9010.900.122.4596.99c2h8tpm85b5gb DBMS_SCHEDULER MACC SELECT VALUE FROM V$PARAMETER ...
0.890 0.121.8097.655cbpmbnnf80md   MACC select NUM , NAME , TYPE , VAL...


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
686.893228.9699.37742.0892.560.1673kc99v9699nm sqlplus@maclean1.oracle.com (TNS V1-V3)   begin loop insert into lgwr_pr...
385.904,049,2090.0055.83401.8996.020.29cfrfu4058yard sqlplus@maclean1.oracle.com (TNS V1-V3)   INSERT INTO LGWR_PRESS VALUES(...
2.0812.080.305.3139.1356.221uk5m5qbzj1vt sqlplus@maclean1.oracle.com (TNS V1-V3)   BEGIN dbms_workload_repository...
1.7511.750.254.1742.0252.226ajkhukk78nsr     begin prvt_hdm.auto_execute( :...
0.46540.010.070.5289.190.00fnq8p3fj3r5as     select /*+ no_monitor */ job, ...
0.40160.020.060.4295.540.0007vdtsr7c56qr sqlplus@maclean1.oracle.com (TNS V1-V3)   select count(*) from lgwr_pres...
0.2820.140.040.2997.680.00f5dqupu2hknzu     WITH a as (SELECT a$.dbid, a$....
0.2640.070.040.3869.6822.64fhf8upax5cxsz MMON_SLAVE   BEGIN sys.dbms_auto_report_int...
0.2140.050.030.2972.3720.240w26sk6t6gq98 MMON_SLAVE   SELECT XMLTYPE(DBMS_REPORT.GET...
0.1310.130.023.273.9195.15adzjh275fvvx4 DBMS_SCHEDULER MACC call WWV_FLOW_WORKSHEET_API.DO...


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
3.1113.1123.223.273.9195.15adzjh275fvvx4 DBMS_SCHEDULER MACC call WWV_FLOW_WORKSHEET_API.DO...
2.9812.9822.255.3139.1356.221uk5m5qbzj1vt sqlplus@maclean1.oracle.com (TNS V1-V3)   BEGIN dbms_workload_repository...
2.1912.1916.362.354.7393.53adzjh275fvvx4 DBMS_SCHEDULER   call WWV_FLOW_WORKSHEET_API.DO...
2.1812.1816.254.1742.0252.226ajkhukk78nsr     begin prvt_hdm.auto_execute( :...
1.48260.0611.061.512.7998.51cvn54b7yz0s8u     select /*+ index(idl_ub1$ i_id...
1.3811.3810.291.422.4097.47aahsjvcnr3swn     merge /*+ dynamic_sampling(mm...
1.1630.398.63742.0892.560.1673kc99v9699nm sqlplus@maclean1.oracle.com (TNS V1-V3)   begin loop insert into lgwr_pr...
1.164,049,2090.008.63401.8996.020.29cfrfu4058yard sqlplus@maclean1.oracle.com (TNS V1-V3)   INSERT INTO LGWR_PRESS VALUES(...
0.8710.876.490.902.4596.99c2h8tpm85b5gb DBMS_SCHEDULER MACC SELECT VALUE FROM V$PARAMETER ...
0.870 6.470.891.8097.655cbpmbnnf80md   MACC select NUM , NAME , TYPE , VAL...
0.870 6.470.881.5898.036c6d071msgfgy   MACC select x.inst_id, x.indx+1, ks...
0.79310.035.910.790.2599.7296g93hntrzjtr   MACC select /*+ rule */ bucket_cnt,...
0.77100.085.770.780.9099.193un99a0zwp4vd   MACC select owner#, name, namespace...
0.67320.024.980.681.3298.233un99a0zwp4vd     select owner#, name, namespace...
0.51260.023.780.511.3799.22ga9j9xk5cy9s0     select /*+ index(idl_sb4$ i_id...
0.442910.003.290.4712.0393.207u49y06aqxg1s     select /*+ rule */ bucket, end...
0.44260.023.270.440.9199.241p5grz1gs7fjq   MACC select obj#, type#, ctime, mti...
0.40320.012.970.402.9798.578swypbbr0m372     select order#, columns, types ...
0.3810.382.800.394.0595.262gvu2zh884p5w DBMS_SCHEDULER MACC SELECT ID, WORKSHEET_ID, WEBSH...
0.289550.002.080.4123.3568.483c1kubcdjnppq     update sys.col_usage$ set equa...
0.26330.011.910.261.9398.567u49y06aqxg1s   MACC select /*+ rule */ bucket, end...
0.26260.011.900.263.4798.4539m4sx9k63ba2     select /*+ index(idl_ub2$ i_id...
0.2420.121.800.3020.2780.415h91zx386wbht     WITH awr_params as ( SELECT /*...
0.2410.241.760.254.7893.922gvu2zh884p5w DBMS_SCHEDULER   SELECT ID, WORKSHEET_ID, WEBSH...
0.2020.101.500.2520.5181.126cp74g22fzahf     WITH ash as (SELECT ash.event...
0.1520.081.130.2028.8873.956wm3n4d7bnddg     SELECT source, (case when tim...


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
12,381,89634,127,298.6798.85742.0892.6.273kc99v9699nm sqlplus@maclean1.oracle.com (TNS V1-V3)   begin loop insert into lgwr_pr...
8,332,2644,049,2092.0666.52401.8996.3cfrfu4058yard sqlplus@maclean1.oracle.com (TNS V1-V3)   INSERT INTO LGWR_PRESS VALUES(...
101,513166,344.560.810.4295.5007vdtsr7c56qr sqlplus@maclean1.oracle.com (TNS V1-V3)   select count(*) from lgwr_pres...
19,950119,950.000.165.3139.156.21uk5m5qbzj1vt sqlplus@maclean1.oracle.com (TNS V1-V3)   BEGIN dbms_workload_repository...
15,196115,196.000.124.174252.26ajkhukk78nsr     begin prvt_hdm.auto_execute( :...
3,3091,1033.000.030.155244.986708bvah4akq     select name from undo$ where f...
3,2059553.360.030.4123.468.53c1kubcdjnppq     update sys.col_usage$ set equa...
3,10713,107.000.022.354.793.5adzjh275fvvx4 DBMS_SCHEDULER   call WWV_FLOW_WORKSHEET_API.DO...
3,05713,057.000.023.273.995.2adzjh275fvvx4 DBMS_SCHEDULER MACC call WWV_FLOW_WORKSHEET_API.DO...
2,12221,061.000.020.2520.581.16cp74g22fzahf     WITH ash as (SELECT ash.event...


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
1,78511,785.0071.925.3139.1356.221uk5m5qbzj1vt sqlplus@maclean1.oracle.com (TNS V1-V3)   BEGIN dbms_workload_repository...
1,54011,540.0062.054.1742.0252.226ajkhukk78nsr     begin prvt_hdm.auto_execute( :...
5902295.0023.770.3020.2780.415h91zx386wbht     WITH awr_params as ( SELECT /*...
4952247.5019.940.2520.5181.126cp74g22fzahf     WITH ash as (SELECT ash.event...
1351135.005.443.273.9195.15adzjh275fvvx4 DBMS_SCHEDULER MACC call WWV_FLOW_WORKSHEET_API.DO...
112264.314.511.512.7998.51cvn54b7yz0s8u     select /*+ index(idl_ub1$ i_id...
1062910.364.270.4712.0393.207u49y06aqxg1s     select /*+ rule */ bucket, end...
76176.003.062.354.7393.53adzjh275fvvx4 DBMS_SCHEDULER   call WWV_FLOW_WORKSHEET_API.DO...
48148.001.931.422.4097.47aahsjvcnr3swn     merge /*+ dynamic_sampling(mm...
48412.001.930.3869.6822.64fhf8upax5cxsz MMON_SLAVE   BEGIN sys.dbms_auto_report_int...
46411.501.850.2972.3720.240w26sk6t6gq98 MMON_SLAVE   SELECT XMLTYPE(DBMS_REPORT.GET...
46411.501.850.2060.1328.777r24h5ucyjggz MMON_SLAVE   WITH MONITOR_DATA AS (SELECT I...
45222.501.810.2028.8873.956wm3n4d7bnddg     SELECT source, (case when tim...
34217.001.370.1722.2277.9614wnf35dahb7v     SELECT A.ID, A.TYPE FROM SYS.W...
319550.031.250.4123.3568.483c1kubcdjnppq     update sys.col_usage$ set equa...


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
2,2042,2043734.670.00160.7673kc99v9699nm sqlplus@maclean1.oracle.com (TNS V1-V3)   begin loop insert into lgwr_pr...
2,2042,2044,049,2090.000.00160.76cfrfu4058yard sqlplus@maclean1.oracle.com (TNS V1-V3)   INSERT INTO LGWR_PRESS VALUES(...
8188181818.000.0059.661uk5m5qbzj1vt sqlplus@maclean1.oracle.com (TNS V1-V3)   BEGIN dbms_workload_repository...
4334331433.000.0031.586ajkhukk78nsr     begin prvt_hdm.auto_execute( :...
1351351135.000.009.85adzjh275fvvx4 DBMS_SCHEDULER MACC call WWV_FLOW_WORKSHEET_API.DO...
112112264.310.008.17cvn54b7yz0s8u     select /*+ index(idl_ub1$ i_id...
1061062910.360.007.737u49y06aqxg1s     select /*+ rule */ bucket, end...
103103425.750.007.51fhf8upax5cxsz MMON_SLAVE   BEGIN sys.dbms_auto_report_int...
7676176.000.005.54adzjh275fvvx4 DBMS_SCHEDULER   call WWV_FLOW_WORKSHEET_API.DO...
7373418.250.005.320w26sk6t6gq98 MMON_SLAVE   SELECT XMLTYPE(DBMS_REPORT.GET...
7373418.250.005.327r24h5ucyjggz MMON_SLAVE   WITH MONITOR_DATA AS (SELECT I...
4747147.000.003.43aahsjvcnr3swn     merge /*+ dynamic_sampling(mm...
3434217.000.002.4814wnf35dahb7v     SELECT A.ID, A.TYPE FROM SYS.W...
31319550.030.002.263c1kubcdjnppq     update sys.col_usage$ set equa...
191929.500.001.3945bv8pz1znkhr     UPDATE WRI$_ADV_RECOMMENDATION...
1818118.000.001.31c2h8tpm85b5gb DBMS_SCHEDULER MACC SELECT VALUE FROM V$PARAMETER ...
17170 0.001.245cbpmbnnf80md   MACC select NUM , NAME , TYPE , VAL...
17170 0.001.246c6d071msgfgy   MACC select x.inst_id, x.indx+1, ks...
1616260.620.001.1739m4sx9k63ba2     select /*+ index(idl_ub2$ i_id...
1616320.500.001.173un99a0zwp4vd     select owner#, name, namespace...


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
4,049,2094,049,2841.00401.8996.3cfrfu4058yard sqlplus@maclean1.oracle.com (TNS V1-V3)   INSERT INTO LGWR_PRESS VALUES(...
1,1031,1031.000.155244.986708bvah4akq     select name from undo$ where f...
9559511.000.4123.468.53c1kubcdjnppq     update sys.col_usage$ set equa...
2914,33414.890.471293.27u49y06aqxg1s     select /*+ rule */ bucket, end...
1541541.000.0449.258.63m8smr0v7v1m6     INSERT INTO sys.wri$_adv_messa...
1261261.000.02750f80h0xb1qvbsk     SELECT sys.wri$_adv_seq_msggro...
1211521.260.077.691.653saa2zkr6wc3     select intcol#, nvl(pos#, 0), ...
1091091.000.0370.40bb926a5dcb8kr     merge into sys.mon_mods$ m usi...
101950.940.075.791.21p5grz1gs7fjq     select obj#, type#, ctime, mti...
1001001.000.0174.706xvp6nxs4a9n4     select nvl(sum(space), 0) from...


Back to SQL Statistics
Back to Top

SQL ordered by Parse Calls

Parse CallsExecutions % Total Parses SQL IdSQL ModulePDB NameSQL Text
1,1031,10322.4886708bvah4akq     select name from undo$ where f...
378547.70fnq8p3fj3r5as     select /*+ no_monitor */ job, ...
33146.750v3dvmc22qnam     insert into sys.col_usage$ (ob...
3319556.753c1kubcdjnppq     update sys.col_usage$ set equa...
1541543.143m8smr0v7v1m6     INSERT INTO sys.wri$_adv_messa...
1261262.57f80h0xb1qvbsk     SELECT sys.wri$_adv_seq_msggro...
1091092.22bb926a5dcb8kr     merge into sys.mon_mods$ m usi...
1001002.046xvp6nxs4a9n4     select nvl(sum(space), 0) from...
1001002.04csnp95dz2r8ss     select file#, block# from recy...
92921.883y6pgnk2ubw7g     insert into wrm$_snapshot_deta...
60601.220hhmdwwgxbw0r     select obj#, type#, flags, rel...
60601.22at1ygf4rd7cvj     select file#, block#, blocks f...
54541.100qc1hv1q61pu6 oraagent.bin@maclean1.oracle.com (TNS V1-V3)   BEGIN DBMS_SESSION.USE_DEFAULT...
54541.105sg7mjrpj21z7     SELECT JOB, LAST_DATE, THIS_DA...
54541.105sg7mjrpj21z7   MACC SELECT JOB, LAST_DATE, THIS_DA...


Back to SQL Statistics
Back to Top

SQL ordered by Sharable Memory

Sharable Mem (b)Executions % Total SQL IdSQL ModulePDB NameSQL Text
4,088,10140.377r24h5ucyjggz MMON_SLAVE   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

SQL ordered by Cluster Wait Time

No data exists for this section of the report.

Back to SQL Statistics
Back to Top

Complete List of SQL Text

SQL IdSQL Text
07vdtsr7c56qrselect count(*) from lgwr_press
0hhmdwwgxbw0rselect obj#, type#, flags, related, bo, purgeobj, con# from RecycleBin$ where ts#=:1 and to_number(bitand(flags, 16)) = 16 order by dropscn
0qc1hv1q61pu6BEGIN DBMS_SESSION.USE_DEFAULT_EDITION_DEFERRED; END;
0v3dvmc22qnaminsert into sys.col_usage$ (obj#, intcol#, equality_preds, equijoin_preds, nonequijoin_preds, range_preds, like_preds, null_preds, timestamp) values ( :objn, :coln, decode(bitand(:flag, 1), 0, 0, 1), decode(bitand(:flag, 2), 0, 0, 1), decode(bitand(:flag, 4), 0, 0, 1), decode(bitand(:flag, 8), 0, 0, 1), decode(bitand(:flag, 16), 0, 0, 1), decode(bitand(:flag, 32), 0, 0, 1), :time)
0w26sk6t6gq98SELECT XMLTYPE(DBMS_REPORT.GET_REPORT_WITH_SUMMARY(:B1 )) FROM DUAL
14wnf35dahb7vSELECT A.ID, A.TYPE FROM SYS.WRI$_ADV_DEFINITIONS A WHERE A.NAME = :B1
1p5grz1gs7fjqselect obj#, type#, ctime, mtime, stime, status, dataobj#, flags, oid$, spare1, spare2, spare3, signature, spare7, spare8, spare9 from obj$ where owner#=:1 and name=:2 and namespace=:3 and remoteowner is null and linkname is null and subname is null
1uk5m5qbzj1vtBEGIN dbms_workload_repository.create_snapshot; END;
2gvu2zh884p5wSELECT ID, WORKSHEET_ID, WEBSHEET_ID, REPORT_ID, EMAIL_SUBJECT, EMAIL_FROM, EMAIL_ADDRESS, NOTIFY_INTERVAL, START_DATE, END_DATE, OFFSET_DATE, LANGUAGE, CREATED_BY, SECURITY_GROUP_ID FROM WWV_FLOW_WORKSHEET_NOTIFY WHERE SYSTIMESTAMP BETWEEN NVL(START_DATE, SYSTIMESTAMP) AND NVL(END_DATE, SYSTIMESTAMP) AND EMAIL_ADDRESS IS NOT NULL
39m4sx9k63ba2select /*+ index(idl_ub2$ i_idl_ub21) +*/ piece#, length, piece from idl_ub2$ where obj#=:1 and part=:2 and version=:3 order by piece#
3c1kubcdjnppqupdate sys.col_usage$ set equality_preds = equality_preds + decode(bitand(:flag, 1), 0, 0, 1), equijoin_preds = equijoin_preds + decode(bitand(:flag, 2), 0, 0, 1), nonequijoin_preds = nonequijoin_preds + decode(bitand(:flag, 4), 0, 0, 1), range_preds = range_preds + decode(bitand(:flag, 8), 0, 0, 1), like_preds = like_preds + decode(bitand(:flag, 16), 0, 0, 1), null_preds = null_preds + decode(bitand(:flag, 32), 0, 0, 1), timestamp = :time where obj# = :objn and intcol# = :coln
3m8smr0v7v1m6INSERT INTO sys.wri$_adv_message_groups (task_id, id, seq, message#, fac, hdr, lm, nl, p1, p2, p3, p4, p5) VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13)
3un99a0zwp4vdselect owner#, name, namespace, remoteowner, linkname, p_timestamp, p_obj#, nvl(property, 0), subname, type#, flags, d_attrs from dependency$ d, obj$ o where d_obj#=:1 and p_obj#=obj#(+) order by order#
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)
45bv8pz1znkhrUPDATE WRI$_ADV_RECOMMENDATIONS SET FILTERED = 'Y' WHERE TASK_ID = :B1 AND ID IN (SELECT REC_ID FROM (SELECT A.REC_ID AS REC_ID, COUNT(*) AS TOTAL_CNT, SUM(CASE WHEN A.FILTERED = 'Y' THEN 1 ELSE 0 END) AS FILT_CNT FROM DBA_ADVISOR_ACTIONS A WHERE A.TASK_ID = :B1 GROUP BY A.REC_ID) WHERE TOTAL_CNT = FILT_CNT)
53saa2zkr6wc3select intcol#, nvl(pos#, 0), col#, nvl(spare1, 0) from ccol$ where con#=:1
5cbpmbnnf80mdselect NUM , NAME , TYPE , VALUE , DISPLAY_VALUE, ISDEFAULT , ISSES_MODIFIABLE , ISSYS_MODIFIABLE , ISPDB_MODIFIABLE , ISINSTANCE_MODIFIABLE, ISMODIFIED , ISADJUSTED , ISDEPRECATED, ISBASIC, DESCRIPTION, UPDATE_COMMENT, HASH, CON_ID from GV$PARAMETER where inst_id = USERENV('Instance')
5h91zx386wbht WITH awr_params as ( SELECT /*+ NO_MERGE(map) USE_HASH(map) */ p.parameter_hash as parameter_hash, max(map.parameter_name) as parameter_name, max (map.keh_id) as keh_id, max (case when p.snap_id = :bid then upper(p.value) else null end) as first_value, max (case when p.snap_id = :eid then upper(p.value) else null end) as last_value, max (case when p.isdefault = 'TRUE' then upper(p.value) else null end) as default_value, max (upper(p.value)) as max_value, min (upper(p.value)) as min_value, max (case when p.snap_id = :bid then decode(p.ismodified, 'FALSE', 0, 'MODIFIED', 1, 4) else null end) as first_is_mod, max (case when p.snap_id = :eid then decode(p.ismodified, 'FALSE', 0, 'MODIFIED', 1, 4) else null end) as last_is_mod FROM ( SELECT /*+ USE_HASH(xmap) */ xmap.keh_id as keh_id, xmap.parameter_hash as parameter_hash, pn.parameter_name as parameter_name FROM X$KEHPRMMAP xmap , WRH$_PARAMETER_NAME pn WHERE pn.dbid = :dbid AND pn.parameter_hash = xmap.parameter_hash ) map, WRH$_PARAMETER p WHERE p.parameter_hash = map.parameter_hash AND p.dbid = :dbid AND p.instance_number = :inst AND p.snap_id >= :bid AND p.snap_id <= :eid GROUP BY p.parameter_hash ), def_params as (SELECT p.hash as parameter_hash, p.name as parameter_name, x.keh_id as keh_id, decode(p.name, 'db_block_size', '8192', 'gcs_server_processes', '1', 'disk_asynch_io', 'TRUE', '0') as value FROM X$KEHPRMMAP x , V$PARAMETER p WHERE x.parameter_hash = p.hash AND (p.name like '%size' OR p.name like '%target' OR p.name in ('gcs_server_processes', 'dis k_asynch_io')) ) SELECT parameter_hash, nvl(a.parameter_name, p.parameter_name) as pname, nvl(a.keh_id, p.keh_id) as keh_id, case when a.keh_id is null then p.value else a.first_value end as first_value, case when a.keh_id is null then p.value else a.last_value end as last_value, case when a.keh_id is null then p.value else a.default_value end as default_value, case when a.keh_id is null then p.value else a.max_value end as min_value, case when a.keh_id is null then p.value else a.min_value end as min_value, case when a.keh_id is null then 0 else a.first_is_mod end as first_is_mod, case when a.keh_id is null then 0 else a.last_is_mod end as last_is_mod FROM awr_params a full outer join def_params p using(parameter_hash)
5sg7mjrpj21z7SELECT JOB, LAST_DATE, THIS_DATE, NEXT_DATE, FIELD1 FROM SYS."JOB$_REDUCED" "JOB$_REDUCED" WHERE "JOB$_REDUCED"."THIS_DATE" IS NULL AND ("JOB$_REDUCED"."LAST_DATE" IS NULL AND "JOB$_REDUCED"."NEXT_DATE"<:1 OR "JOB$_REDUCED"."NEXT_DATE">=:2 AND "JOB$_REDUCED"."NEXT_DATE"<=:3) AND ("JOB$_REDUCED"."FIELD1"=:4 OR "JOB$_REDUCED"."FIELD1"=0 AND 'Y'=:5) AND ("JOB$_REDUCED"."JOB"<1000000000 AND "SYS"."DBMS_LOGSTDBY"."DB_IS_LOGSTDBY"()=0 OR "JOB$_REDUCED"."JOB">=1000000000 AND "SYS"."DBMS_LOGSTDBY"."DB_IS_LOGSTDBY"()=1)
6ajkhukk78nsrbegin prvt_hdm.auto_execute( :dbid, :inst_num , :end_snap_id ); end;
6c6d071msgfgy select x.inst_id, x.indx+1, ksppinm, ksppity, ksppstvl, ksppstdvl, ksppstdf, decode(bitand(ksppiflg/256, 1), 1, 'TRUE', 'FALSE'), decode(bitand(ksppiflg/65536, 3), 1, 'IMMEDIATE', 2, 'DEFERRED', 3, 'IMMEDIATE', 'FALSE'), decode(bitand(ksppiflg/524288, 1), 1, 'TRUE', 'FALSE'), decode(bitand(ksppiflg, 4), 4, 'FALSE', decode(bitand(ksppiflg/65536, 3), 0, 'FALSE', 'TRUE')), decode(bitand(ksppstvf, 7), 1, 'MODIFIED', 4, 'SYSTEM_MOD', 'FALSE'), decode(bitand(ksppstvf, 2), 2, 'TRUE', 'FALSE'), decode(bitand(ksppilrmflg/64, 1), 1, 'TRUE', 'FALSE'), decode(bitand(ksppilrmflg/268435456, 1), 1, 'TRUE', 'FALSE'), ksppdesc, ksppstcmnt, ksppihash, x.con_id from x$ksppi x, x$ksppcv y where (x.indx = y.indx) and bitand(ksppiflg, 268435456) = 0 and ((translate(ksppinm, '_', '#') not like '##%') and ((translate(ksppinm, '_', '#') not like '#%') or (ksppstdf = 'FALSE') or (bitand(ks ppstvf, 5) > 0)))
6cp74g22fzahf WITH ash as (SELECT ash.event_id, sum(decode(ash.session_type, 1, 1, 0)) as cnt_fg, count(*) as cnt_total FROM WRH$_ACTIVE_SESSION_HISTORY ash WHERE ash.dbid = :dbid AND ash.instance_number = :inst AND ash.snap_id > :bid AND ash.snap_id <= :eid AND ash.wait_time = 0 GROUP BY ash.event_id ), evts as (SELECT event_id, sum(w_cnt) as w_cnt, sum(to_cnt) as to_cnt, sum(w_time) as w_time , sum(w_cnt_fg) as w_cnt_fg, sum(to_cnt_fg) as to_cnt_fg, sum(w_time_fg) as w_time_fg FROM (SELECT es.event_id as event_id, es.total_waits - nvl(bs.total_waits, 0) as w_cnt, es.TOTAL_TIMEOUTS - nvl(bs.TOTAL_TIMEOUTS, 0) as to_cnt, es.TIME_WAITED_MICRO - nvl(bs.TIME_WAITED_MICRO, 0) as w_time , es.total_waits_fg - nvl(bs.total_waits_fg, 0) as w_cnt_fg, es.TOTAL_TIMEOUTS_FG - nvl(bs.TOTAL_TIMEOUTS_FG, 0) as to_cnt_fg, es.TIME_WAITED_MICRO_FG - nvl(bs.TIME_WAITED_MICRO_FG, 0) as w_time_fg FROM WRH$_SYSTEM_EVENT es , WRH$_SYSTEM_EVENT bs WHERE es.dbid = :db id AND es.instance_number = :inst AND es.snap_id = :eid AND bs.dbid(+) = :dbid AND bs.instance_number(+) = es.instance_number AND bs.snap_id(+) = :bid AND es.instance_number = bs.instance_number(+) AND es.event_id = bs.event_id(+) UNION ALL SELECT x.event_hash as event_id, 0 as w_cnt, 0 as to_cnt, 0 as w_time , 0 as w_cnt_fg, 0 as to_cnt_fg, 0 as w_time_fg FROM X$KEHEVTMAP x ) GROUP BY event_id ), awr_names as (SELECT e.event_id, e.event_name, e.wait_class_id, e.parameter1, e.parameter2, e.parameter3 FROM WRH$_EVENT_NAME e WHERE e.dbid = :dbid ), named_events as (SELECT names.event_id as event_id, names.event_name as event_name, evts.w_cnt as w_cnt, evts.to_cnt as to_cnt, evts.w_time as w_time, evts.w_cnt_fg as w_cnt_fg, evts.to_cnt_fg as to_cnt_fg, evts.w_time_fg as w_time_fg, names.wait_class_id as wait_class_id, names.parameter1 as p1, names.parameter2 as p2, names.parameter3 as p3 FROM evts, awr_names names WHERE evts. event_id = names.event_id AND names.wait_class_id <> 2723168908 ), data as (SELECT e.event_id as event_id, e.event_name as event_name, e.wait_class_id as wait_class_id, e.p1 as p1, e.p2 as p2, e.p3 as p3, e.w_cnt as total_w_cnt, e.to_cnt as total_to_cnt, e.w_time as total_w_time, e.w_cnt_fg as fg_w_cnt, e.to_cnt_fg as fg_to_cnt, e.w_time_fg as fg_w_time, nvl(a.cnt_total, 0) as total_ash_cnt, nvl(a.cnt_fg, 0) as fg_ash_cnt FROM named_events e, ash a WHERE e.event_id = a.event_id(+) ), final_query as (SELECT d.event_name, d.event_id, d.p1 as p1, case when (p1 is null OR p1 = '0') then 0 else 1 end as p1valid, d.p2 as p2, case when (p2 is null OR p2 = '0') then 0 else 1 end as p2valid, d.p3 as p3, case when (p3 is null OR p3 = '0') then 0 else 1 end as p3valid, nvl(e.keh_id, 0) as keh_evt_id, c.class# as class_num, d.wait_class_id as wait_class_id, c.keh_id as keh_ecl_id, round(d.total_ash_cnt, 0) as total_ash_cnt , round(d.fg_ash_cnt, 0) as fg_ash_cnt, round(d.fg_w_cnt, 0) as fg_w_cnt, round(d.fg_to_cnt, 0) as fg_to_cnt, round(d.fg_w_time, 0) as fg_w_time, round(d.total_w_cnt, 0) as total_w_cnt, round(d.total_to_cnt, 0) as total_to_cnt, round(d.total_w_time, 0) as total_w_time from data d, X$KEHEVTMAP e , X$KEHECLMAP c where d.event_id = e.event_hash(+) and d.wait_class_id = c.class_hash ) SELECT * FROM final_query WHERE keh_evt_id > 0 OR total_w_time > 0 ORDER BY wait_class_id, fg_w_time DESC, event_id
6wm3n4d7bnddg SELECT source, (case when time_secs < 1 then 1 else time_secs end) as time_secs, operation FROM ( SELECT 1 as source, trunc((sysdate - cast(ll.log_date as date)) * 86400) as time_secs, decode(ll.operation, 'OPEN', 0 , 1 ) as operation, ll.log_id as log_id FROM DBA_SCHEDULER_WINDOW_LOG ll , ( SELECT max(l.log_id) as max_log_id FROM DBA_SCHEDULER_WINDOW_LOG l , DBA_SCHEDULER_WINGROUP_MEMBERS m WHERE l.window_name = m.window_name AND m.window_group_name = 'MAINTENANCE_WINDOW_GROUP' AND l.operation in ('OPEN', 'CLOSE') AND CAST(l.log_date AS DATE) < ( SELECT cast(min(end_interval_time) as date) as btime FROM WRM$_SNAPSHOT bsi WHERE bsi.dbid = :dbid AND bsi.instance_number = :inst AND bsi. snap_id = :bid ) ) max_log WHERE ll.log_id = max_log.max_log_id UNION ALL SELECT 2 as source, trunc((sysdate - cast(l.log_date as date)) * 86400) as time_secs, decode(l.operation, 'OPEN', 0 , 1 ) as operation, l.log_id as log_id FROM DBA_SCHEDULER_WINDOW_LOG l , DBA_SCHEDULER_WINGROUP_MEMBERS m WHERE l.window_name = m.window_name AND m.window_group_name = 'MAINTENANCE_WINDOW_GROUP' AND l.operation in ('OPEN', 'CLOSE') AND CAST(l.log_date AS DATE) <= ( SELECT cast(max(end_interval_time) as date) as etime FROM WRM$_SNAPSHOT esi WHERE esi.dbid = :dbid AND esi.instance_number = :inst AND esi.snap_id = :eid ) AND CAST(l.log_date AS DATE) >= ( SELECT cast(min(end_interval_time) as date) as btime FROM WRM$_SNAPSHOT bsi WHERE bsi.dbid = :dbid AND bsi.instance_number = :inst AND bsi.snap_id = :bid ) UNION ALL SELECT 0 as source, trunc((sysdate - et.etime) * 86400) as time_secs, 3 as operation, 0 as log_id FROM ( SELECT cast(max(end_interval_time) as date) as etime FROM WRM$_SNAPSHOT esi WHERE esi.dbid = :dbid AND esi.instance_number = :inst AND esi.snap_id = :eid ) et UNION ALL SELECT 0 as source, trunc((sysdate - bt.btime) * 86400) as time_secs, 2 as operation, 0 as log_id FROM ( SELECT cast(min(end_interval_time) as date) as btime FROM WRM$_SNAPSHOT bsi WHERE bsi.dbid = :dbid AND bsi.instance_number = :inst AND bsi.snap_id = :bid ) bt ) WHERE time_secs >= 0 ORDER B Y source ASC, time_secs DESC, log_id ASC
6xvp6nxs4a9n4select nvl(sum(space), 0) from recyclebin$ where ts# = :1
73kc99v9699nmbegin loop insert into lgwr_press values(lgwr_seq.nextval) ; commit; end loop; end;
7r24h5ucyjggz 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, 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+ CONCURRENCY_WAIT_TIME+ CLUST ER_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, 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_ALLOCATED 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_IN ST_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_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 WHERE P.OBJECT_ID = MAX_PL_OID AND P.SUBPROGRAM_ID = MAX_PL_PROGID), '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), '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, 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(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, MA X(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(CONCURREN CY_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, 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.BUCKE T_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.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_COUNT / 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.OTHER_SQL_ACTIVITY_TYPE, ASH.EVENT_NAME, 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_I D, 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_LEVEL_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.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(GVT F.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 USERENV('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_I D, 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.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_SUBPROGRA M_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 != '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' 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) DB OP_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.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.C 2, 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 FROM 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.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 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 ASH000.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_MONITORE D_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 ELSE 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.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 (ASH 0.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_NAME = :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 Server(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_OTHER_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_S AMPLES, NULL)) OVER(PARTITION BY AD01.BUCKET_NUM)), 0)) BUCKET_OTHER_ACTIVITY, (NVL(AD01.BUCKET_DISTINCT_SAMPLES, 0) * :B25 ) BUCKET_ACTIVE_SECONDS, DECODE(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_DISTINCT_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_count", :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", MAGG.DB_UNIQUE_NAME AS "db_unique_name", MAGG.PLATFORM_NAME AS "db_platform_name", MAGG.HOST_NAME AS "rep ort_host_name"), NVL2(SUM_SERVERS_REQUESTED, XMLELEMENT( "servers_requested", 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_CLIENT_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(:B63 , 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))), 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(:B64 ) 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", XMLATTRIBUTES('sys' AS "type"), XMLAGG( XMLELEMENT( "pa ram", 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_APPLICATI ON_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_B YTES, 0), 0, NULL, XMLELEMENT( "stat", XMLATTRIBUTES('write_bytes' AS "name"), SUM_WRITE_BYTES)), CASE WHEN SUM_IO_INTER_BYTES IS NULL OR SUM_IO_BYTES = 0 OR SUM_IO_INTER_BYTES = SUM_IO_BYTES 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), 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 (SELECT 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.ACTIVITY_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.WAIT_COUNT AS "wait_count", AT.CPU_COUNT AS "cpu_count", D ECODE(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.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) ACTI VITY_START, MAX(AD0.ACTIVITY_END) ACTIVITY_END, SUM(AD0.ACTIVITY_COUNT) ACTIVITY_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(( :B22 - :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(WAIT_COUNT) WAIT_COUNT, SUM(CPU_COUNT) CPU_COUNT, SUM(OTHER_SQL_COUNT) OTHER_SQL_COUNT, MIN(AD1.ACTIVITY_START) ACTIVITY_START, MAX(AD1.A CTIVITY_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.ACTIVITY_START) ACTIVITY_START, MAX(AD0.ACTIVITY_END) ACTIVITY_END, SUM(AD0.ACTIVITY_COUNT) ACTIVITY_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 W HEN :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 WHEN 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 :B65 = 'Y' THEN AD1.DFO_MOST_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, NU LL, 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 Execution' 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_DA TA 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 :B67 = 1 THEN PX_SESSIONS ELSE NULL END, CASE WHEN :B65 = 'Y' THEN DECODE(:B66 , 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_SES SION.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.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_nam e", SID AS "session_id", SESSION_SERIAL# AS "session_serial", PX_SERVER_GROUP AS "server_group", PX_SERVER_SET AS "server_set", PX_SERVER# AS "server_num"), XMLELEMENT( "stats", XMLATTRIBUTES( 'monitor' AS "type"), NVL2(MAX(ELAPSED_TIME), XMLELEMENT( "stat", XMLATTRIBUTES('elapsed_time' AS "name"), MAX(ELAPSED_TIME)), NULL), NVL2(MAX(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", 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), CASE WHEN MAX(IO_INTER_BYTES) IS NULL OR NVL(MAX(IO_BYTES), 0) = 0 OR MAX(IO_INTER_BYTES) = MAX(IO_BYTES) THEN NULL ELSE XMLELEMENT( "stat", XMLATTRIBUTES('cell_offload_ef ficiency' AS "name"), ROUND(MAX(IO_BYTES) / DECODE(MAX(IO_INTER_BYTES), 0, 1, MAX(IO_INTER_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.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 :B 61 = 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_BUFFER_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.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.APPLICATION_WAIT_TIME, 0, NULL, MO.APPLICATION_WAIT_TIME)) APPLICATION_WAIT_TIME, MAX(DECODE(MO.CONCURRENCY_WAIT_TIME, 0, NULL, MO.CONCURRENCY_WAIT_TI ME)) CONCURRENCY_WAIT_TIME, MAX(DECODE(MO.CLUSTER_WAIT_TIME, 0, NULL, MO.CLUSTER_WAIT_TIME)) CLUSTER_WAIT_TIME, MAX(DECODE(MO.USER_IO_WAIT_TIME, 0, NULL, MO.USER_IO_WAIT_TIME)) USER_IO_WAIT_TIME, MAX(DECODE(PLSQL_EXEC_TIME, 0, NULL, PLSQL_EXEC_TIME)) PLSQL_EXEC_TIME, MAX(DECODE(MO.JAVA_EXEC_TIME, 0, NULL, MO.JAVA_EXEC_TIME)) JAVA_EXEC_TIME, MAX(DECODE(MO.OTHER_WAIT_TIME, 0, NULL, MO.OTHER_WAIT_TIME)) OTHER_WAIT_TIME FROM MONITOR_DATA MO, (SELECT ASH1.INST_ID, ASH1.SESSION_ID, ASH1.ACTIVITY_TYPE, ASH1.EVENT_NAME, SUM(ASH1.ACTIVITY_COUNT) ACTIVITY_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 (:B67 = 1 OR :B66 = 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 (:B67 = 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.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.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_WA IT_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", 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), CASE WHEN MAX(IO_INTER_BYTES) IS NULL OR NVL(MAX(IO_BYT ES), 0) = 0 OR MAX(IO_INTER_BYTES) = MAX(IO_BYTES) 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 :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.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.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.USER_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 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) WRI TE_REQS, SUM(MO0.WRITE_BYTES) WRITE_BYTES, SUM(MO0.IO_REQS) IO_REQS, SUM(MO0.APPLICATION_WAIT_TIME) APPLICATION_WAIT_TIME, SUM(MO0.CONCURRENCY_WAIT_TIME) CONCURRENCY_WAIT_TIME, SUM(MO0.CLUSTER_WAIT_TIME) CLUSTER_WAIT_TIME, SUM(MO0.USER_IO_WAIT_TIME) USER_IO_WAIT_TIME, SUM(MO0.PLSQL_EXEC_TIME) PLSQL_EXEC_TIME, SUM(MO0.JAVA_EXEC_TIME) JAVA_EXEC_TIME, SUM(MO0.OTHER_WAIT_TIME) OTHER_WAIT_TIME FROM MONITOR_DATA MO0 GROUP BY MO0.INST_ID) MO, (SELECT ASH0.INST_ID, ASH0.ACTIVITY_TYPE, ASH0.EVENT_NAME, SUM(ASH0.ACTIVITY_COUNT) ACTIVITY_COUNT, SUM(ASH0.WAIT_COUNT) WAIT_COUNT, 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_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.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('fi rst_active' AS "name"), TO_CHAR(FIRST_MOVE_TIME, :B12 )), NULL), CASE WHEN PLI.FIRST_MOVE_TIME != PLI.FIRST_CHANGE_TIME THEN XMLELEMENT( "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", XMLATTRIBUTES('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(:B65 , '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(:B65 , '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(:B65 , '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(:B65 , '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 E ND, CASE WHEN PLI.READ_REQS > 0 THEN XMLELEMENT( "stat", XMLATTRIBUTES('read_reqs' AS "name"), PLI.READ_REQS) ELSE NULL 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(:B65 , '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(:B65 , '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", XMLATTRIBUTES('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(:B65 , '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(:B65 , '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, 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('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(:B65 , '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( "rwsstats", 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 - P LI.LINE_ACTIVITY_START) * 3600*24) + 1 AS "duration", PLI.LINE_ACTIVITY_COUNT AS "count", 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_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, 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_CH ANGE_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, PM.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_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_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.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.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) D FO_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.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", XMLATTRIBUTES( 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.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.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 :B68 = 'DONE (ERROR)') AND LO.SQL_ID = :B9 AND LO.SQL_EXEC_START = :B8 AND 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_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.PLAN_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) FI RST_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) MAX_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.PHYS ICAL_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, MAX(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)), 10000000000000000), 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_T YPE), 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(OTHERSTAT_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 , SESSI ON_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.D FO_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 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_NA ME 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 "number", MA.MAX_ERROR_FACILITY AS "facility"), MA.MAX_ERROR_MESSAGE) END, XMLELEMENT( "stats", XMLATTRIBUTES('monitor' AS "type"), XMLELEMENT( "stat", XMLATTRIBUTE S('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)), 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", X MLATTRIBUTES('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 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) ) 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 A S "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.SU M_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.SU M_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 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) ) 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') ELSE NULL END XPLAN_XML FROM DUAL) V1) CONST_VIEW
7u49y06aqxg1sselect /*+ rule */ bucket, endpoint, col#, epvalue, epvalue_raw, ep_repeat_count from histgrm$ where obj#=:1 and intcol#=:2 and row#=:3 order by bucket
86708bvah4akqselect name from undo$ where file#=:1 and block#=:2 and ts#=:3 and status$ != 1
8swypbbr0m372select order#, columns, types from access$ where d_obj#=:1
96g93hntrzjtrselect /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_size, minimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, avgcln from hist_head$ where obj#=:1 and intcol#=:2
aahsjvcnr3swn merge /*+ dynamic_sampling(mm 4) dynamic_sampling_est_cdn(mm) dynamic_sampling(m 4) dynamic_sampling_est_cdn(m) */ into sys.mon_mods_all$ mm using (select m.obj# obj#, m.inserts inserts, m.updates updates, m.deletes deletes, m.flags flags, m.timestamp timestamp, m.drop_segments drop_segments from sys.mon_mods$ m, tab$ t where m.obj# = t.obj# and :objn = 0 union all select m.obj# obj#, m.inserts inserts, m.updates updates, m.deletes deletes, m.flags flags, m.timestamp timestamp, m.drop_segments drop_segments from sys.mon_mods$ m, tab$ t where m.obj# = t.obj# and :objn > 0 and m.obj# = :objn ) v on (mm.obj# = v.obj#) when matched then update set mm.inserts = mm.inserts + v.inserts, mm.updates = mm.updates + v.updates, mm.deletes = mm.deletes + v.deletes, mm.flags = mm.flags + v.flags - bitand(mm.flags, v.flags) /* bitor(mm.flags, v.flags) */, mm.timestamp = v.timestamp, mm.drop_segments = mm.drop_segments + v.drop_segments when NOT matched then insert (obj#, inserts, updates, deletes, timestamp, flags, drop_segments) values (v.obj#, v.inserts, v.updates, v.deletes, sysdate, v.flags, v.drop_segments)
adzjh275fvvx4call WWV_FLOW_WORKSHEET_API.DO_NOTIFY ( )
at1ygf4rd7cvjselect file#, block#, blocks from seg$ where type# = 3 and ts# = :1
bb926a5dcb8kr merge into sys.mon_mods$ 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 = inserts + :ins, updates = updates + :upd, deletes = deletes + :del, flags = (decode(bitand(flags, :flag), :flag, flags, flags + :flag)), drop_segments = drop_segments + :dropseg, timestamp = :time when NOT matched then insert (obj#, inserts, updates, del etes, timestamp, flags, drop_segments) values (:objn, :ins, :upd, :del, :time, :flag, :dropseg)
c2h8tpm85b5gbSELECT VALUE FROM V$PARAMETER WHERE NAME='compatible'
cfrfu4058yardINSERT INTO LGWR_PRESS VALUES(LGWR_SEQ.NEXTVAL)
csnp95dz2r8ssselect file#, block# from recyclebin$ where ts# = :1 and file# != 0 and block# != 0 and space = 0
cvn54b7yz0s8uselect /*+ index(idl_ub1$ i_idl_ub11) +*/ piece#, length, piece from idl_ub1$ where obj#=:1 and part=:2 and version=:3 order by piece#
f5dqupu2hknzu WITH a as (SELECT a$.dbid, a$.snap_id, a$.INSTANCE_NUMBER, a$.PLSQL_ENTRY_OBJECT_ID, a$.PLSQL_ENTRY_SUBPROGRAM_ID, a$.TIME_MODEL, a$.CON_DBID FROM WRH$_ACTIVE_SESSION_HISTORY a$ WHERE a$.dbid = :dbid AND a$.instance_number = :inst AND a$.snap_id > :bid AND a$.snap_id <= :eid AND a$.session_type = 1 ) , ash as (SELECT cnt , CON_DBID, PLSQL_ENTRY_OBJECT_ID, PLSQL_ENTRY_SUBPROGRAM_ID FROM ( SELECT count(*) as cnt , a.CON_DBID, a.PLSQL_ENTRY_OBJECT_ID, a.PLSQL_ENTRY_SUBPROGRAM_ID FROM a WHERE bitand(a.time_model, power(2, 11 )) !=0 AND a.PLSQL_ENTRY_OBJECT_ID > 0 AND a.PLSQL_ENTRY_SUBPROGRAM_ID > 0 GROUP BY CON_DBID, PLSQL_ENTRY_OBJECT_ID, PLSQL_ENTRY_SUBPROGRAM_ID HAVING count(*) >= :rowcnt ORDER BY 1 DESC, 2 ASC ) WHERE rownum <= :ret) SELECT x.con_dbid, x.plsql_entry_object_id, x.plsql_entry_subprogram_id, dba.object_name, dba.object_type, dba.owner, dba.procedure_name, to_number(d ba.overload), x.cnt FROM dba_procedures dba, ash x WHERE x.plsql_entry_object_id = dba.object_id(+) AND x.plsql_entry_subprogram_id = dba.subprogram_id(+) ORDER BY x.cnt DESC, x.plsql_entry_object_id, x.plsql_entry_subprogram_id
f80h0xb1qvbskSELECT sys.wri$_adv_seq_msggroup.nextval FROM dual
fhf8upax5cxszBEGIN sys.dbms_auto_report_internal.i_save_report (:rep_ref, :snap_id, :pr_class, :rep_id, :suc); END;
fnq8p3fj3r5asselect /*+ no_monitor */ 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
ga9j9xk5cy9s0select /*+ index(idl_sb4$ i_idl_sb41) +*/ piece#, length, piece from idl_sb4$ where obj#=:1 and part=:2 and version=:3 order by piece#

Back to SQL Statistics
Back to Top

Instance Activity Statistics

Back to Top

Key Instance Activity Stats

StatisticTotalper Secondper Trans
db block changes16,262,27260,219.494.02
execute count4,056,24515,020.351.00
logons cumulative4061.500.00
opened cursors cumulative8,105,05230,013.152.00
parse count (total)4,90618.170.00
parse time elapsed6302.330.00
physical reads2,4829.190.00
physical writes16,61661.530.00
redo size2,209,206,3088,180,730.64545.57
session cursor cache hits42,723158.200.01
session logical reads12,525,48146,382.083.09
user calls2,2638.380.00
user commits4,049,36314,994.861.00
workarea executions - optimal6882.550.00


Back to Instance Activity Statistics
Back to Top

Other Instance Activity Stats

StatisticTotalper Secondper Trans
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 slow jump count00.000.00
Batched IO vector block count00.000.00
Batched IO vector read count00.000.00
Block Cleanout Optim referenced90.030.00
CCursor + sql area evicted100.040.00
CLI BG ENQ900.330.00
CLI BG Fls done00.000.00
CLI BG attempt Flush900.330.00
CLI Flstask create900.330.00
CLI Flush900.330.00
CLI SGA Alloc00.000.00
CLI Thru Wrt00.000.00
CLI bytes fls to table00.000.00
CPU used by this session69,362256.850.02
CPU used when call started23,46986.910.01
CR blocks created1000.370.00
Commit SCN cached40.010.00
DBWR checkpoint buffers written16,56861.350.00
DBWR checkpoints160.060.00
DBWR object drop buffers written140.050.00
DBWR thread checkpoint buffers written16,56861.350.00
DBWR transaction table writes100.040.00
DBWR undo block writes14,61654.120.00
DFO trees parallelized610.230.00
HSC Heap Segment Block Changes4,051,92015,004.331.00
Heap Segment Array Inserts2370.880.00
Heap Segment Array Updates250.090.00
KTFB alloc req1,3855.130.00
KTFB alloc space (block)1,557,069,8245,765,857.52384.52
KTFB alloc time (ms)13,574,64850,267.173.35
KTFB apply req2,0607.630.00
KTFB apply time (ms)40,007148.150.01
KTFB commit req00.000.00
KTFB commit time (ms)00.000.00
KTFB free req7382.730.00
KTFB free space (block)108,048400.100.03
KTFB free time (ms)432,4101,601.220.11
LOB table id lookup cache misses00.000.00
PX local messages recv'd3,21511.910.00
PX local messages sent3,22511.940.00
Parallel operations not downgraded720.270.00
Requests to/from client1790.660.00
SQL*Net roundtrips to/from client1780.660.00
TBS Extension: bytes extended23,592,96087,365.155.83
TBS Extension: files extended120.040.00
TBS Extension: tasks created1000.370.00
TBS Extension: tasks executed120.040.00
active txn count during cleanout12,31245.590.00
auto extends on undo tablespace880.330.00
background checkpoints completed20.010.00
background checkpoints started30.010.00
background timeouts1,9887.360.00
branch node splits00.000.00
buffer is not pinned count19,40171.840.00
buffer is pinned count9,86636.530.00
bytes received via SQL*Net from client201,645746.700.05
bytes sent via SQL*Net to client41,541153.830.01
calls to get snapshot scn: kcmgss8,110,78630,034.392.00
calls to kcmgas4,051,09115,001.261.00
calls to kcmgcs18,16667.270.00
cell physical IO interconnect bytes5,357,847,55219,840,205.711,323.13
change write time2,79410.350.00
cleanout - number of ktugct calls52,552194.600.01
cleanouts and rollbacks - consistent read gets20.010.00
cleanouts only - consistent read gets460.170.00
cluster key scan block gets1,5785.840.00
cluster key scans1,2884.770.00
commit batch performed4,049,36514,994.871.00
commit batch requested4,049,36514,994.871.00
commit batch/immediate performed4,049,42514,995.091.00
commit batch/immediate requested4,049,42514,995.091.00
commit cleanout failures: block lost20.010.00
commit cleanout failures: callback failure1300.480.00
commit cleanout failures: cannot pin45,991170.310.01
commit cleanouts4,050,87515,000.461.00
commit cleanouts successfully completed4,004,75214,829.670.99
commit immediate performed600.220.00
commit immediate requested600.220.00
commit nowait performed4,049,36514,994.871.00
commit nowait requested4,049,36514,994.871.00
commit txn count during cleanout46,205171.100.01
commit wait/nowait performed4,049,36514,994.871.00
commit wait/nowait requested4,049,36514,994.871.00
consistent changes7,49827.770.00
consistent gets203,177752.370.05
consistent gets examination75,457279.420.02
consistent gets examination (fastpath)72,618268.910.02
consistent gets from cache203,177752.370.05
consistent gets pin127,720472.950.03
consistent gets pin (fastpath)126,794469.520.03
cursor authentications2600.960.00
data blocks consistent reads - undo records applied7,03926.070.00
db block gets12,322,30445,629.713.04
db block gets direct120.040.00
db block gets from cache12,322,29245,629.673.04
db block gets from cache (fastpath)16,06459.490.00
deferred (CURRENT) block cleanout applications2,025,1527,499.170.50
enqueue conversions9,78436.230.00
enqueue releases8,139,77430,141.732.01
enqueue requests8,139,77530,141.732.01
enqueue timeouts00.000.00
enqueue waits1,2834.750.00
file io service time00.000.00
free buffer requested80,293297.330.02
gc force cr disk read220.080.00
gc local grants80,226297.080.02
global enqueue get time13,61250.410.00
global enqueue gets async9583.550.00
global enqueue gets sync8,146,24030,165.672.01
global enqueue releases8,136,47330,129.512.01
heap block compress190.070.00
immediate (CR) block cleanout applications480.180.00
immediate (CURRENT) block cleanout applications1170.430.00
index crx upgrade (positioned)310.110.00
index fast full scans (full)00.000.00
index fetch by key4,96918.400.00
index scans kdiixs13,14311.640.00
leaf node 90-10 splits30.010.00
leaf node splits360.130.00
lob reads990.370.00
lob writes1310.490.00
lob writes unaligned1310.490.00
logical read bytes from cache102,608,642,048379,961,644.3225,339.45
max cf enq hold time8403.110.00
messages received2,6039.640.00
messages sent2,6039.640.00
min active SCN optimization applied on CR1270.470.00
no work - consistent read gets111,724413.720.03
non-idle wait count214,822795.490.05
parse count (describe)470.170.00
parse count (failures)70.030.00
parse count (hard)3071.140.00
parse time cpu500.190.00
physical read IO requests1,3715.080.00
physical read bytes20,332,54475,291.785.02
physical read total IO requests6,12822.690.00
physical read total bytes95,614,976354,063.9723.61
physical read total multi block requests80.030.00
physical reads cache2,4609.110.00
physical reads cache prefetch1,1114.110.00
physical reads direct220.080.00
physical reads direct temporary tablespace00.000.00
physical reads prefetch warmup00.000.00
physical write IO requests7,76528.750.00
physical write bytes136,118,272504,048.4133.61
physical write total IO requests17,48964.760.00
physical write total bytes5,262,232,57619,486,141.741,299.52
physical write total multi block requests6,67224.710.00
physical writes direct340.130.00
physical writes direct (lob)40.010.00
physical writes direct temporary tablespace120.040.00
physical writes from cache16,58261.400.00
physical writes non checkpoint16,47861.020.00
pinned cursors current50.020.00
prefetched blocks aged out before use00.000.00
queries parallelized610.230.00
recovery blocks read00.000.00
recursive calls12,182,96345,113.733.01
recursive cpu usage57,634213.420.01
redo KB read00.000.00
redo blocks checksummed by FG (exclusive)2,2498.330.00
redo blocks read for recovery00.000.00
redo blocks written4,464,14916,530.821.10
redo blocks written (group 0)00.000.00
redo blocks written (group 1)00.000.00
redo buffer allocation retries80.030.00
redo entries8,154,14530,194.952.01
redo k-bytes read for recovery00.000.00
redo log space requests210.080.00
redo subscn max counts1740.640.00
redo synch time930.340.00
redo synch time (usec)932,1543,451.780.23
redo synch time overhead (usec)2,0057.420.00
redo synch time overhead count ( 2ms)80.030.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)00.000.00
redo synch writes80.030.00
redo wastage795,9242,947.320.20
redo write info find80.030.00
redo write size count ( 4KB)950.350.00
redo write size count ( 8KB)1030.380.00
redo write size count ( 16KB)1250.460.00
redo write size count ( 32KB)360.130.00
redo write size count ( 128KB)430.160.00
redo write size count ( 256KB)350.130.00
redo write size count ( 512KB)480.180.00
redo write size count (1024KB)1320.490.00
redo write size count (inf)1,0844.010.00
redo write time14,21252.630.00
redo writes1,7016.300.00
redo writes (group 0)00.000.00
redo writes (group 1)00.000.00
redo writes adaptive all1,7016.300.00
redo writes adaptive worker00.000.00
rollback changes - undo records applied620.230.00
rollbacks only - consistent read gets960.360.00
root node splits00.000.00
rows fetched via callback1,9907.370.00
securefile allocation bytes32,768121.340.01
securefile allocation chunks40.010.00
securefile bytes non-transformed56,025207.460.01
securefile direct write bytes32,768121.340.01
securefile direct write ops40.010.00
securefile number of non-transformed flushes330.120.00
segment cfs allocations00.000.00
segment chunks allocation from disepnser40.010.00
segment dispenser allocations40.010.00
segment dispenser load empty00.000.00
segment dispenser load tasks00.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
shared hash latch upgrades - no wait3,62113.410.00
shared hash latch upgrades - wait10.000.00
shared io pool buffer get success40.010.00
sorts (memory)1,2344.570.00
sorts (rows)14,24052.730.00
sql area evicted90.030.00
sql area purged90.030.00
switch current to new buffer210.080.00
table fetch by rowid9,79736.280.00
table fetch continued row1230.460.00
table scan blocks gotten101,580376.150.03
table scan rows gotten62,149,023230,138.9515.35
table scans (short tables)1,4545.380.00
temp space allocated (bytes)1,048,5763,882.900.26
total cf enq hold time3,88014.370.00
total number of cf enq holders4671.730.00
total number of times SMON posted1900.700.00
transaction lock background get time00.000.00
transaction lock background gets00.000.00
transaction lock foreground requests00.000.00
transaction rollbacks600.220.00
undo change vector size551,759,6042,043,175.72136.26
write clones created in background00.000.00
write clones created in foreground100.040.00


Back to Instance Activity Statistics
Back to Top

Instance Activity Stats - Absolute Values

StatisticBegin ValueEnd Value
logons current6065
opened cursors current5160
session cursor cache count5,7136,684
session pga memory251,079,720286,536,128
session pga memory max293,843,304345,979,968
session uga memory199,303,776241,934,344
session uga memory max843,101,696999,147,000


Back to Instance Activity Statistics
Back to Top

Instance Activity Stats - Thread Activity

StatisticTotalper Hour
log switches (derived)339.99


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 Tm(ms)
LGWR1M0.14.004M4.3G29.8016.16M178579.10
Others60M14.98.222M529M6.251.959M46274.42
DBWR10M2.49.037M129M28.63.478M6720.02
Buffer Cache Reads18M4.64.067M0M0.000M12548.37
Direct Reads0M0.080M0M0.030M0 
Direct Writes0M0.000M0M0.060M30.00
TOTAL:89M22.34.33M4.9G64.7818.597M834120.64


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 File0M0.040M4.3G29.5616.153M2.67 
Data File21M5.27.078M130M29.01.481M6.1712.88
Control File69M16.45.256M16M3.73.059M4.08 
Temp File0M0.150M0M0.100M0.00 
TOTAL:90M21.91.333M4.4G62.3916.693M4.5512.88


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 Tm(ms)
LGWR 1M0.14.004M4.3G29.8016.156M510.63
LGWR (Log File) 0M0.040M4.3G29.5616.149M241.33
LGWR (Control File) 1M0.10.004M2M0.24.007M270.00
Others 61M14.98.226M529M6.251.959M41574.57
Others (Data File) 3M1.11.011M514M2.771.903M4132.15
Others (Control File) 58M13.86.215M15M3.48.056M37444.83
DBWR 10M2.49.037M129M28.63.478M6720.02
DBWR (Data File) 0M0.000M129M28.63.478M0 
DBWR (Control File) 10M2.49.037M0M0.000M6720.02
Buffer Cache Reads 17M4.10.063M0M0.000M11089.30
Buffer Cache Reads (Data File) 17M4.10.063M0M0.000M11089.30
Direct Reads 0M0.080M0M0.030M0 
Direct Reads (Data File) 0M0.080M0M0.030M0 
Direct Writes 0M0.000M0M0.060M30.00
Direct Writes (Data File) 0M0.000M0M0.060M30.00
TOTAL: 89M21.80.33M4.9G64.7818.593M59914.89


Back to IO Stats
Back to Top

Tablespace IO Stats

TablespaceReadsAv Rds/sAv Rd(ms)Av Blks/Rd 1-bk Rds/sAv 1-bk Rd(ms)WritesWrites avg/sBuffer WaitsAv Buf Wt(ms)PDB Name
UNDOTBS1 200.001.0000.006,381242,7500.55  
SYSAUX 57724.752.9224.43566200.00  
USERS 400.001.0000.00761328,3480.13  
TEMP 4000.251.0300.2626000.00  
SYSAUX 200.001.0000.008000.00 MACC
EXAMPLE 200.001.0000.002000.00 MACC
UNDOTBS2 200.001.0000.002000.00  
USERS 200.001.0000.002000.00 MACC


Back to IO Stats
Back to Top

File IO Stats

TablespaceFilenameReadsAv Rds/sAv Rd(ms)Av Blks/Rd 1-bk Rds/sAv 1-bk Rd(ms)WritesWrites avg/sBuffer WaitsAv Buf Wt(ms)PDB Name
EXAMPLE+DATADG/MAC/DD7D8C1D4C234B38E04325AAE80AF577/DATAFILE/example.278.819692585 200.001.0000.002000.00 MACC
SYSAUX+DATADG/MAC/DATAFILE/sysaux.257.819691725 57724.752.9224.43566200.00  
SYSAUX+DATADG/MAC/DD7D8C1D4C234B38E04325AAE80AF577/DATAFILE/sysaux.277.819692585 200.001.0000.008000.00 MACC
TEMP+DATADG/MAC/TEMPFILE/temp.267.819691865 4000.251.0300.262600   
UNDOTBS1+DATADG/MAC/DATAFILE/undotbs1.260.819691817 200.001.0000.006,381242,7500.55  
UNDOTBS2+DATADG/MAC/DATAFILE/undotbs2.271.819692307 200.001.0000.002000.00  
USERS+DATADG/MAC/DATAFILE/users.259.819691817 400.001.0000.00761328,3480.13  
USERS+DATADG/MAC/DD7D8C1D4C234B38E04325AAE80AF577/DATAFILE/users.280.819692587 200.001.0000.002000.00 MACC


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
D532,78410012,524,2772,51316,5820031,234


Back to Buffer Pool Statistics
Back to Top

Checkpoint Activity

MTTR WritesLog Size WritesLog Ckpt WritesOther Settings WritesAutotune Ckpt WritesThread Ckpt Writes
016,5680000


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
B00236121335632713563271   0
E025461919356381035632713563271   82


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
D4480.10531.0223122.00
D8960.201051.0023122.00
D1,3440.301581.0023122.00
D1,7920.392101.0023122.00
D2,2400.492631.0023122.00
D2,6880.593151.0023122.00
D3,1360.693681.0023122.00
D3,5840.794201.0023122.00
D4,0320.894731.0023122.00
D4,4800.995251.0023122.00
D4,5441.005331.0023122.00
D4,9281.085781.0023122.00
D5,3761.186301.0023122.00
D5,8241.286831.0023122.00
D6,2721.387351.0023122.00
D6,7201.487881.0023122.00
D7,1681.588401.0023122.00
D7,6161.688931.0023122.00
D8,0641.779461.0023122.00
D8,5121.879981.0023122.00
D8,9601.971,0511.0023122.00


Back to Advisory Statistics
Back to Top

PGA Aggr Summary

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


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)
B1,5001,130326.610.000.000.000.00153,600
E1,5001,110359.940.000.000.000.00153,600


Back to Advisory Statistics
Back to Top

PGA Aggr Target Histogram

Low Optimal High OptimalTotal ExecsOptimal Execs1-Pass ExecsM-Pass Execs
2K4K55555500
64K128K4400
128K256K3300
512K1024K787800
1M2M484800


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
1880.13292.883.5599.0033,793,978
3750.25292.880.00100.0003,748,581
7500.50292.880.00100.0003,748,581
1,1250.75292.880.00100.0003,748,581
1,5001.00292.880.00100.0003,748,581
1,8001.20292.880.00100.0003,748,581
2,1001.40292.880.00100.0003,748,581
2,4001.60292.880.00100.0003,748,581
2,7001.80292.880.00100.0003,748,581
3,0002.00292.880.00100.0003,748,581
4,5003.00292.880.00100.0003,748,581
6,0004.00292.880.00100.0003,748,581
9,0006.00292.880.00100.0003,748,581
12,0008.00292.880.00100.0003,748,581


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)
5920.57401,8201,8441.00161.075,495
7040.68934,6311,8451.00151.005,496
8160.78934,6311,8451.00151.005,496
8800.85934,6311,8451.00151.005,496
8960.86934,6311,8451.00151.005,496
9120.88934,6311,8451.00151.005,496
9280.89934,6311,8451.00151.005,496
9440.91934,6311,8451.00151.005,496
9600.92934,6311,8451.00151.005,496
9760.94934,6311,8451.00151.005,496
9920.95934,6311,8451.00151.005,496
1,0080.97934,6311,8451.00151.005,496
1,0240.98934,6311,8451.00151.005,496
1,0401.00934,6311,8451.00151.005,496
1,0561.02934,6311,8451.00151.005,496
1,0721.03934,6311,8451.00151.005,496
1,0881.05934,6311,8451.00151.005,496
1,1041.06934,6311,8451.00151.005,496
1,1201.08934,6311,8451.00151.005,496
1,1361.09934,6311,8451.00151.005,496
1,1521.11934,6311,8451.00151.005,496
1,1681.12934,6311,8451.00151.005,496
1,1841.14934,6311,8451.00151.005,496
1,2641.22934,6311,8451.00151.005,496
1,3761.32934,6311,8451.00151.005,496
1,4881.43934,6311,8451.00151.005,496
1,6001.54934,6311,8451.00151.005,496
1,7121.65934,6311,8451.00151.005,496
1,8241.75934,6311,8451.00151.005,496
1,9361.86934,6311,8451.00151.005,496
2,0481.97934,6311,8451.00151.005,496
2,1602.08934,6311,8451.00151.005,496


Back to Advisory Statistics
Back to Top

SGA Target Advisory

SGA Target Size (M)SGA Size FactorEst DB Time (s)Est Physical Reads
1,5000.251,28023,220
2,2500.381,27922,886
3,0000.501,27922,884
3,7500.631,27922,884
4,5000.751,27922,884
5,2500.881,27922,884
6,0001.001,27922,884
6,7501.131,27922,884
7,5001.251,27922,884
8,2501.381,27922,884
9,0001.501,27922,884
9,7501.631,27922,884
10,5001.751,27922,884
11,2501.881,27922,884
12,0002.001,27922,884


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)
data block28,45040
file header block52424
undo header2,19400
undo block3800
1st level bmb1800
segment header900
2nd level bmb100


Back to Wait Statistics
Back to Top

Enqueue Activity

Enqueue Type (Request Reason)RequestsSucc GetsFailed GetsWaitsWt Time (s)Av Wt Time(ms)
HW-Segment High Water Mark 15,32415,32406762030.12
US-Undo Segment 5,8735,873048400.85
TX-Transaction 4,052,6134,052,44901407.86
SQ-Sequence Cache 13513507800.90
JS-Job Scheduler (queue lock) 2,3672,3670205.00
PS-PX Process Reservation 1,5181,51801500.00
RO-Multiple Object Reuse (fast object reuse) 13013001300.00
FB-Format Block 4054050100.00
TM-DML 4,050,3004,050,300000 
TT-Tablespace 9,2989,298000 
CR-Reuse Block Range (block range reuse ckpt) 4,4284,428000 
JS-Job Scheduler 2,3672,367000 
CF-Controlfile Transaction 1,5631,563000 
SE-Session Migration 415415000 
SJ-KTSJ Slave Task Cancel (Slave Task Cancel) 379379000 
DD-ASM Local Disk Group 314314000 
CU-Cursor 285285000 
JS-Job Scheduler (q mem clnup lck) 224224000 
CT-Block Change Tracking (CTWR process start/stop) 112112000 
CT-Block Change Tracking (state) 112112000 
FD-Flashback Database (Flashback logical operations) 112112000 
FP-File Object (global fob contention) 112112000 
MV-Online Datafile Move (datafile move) 112112000 
SW-Suspend Writes 112112000 
XL-ASM Extent Fault Lock (fault extent map) 9494000 
MC-SGA Log Operation (Securefile log) 8989000 
MF-SGA Log-Bkt Flush (flush bkgnd periodic) 8989000 
AE-Edition Lock (lock) 8585000 
JD-Job Queue Date 5454000 
WF-AWR Flush 4646000 
MR-Media Recovery 2626000 
IS-Instance State 1717000 
PR-Process Startup 1616000 
TO-Temp Object 1515000 
AF-Advisor Framework (task serialization) 1313000 
PV-KSV slave startup (syncstart) 1111000 
JS-Job Scheduler (job run lock - synchronize) 66000 
TH-Threshold Chain (metric threshold evaluation) 55000 
RS-Reclaimable Space (write alert level) 44000 
UL-User-defined 44000 
TS-Temporary Segment 22000 
RS-Reclaimable Space (read alert level) 11000 
SH-Active Session History Flushing 11000 
TA-Instance Undo 11000 
TD-KTF map table enqueue (KTF dump entries) 11000 


Back to Wait Statistics
Back to Top

Undo Statistics

Back to Top

Undo Segment Summary

No data exists for this section of the report.

Back to Undo Statistics
Back to Top

Undo Segment Stats

No data exists for this section of the report.

Back to Undo Statistics
Back to Top

Latch Statistics

Back to Top

Latch Activity

Latch NameGet RequestsPct Get MissAvg Slps /MissWait Time (s)NoWait RequestsPct NoWait Miss
AQ Background: interrupt command latch10.00 090.00
AQ Coord jobx_kwsbgsgn latch90.00 00 
AQ Sharded master pool latch90.00 00 
AQ deq hash table latch10.00 00 
AQ dequeue txn counter latch620.00 00 
ASM Keyed state latch330.00 00 
ASM allocation9310.00 00 
ASM db client latch9680.00 00 
ASM map headers2,4460.00 00 
ASM map load waiting list5420.00 00 
ASM map operation freelist3250.00 00 
ASM map operation hash table1,711,8250.000.0000 
ASM network background latch3,6970.00 00 
ASM network state latch1,1650.090.0000 
ASM remote client latch40.00 00 
AWR Alerted Metric Element list2,3080.00 00 
Consistent RBA1,7080.00 00 
DML lock allocation8,100,6120.100.0100 
Event Group Locks4230.00 00 
FIB s.o chain latch2240.00 00 
FOB s.o list latch3320.00 00 
File State Object Pool Parent Latch10.00 00 
Hang Manager wait event statistics170.00 00 
I/O Staticstics latch10.00 00 
ILM Stats Stripe Latch10.00 00 
ILM access tracking extent10.00 00 
ILM activity tracking latch10.00 00 
IPC other latch40.00 00 
IPC stats buffer allocation latch1130.00 01040.00
In memory undo latch10.00 00 
Init msg cache10.00 00 
JS Sh mem access50.00 00 
JS broadcast add buf latch1200.00 00 
JS broadcast drop buf latch1200.00 00 
JS broadcast load blnc latch80.00 00 
JS mem alloc latch140.00 00 
JS queue access latch150.00 00 
JS queue state obj latch4,7340.00 00 
JS slv state obj latch420.00 00 
KCNIBR - invalid block range tree10.00 00 
KFC FX Hash Latch10.00 00 
KFC Hash Latch10.00 00 
KFCL LE Freelist10.00 00 
KFK SGA Libload latch160.00 00 
KFMD SGA70.00 00 
KGNFS-NFS:SHM structure20.00 00 
KGNFS-NFS:SVR LIST910.00 00 
KJC message pool free list10.00 00 
KJCT flow control latch10.00 00 
KMG MMAN ready and startup request latch900.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 id table parent latch10.00 00 
KSFS ksfs_node latch10.00 00 
KSFSD name cache parent latch10.00 00 
KSXR large replies100.00 00 
KTF sga latch30.00 0890.00
KTILM latch10.00 00 
KWQMN job cache list latch190.00 00 
Locator state objects pool parent latch10.00 00 
Lsod array latch550.00 00 
MQL Tracking Latch0  060.00
Memory Management Latch10.00 0900.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 
MinActiveScn Latch940.00 00 
Mutex10.00 00 
Mutex Stats10.00 00 
OS process1480.00 00 
OS process allocation6350.00 00 
OS process: request allocation330.00 00 
PL/SQL warning settings5200.00 00 
PX hash array latch10.00 00 
Parent latch for dependency tracking10.00 00 
QMT10.00 00 
Real-time descriptor latch6740.450.3300 
Report Request stats latch240.00 00 
Report Request struct latch80.00 00 
Request holder compeltion list latch330.00 00 
Retry Ht elm latch10.00 00 
Retry bkt latch10.00 00 
SGA Blackbox latch10.00 00 
SGA IO buffer pool latch2250.00 03410.00
SGA Logging Bkt Latch6310.00 00 
SGA Logging Log Latch9090.00 02700.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 0880.00
SQL memory manager workarea list latch6,4460.00 00 
Sched IM Job latch50.00 00 
Sched InMem Job Cache200.00 00 
Shared B-Tree100.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 
Undo Hint Latch0  01,4100.14
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 latch3090.00 00 
active service list4,3240.280.0001,6300.00
affinity stats hash tables freelist50.00 00 
affinity stats objects freelist10.00 00 
begin backup scn array40.00 00 
buffer pool10.00 00 
business card10.00 00 
cache buffer handles4,4980.110.0000 
cache buffers chains49,676,7710.020.01081,9100.00
cache buffers lru chain16,6930.010.00097,5720.07
cache table scan latch650.00 0650.00
call allocation3,1251.180.5700 
cas latch10.00 00 
change notification client cache latch10.00 00 
channel handle pool latch410.00 00 
channel operations parent latch6,3060.190.0000 
checkpoint queue latch20,9040.00 078,2610.00
client/application info2,3460.00 00 
compile environment latch4100.00 00 
corrupted undo seg latch2530.00 00 
cp handoff latch10.00 00 
cp pool latch10.00 00 
cp server hash latch10.00 00 
cp sga latch40.00 00 
cp srv type state latch130.00 00 
cp srv type wait latch10.00 00 
cvmap freelist lock10.00 00 
deferred cleanup latch40.00 00 
dispatcher info120.00 00 
dml lock allocation40.00 00 
done queue latch10.00 00 
dtp latch170.00 00 
dummy allocation8080.120.0000 
eleventh spare latch - children10.00 00 
enqueue freelist latch10.00 071,9530.04
enqueue hash chains24,456,7673.010.011430.00
enqueues1280.00 00 
error message lists8280.480.0000 
event stats latch4010.00 00 
fast space usage latch40.00 00 
file cache latch3,8390.00 00 
flashback copy10.00 00 
fourth Audit Vault latch10.00 00 
gc bast context freelist10.00 00 
gc element24,1490.090.0000 
gcs commit scn state10.00 00 
gcs opaque info freelist10.00 00 
gcs partitioned table hash8,536,5750.080.00071,2250.06
gcs pcm hashed value bucket hash10.00 00 
gcs remaster request queue200.00 00 
gcs resource freelist8,8490.260.0000 
gcs resource hash84,2790.00 00 
gcs resource scan list10.00 00 
gcs resource validate list10.00 00 
gcs shadows freelist10.00 00 
ges cached resource lists3,483,9470.000.1205,5370.25
ges deadlock list280.00 00 
ges domain table17,596,2080.00 00 
ges enqueue table freelist17,597,8140.810.0100 
ges group table16,953,2070.000.0000 
ges process hash list1590.00 00 
ges process parent latch33,884,7230.000.4800 
ges process table freelist333.030.0000 
ges resource hash list29,832,36610.830.0111742.87
ges resource scan list3520.00 00 
ges resource table freelist6,968,2900.040.0500 
ges timeout list310.00 00 
ges value block free list1570.00 00 
global tx hash mapping10.00 00 
granule from data transfer cache10.00 00 
granule operation10.00 00 
hash table Sql Plan Finding latch200.00 020.00
hash table column usage latch4390.00 0100,6730.00
hash table modification latch210.00 00 
heartbeat check10.00 01170.00
heartbeat structure management0  0530.00
internal temp table object number allocation latch300.00 00 
interrupt manipulation2,3850.250.0000 
intra txn parallel recovery10.00 00 
io pool granule list80.00 00 
io pool granule metadata list170.00 00 
job workq parent latch70.00 060.00
job_queue_processes free list latch240.00 00 
job_queue_processes parameter latch50.00 00 
k2q global data latch1800.00 00 
k2q lock allocation10.00 00 
kcb DW scan objtemp hash table latch10.00 00 
kcbtsemkid latch30.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 latch10.00 01,4480.00
kjoedcso state object freelist latch40.00 00 
kjoeq omni enqueue hash bucket latch10.00 00 
kjoer owner hash bucket10.00 00 
kokc descriptor allocation latch620.00 00 
krso process latch90.00 00 
ksfv messages10.00 00 
ksi resource reuse count10.00 00 
ksim group membership cache10,5650.00 00 
ksim membership request latch0  01200.00
kss move lock230.00 00 
ksuosstats global area340.00 00 
ksv allocation latch480.00 00 
ksv class latch8,5540.160.0700 
ksv instance latch140.00 00 
ksv msg queue latch3950.00 03940.00
ksxp shared latch330.00 00 
ksxp so latch330.00 00 
ksz_so allocation latch333.030.0000 
ktfbn latch10.00 00 
ktm global data7610.00 00 
kwqbsn:qsga90.00 00 
kwslbmdl: metadata latch280.00 00 
kwslbql: queue latch10.00 00 
lgwr LWN SCN1,7090.410.0000 
list of block allocation440.00 00 
loader state object freelist480.00 00 
lob segment dispenser latch100.00 00 
lob segment hash table latch130.00 00 
lob segment query latch10.00 00 
lock DBA buffer during media recovery10.00 00 
log write info0  01,7050.00
log write slave phase10.00 00 
logical standby cache10.00 00 
logminer context allocation10.00 00 
logminer work area10.00 00 
longop free list parent10.00 00 
managed standby latch90.00 00 
mapped buffers lru chain10.00 00 
message bitmap latch10.00 00 
message pool operations parent latch2,2880.00 00 
messages21,8000.090.0000 
mostly latch-free SCN1,7431.610.0000 
msg queue latch10.00 00 
multiblock read objects1340.00 00 
name-service memory objects2000.00 00 
name-service namespace bucket1,7900.00 00 
name-service pending queue1880.00 00 
name-service request240.00 00 
name-service request queue2,6710.00 00 
ncodef allocation latch40.00 00 
object queue header freelist7580.00 00 
object queue header operation192,0880.000.0000 
object queue memory10.00 00 
object stats modification3570.00 00 
parallel query alloc buffer6,6230.050.0000 
parallel query stats5840.680.0000 
parallel txn reco latch210.00 00 
parameter table management9490.00 00 
peshm10.00 00 
pesom_free_list10.00 00 
pesom_hash_node10.00 00 
pkey global statistics10.00 00 
post/wait queue6,0290.00 06,2280.00
process allocation490.00 0160.00
process group creation333.030.0000 
process pkey statistics list60.00 00 
process queue3,6070.00 00 
process queue reference101,8730.010.2506,7400.06
qmn task queue latch360.00 00 
query server freelists3,6790.00 00 
query server process5650.00 060.00
queued dump request10.00 00 
queuing load statistics10.00 00 
recovery domain hash list10.00 00 
redo allocation7,3402.860.0408,153,8190.37
redo copy10.00 08,154,1450.00
redo writing7,1890.00 00 
resmgr group change latch4280.00 00 
resmgr:active threads8660.00 0950.00
resmgr:actses change group4290.00 00 
resmgr:actses change state900.00 00 
resmgr:free threads list8290.360.0000 
resmgr:plan CPU method10.00 00 
resmgr:resource group CPU method3080.320.0000 
resmgr:schema config200.00 050.00
resmgr:session queuing10.00 00 
resumable state object2,2090.00 00 
rm cas latch10.00 00 
row cache objects307,5770.270.0000 
rules engine aggregate statistics10.00 00 
rules engine rule set statistics20.00 00 
second Audit Vault latch10.00 00 
segmented array pool2260.00 00 
sequence cache12,148,6312.310.0000 
session allocation4,052,6260.050.0004,051,8460.06
session idle bit4,055,9610.010.0200 
session queue latch10.00 00 
session state list latch8480.00 00 
session statistics8090.00 00 
session switching220.00 00 
session timer900.00 00 
seventh spare latch - X parent10.00 00 
sga hash table parent latch10.00 00 
shard latch10.00 00 
shared pool83,3660.670.0100 
shared pool sim alloc230.00 00 
shared pool simulator5120.00 00 
sim partition latch10.00 00 
simulator hash latch288,1570.00 00 
simulator lru latch10.00 0278,2080.01
sixth spare latch - X parent10.00 00 
sort extent pool3120.00 00 
space background task latch2,2100.860.4201800.00
state object free list20.00 00 
statistics aggregation50.00 00 
subscriber Ht bkt10.00 00 
tablespace key chain10.00 00 
temp lob duration state obj allocation140.00 00 
temporary table state object allocation30.00 00 
test excl. parent l010.00 00 
test excl. parent2 l010.00 00 
test excl. parent2 lmid cln10.00 00 
threshold alerts latch50.00 00 
trace latch10.00 00 
transaction allocation2,6110.040.0000 
twelfth spare latch - children10.00 00 
twenty-first spare latch - S par10.00 00 
twenty-second spare latch - S par10.00 00 
undo global data12,355,9620.070.0100 
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 Gets
ges resource hash list29,832,3663,230,92826,5443,206,121
enqueue hash chains24,456,767736,4796,804730,199
sequence cache12,148,631281,154189280,982
ges enqueue table freelist17,597,814142,930796142,182
cache buffers chains49,676,7719,796749,723
DML lock allocation8,100,6128,169608,115
undo global data12,355,9628,168678,103
ges resource table freelist6,968,2902,4761212,364
session allocation4,052,6262,11682,108
row cache objects307,5778412839
shared pool83,3665576551
session idle bit4,055,9612344230
redo allocation7,3402108202
ges cached resource lists3,483,94711213102
ges process parent latch33,884,723663234
call allocation3,125372119
space background task latch2,21019811
ksv class latch8,55414113
process queue reference101,873827
Real-time descriptor latch674312


Back to Latch Statistics
Back to Top

Latch Miss Sources

Latch NameWhereNoWait Misses SleepsWaiter Sleeps
DML lock allocationktaiam03031
DML lock allocationktaidm03029
Real-time descriptor latchkeomgAddNewEntry011
cache buffers chainskcbgcur_204131
cache buffers chainskcbnlc03426
cache buffers chainskcbgtcr: fast path exam0145
cache buffers chainskcbchg: change complete01316
cache buffers chainskcbchg: cur pin change0821
cache buffers chainskcbgtcr: fast path pin062
cache buffers chainskcbgcur_40410
cache buffers chainskcbzwb047
cache buffers chainskcbgtcr: slow path exclusive011
call allocationksuxds02121
enqueue hash chainsksqgtl302,2162,222
enqueue hash chainsksqcmi: if lk mode not requested01,778669
enqueue hash chainsksqrcl01,6062,032
enqueue hash chainsksqcmi: if lk mode requested01,2021,880
enqueue hash chainsksqcnl024
ges cached resource listskjruref: directly free local master01313
ges enqueue table freelistkjlfr: remove lock from parent object0620313
ges enqueue table freelistkjlalc: lock allocation0176483
ges process parent latchkjata_fg0320
ges resource hash listkjakoca: search for resp by resname09,252365
ges resource hash listkjlrlr: remove lock from resource queue07,9041,507
ges resource hash listkjrref: find matched resource06,18824,503
ges resource hash listkjrmas1: lookup master node03,197154
ges resource hash listkjcvscn: remove from scan queue010
ges resource table freelistkjralc: resource allocation010868
ges resource table freelistkjrfr: remove from resource table01353
ksv class latchksvclsl: rdp - recycle010
process queue referencekxfpqrsnd010
process queue referencekxfprienq2010
redo allocationkcrfw_redo_gen: redo allocation 1070
redo allocationkcrfw_redo_gen: redo allocation 2010
row cache objectskqreqd: reget020
sequence cachekdnssd01338
sequence cachekdnnxt: cached seq0414
sequence cachekdnss015177
session allocationksucri_int : SSO050
session allocationksuxds038
session idle bitksuxds044
shared poolkghfre046
shared poolkghalo020
space background task latchktsj_grab_task078
space background task latchktsjCreateTask010
undo global dataktudba: KSLBEGIN0395
undo global dataktudnx:child02024
undo global dataktufrbs_20638
undo global dataktusm_stealext: KSLBEGIN010
undo global dataktusm_stealext_2010


Back to Latch Statistics
Back to Top

Mutex Sleep Summary

Mutex TypeLocationSleepsWait Time (ms)
Library Cachekglpnal2 9158,2552,582
Library Cachekglpin1 416,4122,975
Library Cachekglpnal1 903,412113
Library Cachekglpndl1 952,040389
Cursor Pinkksfbc [KKSCHLFSP2]272305
Cursor Parentkksfbc [KKSPRTLOC32]10988
Cursor PinkksLockDelete [KKSCHLPIN6]97109
Library Cachekglhdgn2 1061911
Library Cachekgllkc1 5783
Library Cachekglget2 243
Library Cachekgllkdl1 8532
Library Cachekglhdgn1 6222
Library Cachekgllkal1 8021


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. TypeLogical Reads%TotalPDB Name
SYSUSERSLGWR_PRESS TABLE4,202,08033.55  
SYSSYSTEMUNDO$ TABLE3,2800.03  
SYSSYSAUXWRH$_SYSTEM_EVENTWRH$_SYSTEM_797696270_0TABLE PARTITION2,0000.02  
SYSSYSTEMI_COL_USAGE$ INDEX1,9520.02  
SYSSYSTEMOBJ$ TABLE1,7760.01  


Back to Segment Statistics
Back to Top

Segments by Physical Reads

OwnerTablespace NameObject NameSubobject NameObj. TypePhysical Reads%TotalPDB Name
SYSSYSAUXWRH$_PARAMETERWRH$_PARAME_797696270_0TABLE PARTITION55922.52  
SYSSYSAUXWRH$_SYSTEM_EVENTWRH$_SYSTEM_797696270_0TABLE PARTITION49419.90  
SYSSYSTEMIDL_UB1$ TABLE1094.39  
SYSSYSTEMTAB$ TABLE491.97  
SYSSYSAUXWRH$_SQL_PLAN TABLE391.57  


Back to Segment Statistics
Back to Top

Segments by Physical Read Requests

OwnerTablespace NameObject NameSubobject NameObj. TypePhys Read Requests%TotalPDB Name
SYSSYSTEMIDL_UB1$ TABLE1097.95  
SYSSYSTEMTAB$ TABLE493.57  
SYSSYSAUXWRH$_SQL_PLAN TABLE392.84  
SYSSYSAUXWRH$_SQLSTAT_INDEXWRH$_SQLSTA_797696270_0INDEX PARTITION271.97  
SYSSYSAUXWRH$_PARAMETERWRH$_PARAME_797696270_0TABLE PARTITION241.75  


Back to Segment Statistics
Back to Top

Segments by UnOptimized Reads

OwnerTablespace NameObject NameSubobject NameObj. TypeUnOptimized Reads%TotalPDB Name
SYSSYSTEMIDL_UB1$ TABLE1097.95  
SYSSYSTEMTAB$ TABLE493.57  
SYSSYSAUXWRH$_SQL_PLAN TABLE392.84  
SYSSYSAUXWRH$_SQLSTAT_INDEXWRH$_SQLSTA_797696270_0INDEX PARTITION271.97  
SYSSYSAUXWRH$_PARAMETERWRH$_PARAME_797696270_0TABLE PARTITION241.75  


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. TypePhysical Writes%TotalPDB Name
SYSUSERSLGWR_PRESS TABLE1,2807.70  
SYSSYSAUXWRH$_SQLSTAT_INDEXWRH$_SQLSTA_797696270_0INDEX PARTITION660.40  
SYSSYSAUXWRH$_SQL_PLAN TABLE230.14  
SYSSYSAUXWRH$_SYSMETRIC_HISTORY_INDEX INDEX210.13  
SYSSYSAUXWRH$_SYSTEM_EVENTWRH$_SYSTEM_797696270_0TABLE PARTITION210.13  


Back to Segment Statistics
Back to Top

Segments by Physical Write Requests

OwnerTablespace NameObject NameSubobject NameObj. TypePhys Write Requests%TotalPDB Name
SYSUSERSLGWR_PRESS TABLE7579.75  
SYSSYSAUXWRH$_SQLSTAT_INDEXWRH$_SQLSTA_797696270_0INDEX PARTITION570.73  
SYSSYSAUXWRH$_SYSTEM_EVENTWRH$_SYSTEM_797696270_0TABLE PARTITION210.27  
SYSSYSAUXWRH$_SQL_PLAN TABLE200.26  
SYSSYSAUXWRH$_SYSSTAT_PKWRH$_SYSSTA_797696270_0INDEX PARTITION160.21  


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. TypeDB Block Changes% of CapturePDB Name
SYSUSERSLGWR_PRESS TABLE4,094,38499.90  
SYSSYSTEMCOL_USAGE$ TABLE6080.01  
SYSSYSAUXWRH$_SYSSTAT_PKWRH$_SYSSTA_797696270_0INDEX PARTITION4960.01  
SYSTEMPSYS_TEMP_0FD9D6600_2BCE72 TABLE4000.01  
SYSSYSAUXWRH$_LATCH_PKWRH$_LATCH_797696270_0INDEX PARTITION4000.01  


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

OwnerTablespace NameObject NameSubobject NameObj. TypeBuffer Busy Waits% of CapturePDB Name
SYSUSERSLGWR_PRESS TABLE28,342100.00  


Back to Segment Statistics
Back to Top

Segments by Global Cache Buffer Busy

No data exists for this section of the report.

Back to Segment Statistics
Back to Top

Segments by CR Blocks Received

No data exists for this section of the report.

Back to Segment Statistics
Back to Top

Segments by Current Blocks Received

No data exists for this section of the report.

Back to Segment Statistics
Back to Top

Dictionary Cache Statistics

Back to Top

Dictionary Cache Stats

CacheGet RequestsPct MissScan ReqsPct MissMod ReqsFinal Usage
dc_awr_control200.000 21
dc_global_oids7315.070 076
dc_histogram_data7,5454.290 03,799
dc_histogram_defs9,8181.840 06,688
dc_objects5,6182.490 82,485
dc_profiles180.000 02
dc_props2590.000 099
dc_rollback_segments30,0060.000 025
dc_segments6,4220.900 21,381
dc_sequences616.560 6113
dc_tablespaces29,6370.000 014
dc_users16,7120.020 0185
outstanding_alerts10.000 18
sch_lj_oids128.330 023


Back to Dictionary Cache Statistics
Back to Top

Dictionary Cache Stats (RAC)

CacheGES RequestsGES ConflictsGES Releases
dc_awr_control400
dc_global_oids1100
dc_histogram_defs18100
dc_objects15400
dc_segments6200
dc_sequences12200
dc_users300
outstanding_alerts200
sch_lj_oids100


Back to Dictionary Cache Statistics
Back to Top

Library Cache Statistics

Back to Top

Library Cache Activity

NamespaceGet RequestsPct MissPin RequestsPct MissReloadsInvali- dations
BODY1617.454192.8600
CLUSTER100.00100.0000
EDITION710.001420.0000
INDEX780.00780.0000
QUEUE540.00640.0000
RULESET0 10.0000
SCHEMA1561.920 00
SQL AREA2,00113.094,059,7800.07119
SQL AREA BUILD21869.720 00
SQL AREA STATS22095.9122095.9100
TABLE/PROCEDURE1,4247.024,053,8370.0000
TRANSFORMATION270.00270.0000
TRIGGER100.00100.0000


Back to Library Cache Statistics
Back to Top

Library Cache Activity (RAC)

NamespaceGES Lock RequestsGES Pin RequestsGES Pin ReleasesGES Inval RequestsGES Invali- dations
BODY040740700
CLUSTER10101000
EDITION71777700
INDEX78787800
QUEUE27646400
RULESET01100
SCHEMA60000
TABLE/PROCEDURE2,4634,052,5094,052,50900
TRANSFORMATION27272700
TRIGGER0101000


Back to Library Cache Statistics
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 cache4,544.004,544.004,544.004,848.000SHR/IMM
Data Transfer Cache0.000.000.000.000STA/
KEEP buffer cache0.000.000.000.000STA/
PGA Target1,504.001,504.001,504.001,504.000STA/
RECYCLE buffer cache0.000.000.000.000STA/
SGA Target6,000.006,000.006,000.006,000.000STA/
Shared IO Pool304.00304.000.00304.000GRO/IMM
java pool16.0016.0016.0016.000STA/
large pool48.0048.0048.00144.000SHR/DEF
shared pool1,040.001,040.001,040.001,040.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

CategoryAlloc (MB)Used (MB)Avg Alloc (MB)Std Dev Alloc (MB)Max Alloc (MB)Hist Max Alloc (MB)Num ProcNum Alloc
BOther282.74 3.377.1660608484
Freeable41.630.001.672.087 2525
SQL1.660.880.060.12152916
PL/SQL0.630.430.010.03006966
EOther313.09 3.777.2960608383
Freeable44.630.001.441.847 3131
SQL1.450.740.040.12173419
PL/SQL0.830.570.010.03017471


Back to Memory Statistics
Back to Top

SGA Memory Summary

SGA regionsBegin Size (Bytes)End Size (Bytes) (if different)
Database Buffers5,083,496,448 
Fixed Size2,302,296 
Redo Buffers19,927,040 
Variable Size1,157,631,656 

Back to Memory Statistics
Back to Top

SGA breakdown difference

PoolNameBegin MBEnd MB% Diff
javafree memory16.0016.000.00
largePX msg pool15.0015.000.00
largefree memory28.4828.480.00
sharedKGLH018.8121.9216.52
sharedKGLS10.2611.178.90
sharedSQLA32.8439.7020.88
shareddb_block_hash_buckets44.0044.000.00
sharedfree memory412.05395.54-4.01
sharedgc name table12.0012.000.00
sharedgcs resources133.73133.730.00
sharedgcs shadows82.3082.300.00
sharedges big msg buffers10.7610.760.00
sharedktli log buf s12.0112.010.00
sharedwrite state object14.6014.600.00
 buffer_cache4,544.004,544.000.00
 fixed_sga2.202.200.00
 log_buffer19.0019.000.00
 shared_io_pool304.00304.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
001010


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 %
2690.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.002690.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
audit_file_dest/s01/admin/MAC/adump    
audit_trailDB    
cluster_databaseTRUE    
commit_writeBatch, nowait    
compatible12.1.0.0.0    
control_files+DATADG/MAC/CONTROLFILE/current.262.819691855, +DATADG/MAC/CONTROLFILE/current.261.819691855    
db_block_size8192    
db_create_file_dest+DATADG    
db_domain      
db_nameMAC    
db_recovery_file_dest+DATADG    
db_recovery_file_dest_size5269094400    
diagnostic_dest/s01    
dispatchers(PROTOCOL=TCP) (SERVICE=MACXDB)    
enable_pluggable_databaseTRUE    
local_listener(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.172)(PORT=1521))    
lock_sgaTRUE    
open_cursors300    
pga_aggregate_target1572864000    
processes300    
remote_listenermaclean-cluster-scan:1521    
remote_login_passwordfileEXCLUSIVE    
sga_target6291456000    


Back to init.ora Parameters
Back to Top

init.ora Multi-Valued Parameters

Parameter NameBegin valueEnd value (if different)PDB Name
control_files+DATADG/MAC/CONTROLFILE/current.261.819691855    
+DATADG/MAC/CONTROLFILE/current.262.819691855    


Back to init.ora Parameters
Back to Top

ADDM Task ADDM:797696270_1_115

          ADDM Report for Task 'ADDM:797696270_1_115'
          -------------------------------------------

Analysis Period
---------------
AWR snapshot range from 114 to 115.
Time period starts at 07-JUL-13 08.25.50 AM
Time period ends at 07-JUL-13 08.30.20 AM

Analysis Target
---------------
Database 'MAC' with DB ID 797696270.
Database version 12.1.0.1.0.
ADDM performed an analysis of instance MAC_1, numbered 1 and hosted at
maclean1.oracle.com.

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

Summary of Findings
-------------------
   Description           Active Sessions      Recommendations
                         Percent of Activity
   --------------------  -------------------  ---------------
1  Top SQL Statements    2.69 | 96            2
2  High Watermark Waits  .07 | 2.66           0


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


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

Finding 1: Top SQL Statements
Impact is 2.69 active sessions, 96% 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 1.38 active sessions, 49.33% of total activity.
   --------------------------------------------------------------------
   Action
      Run SQL Tuning Advisor on the INSERT statement with SQL_ID
      "cfrfu4058yard".
      Related Object
         SQL statement with SQL_ID cfrfu4058yard.
         INSERT INTO LGWR_PRESS VALUES(LGWR_SEQ.NEXTVAL)
   Rationale
      The SQL statement executed in the root container.
   Rationale
      The SQL spent 88% 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 "cfrfu4058yard" was executed 4049209 times and
      had an average elapsed time of 0.000099 seconds.
   Rationale
      Top level calls to execute the PL/SQL statement with SQL_ID
      "73kc99v9699nm" are responsible for 100% of the database time spent on
      the INSERT statement with SQL_ID "cfrfu4058yard".
      Related Object
         SQL statement with SQL_ID 73kc99v9699nm.
         begin
         loop
         insert into lgwr_press values(lgwr_seq.nextval) ;
         commit;
         end loop;
         end;

   Recommendation 2: SQL Tuning
   Estimated benefit is 1.12 active sessions, 40% of total activity.
   -----------------------------------------------------------------
   Action
      Investigate the PL/SQL statement with SQL_ID "73kc99v9699nm" 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 73kc99v9699nm.
         begin
         loop
         insert into lgwr_press values(lgwr_seq.nextval) ;
         commit;
         end loop;
         end;
   Rationale
      The SQL statement executed in the root container.
   Rationale
      The SQL Tuning Advisor cannot operate on PL/SQL statements.
   Rationale
      Database time for this SQL was divided as follows: 80% for SQL
      execution, 0% for parsing, 20% for PL/SQL execution and 0% for Java
      execution.
   Rationale
      SQL statement with SQL_ID "73kc99v9699nm" was executed 3 times and had
      an average elapsed time of 247 seconds.


Finding 2: High Watermark Waits
Impact is .07 active sessions, 2.66% of total activity.
-------------------------------------------------------
Contention on the high watermark (HW) enqueue was consuming significant
database time.

   No recommendations are available.

   Symptoms That Led to the Finding:
   ---------------------------------
      Wait class "Configuration" was consuming significant database time.
      Impact is .08 active sessions, 2.8% of total activity.



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

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

Miscellaneous Information
-------------------------
Wait class "Application" was not consuming significant database time.
Wait class "Cluster" was not consuming significant database time.
Wait class "Commit" was not consuming significant database time.
Wait class "Concurrency" 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.

The database's maintenance windows were active during 100% of the analysis
period.


Back to Top

RAC Statistics

BeginEnd
Number of Instances: 1 1

Global Cache Load Profile

Per SecondPer Transaction
Global Cache blocks received: 0.01 0.00
Global Cache blocks served: 0.01 0.00
GCS/GES messages received: 0.00 0.00
GCS/GES messages sent: 0.00 0.00
DBWR Fusion writes: 0.00 0.00
Estd Interconnect traffic (KB) 0.12 

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

Buffer access - local cache %: 99.98
Buffer access - remote cache %: 0.00
Buffer access - disk %: 0.02

Global Cache and Enqueue Services - Workload Characteristics

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

Global Cache and Enqueue Services - Messaging Statistics

Avg message sent queue time (ms): 
Avg message sent queue time on ksxp (ms): 
Avg message received queue time (ms): 
Avg GCS message process time (ms): 
Avg GES message process time (ms): 
% of direct sent messages: 
% of indirect sent messages: 
% of flow controlled messages: 


Cluster Interconnect

 Begin End
InterfaceIP AddressPubSourcePubSrc
eth1:1169.254.137.242N   

RAC Statistics


Back to Top


Back to Top

Global Messaging Statistics

StatisticTotalper Secondper Trans
KA messages received actual00.000.00
KA messages received logical00.000.00
acks for commit broadcast(actual)00.000.00
acks for commit broadcast(logical)00.000.00
broadcast msgs on commit(actual)00.000.00
broadcast msgs on commit(logical)00.000.00
broadcast msgs on commit(wasted)00.000.00
dynamically allocated gcs resources00.000.00
dynamically allocated gcs shadows00.000.00
flow control messages received00.000.00
flow control messages sent00.000.00
gcs apply delta00.000.00
gcs assume cvt00.000.00
gcs assume no cvt00.000.00
gcs ast xid00.000.00
gcs blocked converts00.000.00
gcs blocked cr converts00.000.00
gcs compatible basts00.000.00
gcs compatible cr basts (global)00.000.00
gcs compatible cr basts (local)00.000.00
gcs cr basts to PIs00.000.00
gcs cr serve without current lock00.000.00
gcs dbwr flush pi msgs00.000.00
gcs dbwr write request msgs00.000.00
gcs delta msgs sent00.000.00
gcs delta push aborted by cancel00.000.00
gcs delta push aborted by remastering00.000.00
gcs delta push cancelled00.000.00
gcs delta push cancelled at master00.000.00
gcs delta push cancelled by close00.000.00
gcs delta push cancelled by consign00.000.00
gcs delta push cancelled by cvt ping00.000.00
gcs delta push cancelled by dwncvt00.000.00
gcs delta push create pi00.000.00
gcs delta update master msgs sent00.000.00
gcs delta wait aborted by remastering00.000.00
gcs delta wait cancelled00.000.00
gcs error msgs00.000.00
gcs force cr block only00.000.00
gcs force cr grant00.000.00
gcs force cr no current00.000.00
gcs forward cr to pinged instance00.000.00
gcs immediate (compatible) converts2230.830.00
gcs immediate (null) converts6,79125.150.00
gcs immediate cr (compatible) converts00.000.00
gcs immediate cr (null) converts1,9537.230.00
gcs indirect ast00.000.00
gcs indirect bidless ast00.000.00
gcs indirect fg ast00.000.00
gcs lms flush pi msgs00.000.00
gcs lms write request msgs00.000.00
gcs msgs process time(ms)00.000.00
gcs msgs received00.000.00
gcs new served by master6,31423.380.00
gcs out-of-order msgs00.000.00
gcs pings refused00.000.00
gcs pkey conflicts retry00.000.00
gcs push delta00.000.00
gcs queued converts00.000.00
gcs reader bypass N->Xw ping local00.000.00
gcs reader bypass N->Xw ping remote00.000.00
gcs reader bypass grant X on assume00.000.00
gcs reader bypass grant ast00.000.00
gcs reader bypass grant fg ast00.000.00
gcs reader bypass grant immediate00.000.00
gcs recovery claim msgs00.000.00
gcs refuse xid00.000.00
gcs regular cr00.000.00
gcs retry convert request00.000.00
gcs share recovery bast00.000.00
gcs side channel msgs actual00.000.00
gcs side channel msgs logical00.000.00
gcs stale cr220.080.00
gcs undo cr00.000.00
gcs write notification msgs00.000.00
gcs writes refused00.000.00
ges msgs process time(ms)00.000.00
ges msgs received00.000.00
global posts dropped00.000.00
global posts queue time00.000.00
global posts queued00.000.00
global posts requested00.000.00
global posts sent00.000.00
implicit batch messages received00.000.00
implicit batch messages sent00.000.00
lmd msg send time(ms)00.000.00
lms(s) msg send time(ms)00.000.00
messages flow controlled00.000.00
messages queue sent actual00.000.00
messages queue sent logical00.000.00
messages received actual00.000.00
messages received logical00.000.00
messages sent directly00.000.00
messages sent indirectly00.000.00
messages sent not implicit batched00.000.00
messages sent pbatched00.000.00
msgs causing lmd to send msgs00.000.00
msgs causing lms(s) to send msgs00.000.00
msgs received queue time (ms)00.000.00
msgs received queued00.000.00
msgs sent queue time (ms)00.000.00
msgs sent queue time on ksxp (ms)00.000.00
msgs sent queued00.000.00
msgs sent queued on ksxp00.000.00
number of broadcasted resources00.000.00
number of directly freed master res3,483,83012,900.690.86
number of ges deadlock detected00.000.00
number of lock borrowed by LMD00.000.00
number of received ODPS messages00.000.00
number of resource borrowed by LMD00.000.00
number of sent ODPS messages00.000.00
process batch messages received00.000.00
process batch messages sent00.000.00



Back to Top

Global CR Served Stats

StatisticTotal
CR Block Requests0
CURRENT Block Requests0
Data Block Requests0
Undo Block Requests0
TX Block Requests0
Current Results0
Private results0
Zero Results0
Disk Read Results0
Fail Results0
Fairness Down Converts0
Fairness Clears0
Free GC Elements0
Flushes0
Flushes Queued0
Flush Queue Full0
Flush Max Time (us)0
Light Works0
Errors0



Back to Top

Global CURRENT Served Stats

StatisticTotal% <1ms% <10ms% <100ms% <1s% <10s
Pins0     
Flushes0     
Writes0     



Back to Top

Global Cache Transfer Stats

Back to Top

Global Cache Transfer Stats

No data exists for this section of the report.

Back to Global Cache Transfer Stats
Back to Top

Global Cache Transfer Times (ms)

No data exists for this section of the report.

Back to Global Cache Transfer Stats
Back to Top

Global Cache Transfer (Immediate)

No data exists for this section of the report.

Back to Global Cache Transfer Stats
Back to Top

Global Cache Times (Immediate)

No data exists for this section of the report.

Back to Global Cache Transfer Stats
Back to Top

Interconnect Stats

Back to Top

Interconnect Ping Latency Stats

Target Instance500B Ping CountAvg Latency 500B msgStddev 500B msg8K Ping CountAvg Latency 8K msgStddev 8K msg
1200.230.05200.220.05
200.000.0000.000.00


Back to Interconnect Stats
Back to Top

Interconnect Throughput by Client

Used BySend Mbytes/secReceive Mbytes/sec
Global Cache0.000.00
Parallel Query0.000.00
DB Locks0.000.00
DB Streams0.000.00
Other0.000.00


Back to Interconnect Stats
Back to Top

Interconnect Device Statistics

Device NameIP AddressPublicSourceSend Mbytes/secSend ErrorsSend DroppedSend Buffer OverrunSend Carrier LostReceive Mbytes/secReceive ErrorsReceive DroppedReceive Buffer OverrunReceive Frame Errors
eth1:1169.254.137.242NO  0.000000 0.000000


Back to Interconnect Stats
Back to Top

Dynamic Remastering Stats

No data exists for this section of the report.


Back to Top

End of Report