NO 1 1913987003 6131 6121 1177 9457816 14514 66 ,993478633 0 35 2137601 998419304 0 16186 12454146 14514 101466 2137566 4038416 44 49355244 49354205 54050274 ,000308588 1 7,5323E+10 0 320570 13205504 553648128 553648128 1476395008 1476395008 359128 14092 385 1,0308E+10 8192 86342968 553648128 553648128 63653 6393254 186 104 62 55 81161416 73 4294967296 4294967296 714219520 613579776 4 4 TYPICAL TRUE 79 79628 11 20 1,7179E+10 1,7179E+10 40302 329929 0 370222 3528046875 1,2930E+10 0 8252849867 937786418 57156250 1366554103 449850 54051451 ,177010114 ,822989886 1,4297E+10 3585203125 4498,5 1,3899E+10 TRUE TYPICAL STATSPACK report for Database DB Id Instance Inst Num Startup Time Release RAC ~~~~~~~~ ----------- ------------ -------- --------------- ----------- --- 1913987003 delpnra0 1 20-Mei-16 14:32 12.1.0.2.0 NO Host Name Platform CPUs Cores Sockets Memory (G) ~~~~ ---------------- ---------------------- ----- ----- ------- ------------ SVDLTORAT0007 Microsoft Windows x86 4 4 4 16.0 Snapshot Snap Id Snap Time Sessions Curs/Sess Comment ~~~~~~~~ ---------- ------------------ -------- --------- ------------------ Begin Snap: 6121 22-Mei-16 03:32:59 55 1.9 End Snap: 6131 22-Mei-16 03:51:45 62 3.0 Elapsed: 18.77 (mins) Av Act Sess: 11.5 DB time: 215.51 (mins) DB CPU: 58.80 (mins) End Time ------------------- 22-Mei-16 03:51:45 Start Time ------------------- 22-Mei-16 03:32:59 TTICS ---------- 4498,5 ENCPU ---------- 4 SPCTIM ---------- 4504 PCT_SP_OSS_CPU_DIFF ------------------- ,111012433 Cache Sizes Begin End ~~~~~~~~~~~ ---------- ---------- Buffer Cache: 1,408M Std Block Size: 8K Shared Pool: 528M Log Buffer: 12,896K Load Profile Per Second Per Transaction Per Exec Per Call ~~~~~~~~~~~~ ------------------ ----------------- ----------- ----------- DB time(s): 11.5 0.9 0.00 10.99 DB CPU(s): 3.1 0.2 0.00 3.00 Redo size: 886,695.7 68,790.1 Logical reads: 43,832.4 3,400.5 Block changes: 8,399.5 651.6 Physical reads: 1,898.4 147.3 Physical writes: 90.1 7.0 User calls: 1.1 0.1 Parses: 11,060.5 858.1 Hard parses: 14.4 1.1 W/A MB processed: 63.8 5.0 Logons: 0.3 0.0 Executes: 5,677.9 440.5 Rollbacks: 0.0 0.0 Transactions: 12.9 Instance Efficiency Indicators ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Buffer Nowait %: 100.00 Redo NoWait %: 100.00 Buffer Hit %: 95.67 Optimal W/A Exec %: 100.00 Library Hit %: 99.35 Soft Parse %: 99.87 Execute to Parse %: -94.80 Latch Hit %: 99.97 Parse CPU to Parse Elapsd %: 22.14 % Non-Parse CPU: 96.08 Shared Pool Statistics Begin End ------ ------ Memory Usage %: 84.40 85.34 % SQL with executions>1: 79.84 77.44 % Memory for SQL w/exec>1: 84.14 84.15 Top 5 Timed Events Avg %Total ~~~~~~~~~~~~~~~~~~ wait Call Event Waits Time (s) (ms) Time ----------------------------------------- ------------ ----------- ------ ------ CPU time 3,591 25.8 LGWR worker group idle 10 2,310 ###### 16.6 AQPC idle 38 1,141 30014 8.2 lreg timer 368 1,126 3059 8.1 heartbeat redo informer 1,073 1,124 1047 8.1 ------------------------------------------------------------- Host CPU (CPUs: 4 Cores: 4 Sockets: 4) ~~~~~~~~ Load Average Begin End User System Idle WIO WCPU ------- ------- ------- ------- ------- ------- -------- 20.00 11.00 73.34 8.96 17.70 Instance CPU ~~~~~~~~~~~~ % Time (seconds) -------- -------------- Host: Total time (s): 4,498.5 Host: Busy CPU time (s): 3,702.2 % of time Host is Busy: 82.3 Instance: Total CPU time (s): 3,585.2 % of Busy CPU used for Instance: 96.8 Instance: Total Database time (s): 14,297.0 %DB time waiting for CPU (Resource Mgr): 0.0 Memory Statistics Begin End ~~~~~~~~~~~~~~~~~ ------------ ------------ Host Mem (MB): 16,383.5 16,383.5 SGA use (MB): 4,096.0 4,096.0 PGA use (MB): 585.2 681.1 % Host Mem used for SGA+PGA: 28.6 29.2 ------------------------------------------------------------- Time Model System Stats DB/Inst: DELPNRA0/delpnra0 Snaps: 6121-6131 -> Ordered by % of DB time desc, Statistic name Statistic Time (s) % DB time ----------------------------------- -------------------- --------- sql execute elapsed time 8,252.8 ######## DB CPU 3,528.0 ######## PL/SQL execution elapsed time 2,284.0 ######## parse time elapsed 937.8 ######## Java execution elapsed time 801.9 ######## hard parse elapsed time 293.1 ######## hard parse (sharing criteria) elaps 53.2 ######## hard parse (bind mismatch) elapsed 47.0 ######## PL/SQL compilation elapsed time 38.6 ######## repeated bind elapsed time 1.0 ######## sequence load elapsed time 0.4 ######## DB time 12,930.5 background elapsed time 1,366.6 background cpu time 57.2 ------------------------------------------------------------- Foreground Wait Events DB/Inst: DELPNRA0/delpnra0 Snaps: 6121-6131 -> Only events with Total Wait Time (s) >= .001 are shown -> ordered by Total Wait Time desc, Waits desc (idle events last) Avg %Total %Tim Total Wait wait Waits Call Event Waits out Time (s) (ms) /txn Time ---------------------------- ------------ ---- ---------- ------ -------- ------ PL/SQL lock timer 8 100 960 ###### 0.0 6.9 utl_file I/O 421,999 0 880 2 29.1 6.3 db file scattered read 169,913 0 636 4 11.7 4.6 library cache: mutex X 10,301 0 589 57 0.7 4.2 db file parallel read 1,629 0 268 165 0.1 1.9 db file sequential read 69,637 0 197 3 4.8 1.4 log file sync 178 0 171 962 0.0 1.2 cursor: pin S wait on X 147 0 81 550 0.0 .6 read by other session 23 0 29 1283 0.0 .2 library cache load lock 360 0 15 40 0.0 .1 undo segment extension 25 96 10 415 0.0 .1 control file sequential read 2,055 0 10 5 0.1 .1 latch: row cache objects 191 0 9 46 0.0 .1 latch: shared pool 2,411 0 8 3 0.2 .1 cursor: pin S 39 0 7 187 0.0 .1 TCP Socket (KGAS) 687 15 5 7 0.0 .0 log file switch (private str 6 0 3 578 0.0 .0 latch free 307 0 2 5 0.0 .0 row cache lock 59 0 1 12 0.0 .0 Disk file operations I/O 565 0 1 1 0.0 .0 kksfbc child completion 4 75 0 43 0.0 .0 buffer busy waits 12 0 0 9 0.0 .0 latch: enqueue hash chains 3 0 0 33 0.0 .0 library cache lock 9 0 0 7 0.0 .0 latch: call allocation 4 0 0 6 0.0 .0 enq: CU - contention 5 0 0 4 0.0 .0 enq: SQ - contention 7 0 0 2 0.0 .0 latch: cache buffers lru cha 140 0 0 0 0.0 .0 latch: redo allocation 2 0 0 3 0.0 .0 cursor: pin X 2 0 0 1 0.0 .0 jobq slave wait 9,902 99 5,071 512 0.7 ------------------------------------------------------------- Background Wait Events DB/Inst: DELPNRA0/delpnra0 Snaps: 6121-6131 -> Only events with Total Wait Time (s) >= .001 are shown -> ordered by Total Wait Time desc, Waits desc (idle events last) Avg %Total %Tim Total Wait wait Waits Call Event Waits out Time (s) (ms) /txn Time ---------------------------- ------------ ---- ---------- ------ -------- ------ LGWR worker group idle 10 0 2,310 ###### 0.0 16.6 AQPC idle 38 100 1,141 30014 0.0 8.2 lreg timer 368 100 1,126 3059 0.0 8.1 heartbeat redo informer 1,073 0 1,124 1047 0.1 8.1 oracle thread bootstrap 207 0 239 1155 0.0 1.7 log file parallel write 1,789 0 145 81 0.1 1.0 control file parallel write 596 0 127 213 0.0 .9 db file parallel write 14,151 0 78 5 1.0 .6 enq: PR - contention 23 0 45 1964 0.0 .3 ASM file metadata operation 625 0 23 37 0.0 .2 latch free 19 0 18 969 0.0 .1 log file sequential read 456 0 11 25 0.0 .1 db file sequential read 3,901 0 10 3 0.3 .1 control file sequential read 4,191 0 10 2 0.3 .1 reliable message 90 0 10 111 0.0 .1 CSS initialization 11 0 3 264 0.0 .0 db file scattered read 90 0 1 13 0.0 .0 direct path write temp 234 0 1 5 0.0 .0 latch: shared pool 214 0 1 4 0.0 .0 log file sync 13 0 1 53 0.0 .0 Disk file operations I/O 478 0 1 1 0.0 .0 latch: row cache objects 31 0 1 17 0.0 .0 Log archive I/O 13 0 0 32 0.0 .0 cursor: pin S wait on X 8 0 0 33 0.0 .0 row cache lock 1 0 0 233 0.0 .0 latch: cache buffers lru cha 10 0 0 16 0.0 .0 log file single write 20 0 0 8 0.0 .0 direct path write 27 0 0 3 0.0 .0 ADR block file read 19 0 0 4 0.0 .0 db file async I/O submit 13,562 0 0 0 0.9 .0 kfk: async disk IO 15 0 0 3 0.0 .0 os thread creation 207 0 0 0 0.0 .0 ADR block file write 5 0 0 6 0.0 .0 CSS operation: action 11 0 0 2 0.0 .0 direct path read 9 0 0 2 0.0 .0 CSS operation: query 66 0 0 0 0.0 .0 LGWR all worker groups 5 0 0 3 0.0 .0 library cache load lock 1 0 0 9 0.0 .0 latch: redo allocation 2 0 0 2 0.0 .0 library cache: mutex X 2 0 0 1 0.0 .0 asynch descriptor resize 407 100 0 0 0.0 .0 LGWR wait for redo copy 13 0 0 0 0.0 .0 rdbms ipc message 8,042 73 21,019 2614 0.6 Space Manager: slave idle wa 729 95 3,552 4873 0.1 class slave wait 282 0 3,352 11885 0.0 DIAG idle wait 2,167 0 2,238 1033 0.1 dispatcher timer 19 100 1,141 60045 0.0 wait for unread message on b 369 100 1,124 3046 0.0 pmon timer 370 100 1,122 3034 0.0 Streams AQ: qmn coordinator 80 50 1,121 14010 0.0 Streams AQ: qmn slave idle w 40 0 1,121 28020 0.0 Background Wait Events DB/Inst: DELPNRA0/delpnra0 Snaps: 6121-6131 -> Only events with Total Wait Time (s) >= .001 are shown -> ordered by Total Wait Time desc, Waits desc (idle events last) Avg %Total %Tim Total Wait wait Waits Call Event Waits out Time (s) (ms) /txn Time ---------------------------- ------------ ---- ---------- ------ -------- ------ ASM background timer 240 0 1,120 4667 0.0 smon timer 21 0 1,047 49843 0.0 JOX Jit Process Sleep 75 99 721 9619 0.0 KSV master wait 118 0 3 25 0.0 ------------------------------------------------------------- Wait Events (fg and bg) DB/Inst: DELPNRA0/delpnra0 Snaps: 6121-6131 -> s - second, cs - centisecond, ms - millisecond, us - microsecond -> %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0 -> Only events with Total Wait Time (s) >= .001 are shown -> ordered by Total Wait Time desc, Waits desc (idle events last) Avg %Total %Tim Total Wait wait Waits Call Event Waits out Time (s) (ms) /txn Time ---------------------------- ------------ ---- ---------- ------ -------- ------ LGWR worker group idle 10 0 2,310 ###### 0.0 16.6 AQPC idle 38 100 1,141 30014 0.0 8.2 lreg timer 368 100 1,126 3059 0.0 8.1 heartbeat redo informer 1,073 0 1,124 1047 0.1 8.1 PL/SQL lock timer 8 100 960 ###### 0.0 6.9 utl_file I/O 421,999 0 880 2 29.1 6.3 db file scattered read 170,003 0 637 4 11.7 4.6 library cache: mutex X 10,303 0 589 57 0.7 4.2 db file parallel read 1,629 0 268 165 0.1 1.9 oracle thread bootstrap 207 0 239 1155 0.0 1.7 db file sequential read 73,538 0 207 3 5.1 1.5 log file sync 191 0 172 900 0.0 1.2 log file parallel write 1,789 0 145 81 0.1 1.0 control file parallel write 596 0 127 213 0.0 .9 cursor: pin S wait on X 155 0 81 523 0.0 .6 db file parallel write 14,151 0 78 5 1.0 .6 enq: PR - contention 23 0 45 1964 0.0 .3 read by other session 23 0 29 1283 0.0 .2 ASM file metadata operation 625 0 23 37 0.0 .2 control file sequential read 6,246 0 20 3 0.4 .1 latch free 326 0 20 61 0.0 .1 library cache load lock 361 0 15 40 0.0 .1 log file sequential read 456 0 11 25 0.0 .1 undo segment extension 25 96 10 415 0.0 .1 reliable message 90 0 10 111 0.0 .1 latch: row cache objects 222 0 9 42 0.0 .1 latch: shared pool 2,625 0 9 3 0.2 .1 cursor: pin S 39 0 7 187 0.0 .1 TCP Socket (KGAS) 687 15 5 7 0.0 .0 log file switch (private str 6 0 3 578 0.0 .0 CSS initialization 11 0 3 264 0.0 .0 Disk file operations I/O 1,043 0 1 1 0.1 .0 direct path write temp 234 0 1 5 0.0 .0 row cache lock 60 0 1 16 0.0 .0 Log archive I/O 13 0 0 32 0.0 .0 kksfbc child completion 4 75 0 43 0.0 .0 latch: cache buffers lru cha 150 0 0 1 0.0 .0 log file single write 20 0 0 8 0.0 .0 buffer busy waits 14 0 0 8 0.0 .0 latch: enqueue hash chains 5 0 0 20 0.0 .0 direct path write 27 0 0 3 0.0 .0 ADR block file read 19 0 0 4 0.0 .0 db file async I/O submit 13,562 0 0 0 0.9 .0 library cache lock 9 0 0 7 0.0 .0 kfk: async disk IO 15 0 0 3 0.0 .0 os thread creation 207 0 0 0 0.0 .0 ADR block file write 5 0 0 6 0.0 .0 latch: call allocation 5 0 0 5 0.0 .0 CSS operation: action 11 0 0 2 0.0 .0 Wait Events (fg and bg) DB/Inst: DELPNRA0/delpnra0 Snaps: 6121-6131 -> s - second, cs - centisecond, ms - millisecond, us - microsecond -> %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0 -> Only events with Total Wait Time (s) >= .001 are shown -> ordered by Total Wait Time desc, Waits desc (idle events last) Avg %Total %Tim Total Wait wait Waits Call Event Waits out Time (s) (ms) /txn Time ---------------------------- ------------ ---- ---------- ------ -------- ------ direct path read 9 0 0 2 0.0 .0 CSS operation: query 66 0 0 0 0.0 .0 enq: CU - contention 5 0 0 4 0.0 .0 enq: SQ - contention 7 0 0 2 0.0 .0 LGWR all worker groups 5 0 0 3 0.0 .0 latch: redo allocation 4 0 0 2 0.0 .0 cursor: pin X 2 0 0 1 0.0 .0 asynch descriptor resize 407 100 0 0 0.0 .0 LGWR wait for redo copy 13 0 0 0 0.0 .0 rdbms ipc message 8,042 73 21,019 2614 0.6 jobq slave wait 9,902 99 5,071 512 0.7 Space Manager: slave idle wa 729 95 3,552 4873 0.1 class slave wait 282 0 3,352 11885 0.0 DIAG idle wait 2,167 0 2,238 1033 0.1 dispatcher timer 19 100 1,141 60045 0.0 wait for unread message on b 369 100 1,124 3046 0.0 pmon timer 370 100 1,122 3034 0.0 Streams AQ: qmn coordinator 80 50 1,121 14010 0.0 Streams AQ: qmn slave idle w 40 0 1,121 28020 0.0 ASM background timer 240 0 1,120 4667 0.0 smon timer 21 0 1,047 49843 0.0 JOX Jit Process Sleep 75 99 721 9619 0.0 KSV master wait 118 0 3 25 0.0 ------------------------------------------------------------- Wait Event Histogram DB/Inst: DELPNRA0/delpnra0 Snaps: 6121-6131 -> Total Waits - units: K is 1000, M is 1000000, G is 1000000000 -> % of Waits - column heading: <=1s is truly <1024ms, >1s is truly >=1024ms -> % of Waits - value: .0 indicates value was <.05%, null is truly 0 -> Ordered by Event (idle events last) Total ----------------- % of Waits ------------------ Event Waits <1ms <2ms <4ms <8ms <16ms <32ms <=1s >1s -------------------------- ----- ----- ----- ----- ----- ----- ----- ----- ----- ADR block file read 19 42.1 26.3 5.3 15.8 5.3 5.3 ADR block file write 5 40.0 40.0 20.0 ADR file lock 6 100.0 AQPC idle 38 100.0 ASM background starting 2 100.0 ASM file metadata operatio 625 72.8 1.4 2.1 2.1 2.4 2.6 16.3 .3 asynch descriptor resize 407 100.0 buffer busy waits 14 85.7 14.3 control file parallel writ 596 .5 47.8 17.8 4.4 6.7 17.8 5.0 control file sequential re 6259 50.3 27.4 15.8 2.8 1.0 1.2 1.6 CSS initialization 11 45.5 18.2 27.3 9.1 CSS operation: action 11 63.6 36.4 CSS operation: query 66 83.3 16.7 cursor: pin S 39 2.6 2.6 12.8 7.7 20.5 53.8 cursor: pin S wait on X 155 3.2 3.9 32.3 21.9 31.0 7.7 cursor: pin X 2 50.0 50.0 db file async I/O submit 13K 100.0 .0 db file parallel read 1632 29.9 23.8 7.7 9.6 5.0 5.0 14.2 4.8 db file parallel write 14K 3.4 3.6 67.4 19.3 2.5 1.9 1.9 .0 db file scattered read 170K 48.5 33.7 11.6 3.1 1.3 .8 1.0 .0 db file sequential read 73K 54.4 24.6 11.9 5.2 2.1 .9 1.0 .0 direct path read 9 33.3 55.6 11.1 direct path write 27 3.7 81.5 14.8 direct path write temp 234 1.7 7.3 70.1 13.2 3.8 1.3 2.6 Disk file operations I/O 1045 98.0 .8 .1 .1 .3 .1 .7 enq: CU - contention 5 80.0 20.0 enq: PR - contention 23 4.3 4.3 4.3 30.4 56.5 enq: SQ - contention 7 85.7 14.3 get branch/thread/sequence 5 100.0 heartbeat redo informer 1073 78.6 21.4 kfk: async disk IO 15 80.0 20.0 kksfbc child completion 4 25.0 75.0 latch: cache buffers chain 6 100.0 latch: cache buffers lru c 150 97.3 1.3 .7 .7 latch: call allocation 5 80.0 20.0 latch: checkpoint queue la 1 100.0 latch: enqueue hash chains 5 60.0 40.0 latch free 326 79.1 5.8 1.8 3.7 1.8 1.5 4.6 1.5 latch: redo allocation 4 50.0 25.0 25.0 latch: row cache objects 224 49.1 3.1 6.7 2.7 9.8 7.6 21.0 latch: shared pool 2625 87.5 2.6 1.6 1.6 1.7 2.2 2.8 LGWR all worker groups 5 80.0 20.0 LGWR wait for redo copy 13 92.3 7.7 LGWR worker group idle 10 100.0 library cache load lock 361 65.1 4.4 5.0 4.2 4.4 3.0 12.5 1.4 library cache lock 9 55.6 22.2 22.2 library cache: mutex X 10K 62.0 3.7 3.9 5.6 13.1 2.7 7.5 1.4 Log archive I/O 13 23.1 7.7 30.8 7.7 15.4 15.4 log file parallel write 1790 14.8 20.6 32.0 8.7 22.7 1.2 log file sequential read 456 9.2 3.7 2.6 36.2 22.4 11.2 14.7 log file single write 20 5.0 90.0 5.0 Wait Event Histogram DB/Inst: DELPNRA0/delpnra0 Snaps: 6121-6131 -> Total Waits - units: K is 1000, M is 1000000, G is 1000000000 -> % of Waits - column heading: <=1s is truly <1024ms, >1s is truly >=1024ms -> % of Waits - value: .0 indicates value was <.05%, null is truly 0 -> Ordered by Event (idle events last) Total ----------------- % of Waits ------------------ Event Waits <1ms <2ms <4ms <8ms <16ms <32ms <=1s >1s -------------------------- ----- ----- ----- ----- ----- ----- ----- ----- ----- log file switch (private s 6 83.3 16.7 log file sync 191 7.3 12.0 9.9 8.9 41.9 19.9 lreg timer 368 100.0 oracle thread bootstrap 207 2.4 20.8 11.6 37.2 28.0 os thread creation 207 99.0 .5 .5 PL/SQL lock timer 8 100.0 read by other session 23 4.3 4.3 4.3 47.8 39.1 Redo Transport Open 5 100.0 reliable message 90 92.2 4.4 1.1 2.2 row cache lock 60 33.3 13.3 11.7 10.0 11.7 13.3 6.7 TCP Socket (KGAS) 687 94.5 1.2 1.3 .4 .4 2.0 .1 undo segment extension 25 20.0 8.0 8.0 4.0 40.0 20.0 utl_file I/O 422K 99.1 .0 .0 .0 .0 .0 .8 .0 ASM background timer 240 4.2 .8 .4 1.7 92.9 class slave wait 282 41.8 .7 .7 .7 5.7 7.1 14.5 28.7 DIAG idle wait 2167 86.0 14.0 dispatcher timer 19 100.0 jobq slave wait 9900 .1 .0 99.9 JOX Jit Process Sleep 75 1.3 2.7 96.0 KSV master wait 118 78.8 1.7 6.8 5.1 2.5 .8 3.4 .8 pmon timer 370 100.0 rdbms ipc message 8042 5.9 .2 .4 .7 1.0 1.1 38.6 52.1 smon timer 21 4.8 4.8 90.5 Space Manager: slave idle 731 .3 .1 .1 1.9 97.5 Streams AQ: qmn coordinato 80 45.0 1.3 1.3 2.5 50.0 Streams AQ: qmn slave idle 40 100.0 wait for unread message on 368 100.0 ------------------------------------------------------------- SQL ordered by CPU DB/Inst: DELPNRA0/delpnra0 Snaps: 6121-6131 -> Total DB CPU (s): 3,528 -> Captured SQL accounts for 56.6% of Total DB CPU -> SQL reported below exceeded 1.0% of Total DB CPU CPU CPU per Elapsd Old Time (s) Executions Exec (s) %Total Time (s) Buffer Gets Hash Value ---------- ------------ ---------- ------ ---------- --------------- ---------- 879.53 3 293.18 24.9 1121.32 2,415,017 1718080854 Module: DBMS_SCHEDULER DECLARE job BINARY_INTEGER := :job; next_date TIMESTAMP WITH TI ME ZONE := :mydate; broken BOOLEAN := FALSE; job_name VARCHAR2 (30) := :job_name; job_subname VARCHAR2(30) := :job_subname; j ob_owner VARCHAR2(30) := :job_owner; job_start TIMESTAMP WITH T 791.73 3 263.91 22.4 1071.21 34,623,435 476427974 Module: DBMS_SCHEDULER call pck_interface.CLEAN_FSE ( ) 54.69 1 54.69 1.6 227.21 177,928 1043884018 Module: DBMS_SCHEDULER DECLARE job BINARY_INTEGER := :job; next_date TIMESTAMP WITH TI ME ZONE := :mydate; broken BOOLEAN := FALSE; job_name VARCHAR2 (30) := :job_name; job_subname VARCHAR2(30) := :job_subname; j ob_owner VARCHAR2(30) := :job_owner; job_start TIMESTAMP WITH T 53.28 0 1.5 100.02 204,530 2034290420 Module: DBMS_SCHEDULER DECLARE job BINARY_INTEGER := :job; next_date TIMESTAMP WITH TI ME ZONE := :mydate; broken BOOLEAN := FALSE; job_name VARCHAR2 (30) := :job_name; job_subname VARCHAR2(30) := :job_subname; j ob_owner VARCHAR2(30) := :job_owner; job_start TIMESTAMP WITH T 39.52 3,963 0.01 1.1 78.29 236,942 4068696614 Module: DBMS_SCHEDULER select START_DATE, START_DATE + 1/96 end_date, :GPTId gpt_id, :F TEId rfe_id_type, sum(VALUE) value from (select dte.WORK_DATE start_date, fse1.VALUE value from (select * from table(pck_retrieve.GET_DATE(:StartDatum, :EindDatum, 38.91 1 38.91 1.1 45.24 67,039 449903094 Module: DBMS_SCHEDULER SELECT SLT.COLUMN_SEQUENCE SEQ_NR, SLT.COLUMN_HEADER DESCRIPTION , DTE.WORK_DATE, FDA.VALUE FROM (SELECT * FROM TABLE(PCK_RETRIEV E.GET_DATE(TRUNC(:B1 ,'YYYY'), DECODE(:B2 ,'O',TRUNC(:B1 +1),'A' ,TRUNC(:B1 ) + 1 ,TRUNC(:B1 )),'D')) ) DTE JOIN (SELECT SLT.SCN_ ------------------------------------------------------------- SQL ordered by Elapsed time for DB: DELPNRA0 Instance: delpnra0 Snaps: 6121 -6 -> Total DB Time (s): 12,930 -> Captured SQL accounts for 24.2% of Total DB Time -> SQL reported below exceeded 1.0% of Total DB Time Elapsed Elap per CPU Old Time (s) Executions Exec (s) %Total Time (s) Physical Reads Hash Value ---------- ------------ ---------- ------ ---------- --------------- ---------- 1121.32 3 373.77 8.7 879.53 2,698 1718080854 Module: DBMS_SCHEDULER DECLARE job BINARY_INTEGER := :job; next_date TIMESTAMP WITH TI ME ZONE := :mydate; broken BOOLEAN := FALSE; job_name VARCHAR2 (30) := :job_name; job_subname VARCHAR2(30) := :job_subname; j ob_owner VARCHAR2(30) := :job_owner; job_start TIMESTAMP WITH T 1071.21 3 357.07 8.3 791.73 75,363 476427974 Module: DBMS_SCHEDULER call pck_interface.CLEAN_FSE ( ) 227.21 1 227.21 1.8 54.69 644 1043884018 Module: DBMS_SCHEDULER DECLARE job BINARY_INTEGER := :job; next_date TIMESTAMP WITH TI ME ZONE := :mydate; broken BOOLEAN := FALSE; job_name VARCHAR2 (30) := :job_name; job_subname VARCHAR2(30) := :job_subname; j ob_owner VARCHAR2(30) := :job_owner; job_start TIMESTAMP WITH T 165.26 0 1.3 5.38 12,774 516534992 Module: DBMS_SCHEDULER call CHK_JOB_DURATION ( ) 164.41 8 20.55 1.3 5.53 12,041 2127873664 Module: DBMS_SCHEDULER SELECT MIN(DIFF_MINUTES) FROM (SELECT (SYSDATE - MAX(TO_DATE(TO_ CHAR(CASE WHEN TO_CHAR(CURRENT_TIMESTAMP,'TZR') = TO_CHAR(ACTUAL _START_DATE,'TZR') THEN ACTUAL_START_DATE WHEN TO_CHAR(CURRENT_T IMESTAMP,'TZR') > TO_CHAR(ACTUAL_START_DATE,'TZR') THEN ACTUAL_S ------------------------------------------------------------- SQL ordered by Gets DB/Inst: DELPNRA0/delpnra0 Snaps: 6121-6131 -> End Buffer Gets Threshold: 10000 Total Buffer Gets: 49,355,244 -> Captured SQL accounts for 79.0% of Total Buffer Gets -> SQL reported below exceeded 1.0% of Total Buffer Gets CPU Elapsd Old Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value --------------- ------------ -------------- ------ -------- --------- ---------- 34,623,435 3 11,541,145.0 70.2 791.73 1071.21 476427974 Module: DBMS_SCHEDULER call pck_interface.CLEAN_FSE ( ) 2,415,017 3 805,005.7 4.9 879.53 1121.32 1718080854 Module: DBMS_SCHEDULER DECLARE job BINARY_INTEGER := :job; next_date TIMESTAMP WITH TI ME ZONE := :mydate; broken BOOLEAN := FALSE; job_name VARCHAR2 (30) := :job_name; job_subname VARCHAR2(30) := :job_subname; j ob_owner VARCHAR2(30) := :job_owner; job_start TIMESTAMP WITH T 1,065,794 553,813 1.9 2.2 22.55 40.49 274848315 Module: DBMS_SCHEDULER SELECT FACTOR FROM UNIT_CONVERSION UCS WHERE UCS.UNT_ID_FROM = : B2 AND UCS.UNT_ID_TO = :B1 AND UCS.CLASS = 'Energy' ------------------------------------------------------------- SQL ordered by Reads DB/Inst: DELPNRA0/delpnra0 Snaps: 6121-6131 -> End Disk Reads Threshold: 1000 Total Disk Reads: 2,137,601 -> Captured SQL accounts for 4.4% of Total Disk Reads -> SQL reported below exceeded 1.0% of Total Disk Reads CPU Elapsd Physical Rds Executions Rds per Exec %Total Time (s) Time (s) Hash Value --------------- ------------ -------------- ------ -------- --------- ---------- 75,363 3 25,121.0 3.5 791.73 1071.21 476427974 Module: DBMS_SCHEDULER call pck_interface.CLEAN_FSE ( ) ------------------------------------------------------------- SQL ordered by Executions DB/Inst: DELPNRA0/delpnra0 Snaps: 6121-6131 -> End Executions Threshold: 100 Total Executions: 6,393,254 -> Captured SQL accounts for 12.5% of Total Executions -> SQL reported below exceeded 1.0% of Total Executions CPU per Elap per Old Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value ------------ --------------- ---------------- ----------- ---------- ---------- 553,813 532,549 1.0 0.00 0.00 274848315 Module: DBMS_SCHEDULER SELECT FACTOR FROM UNIT_CONVERSION UCS WHERE UCS.UNT_ID_FROM = : B2 AND UCS.UNT_ID_TO = :B1 AND UCS.CLASS = 'Energy' 159,582 159,579 1.0 0.00 0.00 4139895953 Module: DBMS_SCHEDULER SELECT UNT.ID FROM UNIT UNT WHERE UPPER(UNT.SHORT_NAME) = :B1 AN D UNT.IND_RELEVANT = 'J' 69,735 69,735 1.0 0.00 0.00 1845873973 Module: DBMS_SCHEDULER select max(fte.IND_TRADE) from FLOW_TYPE fte where fte.ID = :FTE Id ------------------------------------------------------------- SQL ordered by Parse Calls DB/Inst: DELPNRA0/delpnra0 Snaps: 6121-6131 -> End Parse Calls Threshold: 1000 Total Parse Calls: 12,454,146 -> Captured SQL accounts for 1.9% of Total Parse Calls -> SQL reported below exceeded 1.0% of Total Parse Calls % Total Old Parse Calls Executions Parses Hash Value ------------ ------------ -------- ---------- 159,581 159,582 1.28 4139895953 Module: DBMS_SCHEDULER SELECT UNT.ID FROM UNIT UNT WHERE UPPER(UNT.SHORT_NAME) = :B1 AN D UNT.IND_RELEVANT = 'J' ------------------------------------------------------------- SQL ordered by Sharable Memory DB/Inst: DELPNRA0/delpnra0 Snaps: 6121-6131 -> End Sharable Memory Threshold(KB): 1024 Max End Sharable Sharable Old Memory (KB) Memory (KB) Parse Calls Executions % Total Hash Value ----------- ----------- ------------ ------------ ------- ---------- 2,726 2,722 282 3,572 0.5 3322665590 select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags,i .property,i.pctfree$,i.initrans,i.maxtrans,i.blevel,i.leafcnt,i. distkey,i.lblkkey,i.dblkkey,i.clufac,i.cols,i.analyzetime,i.samp lesize,i.dataobj#,nvl(i.degree,1),nvl(i.instances,1),i.rowcnt,mo 2,561 2,561 290 16,188 0.5 656850954 select obj#,type#,ctime,mtime,stime, status, dataobj#, flags, oi d$, spare1, spare2, spare3, signature, spare7, spare8, spare9 fr om obj$ where owner#=:1 and name=:2 and namespace=:3 and remoteo wner is null and linkname is null and subname is null ------------------------------------------------------------- Instance Activity Stats DB/Inst: DELPNRA0/delpnra0 Snaps: 6121-6131 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ active txn count during cleanout 1,530 1.4 0.1 application wait time 0 0.0 0.0 background checkpoints completed 5 0.0 0.0 background checkpoints started 5 0.0 0.0 background timeouts 5,960 5.3 0.4 Batched IO block miss count 41,689 37.0 2.9 Batched IO (bound) vector count 10,500 9.3 0.7 Batched IO buffer defrag count 2 0.0 0.0 Batched IO double miss count 1,324 1.2 0.1 Batched IO (full) vector count 11 0.0 0.0 Batched IO same unit count 24,269 21.6 1.7 Batched IO single block count 9,194 8.2 0.6 Batched IO slow jump count 0 0.0 0.0 Batched IO vector block count 8,182 7.3 0.6 Batched IO vector read count 1,715 1.5 0.1 branch node splits 0 0.0 0.0 buffer is not pinned count 22,539,031 20,016.9 1,552.9 buffer is pinned count 9,043,828 8,031.8 623.1 bytes received via SQL*Net from c 0 0.0 0.0 bytes sent via SQL*Net to client 0 0.0 0.0 Cached Commit SCN referenced 819,175 727.5 56.4 calls to get snapshot scn: kcmgss 5,451,290 4,841.3 375.6 calls to kcmgas 16,921 15.0 1.2 calls to kcmgcs 410,623 364.7 28.3 CCursor + sql area evicted 6,824 6.1 0.5 cell physical IO interconnect byt 22,761,298,944 20,214,297.5 1,568,230.6 change write time 3,991 3.5 0.3 cleanout - number of ktugct calls 1,516 1.4 0.1 cleanouts and rollbacks - consist 1,138 1.0 0.1 cleanouts only - consistent read 75 0.1 0.0 cluster key scan block gets 1,433,200 1,272.8 98.8 cluster key scans 1,394,667 1,238.6 96.1 commit batch/immediate performed 5 0.0 0.0 commit batch/immediate requested 5 0.0 0.0 commit cleanout failures: block l 5 0.0 0.0 commit cleanout failures: buffer 55 0.1 0.0 commit cleanout failures: callbac 10 0.0 0.0 commit cleanout failures: cannot 0 0.0 0.0 commit cleanouts 198,309 176.1 13.7 commit cleanouts successfully com 198,239 176.1 13.7 commit immediate performed 5 0.0 0.0 commit immediate requested 5 0.0 0.0 Commit SCN cached 48 0.0 0.0 commit txn count during cleanout 147 0.1 0.0 concurrency wait time 71,142 63.2 4.9 consistent changes 206,780 183.6 14.3 consistent gets 30,550,947 27,132.3 2,104.9 consistent gets direct 0 0.0 0.0 consistent gets examination 16,322,785 14,496.3 1,124.6 consistent gets examination (fast 16,317,659 14,491.7 1,124.3 consistent gets from cache 30,550,948 27,132.3 2,104.9 consistent gets pin 14,228,165 12,636.0 980.3 consistent gets pin (fastpath) 13,988,690 12,423.4 963.8 CPU used by this session 359,128 318.9 24.7 CPU used when call started 431 0.4 0.0 CR blocks created 1,314 1.2 0.1 Instance Activity Stats DB/Inst: DELPNRA0/delpnra0 Snaps: 6121-6131 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ cursor authentications 5,343 4.8 0.4 cursor reload failures 407 0.4 0.0 data blocks consistent reads - un 127,629 113.4 8.8 db block changes 9,457,816 8,399.5 651.6 db block gets 18,803,840 16,699.7 1,295.6 db block gets direct 575 0.5 0.0 db block gets from cache 18,803,257 16,699.2 1,295.5 db block gets from cache (fastpat 7,933,104 7,045.4 546.6 DB time 1,407,411 1,249.9 97.0 DBWR checkpoint buffers written 65,867 58.5 4.5 DBWR checkpoints 5 0.0 0.0 DBWR object drop buffers written 0 0.0 0.0 DBWR parallel query checkpoint bu 0 0.0 0.0 DBWR revisited being-written buff 0 0.0 0.0 DBWR tablespace checkpoint buffer 0 0.0 0.0 DBWR thread checkpoint buffers wr 0 0.0 0.0 DBWR transaction table writes 66 0.1 0.0 DBWR undo block writes 23,211 20.6 1.6 deferred (CURRENT) block cleanout 67,794 60.2 4.7 dirty buffers inspected 17,513 15.6 1.2 Effective IO time 0 0.0 0.0 enqueue conversions 718 0.6 0.1 enqueue releases 1,365,018 1,212.3 94.1 enqueue requests 1,365,027 1,212.3 94.1 enqueue timeouts 5 0.0 0.0 enqueue waits 35 0.0 0.0 exchange deadlocks 0 0.0 0.0 execute count 6,393,254 5,677.9 440.5 fastpath consistent get quota lim 0 0.0 0.0 file io service time 0 0.0 0.0 file io wait time 1,465,222,828,185 ############## ############ frame signature mismatch 5 0.0 0.0 free buffer inspected 2,345,542 2,083.1 161.6 free buffer requested 2,367,676 2,102.7 163.1 heap block compress 5,429 4.8 0.4 Heap Segment Array Inserts 275 0.2 0.0 Heap Segment Array Updates 36 0.0 0.0 Heatmap Blklevel Flush Task Count 1 0.0 0.0 hot buffers moved to head of LRU 420,858 373.8 29.0 HSC Heap Segment Block Changes 2,684,521 2,384.1 185.0 immediate (CR) block cleanout app 1,213 1.1 0.1 immediate (CURRENT) block cleanou 283 0.3 0.0 IMU commits 182 0.2 0.0 IMU contention 38 0.0 0.0 IMU CR rollbacks 1 0.0 0.0 IMU- failed to get a private stra 4 0.0 0.0 IMU Flushes 190 0.2 0.0 IMU ktichg flush 2 0.0 0.0 IMU pool not allocated 4 0.0 0.0 IMU recursive-transaction flush 0 0.0 0.0 IMU Redo allocation size 3,303,520 2,933.9 227.6 IMU undo allocation size 5,481,632 4,868.2 377.7 in call idle wait time 5,040,397 4,476.4 347.3 index crx upgrade (positioned) 0 0.0 0.0 index fast full scans (full) 705 0.6 0.1 index fetch by key 4,847,269 4,304.9 334.0 Instance Activity Stats DB/Inst: DELPNRA0/delpnra0 Snaps: 6121-6131 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ index scans kdiixs1 4,035,041 3,583.5 278.0 java call heap collected bytes 59,701,784 53,021.1 4,113.4 java call heap collected count 566,631 503.2 39.0 java call heap gc count 41 0.0 0.0 java call heap live object count 134,726 119.7 9.3 java call heap live object count 135,029 119.9 9.3 java call heap live size 14,567,208 12,937.1 1,003.7 java call heap live size max 14,567,208 12,937.1 1,003.7 java call heap object count 242,488 215.4 16.7 java call heap object count max 263,336 233.9 18.1 java call heap total size 32,505,856 28,868.4 2,239.6 java call heap total size max 32,505,856 28,868.4 2,239.6 java call heap used size 26,972,128 23,953.9 1,858.4 java call heap used size max 27,387,664 24,323.0 1,887.0 java session heap live size 2,871,296 2,550.0 197.8 java session heap live size max 2,871,296 2,550.0 197.8 java session heap used size 2,871,296 2,550.0 197.8 java session heap used size max 2,871,296 2,550.0 197.8 KTFB alloc req 101 0.1 0.0 KTFB alloc space (block) 100,990,976 89,690.0 6,958.2 KTFB alloc time (ms) 9,301 8.3 0.6 KTFB apply req 177 0.2 0.0 KTFB apply time (ms) 4,226 3.8 0.3 KTFB commit req 0 0.0 0.0 KTFB commit time (ms) 0 0.0 0.0 KTFB free req 82 0.1 0.0 KTFB free space (block) 10,496 9.3 0.7 KTFB free time (ms) 241 0.2 0.0 leaf node splits 59 0.1 0.0 leaf node 90-10 splits 13 0.0 0.0 lob reads 20,890 18.6 1.4 LOB table id lookup cache misses 0 0.0 0.0 lob writes 23,895 21.2 1.7 lob writes unaligned 22,733 20.2 1.6 logical read bytes from cache 404,309,262,336 359,066,840.4 ############ logons cumulative 385 0.3 0.0 max cf enq hold time 0 0.0 0.0 messages received 16,256 14.4 1.1 messages sent 16,256 14.4 1.1 no buffer to keep pinned count 3,008 2.7 0.2 no work - consistent read gets 13,686,554 12,155.0 943.0 non-idle wait count 731,664 649.8 50.4 non-idle wait time 365,009 324.2 25.2 Number of read IOs issued 0 0.0 0.0 opened cursors cumulative 17,224,336 15,296.9 1,186.7 parse count (describe) 0 0.0 0.0 parse count (failures) 12 0.0 0.0 parse count (hard) 16,186 14.4 1.1 parse count (total) 12,454,146 11,060.5 858.1 parse time cpu 14,092 12.5 1.0 parse time elapsed 63,653 56.5 4.4 physical read bytes 17,511,227,392 15,551,711.7 1,206,506.0 physical read IO requests 251,444 223.3 17.3 physical read total bytes 18,739,858,432 16,642,858.3 1,291,157.4 physical read total IO requests 258,921 230.0 17.8 physical read total multi block r 103,752 92.1 7.2 Instance Activity Stats DB/Inst: DELPNRA0/delpnra0 Snaps: 6121-6131 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ physical reads 2,137,601 1,898.4 147.3 physical reads cache 2,137,566 1,898.4 147.3 physical reads cache prefetch 1,892,323 1,680.6 130.4 physical reads direct 35 0.0 0.0 physical reads direct (lob) 0 0.0 0.0 physical reads direct temporary t 0 0.0 0.0 physical reads prefetch warmup 0 0.0 0.0 physical write bytes 831,209,472 738,196.7 57,269.5 physical write IO requests 19,801 17.6 1.4 physical write total bytes 4,021,440,512 3,571,439.2 277,073.2 physical write total IO requests 26,908 23.9 1.9 physical write total multi block 5,944 5.3 0.4 physical writes 101,466 90.1 7.0 physical writes direct 637 0.6 0.0 physical writes direct (lob) 27 0.0 0.0 physical writes direct temporary 489 0.4 0.0 physical writes from cache 100,829 89.6 7.0 physical writes non checkpoint 34,197 30.4 2.4 pinned buffers inspected 275 0.2 0.0 pinned cursors current 33 0.0 0.0 prefetch warmup blocks aged out b 0 0.0 0.0 prefetched blocks aged out before 0 0.0 0.0 process last non-idle time 1,125 1.0 0.1 PX local messages recv'd 0 0.0 0.0 PX local messages sent 0 0.0 0.0 recovery blocks read 0 0.0 0.0 recursive calls 54,050,274 48,002.0 3,724.0 recursive cpu usage 413,281 367.0 28.5 redo blocks checksummed by FG (ex 9,202 8.2 0.6 redo blocks read for recovery 0 0.0 0.0 redo blocks written 2,017,284 1,791.6 139.0 redo blocks written (group 0) 0 0.0 0.0 redo blocks written (group 1) 0 0.0 0.0 redo buffer allocation retries 19 0.0 0.0 redo entries 4,038,416 3,586.5 278.2 redo KB read 1,078,955 958.2 74.3 redo k-bytes read for recovery 0 0.0 0.0 redo log space requests 44 0.0 0.0 redo log space wait time 351 0.3 0.0 redo ordering marks 9,499 8.4 0.7 redo size 998,419,304 886,695.7 68,790.1 redo size for direct writes 708,296 629.0 48.8 redo subscn max counts 362 0.3 0.0 redo synch long waits 1 0.0 0.0 redo synch time 17,177 15.3 1.2 redo synch time overhead count ( 149 0.1 0.0 redo synch time overhead count ( 12 0.0 0.0 redo synch time overhead count ( 11 0.0 0.0 redo synch time overhead count (i 5 0.0 0.0 redo synch time overhead count (1 16 0.0 0.0 redo synch time overhead (usec) 1,360,110,035 1,207,913.0 93,710.2 redo synch time (usec) 171,821,443 152,594.5 11,838.3 redo synch writes 193 0.2 0.0 redo wastage 535,848 475.9 36.9 redo write finish time 145,182,608 128,936.6 10,002.9 redo write gather time 130,912 116.3 9.0 Instance Activity Stats DB/Inst: DELPNRA0/delpnra0 Snaps: 6121-6131 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ redo write info find 193 0.2 0.0 redo write info find fail 0 0.0 0.0 redo write schedule time 142,067 126.2 9.8 redo write size count ( 4KB) 97 0.1 0.0 redo write size count ( 8KB) 96 0.1 0.0 redo write size count ( 16KB) 105 0.1 0.0 redo write size count ( 32KB) 61 0.1 0.0 redo write size count ( 128KB) 284 0.3 0.0 redo write size count ( 256KB) 129 0.1 0.0 redo write size count ( 512KB) 99 0.1 0.0 redo write size count (inf) 776 0.7 0.1 redo write size count (1024KB) 142 0.1 0.0 redo write time 14,519 12.9 1.0 redo write time (usec) 145,190,835 128,943.9 10,003.5 redo write total time 148,379,467 131,775.7 10,223.2 redo write worker delay (usec) 0 0.0 0.0 redo writes 1,789 1.6 0.1 redo writes adaptive all 1,789 1.6 0.1 redo writes adaptive worker 0 0.0 0.0 redo writes (group 0) 0 0.0 0.0 redo writes (group 1) 0 0.0 0.0 Requests to/from client 0 0.0 0.0 rollback changes - undo records a 1,321,499 1,173.6 91.1 rollbacks only - consistent read 115 0.1 0.0 root node splits 0 0.0 0.0 RowCR - row contention 14 0.0 0.0 RowCR attempts 11,863 10.5 0.8 RowCR hits 11,124 9.9 0.8 rows fetched via callback 3,259,332 2,894.6 224.6 scheduler wait time 0 0.0 0.0 securefile allocation bytes 221,184 196.4 15.2 securefile allocation chunks 19 0.0 0.0 securefile bytes non-transformed 223,445 198.4 15.4 securefile direct read bytes 0 0.0 0.0 securefile direct read ops 0 0.0 0.0 securefile direct write bytes 221,184 196.4 15.2 securefile direct write ops 19 0.0 0.0 securefile number of non-transfor 43 0.0 0.0 segment cfs allocations 0 0.0 0.0 segment chunks allocation from di 19 0.0 0.0 segment dispenser allocations 19 0.0 0.0 segment dispenser load empty 0 0.0 0.0 segment dispenser load tasks 0 0.0 0.0 segment prealloc bytes 0 0.0 0.0 segment prealloc ops 2 0.0 0.0 segment prealloc tasks 0 0.0 0.0 segment prealloc time (ms) 199 0.2 0.0 segment prealloc ufs2cfs bytes 0 0.0 0.0 segment total chunk allocation 0 0.0 0.0 session connect time 0 0.0 0.0 session cursor cache hits 3,304,084 2,934.4 227.7 session logical reads 49,355,244 43,832.4 3,400.5 session pga memory 83,649,224 74,288.8 5,763.4 session pga memory max 173,761,224 154,317.3 11,972.0 session uga memory 1,015,913,056 902,231.8 69,995.4 session uga memory max 1,845,619,096 1,639,093.3 127,161.3 Instance Activity Stats DB/Inst: DELPNRA0/delpnra0 Snaps: 6121-6131 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ shared hash latch upgrades - no w 269,378 239.2 18.6 shared hash latch upgrades - wait 0 0.0 0.0 shared io pool buffer get success 19 0.0 0.0 SMON posted for undo segment shri 12 0.0 0.0 sorts (disk) 0 0.0 0.0 sorts (memory) 273,461 242.9 18.8 sorts (rows) 35,338,442 31,384.1 2,434.8 sql area evicted 10,764 9.6 0.7 sql area purged 94 0.1 0.0 SQL*Net roundtrips to/from client 0 0.0 0.0 summed dirty queue length 169,591 150.6 11.7 switch current to new buffer 190,030 168.8 13.1 table fetch by rowid 10,581,227 9,397.2 729.0 table fetch continued row 55,409 49.2 3.8 table scan blocks gotten 1,560,634 1,386.0 107.5 table scan disk non-IMC rows gott 99,788,763 88,622.4 6,875.4 table scan rows gotten 99,833,291 88,661.9 6,878.4 table scans (direct read) 0 0.0 0.0 table scans (long tables) 29 0.0 0.0 table scans (short tables) 194,903 173.1 13.4 TBS Extension: bytes extended 0 0.0 0.0 TBS Extension: files extended 0 0.0 0.0 TBS Extension: tasks created 0 0.0 0.0 TBS Extension: tasks executed 0 0.0 0.0 temp space allocated (bytes) 1,048,576 931.2 72.3 total cf enq hold time 42,875 38.1 3.0 total number of cf enq holders 71 0.1 0.0 total number of times SMON posted 20 0.0 0.0 transaction rollbacks 4 0.0 0.0 transaction tables consistent rea 0 0.0 0.0 transaction tables consistent rea 0 0.0 0.0 undo change vector size 169,386,072 150,431.7 11,670.5 user calls 1,177 1.1 0.1 user commits 14,514 12.9 1.0 user I/O wait time 202,229 179.6 13.9 user logons cumulative 0 0.0 0.0 user logouts cumulative 0 0.0 0.0 user rollbacks 0 0.0 0.0 workarea executions - onepass 0 0.0 0.0 workarea executions - optimal 320,257 284.4 22.1 write clones created in backgroun 0 0.0 0.0 write clones created in foregroun 26 0.0 0.0 ------------------------------------------------------------- Instance Activity Stats DB/Inst: DELPNRA0/delpnra0 Snaps: 6121-6131 -> Statistics with absolute values (should not be diffed) Statistic Begin Value End Value --------------------------------- --------------- --------------- logons current 55 62 opened cursors current 104 186 session cursor cache count 2,080,642 2,099,152 workarea memory allocated 6,503 8,062 ------------------------------------------------------------- Instance Activity Stats DB/Inst: DELPNRA0/delpnra0 Snaps: 6121-6131 -> Statistics identified by '(derived)' come from sources other than SYSSTAT Statistic Total per Hour --------------------------------- ------------------ --------- log switches (derived) 5 15.99 ------------------------------------------------------------- OS Statistics DB/Inst: DELPNRA0/delpnra0 Snaps: 6121-6131 -> ordered by statistic type (CPU use, Virtual Memory, Hardware Config), Name Statistic Total ------------------------- ---------------------- BUSY_TIME 370,222 IDLE_TIME 79,628 SYS_TIME 40,302 USER_TIME 329,929 RSRC_MGR_CPU_WAIT_TIME 0 PHYSICAL_MEMORY_BYTES 17,179,332,608 NUM_CPU_CORES 4 NUM_CPUS 4 NUM_CPU_SOCKETS 4 ------------------------------------------------------------- OS Statistics - detail DB/Inst: DELPNRA0/delpnra0 Snaps: 6121-6131 Snap Snapshot Id Day Time Load %Busy %User %System %WIO %WCPU ------ --------------- ------ ------ ------ ------- ------ ------ 6121 Zo 22 03:32:59 20.0 6131 Zo 22 03:51:45 11.0 82.3 73.3 9.0 ------------------------------------------------------------- IO Stat by Function - summary DB/Inst: DELPNRA0/delpnra0 Snaps: 6121-6131 ->Data Volume values suffixed with M,G,T,P are in multiples of 1024, other values suffixed with K,M,G,T,P are in multiples of 1000 ->ordered by Data Volume (Read+Write) desc ---------- Read --------- --------- Write -------- --- Wait ---- Data Requests Data Data Requests Data Avg Function Volume /sec Vol/sec Volume /sec Vol/sec Count Tm(ms) --------------- ------ -------- -------- ------ -------- -------- ------ ------ Buffer Cache Re 16G 222.9 14.8M 245K 0.0 Others 1169M 6.5 1.0M 1074M 2.2 1.0M 6759 0.0 LGWR 2M .1 .0M 1972M 4.4 1.8M 1972 0.0 Direct Reads 1M .0 .0M .0 0.0 Streams AQ .0 5 0.0 ------------------------------------------------------------- IO Stat by Function - detail DB/Inst: DELPNRA0/delpnra0 Snaps: 6121-6131 ->ordered by Data Volume (Read+Write) desc ----------- Read ---------- ----------- Write --------- Small Large Small Large Small Large Small Large Read Read Data Data Write Write Data Data Function Reqs Reqs Read Read Reqs Reqs Writn Writn ------------------ ------ ------ ------ ------ ------ ------ ------ ------ Buffer Cache Reads 148K 103K 3873M 13G Others 6206 1136 98M 1071M 1394 1059 20M 1054M LGWR 115 2M 2352 2594 66M 1906M Direct Reads 35 1M 1 Streams AQ 5 ------------------------------------------------------------- Tablespace IO Stats DB/Inst: DELPNRA0/delpnra0 Snaps: 6121-6131 ->ordered by IOs (Reads + Writes) desc Tablespace ------------------------------ Av Av Av Av Buffer Av Buf Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms) -------------- ------- ------ ------- ------------ -------- ---------- ------ PNR_IDX 192,532 171 489.1 8.9 253 0 46 600.4 PNR_DATA 41,163 37 ###### 9.6 15,951 14 3 643.3 SYSAUX 10,186 9 ###### 1.3 282 0 0 0.0 SYSTEM 6,184 5 347.6 2.0 66 0 7 5.7 UNDOTBS1 233 0 1.5 1.0 2,774 2 10 6.0 USERS 486 0 4.4 1.4 206 0 0 0.0 TEMP 241 0 5.0 2.0 288 0 0 0.0 STATSPACK_DATA 5 0 6.0 1.0 5 0 0 0.0 ------------------------------------------------------------- File IO Stats DB/Inst: DELPNRA0/delpnra0 Snaps: 6121-6131 ->Mx Rd Bkt: Max bucket time for single block read ->ordered by Tablespace, File Tablespace Filename ------------------------ ---------------------------------------------------- Av Mx Av Av Rd Rd Av Av Buffer BufWt Reads Reads/s (ms) Bkt Blks/Rd Writes Writes/s Waits (ms) -------------- ------- ----- --- ------- ------------ -------- ---------- ------ PNR_DATA +DELPNRA0_DATA/DELPNRA0/DATAFILE/pnr_data.266.866627 41,163 37 ##### 1 9.6 15,951 14 3 643.3 PNR_IDX +DELPNRA0_DATA/DELPNRA0/DATAFILE/pnr_idx.267.8666278 192,532 171 ##### 1 8.9 253 0 46 600.4 STATSPACK_DATA +DELPNRA0_DATA/DELPNRA0/DATAFILE/statspack_data.268. 5 0 6.0 1.0 5 0 0 SYSAUX +DELPNRA0_DATA/DELPNRA0/DATAFILE/sysaux.256.86662124 10,186 9 ##### 1 1.3 282 0 0 SYSTEM +DELPNRA0_DATA/DELPNRA0/DATAFILE/system.257.86662130 6,184 5 ##### 1 2.0 66 0 7 5.7 TEMP +DELPNRA0_DATA/DELPNRA0/TEMPFILE/temp.264.866621519 241 0 5.0 2.0 288 0 0 UNDOTBS1 +DELPNRA0_DATA/DELPNRA0/DATAFILE/undotbs1.259.888303 233 0 1.5 1 1.0 2,774 2 10 6.0 USERS +DELPNRA0_DATA/DELPNRA0/DATAFILE/users.258.866621373 486 0 4.4 1 1.4 206 0 0 ------------------------------------------------------------- File Read Histogram Stats DB/Inst: DELPNRA0/delpnra0 Snaps: 6121-6131 ->Number of single block reads in each time range ->Tempfiles are not included ->ordered by Tablespace, File Tablespace Filename ------------------------ ---------------------------------------------------- 0 - 2 ms 2 - 4 ms 4 - 8 ms 8 - 16 ms 16 - 32 ms 32+ ms ------------ ------------ ------------ ------------ ------------ ------------ SYSAUX +DELPNRA0_DATA/DELPNRA0/DATAFILE/sysaux.256.86662124 3,155 0 0 0 0 0 SYSTEM +DELPNRA0_DATA/DELPNRA0/DATAFILE/system.257.86662130 2,329 0 0 0 0 0 UNDOTBS1 +DELPNRA0_DATA/DELPNRA0/DATAFILE/undotbs1.259.888303 149 0 0 0 0 0 PNR_IDX +DELPNRA0_DATA/DELPNRA0/DATAFILE/pnr_idx.267.8666278 28,516 0 0 0 0 0 USERS +DELPNRA0_DATA/DELPNRA0/DATAFILE/users.258.866621373 130 0 0 0 0 0 PNR_DATA +DELPNRA0_DATA/DELPNRA0/DATAFILE/pnr_data.266.866627 5,415 0 0 0 0 0 ------------------------------------------------------------- IO Stats Function Detail DB/Inst: DELPNRA0/delpnra0 Snaps: 6121-6131 Small Small Large Large Function FileType Read Write Read Write Wait Time Name Name (MB) (MB) (MB) (MB) Waits (ms) ------------ ------------ ------- ------- ------- ------- --------- ----------- RMAN Control File 0 0 0 0 0 0 RMAN Data File 0 0 0 0 0 0 RMAN Archive Log 0 0 0 0 0 0 RMAN Other(8) 0 0 0 0 0 0 RMAN Parameter Fi 0 0 0 0 0 0 RMAN OSM File 0 0 0 0 0 0 DBWR Control File 0 0 0 0 0 0 DBWR Data File 0 501 0 287 0 0 LGWR Control File 2 1 0 0 95 282 LGWR Data File 0 0 0 0 0 0 LGWR Log File 0 65 0 1906 40 202 LGWR Other(8) 0 0 0 0 1837 148574 Streams AQ Data File 0 0 0 0 5 16 Recovery Control File 0 0 0 0 0 0 Recovery Data File 0 0 0 0 0 0 Recovery Log File 0 0 0 0 0 0 Recovery Other(8) 0 0 0 0 0 0 Recovery OSM File 0 0 0 0 0 0 Buffer Cache Data File 3872 0 12829 0 243168 853293 Buffer Cache Other(8) 0 0 0 0 1628 268337 Direct Reads Control File 0 0 0 0 0 0 Direct Reads Data File 1 0 0 0 0 0 Direct Write Control File 0 0 0 0 0 0 Direct Write Data File 0 1 0 0 3 0 Others(14) Control File 96 17 10 0 6161 19023 Others(14) Data File 0 3 0 1 6 16 Others(14) Log File 1 0 1053 0 35 47 Others(14) Archive Log 0 0 0 1053 0 0 Others(14) Other(8) 0 0 0 0 557 123246 Others(14) Parameter Fi 0 0 0 0 0 0 Others(14) OSM File 1 0 8 0 0 0 Others(14) Password Fil 0 0 0 0 0 0 Others(14) External Tab 0 0 0 0 0 0 Others(14) OS Audit Fil 0 0 0 0 0 0 ------------------------------------------------------------- Instance Recovery Stats DB/Inst: DELPNRA0/delpnra0 Snaps: 6121-6131 -> B: Begin snapshot, E: End snapshot Targt Estd Log File Log Ckpt Log Ckpt MTTR MTTR Recovery Actual Target Size Timeout Interval (s) (s) Estd IOs Redo Blks Redo Blks Redo Blks Redo Blks Redo Blks - ----- ----- ---------- --------- --------- ---------- --------- ------------ B 0 46 24819 693489 829440 829440 1588538 E 0 40 11402 558156 829440 829440 2267567 ------------------------------------------------------------- Memory Dynamic Components DB/Inst: DELPNRA0/delpnra0 Snaps: 6121-6131 -> Op - memory resize Operation -> Cache: D: Default, K: Keep, R: Recycle -> Mode: DEF: DEFerred mode, IMM: IMMediate mode Begin Snap End Snap Op Last Op Cache Size (M) Size (M) Count Type/Mode Last Op Time ---------------------- ---------- -------- ------- ---------- --------------- D:buffer cache 1,408 0 GROW/DEF 21-Mei 20:24:10 java pool 32 0 SHRINK/DEF 20-Mei 14:45:18 large pool 32 0 STATIC PGA Target 1,024 0 STATIC SGA Target 2,048 0 STATIC Shared IO Pool 16 0 STATIC shared pool 528 0 SHRINK/DEF 21-Mei 20:24:10 streams pool 16 0 STATIC ------------------------------------------------------------- Buffer Pool Advisory DB/Inst: DELPNRA0/delpnra0 End Snap: 6131 -> Only rows with estimated physical reads >0 are displayed -> ordered by Pool, Block Size, Buffers For Estimate Est Phys Estimated Est Size for Size Buffers Read Phys Reads Est Phys % dbtime P Est (M) Factr (thousands) Factr (thousands) Read Time for Rds --- -------- ----- ------------ ------ -------------- ------------ -------- D 128 .1 16 1.2 263,159 ############ ###### D 256 .2 31 1.1 249,285 829,680,564 ###### D 384 .3 47 1.1 243,777 709,346,662 ###### D 512 .4 63 1.1 239,492 615,729,390 ###### D 640 .5 79 1.1 235,747 533,907,975 ###### D 768 .5 94 1.1 232,398 460,748,640 ###### D 896 .6 110 1.0 229,222 391,367,053 ###### D 1,024 .7 126 1.0 226,418 330,092,743 ###### D 1,152 .8 142 1.0 224,003 277,338,642 ###### D 1,280 .9 157 1.0 221,774 228,642,944 ###### D 1,408 1.0 173 1.0 219,819 185,929,880 ###### D 1,536 1.1 189 1.0 217,986 145,881,044 ###### D 1,664 1.2 205 1.0 216,212 107,126,234 ###### D 1,792 1.3 220 1.0 214,392 67,363,601 ###### D 1,920 1.4 236 1.0 212,692 30,227,530 ###### D 2,048 1.5 252 1.0 211,097 93,603 5.2 D 2,176 1.5 268 1.0 209,484 93,594 5.2 D 2,304 1.6 283 0.9 207,971 93,586 5.2 D 2,432 1.7 299 0.9 206,537 93,578 5.2 D 2,560 1.8 315 0.9 205,129 93,571 5.2 ------------------------------------------------------------- Buffer Pool Statistics DB/Inst: DELPNRA0/delpnra0 Snaps: 6121-6131 -> Standard block size Pools D: default, K: keep, R: recycle -> Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32k -> Buffers: the number of buffers. Units of K, M, G are divided by 1000 Free Writ Buffer Pool Buffer Physical Physical Buffer Comp Busy P Buffers Hit% Gets Reads Writes Waits Wait Waits --- ------- ---- -------------- ------------ ----------- ------- ---- ---------- D 172K 96 49,371,862 2,137,511 100,829 0 0 66 ------------------------------------------------------------- Buffer wait Statistics DB/Inst: DELPNRA0/delpnra0 Snaps: 6121-6131 -> ordered by wait time desc, waits desc Class Waits Total Wait Time (s) Avg Time (ms) ---------------------- ----------- ------------------- ------------- data block 56 30 528 undo header 10 0 6 ------------------------------------------------------------- PGA Aggr Target Stats DB/Inst: DELPNRA0/delpnra0 Snaps: 6121-6131 -> B: Begin snap E: End snap (rows identified with B or E contain data which is absolute i.e. not diffed over the interval) -> PGA cache hit % - percentage of W/A (WorkArea) data processed only in-memory -> Auto PGA Target - actual workarea memory target -> W/A PGA Used - amount of memory used for all WorkAreas (manual + auto) -> %PGA W/A Mem - percentage of PGA memory allocated to WorkAreas -> %Auto W/A Mem - percentage of WorkArea memory controlled by Auto Mem Mgmt -> %Man W/A Mem - percentage of WorkArea memory under Manual control PGA Cache Hit % W/A MB Processed Extra W/A MB Read/Written --------------- ---------------- ------------------------- 100.0 71,834 0 %PGA %Auto %Man PGA Aggr Auto PGA PGA Mem W/A PGA W/A W/A W/A Global Mem Target(M) Target(M) Alloc(M) Used(M) Mem Mem Mem Bound(K) - --------- --------- ---------- ---------- ------ ------ ------ ---------- B 1,024 562 585.2 5.2 .9 100.0 .0 104,850 E 1,024 533 681.1 3.8 .6 100.0 .0 104,850 ------------------------------------------------------------- PGA Aggr Target Histogram DB/Inst: DELPNRA0/delpnra0 Snaps: 6121-6131 -> Optimal Executions are purely in-memory operations Low High Optimal Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs ------- ------- -------------- ------------- ------------ ------------ 2K 4K 258,976 258,976 0 0 64K 128K 1,236 1,236 0 0 128K 256K 50 50 0 0 256K 512K 30 30 0 0 512K 1024K 773 773 0 0 1M 2M 55,161 55,161 0 0 2M 4M 4,302 4,302 0 0 4M 8M 42 42 0 0 ------------------------------------------------------------- PGA Memory Advisory DB/Inst: DELPNRA0/delpnra0 End Snap: 6131 -> When using Auto Memory Mgmt, minimally choose a pga_aggregate_target value where Estd PGA Overalloc Count is 0 Estd Extra Estd PGA Aggr W/A MB Estd Time PGA Estd PGA Target Size W/A MB Read/Written to Process Cache Overalloc Est (MB) Factr Processed to Disk Bytes (s) Hit % Count ---------- ------ -------------- -------------- ---------- ------ ---------- 128 0.1 8,457,683 568,934 ######## 94.0 22,080 256 0.3 8,457,683 568,934 ######## 94.0 22,080 512 0.5 8,457,683 107,195 ######## 99.0 555 768 0.8 8,457,683 115 ######## 100.0 0 1,024 1.0 8,457,683 115 ######## 100.0 0 1,229 1.2 8,457,683 115 ######## 100.0 0 1,434 1.4 8,457,683 115 ######## 100.0 0 1,638 1.6 8,457,683 115 ######## 100.0 0 1,843 1.8 8,457,683 115 ######## 100.0 0 2,048 2.0 8,457,683 115 ######## 100.0 0 3,072 3.0 8,457,683 115 ######## 100.0 0 4,096 4.0 8,457,683 115 ######## 100.0 0 6,144 6.0 8,457,683 115 ######## 100.0 0 8,192 8.0 8,457,683 115 ######## 100.0 0 ------------------------------------------------------------- Process Memory Summary Stats DB/Inst: DELPNRA0/delpnra0 Snaps: 6121-6131 -> B: Begin snap E: End snap -> All rows below contain absolute values (i.e. not diffed over the interval) -> Max Alloc is Maximum PGA Allocation size at snapshot time Hist Max Alloc is the Historical Max Allocation for still-connected processes -> Num Procs or Allocs: For Begin/End snapshot lines, it is the number of processes. For Category lines, it is the number of allocations -> ordered by Begin/End snapshot, Alloc (MB) desc Hist Num Avg Std Dev Max Max Procs Alloc Used Freeabl Alloc Alloc Alloc Alloc or Category (MB) (MB) (MB) (MB) (MB) (MB) (MB) Allocs - -------- --------- --------- -------- -------- ------- ------- ------ ------ B -------- 585.2 405.7 124.0 8.1 13.9 60 60 72 Other 376.8 5.2 11.0 60 60 72 Freeable 124.8 .0 3.9 8.4 46 32 PL/SQL 60.3 29.0 1.0 4.7 28 29 55 JAVA 13.8 13.8 4.6 5.6 11 11 3 SQL 9.4 2.7 .3 .7 3 11 21 E -------- 681.2 441.9 137.4 8.7 14.5 60 85 78 Other 444.4 5.7 11.5 60 60 78 Freeable 132.0 .0 3.9 8.0 44 34 PL/SQL 69.3 61.3 1.0 4.6 28 60 61 JAVA 18.4 18.3 6.1 4.2 11 11 3 SQL 17.1 11.8 .5 1.2 7 11 25 ------------------------------------------------------------- Top Process Memory (by component) DB/Inst: DELPNRA0/delpnra0 Snaps: 6121-6131 -> ordered by Begin/End snapshot, Alloc (MB) desc Alloc Used Freeabl Max Hist Max PId Category (MB) (MB) (MB) Alloc (MB) Alloc (MB) - ------ ------------- ------- ------- -------- ---------- ---------- B 24 MARK -------- 59.5 59.3 .0 59.5 59.5 Other 59.5 59.5 59.5 PL/SQL .0 .0 .0 .0 62 J006 -------- 50.3 3.5 46.0 50.3 50.3 Freeable 46.0 .0 46.0 Other 3.3 3.3 3.3 SQL .9 .0 .9 6.4 PL/SQL .1 .1 .1 .1 66 J010 -------- 48.5 40.8 6.9 48.5 48.5 PL/SQL 27.5 .0 27.5 29.0 Other 13.7 13.7 13.7 Freeable 6.9 .0 6.9 SQL .3 .0 .3 8.3 35 ARC0 -------- 44.2 40.5 .0 44.2 44.2 Other 44.2 44.2 44.2 PL/SQL .0 .0 .0 .0 37 ARC2 -------- 44.2 40.4 1.1 44.2 44.2 Other 43.2 43.2 43.2 Freeable 1.1 .0 1.1 PL/SQL .0 .0 .0 .0 39 ARC3 -------- 44.2 40.4 1.1 44.2 44.2 Other 43.2 43.2 43.2 Freeable 1.1 .0 1.1 PL/SQL .0 .0 .0 .0 69 J013 -------- 32.3 28.9 2.3 32.3 32.3 PL/SQL 25.9 23.1 25.9 26.0 Other 3.9 3.9 3.9 Freeable 2.3 .0 2.3 SQL .2 .0 .2 5.1 63 J007 -------- 26.8 13.8 12.3 26.8 26.8 Freeable 12.3 .0 12.3 JAVA 10.9 10.9 10.9 10.9 Other 3.2 3.2 3.2 PL/SQL .3 .3 .3 3.4 SQL .2 .0 .2 11.1 11 DBW0 -------- 25.7 10.6 11.3 25.7 27.6 Other 14.5 14.5 16.3 Freeable 11.3 .0 11.3 PL/SQL .0 .0 .0 .0 36 ARC1 -------- 19.4 17.7 .0 19.4 19.4 Other 19.4 19.4 19.4 PL/SQL .0 .0 .0 .0 68 J012 -------- 18.7 9.6 8.2 18.7 18.7 Freeable 8.2 .0 8.2 Other 4.9 4.9 4.9 PL/SQL 3.4 2.9 3.4 4.5 SQL 2.2 2.0 2.2 6.9 49 J002 -------- 12.7 6.1 4.2 12.7 12.7 Freeable 5.0 .0 5.0 Other 4.9 4.9 4.9 PL/SQL 2.0 1.8 2.0 2.2 SQL .8 .6 .8 5.4 72 J004 -------- 11.9 2.3 8.9 11.9 12.2 Top Process Memory (by component) DB/Inst: DELPNRA0/delpnra0 Snaps: 6121-6131 -> ordered by Begin/End snapshot, Alloc (MB) desc Alloc Used Freeabl Max Hist Max PId Category (MB) (MB) (MB) Alloc (MB) Alloc (MB) - ------ ------------- ------- ------- -------- ---------- ---------- B 72 Freeable 8.9 .0 8.9 Other 2.8 2.8 2.8 SQL .2 .0 .2 8.1 PL/SQL .1 .1 .1 .1 47 J001 -------- 10.5 6.2 3.9 10.5 12.6 Freeable 3.9 .0 3.9 SQL 3.5 .0 3.5 5.4 Other 2.8 2.8 2.8 PL/SQL .4 .3 .4 5.5 10 DIA0 -------- 10.1 9.6 .0 10.1 10.3 Other 10.1 10.1 10.3 PL/SQL .0 .0 .0 .0 56 CJQ0 -------- 9.4 3.2 5.8 9.4 10.9 Freeable 5.8 .0 5.8 Other 3.4 3.4 3.4 SQL .1 .0 .1 2.0 PL/SQL .0 .0 .0 .0 27 J000 -------- 7.0 1.6 1.0 7.0 16.3 Other 6.0 6.0 6.0 Freeable 1.0 .0 1.0 PL/SQL .0 .0 .0 5.4 JAVA .0 .0 .0 4.6 SQL .0 .0 .0 3.0 31 P001 -------- 6.9 2.8 .1 6.9 7.2 Other 6.8 6.8 7.1 Freeable .1 .0 .1 PL/SQL .0 .0 .0 .0 SQL .0 .0 .0 .0 32 P002 -------- 6.7 2.6 .0 6.7 7.0 Other 6.7 6.7 6.9 PL/SQL .0 .0 .0 .0 SQL .0 .0 .0 .1 61 J005 -------- 6.5 3.4 2.1 6.5 6.5 Other 3.9 3.9 3.9 Freeable 2.1 .0 2.1 SQL .2 .1 .2 2.8 PL/SQL .2 .2 .2 .2 E 24 MARK -------- 59.5 59.3 .0 59.5 59.5 Other 59.5 59.5 59.5 PL/SQL .0 .0 .0 .0 60 J002 -------- 51.4 6.8 44.1 51.4 85.0 Freeable 44.1 .0 44.1 PL/SQL 3.5 3.3 3.5 60.3 Other 3.2 3.2 3.2 SQL .7 .3 .7 7.1 63 J006 -------- 50.3 3.5 4.9 50.3 50.3 Other 44.4 44.4 44.4 Freeable 4.9 .0 4.9 SQL .9 .1 .9 6.4 PL/SQL .1 .1 .1 .1 62 J005 -------- 47.2 37.5 6.9 47.2 50.3 PL/SQL 27.8 22.9 27.8 29.0 Other 12.0 12.0 12.0 Top Process Memory (by component) DB/Inst: DELPNRA0/delpnra0 Snaps: 6121-6131 -> ordered by Begin/End snapshot, Alloc (MB) desc Alloc Used Freeabl Max Hist Max PId Category (MB) (MB) (MB) Alloc (MB) Alloc (MB) - ------ ------------- ------- ------- -------- ---------- ---------- E 62 Freeable 7.0 .0 7.0 SQL .3 .0 .3 7.5 35 ARC0 -------- 44.2 40.4 1.1 44.2 44.2 Other 43.2 43.2 43.2 Freeable 1.1 .0 1.1 PL/SQL .0 .0 .0 .0 37 ARC2 -------- 44.2 40.4 1.1 44.2 44.2 Other 43.2 43.2 43.2 Freeable 1.1 .0 1.1 PL/SQL .0 .0 .0 .0 39 ARC3 -------- 44.2 40.5 .0 44.2 44.2 Other 44.2 44.2 44.2 PL/SQL .0 .0 .0 .0 76 M000 -------- 39.2 16.3 22.1 39.2 39.2 Freeable 16.1 .0 16.1 Other 15.1 15.1 15.1 SQL 6.8 6.3 6.8 8.1 PL/SQL 1.2 1.2 1.2 1.3 47 J001 -------- 31.8 28.8 2.1 31.8 31.8 PL/SQL 25.9 24.3 25.9 26.0 Other 3.7 3.7 3.7 Freeable 2.1 .0 2.1 SQL .2 .0 .2 5.2 11 DBW0 -------- 25.7 10.6 11.3 25.7 27.6 Other 14.5 14.5 16.3 Freeable 11.3 .0 11.3 PL/SQL .0 .0 .0 .0 36 ARC1 -------- 19.4 17.7 .0 19.4 19.4 Other 19.4 19.4 19.4 PL/SQL .0 .0 .0 .0 26 J000 -------- 19.3 13.9 4.4 19.3 50.7 JAVA 10.9 10.9 10.9 10.9 Freeable 4.4 .0 4.4 Other 3.6 3.6 3.6 PL/SQL .3 .3 .3 29.0 SQL .2 .0 .2 11.2 67 J010 -------- 18.6 10.1 7.3 18.6 18.6 Freeable 7.3 .0 7.3 Other 5.2 5.2 5.2 PL/SQL 3.4 2.9 3.4 4.5 SQL 2.8 2.6 2.8 6.9 70 J013 -------- 13.2 9.0 1.9 13.2 13.2 Other 5.5 5.5 5.5 PL/SQL 4.7 4.5 4.7 5.4 Freeable 1.9 .0 1.9 SQL 1.1 .1 1.1 3.0 72 J004 -------- 12.2 2.3 9.1 12.2 12.2 Freeable 9.1 .0 9.1 Other 2.8 2.8 2.8 SQL .2 .0 .2 8.1 PL/SQL .1 .1 .1 .1 69 J011 -------- 11.2 6.5 3.0 11.2 11.2 Other 6.3 6.3 6.3 Top Process Memory (by component) DB/Inst: DELPNRA0/delpnra0 Snaps: 6121-6131 -> ordered by Begin/End snapshot, Alloc (MB) desc Alloc Used Freeabl Max Hist Max PId Category (MB) (MB) (MB) Alloc (MB) Alloc (MB) - ------ ------------- ------- ------- -------- ---------- ---------- E 69 Freeable 3.5 .0 3.5 PL/SQL 1.1 .9 1.1 1.9 SQL .3 .0 .3 4.5 10 DIA0 -------- 10.1 9.6 .0 10.1 10.3 Other 10.1 10.1 10.3 PL/SQL .0 .0 .0 .0 56 CJQ0 -------- 9.4 3.2 5.8 9.4 10.9 Freeable 5.8 .0 5.8 Other 3.4 3.4 3.4 SQL .1 .0 .1 2.0 PL/SQL .0 .0 .0 .0 66 J009 -------- 8.4 7.0 .8 8.4 8.4 JAVA 4.6 4.5 4.6 4.6 Other 2.7 2.7 2.7 Freeable .8 .0 .8 PL/SQL .3 .3 .3 .3 SQL .2 .0 .2 1.0 31 P001 -------- 6.9 2.8 .1 6.9 7.2 Other 6.8 6.8 7.1 Freeable .1 .0 .1 SQL .0 .0 .0 .0 PL/SQL .0 .0 .0 .0 ------------------------------------------------------------- Enqueue activity DB/Inst: DELPNRA0/delpnra0 Snaps: 6121-6131 -> only enqueues with waits are shown -> Enqueue stats gathered prior to 10g should not be compared with 10g data -> ordered by Wait Time desc, Waits desc Enqueue Type (Request Reason) ------------------------------------------------------------------------------ Requests Succ Gets Failed Gets Waits Wt Time (s) Av Wt Time(ms) ------------ ------------ ----------- ----------- ------------ -------------- PR-Process Startup 207 207 0 23 45 1,964.57 SQ-Sequence Cache 83 83 0 7 0 2.14 CU-Cursor 13,327 13,327 0 5 0 .00 ------------------------------------------------------------- Undo Segment Summary DB/Inst: DELPNRA0/delpnra0 Snaps: 6121-6131 -> Min/Max TR (mins) - Min and Max Tuned Retention (minutes) -> STO - Snapshot Too Old count, OOS - Out Of Space count -> Undo segment block stats: uS - unexpired Stolen, uR - unexpired Released, uU - unexpired reUsed eS - expired Stolen, eR - expired Released, eU - expired reUsed Undo Num Undo Number of Max Qry Max Tx Min/Max STO/ uS/uR/uU/ TS# Blocks (K) Transactions Len (s) Concy TR (mins) OOS eS/eR/eU ---- ---------- --------------- -------- ---------- --------- ----- ----------- 2 12.2 1,507 974 11 29,3/29,3 0/0 0/0/0/0/0/0 ------------------------------------------------------------- Undo Segment Stats DB/Inst: DELPNRA0/delpnra0 Snaps: 6121-6131 -> Most recent 35 Undostat rows, ordered by End Time desc Num Undo Number of Max Qry Max Tx Tun Ret STO/ uS/uR/uU/ End Time Blocks Transactions Len (s) Concy (mins) OOS eS/eR/eU ------------ ----------- ------------ ------- ------- ------- ----- ----------- 22-Mei 03:42 12,169 1,507 974 11 29 0/0 0/0/0/0/0/0 ------------------------------------------------------------- Latch Activity DB/Inst: DELPNRA0/delpnra0 Snaps: 6121-6131 ->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for willing-to-wait latch get requests ->"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests ->"Pct Misses" for both should be very close to 0.0 Pct Avg Wait Pct Get Get Slps Time NoWait NoWait Latch Requests Miss /Miss (s) Requests Miss ------------------------ -------------- ------ ------ ------ ------------ ------ active checkpoint queue 14,480 0.0 0 0 active service list 7,679 0.1 0.0 0 2,128 0.0 alert log latch 10 0.0 0 0 AQ Background: interrupt 1 0.0 0 40 0.0 AQ Coord jobx_kwsbgsgn l 38 0.0 0 0 AQ deq hash table latch 1 0.0 0 0 AQ Sharded master pool l 38 0.0 0 0 archive destination 208 0.0 0 0 archive process latch 101 0.0 0 0 ASM allocation 41 0.0 0 0 ASM db client latch 762 0.0 0 0 ASM Keyed state latch 408 0.0 0 0 ASM map headers 30 0.0 0 0 ASM map load waiting lis 5 0.0 0 0 ASM map operation freeli 1,744 0.1 0.0 0 0 ASM map operation hash t 583,617 0.0 0.0 0 0 ASM network background l 562 0.0 0 0 ASM network state latch 188 0.0 0 0 ASM remote client latch 18 0.0 0 0 begin backup scn array 23 0.0 0 0 buffer pool 1 0.0 0 0 business card 1 0.0 0 0 cache buffers chains 102,596,897 0.0 0.0 0 4,324,848 0.0 cache buffers lru chain 499,311 0.2 0.1 0 2,370,171 0.1 cache table scan latch 0 0 169,973 0.0 call allocation 25,538 0.0 0.5 0 0 cas latch 1 0.0 0 0 change notification clie 1 0.0 0 0 Change Notification Hash 646 0.0 0 0 channel handle pool latc 418 0.5 0.0 0 0 channel operations paren 1,551 0.1 0.0 0 11 0.0 checkpoint queue latch 497,116 0.0 0.1 0 86,789 0.0 client/application info 1,335 0.1 0.0 0 0 compile environment latc 427 0.0 0 0 Consistent RBA 1,795 0.0 0 0 corrupted undo seg latch 3,962,737 0.0 0.0 0 0 cp handoff latch 1 0.0 0 0 cp pool latch 1 0.0 0 0 cp server hash latch 1 0.0 0 0 cp sga latch 18 0.0 0 0 cp srv type state latch 55 0.0 0 0 cp srv type wait latch 1 0.0 0 0 cvmap freelist lock 1 0.0 0 0 deferred cleanup latch 18 0.0 0 0 Dependency Info 75 0.0 0 0 dispatcher info 164 0.0 0 0 DML lock allocation 2,661,749 0.0 0.0 0 0 dml lock allocation 18 0.0 0 0 done queue latch 1 0.0 0 0 dtp latch 201 0.0 0 0 Latch Activity DB/Inst: DELPNRA0/delpnra0 Snaps: 6121-6131 ->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for willing-to-wait latch get requests ->"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests ->"Pct Misses" for both should be very close to 0.0 Pct Avg Wait Pct Get Get Slps Time NoWait NoWait Latch Requests Miss /Miss (s) Requests Miss ------------------------ -------------- ------ ------ ------ ------------ ------ dummy allocation 765 0.0 0 0 eighth spare latch - X p 1 0.0 0 0 eleventh spare latch - c 1 0.0 0 0 enqueue freelist latch 1 0.0 0 60,127 0.0 enqueue hash chains 2,729,370 0.0 0.1 0 18 0.0 enqueues 3 0.0 0 0 Event Group Locks 593 0.0 0 0 event stats latch 379 0.0 0 0 FAL Queue 41 0.0 0 0 fast space usage latch 19 0.0 0 0 FIB s.o chain latch 10 0.0 0 0 fifteenth spare latch - 1 0.0 0 0 file cache latch 225 0.0 0 0 File State Object Pool P 1 0.0 0 0 flash file access latch 1 0.0 0 0 flashback copy 1 0.0 0 0 FOB s.o list latch 1,502 0.7 0.0 0 0 fourteenth spare latch - 1 0.0 0 0 fourth Audit Vault latch 1 0.0 0 0 gc element 1 0.0 0 0 gcs commit scn state 1 0.0 0 0 gcs opaque info freelist 1 0.0 0 0 gcs partitioned table ha 1 0.0 0 0 gcs pcm hashed value buc 1 0.0 0 0 gcs resource freelist 1 0.0 0 0 gcs resource hash 1 0.0 0 0 gcs resource scan list 1 0.0 0 0 gcs resource validate li 1 0.0 0 0 gcs shadows freelist 1 0.0 0 0 ges cached resource list 1 0.0 0 0 ges domain table 1 0.0 0 0 ges enqueue table freeli 1 0.0 0 0 ges group table 1 0.0 0 0 ges process hash list 1 0.0 0 0 ges process parent latch 1 0.0 0 0 ges resource hash list 1 0.0 0 0 ges resource scan list 1 0.0 0 0 ges resource table freel 1 0.0 0 0 ges timeout list 1 0.0 0 0 ges value block free lis 1 0.0 0 0 global tx hash mapping 1 0.0 0 0 granule from data transf 1 0.0 0 0 granule operation 1 0.0 0 0 hash table column usage 710 0.0 0 1,739,319 0.0 hash table modification 20 0.0 0 0 hash table Sql Plan Find 124 0.0 0 4,020 0.0 heartbeat check 1 0.0 0 0 ILM access tracking exte 1 0.0 0 0 ILM activity tracking la 1 0.0 0 0 ILM Stats main anchor la 544 0.0 0 0 Latch Activity DB/Inst: DELPNRA0/delpnra0 Snaps: 6121-6131 ->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for willing-to-wait latch get requests ->"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests ->"Pct Misses" for both should be very close to 0.0 Pct Avg Wait Pct Get Get Slps Time NoWait NoWait Latch Requests Miss /Miss (s) Requests Miss ------------------------ -------------- ------ ------ ------ ------------ ------ ILM Stats Stripe Latch 1 0.0 0 0 IM area sb latch 1 0.0 0 0 IM area scb latch 1 0.0 0 0 IM emb latch 1 0.0 0 0 IM seg hdr latch 1 0.0 0 0 imc buf hdl lat 1 0.0 0 0 imc hash lat 1 0.0 0 0 imc sj lat 1 0.0 0 0 imc srchsp lat 1 0.0 0 0 imc-txn-wrk-lat 1 0.0 0 0 In memory undo latch 2,663,798 0.0 0 15,009 0.0 in-memory area latch 1 0.0 0 0 in-memory columnar segme 1 0.0 0 0 in-memory columnar ts ex 1 0.0 0 0 in-memory global pool la 1 0.0 0 0 internal temp table obje 138 0.0 0 0 intra txn parallel recov 1 0.0 0 0 io pool granule list 38 0.0 0 0 io pool granule metadata 77 0.0 0 0 I/O Staticstics latch 1 0.0 0 0 IPC stats buffer allocat 1 0.0 0 0 job workq parent latch 182 0.0 0 181 1.1 job_queue_processes free 729 0.1 0.0 0 0 job_queue_processes para 2 0.0 0 0 JOX JIT latch 4 0.0 0 4 0.0 JOX SGA heap latch 780 0.0 0 0 JS mem alloc latch 442 0.0 0 0 JS queue access latch 443 0.0 0 0 JS queue state obj latch 11,354 0.0 0 0 JS Sh mem access 13 0.0 0 0 JS slv state obj latch 328 0.3 0.0 0 0 kcb DW scan objtemp hash 1 0.0 0 0 kcbtsemkid latch 5 0.0 0 0 kcn buffer chains 1 0.0 0 0 KCNIBR - invalid block r 1 0.0 0 0 kdlx hb parent latch 1 0.0 0 0 KFC FX Hash Latch 1 0.0 0 0 KFC Hash Latch 1 0.0 0 0 KFCL LE Freelist 1 0.0 0 0 KFK SGA Libload latch 72 0.0 0 0 KFMD SGA 11 0.0 0 0 kgb parent 1 0.0 0 0 kgnfs mount latch 1 0.0 0 0 KGNFS-NFS:SHM structure 1 0.0 0 0 KGNFS-NFS:SVR LIST 1 0.0 0 0 KJC message pool free li 1 0.0 0 0 KJC message pool pending 1 0.0 0 0 kjci objects freelist la 1 0.0 0 0 kjci process context lat 1 0.0 0 15,588 0.0 KJCT flow control latch 1 0.0 0 0 Latch Activity DB/Inst: DELPNRA0/delpnra0 Snaps: 6121-6131 ->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for willing-to-wait latch get requests ->"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests ->"Pct Misses" for both should be very close to 0.0 Pct Avg Wait Pct Get Get Slps Time NoWait NoWait Latch Requests Miss /Miss (s) Requests Miss ------------------------ -------------- ------ ------ ------ ------------ ------ kjoedcso state object fr 18 0.0 0 0 kjoeq omni enqueue hash 1 0.0 0 0 kjoer owner hash bucket 1 0.0 0 0 KMG MMAN ready and start 370 0.0 0 0 kokc descriptor allocati 2,156,913 0.0 0.2 1 0 krso process latch 37 0.0 0 0 KSFS id table parent lat 1 0.0 0 0 KSFS ksfs_node latch 1 0.0 0 0 KSFS OFS ctx level paren 1 0.0 0 0 KSFS OFS req layer paren 1 0.0 0 0 KSFS OFS sess layer pare 1 0.0 0 0 KSFSD name cache parent 1 0.0 0 0 ksfv messages 1 0.0 0 0 ksi resource reuse count 1 0.0 0 0 ksim group membership ca 1 0.0 0 0 kss move lock 107 0.0 0 0 ksuosstats global area 489 2.0 1.0 18 0 ksv allocation latch 228 0.0 0 0 ksv class latch 1,004 0.0 0 0 ksv instance latch 8 0.0 0 0 ksv msg queue latch 26 0.0 0 25 0.0 KSXR hang analysis phase 1 0.0 0 0 KSXR hang analysis phase 1 0.0 0 0 ksz_so allocation latch 408 0.0 0 0 KTF sga latch 13,007 0.0 0 345 0.0 ktfbn latch 257 0.0 0 0 KTILM latch 1 0.0 0 0 ktm global data 86 0.0 0 0 ktm prv jrnls 1 0.0 0 0 ktmpj HT ls 1 0.0 0 0 kwqbsn:qsga 42 0.0 0 0 KWQP Prop Status 2 0.0 0 0 kwslbmdl: metadata latch 38 0.0 0 0 kwslbql: queue latch 1 0.0 0 0 kwsptjobAdPtLt: AdPt lis 81 0.0 0 0 kwsptQcachLt: queue cach 1 0.0 0 0 kwsptTrncTsksLt: trunc t 1 0.0 0 0 kxfxscanrate_latch 1 0.0 0 0 k2q lock allocation 1 0.0 0 0 lgwr LWN SCN 1,796 0.2 0.0 0 0 list of block allocation 19,577 0.0 0.0 0 0 loader state object free 636 0.0 0 0 lob segment dispenser la 39 0.0 0 0 lob segment hash table l 90 0.0 0 0 lob segment query latch 1 0.0 0 0 Locator state objects po 1 0.0 0 0 lock DBA buffer during m 1 0.0 0 0 log write info 0 0 1,794 0.0 log write slave phase 1 0.0 0 0 logical standby cache 1 0.0 0 0 Latch Activity DB/Inst: DELPNRA0/delpnra0 Snaps: 6121-6131 ->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for willing-to-wait latch get requests ->"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests ->"Pct Misses" for both should be very close to 0.0 Pct Avg Wait Pct Get Get Slps Time NoWait NoWait Latch Requests Miss /Miss (s) Requests Miss ------------------------ -------------- ------ ------ ------ ------------ ------ logminer context allocat 1 0.0 0 0 logminer local 1 0.0 0 0 logminer work area 1 0.0 0 0 longop free list parent 18 0.0 0 17 0.0 Lsod array latch 335 0.0 0 0 managed standby latch 37 0.0 0 0 mapped buffers lru chain 1 0.0 0 0 Memory Management Latch 1 0.0 0 370 0.0 Memory Queue 1 0.0 0 0 Memory Queue Message Sub 1 0.0 0 0 Memory Queue Message Sub 1 0.0 0 0 Memory Queue Message Sub 1 0.0 0 0 Memory Queue Message Sub 1 0.0 0 0 Memory Queue Subscriber 1 0.0 0 0 message bitmap latch 1 0.0 0 0 Message cache latch 1 0.0 0 0 message pool operations 247 0.0 0 0 messages 47,314 0.0 0.0 0 0 MQL Tracking Latch 0 0 22 0.0 msg queue latch 1 0.0 0 0 multiblock read objects 379,422 0.0 0.0 0 0 Mutex 1 0.0 0 0 Mutex Stats 1 0.0 0 0 name-service namespace b 1 0.0 0 0 ncodef allocation latch 18 0.0 0 0 ninth spare latch - X pa 1 0.0 0 0 object queue header free 11,838 0.0 0 0 object queue header oper 4,819,491 0.0 0.0 0 0 object queue memory 1 0.0 0 0 object stats modificatio 4 0.0 0 0 OS process 1,846 0.0 0 0 OS process allocation 3,310 0.0 0 0 OS process: request allo 408 0.2 0.0 0 0 parallel query alloc buf 1 0.0 0 144 0.0 parallel query stats 1 0.0 0 0 parameter table manageme 10,835,012 0.1 0.0 1 0 Parent latch for depende 61 0.0 0 0 peshm 11 0.0 0 0 pesom_free_list 4,256 0.0 0 0 pesom_hash_node 6,757 0.0 0 0 pesom_heap_alloc 58 0.0 0 0 pkey global statistics 1 0.0 0 0 PL/SQL warning settings 1,163 0.0 0 0 post/wait queue 2,571 0.1 0.0 0 2,349 0.2 process allocation 615 0.0 0 207 0.0 process group creation 408 0.0 0 0 process pkey statistics 1 0.0 0 0 process queue 1 0.0 0 0 process queue reference 1 0.0 0 0 PX hash array latch 1 0.0 0 0 Latch Activity DB/Inst: DELPNRA0/delpnra0 Snaps: 6121-6131 ->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for willing-to-wait latch get requests ->"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests ->"Pct Misses" for both should be very close to 0.0 Pct Avg Wait Pct Get Get Slps Time NoWait NoWait Latch Requests Miss /Miss (s) Requests Miss ------------------------ -------------- ------ ------ ------ ------------ ------ qmn task queue latch 163 0.0 0 0 QMT 1 0.0 0 0 query server freelists 1 0.0 0 0 query server process 719 0.0 0 0 queued dump request 4 0.0 0 0 queuing load statistics 1 0.0 0 0 Real-time descriptor lat 20,928 0.0 0 0 recovery domain hash lis 1 0.0 0 0 redo allocation 41,990 0.2 0.0 0 4,040,293 0.0 redo copy 1 0.0 0 4,040,362 0.0 redo transport task latc 43 0.0 0 0 redo writing 20,915 0.0 0 0 Report Request stats lat 82 0.0 0 0 Report Request struct la 83 0.0 0 0 Request holder compeltio 415 0.0 0 0 reservation so alloc lat 10 0.0 0 0 Reserved Space Latch 20 0.0 0 0 resmgr group change latc 184 0.0 0 0 resmgr:active threads 768 0.0 0 0 resmgr:actses change gro 235 0.0 0 0 resmgr:actses change sta 1 0.0 0 0 resmgr:free threads list 764 0.4 0.0 0 0 resmgr:plan CPU method 1 0.0 0 0 resmgr:resource group CP 1 0.0 0 0 resmgr:schema config 12 0.0 0 0 resmgr:session queuing 1 0.0 0 0 Result Cache: RC Latch 30 0.0 0 0 Result Cache: SO Latch 30 0.0 0 0 Retry bkt latch 1 0.0 0 0 Retry Ht elm latch 1 0.0 0 0 rm cas latch 1 0.0 0 0 row cache objects 20,688,238 0.1 0.0 9 40,254 0.3 RTT parent context 5 0.0 0 0 rules engine rule set st 100 0.0 0 0 Sched IM Job latch 19 0.0 0 0 Sched InMem Job Cache 172 0.0 0 0 second Audit Vault latch 1 0.0 0 0 segmented array pool 10 0.0 0 0 sequence cache 3,534 0.0 0 0 session allocation 1,325,748 0.0 0 1,325,097 0.0 session idle bit 1,327,057 0.0 0 0 session queue latch 1 0.0 0 0 session state list latch 1,433 0.0 0 0 session statistics 765 0.0 0 0 session switching 220 0.0 0 0 session timer 371 0.0 0 0 seventh spare latch - X 1 0.0 0 0 SGA blob parent 1 0.0 0 0 SGA bucket locks 1 0.0 0 0 sga hash table parent la 1 0.0 0 0 Latch Activity DB/Inst: DELPNRA0/delpnra0 Snaps: 6121-6131 ->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for willing-to-wait latch get requests ->"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests ->"Pct Misses" for both should be very close to 0.0 Pct Avg Wait Pct Get Get Slps Time NoWait NoWait Latch Requests Miss /Miss (s) Requests Miss ------------------------ -------------- ------ ------ ------ ------------ ------ SGA heap locks 1 0.0 0 0 SGA IO buffer pool latch 327 0.0 0 376 0.0 SGA Logging Bkt Latch 1 0.0 0 0 SGA Logging Log Latch 2,985 0.0 0 0 SGA pool locks 1 0.0 0 0 shard latch 1 0.0 0 0 Shared B-Tree 44 0.0 0 0 shared pool 5,963,301 0.3 0.2 9 0 shared pool sim alloc 1 0.0 0 0 shared pool simulator 69,067 0.0 0.0 0 0 sim partition latch 1 0.0 0 0 simulator hash latch 2,211,481 0.0 0.0 0 0 simulator lru latch 100,841 0.1 0.0 0 1,813,544 0.1 sixth spare latch - X pa 1 0.0 0 0 sort extent pool 2,662 0.0 0 0 space background state o 5 0.0 0 0 space background task la 399 1.8 1.0 0 754 0.0 SQL memory manager latch 1 0.0 0 329 0.0 SQL memory manager worka 436,110 0.0 0.7 0 0 SR Stats main anchor lat 2,123,827 0.0 0.0 0 0 SR Stats Stripe Latch 1 0.0 0 0 Streams Generic 1 0.0 0 0 subscriber Ht bkt 1 0.0 0 0 Subscriber Ht elm latch 1 0.0 0 0 tablespace key chain 1 0.0 0 0 temp lob duration state 369 0.0 0 0 temporary table state ob 20 0.0 0 0 tenth spare latch - X pa 1 0.0 0 0 test excl. parent l0 1 0.0 0 0 test excl. parent2 lmid 1 0.0 0 0 test excl. parent2 l0 1 0.0 0 0 test shared parent2 lmid 1 0.0 0 0 Testing 1 0.0 0 0 thirteenth spare latch - 1 0.0 0 0 threshold alerts latch 41 0.0 0 0 Token Manager 1 0.0 0 0 transaction allocation 1,546 0.0 0 0 twelfth spare latch - ch 1 0.0 0 0 twenty-fifth spare latch 1 0.0 0 0 twenty-first spare latch 1 0.0 0 0 twenty-fourth spare latc 1 0.0 0 0 twenty-second spare latc 1 0.0 0 0 twenty-third spare latch 1 0.0 0 0 Txn bkt latch 1 0.0 0 0 Txn Ht elm latch 1 0.0 0 0 undo global data 34,938 0.0 0.0 0 0 virtual circuit buffers 1 0.0 0 0 virtual circuit holder 1 0.0 0 0 virtual circuit queues 1 0.0 0 0 WCR: sync 1 0.0 0 0 Latch Activity DB/Inst: DELPNRA0/delpnra0 Snaps: 6121-6131 ->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for willing-to-wait latch get requests ->"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests ->"Pct Misses" for both should be very close to 0.0 Pct Avg Wait Pct Get Get Slps Time NoWait NoWait Latch Requests Miss /Miss (s) Requests Miss ------------------------ -------------- ------ ------ ------ ------------ ------ Write State Object Pool 1 0.0 0 0 XDB NFS Security Latch 1 0.0 0 0 XDB unused session pool 1 0.0 0 0 XDB used session pool 1 0.0 0 0 XS state object latch 30 0.0 0 0 ------------------------------------------------------------- Latch Sleep breakdown DB/Inst: DELPNRA0/delpnra0 Snaps: 6121-6131 -> ordered by misses desc Get Spin Latch Name Requests Misses Sleeps Gets -------------------------- --------------- ------------ ----------- ----------- shared pool 5,963,301 19,473 2,932 16,885 row cache objects 20,688,238 16,461 241 16,234 parameter table management 10,835,012 13,653 300 13,356 cache buffers lru chain 499,311 1,126 162 976 cache buffers chains 102,596,897 941 7 935 simulator lru latch 100,841 94 2 92 redo allocation 41,990 83 4 79 enqueue hash chains 2,729,370 76 5 71 kokc descriptor allocation 2,156,913 44 8 37 checkpoint queue latch 497,116 15 1 14 call allocation 25,538 12 6 7 ksuosstats global area 489 10 10 0 space background task latc 399 7 7 0 SQL memory manager workare 436,110 6 4 3 ------------------------------------------------------------- Latch Miss Sources DB/Inst: DELPNRA0/delpnra0 Snaps: 6121-6131 -> only latches with sleeps are shown -> ordered by name, sleeps desc NoWait Waiter Latch Name Where Misses Sleeps Sleeps ------------------------ -------------------------- ------- ---------- -------- cache buffers chains kcbzibmlt: multi-block rea 0 5 0 cache buffers chains kcbgcur: fast path excl 0 2 0 cache buffers chains kcbgtcr: slow path excl 0 2 0 cache buffers chains kcbchg: cr pin cleanout 0 1 0 cache buffers chains kcbbic1 0 1 0 cache buffers lru chain kcbzgws 0 161 0 cache buffers lru chain kcbzgm 0 1 0 call allocation ksuxds 0 3 3 call allocation ksudlc : rec call 0 2 3 call allocation ksudlp: top call 0 1 0 channel operations paren ksrwait() 0 1 0 checkpoint queue latch kcbbwthc: thread checkpoin 0 1 0 enqueue hash chains ksqrcl 0 5 4 kokc descriptor allocati kokcdlt: regular free 0 6 0 kokc descriptor allocati kokcdlt: allocation 0 2 8 ksuosstats global area ksugetosstat 0 10 10 parameter table manageme kspptval 0 305 321 parameter table manageme ksp_param_table_free 0 12 0 parameter table manageme kspcpy 0 5 1 PC and Classifier lists No latch 0 3 0 redo allocation kcrfw_redo_gen: redo alloc 0 3 0 redo allocation kcrfw_redo_write: before w 0 1 1 row cache objects kqreqd: reget 0 122 0 row cache objects kqrpre: find obj 0 30 161 row cache objects kqrso 0 26 14 row cache objects kqreqd 0 8 12 row cache objects kqrpre: init complete 0 1 7 shared pool kghalo 0 2,361 357 shared pool kghupr1 0 412 2,317 shared pool kghalp 0 86 194 shared pool kghfrunp: clatch: nowait 0 45 0 shared pool kgh_next_free 0 34 0 shared pool kghfrunp: alloc: wait 0 24 4 shared pool kgh_heap_sizes 0 13 11 shared pool kghasp 0 12 56 shared pool kghfrunp: clatch: wait 0 12 45 shared pool kghfre 0 2 5 simulator lru latch kcbs_simulate: simulate se 0 2 0 space background task la ktsj_grab_task 0 4 7 SQL memory manager worka qesmmIRegisterWorkArea:2 0 4 4 ------------------------------------------------------------- Mutex Sleep DB/Inst: DELPNRA0/delpnra0 Snaps: 6121-6131 -> ordered by Wait Time desc Wait Mutex Type Location Sleeps Time (s) ------------------ -------------------------------- -------------- ------------ Library Cache kglhdgn2 106 8,304 301.9 Library Cache kgllkdl1 85 7,027 224.5 Cursor Pin kkslce [KKSCHLPIN2] 1,278 81.1 Library Cache kglhdgn1 62 1,457 56.3 Cursor Pin kksfbc [KKSCHLFSP2] 81 6.5 Library Cache kglllal3 111 8 3.8 Cursor Pin kksLockDelete [KKSCHLPIN6] 9 0.8 Library Cache kglpnal1 90 2 0.4 Library Cache kglpin1 4 6 0.3 Library Cache kglget2 2 10 0.2 Library Cache kgllkc1 57 4 0.1 Library Cache kglini1 32 6 0.0 Library Cache kglget1 1 2 0.0 Cursor Pin kksSetBindType [KKSCHLPIN4] 2 0.0 Library Cache kgllldl2 112 1 0.0 Library Cache kglGetHandleReference 123 2 0.0 Library Cache kglobld1 75 1 0.0 Library Cache kglUpgradeLock 119 1 0.0 Library Cache kgllkal3 82 1 0.0 ------------------------------------------------------------- Dictionary Cache Stats DB/Inst: DELPNRA0/delpnra0 Snaps: 6121-6131 ->"Pct Misses" should be very low (< 2% in most cases) ->"Final Usage" is the number of cache entries being used in End Snapshot Get Pct Scan Pct Mod Final Cache Requests Miss Reqs Miss Reqs Usage ------------------------- ------------ ------ ------- ----- -------- ---------- dc_awr_control 66 34.8 0 0 1 dc_global_oids 1,096,896 0.1 0 0 43 dc_histogram_data 380,321 12.0 0 0 1,104 dc_histogram_defs 644,929 6.8 0 2,822 867 dc_object_grants 40 42.5 0 0 1 dc_objects 1,399,404 1.4 0 490 686 dc_profiles 351 8.8 0 0 2 dc_props 1,792 2.2 0 0 2 dc_rollback_segments 885 0.0 0 0 91 dc_segments 142,711 8.7 0 12 147 dc_sequences 652 19.0 0 652 1 dc_tablespaces 1,720,679 0.0 0 0 2 dc_users 1,692,618 0.0 696 8.2 0 23 outstanding_alerts 2 100.0 0 0 0 qmtmrctn_cache_entries 39 100.0 0 0 0 qmtmrctq_cache_entries 1,586 100.0 0 0 0 sch_lj_oids 404 88.9 0 0 2 ------------------------------------------------------------- Library Cache Activity DB/Inst: DELPNRA0/delpnra0 Snaps: 6121-6131 ->"Pct Misses" should be very low Get Pct Pin Pct Invali- Namespace Requests Miss Requests Miss Reloads dations --------------- ------------ ------ -------------- ------ ---------- -------- ACL 30 50.0 30 50.0 0 0 AUDIT POLICY 348 16.1 348 17.0 0 0 BODY 3,502 15.3 5,528 12.5 101 0 CLUSTER 1,943 1.4 1,955 1.4 0 0 DBLINK 1 100.0 0 0 0 DIRECTORY 201 50.2 402 50.7 0 0 EDITION 306 20.6 607 21.7 0 0 INDEX 215 29.3 215 73.0 94 0 JAVA DATA 176 13.6 4 100.0 4 0 JAVA SOURCE 4 100.0 4 100.0 0 0 OBJECT ID 78 100.0 0 0 0 SCHEMA 8,868 0.5 0 0 0 SECURITY CLASS 15 100.0 15 100.0 0 0 SQL AREA 10,882,873 0.2 7,991,248 0.3 6,810 94 SQL AREA BUILD 10,624 86.3 0 0 0 SQL AREA STATS 6,781 88.1 6,781 88.1 0 0 TABLE/PROCEDURE 1,177,924 0.7 1,251,122 2.1 13,858 0 TRIGGER 583 24.7 2,701 7.5 34 0 XML SCHEMA 88 44.3 135 60.0 0 0 ------------------------------------------------------------- Rule Sets DB/Inst: DELPNRA0/delpnra0 Snaps: 6121-6131 -> * indicates Rule Set activity (re)started between Begin/End snaps -> Top 25 ordered by Evaluations desc No-SQL SQL Rule * Eval/sec Reloads/sec Eval % Eval % ----------------------------------- - ------------ ----------- ------ ------ SYS.ALERT_QUE_R 0 0 0 0 ------------------------------------------------------------- Streams Pool Advisory DB/Inst: DELPNRA0/delpnra0 End Snap: 6131 Streams Pool Streams Pool Est Spill Est Spill Est Unspill Est Unspill Size (M) Size Factor Count Time (s) Count Time (s) ------------ ------------ --------- --------- ----------- ----------- 16.0 1.0 0 0 0 0 32.0 2.0 0 0 0 0 48.0 3.0 0 0 0 0 64.0 4.0 0 0 0 0 80.0 5.0 0 0 0 0 96.0 6.0 0 0 0 0 112.0 7.0 0 0 0 0 128.0 8.0 0 0 0 0 144.0 9.0 0 0 0 0 160.0 10.0 0 0 0 0 176.0 11.0 0 0 0 0 192.0 12.0 0 0 0 0 208.0 13.0 0 0 0 0 224.0 14.0 0 0 0 0 240.0 15.0 0 0 0 0 256.0 16.0 0 0 0 0 272.0 17.0 0 0 0 0 288.0 18.0 0 0 0 0 304.0 19.0 0 0 0 0 320.0 20.0 0 0 0 0 ------------------------------------------------------------- Shared Pool Advisory DB/Inst: DELPNRA0/delpnra0 End Snap: 6131 -> SP: Shared Pool Est LC: Estimated Library Cache Factr: Factor -> Note there is often a 1:Many correlation between a single logical object in the Library Cache, and the physical number of memory objects associated with it. Therefore comparing the number of Lib Cache objects (e.g. in v$librarycache), with the number of Lib Cache Memory Objects is invalid Est LC Est LC Est LC Est LC Shared SP Est LC Time Time Load Load Est LC Pool Size Size Est LC Saved Saved Time Time Mem Size (M) Factr (M) Mem Obj (s) Factr (s) Factr Obj Hits ---------- ----- -------- ------------ ------- ------ ------- ------ ----------- 528 1.0 43 2,191 ####### 1.0 81,602 1.0 ########### 544 1.0 57 2,682 ####### 1.0 69,443 .9 ########### 560 1.1 70 3,200 ####### 1.0 64,989 .8 ########### 576 1.1 85 3,680 ####### 1.0 61,778 .8 ########### 592 1.1 100 4,196 ####### 1.0 58,421 .7 ########### 608 1.2 114 4,480 ####### 1.0 55,206 .7 ########### 624 1.2 128 4,656 ####### 1.0 52,415 .6 ########### 640 1.2 143 4,874 ####### 1.0 50,356 .6 ########### 656 1.2 157 5,191 ####### 1.0 48,625 .6 ########### 672 1.3 172 5,610 ####### 1.0 47,094 .6 ########### 720 1.4 219 6,388 ####### 1.0 43,931 .5 ########### 784 1.5 281 7,810 ####### 1.0 41,817 .5 ########### 848 1.6 343 8,881 ####### 1.0 40,618 .5 ########### 912 1.7 406 10,446 ####### 1.0 39,595 .5 ########### 976 1.8 469 11,581 ####### 1.0 38,934 .5 ########### 1,040 2.0 532 12,721 ####### 1.0 38,450 .5 ########### 1,104 2.1 594 14,372 ####### 1.0 38,102 .5 ########### ------------------------------------------------------------- Java Pool Advisory DB/Inst: DELPNRA0/delpnra0 End Snap: 6131 Est LC Est LC Est LC Est LC Java JP Est LC Time Time Load Load Est LC Pool Size Size Est LC Saved Saved Time Time Mem Size(M) Factr (M) Mem Obj (s) Factr (s) Factr Obj Hits ---------- ----- -------- ------------ ------- ------ ------- ------ ----------- 16 .5 7 243 285 1.0 81,603 1.0 594 32 1.0 8 271 285 1.0 81,603 1.0 594 48 1.5 8 271 448 1.6 81,440 1.0 859 64 2.0 8 271 448 1.6 81,440 1.0 859 80 2.5 8 271 448 1.6 81,440 1.0 859 96 3.0 8 271 448 1.6 81,440 1.0 859 112 3.5 8 271 448 1.6 81,440 1.0 859 128 4.0 8 271 448 1.6 81,440 1.0 859 144 4.5 8 271 448 1.6 81,440 1.0 859 160 5.0 8 271 448 1.6 81,440 1.0 859 176 5.5 8 271 448 1.6 81,440 1.0 859 ------------------------------------------------------------- SGA Target Advisory DB/Inst: DELPNRA0/delpnra0 End Snap: 6131 SGA Target SGA Size Est DB Est DB Est Physical Size (M) Factor Time (s) Time Factor Reads ---------- -------- -------- ----------- -------------- 512 .3 0 .0 219,816,969 1,024 .5 ######## 3.8 243,777,019 1,536 .8 ######## 2.1 229,225,135 2,048 1.0 ######## 1.0 219,816,969 2,560 1.3 2,509 .0 211,090,235 3,072 1.5 1,972 .0 211,090,235 3,584 1.8 1,972 .0 206,540,024 4,096 2.0 1,972 .0 205,133,195 ------------------------------------------------------------- SGA Memory Summary DB/Inst: DELPNRA0/delpnra0 Snaps: 6121-6131 End Size (Bytes) SGA regions Begin Size (Bytes) (if different) ------------------------------ -------------------- -------------------- Database Buffers 1,493,172,224 Fixed Size 3,053,880 Redo Buffers 13,721,600 Variable Size 2,785,019,592 -------------------- -------------------- sum 4,294,967,296 ------------------------------------------------------------- SGA breakdown difference DB/Inst: DELPNRA0/delpnra0 Snaps: 6121-6131 -> Top 35 rows by size, ordered by Pool, Name (note rows with null values for Pool column, or Names showing free memory are always shown) -> Null value for Begin MB or End MB indicates the size of that Pool/Name was insignificant, or zero in that snapshot Pool Name Begin MB End MB % Diff ------ ------------------------------ -------------- -------------- -------- java p free memory 14.6 14.6 0.00 java p JOXLE 16.9 16.9 0.00 java p joxs heap .5 .5 0.00 large ASM map operations hashta .4 .4 0.00 large free memory 16.6 16.6 0.00 large PX msg pool 15.0 15.0 0.00 shared ASH buffers 8.0 8.0 0.00 shared buffer handles 9.2 9.2 0.00 shared db_block_hash_buckets 11.0 11.0 0.00 shared dbktb: trace buffer 31.3 31.3 0.00 shared enqueue 8.6 8.6 0.00 shared event statistics per sess 43.0 43.0 0.00 shared FileOpenBlock 6.6 6.6 0.00 shared free memory 82.3 77.4 -6.00 shared KGH: NO ACCESS 10.6 10.6 0.00 shared KGLH0 19.1 21.2 11.48 shared ksunfy : SSO free list 24.2 24.2 0.00 shared KTI-UNDO 22.4 22.4 0.00 shared Multiblock Index SO 23.7 23.7 0.00 shared object queue hash buckets 8.0 8.0 0.00 shared private strands 22.4 22.4 0.00 shared procs: ksunfy 5.5 5.5 0.00 shared row cache 8.3 8.3 0.00 shared SQLA 15.2 9.5 -37.78 shared transaction 9.5 9.5 0.00 shared VIRTUAL CIRCUITS 9.9 9.9 0.00 shared write state object 7.3 7.3 0.00 shared XDBSC 9.8 9.8 0.00 stream free memory 16.0 16.0 0.00 buffer_cache 1,408.0 1,408.0 0.00 fixed_sga 2.9 2.9 0.00 log_buffer 13.1 13.1 0.00 shared_io_pool 16.0 16.0 0.00 ------------------------------------------------------------- SQL Memory Statistics DB/Inst: DELPNRA0/delpnra0 Snaps: 6121-6131 Begin End % Diff -------------- -------------- -------------- Avg Cursor Size (KB): 38.72 37.08 -4.41 Cursor to Parent ratio: 1.22 1.35 9.64 Total Cursors: 455 444 -2.48 Total Parents: 372 328 -13.41 ------------------------------------------------------------- init.ora Parameters DB/Inst: DELPNRA0/delpnra0 Snaps: 6121-6131 End value Parameter Name Begin value (if different) ----------------------------- --------------------------------- -------------- audit_file_dest D:\ORACLE\ADMIN\DELPNRA0\ADUMP audit_trail DB compatible 12.1.0.2.0 control_file_record_keep_time 15 control_files +DELPNRA0_DATA/DELPNRA0/CONTROLFI LE/current.260.866621499, +DELPNR A0_FRA/DELPNRA0/CONTROLFILE/curre nt.256.866621499 db_block_size 8192 db_cache_size 0 db_create_file_dest +DELPNRA0_DATA db_create_online_log_dest_1 +DELPNRA0_DATA db_create_online_log_dest_2 +DELPNRA0_FRA db_domain db_file_multiblock_read_count 16 db_name DELPNRA0 db_recovery_file_dest +DELPNRA0_FRA db_recovery_file_dest_size 21474836480 diagnostic_dest D:\ORACLE dispatchers (PROTOCOL=TCP) (SERVICE=DELPNRA0X DB) hash_area_size 0 local_listener (ADDRESS=(PROTOCOL=TCP)(HOST=10. 101.0.210)(PORT=1521)) log_archive_format DELPNRA0_%s_%r_%t.arc memory_max_target 0 memory_target 0 nls_language AMERICAN nls_territory THE NETHERLANDS open_cursors 500 optimizer_dynamic_sampling 0 pga_aggregate_target 1073741824 processes 2000 recyclebin OFF remote_login_passwordfile EXCLUSIVE sga_max_size 4294967296 sga_target 2147483648 shared_pool_reserved_size 58720256 shared_servers 0 _smu_debug_mode 134217728 sort_area_size 0 undo_tablespace UNDOTBS1 ------------------------------------------------------------- End of Report ( sp_6121_6131.lst )