WORKLOAD REPOSITORY report for DB Name DB Id Instance Inst Num Startup Time Release RAC ------------ ----------- ------------ -------- --------------- ----------- --- DEVDWH01 2903813870 DEVDWH01 1 04-Oct-11 16:30 11.2.0.1.0 NO Host Name Platform CPUs Cores Sockets Memory(GB) ---------------- -------------------------------- ---- ----- ------- ---------- mbhibisrv01.maxb Linux IA (32-bit) 16 8 2 15.86 Snap Id Snap Time Sessions Curs/Sess --------- ------------------- -------- --------- Begin Snap: 983 10-Oct-11 10:30:50 30 1.6 End Snap: 985 10-Oct-11 12:30:17 39 2.0 Elapsed: 119.45 (mins) DB Time: 831.29 (mins) Cache Sizes Begin End ~~~~~~~~~~~ ---------- ---------- Buffer Cache: 64M 64M Std Block Size: 8K Shared Pool Size: 384M 384M Log Buffer: 10,604K Load Profile Per Second Per Transaction Per Exec Per Call ~~~~~~~~~~~~ --------------- --------------- ---------- ---------- DB Time(s): 7.0 14.4 0.06 0.04 DB CPU(s): 0.4 0.8 0.00 0.00 Redo size: 205,594.9 426,101.2 Logical reads: 15,672.5 32,481.7 Block changes: 1,012.3 2,098.0 Physical reads: 881.0 1,825.9 Physical writes: 119.3 247.3 User calls: 196.8 407.8 Parses: 82.6 171.1 Hard parses: 1.9 3.8 W/A MB processed: 688.4 1,426.7 Logons: 18.8 38.9 Executes: 113.3 234.9 Rollbacks: 0.0 0.0 Transactions: 0.5 Instance Efficiency Percentages (Target 100%) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Buffer Nowait %: 99.85 Redo NoWait %: 95.66 Buffer Hit %: 98.18 In-memory Sort %: 100.00 Library Hit %: 97.14 Soft Parse %: 97.76 Execute to Parse %: 27.16 Latch Hit %: 99.77 Parse CPU to Parse Elapsd %: 75.69 % Non-Parse CPU: 93.86 Shared Pool Statistics Begin End ------ ------ Memory Usage %: 86.68 86.34 % SQL with executions>1: 85.06 66.52 % Memory for SQL w/exec>1: 77.98 75.65 Top 5 Timed Foreground Events ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Avg wait % DB Event Waits Time(s) (ms) time Wait Class ------------------------------ ------------ ----------- ------ ------ ---------- log file sync 140,912 23,137 164 46.4 Commit enq: CR - block range reuse ck 29,847 10,564 354 21.2 Other PX Deq: Slave Session Stats 142,918 10,095 71 20.2 Other DB CPU 2,795 5.6 direct path write 21,116 2,524 120 5.1 User I/O Host CPU (CPUs: 16 Cores: 8 Sockets: 2) ~~~~~~~~ Load Average Begin End %User %System %WIO %Idle --------- --------- --------- --------- --------- --------- 0.21 5.05 4.1 1.4 8.1 94.0 Instance CPU ~~~~~~~~~~~~ % of total CPU for Instance: 3.1 % of busy CPU for Instance: 51.5 %DB time waiting for CPU - Resource Mgr: 0.0 Memory Statistics ~~~~~~~~~~~~~~~~~ Begin End Host Mem (MB): 16,239.1 16,239.1 SGA use (MB): 1,504.0 1,504.0 PGA use (MB): 112.1 111.5 % Host Mem used for SGA+PGA: 9.95 9.95 Time Model Statistics DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 -> Total time in database user-calls (DB Time): 49877.4s -> Statistics including the word "background" measure background process time, and so do not contribute to the DB time statistic -> Ordered by % or DB time desc, Statistic name Statistic Name Time (s) % of DB Time ------------------------------------------ ------------------ ------------ sql execute elapsed time 24,833.2 49.8 DB CPU 2,795.1 5.6 connection management call elapsed time 320.5 .6 parse time elapsed 298.1 .6 hard parse elapsed time 258.9 .5 PL/SQL execution elapsed time 63.0 .1 PL/SQL compilation elapsed time 27.1 .1 hard parse (sharing criteria) elapsed time 6.6 .0 failed parse elapsed time 0.4 .0 repeated bind elapsed time 0.3 .0 hard parse (bind mismatch) elapsed time 0.2 .0 sequence load elapsed time 0.0 .0 DB time 49,877.4 background elapsed time 12,692.2 background cpu time 767.2 ------------------------------------------------------------- Operating System Statistics DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 -> *TIME statistic values are diffed. All others display actual values. End Value is displayed if different -> ordered by statistic type (CPU Use, Virtual Memory, Hardware Config), Name Statistic Value End Value ------------------------- ---------------------- ---------------- BUSY_TIME 692,306 IDLE_TIME 10,893,362 IOWAIT_TIME 943,140 NICE_TIME 43,637 SYS_TIME 160,809 USER_TIME 477,935 LOAD 0 5 RSRC_MGR_CPU_WAIT_TIME 0 PHYSICAL_MEMORY_BYTES 17,027,981,312 NUM_CPUS 16 NUM_CPU_CORES 8 NUM_CPU_SOCKETS 2 GLOBAL_RECEIVE_SIZE_MAX 4,194,304 GLOBAL_SEND_SIZE_MAX 1,048,576 TCP_RECEIVE_SIZE_DEFAULT 87,380 TCP_RECEIVE_SIZE_MAX 4,194,304 TCP_RECEIVE_SIZE_MIN 4,096 TCP_SEND_SIZE_DEFAULT 16,384 TCP_SEND_SIZE_MAX 4,194,304 TCP_SEND_SIZE_MIN 4,096 ------------------------------------------------------------- Operating System Statistics - DetailDB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 Snap Time Load %busy %user %sys %idle %iowait --------------- -------- -------- -------- -------- -------- -------- 10-Oct 10:30:50 0.2 N/A N/A N/A N/A N/A 10-Oct 11:30:03 6.8 7.6 5.4 1.7 92.4 8.0 10-Oct 12:30:17 5.0 4.4 2.9 1.1 95.6 8.3 ------------------------------------------------------------- Foreground Wait Class DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 -> s - second, ms - millisecond - 1000th of a second -> ordered by wait time desc, waits desc -> %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0 -> Captured Time accounts for 120.2% of Total DB time 49,877.40 (s) -> Total FG Wait Time: 57,144.60 (s) DB CPU time: 2,795.06 (s) Avg %Time Total Wait wait Wait Class Waits -outs Time (s) (ms) %DB time -------------------- ---------------- ----- ---------------- -------- --------- Commit 140,912 0 23,137 164 46.4 Other 607,368 31 22,389 37 44.9 User I/O 1,474,626 0 6,812 5 13.7 DB CPU 2,795 5.6 Application 20,683 0 2,237 108 4.5 Configuration 44,856 0 1,996 45 4.0 Concurrency 156,574 0 563 4 1.1 System I/O 403 0 6 14 0.0 Network 722,866 0 5 0 0.0 ------------------------------------------------------------- Foreground Wait Events DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 -> s - second, ms - millisecond - 1000th of a second -> Only events with Total Wait Time (s) >= .001 are shown -> ordered by wait time desc, waits desc (idle events last) -> %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0 Avg %Time Total Wait wait Waits % DB Event Waits -outs Time (s) (ms) /txn time -------------------------- ------------ ----- ---------- ------- -------- ------ log file sync 140,912 0 23,137 164 40.7 46.4 enq: CR - block range reus 29,847 0 10,564 354 8.6 21.2 PX Deq: Slave Session Stat 142,918 1 10,095 71 41.3 20.2 direct path write 21,116 0 2,524 120 6.1 5.1 local write wait 6,998 0 2,371 339 2.0 4.8 enq: RO - fast object reus 3,029 0 1,953 645 0.9 3.9 free buffer waits 14,355 0 1,465 102 4.2 2.9 direct path write temp 7,852 0 1,045 133 2.3 2.1 PX Deq: Table Q Get Keys 102,954 0 776 8 29.8 1.6 latch: parallel query allo 11,815 0 494 42 3.4 1.0 buffer busy waits 104,356 0 478 5 30.2 1.0 enq: ST - contention 262 0 455 1736 0.1 .9 db file sequential read 686,088 0 369 1 198.4 .7 enq: KO - fast object chec 125 0 275 2198 0.0 .6 direct path read 559,180 0 222 0 161.7 .4 latch free 69,502 0 185 3 20.1 .4 PX Deq: Signal ACK EXT 23,564 0 157 7 6.8 .3 direct path sync 226 0 152 672 0.1 .3 reliable message 35,217 0 87 2 10.2 .2 read by other session 15,244 0 76 5 4.4 .2 db file scattered read 149,746 0 42 0 43.3 .1 rdbms ipc reply 1,229 1 26 21 0.4 .1 latch: shared pool 12,241 0 26 2 3.5 .1 log file switch (private s 8 0 21 2642 0.0 .0 write complete waits 5 0 20 3927 0.0 .0 enq: TX - allocate ITL ent 30,149 0 18 1 8.7 .0 library cache: mutex X 34,680 0 17 1 10.0 .0 os thread startup 1,006 0 16 16 0.3 .0 row cache lock 4 0 14 3556 0.0 .0 log file switch completion 11 0 12 1050 0.0 .0 enq: TX - row lock content 17,409 0 9 1 5.0 .0 control file parallel writ 54 0 6 107 0.0 .0 library cache load lock 23 0 5 220 0.0 .0 undo segment extension 52 87 4 75 0.0 .0 db file single write 6 0 3 581 0.0 .0 SQL*Net more data from cli 120,585 0 3 0 34.9 .0 Disk file operations I/O 7,616 0 3 0 2.2 .0 latch: cache buffers chain 3,087 0 3 1 0.9 .0 db file parallel read 10,912 0 2 0 3.2 .0 cursor: pin S wait on X 31 0 2 52 0.0 .0 Log file init write 603 0 2 3 0.2 .0 latch: row cache objects 529 0 1 3 0.2 .0 log buffer space 4 0 1 350 0.0 .0 PX qref latch 3,156 100 1 0 0.9 .0 log file switch (checkpoin 7 0 1 128 0.0 .0 SQL*Net message to client 571,305 0 1 0 165.2 .0 SQL*Net more data to clien 30,976 0 1 0 9.0 .0 direct path read temp 9,037 0 1 0 2.6 .0 enq: CF - contention 1 0 1 517 0.0 .0 enq: TX - index contention 5 0 0 99 0.0 .0 latch: call allocation 271 0 0 2 0.1 .0 latch: redo allocation 103 0 0 4 0.0 .0 cursor: pin S 519 0 0 1 0.2 .0 wait list latch free 27 0 0 12 0.0 .0 asynch descriptor resize 168,218 100 0 0 48.6 .0 buffer deadlock 3,848 100 0 0 1.1 .0 enq: PS - contention 206 0 0 1 0.1 .0 cursor: mutex S 82 100 0 2 0.0 .0 SQL*Net break/reset to cli 116 0 0 2 0.0 .0 ADR block file read 5 0 0 34 0.0 .0 Foreground Wait Events DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 -> s - second, ms - millisecond - 1000th of a second -> Only events with Total Wait Time (s) >= .001 are shown -> ordered by wait time desc, waits desc (idle events last) -> %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0 Avg %Time Total Wait wait Waits % DB Event Waits -outs Time (s) (ms) /txn time -------------------------- ------------ ----- ---------- ------- -------- ------ kksfbc child completion 3 100 0 51 0.0 .0 latch: enqueue hash chains 122 0 0 1 0.0 .0 cr request retry 13,448 100 0 0 3.9 .0 control file sequential re 346 0 0 0 0.1 .0 enq: TM - contention 4 0 0 14 0.0 .0 PX Deq: Table Q qref 347 0 0 0 0.1 .0 enq: PR - contention 2 0 0 15 0.0 .0 latch: object queue header 8 0 0 3 0.0 .0 Parameter File I/O 2 0 0 7 0.0 .0 library cache lock 11 0 0 1 0.0 .0 PX Deq: Signal ACK RSG 31 55 0 0 0.0 .0 lock deadlock retry 510 100 0 0 0.1 .0 latch: undo global data 4 0 0 1 0.0 .0 latch: messages 8 0 0 0 0.0 .0 SQL*Net message from clien 571,299 0 695,322 1217 165.2 PX Deq: Execution Msg 3,535,252 2 192,345 54 1,022.3 jobq slave wait 15,504 100 7,765 501 4.5 PX Deq: Table Q Normal 2,139,918 0 6,334 3 618.8 PX Deq Credit: send blkd 134,771 0 1,418 11 39.0 PX Deq: Execute Reply 48,590 0 430 9 14.1 PX Deq: Parse Reply 8,435 0 12 1 2.4 PX Deq: Msg Fragment 127 0 5 39 0.0 PX Deq: Join ACK 27,143 0 3 0 7.8 PX Deq Credit: need buffer 978 0 1 1 0.3 ------------------------------------------------------------- Background Wait Events DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 -> ordered by wait time desc, waits desc (idle events last) -> Only events with Total Wait Time (s) >= .001 are shown -> %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0 Avg %Time Total Wait wait Waits % bg Event Waits -outs Time (s) (ms) /txn time -------------------------- ------------ ----- ---------- ------- -------- ------ db file async I/O submit 37,553 0 8,157 217 10.9 64.3 log file parallel write 106,230 0 2,544 24 30.7 20.0 control file parallel writ 3,256 0 743 228 0.9 5.9 db file sequential read 6,661 0 74 11 1.9 .6 Data file init write 117 0 61 522 0.0 .5 latch: call allocation 30,552 0 57 2 8.8 .4 free buffer waits 56 0 37 666 0.0 .3 db file single write 253 0 21 83 0.1 .2 enq: CF - contention 3 0 15 5089 0.0 .1 latch: shared pool 5,460 0 13 2 1.6 .1 direct path write temp 28 0 10 359 0.0 .1 reliable message 182 3 7 41 0.1 .1 rdbms ipc reply 178 2 6 34 0.1 .0 os thread startup 276 0 5 17 0.1 .0 log file single write 44 0 2 56 0.0 .0 control file sequential re 9,152 0 2 0 2.6 .0 enq: JD - contention 1 0 2 2025 0.0 .0 direct path sync 3 0 2 599 0.0 .0 log file sync 4 0 1 281 0.0 .0 latch free 438 0 1 2 0.1 .0 enq: JS - queue lock 1 0 1 822 0.0 .0 latch: parallel query allo 82 0 1 9 0.0 .0 db file scattered read 296 0 1 2 0.1 .0 log file switch completion 2 0 0 111 0.0 .0 wait list latch free 18 0 0 11 0.0 .0 LGWR wait for redo copy 1,394 0 0 0 0.4 .0 SQL*Net break/reset to cli 4 0 0 31 0.0 .0 Disk file operations I/O 294 0 0 0 0.1 .0 ADR block file write 10 0 0 12 0.0 .0 ADR block file read 26 0 0 4 0.0 .0 direct path write 50 0 0 1 0.0 .0 latch: messages 190 0 0 0 0.1 .0 asynch descriptor resize 3,519 100 0 0 1.0 .0 latch: cache buffers lru c 54 0 0 0 0.0 .0 enq: PR - contention 2 0 0 8 0.0 .0 latch: redo writing 136 0 0 0 0.0 .0 latch: cache buffers chain 35 0 0 0 0.0 .0 SQL*Net more data from cli 4 0 0 3 0.0 .0 SQL*Net message to client 5,779 0 0 0 1.7 .0 latch: redo allocation 3 0 0 0 0.0 .0 PX Idle Wait 607,718 77 1,088,373 1791 175.7 rdbms ipc message 294,533 10 81,274 276 85.2 DIAG idle wait 14,359 100 14,374 1001 4.2 smon timer 76 14 7,299 96045 0.0 shared server idle wait 240 100 7,202 30008 0.1 dispatcher timer 120 100 7,201 60007 0.0 Streams AQ: qmn coordinato 762 34 7,198 9446 0.2 Streams AQ: qmn slave idle 273 0 7,198 26365 0.1 pmon timer 3,485 69 7,198 2065 1.0 Space Manager: slave idle 1,428 100 7,133 4995 0.4 Streams AQ: waiting for ti 29 66 6,376 219865 0.0 SQL*Net message from clien 7,706 0 8 1 2.2 class slave wait 33 0 0 0 0.0 ------------------------------------------------------------- Wait Event Histogram DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 -> Units for Total Waits column: K is 1000, M is 1000000, G is 1000000000 -> % of Waits: value of .0 indicates value was <.05%; value of null is truly 0 -> % of Waits: column heading of <=1s is truly <1024ms, >1s is truly >=1024ms -> Ordered by Event (idle events last) % of Waits ----------------------------------------------- Total Event Waits <1ms <2ms <4ms <8ms <16ms <32ms <=1s >1s -------------------------- ----- ----- ----- ----- ----- ----- ----- ----- ----- ADR block file read 31 67.7 3.2 12.9 6.5 9.7 ADR block file write 10 20.0 10.0 20.0 20.0 20.0 10.0 ADR file lock 12 100.0 Data file init write 204 1.0 93.1 5.9 Disk file operations I/O 7911 98.4 .3 .2 .5 .4 .1 .1 LGWR wait for redo copy 1396 99.0 .6 .2 .1 .1 Log file init write 603 .5 92.0 5.5 1.2 .2 .3 .3 PX Deq: Signal ACK EXT 23.6K 92.6 .3 .4 .5 .6 1.4 4.3 .1 PX Deq: Signal ACK RSG 31 96.8 3.2 PX Deq: Slave Session Stat 142.6 7.4 1.6 14.0 27.8 5.4 4.8 38.1 .9 PX Deq: Table Q Get Keys 103K 34.5 3.5 6.8 22.0 23.7 8.2 1.2 .0 PX Deq: Table Q qref 347 97.1 1.2 1.2 .6 PX qref latch 3156 90.8 4.2 2.9 1.4 .6 .1 Parameter File I/O 2 50.0 50.0 SQL*Net break/reset to cli 122 88.5 4.9 2.5 .8 3.3 SQL*Net message to client 577.4 100.0 SQL*Net more data from cli 121.2 100.0 .0 .0 .0 .0 .0 SQL*Net more data to clien 31K 99.9 .0 .0 .0 .0 asynch descriptor resize 172.1 100.0 .0 .0 buffer busy waits 104.3 74.6 14.5 6.9 3.1 .7 .1 .1 .1 buffer deadlock 3857 98.5 .9 .5 .1 control file parallel writ 3337 4.4 6.8 12.2 73.7 3.0 control file sequential re 9574 99.7 .0 .0 .1 .2 cr request retry 13.5K 100.0 .0 .0 .0 cursor: mutex S 81 12.3 24.7 58.0 4.9 cursor: pin S 519 81.3 8.3 9.1 1.2 .2 cursor: pin S wait on X 31 54.8 12.9 32.3 db file async I/O submit 37.6K 1.5 3.3 6.9 15.8 25.2 44.4 2.9 db file parallel read 10.9K 99.5 .4 .0 .0 .0 .1 db file scattered read 149.9 99.7 .0 .0 .0 .0 .0 .2 .0 db file sequential read 688.4 98.5 .0 .2 .2 .3 .3 .4 .0 db file single write 274 .7 4.7 4.7 3.6 27.7 58.0 .4 direct path read 555.9 99.7 .0 .1 .0 .0 .0 .1 .0 direct path read temp 9037 99.9 .0 .0 direct path sync 229 .4 .4 .4 .9 87.3 10.5 direct path write 21.2K 38.9 .2 .5 5.3 7.2 10.8 34.3 2.9 direct path write temp 7880 .7 .6 2.9 13.6 81.4 .9 enq: CF - contention 4 50.0 50.0 enq: CI - contention 1 100.0 enq: CR - block range reus 29.8K .0 .8 2.9 11.5 81.0 3.7 enq: HW - contention 3 100.0 enq: JD - contention 1 100.0 enq: JS - queue lock 1 100.0 enq: KO - fast object chec 125 .8 1.6 1.6 3.2 62.4 30.4 enq: PR - contention 4 25.0 75.0 enq: PS - contention 206 90.3 3.9 1.9 1.0 1.5 1.0 .5 enq: RO - fast object reus 3029 6.7 .3 .9 2.4 5.9 12.2 63.8 7.7 enq: ST - contention 262 8.8 4.2 .4 1.1 1.1 3.1 46.6 34.7 enq: TM - contention 4 75.0 25.0 enq: TX - allocate ITL ent 30.1K 85.5 11.4 2.6 .4 .0 .0 enq: TX - contention 1 100.0 enq: TX - index contention 5 20.0 80.0 enq: TX - row lock content 17.4K 89.8 8.5 1.4 .2 .0 free buffer waits 14.4K .8 .3 .2 .1 97.0 .0 .1 1.5 kksfbc child completion 3 100.0 latch free 69.9K 55.4 14.2 12.8 9.4 5.5 2.2 .6 latch: cache buffers chain 3122 73.3 11.2 9.0 4.8 1.4 .3 latch: cache buffers lru c 55 81.8 9.1 9.1 latch: call allocation 30.8K 43.7 14.4 31.1 10.4 .4 .0 latch: checkpoint queue la 9 100.0 Wait Event Histogram DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 -> Units for Total Waits column: K is 1000, M is 1000000, G is 1000000000 -> % of Waits: value of .0 indicates value was <.05%; value of null is truly 0 -> % of Waits: column heading of <=1s is truly <1024ms, >1s is truly >=1024ms -> Ordered by Event (idle events last) % of Waits ----------------------------------------------- Total Event Waits <1ms <2ms <4ms <8ms <16ms <32ms <=1s >1s -------------------------- ----- ----- ----- ----- ----- ----- ----- ----- ----- latch: enqueue hash chains 123 74.8 8.1 13.8 2.4 .8 latch: messages 198 95.5 2.5 1.5 .5 latch: object queue header 12 66.7 8.3 8.3 8.3 8.3 latch: parallel query allo 11.9K 53.2 10.0 8.3 5.5 2.2 .8 20.0 .1 latch: redo allocation 106 53.8 12.3 16.0 .9 17.0 latch: redo writing 136 98.5 1.5 latch: row cache objects 529 43.9 10.6 20.4 18.9 5.9 .4 latch: shared pool 17.7K 61.0 19.9 11.1 3.1 2.7 .9 1.4 latch: undo global data 4 100.0 library cache load lock 23 52.2 8.7 4.3 4.3 4.3 17.4 8.7 library cache lock 11 72.7 9.1 9.1 9.1 library cache: mutex X 34.7K 87.4 6.9 4.4 .9 .4 .0 .0 local write wait 6992 .3 1.9 6.9 15.4 71.6 3.9 lock deadlock retry 526 100.0 log buffer space 4 100.0 log file parallel write 106.3 50.0 4.2 4.7 5.4 11.9 9.6 14.0 .2 log file sequential read 44 100.0 log file single write 47 6.4 8.5 6.4 8.5 8.5 61.7 log file switch (checkpoin 7 100.0 log file switch (private s 8 50.0 50.0 log file switch completion 13 53.8 46.2 log file sync 134.3 .0 .1 .4 2.4 4.6 8.0 83.8 .7 os thread startup 1284 76.0 22.4 1.6 rdbms ipc reply 1408 98.2 .2 .1 .1 .1 .1 1.2 read by other session 15.2K 71.4 11.3 8.8 4.7 1.1 .6 2.0 .0 reliable message 35.4K 98.3 .5 .3 .1 .1 .1 .6 row cache lock 4 25.0 25.0 25.0 25.0 undo segment extension 52 42.3 1.9 1.9 15.4 15.4 23.1 wait list latch free 44 100.0 write complete waits 5 20.0 80.0 DIAG idle wait 14.4K 100.0 .0 PX Deq Credit: need buffer 978 59.3 17.1 11.8 9.4 2.5 PX Deq Credit: send blkd 134.7 33.1 12.2 10.9 8.6 9.6 15.1 10.5 .0 PX Deq: Execute Reply 48.6K 82.5 4.1 3.6 2.1 2.5 1.4 3.6 .1 PX Deq: Execution Msg 3524. 45.7 13.8 16.8 8.7 6.6 1.9 4.3 2.1 PX Deq: Join ACK 27.1K 98.1 1.4 .2 .2 .1 .0 PX Deq: Msg Fragment 127 15.7 3.1 12.6 18.9 7.9 41.7 PX Deq: Parse Reply 8435 97.8 1.1 .6 .1 .1 .0 .1 .1 PX Deq: Table Q Normal 2136. 71.7 13.2 7.3 2.7 1.4 1.4 2.3 .0 PX Idle Wait 578.7 .2 .0 .0 .1 .1 .2 14.3 85.1 SQL*Net message from clien 579.3 77.6 15.4 2.5 1.0 .5 1.3 .4 1.3 Space Manager: slave idle 1428 100.0 Streams AQ: qmn coordinato 768 66.1 .1 33.7 Streams AQ: qmn slave idle 276 6.2 93.8 Streams AQ: waiting for ti 29 34.5 31.0 34.5 class slave wait 33 97.0 3.0 dispatcher timer 121 100.0 jobq slave wait 15.6K .0 100.0 pmon timer 3499 29.7 .2 .3 .2 .2 .1 .2 69.0 rdbms ipc message 294.3 45.2 3.9 3.7 5.7 11.7 7.2 15.7 6.8 shared server idle wait 241 100.0 smon timer 76 100.0 ------------------------------------------------------------- Wait Event Histogram Detail (64 msec to 2 sec)DB/Inst: DEVDWH01/DEVDWH01 Sna -> Units for Total Waits column: K is 1000, M is 1000000, G is 1000000000 -> Units for % of Total Waits: ms is milliseconds s is 1024 milliseconds (approximately 1 second) -> % of Total Waits: total waits for all wait classes, including Idle -> % of Total Waits: value of .0 indicates value was <.05%; value of null is truly 0 -> Ordered by Event (only non-idle events are displayed) % of Total Waits ----------------------------------------------- Waits 64ms Event to 2s <32ms <64ms <1/8s <1/4s <1/2s <1s <2s >=2s -------------------------- ----- ----- ----- ----- ----- ----- ----- ----- ----- ADR block file read 3 90.3 6.5 3.2 ADR block file write 1 90.0 10.0 Data file init write 202 1.0 9.8 54.9 28.4 5.9 Disk file operations I/O 4 99.9 .0 .0 .0 .0 LGWR wait for redo copy 1 99.9 .1 Log file init write 2 99.7 .2 .2 PX Deq: Signal ACK EXT 1028 95.6 1.9 1.5 .6 .3 .0 .1 PX Deq: Slave Session Stat 55.5K 61.1 12.2 13.6 8.1 3.3 .8 .9 PX Deq: Table Q Get Keys 1303 98.7 1.0 .1 .1 .1 .0 SQL*Net break/reset to cli 4 96.7 2.5 .8 buffer busy waits 61 99.9 .0 .0 .0 .0 .0 .1 control file parallel writ 2523 23.3 22.9 21.9 13.5 11.5 3.8 1.9 1.1 control file sequential re 20 99.8 .1 .1 .1 .0 cursor: pin S wait on X 10 67.7 19.4 3.2 3.2 6.5 db file async I/O submit 17.1K 52.7 24.2 10.0 4.6 4.1 1.5 1.1 1.7 db file parallel read 12 99.9 .1 .0 .0 db file scattered read 251 99.8 .1 .0 .0 .0 .0 .0 db file sequential read 3066 99.6 .2 .1 .0 .0 .0 .0 .0 db file single write 160 41.6 27.7 10.9 8.0 7.7 3.6 .4 direct path read 475 99.9 .0 .0 .0 .0 .0 .0 .0 direct path read temp 4 100.0 .0 .0 direct path sync 213 2.2 5.7 6.6 9.6 34.9 30.6 5.7 4.8 direct path write 7736 62.8 12.9 8.5 3.9 5.8 3.2 2.2 .6 direct path write temp 6437 17.8 36.9 28.4 5.7 8.2 2.2 .3 .5 enq: CF - contention 2 25.0 25.0 50.0 enq: CR - block range reus 24.5K 15.3 27.4 29.8 12.6 8.8 2.4 1.3 2.4 enq: JD - contention 1 100.0 enq: JS - queue lock 1 100.0 enq: KO - fast object chec 97 7.2 7.2 9.6 16.0 12.8 16.8 15.2 15.2 enq: PS - contention 1 99.5 .5 enq: RO - fast object reus 2026 28.5 16.4 16.7 14.2 10.2 6.3 3.1 4.6 enq: ST - contention 163 18.7 7.3 8.4 10.3 8.8 11.8 15.6 19.1 enq: TM - contention 1 75.0 25.0 enq: TX - allocate ITL ent 8 100.0 .0 enq: TX - index contention 4 20.0 20.0 40.0 20.0 free buffer waits 29 98.3 .0 .0 .0 .1 .1 1.4 kksfbc child completion 3 100.0 latch free 397 99.4 .5 .0 latch: parallel query allo 2384 80.0 2.4 7.4 5.7 3.1 1.4 .1 latch: shared pool 242 98.6 1.2 .2 library cache load lock 5 73.9 8.7 4.3 4.3 4.3 4.3 library cache: mutex X 3 100.0 .0 local write wait 5128 24.5 21.6 23.0 13.1 9.6 4.3 1.8 2.2 log buffer space 4 100.0 log file parallel write 15K 85.8 7.6 3.4 1.4 1.4 .3 .1 .1 log file single write 29 38.3 40.4 14.9 2.1 4.3 log file switch (checkpoin 7 28.6 42.9 28.6 log file switch (private s 4 25.0 12.5 12.5 50.0 log file switch completion 11 7.7 15.4 15.4 15.4 30.8 15.4 log file sync 113.1 15.5 21.8 28.5 18.5 12.3 2.6 .4 .3 os thread startup 21 98.4 1.4 .2 rdbms ipc reply 19 98.7 .1 .1 1.2 read by other session 315 97.9 .4 1.0 .5 .0 .2 .0 reliable message 216 99.4 .1 .1 .1 .1 .2 row cache lock 1 50.0 25.0 25.0 undo segment extension 12 76.9 7.7 3.8 3.8 1.9 5.8 write complete waits 1 20.0 80.0 ------------------------------------------------------------- Wait Event Histogram Detail (4 sec to 2 min)DB/Inst: DEVDWH01/DEVDWH01 Snaps -> Units for Total Waits column: K is 1000, M is 1000000, G is 1000000000 -> Units for % of Total Waits: s is 1024 milliseconds (approximately 1 second) m is 64*1024 milliseconds (approximately 67 seconds or 1.1 minutes) -> % of Total Waits: total waits for all wait classes, including Idle -> % of Total Waits: value of .0 indicates value was <.05%; value of null is truly 0 -> Ordered by Event (only non-idle events are displayed) % of Total Waits ----------------------------------------------- Waits 4s Event to 2m <2s <4s <8s <16s <32s < 1m < 2m >=2m -------------------------- ----- ----- ----- ----- ----- ----- ----- ----- ----- buffer busy waits 77 99.9 .0 .0 control file parallel writ 36 98.9 .6 .3 .1 .1 .0 db file async I/O submit 648 98.3 .8 .4 .4 .1 .0 .0 db file sequential read 1 100.0 .0 direct path read 4 100.0 .0 direct path sync 11 95.2 3.5 .9 .4 direct path write 130 99.4 .6 .0 direct path write temp 41 99.5 .3 .2 .1 .0 enq: CF - contention 2 50.0 25.0 25.0 enq: CR - block range reus 719 97.6 1.0 .7 .4 .2 .0 .0 .0 enq: KO - fast object chec 19 84.8 4.8 3.2 3.2 3.2 .8 enq: RO - fast object reus 140 95.4 1.6 1.4 1.0 .4 .2 .0 enq: ST - contention 50 80.9 10.7 4.2 1.9 1.9 .4 free buffer waits 209 98.6 .2 1.1 .1 .0 library cache load lock 1 95.7 4.3 local write wait 152 97.8 .8 .7 .4 .3 log file parallel write 55 99.9 .0 .0 .0 log file switch (private s 4 50.0 37.5 12.5 log file switch completion 2 84.6 15.4 log file sync 390 99.7 .1 .1 .1 .0 .0 row cache lock 1 75.0 25.0 write complete waits 4 20.0 40.0 40.0 ------------------------------------------------------------- Wait Event Histogram Detail (4 min to 1 hr)DB/Inst: DEVDWH01/DEVDWH01 Snaps: -> Units for Total Waits column: K is 1000, M is 1000000, G is 1000000000 -> Units for % of Total Waits: m is 64*1024 milliseconds (approximately 67 seconds or 1.1 minutes) h is 4096*1024 milliseconds (approximately 70 minutes or 1.17 hours) -> % of Total Waits: total waits for all wait classes, including Idle -> % of Total Waits: value of .0 indicates value was <.05%; value of null is truly 0 -> Ordered by Event (only non-idle events are displayed) % of Total Waits ----------------------------------------- Waits 4m Event to 1h <2m <4m <8m <1/4h <1/2h < 1h >=1h -------------------------- ----- ----- ----- ----- ----- ----- ----- ----- enq: CR - block range reus 3 100.0 .0 ------------------------------------------------------------- Service Statistics DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 -> ordered by DB Time Physical Logical Service Name DB Time (s) DB CPU (s) Reads (K) Reads (K) ---------------------------- ------------ ------------ ------------ ------------ SYS$USERS 45,286 2,460 5,433 103,598 DEVDWH01.maxbupa.com 4,551 335 872 8,556 DEVDWH01XDB 0 0 0 0 SYS$BACKGROUND 0 0 9 134 ------------------------------------------------------------- Service Wait Class Stats DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 -> Wait Class info for services in the Service Statistics section. -> Total Waits and Time Waited displayed for the following wait classes: User I/O, Concurrency, Administrative, Network -> Time Waited (Wt Time) in seconds Service Name ---------------------------------------------------------------- User I/O User I/O Concurcy Concurcy Admin Admin Network Network Total Wts Wt Time Total Wts Wt Time Total Wts Wt Time Total Wts Wt Time --------- --------- --------- --------- --------- --------- --------- --------- SYS$USERS 1342605 5471 155825 522 0 0 10971 0 DEVDWH01.maxbupa.com 132023 1341 379 40 0 0 711895 5 SYS$BACKGROUND 7674 167 323 5 0 0 0 0 ------------------------------------------------------------- SQL ordered by Elapsed Time DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 -> Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code. -> % Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100 -> %Total - Elapsed Time as a percentage of Total DB time -> %CPU - CPU Time as a percentage of Elapsed Time -> %IO - User I/O Time as a percentage of Elapsed Time -> Captured SQL account for 4.1% of Total DB Time (s): 49,877 -> Captured PL/SQL account for 0.4% of Total DB Time (s): 49,877 Elapsed Elapsed Time Time (s) Executions per Exec (s) %Total %CPU %IO SQL Id ---------------- -------------- ------------- ------ ------ ------ ------------- 484.3 30,372 0.02 1.0 3.7 .2 g7mt7ptq286u7 insert into seg$ (file#,block#,type#,ts#,blocks,extents,minexts,maxexts,extsize, extpct,user#,iniexts,lists,groups,cachehint,hwmincr, spare1, scanhint, bitmapran ges) values (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,DECODE(:17,0 ,NULL,:17),:18,:19) 343.0 15,430 0.02 .7 8.7 .0 gg88ksxngbcmr Module: pmdtm@mbhibisrv01.maxbupa.com (TNS V1-V3) INSERT INTO STG_DLR_CONTAQUE_LOG(WRAPUPDURATION,DLR_USER,UNIQUEID,STATUS,START_E POCH,SETUPDURATION,QUEUE_SECONDS,PROCESSED,PHONE_NUMBER,PHONE_CODE,LIST_ID,LENGT H_IN_SEC,LEAD_ID,IDLEDURATION,GROUP_ID,FILENAME,END_EPOCH,DIGIT_CAPTURE,COMMENTS ,CAMPAIGN_ID,CALLERID,CALL_DATE,STG_REFRESH_DATE) VALUES ( :1, :2, :3, :4, :5, 161.0 1 160.97 .3 2.1 97.3 33br0z8mg0rv4 Module: JDBC Thin Client /* SQL Analyze(0) */ select /*+ full(t) parallel(t,64) parallel_index(t,64) dbms _stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring no_substrb_pad */to_char(count("SOURCE_DISTRIBUTION_ID")),to_char(substrb(dump( min("SOURCE_DISTRIBUTION_ID"),16,0,32),1,120)),to_char(substrb(dump(max("SOURCE_ 128.1 0 N/A .3 1.1 .0 9fdtc3abut70b Module: JDBC Thin Client CREATE BITMAP INDEX W_DAY_D_M23 ON W_DAY_D ( JULIAN_MONTH_NUM Asc ) NOLOG GING PARALLEL 111.5 1 111.51 .2 99.3 .4 17fs5z9cdhx7y Module: TOAD 9.5.0.31 SELECT TO_CHAR(SE.SETTLE_DATE,'MM'),TO_CHAR(SE.SETTLE_DATE,'MON'),COUNT(mbrshp_n o) FROM STG_MXMS_BF_TXN txn, STG_MXMS_BF_SETTLEMENT SE,STG_MXMS_MR_CONTRACT C,S TG_MXMS_MR_MEMBER M WHERE M.CONT_NO=C.CONT_NO AND TXN.DOC_TYPE='IV' AND TXN.D OC_NO=SE.SETTLED_DOC_NO AND TXN.CONT_NO=C.CONT_NO AND TXN.CONT_YYMM = C.CONT_YY 92.2 0 N/A .2 2.1 .2 cxbzcr3fmv70g Module: JDBC Thin Client CREATE BITMAP INDEX W_GL_BALANCE_F_F1 ON W_GL_BALANCE_F ( BALANCE_DT_WID A SC ) NOLOGGING PARALLEL 90.2 0 N/A .2 1.2 .0 7x2dqjv8pqguz Module: JDBC Thin Client CREATE BITMAP INDEX W_INT_ORG_DH_F1 ON W_INT_ORG_DH ( ORG_WID ASC ) NOLOG GING PARALLEL 83.0 1 82.97 .2 99.9 .1 ckft225kcb294 Module: TOAD 9.5.0.31 SELECT TO_CHAR(SE.SETTLE_DATE,'MM'),TO_CHAR(SE.SETTLE_DATE,'MON'),SUM (TXN.AMT)/ 100000 AS "GWP " FROM STG_MXMS_BF_TXN txn,STG_MXMS_BF_SETTLEMENT SE,STG_MXMS_MR_ CONTRACT C,STG_MXMS_BF_TXN_DTL BT WHERE TXN.DOC_TYPE='IV' AND TXN.DOC_NO=SE.SETT LED_DOC_NO AND TXN.CONT_NO=C.CONT_NO AND TXN.CONT_YYMM = C.CONT_YYMM AND TXN.DO 54.3 120 0.45 .1 7.6 63.4 6gvch1xu9ca3g DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN : = FALSE; BEGIN EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS(); :mydate := next_date ; IF broken THEN :b := 1; ELSE :b := 0; END IF; END; 41.0 1 41.03 .1 13.8 31.8 572fbaj0fdw2b Module: sqlplus@mbhibisrv01.maxbupa.com (TNS V1-V3) select output from table(dbms_workload_repository.awr_report_html( :dbid, :inst_num, :bid, :eid, :rpt_options )) SQL ordered by Elapsed Time DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 -> Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code. -> % Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100 -> %Total - Elapsed Time as a percentage of Total DB time -> %CPU - CPU Time as a percentage of Elapsed Time -> %IO - User I/O Time as a percentage of Elapsed Time -> Captured SQL account for 4.1% of Total DB Time (s): 49,877 -> Captured PL/SQL account for 0.4% of Total DB Time (s): 49,877 Elapsed Elapsed Time Time (s) Executions per Exec (s) %Total %CPU %IO SQL Id ---------------- -------------- ------------- ------ ------ ------ ------------- ------------------------------------------------------------- SQL ordered by CPU Time DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 -> Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code. -> %Total - CPU Time as a percentage of Total DB CPU -> %CPU - CPU Time as a percentage of Elapsed Time -> %IO - User I/O Time as a percentage of Elapsed Time -> Captured SQL account for 14.3% of Total CPU Time (s): 2,795 -> Captured PL/SQL account for 1.3% of Total CPU Time (s): 2,795 CPU CPU per Elapsed Time (s) Executions Exec (s) %Total Time (s) %CPU %IO SQL Id ---------- ------------ ---------- ------ ---------- ------ ------ ------------- 110.7 1 110.72 4.0 111.5 99.3 .4 17fs5z9cdhx7y Module: TOAD 9.5.0.31 SELECT TO_CHAR(SE.SETTLE_DATE,'MM'),TO_CHAR(SE.SETTLE_DATE,'MON'),COUNT(mbrshp_n o) FROM STG_MXMS_BF_TXN txn, STG_MXMS_BF_SETTLEMENT SE,STG_MXMS_MR_CONTRACT C,S TG_MXMS_MR_MEMBER M WHERE M.CONT_NO=C.CONT_NO AND TXN.DOC_TYPE='IV' AND TXN.D OC_NO=SE.SETTLED_DOC_NO AND TXN.CONT_NO=C.CONT_NO AND TXN.CONT_YYMM = C.CONT_YY 82.9 1 82.89 3.0 83.0 99.9 .1 ckft225kcb294 Module: TOAD 9.5.0.31 SELECT TO_CHAR(SE.SETTLE_DATE,'MM'),TO_CHAR(SE.SETTLE_DATE,'MON'),SUM (TXN.AMT)/ 100000 AS "GWP " FROM STG_MXMS_BF_TXN txn,STG_MXMS_BF_SETTLEMENT SE,STG_MXMS_MR_ CONTRACT C,STG_MXMS_BF_TXN_DTL BT WHERE TXN.DOC_TYPE='IV' AND TXN.DOC_NO=SE.SETT LED_DOC_NO AND TXN.CONT_NO=C.CONT_NO AND TXN.CONT_YYMM = C.CONT_YYMM AND TXN.DO 30.0 15,430 0.00 1.1 343.0 8.7 .0 gg88ksxngbcmr Module: pmdtm@mbhibisrv01.maxbupa.com (TNS V1-V3) INSERT INTO STG_DLR_CONTAQUE_LOG(WRAPUPDURATION,DLR_USER,UNIQUEID,STATUS,START_E POCH,SETUPDURATION,QUEUE_SECONDS,PROCESSED,PHONE_NUMBER,PHONE_CODE,LIST_ID,LENGT H_IN_SEC,LEAD_ID,IDLEDURATION,GROUP_ID,FILENAME,END_EPOCH,DIGIT_CAPTURE,COMMENTS ,CAMPAIGN_ID,CALLERID,CALL_DATE,STG_REFRESH_DATE) VALUES ( :1, :2, :3, :4, :5, 27.7 905 0.03 1.0 28.0 98.8 .0 62yyzw3309d6a Module: DBMS_SCHEDULER SELECT VALUE FROM V$SESSION_FIX_CONTROL WHERE BUGNO = :B1 AND SESSION_ID = USERE NV('SID') 17.7 30,372 0.00 0.6 484.3 3.7 .2 g7mt7ptq286u7 insert into seg$ (file#,block#,type#,ts#,blocks,extents,minexts,maxexts,extsize, extpct,user#,iniexts,lists,groups,cachehint,hwmincr, spare1, scanhint, bitmapran ges) values (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,DECODE(:17,0 ,NULL,:17),:18,:19) 17.2 226 0.08 0.6 17.4 99.2 .0 c0j6cx9kzjf7g Module: DBMS_SCHEDULER SELECT EXTRACTVALUE(VALUE(T), '/select_list_item/pos') + 1 POS, EXTRACTVALUE(VAL UE(T), '/select_list_item/value') RES, EXTRACTVALUE(VALUE(T), '/select_list_item /nonnulls') NONNULLS, EXTRACTVALUE(VALUE(T), '/select_list_item/ndv') NDV, EXTRA CTVALUE(VALUE(T), '/select_list_item/split') SPLIT, EXTRACTVALUE(VALUE(T), '/sel 14.9 1 14.89 0.5 13.7 108.3 .7 07favwdwwxqpd Module: pmdtm@mbhibisrv01.maxbupa.com (TNS V1-V3) SELECT /*+ USE_HASH (X1 X2 X3 X4 X5) */ W_GL_OTHER_FS.GL_ACCOUNT_ID , ACCT.ROW _WID GL_ACCOUNT_WID , ACCT.COST_CENTER_NUM COST_CENTER_NUM , ACCT.COST_CENTER_AT TRIB COST_CENTER_ATTRIB , ACCT.PROFIT_CENTER_NUM PROFIT_CENTER_NUM , ACCT.PROFIT _CENTER_ATTRIB PROFIT_CENTER_ATTRIB , BUDGT.ROW_WID BUDGT_ORG_WID , W_GL_OTHER_F 9.1 14,935 0.00 0.3 9.2 98.5 .0 cvhk2j2gymzhd Module: DBMS_SCHEDULER SELECT SU.NAME, SO.NAME, A.STATSTYPE#, C.INTCOL# FROM ASSOCIATION$ A, OBJ$ O, US ER$ U, COL$ C, OBJ$ SO, USER$ SU, COLTYPE$ CT, OBJ$ TY WHERE O.OWNER#=U.USER# AN D A.OBJ#=TY.OBJ# AND O.OBJ#=C.OBJ# AND C.INTCOL#=CT.INTCOL# AND O.OBJ#=CT.OBJ# A ND CT.TOID=TY.OID$ AND A.STATSTYPE#=SO.OBJ# AND SO.OWNER#=SU.USER# AND O.TYPE#=2 7.1 48,291 0.00 0.3 23.1 30.7 .3 0kkhhb2w93cx0 update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,extsize=:9,e xtpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535, NULL, :13),groups=decod e(:14, 65535, NULL, :14), cachehint=:15, hwmincr=:16, spare1=DECODE(:17,0,NULL,: 17),scanhint=:18, bitmapranges=:19 where ts#=:1 and file#=:2 and block#=:3 6.0 2 3.01 0.2 9.7 62.0 26.8 0tj7ggphk3u93 SQL ordered by CPU Time DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 -> Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code. -> %Total - CPU Time as a percentage of Total DB CPU -> %CPU - CPU Time as a percentage of Elapsed Time -> %IO - User I/O Time as a percentage of Elapsed Time -> Captured SQL account for 14.3% of Total CPU Time (s): 2,795 -> Captured PL/SQL account for 1.3% of Total CPU Time (s): 2,795 CPU CPU per Elapsed Time (s) Executions Exec (s) %Total Time (s) %CPU %IO SQL Id ---------- ------------ ---------- ------ ---------- ------ ------ ------------- Module: JDBC Thin Client BEGIN DBMS_STATS.GATHER_TABLE_STATS(ownname => 'DWHFIN', tabname => 'W_AR_XACT_F ', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL INDEX ED COLUMNS SIZE AUTO',cascade => false, degree => DBMS_STATS.DEFAULT_DEGREE) ; E ND; ------------------------------------------------------------- SQL ordered by User I/O Wait Time DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 -> Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code. -> %Total - User I/O Time as a percentage of Total User I/O Wait time -> %CPU - CPU Time as a percentage of Elapsed Time -> %IO - User I/O Time as a percentage of Elapsed Time -> Captured SQL account for 5.0% of Total User I/O Wait Time (s): 6 -> Captured PL/SQL account for 1.0% of Total User I/O Wait Time (s): User I/O UIO per Elapsed Time (s) Executions Exec (s) %Total Time (s) %CPU %IO SQL Id ---------- ------------ ---------- ------ ---------- ------ ------ ------------- 156.6 1 156.63 2.2 161.0 2.1 97.3 33br0z8mg0rv4 Module: JDBC Thin Client /* SQL Analyze(0) */ select /*+ full(t) parallel(t,64) parallel_index(t,64) dbms _stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring no_substrb_pad */to_char(count("SOURCE_DISTRIBUTION_ID")),to_char(substrb(dump( min("SOURCE_DISTRIBUTION_ID"),16,0,32),1,120)),to_char(substrb(dump(max("SOURCE_ 40.6 1 40.64 0.6 41.0 .7 99.2 4hurwnx49uf2w Module: pmdtm@mbhibisrv01.maxbupa.com (TNS V1-V3) SELECT TARGET_TABLE.CHANGED_ON_DT AS CHANGED_ON_DT, TARGET_TABLE.AUX1_CHANGED_ ON_DT AS AUX1_CHANGED_ON_DT, TARGET_TABLE.AUX2_CHANGED_ON_DT AS AUX2_CHANGED_ON _DT, TARGET_TABLE.AUX3_CHANGED_ON_DT AS AUX3_CHANGED_ON_DT, TARGET_TABLE.AUX4_ CHANGED_ON_DT AS AUX4_CHANGED_ON_DT, TARGET_TABLE.DELETE_FLG AS DELETE_FLG, TA 34.5 120 0.29 0.5 54.3 7.6 63.4 6gvch1xu9ca3g DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN : = FALSE; BEGIN EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS(); :mydate := next_date ; IF broken THEN :b := 1; ELSE :b := 0; END IF; END; 27.9 3,562 0.01 0.4 28.3 1.3 98.5 db78fxqxwxt7r select /*+ rule */ bucket, endpoint, col#, epvalue from histgrm$ where obj#=:1 a nd intcol#=:2 and row#=:3 order by bucket 14.1 2 7.07 0.2 14.4 1.6 98.5 gmg0jbf9230zp Module: pmdtm@mbhibisrv01.maxbupa.com (TNS V1-V3) SELECT LOOKUP_TABLE.ROW_WID AS ROW_WID, LOOKUP_TABLE.COST_CENTER_NUM AS COST_C ENTER_NUM, LOOKUP_TABLE.COST_CENTER_ATTRIB AS COST_CENTER_ATTRIB, LOOKUP_TABLE .PROFIT_CENTER_NUM AS PROFIT_CENTER_NUM, LOOKUP_TABLE.PROFIT_CENTER_ATTRIB AS P ROFIT_CENTER_ATTRIB, LOOKUP_TABLE.DATASOURCE_NUM_ID AS DATASOURCE_NUM_ID, LOOK 13.0 1 13.04 0.2 41.0 13.8 31.8 572fbaj0fdw2b Module: sqlplus@mbhibisrv01.maxbupa.com (TNS V1-V3) select output from table(dbms_workload_repository.awr_report_html( :dbid, :inst_num, :bid, :eid, :rpt_options )) 10.6 96 0.11 0.2 10.7 .1 99.2 39m4sx9k63ba2 select /*+ index(idl_ub2$ i_idl_ub21) +*/ piece#,length,piece from idl_ub2$ wher e obj#=:1 and part=:2 and version=:3 order by piece# 10.5 1 10.54 0.2 11.2 5.9 94.4 156djstmt3kn3 Module: pmdtm@mbhibisrv01.maxbupa.com (TNS V1-V3) SELECT F.GROUP_ACCT_WID, F.CUSTOMER_WID, F.CUSTOMER_ACCNT_WID, F.OPERATING_U NIT_ORG_WID, F.RECEIVABLES_ORG_WID, F.LEDGER_WID, F.PROFIT_CENTER_WID, F.COM PANY_ORG_WID, F.BUSN_AREA_ORG_WID, D.MCAL_YEAR_WID, D.MCAL_CAL_WID, F.DOC_TY PE_WID, F.DOC_STATUS_WID, F.DB_CR_IND, F.LOC_CURR_CODE, SUM(F.AR_LOC_AMT) AR 10.0 1 10.05 0.1 10.1 .6 99.4 5dfmd823r8dsp insert into wrh$_memory_resize_ops (snap_id, dbid, instance_number, compone nt, oper_type, start_time, end_time, target_size, oper_mode, parameter, initi al_size, final_size, status) select snap_id, dbid, instance_num, compo nent, oper_type, start_time, max(end_time), target_size, max(oper_mode), 9.3 2,078 0.00 0.1 10.0 2.5 93.0 3ktacv9r56b51 select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_obj#, nvl(proper ty,0),subname,type#,d_attrs from dependency$ d, obj$ o where d_obj#=:1 and p_obj #=obj#(+) order by order# ------------------------------------------------------------- SQL ordered by Gets DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 -> Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code. -> %Total - Buffer Gets as a percentage of Total Buffer Gets -> %CPU - CPU Time as a percentage of Elapsed Time -> %IO - User I/O Time as a percentage of Elapsed Time -> Total Buffer Gets: 112,321,756 -> Captured SQL account for 37.1% of Total Buffer Gets Elapsed Gets Executions per Exec %Total Time (s) %CPU %IO SQL Id ----------- ----------- ------------ ------ ---------- ------ ------ ----------- 19,088,636 1 1.908864E+07 17.0 111.5 99.3 .4 17fs5z9cdhx Module: TOAD 9.5.0.31 SELECT TO_CHAR(SE.SETTLE_DATE,'MM'),TO_CHAR(SE.SETTLE_DATE,'MON'),COUNT(mbrshp_n o) FROM STG_MXMS_BF_TXN txn, STG_MXMS_BF_SETTLEMENT SE,STG_MXMS_MR_CONTRACT C,S TG_MXMS_MR_MEMBER M WHERE M.CONT_NO=C.CONT_NO AND TXN.DOC_TYPE='IV' AND TXN.D OC_NO=SE.SETTLED_DOC_NO AND TXN.CONT_NO=C.CONT_NO AND TXN.CONT_YYMM = C.CONT_YY 14,210,242 1 1.421024E+07 12.7 83.0 99.9 .1 ckft225kcb2 Module: TOAD 9.5.0.31 SELECT TO_CHAR(SE.SETTLE_DATE,'MM'),TO_CHAR(SE.SETTLE_DATE,'MON'),SUM (TXN.AMT)/ 100000 AS "GWP " FROM STG_MXMS_BF_TXN txn,STG_MXMS_BF_SETTLEMENT SE,STG_MXMS_MR_ CONTRACT C,STG_MXMS_BF_TXN_DTL BT WHERE TXN.DOC_TYPE='IV' AND TXN.DOC_NO=SE.SETT LED_DOC_NO AND TXN.CONT_NO=C.CONT_NO AND TXN.CONT_YYMM = C.CONT_YYMM AND TXN.DO 1,916,814 15,430 124.2 1.7 343.0 8.7 .0 gg88ksxngbc Module: pmdtm@mbhibisrv01.maxbupa.com (TNS V1-V3) INSERT INTO STG_DLR_CONTAQUE_LOG(WRAPUPDURATION,DLR_USER,UNIQUEID,STATUS,START_E POCH,SETUPDURATION,QUEUE_SECONDS,PROCESSED,PHONE_NUMBER,PHONE_CODE,LIST_ID,LENGT H_IN_SEC,LEAD_ID,IDLEDURATION,GROUP_ID,FILENAME,END_EPOCH,DIGIT_CAPTURE,COMMENTS ,CAMPAIGN_ID,CALLERID,CALL_DATE,STG_REFRESH_DATE) VALUES ( :1, :2, :3, :4, :5, 1,656,300 14,935 110.9 1.5 9.2 98.5 .0 cvhk2j2gymz Module: DBMS_SCHEDULER SELECT SU.NAME, SO.NAME, A.STATSTYPE#, C.INTCOL# FROM ASSOCIATION$ A, OBJ$ O, US ER$ U, COL$ C, OBJ$ SO, USER$ SU, COLTYPE$ CT, OBJ$ TY WHERE O.OWNER#=U.USER# AN D A.OBJ#=TY.OBJ# AND O.OBJ#=C.OBJ# AND C.INTCOL#=CT.INTCOL# AND O.OBJ#=CT.OBJ# A ND CT.TOID=TY.OID$ AND A.STATSTYPE#=SO.OBJ# AND SO.OWNER#=SU.USER# AND O.TYPE#=2 1,288,567 10,717 120.2 1.1 4.9 92.3 .3 6qg99cfg26k Module: DBMS_SCHEDULER SELECT COUNT(UNQ) UNQ, COUNT(PFX) PFX FROM (SELECT /*+ first_rows(1) leading(cc) */ CD.TYPE# UNQ, NULL PFX FROM SYS.CCOL$ CC, SYS.CDEF$ CD WHERE CC.OBJ# = :B2 A ND CC.INTCOL# = :B1 AND CD.CON# = CC.CON# AND CD.OBJ# = CC.OBJ# AND CD.ENABLED I S NOT NULL AND CD.INTCOLS = 1 AND CD.TYPE# IN (2,3) AND BITAND(CD.DEFER, 2+4) = 1,013,152 1 1,013,152.0 0.9 41.0 13.8 31.8 572fbaj0fdw Module: sqlplus@mbhibisrv01.maxbupa.com (TNS V1-V3) select output from table(dbms_workload_repository.awr_report_html( :dbid, :inst_num, :bid, :eid, :rpt_options )) 411,115 30,372 13.5 0.4 484.3 3.7 .2 g7mt7ptq286 insert into seg$ (file#,block#,type#,ts#,blocks,extents,minexts,maxexts,extsize, extpct,user#,iniexts,lists,groups,cachehint,hwmincr, spare1, scanhint, bitmapran ges) values (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,DECODE(:17,0 ,NULL,:17),:18,:19) 316,794 21,608 14.7 0.3 5.2 21.7 77.9 djupx9h0ggd Module: JDBC Thin Client SELECT /*+ rule */ OI.NAME FROM SYS.USER$ U, SYS.OBJ$ OT, SYS.COL$ C, SYS.ICOL$ IC, SYS.OBJ$ OI WHERE U.NAME = :B3 AND OT.OWNER# = U.USER# AND OT.NAME = :B2 AND OT.TYPE# = 2 AND OT.OBJ# = C.OBJ# AND C.NAME = :B1 AND OT.OBJ# = IC.BO# AND IC. INTCOL# = C.INTCOL# AND IC.OBJ# = OI.OBJ# 313,322 64,175 4.9 0.3 9.1 54.4 4.5 bsa0wjtftg3 select file# from file$ where ts#=:1 256,946 2 128,473.0 0.2 2.0 93.8 6.3 2pua8japxkc Module: JDBC Thin Client SQL ordered by Gets DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 -> Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code. -> %Total - Buffer Gets as a percentage of Total Buffer Gets -> %CPU - CPU Time as a percentage of Elapsed Time -> %IO - User I/O Time as a percentage of Elapsed Time -> Total Buffer Gets: 112,321,756 -> Captured SQL account for 37.1% of Total Buffer Gets Elapsed Gets Executions per Exec %Total Time (s) %CPU %IO SQL Id ----------- ----------- ------------ ------ ---------- ------ ------ ----------- BEGIN DBMS_STATS.GATHER_TABLE_STATS(ownname => 'DWHFIN', tabname => 'W_POSITION_ DH', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL IND EXED COLUMNS SIZE AUTO',cascade => false, degree => DBMS_STATS.DEFAULT_DEGREE) ; END; ------------------------------------------------------------- SQL ordered by Reads DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 -> %Total - Physical Reads as a percentage of Total Disk Reads -> %CPU - CPU Time as a percentage of Elapsed Time -> %IO - User I/O Time as a percentage of Elapsed Time -> Total Disk Reads: 6,313,817 -> Captured SQL account for 2.6% of Total Physical Reads Elapsed Reads Executions per Exec %Total Time (s) %CPU %IO SQL Id ----------- ----------- ---------- ------ ---------- ------ ------ ------------- 27,577 1 27,577.0 0.4 13.7 108.3 .7 07favwdwwxqpd Module: pmdtm@mbhibisrv01.maxbupa.com (TNS V1-V3) SELECT /*+ USE_HASH (X1 X2 X3 X4 X5) */ W_GL_OTHER_FS.GL_ACCOUNT_ID , ACCT.ROW _WID GL_ACCOUNT_WID , ACCT.COST_CENTER_NUM COST_CENTER_NUM , ACCT.COST_CENTER_AT TRIB COST_CENTER_ATTRIB , ACCT.PROFIT_CENTER_NUM PROFIT_CENTER_NUM , ACCT.PROFIT _CENTER_ATTRIB PROFIT_CENTER_ATTRIB , BUDGT.ROW_WID BUDGT_ORG_WID , W_GL_OTHER_F 27,438 3 9,146.0 0.4 0.6 102.8 14.2 4rjfwz9uk4m5c Module: SQL Developer select dd.DATE_KEY, ch.channel, ch. Sub_channel, br.branch_name, a.Plan_type_desc,a.GWP,a. LIVES,a.POLICIES /*sum(a.GWP), sum(a.LIVES), sum(a.POLICIES)*/ from dwhods.s_d_view1 a, c 15,838 1 15,838.0 0.3 161.0 2.1 97.3 33br0z8mg0rv4 Module: JDBC Thin Client /* SQL Analyze(0) */ select /*+ full(t) parallel(t,64) parallel_index(t,64) dbms _stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring no_substrb_pad */to_char(count("SOURCE_DISTRIBUTION_ID")),to_char(substrb(dump( min("SOURCE_DISTRIBUTION_ID"),16,0,32),1,120)),to_char(substrb(dump(max("SOURCE_ 14,708 1 14,708.0 0.2 111.5 99.3 .4 17fs5z9cdhx7y Module: TOAD 9.5.0.31 SELECT TO_CHAR(SE.SETTLE_DATE,'MM'),TO_CHAR(SE.SETTLE_DATE,'MON'),COUNT(mbrshp_n o) FROM STG_MXMS_BF_TXN txn, STG_MXMS_BF_SETTLEMENT SE,STG_MXMS_MR_CONTRACT C,S TG_MXMS_MR_MEMBER M WHERE M.CONT_NO=C.CONT_NO AND TXN.DOC_TYPE='IV' AND TXN.D OC_NO=SE.SETTLED_DOC_NO AND TXN.CONT_NO=C.CONT_NO AND TXN.CONT_YYMM = C.CONT_YY 14,056 1 14,056.0 0.2 41.0 .7 99.2 4hurwnx49uf2w Module: pmdtm@mbhibisrv01.maxbupa.com (TNS V1-V3) SELECT TARGET_TABLE.CHANGED_ON_DT AS CHANGED_ON_DT, TARGET_TABLE.AUX1_CHANGED_ ON_DT AS AUX1_CHANGED_ON_DT, TARGET_TABLE.AUX2_CHANGED_ON_DT AS AUX2_CHANGED_ON _DT, TARGET_TABLE.AUX3_CHANGED_ON_DT AS AUX3_CHANGED_ON_DT, TARGET_TABLE.AUX4_ CHANGED_ON_DT AS AUX4_CHANGED_ON_DT, TARGET_TABLE.DELETE_FLG AS DELETE_FLG, TA 12,613 1 12,613.0 0.2 83.0 99.9 .1 ckft225kcb294 Module: TOAD 9.5.0.31 SELECT TO_CHAR(SE.SETTLE_DATE,'MM'),TO_CHAR(SE.SETTLE_DATE,'MON'),SUM (TXN.AMT)/ 100000 AS "GWP " FROM STG_MXMS_BF_TXN txn,STG_MXMS_BF_SETTLEMENT SE,STG_MXMS_MR_ CONTRACT C,STG_MXMS_BF_TXN_DTL BT WHERE TXN.DOC_TYPE='IV' AND TXN.DOC_NO=SE.SETT LED_DOC_NO AND TXN.CONT_NO=C.CONT_NO AND TXN.CONT_YYMM = C.CONT_YYMM AND TXN.DO 10,336 120 86.1 0.2 54.3 7.6 63.4 6gvch1xu9ca3g DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN : = FALSE; BEGIN EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS(); :mydate := next_date ; IF broken THEN :b := 1; ELSE :b := 0; END IF; END; 6,647 5 1,329.4 0.1 0.8 99.7 3.4 f3khxzf7pb4ac Module: TOAD 9.5.0.31 select tabs.table_name, 'DWHSTG', tabs.cluster_name , partitioned, iot_t ype , TEMPORARY, table_type, table_type_owner ,tablespace_name , NESTED , last_analyzed , Dropped , decode(nvl(b uffer_pool, 'x') || upper(partitioned) || nvl(iot_type, 'x'), 'xNOx', 'YES', 'NO 6,228 0 N/A 0.1 92.2 2.1 .2 cxbzcr3fmv70g Module: JDBC Thin Client CREATE BITMAP INDEX W_GL_BALANCE_F_F1 ON W_GL_BALANCE_F ( BALANCE_DT_WID A SC ) NOLOGGING PARALLEL 4,224 101 41.8 0.1 15.5 7.6 38.7 3am9cfkvx7gq1 SQL ordered by Reads DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 -> %Total - Physical Reads as a percentage of Total Disk Reads -> %CPU - CPU Time as a percentage of Elapsed Time -> %IO - User I/O Time as a percentage of Elapsed Time -> Total Disk Reads: 6,313,817 -> Captured SQL account for 2.6% of Total Physical Reads Elapsed Reads Executions per Exec %Total Time (s) %CPU %IO SQL Id ----------- ----------- ---------- ------ ---------- ------ ------ ------------- CALL MGMT_ADMIN_DATA.EVALUATE_MGMT_METRICS(:target_guid,:metric_guid,:metric_val ues) ------------------------------------------------------------- SQL ordered by Physical Reads (UnOptimized)DB/Inst: DEVDWH01/DEVDWH01 Snaps: -> UnOptimized Read Reqs = Physical Read Reqts - Optimized Read Reqs -> %Opt - Optimized Reads as percentage of SQL Read Requests -> %Total - UnOptimized Read Reqs as a percentage of Total UnOptimized Read Reqs -> Total Physical Read Requests: 1,559,286 -> Captured SQL account for 1.8% of Total -> Total UnOptimized Read Requests: 1,559,286 -> Captured SQL account for 1.8% of Total -> Total Optimized Read Requests: 1 -> Captured SQL account for 0.0% of Total UnOptimized Physical UnOptimized Read Reqs Read Reqs Executions Reqs per Exe %Opt %Total SQL Id ----------- ----------- ---------- ------------ ------ ------ ------------- 8,250 8,250 120 68.8 0.0 0.5 6gvch1xu9ca3g DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN : = FALSE; BEGIN EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS(); :mydate := next_date ; IF broken THEN :b := 1; ELSE :b := 0; END IF; END; 2,508 2,508 101 24.8 0.0 0.2 3am9cfkvx7gq1 CALL MGMT_ADMIN_DATA.EVALUATE_MGMT_METRICS(:target_guid,:metric_guid,:metric_val ues) 2,043 2,043 0 N/A 0.0 0.1 cxbzcr3fmv70g Module: JDBC Thin Client CREATE BITMAP INDEX W_GL_BALANCE_F_F1 ON W_GL_BALANCE_F ( BALANCE_DT_WID A SC ) NOLOGGING PARALLEL 1,833 1,833 1 1,833.0 0.0 0.1 07favwdwwxqpd Module: pmdtm@mbhibisrv01.maxbupa.com (TNS V1-V3) SELECT /*+ USE_HASH (X1 X2 X3 X4 X5) */ W_GL_OTHER_FS.GL_ACCOUNT_ID , ACCT.ROW _WID GL_ACCOUNT_WID , ACCT.COST_CENTER_NUM COST_CENTER_NUM , ACCT.COST_CENTER_AT TRIB COST_CENTER_ATTRIB , ACCT.PROFIT_CENTER_NUM PROFIT_CENTER_NUM , ACCT.PROFIT _CENTER_ATTRIB PROFIT_CENTER_ATTRIB , BUDGT.ROW_WID BUDGT_ORG_WID , W_GL_OTHER_F 1,767 1,767 3 589.0 0.0 0.1 4rjfwz9uk4m5c Module: SQL Developer select dd.DATE_KEY, ch.channel, ch. Sub_channel, br.branch_name, a.Plan_type_desc,a.GWP,a. LIVES,a.POLICIES /*sum(a.GWP), sum(a.LIVES), sum(a.POLICIES)*/ from dwhods.s_d_view1 a, c 1,746 1,746 1 1,746.0 0.0 0.1 33br0z8mg0rv4 Module: JDBC Thin Client /* SQL Analyze(0) */ select /*+ full(t) parallel(t,64) parallel_index(t,64) dbms _stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring no_substrb_pad */to_char(count("SOURCE_DISTRIBUTION_ID")),to_char(substrb(dump( min("SOURCE_DISTRIBUTION_ID"),16,0,32),1,120)),to_char(substrb(dump(max("SOURCE_ 1,516 1,516 5 303.2 0.0 0.1 f3khxzf7pb4ac Module: TOAD 9.5.0.31 select tabs.table_name, 'DWHSTG', tabs.cluster_name , partitioned, iot_t ype , TEMPORARY, table_type, table_type_owner ,tablespace_name , NESTED , last_analyzed , Dropped , decode(nvl(b uffer_pool, 'x') || upper(partitioned) || nvl(iot_type, 'x'), 'xNOx', 'YES', 'NO 1,505 1,505 1 1,505.0 0.0 0.1 c6ktchj9m5n60 Module: admintool.exe select distinct owner from ALL_CATALOG order by owner 1,343 1,343 1 1,343.0 0.0 0.1 572fbaj0fdw2b Module: sqlplus@mbhibisrv01.maxbupa.com (TNS V1-V3) select output from table(dbms_workload_repository.awr_report_html( :dbid, :inst_num, :bid, :eid, :rpt_options )) 1,293 1,293 30,372 0.0 0.0 0.1 g7mt7ptq286u7 insert into seg$ (file#,block#,type#,ts#,blocks,extents,minexts,maxexts,extsize, extpct,user#,iniexts,lists,groups,cachehint,hwmincr, spare1, scanhint, bitmapran ges) values (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,DECODE(:17,0 ,NULL,:17),:18,:19) ------------------------------------------------------------- SQL ordered by Executions DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 -> %CPU - CPU Time as a percentage of Elapsed Time -> %IO - User I/O Time as a percentage of Elapsed Time -> Total Executions: 812,141 -> Captured SQL account for 55.8% of Total Elapsed Executions Rows Processed Rows per Exec Time (s) %CPU %IO SQL Id ------------ --------------- -------------- ---------- ------ ------ ----------- 64,175 127,765 2.0 9.1 54.4 4.5 bsa0wjtftg3 select file# from file$ where ts#=:1 48,291 48,291 1.0 23.1 30.7 .3 0kkhhb2w93c update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,extsize=:9,e xtpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535, NULL, :13),groups=decod e(:14, 65535, NULL, :14), cachehint=:15, hwmincr=:16, spare1=DECODE(:17,0,NULL,: 17),scanhint=:18, bitmapranges=:19 where ts#=:1 and file#=:2 and block#=:3 34,060 3,561 0.1 7.0 47.2 27.3 9tgj4g8y4rw select type#,blocks,extents,minexts,maxexts,extsize,extpct,user#,iniexts,NVL(lis ts,65535),NVL(groups,65535),cachehint,hwmincr, NVL(spare1,0),NVL(scanhint,0),NVL (bitmapranges,0) from seg$ where ts#=:1 and file#=:2 and block#=:3 30,372 30,358 1.0 484.3 3.7 .2 g7mt7ptq286 insert into seg$ (file#,block#,type#,ts#,blocks,extents,minexts,maxexts,extsize, extpct,user#,iniexts,lists,groups,cachehint,hwmincr, spare1, scanhint, bitmapran ges) values (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,DECODE(:17,0 ,NULL,:17),:18,:19) 30,354 30,339 1.0 5.4 90.4 2.7 7zx0nu1u7qc delete from seg$ where ts#=:1 and file#=:2 and block#=:3 29,629 31,694 1.1 1.3 64.3 34.1 5n1fs4m2n2y select pos#,intcol#,col#,spare1,bo#,spare2,spare3 from icol$ where obj#=:1 27,465 0 0.0 1.2 71.9 23.7 1mvbtvbqt4z delete from sys.cache_stats_1$ where dataobj# = :1 21,608 1,968 0.1 5.2 21.7 77.9 djupx9h0ggd Module: JDBC Thin Client SELECT /*+ rule */ OI.NAME FROM SYS.USER$ U, SYS.OBJ$ OT, SYS.COL$ C, SYS.ICOL$ IC, SYS.OBJ$ OI WHERE U.NAME = :B3 AND OT.OWNER# = U.USER# AND OT.NAME = :B2 AND OT.TYPE# = 2 AND OT.OBJ# = C.OBJ# AND C.NAME = :B1 AND OT.OBJ# = IC.BO# AND IC. INTCOL# = C.INTCOL# AND IC.OBJ# = OI.OBJ# 15,430 1,542,394 100.0 343.0 8.7 .0 gg88ksxngbc Module: pmdtm@mbhibisrv01.maxbupa.com (TNS V1-V3) INSERT INTO STG_DLR_CONTAQUE_LOG(WRAPUPDURATION,DLR_USER,UNIQUEID,STATUS,START_E POCH,SETUPDURATION,QUEUE_SECONDS,PROCESSED,PHONE_NUMBER,PHONE_CODE,LIST_ID,LENGT H_IN_SEC,LEAD_ID,IDLEDURATION,GROUP_ID,FILENAME,END_EPOCH,DIGIT_CAPTURE,COMMENTS ,CAMPAIGN_ID,CALLERID,CALL_DATE,STG_REFRESH_DATE) VALUES ( :1, :2, :3, :4, :5, 15,292 13,858 0.9 9.4 5.0 95.3 96g93hntrzj select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_ size, minimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, a vgcln from hist_head$ where obj#=:1 and intcol#=:2 14,935 0 0.0 9.2 98.5 .0 cvhk2j2gymz Module: DBMS_SCHEDULER SELECT SU.NAME, SO.NAME, A.STATSTYPE#, C.INTCOL# FROM ASSOCIATION$ A, OBJ$ O, US ER$ U, COL$ C, OBJ$ SO, USER$ SU, COLTYPE$ CT, OBJ$ TY WHERE O.OWNER#=U.USER# AN D A.OBJ#=TY.OBJ# AND O.OBJ#=C.OBJ# AND C.INTCOL#=CT.INTCOL# AND O.OBJ#=CT.OBJ# A ND CT.TOID=TY.OID$ AND A.STATSTYPE#=SO.OBJ# AND SO.OWNER#=SU.USER# AND O.TYPE#=2 12,717 14,179 1.1 1.1 96.4 .1 cm5vu20fhtn select /*+ connect_by_filtering */ privilege#,level from sysauth$ connect by gra ntee#=prior privilege# and privilege#>0 start with grantee#=:1 and privilege#>0 10,717 10,717 1.0 4.9 92.3 .3 6qg99cfg26k SQL ordered by Executions DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 -> %CPU - CPU Time as a percentage of Elapsed Time -> %IO - User I/O Time as a percentage of Elapsed Time -> Total Executions: 812,141 -> Captured SQL account for 55.8% of Total Elapsed Executions Rows Processed Rows per Exec Time (s) %CPU %IO SQL Id ------------ --------------- -------------- ---------- ------ ------ ----------- Module: DBMS_SCHEDULER SELECT COUNT(UNQ) UNQ, COUNT(PFX) PFX FROM (SELECT /*+ first_rows(1) leading(cc) */ CD.TYPE# UNQ, NULL PFX FROM SYS.CCOL$ CC, SYS.CDEF$ CD WHERE CC.OBJ# = :B2 A ND CC.INTCOL# = :B1 AND CD.CON# = CC.CON# AND CD.OBJ# = CC.OBJ# AND CD.ENABLED I S NOT NULL AND CD.INTCOLS = 1 AND CD.TYPE# IN (2,3) AND BITAND(CD.DEFER, 2+4) = 8,889 5,762 0.6 1.0 43.7 43.2 3nkd3g3ju5p select obj#,type#,ctime,mtime,stime, status, dataobj#, flags, oid$, spare1, spar e2 from obj$ where owner#=:1 and name=:2 and namespace=:3 and remoteowner is nul l and linkname is null and subname is null ------------------------------------------------------------- SQL ordered by Parse Calls DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 -> Total Parse Calls: 591,598 -> Captured SQL account for 55.9% of Total % Total Parse Calls Executions Parses SQL Id ------------ ------------ --------- ------------- 63,843 64,175 10.79 bsa0wjtftg3uw select file# from file$ where ts#=:1 48,293 48,291 8.16 0kkhhb2w93cx0 update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,extsize=:9,e xtpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535, NULL, :13),groups=decod e(:14, 65535, NULL, :14), cachehint=:15, hwmincr=:16, spare1=DECODE(:17,0,NULL,: 17),scanhint=:18, bitmapranges=:19 where ts#=:1 and file#=:2 and block#=:3 33,939 34,060 5.74 9tgj4g8y4rwy8 select type#,blocks,extents,minexts,maxexts,extsize,extpct,user#,iniexts,NVL(lis ts,65535),NVL(groups,65535),cachehint,hwmincr, NVL(spare1,0),NVL(scanhint,0),NVL (bitmapranges,0) from seg$ where ts#=:1 and file#=:2 and block#=:3 30,286 30,354 5.12 7zx0nu1u7qccz delete from seg$ where ts#=:1 and file#=:2 and block#=:3 30,260 30,372 5.11 g7mt7ptq286u7 insert into seg$ (file#,block#,type#,ts#,blocks,extents,minexts,maxexts,extsize, extpct,user#,iniexts,lists,groups,cachehint,hwmincr, spare1, scanhint, bitmapran ges) values (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,DECODE(:17,0 ,NULL,:17),:18,:19) 27,465 27,465 4.64 1mvbtvbqt4z4p delete from sys.cache_stats_1$ where dataobj# = :1 12,717 12,717 2.15 cm5vu20fhtnq1 select /*+ connect_by_filtering */ privilege#,level from sysauth$ connect by gra ntee#=prior privilege# and privilege#>0 start with grantee#=:1 and privilege#>0 6,174 6,174 1.04 bcv9qynmu1nv9 Module: pmdtm@mbhibisrv01.maxbupa.com (TNS V1-V3) select sys.dbms_standard.dictionary_obj_type from dual 5,680 5,680 0.96 grwydz59pu6mc select text from view$ where rowid=:1 4,197 4,197 0.71 9y7s2afnkqs9d Module: DBMS_SCHEDULER delete from idl_ub2$ where obj#=:1 and part=:2 ------------------------------------------------------------- SQL ordered by Sharable Memory DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 No data exists for this section of the report. ------------------------------------------------------------- SQL ordered by Version Count DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 -> Only Statements with Version Count greater than 20 are displayed Version Count Executions SQL Id -------- ------------ ------------- 22 1,761 9gkq7rruycsjp select parttype, partcnt, partkeycols, flags, defts#, defpctfree, defpctused, de finitrans, defmaxtrans, deftiniexts, defextsize, defminexts, defmaxexts, defextp ct, deflists, defgroups, deflogging, spare1, mod(spare2, 256) subparttype, mod(t runc(spare2/256), 256) subpartkeycols, mod(trunc(spare2/65536), 65536) defsubpar 21 191 0v3dvmc22qnam insert into sys.col_usage$ (obj#, intcol#, equality_preds, equijoin_preds, noneq uijoin_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), decod e(bitand(:flag,4),0,0,1), decode(bitand(:flag,8),0,0,1), decode(bitand(:flag ------------------------------------------------------------- Instance Activity Stats DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 -> Ordered by statistic name Statistic Total per Second per Trans -------------------------------- ------------------ -------------- ------------- Batched IO (bound) vector count 11,411 1.6 3.3 Batched IO (full) vector count 34,916 4.9 10.1 Batched IO (space) vector count 0 0.0 0.0 Batched IO block miss count 3,240,898 452.2 937.2 Batched IO buffer defrag count 5,110 0.7 1.5 Batched IO double miss count 3,041,004 424.3 879.4 Batched IO same unit count 896,356 125.1 259.2 Batched IO single block count 19,101 2.7 5.5 Batched IO slow jump count 164 0.0 0.1 Batched IO vector block count 2,360,356 329.4 682.6 Batched IO vector read count 27,231 3.8 7.9 Block Cleanout Optim referenced 62 0.0 0.0 CCursor + sql area evicted 394 0.1 0.1 CPU used by this session 267,430 37.3 77.3 CPU used when call started 266,206 37.1 77.0 CR blocks created 12,550 1.8 3.6 Cached Commit SCN referenced 2,839,107 396.2 821.0 Commit SCN cached 146,305 20.4 42.3 DB time 8,246,270 1,150.6 2,384.7 DBWR checkpoint buffers written 16,681 2.3 4.8 DBWR checkpoints 3,195 0.5 0.9 DBWR object drop buffers written 6,024 0.8 1.7 DBWR parallel query checkpoint b 4,281 0.6 1.2 DBWR revisited being-written buf 2,565,874 358.0 742.0 DBWR tablespace checkpoint buffe 1,692 0.2 0.5 DBWR thread checkpoint buffers w 640 0.1 0.2 DBWR transaction table writes 4,700 0.7 1.4 DBWR undo block writes 55,073 7.7 15.9 DDL statements parallelized 1,100 0.2 0.3 DFO trees parallelized 1,153 0.2 0.3 HSC Heap Segment Block Changes 1,767,808 246.7 511.2 HSC IDL Compressed Blocks 0 0.0 0.0 Heap Segment Array Inserts 71,398 10.0 20.7 Heap Segment Array Updates 49,004 6.8 14.2 IMU CR rollbacks 246 0.0 0.1 IMU Flushes 1,280 0.2 0.4 IMU Redo allocation size 2,259,840 315.3 653.5 IMU commits 1,511 0.2 0.4 IMU contention 5 0.0 0.0 IMU ktichg flush 26 0.0 0.0 IMU pool not allocated 50 0.0 0.0 IMU recursive-transaction flush 0 0.0 0.0 IMU undo allocation size 4,373,456 610.2 1,264.7 IMU- failed to get a private str 50 0.0 0.0 LOB table id lookup cache misses 0 0.0 0.0 Number of read IOs issued 558,189 77.9 161.4 PX local messages recv'd 6,267,158 874.5 1,812.4 PX local messages sent 6,267,158 874.5 1,812.4 Parallel operations downgraded 1 726 0.1 0.2 Parallel operations downgraded 2 126 0.0 0.0 Parallel operations downgraded 5 32 0.0 0.0 Parallel operations downgraded 7 5 0.0 0.0 Parallel operations downgraded t 0 0.0 0.0 Parallel operations not downgrad 264 0.0 0.1 RowCR - row contention 0 0.0 0.0 RowCR attempts 7,650 1.1 2.2 RowCR hits 7,649 1.1 2.2 SMON posted for undo segment shr 64 0.0 0.0 SQL*Net roundtrips to/from clien 571,563 79.8 165.3 SQL*Net roundtrips to/from dblin 0 0.0 0.0 Instance Activity Stats DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 -> Ordered by statistic name Statistic Total per Second per Trans -------------------------------- ------------------ -------------- ------------- TBS Extension: bytes extended 104,857,600 14,631.0 30,323.2 TBS Extension: files extended 1 0.0 0.0 TBS Extension: tasks created 1 0.0 0.0 TBS Extension: tasks executed 1 0.0 0.0 active txn count during cleanout 136,106 19.0 39.4 application wait time 223,679 31.2 64.7 auto extends on undo tablespace 0 0.0 0.0 background checkpoints completed 18 0.0 0.0 background checkpoints started 22 0.0 0.0 background timeouts 30,523 4.3 8.8 branch node splits 0 0.0 0.0 buffer is not pinned count 12,483,941 1,741.9 3,610.2 buffer is pinned count 8,217,974 1,146.7 2,376.5 bytes received via SQL*Net from 2,360,922,938 329,424.9 682,742.3 bytes received via SQL*Net from 0 0.0 0.0 bytes sent via SQL*Net to client 702,248,239 97,986.3 203,079.3 bytes sent via SQL*Net to dblink 0 0.0 0.0 calls to get snapshot scn: kcmgs 1,737,214 242.4 502.4 calls to kcmgas 435,412 60.8 125.9 calls to kcmgcs 3,587,036 500.5 1,037.3 cell physical IO interconnect by 61,322,545,152 8,556,472.6 17,733,529.5 change write time 4,233 0.6 1.2 cleanout - number of ktugct call 354,014 49.4 102.4 cleanouts and rollbacks - consis 9,481 1.3 2.7 cleanouts only - consistent read 215,186 30.0 62.2 cluster key scan block gets 2,938,430 410.0 849.8 cluster key scans 850,565 118.7 246.0 commit batch/immediate performed 65,438 9.1 18.9 commit batch/immediate requested 65,438 9.1 18.9 commit cleanout failures: block 1,442 0.2 0.4 commit cleanout failures: buffer 904 0.1 0.3 commit cleanout failures: callba 1,339 0.2 0.4 commit cleanout failures: cannot 10,097 1.4 2.9 commit cleanouts 237,487 33.1 68.7 commit cleanouts successfully co 223,705 31.2 64.7 commit immediate performed 65,438 9.1 18.9 commit immediate requested 65,438 9.1 18.9 commit txn count during cleanout 226,140 31.6 65.4 concurrency wait time 56,735 7.9 16.4 consistent changes 583,635 81.4 168.8 consistent gets 103,672,293 14,465.6 29,980.4 consistent gets - examination 3,872,612 540.4 1,119.9 consistent gets direct 4,359,550 608.3 1,260.7 consistent gets from cache 99,312,743 13,857.3 28,719.7 consistent gets from cache (fast 94,385,638 13,169.8 27,294.9 cursor authentications 2,476 0.4 0.7 data blocks consistent reads - u 16,417 2.3 4.8 db block changes 7,254,903 1,012.3 2,098.0 db block gets 8,649,433 1,206.9 2,501.3 db block gets direct 310,794 43.4 89.9 db block gets from cache 8,338,639 1,163.5 2,411.4 db block gets from cache (fastpa 1,743,539 243.3 504.2 deferred (CURRENT) block cleanou 76,183 10.6 22.0 dirty buffers inspected 166,661 23.3 48.2 enqueue conversions 464,439 64.8 134.3 enqueue releases 1,407,447 196.4 407.0 enqueue requests 1,408,727 196.6 407.4 enqueue timeouts 1,265 0.2 0.4 enqueue waits 81,079 11.3 23.5 exchange deadlocks 3,857 0.5 1.1 Instance Activity Stats DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 -> Ordered by statistic name Statistic Total per Second per Trans -------------------------------- ------------------ -------------- ------------- execute count 812,141 113.3 234.9 failed probes on index block rec 0 0.0 0.0 file io service time 279,505 39.0 80.8 file io wait time 8,683,454,711 1,211,622.0 2,511,120.5 free buffer inspected 4,408,885 615.2 1,275.0 free buffer requested 2,700,575 376.8 781.0 global undo segment hints helped 0 0.0 0.0 heap block compress 8,479 1.2 2.5 hot buffers moved to head of LRU 174,462 24.3 50.5 immediate (CR) block cleanout ap 224,667 31.4 65.0 immediate (CURRENT) block cleano 25,543 3.6 7.4 index crx upgrade (positioned) 455 0.1 0.1 index crx upgrade (prefetch) 2 0.0 0.0 index fast full scans (full) 575 0.1 0.2 index fast full scans (rowid ran 2,598 0.4 0.8 index fetch by key 2,247,709 313.6 650.0 index scans kdiixs1 4,948,962 690.5 1,431.2 leaf node 90-10 splits 1,479 0.2 0.4 leaf node splits 1,865 0.3 0.5 lob reads 318,261 44.4 92.0 lob writes 213,742 29.8 61.8 lob writes unaligned 213,742 29.8 61.8 logons cumulative 134,343 18.8 38.9 max cf enq hold time 31,450 4.4 9.1 messages received 52,939,163 7,386.7 15,309.2 messages sent 52,939,165 7,386.7 15,309.2 min active SCN optimization appl 154 0.0 0.0 no buffer to keep pinned count 80,309 11.2 23.2 no work - consistent read gets 95,651,945 13,346.5 27,661.1 non-idle wait count 4,688,271 654.2 1,355.8 non-idle wait time 6,902,653 963.1 1,996.1 opened cursors cumulative 755,970 105.5 218.6 parse count (describe) 35 0.0 0.0 parse count (failures) 51 0.0 0.0 parse count (hard) 13,245 1.9 3.8 parse count (total) 591,598 82.6 171.1 parse time cpu 17,164 2.4 5.0 parse time elapsed 22,677 3.2 6.6 physical read IO requests 1,559,286 217.6 450.9 physical read bytes 51,722,788,864 7,216,997.1 14,957,428.8 physical read total IO requests 1,569,174 219.0 453.8 physical read total bytes 51,882,344,448 7,239,260.2 15,003,569.8 physical read total multi block 111,295 15.5 32.2 physical reads 6,313,817 881.0 1,825.9 physical reads cache 1,962,468 273.8 567.5 physical reads cache prefetch 1,109,033 154.8 320.7 physical reads direct 4,351,349 607.2 1,258.3 physical reads direct (lob) 408 0.1 0.1 physical reads direct temporary 58,051 8.1 16.8 physical reads prefetch warmup 0 0.0 0.0 physical write IO requests 180,176 25.1 52.1 physical write bytes 7,005,618,176 977,509.7 2,025,916.2 physical write total IO requests 250,413 34.9 72.4 physical write total bytes 9,440,200,704 1,317,212.4 2,729,959.7 physical write total multi block 21,769 3.0 6.3 physical writes 855,178 119.3 247.3 physical writes direct 370,145 51.7 107.0 physical writes direct (lob) 31 0.0 0.0 physical writes direct temporary 60,288 8.4 17.4 physical writes from cache 485,033 67.7 140.3 Instance Activity Stats DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 -> Ordered by statistic name Statistic Total per Second per Trans -------------------------------- ------------------ -------------- ------------- physical writes non checkpoint 833,247 116.3 241.0 pinned buffers inspected 4,150 0.6 1.2 pinned cursors current 2 0.0 0.0 prefetch clients - default 1 0.0 0.0 prefetch warmup blocks aged out 0 0.0 0.0 prefetched blocks aged out befor 249,254 34.8 72.1 process last non-idle time 7,197 1.0 2.1 queries parallelized 53 0.0 0.0 recursive aborts on index block 0 0.0 0.0 recursive calls 7,162,304 999.4 2,071.2 recursive cpu usage 181,945 25.4 52.6 redo blocks checksummed by FG (e 678,292 94.6 196.2 redo blocks written 3,005,077 419.3 869.0 redo buffer allocation retries 179,694 25.1 52.0 redo entries 4,146,855 578.6 1,199.2 redo k-bytes read total 0 0.0 0.0 redo log space requests 180,022 25.1 52.1 redo log space wait time 3,378 0.5 1.0 redo ordering marks 117,152 16.4 33.9 redo size 1,473,457,872 205,594.9 426,101.2 redo size for direct writes 1,190,920 166.2 344.4 redo subscn max counts 454,028 63.4 131.3 redo synch time 2,313,292 322.8 669.0 redo synch writes 141,141 19.7 40.8 redo wastage 15,826,664 2,208.3 4,576.8 redo write time 255,283 35.6 73.8 redo writes 53,130 7.4 15.4 rollback changes - undo records 456 0.1 0.1 rollbacks only - consistent read 1,800 0.3 0.5 root node splits 0 0.0 0.0 rows fetched via callback 843,720 117.7 244.0 scheduler wait time 0 0.0 0.0 session connect time 0 0.0 0.0 session cursor cache hits 437,202 61.0 126.4 session logical reads 112,321,756 15,672.5 32,481.7 shared hash latch upgrades - no 157,717 22.0 45.6 shared hash latch upgrades - wai 166 0.0 0.1 sorts (disk) 3 0.0 0.0 sorts (memory) 741,921 103.5 214.6 sorts (rows) 50,152,875 6,997.9 14,503.4 sql area evicted 7,941 1.1 2.3 sql area purged 411 0.1 0.1 summed dirty queue length 353,651 49.4 102.3 switch current to new buffer 83,669 11.7 24.2 table fetch by rowid 6,693,523 934.0 1,935.7 table fetch continued row 2,323 0.3 0.7 table scan blocks gotten 88,975,255 12,414.9 25,730.3 table scan rows gotten 772,756,504 107,824.5 223,469.2 table scans (direct read) 408,969 57.1 118.3 table scans (long tables) 591,526 82.5 171.1 table scans (rowid ranges) 528,185 73.7 152.7 table scans (short tables) 181,785 25.4 52.6 temp space allocated (bytes) 0 0.0 0.0 total cf enq hold time 310,750 43.4 89.9 total number of cf enq holders 371 0.1 0.1 total number of times SMON poste 65 0.0 0.0 transaction rollbacks 5 0.0 0.0 transaction tables consistent re 7 0.0 0.0 transaction tables consistent re 468 0.1 0.1 undo change vector size 272,300,484 37,994.7 78,745.1 Instance Activity Stats DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 -> Ordered by statistic name Statistic Total per Second per Trans -------------------------------- ------------------ -------------- ------------- user I/O wait time 701,770 97.9 202.9 user calls 1,410,316 196.8 407.8 user commits 3,455 0.5 1.0 user rollbacks 3 0.0 0.0 workarea executions - onepass 48 0.0 0.0 workarea executions - optimal 1,293,931 180.6 374.2 write clones created in backgrou 7,002 1.0 2.0 write clones created in foregrou 3,904 0.5 1.1 ------------------------------------------------------------- Instance Activity Stats - Absolute ValuesDB/Inst: DEVDWH01/DEVDWH01 Snaps: 9 -> Statistics with absolute values (should not be diffed) Statistic Begin Value End Value -------------------------------- --------------- --------------- session uga memory max 1.821013133E+11 2.581307255E+11 session pga memory 15,563,497,208 15,814,580,848 session pga memory max 17,959,199,744 18,412,627,072 session cursor cache count 947,184 1,076,702 session uga memory 3.982390322E+13 4.043132321E+13 opened cursors current 49 79 workarea memory allocated 17,941 10,297 logons current 30 39 ------------------------------------------------------------- Instance Activity Stats - Thread ActivityDB/Inst: DEVDWH01/DEVDWH01 Snaps: 98 -> Statistics identified by '(derived)' come from sources other than SYSSTAT Statistic Total per Hour -------------------------------- ------------------ --------- log switches (derived) 22 11.05 ------------------------------------------------------------- IOStat by Function summary DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 -> 'Data' columns suffixed with M,G,T,P are in multiples of 1024 other columns suffixed with K,M,G,T,P are in multiples of 1000 -> ordered by (Data Read + Write) desc Reads: Reqs Data Writes: Reqs Data Waits: Avg Function Name Data per sec per sec Data per sec per sec Count Tm(ms) --------------- ------- ------- ------- ------- ------- ------- ------- ------- Direct Reads 32.8G 78.4 4.69260 42M 0.1 .005860 566.1K 0.3 Buffer Cache Re 14.9G 137.7 2.13330 0M 0.0 0M 861.9K 0.6 DBWR 0M 0.0 0M 3.7G 21.1 .527850 37.6K 218.7 Direct Writes 215M 0.4 .029999 2.8G 3.9 .397806 29K 122.8 LGWR 17M 0.2 .002372 1.4G 8.9 .206228 54.8K 47.1 Others 136M 1.2 .018976 899M 1.0 .125439 16.1K 99.1 Streams AQ 2M 0.0 .000279 0M 0.0 0M 318 4.1 TOTAL: 48.1G 217.8 6.87754 8.8G 35.0 1.26318 1565.9K 10.6 ------------------------------------------------------------- IOStat by Filetype summary DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 -> 'Data' columns suffixed with M,G,T,P are in multiples of 1024 other columns suffixed with K,M,G,T,P are in multiples of 1000 -> Small Read and Large Read are average service times, in milliseconds -> Ordered by (Data Read + Write) desc Reads: Reqs Data Writes: Reqs Data Small Large Filetype Name Data per sec per sec Data per sec per sec Read Read --------------- ------- ------- ------- ------- ------- ------- ------- ------- Data File 47.7G 215.3 6.80945 6.1G 24.0 .865099 0.4 0.5 Log File 0M 0.0 0M 2G 8.9 .288971 0.0 N/A Temp File 465M 1.3 .064882 477M 1.1 .066556 0.1 0.0 Control File 150M 1.3 .020929 104M 0.9 .014511 0.2 N/A Other 0M 0.0 0M 0M 0.0 0M 6.5 N/A TOTAL: 48.3G 218.0 6.89526 8.6G 35.0 1.23513 0.4 0.5 ------------------------------------------------------------- IOStat by Function/Filetype summary DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 -> 'Data' columns suffixed with M,G,T,P are in multiples of 1024 other columns suffixed with K,M,G,T,P are in multiples of 1000 -> Ordered by (Data Read + Write) desc for each function Reads: Reqs Data Writes: Reqs Data Waits: Avg Data per sec per sec Data per sec per sec Count Tm(ms) ------- ------- ------- ------- ------- ------- ------- ------- Direct Reads 32.8G 78.4 4.69246 42M 0.1 .005860 0 N/A Direct Reads (Data File) 32.8G 78.4 4.69246 42M 0.1 .005860 0 N/A Buffer Cache Reads 14.9G 137.7 2.13344 0M 0.0 0M 840.1K 0.6 Buffer Cache Reads (Data File) 14.9G 137.7 2.13344 0M 0.0 0M 840.1K 0.6 DBWR 0M 0.0 0M 3.7G 21.1 .527850 35 0.0 DBWR (Data File) 0M 0.0 0M 3.7G 21.1 .527850 0 N/A DBWR (Control File) 0M 0.0 0M 0M 0.0 0M 35 0.0 Direct Writes 215M 0.4 .029999 2.8G 3.9 .397806 0 N/A Direct Writes (Data File) 215M 0.4 .029999 2.8G 3.9 .397806 0 N/A LGWR 17M 0.2 .002372 1.4G 8.9 .206228 1184 2.1 LGWR (Log File) 0M 0.0 0M 1.4G 8.8 .205251 88 27.7 LGWR (Control File) 17M 0.2 .002372 7M 0.1 .000976 1096 0.0 Others 135M 1.2 .018836 299M 0.9 .041720 9107 3.4 Others (Control File) 132M 1.2 .018418 97M 0.9 .013534 8443 0.3 Others (Data File) 3M 0.1 .000418 202M 0.1 .028185 664 43.8 Streams AQ 2M 0.0 .000279 0M 0.0 0M 318 4.1 Streams AQ (Data File) 2M 0.0 .000279 0M 0.0 0M 318 4.1 TOTAL: 48.1G 217.8 6.87740 8.3G 34.9 1.17946 850.7K 0.6 ------------------------------------------------------------- Tablespace IO Stats DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 -> 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) -------------- ------- ------- ------- ------------ -------- ---------- ------- DWHFIN 1,454,776 203 0.2 3.9 132,057 18 49,669 2.2 TEMPSTG 35,718 5 0.0 7.7 35,450 5 0 0.0 SYSTEM 39,717 6 3.6 2.0 4,051 1 106,346 4.1 TEMPFIN 20,236 3 0.2 8.0 17,414 2 205 837.5 UNDOTBS1 8,725 1 0.1 1.0 22,826 3 1,974 0.1 DWHSTG 16,019 2 0.5 17.0 9,163 1 0 0.0 TEMPODS 9,089 1 0.1 1.1 8,585 1 0 0.0 SYSAUX 10,161 1 7.1 1.1 3,617 1 9 1.1 DWHODS 12,400 2 0.5 14.8 0 0 0 0.0 TEMP 5,000 1 0.7 4.7 2,976 0 0 0.0 TBS_DWHDM_TEMP 2,986 0 0.3 26.3 3,617 1 13 991.5 TBS_DWHDM_DATA 763 0 4.9 4.1 15 0 0 0.0 INDXODS 227 0 10.8 1.2 0 0 0 0.0 TBS_DWHDM_INDEX 34 0 15.3 1.7 0 0 0 0.0 INDXFIN 4 0 0.0 1.0 0 0 0 0.0 USERS 4 0 0.0 1.0 0 0 0 0.0 ------------------------------------------------------------- File IO Stats DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 -> ordered by Tablespace, File Tablespace Filename ------------------------ ---------------------------------------------------- Av Av Av Av Buffer Av Buf Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms) -------------- ------- ------- ------- ------------ -------- ---------- ------- DWHFIN /u01/app1/oracle/DEVDWH01/oradata/dwhfin01.dbf 770,243 107 0.2 3.8 66,256 9 19,412 3.4 DWHFIN /u01/app1/oracle/DEVDWH01/oradata/dwhfin02.dbf 684,533 96 0.1 4.0 65,801 9 30,257 1.4 DWHODS /u01/app1/oracle/DEVDWH01/oradata/dwhods01.dbf 6,088 1 0.6 14.7 0 0 0 0.0 DWHODS /u01/app1/oracle/DEVDWH01/oradata/dwhods02.dbf 6,312 1 0.5 15.0 0 0 0 0.0 DWHSTG /u01/app1/oracle/DEVDWH01/oradata/dwhstg01.dbf 8,312 1 0.5 17.0 4,896 1 0 0.0 DWHSTG /u01/app1/oracle/DEVDWH01/oradata/dwhstg02.dbf 7,707 1 0.6 16.9 4,267 1 0 0.0 INDXFIN /u01/app1/oracle/DEVDWH01/temp/indxfin01.dbf 4 0 0.0 1.0 0 0 0 0.0 INDXODS /u01/app1/oracle/DEVDWH01/oradata/indxODS01.dbf 227 0 10.8 1.2 0 0 0 0.0 SYSAUX /u01/app1/oracle/DEVDWH01/oradata/sysaux01.dbf 10,161 1 7.1 1.1 3,617 1 9 1.1 SYSTEM /u01/app1/oracle/DEVDWH01/oradata/system01.dbf 39,717 6 3.6 2.0 4,051 1 106,346 4.1 TBS_DWHDM_DATA /u01/app1/oracle/DEVDWH01/oradata/DWHDM_DATA01.dbf 763 0 4.9 4.1 15 0 0 0.0 TBS_DWHDM_INDEX /u01/app1/oracle/DEVDWH01/oradata/DWHDM_INDEX01.dbf 34 0 15.3 1.7 0 0 0 0.0 TBS_DWHDM_TEMP /u01/app1/oracle/DEVDWH01/oradata/DWHDM_TEMP.dbf 2,986 0 0.3 26.3 3,617 1 13 991.5 TEMP /u01/app1/oracle/DEVDWH01/temp/temp01.dbf 5,000 1 0.7 4.7 2,976 0 0 N/A TEMPFIN /u01/app1/oracle/DEVDWH01/temp/tempfin01.dbf 20,236 3 0.2 8.0 17,414 2 205 837.5 TEMPODS /u01/app1/oracle/DEVDWH01/temp/tempods01.dbf 9,089 1 0.1 1.1 8,585 1 0 N/A TEMPSTG /u01/app1/oracle/DEVDWH01/temp/tempstg01.dbf 35,718 5 0.0 7.7 35,450 5 0 N/A UNDOTBS1 /u01/app1/oracle/DEVDWH01/oradata/undotbs01.dbf 8,725 1 0.1 1.0 22,826 3 1,974 0.1 USERS /u01/app1/oracle/DEVDWH01/oradata/users01.dbf 4 0 0.0 1.0 0 0 0 0.0 ------------------------------------------------------------- Buffer Pool Statistics DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 -> Standard block size Pools D: default, K: keep, R: recycle -> Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32k Free Writ Buffer Number of Pool Buffer Physical Physical Buff Comp Busy P Buffers Hit% Gets Reads Writes Wait Wait Waits --- ---------- ---- ------------ ------------ ----------- ------ ------ -------- D 7,972 98 107,597,362 1,961,951 483,559 1.E+05 755 158,098 ------------------------------------------------------------- Checkpoint Activity DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 -> Total Physical Writes: 855,178 Other Autotune Thread MTTR Log Size Log Ckpt Settings Ckpt Ckpt Writes Writes Writes Writes Writes Writes ----------- ----------- ----------- ----------- ----------- ----------- 0 578 0 0 10,068 62 ------------------------------------------------------------- Instance Recovery Stats DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 -> B: Begin Snapshot, E: End Snapshot Estd Targt Estd Log Ckpt Log Ckpt Opt RAC MTTR MTTR Recovery Actual Target Log Sz Timeout Interval Log Avail (s) (s) Estd IOs RedoBlks RedoBlks RedoBlks RedoBlks RedoBlks Sz(M) Time - ----- ----- -------- -------- -------- -------- -------- -------- ------ ----- B 0 10 523 1312 2112 165888 2112 N/A N/A N/A E 0 46 5024 856062 912384 912384 1164433 N/A N/A N/A ------------------------------------------------------------- MTTR Advisory DB/Inst: DEVDWH01/DEVDWH01 Snap: 985 No data exists for this section of the report. ------------------------------------------------------------- Buffer Pool Advisory DB/Inst: DEVDWH01/DEVDWH01 Snap: 985 No data exists for this section of the report. ------------------------------------------------------------- PGA Aggr Summary DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 -> PGA cache hit % - percentage of W/A (WorkArea) data processed only in-memory PGA Cache Hit % W/A MB Processed Extra W/A MB Read/Written --------------- ------------------ -------------------------- 99.9 963,097 807 ------------------------------------------------------------- Warning: pga_aggregate_target was set too low for current workload, as this value was exceeded during this interval. Use the PGA Advisory view to help identify a different value for pga_aggregate_target. PGA Aggr Target Stats DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 No data exists for this section of the report. ------------------------------------------------------------- PGA Aggr Target Histogram DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 -> Optimal Executions are purely in-memory operations Low High Optimal Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs ------- ------- -------------- -------------- ------------ ------------ 2K 4K 659,265 659,265 0 0 64K 128K 4,770 4,770 0 0 128K 256K 1,918 1,918 0 0 256K 512K 222 222 0 0 512K 1024K 2,043 2,043 0 0 1M 2M 608,788 608,785 3 0 2M 4M 208 201 7 0 4M 8M 86 70 16 0 8M 16M 35 28 7 0 16M 32M 26 16 10 0 32M 64M 12 10 2 0 64M 128M 3 0 3 0 ------------------------------------------------------------- PGA Memory Advisory DB/Inst: DEVDWH01/DEVDWH01 Snap: 985 -> When using Auto Memory Mgmt, minimally choose a pga_aggregate_target value where Estd PGA Overalloc Count is 0 Estd Extra Estd P Estd PGA PGA Target Size W/A MB W/A MB Read/ Cache Overallo Estd Est (MB) Factr Processed Written to Disk Hit % Count Time ---------- ------- ---------------- ---------------- ------ -------- ------- 88 0.1 14,725,219.0 34,745.2 100.0 966 2.2E+07 176 0.3 14,725,219.0 23,228.3 100.0 369 2.2E+07 352 0.5 14,725,219.0 22,354.3 100.0 338 2.2E+07 528 0.8 14,725,219.0 20,119.5 100.0 234 2.2E+07 704 1.0 14,725,219.0 4,727.8 100.0 13 2.2E+07 845 1.2 14,725,219.0 3,581.5 100.0 9 2.2E+07 986 1.4 14,725,219.0 3,570.2 100.0 3 2.2E+07 1,126 1.6 14,725,219.0 3,570.2 100.0 3 2.2E+07 1,267 1.8 14,725,219.0 3,570.2 100.0 3 2.2E+07 1,408 2.0 14,725,219.0 3,570.2 100.0 3 2.2E+07 2,112 3.0 14,725,219.0 3,570.2 100.0 0 2.2E+07 2,816 4.0 14,725,219.0 3,570.2 100.0 0 2.2E+07 4,224 6.0 14,725,219.0 3,570.2 100.0 0 2.2E+07 5,632 8.0 14,725,219.0 3,570.2 100.0 0 2.2E+07 ------------------------------------------------------------- Shared Pool Advisory DB/Inst: DEVDWH01/DEVDWH01 Snap: 985 -> 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 Obj Size(M) Factr (M) Mem Obj (s) Factr (s) Factr Hits (K) -------- ----- -------- ------------ -------- ------ ------- ------ ------------ 288 .8 44 3,543 524,769 1.0 22,122 1.2 12,379 336 .9 92 5,932 527,699 1.0 19,192 1.0 13,627 384 1.0 140 8,403 528,408 1.0 18,483 1.0 13,643 432 1.1 187 9,955 528,760 1.0 18,131 1.0 13,654 480 1.3 234 12,009 529,327 1.0 17,564 1.0 13,667 528 1.4 282 14,728 529,585 1.0 17,306 .9 13,676 576 1.5 330 16,631 529,782 1.0 17,109 .9 13,682 624 1.6 378 18,879 529,865 1.0 17,026 .9 13,685 672 1.8 426 21,518 529,957 1.0 16,934 .9 13,688 720 1.9 474 24,571 530,028 1.0 16,863 .9 13,691 768 2.0 522 26,688 530,104 1.0 16,787 .9 13,694 ------------------------------------------------------------- SGA Target Advisory DB/Inst: DEVDWH01/DEVDWH01 Snap: 985 No data exists for this section of the report. ------------------------------------------------------------- Streams Pool Advisory DB/Inst: DEVDWH01/DEVDWH01 Snap: 985 Size for Size Est Spill Est Spill Est Unspill Est Unspill Est (MB) Factor Count Time (s) Count Time (s) ---------- --------- ----------- ----------- ----------- ----------- 16 0.5 0 0 0 0 32 1.0 0 0 0 0 48 1.5 0 0 0 0 64 2.0 0 0 0 0 80 2.5 0 0 0 0 96 3.0 0 0 0 0 112 3.5 0 0 0 0 128 4.0 0 0 0 0 144 4.5 0 0 0 0 160 5.0 0 0 0 0 176 5.5 0 0 0 0 192 6.0 0 0 0 0 208 6.5 0 0 0 0 224 7.0 0 0 0 0 240 7.5 0 0 0 0 256 8.0 0 0 0 0 272 8.5 0 0 0 0 288 9.0 0 0 0 0 304 9.5 0 0 0 0 320 10.0 0 0 0 0 ------------------------------------------------------------- Java Pool Advisory DB/Inst: DEVDWH01/DEVDWH01 Snap: 985 No data exists for this section of the report. ------------------------------------------------------------- Buffer Wait Statistics DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 -> ordered by wait time desc, waits desc Class Waits Total Wait Time (s) Avg Time (ms) ------------------ ----------- ------------------- -------------- data block 127,498 516 4 file header block 26,226 34 1 segment header 2,262 1 0 undo header 1,489 0 0 undo block 485 0 0 1st level bmb 13 0 0 2nd level bmb 3 0 0 ------------------------------------------------------------- Enqueue Activity DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 -> 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) ------------ ------------ ----------- ----------- ------------ -------------- CR-Reuse Block Range (block range reuse ckpt) 275,881 275,882 0 29,800 10,595 355.55 RO-Multiple Object Reuse (fast object reuse) 27,432 27,432 0 3,029 1,953 644.62 ST-Space Transaction 1,462 1,462 0 262 455 1,735.42 KO-Multiple Object Checkpoint (fast object checkpoint) 1,125 1,125 0 125 275 2,197.20 TX-Transaction (allocate ITL entry) 31,231 31,231 0 30,148 18 .59 CF-Controlfile Transaction 2,955 2,951 4 4 16 3,947.50 TX-Transaction (row lock contention) 17,550 17,550 0 17,408 10 .59 JD-Job Queue Date 1,571 1,571 0 1 2 2,030.00 JS-Job Scheduler (queue lock) 26,107 26,107 0 1 1 820.00 TX-Transaction (index contention) 5 5 0 5 1 100.00 PS-PX Process Reservation 529,848 528,804 864 206 0 1.02 PR-Process Startup 1,282 1,282 0 4 0 12.50 TM-DML 117,877 117,482 395 4 0 12.50 HW-Segment High Water Mark 40,958 40,963 0 3 0 .00 CI-Cross-Instance Call Invocation 2,830 2,830 0 1 0 .00 TX-Transaction 230,776 232,056 0 1 0 .00 ------------------------------------------------------------- Undo Segment Summary DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 -> 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) Concurcy TR (mins) OOS eS/eR/eU ---- ---------- --------------- -------- -------- --------- ----- -------------- 2 39.8 232,275 2,187 185 19.3/47.5 0/0 0/0/0/0/0/0 ------------------------------------------------------------- Undo Segment Stats DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 -> Most recent 35 Undostat rows, ordered by 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 ------------ ----------- ------------ ------- ------- ------- ----- ------------ 10-Oct 12:30 12,329 548 499 4 19 0/0 0/0/0/0/0/0 10-Oct 12:20 343 1,984 1,102 21 29 0/0 0/0/0/0/0/0 10-Oct 12:10 5,682 36,677 501 185 19 0/0 0/0/0/0/0/0 10-Oct 12:00 2,931 20,589 1,105 172 29 0/0 0/0/0/0/0/0 10-Oct 11:50 2,694 22,136 504 173 19 0/0 0/0/0/0/0/0 10-Oct 11:40 1,337 8,709 1,107 150 29 0/0 0/0/0/0/0/0 10-Oct 11:30 4,257 54,204 506 157 19 0/0 0/0/0/0/0/0 10-Oct 11:20 3,294 39,614 1,109 172 29 0/0 0/0/0/0/0/0 10-Oct 11:10 1,924 15,424 2,187 131 47 0/0 0/0/0/0/0/0 10-Oct 11:00 3,223 20,262 1,586 127 37 0/0 0/0/0/0/0/0 10-Oct 10:50 1,419 10,882 985 120 27 0/0 0/0/0/0/0/0 10-Oct 10:40 384 1,246 1,114 22 30 0/0 0/0/0/0/0/0 ------------------------------------------------------------- Latch Activity DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 -> "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 Name Requests Miss /Miss (s) Requests Miss ------------------------ -------------- ------ ------ ------ ------------ ------ AQ deq hash table latch 102 0.0 0 0 N/A AQ dequeue txn counter l 20 0.0 0 0 N/A AQ disk delete txn count 20 0.0 0 0 N/A ASM db client latch 64,566 0.0 0 0 N/A ASM map operation hash t 2 0.0 0 0 N/A ASM network state latch 176 0.0 0 0 N/A AWR Alerted Metric Eleme 63,923 0.0 0 0 N/A Change Notification Hash 3,163 0.0 0 0 N/A Consistent RBA 53,141 0.0 0 0 N/A DML lock allocation 235,802 0.0 0.0 0 0 N/A Event Group Locks 3,211 0.0 0 0 N/A FIB s.o chain latch 26 0.0 0 0 N/A FOB s.o list latch 15,740 3.5 0.8 4 0 N/A File State Object Pool P 2 0.0 0 0 N/A IPC stats buffer allocat 2 0.0 0 0 N/A In memory undo latch 155,436 0.0 0.0 0 4,519 0.0 JS Sh mem access 14 0.0 0 0 N/A JS mem alloc latch 14 0.0 0 0 N/A JS queue access latch 16 0.0 0 0 N/A JS queue state obj latch 52,214 0.0 0 0 N/A JS slv state obj latch 488 0.0 0 0 N/A KFC FX Hash Latch 2 0.0 0 0 N/A KFC Hash Latch 2 0.0 0 0 N/A KFCL LE Freelist 2 0.0 0 0 N/A KGNFS-NFS:SHM structure 2 0.0 0 0 N/A KGNFS-NFS:SVR LIST 2 0.0 0 0 N/A KJC message pool free li 2 0.0 0 0 N/A KJCT flow control latch 2 0.0 0 0 N/A KMG MMAN ready and start 2,400 0.0 0 0 N/A KTF sga latch 43 0.0 0 2,122 0.0 KWQMN job cache list lat 74 0.0 0 0 N/A KWQP Prop Status 4 0.0 0 0 N/A KWQS pqsubs latch 36 0.0 0 0 N/A KWQS pqueue ctx latch 188 0.0 0 0 N/A Locator state objects po 2 0.0 0 0 N/A Lsod array latch 2 0.0 0 0 N/A MQL Tracking Latch 0 N/A 0 144 0.0 Memory Management Latch 2 0.0 0 2,400 0.0 Memory Queue 2 0.0 0 0 N/A Memory Queue Message Sub 2 0.0 0 0 N/A Memory Queue Message Sub 2 0.0 0 0 N/A Memory Queue Message Sub 2 0.0 0 0 N/A Memory Queue Message Sub 2 0.0 0 0 N/A Memory Queue Subscriber 2 0.0 0 0 N/A MinActiveScn Latch 40 0.0 0 0 N/A Mutex 2 0.0 0 0 N/A Mutex Stats 2 0.0 0 0 N/A OS process 17,584 0.0 0 0 N/A OS process allocation 13,939 0.3 0.0 0 0 N/A OS process: request allo 6,411 0.1 0.0 0 0 N/A PL/SQL warning settings 150,978 0.2 0.0 0 0 N/A PX hash array latch 2 0.0 0 0 N/A QMT 2 0.0 0 0 N/A Real-time plan statistic 156,474 23.4 0.3 23 1 0.0 Reserved Space Latch 3 0.0 0 0 N/A SGA IO buffer pool latch 1,383 0.0 0 1,784 0.0 SGA blob parent 2 0.0 0 0 N/A SGA bucket locks 2 0.0 0 0 N/A SGA heap locks 2 0.0 0 0 N/A SGA pool locks 2 0.0 0 0 N/A Latch Activity DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 -> "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 Name Requests Miss /Miss (s) Requests Miss ------------------------ -------------- ------ ------ ------ ------------ ------ SQL memory manager latch 121,535 63.1 0.8 148 2,162 0.2 SQL memory manager worka 5,077,410 0.3 0.0 0 0 N/A Shared B-Tree 763 0.0 0 0 N/A Streams Generic 2 0.0 0 0 N/A Testing 2 0.0 0 0 N/A Token Manager 2 0.0 0 0 N/A WCR: sync 2 0.0 0 0 N/A Write State Object Pool 2 0.0 0 0 N/A X$KSFQP 2 0.0 0 0 N/A XDB NFS Security Latch 2 0.0 0 0 N/A XDB unused session pool 2 0.0 0 0 N/A XDB used session pool 2 0.0 0 0 N/A active checkpoint queue 53,021,342 0.0 0.0 0 1 0.0 active service list 689,497 9.5 0.0 0 5,564 0.0 begin backup scn array 21,166 0.0 0 0 N/A buffer pool 2 0.0 0 0 N/A business card 2 0.0 0 0 N/A cache buffer handles 240,594 0.0 0.0 0 0 N/A cache buffers chains 238,642,959 0.3 0.0 3 12,178,727 0.0 cache buffers lru chain 486,335,455 0.0 0.0 0 12,090,176 3.2 cache table scan latch 145,363 2.7 0.0 0 145,363 3.2 call allocation 827,658 17.1 0.2 57 0 N/A cas latch 2 0.0 0 0 N/A change notification clie 2 0.0 0 0 N/A channel handle pool latc 7,295 0.2 0.8 0 0 N/A channel operations paren 461,227 0.2 0.0 0 0 N/A checkpoint queue latch 1,762,426,088 0.0 0.0 0 486,436 0.1 client/application info 808,157 0.0 0.0 0 0 N/A compile environment latc 135,308 0.2 0.0 0 0 N/A corrupted undo seg latch 1,373 0.0 0 0 N/A cp cmon/server latch 2 0.0 0 0 N/A cp pool latch 2 0.0 0 0 N/A cp server hash latch 2 0.0 0 0 N/A cp sga latch 176 0.0 0 0 N/A cvmap freelist lock 2 0.0 0 0 N/A deferred cleanup latch 176 0.0 0 0 N/A dml lock allocation 119,792 1.6 0.0 0 0 N/A done queue latch 2 0.0 0 0 N/A dummy allocation 270,685 5.4 0.0 0 0 N/A enqueue hash chains 3,415,393 1.7 0.0 0 4,760 0.0 enqueues 1,731,961 3.4 0.0 3 0 N/A error message lists 265,147 10.5 0.0 0 0 N/A fifth spare latch 2 0.0 0 0 N/A file cache latch 71,101 0.5 0.0 0 0 N/A flashback copy 2 0.0 0 0 N/A gc element 2 0.0 0 0 N/A gcs commit scn state 2 0.0 0 0 N/A gcs partitioned table ha 2 0.0 0 0 N/A gcs pcm hashed value buc 2 0.0 0 0 N/A gcs resource freelist 2 0.0 0 0 N/A gcs resource hash 2 0.0 0 0 N/A gcs resource scan list 2 0.0 0 0 N/A gcs shadows freelist 2 0.0 0 0 N/A ges domain table 2 0.0 0 0 N/A ges enqueue table freeli 2 0.0 0 0 N/A ges group table 2 0.0 0 0 N/A ges process hash list 2 0.0 0 0 N/A ges process parent latch 2 0.0 0 0 N/A ges resource hash list 2 0.0 0 0 N/A ges resource scan list 2 0.0 0 0 N/A Latch Activity DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 -> "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 Name Requests Miss /Miss (s) Requests Miss ------------------------ -------------- ------ ------ ------ ------------ ------ ges resource table freel 2 0.0 0 0 N/A ges value block free lis 2 0.0 0 0 N/A global KZLD latch for me 1,922 0.0 0 0 N/A global tx hash mapping 2 0.0 0 0 N/A granule operation 2 0.0 0 0 N/A hash table column usage 2,191 0.0 0 8,953,106 0.0 hash table modification 406 0.2 0.0 0 0 N/A heartbeat check 2 0.0 0 0 N/A internal temp table obje 6,008 0.1 0.0 0 0 N/A intra txn parallel recov 2 0.0 0 0 N/A io pool granule metadata 2 0.0 0 0 N/A job workq parent latch 141 0.0 0 139 0.0 job_queue_processes free 291 0.0 0 0 N/A job_queue_processes para 1,701 0.0 0 0 N/A k2q lock allocation 2 0.0 0 0 N/A kcbtsemkid latch 25 0.0 0 0 N/A kdlx hb parent latch 2 0.0 0 0 N/A kgb parent 2 0.0 0 0 N/A kks stats 61,512 0.0 0.0 0 0 N/A kokc descriptor allocati 2,932 0.0 0 0 N/A ksfv messages 2 0.0 0 0 N/A kss move lock 68 0.0 0 0 N/A ksuosstats global area 486 0.0 0 0 N/A ksv allocation latch 308 0.0 0 0 N/A ksv class latch 134 0.0 0 0 N/A ksv msg queue latch 2 0.0 0 0 N/A ksz_so allocation latch 6,411 0.2 0.0 0 0 N/A ktm global data 176 0.0 0 0 N/A kwqbsn:qsga 261 0.0 0 0 N/A lgwr LWN SCN 54,697 0.0 1.0 0 0 N/A list of block allocation 70,266 1.2 0.0 0 0 N/A loader state object free 879,324 3.4 0.0 0 0 N/A lob segment dispenser la 2 0.0 0 0 N/A lob segment hash table l 26 0.0 0 0 N/A lob segment query latch 2 0.0 0 0 N/A lock DBA buffer during m 2 0.0 0 0 N/A logical standby cache 2 0.0 0 0 N/A logminer context allocat 4 0.0 0 0 N/A logminer work area 2 0.0 0 0 N/A longop free list parent 18 0.0 0 15 0.0 mapped buffers lru chain 2 0.0 0 0 N/A message pool operations 103,388 0.2 0.0 0 0 N/A messages 106,319,774 0.0 0.0 0 0 N/A mostly latch-free SCN 54,911 0.2 0.4 0 0 N/A msg queue latch 2 0.0 0 0 N/A multiblock read objects 668,138 3.7 0.0 0 0 N/A name-service namespace b 2 0.0 0 0 N/A ncodef allocation latch 176 0.0 0 0 N/A object queue header heap 74,463 0.0 0.3 0 75,193 0.0 object queue header oper 57,148,730 0.0 0.0 0 0 N/A object stats modificatio 23,543 0.6 0.0 0 0 N/A parallel query alloc buf 24,353,418 18.8 0.0 494 0 N/A parallel query stats 135,458 9.4 0.0 0 0 N/A parameter list 18,127 0.0 0.0 0 0 N/A parameter table manageme 270,686 0.5 0.0 0 0 N/A peshm 2 0.0 0 0 N/A pesom_free_list 2 0.0 0 0 N/A pesom_hash_node 2 0.0 0 0 N/A post/wait queue 542,500 9.3 0.0 0 337,800 9.0 process allocation 8,846 0.6 1.0 1 3,203 0.2 Latch Activity DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 -> "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 Name Requests Miss /Miss (s) Requests Miss ------------------------ -------------- ------ ------ ------ ------------ ------ process group creation 6,411 0.6 0.0 0 0 N/A process queue 16,065,550 0.2 0.0 2 0 N/A process queue reference 873,406,646 0.0 0.0 0 11,067,904 0.0 qmn task queue latch 1,576 9.4 0.0 0 0 N/A query server freelists 16,060,041 4.1 0.0 2 0 N/A query server process 1,006 9.0 1.1 2 1,006 0.0 queued dump request 24 0.0 0 0 N/A queuing load statistics 2 0.0 0 0 N/A recovery domain hash lis 2 0.0 0 0 N/A redo allocation 395,430 4.3 0.0 0 4,288,321 2.6 redo copy 2 0.0 0 4,309,396 0.3 redo writing 53,237,946 0.0 0.0 0 0 N/A resmgr group change latc 134,632 0.2 0.0 0 0 N/A resmgr:active threads 270,685 0.0 0 0 N/A resmgr:actses change gro 134,096 0.0 0 0 N/A resmgr:actses change sta 2 0.0 0 0 N/A resmgr:free threads list 270,683 7.6 0.0 0 0 N/A resmgr:plan CPU method 2 0.0 0 0 N/A resmgr:resource group CP 2 0.0 0 0 N/A resmgr:schema config 132,119 8.5 0.0 0 0 N/A resmgr:session queuing 2 0.0 0 0 N/A rm cas latch 2 0.0 0 0 N/A row cache objects 17,379,109 1.7 0.0 1 11,915 0.0 rules engine aggregate s 22 0.0 0 0 N/A rules engine rule set st 244 0.0 0 0 N/A second spare latch 2 0.0 0 0 N/A sequence cache 6,760 0.0 0 0 N/A session allocation 746,629 0.7 0.0 0 248,881 0.1 session idle bit 2,935,809 0.1 0.0 0 0 N/A session queue latch 2 0.0 0 0 N/A session state list latch 267,882 8.7 0.0 0 0 N/A session switching 16,188 0.0 0.0 0 0 N/A session timer 3,485 0.0 0 0 N/A shared pool 5,379,375 27.1 0.0 39 0 N/A shared pool sim alloc 2 0.0 0 0 N/A shared pool simulator 125,320 0.0 0.0 0 0 N/A sim partition latch 2 0.0 0 0 N/A simulator hash latch 520,394 0.0 0 0 N/A simulator lru latch 409,948 0.0 0 520,392 0.0 sort extent pool 17,194 0.0 0 0 N/A space background state o 22 0.0 0 0 N/A space background task la 5,296 0.0 0 4,798 0.0 state object free list 4 0.0 0 0 N/A statistics aggregation 1,120 0.0 0 0 N/A tablespace key chain 2 0.0 0 0 N/A temp lob duration state 246 0.0 0 0 N/A temporary table state ob 56 0.0 0 0 N/A test excl. parent l0 2 0.0 0 0 N/A test excl. parent2 l0 2 0.0 0 0 N/A third spare latch 2 0.0 0 0 N/A threshold alerts latch 474 0.0 0 0 N/A trace latch 2 0.0 0 0 N/A transaction allocation 276,001 0.3 0.1 0 0 N/A transaction branch alloc 3,485 0.0 0 0 N/A undo global data 1,409,595 0.2 0.0 0 6 0.0 virtual circuit buffers 2 0.0 0 0 N/A virtual circuit holder 2 0.0 0 0 N/A virtual circuit queues 2 0.0 0 0 N/A ------------------------------------------------------------- Latch Sleep Breakdown DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 -> ordered by misses desc Get Spin Latch Name Requests Misses Sleeps Gets -------------------------- --------------- ------------ ----------- ----------- parallel query alloc buffe 24,353,418 4,572,959 12,978 4,561,716 shared pool 5,379,375 1,459,997 17,912 1,443,443 query server freelists 16,060,041 655,563 784 654,808 cache buffers chains 238,642,959 653,315 3,407 650,145 row cache objects 17,379,109 299,366 623 298,845 call allocation 827,658 141,329 31,638 115,108 SQL memory manager latch 121,535 76,632 62,216 20,654 active service list 689,497 65,822 308 65,521 enqueues 1,731,961 58,660 504 58,277 enqueue hash chains 3,415,393 57,946 141 57,823 post/wait queue 542,500 50,512 11 50,501 Real-time plan statistics 156,474 36,673 10,794 28,036 cache buffers lru chain 486,335,455 34,099 55 34,044 loader state object freeli 879,324 29,521 28 29,493 error message lists 265,147 27,804 18 27,787 process queue 16,065,550 25,197 533 24,679 multiblock read objects 668,138 24,460 28 24,432 session state list latch 267,882 23,392 5 23,387 process queue reference 873,406,646 20,722 123 16,838 resmgr:free threads list 270,683 20,540 8 20,532 messages 106,319,774 18,543 198 18,345 redo allocation 395,430 17,040 156 16,934 dummy allocation 270,685 14,745 81 14,664 SQL memory manager workare 5,077,410 14,076 28 14,050 parallel query stats 135,458 12,754 48 12,706 active checkpoint queue la 53,021,342 12,652 128 12,525 resmgr:schema config 132,119 11,238 3 11,235 redo writing 53,237,946 8,415 136 8,279 checkpoint queue latch 1,762,426,088 5,242 9 5,233 object queue header operat 57,148,730 4,324 13 4,312 cache table scan latch 145,363 3,944 17 3,934 undo global data 1,409,595 3,355 4 3,351 dml lock allocation 119,792 1,923 19 1,904 list of block allocation 70,266 829 1 828 transaction allocation 276,001 751 41 723 channel operations parent 461,227 705 1 704 FOB s.o list latch 15,740 553 426 256 qmn task queue latch 1,576 148 5 143 query server process 1,006 91 102 0 mostly latch-free SCN 54,911 89 35 54 process allocation 8,846 53 53 21 process group creation 6,411 39 1 38 channel handle pool latch 7,295 11 9 2 object queue header heap 74,463 10 3 7 lgwr LWN SCN 54,697 2 2 0 ------------------------------------------------------------- Latch Miss Sources DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 -> only latches with sleeps are shown -> ordered by name, sleeps desc NoWait Waiter Latch Name Where Misses Sleeps Sleeps ------------------------ -------------------------- ------- ---------- -------- FOB s.o list latch ksfd_allfob 0 426 426 Real-time plan statistic keswxAddNewPlanEntry 0 10,794 10,794 SQL memory manager latch qesmmIQueryRefreshBound 0 62,212 62,216 SQL memory manager latch qesmmIDeamonCb 0 4 0 SQL memory manager worka qesmmIRegisterWorkArea:1 0 17 20 SQL memory manager worka qesmmIUnRegisterWorkArea 0 7 2 SQL memory manager worka qesmmIRegisterWorkArea:2 0 4 1 active checkpoint queue kcbbacq: scan active check 0 118 80 active checkpoint queue kcbkcpa: Checkpoint post-p 0 6 9 active checkpoint queue kcbk_add_rbr_ckpt: Add rbr 0 2 4 active checkpoint queue kcbk_add_rbr_ckpt: Add rbr 0 1 0 active checkpoint queue kcbkcpa: Checkpoint post-p 0 1 35 active service list kswslogon: session logout 0 229 198 active service list kswsgsnp: get service name 0 111 91 active service list kswslogon: session login 0 13 41 active service list kswsigsn: get service name 0 3 22 active service list kswssetsvc: PX session swi 0 3 9 active service list kswsite: service iterator 0 1 0 cache buffers chains kcbgtcr: fast path (cr pin 0 1,430 1,745 cache buffers chains kcbgcur_2 0 494 186 cache buffers chains kcbrls_1 0 492 522 cache buffers chains kcbchg1: kslbegin: bufs no 0 437 205 cache buffers chains kcbgtcr: fast path 0 283 580 cache buffers chains kcbzwb 0 276 66 cache buffers chains kcbgtcr_2 0 133 75 cache buffers chains kcbrls_2 0 84 95 cache buffers chains kcbzibmlt: multi-block rea 0 60 0 cache buffers chains kcbgtcr: kslbegin excl 0 44 25 cache buffers chains kcbbxsv 0 34 35 cache buffers chains kcbget: pin buffer 0 34 20 cache buffers chains kcbnew: new latch again 0 25 0 cache buffers chains kcbchg1: kslbegin: call CR 0 18 254 cache buffers chains kcbzgb: scan from tail. no 0 13 0 cache buffers chains kcbnlc 0 9 16 cache buffers chains kcbzibmlt: finish free buf 0 8 11 cache buffers chains kcbgcur: fast path (shr) 0 7 44 cache buffers chains kcbbic2 0 3 0 cache buffers chains kcbgcur_6 0 2 0 cache buffers chains kcbgtcr: kslbegin shared 0 2 2 cache buffers chains kcbget: exchange 0 1 0 cache buffers chains kcbzib: finish processing 0 1 0 cache buffers lru chain kcbzgws 0 33 0 cache buffers lru chain kcbo_link_q 0 12 0 cache buffers lru chain kcbzar: KSLNBEGIN 0 5 0 cache buffers lru chain kcbbwlru 0 1 36 cache buffers lru chain kcbbxsv: move to being wri 0 1 0 cache buffers lru chain kcbgtcr:CR Scan:KCBRSKIP_1 0 1 0 cache buffers lru chain kcbkcxe 0 1 12 cache buffers lru chain kcbzgb 0 1 1 cache table scan latch kcbz_free_table_scan_read_ 0 9 17 cache table scan latch kcbz_find_table_scan_read_ 0 8 0 call allocation ksuxds 0 30,731 31,293 call allocation ksursi0 0 673 146 call allocation ksuprc 0 78 83 call allocation ksudlp: top call 0 51 35 call allocation kxfpRecvSessStats 0 43 18 call allocation ksusin_int 0 42 49 call allocation ksudlc : rec call 0 20 13 channel handle pool latc ksrchconnect() 0 9 8 channel operations paren ksrapublish() 0 1 1 Latch Miss Sources DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 -> only latches with sleeps are shown -> ordered by name, sleeps desc NoWait Waiter Latch Name Where Misses Sleeps Sleeps ------------------------ -------------------------- ------- ---------- -------- channel operations paren ksrmfree() 0 1 0 checkpoint queue latch kcbkubc_4 0 4 1 checkpoint queue latch kcbbwthc: thread checkpoin 0 3 3 checkpoint queue latch kcbbwdl: thread checkpoint 0 1 1 checkpoint queue latch kcbklbc_cql 0 1 0 dml lock allocation ktaiat 0 16 16 dml lock allocation ktaidt 0 3 3 dummy allocation ktcdod 0 60 60 dummy allocation ktcscnfy 0 21 21 enqueue hash chains ksqgtl3 0 63 61 enqueue hash chains ksqrcl 0 58 57 enqueue hash chains ksqcmi: get hash chain lat 0 19 20 enqueue hash chains ksqcnl 0 1 2 enqueues ksqgel: create enqueue 0 303 335 enqueues ksqdel 0 171 169 enqueues ksqgtl2 0 30 0 error message lists kxfpqsnd 0 11 15 error message lists kxfpqidqr: KSLBEGIN 0 7 3 lgwr LWN SCN kcs022 0 1 0 list of block allocation ktlabl 0 1 1 loader state object free kcblgs 0 16 17 loader state object free kcblfs 0 12 11 messages ksarcv 0 128 59 messages ksaamb: after wakeup 0 53 27 messages ksarcv: after wait 0 17 112 mostly latch-free SCN kcs04 0 34 37 mostly latch-free SCN kcs05 0 2 0 multiblock read objects kcbzibmlt: normal mbr free 0 15 8 multiblock read objects kcbzibmlt 0 13 20 object queue header heap kcbo_link_q:alloc 0 3 3 object queue header oper kcbo_switch_q_bg 0 3 1 object queue header oper kcbo_switch_cq 0 2 2 object queue header oper kcbo_switch_mq_bg 0 2 1 object queue header oper kcbo_unlink_q 0 2 2 object queue header oper kcbo_write_q 0 2 0 object queue header oper kcbo_service_ockpt 0 1 1 object queue header oper kcbo_unlink_q_bg 0 1 1 parallel query alloc buf kxfpbalo 0 12,651 12,646 parallel query alloc buf kxfpbfre 0 320 324 parallel query stats kxfprst: KSLBEGIN:1 0 48 48 post/wait queue ksliwat:remove 0 5 10 post/wait queue ksliwat:add:nowait 0 4 0 post/wait queue ksliwat:add:wait 0 1 1 post/wait queue kslpstevent:get 0 1 0 process allocation ksucrp:1 0 35 0 process allocation ksuapc 0 16 51 process allocation ksudlp 0 2 2 process group creation ksupgprem 0 1 1 process queue kxfpqsnd: KSLBEGIN 0 318 499 process queue kxfpqidqr: KSLBEGIN 0 215 34 process queue reference kxfpqrsnd 0 100 0 process queue reference kxfpnuldq 0 1 19 qmn task queue latch kwqmnmvtsks: delay to read 0 5 0 query server freelists kxfpobadf 0 508 506 query server freelists kxfpobrmf 0 276 278 query server process kxfpg1srv 0 102 102 redo allocation kcrfw_redo_gen: redo alloc 0 96 0 redo allocation kcrfw_redo_gen: redo alloc 0 51 153 redo allocation kcrfw_redo_gen: redo alloc 0 9 0 redo writing kcrfwcr 0 103 73 Latch Miss Sources DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 -> only latches with sleeps are shown -> ordered by name, sleeps desc NoWait Waiter Latch Name Where Misses Sleeps Sleeps ------------------------ -------------------------- ------- ---------- -------- redo writing kcrfw_post: rba scn pair 0 30 34 redo writing kcrfw_cal_target_rba 0 3 27 resmgr:free threads list kskthrcreate 0 8 8 resmgr:schema config kgskgetclassnamebyid 0 3 3 row cache objects kqreqd: reget 0 258 30 row cache objects kqrpre: find obj 0 202 412 row cache objects kqreqd 0 100 71 row cache objects kqrcmt: while loop 0 30 40 row cache objects kqrpup 0 10 2 row cache objects kqrso 0 9 0 row cache objects kqrcmt: clear flag 0 7 46 row cache objects kqrpre: init complete 0 6 22 row cache objects kqrpdl 0 1 0 session state list latch kpseqd 0 3 0 session state list latch kpseqa 0 2 5 shared pool kghalo 0 15,082 15,078 shared pool kghfre 0 2,698 2,682 shared pool kghalp 0 67 42 shared pool kghupr1 0 51 99 shared pool kgh_heap_sizes 0 6 8 shared pool kghasp 0 4 3 shared pool kghfrh 0 3 0 shared pool kghfru 0 1 0 transaction allocation ktcxbr 0 35 33 transaction allocation ktcdod 0 3 3 transaction allocation ktcdso 0 2 7 transaction allocation ktcccDeleteCommitCallbacks 0 1 0 undo global data ktudba: KSLBEGIN 0 4 4 ------------------------------------------------------------- Mutex Sleep Summary DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 -> ordered by number of sleeps desc Wait Mutex Type Location Sleeps Time (ms) --------------------- -------------------------------- ------------ ------------ Library Cache kgllkdl1 85 16,540 0 Library Cache kglhdgn2 106 12,586 0 Library Cache kgllkc1 57 8,723 0 Library Cache kglpndl1 95 1,155 1 Library Cache kglhdgn1 62 1,128 11 Cursor Pin kkslce [KKSCHLPIN2] 837 -11 Library Cache kglget2 2 815 1 Library Cache kglpnal1 90 602 1 Library Cache kglpin1 4 570 0 hash table kkscsSearchChildList [KKSHBKLOC2 274 0 Library Cache kglhdgh1 64 128 0 Cursor Pin kksfbc [KKSCHLFSP2] 35 0 Library Cache kgldtin1 42 11 0 Cursor Pin kksLockDelete [KKSCHLPIN6] 10 0 Library Cache kglIsOwnerVersionable 121 4 0 Library Cache kglllal3 111 3 -0 Cursor Pin kksfbc [KKSCHLPIN1] 3 0 Library Cache kglini1 32 2 -0 Library Cache kglllal1 109 1 0 Library Cache kgllldl2 112 1 -1 Library Cache kglobld1 75 1 0 Library Cache kglobpn1 71 1 -0 Library Cache kglrfcl1 79 1 0 ------------------------------------------------------------- Parent Latch Statistics DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 No data exists for this section of the report. ------------------------------------------------------------- Child Latch Statistics DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 No data exists for this section of the report. ------------------------------------------------------------- Segments by Logical Reads DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 -> Total Logical Reads: 112,321,756 -> Captured Segments account for 93.9% of Total Tablespace Subobject Obj. Logical Owner Name Object Name Name Type Reads %Total ---------- ---------- -------------------- ---------- ----- ------------ ------- DWHSTG DWHSTG STG_PVTL_CCS_AGENT_D TABLE 80,764,640 71.90 SYS SYSTEM TAB$ TABLE 2,219,296 1.98 DWHSTG DWHSTG STG_DLR_CONTAQUE_LOG TABLE 1,762,672 1.57 DWHFIN DWHFIN W_PARTY_ORG_D TABLE 1,594,224 1.42 DWHFIN DWHFIN W_AR_XACT_F TABLE 1,205,840 1.07 ------------------------------------------------------------- Segments by Physical Reads DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 -> Total Physical Reads: 6,313,817 -> Captured Segments account for 91.3% of Total Tablespace Subobject Obj. Physical Owner Name Object Name Name Type Reads %Total ---------- ---------- -------------------- ---------- ----- ------------ ------- DWHFIN DWHFIN W_PARTY_ORG_D TABLE 1,205,501 19.09 DWHFIN DWHFIN W_AR_XACT_F TABLE 793,237 12.56 DWHFIN DWHFIN W_AP_XACT_F TABLE 757,650 12.00 DWHFIN DWHFIN W_GL_OTHER_F TABLE 609,116 9.65 DWHFIN DWHFIN W_PARTY_PER_D TABLE 377,049 5.97 ------------------------------------------------------------- Segments by Physical Read Requests DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 -> Total Physical Read Requests: 1,559,286 -> Captured Segments account for 92.3% of Total Tablespace Subobject Obj. Phys Read Owner Name Object Name Name Type Requests %Total ---------- ---------- -------------------- ---------- ----- ------------ ------- DWHFIN DWHFIN W_PARTY_ORG_D TABLE 315,980 20.26 DWHFIN DWHFIN W_AR_XACT_F TABLE 214,741 13.77 DWHFIN DWHFIN W_PARTY_PER_D TABLE 183,335 11.76 DWHFIN DWHFIN W_AP_XACT_F TABLE 140,299 9.00 DWHFIN DWHFIN W_GL_REVN_F TABLE 113,705 7.29 ------------------------------------------------------------- Segments by UnOptimized Reads DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 -> Total UnOptimized Read Requests: 1,559,286 -> Captured Segments account for 92.3% of Total Tablespace Subobject Obj. UnOptimized Owner Name Object Name Name Type Reads %Total ---------- ---------- -------------------- ---------- ----- ------------ ------- DWHFIN DWHFIN W_PARTY_ORG_D TABLE 315,980 20.26 DWHFIN DWHFIN W_AR_XACT_F TABLE 214,741 13.77 DWHFIN DWHFIN W_PARTY_PER_D TABLE 183,335 11.76 DWHFIN DWHFIN W_AP_XACT_F TABLE 140,299 9.00 DWHFIN DWHFIN W_GL_REVN_F TABLE 113,705 7.29 ------------------------------------------------------------- Segments by Optimized Reads DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 No data exists for this section of the report. ------------------------------------------------------------- Segments by Direct Physical Reads DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 -> Total Direct Physical Reads: 4,351,349 -> Captured Segments account for 94.3% of Total Tablespace Subobject Obj. Direct Owner Name Object Name Name Type Reads %Total ---------- ---------- -------------------- ---------- ----- ------------ ------- DWHFIN DWHFIN W_PARTY_ORG_D TABLE 918,375 21.11 DWHFIN DWHFIN W_AP_XACT_F TABLE 597,768 13.74 DWHFIN DWHFIN W_GL_OTHER_F TABLE 555,843 12.77 DWHFIN DWHFIN W_AR_XACT_F TABLE 517,990 11.90 DWHFIN DWHFIN W_PARTY_PER_D TABLE 286,472 6.58 ------------------------------------------------------------- Segments by Physical Writes DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 -> Total Physical Writes: 855,178 -> Captured Segments account for 48.9% of Total Tablespace Subobject Obj. Physical Owner Name Object Name Name Type Writes %Total ---------- ---------- -------------------- ---------- ----- ------------ ------- DWHFIN DWHFIN W_GL_OTHER_F TABLE 46,254 5.41 DWHSTG DWHSTG STG_DLR_CONTAQUE_LOG TABLE 37,810 4.42 DWHFIN DWHFIN W_GL_OTHER_FS TABLE 35,701 4.17 DWHFIN DWHFIN W_AR_XACT_F TABLE 22,777 2.66 DWHFIN DWHFIN W_ORA_GL_JOURNALS_F_ TABLE 19,952 2.33 ------------------------------------------------------------- Segments by Physical Write Requests DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 -> Total Physical Write Requestss: 180,176 -> Captured Segments account for 44.3% of Total Tablespace Subobject Obj. Phys Write Owner Name Object Name Name Type Requests %Total ---------- ---------- -------------------- ---------- ----- ------------ ------- DWHFIN DWHFIN W_AR_XACT_F TABLE 10,615 5.89 DWHSTG DWHSTG STG_DLR_CONTAQUE_LOG TABLE 9,152 5.08 DWHFIN DWHFIN W_GL_OTHER_F TABLE 7,268 4.03 DWHFIN DWHFIN W_PARTY_ORG_D TABLE 6,175 3.43 DWHFIN DWHFIN W_GL_REVN_F TABLE 5,155 2.86 ------------------------------------------------------------- Segments by Direct Physical Writes DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 -> Total Direct Physical Writes: 370,145 -> Captured Segments account for 75.7% of Total Tablespace Subobject Obj. Direct Owner Name Object Name Name Type Writes %Total ---------- ---------- -------------------- ---------- ----- ------------ ------- DWHFIN DWHFIN W_GL_OTHER_F TABLE 39,749 10.74 DWHFIN DWHFIN W_GL_OTHER_FS TABLE 34,591 9.35 DWHFIN DWHFIN W_ORA_GL_JOURNALS_F_ TABLE 19,119 5.17 DWHFIN DWHFIN W_ORA_GL_OTHER_GL_F_ TABLE 17,059 4.61 DWHFIN DWHFIN W_AP_XACT_FS TABLE 16,571 4.48 ------------------------------------------------------------- Segments by Table Scans DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 -> Total Table Scans: 592,101 -> Captured Segments account for 92.8% of Total Tablespace Subobject Obj. Table Owner Name Object Name Name Type Scans %Total ---------- ---------- -------------------- ---------- ----- ------------ ------- DWHFIN DWHFIN W_PARTY_PER_D TABLE 123,452 20.85 DWHFIN DWHFIN W_PARTY_ORG_D TABLE 80,334 13.57 DWHFIN DWHFIN W_AR_XACT_F TABLE 27,425 4.63 DWHFIN DWHFIN W_PARTY_D TABLE 27,421 4.63 DWHFIN DWHFIN W_AP_XACT_F TABLE 24,150 4.08 ------------------------------------------------------------- Segments by DB Blocks Changes DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 -> % of Capture shows % of DB Block Changes for each top segment compared -> with total DB Block Changes for all segments captured by the Snapshot Tablespace Subobject Obj. DB Block % of Owner Name Object Name Name Type Changes Capture ---------- ---------- -------------------- ---------- ----- ------------ ------- DWHSTG DWHSTG STG_DLR_CONTAQUE_LOG TABLE 1,670,720 59.27 ** UNAVAIL ** UNAVAIL ** UNAVAILABLE ** AILABLE ** UNDEF 284,240 10.08 ** UNAVAIL ** UNAVAIL ** UNAVAILABLE ** AILABLE ** UNDEF 272,096 9.65 SYS SYSTEM SEG$ TABLE 156,160 5.54 DWHFIN DWHFIN W_AR_XACT_F TABLE 87,328 3.10 ------------------------------------------------------------- Segments by Row Lock Waits DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 -> % of Capture shows % of row lock waits for each top segment compared -> with total row lock waits for all segments captured by the Snapshot Row Tablespace Subobject Obj. Lock % of Owner Name Object Name Name Type Waits Capture ---------- ---------- -------------------- ---------- ----- ------------ ------- SYS SYSTEM I_FILE#_BLOCK# INDEX 17,549 99.97 SYS SYSAUX I_WRI$_OPTSTAT_OPR_S INDEX 2 .01 SYS SYSTEM I_H_OBJ#_COL# INDEX 1 .01 SYS SYSTEM I_IND1 INDEX 1 .01 SYS SYSAUX I_WRI$_OPTSTAT_H_OBJ INDEX 1 .01 ------------------------------------------------------------- Segments by ITL Waits DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 -> % of Capture shows % of ITL waits for each top segment compared -> with total ITL waits for all segments captured by the Snapshot Tablespace Subobject Obj. ITL % of Owner Name Object Name Name Type Waits Capture ---------- ---------- -------------------- ---------- ----- ------------ ------- SYS SYSTEM SEG$ TABLE 31,230 100.00 SYS SYSAUX I_WRI$_OPTSTAT_H_OBJ INDEX 1 .00 ------------------------------------------------------------- Segments by Buffer Busy Waits DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 -> % of Capture shows % of Buffer Busy Waits for each top segment compared -> with total Buffer Busy Waits for all segments captured by the Snapshot Buffer Tablespace Subobject Obj. Busy % of Owner Name Object Name Name Type Waits Capture ---------- ---------- -------------------- ---------- ----- ------------ ------- SYS SYSTEM SEG$ TABLE 76,367 71.36 SYS SYSTEM I_FILE#_BLOCK# INDEX 29,503 27.57 DWHFIN DWHFIN W_AR_XACT_GRPACCT_FS TABLE 256 .24 DWHFIN DWHFIN W_AR_XACT_GRPACCT_FS TABLE 166 .16 DWHFIN DWHFIN W_AR_XACT_GRPACCT_FS TABLE 161 .15 ------------------------------------------------------------- Dictionary Cache Stats DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 -> "Pct Misses" should be very low (< 2% in most cases) -> "Final Usage" is the number of cache entries being used Get Pct Scan Pct Mod Final Cache Requests Miss Reqs Miss Reqs Usage ------------------------- ------------ ------ ------- ----- -------- ---------- dc_awr_control 138 0.0 0 N/A 4 1 dc_constraints 147 34.0 0 N/A 147 1 dc_files 28 100.0 0 N/A 0 14 dc_global_oids 3,468 1.9 0 N/A 0 49 dc_histogram_data 354,803 1.0 0 N/A 733 1,957 dc_histogram_defs 371,543 5.8 0 N/A 1,882 4,632 dc_object_grants 4,115 3.4 0 N/A 0 65 dc_objects 1,094,519 0.9 0 N/A 9,336 2,371 dc_profiles 2,069 0.0 0 N/A 0 1 dc_rollback_segments 14,138 0.0 0 N/A 0 172 dc_segments 388,083 8.7 0 N/A 109,088 1,807 dc_sequences 20 65.0 0 N/A 20 1 dc_table_scns 26 100.0 0 N/A 0 0 dc_tablespaces 1,933,239 0.0 0 N/A 0 17 dc_users 1,748,446 0.1 0 N/A 4 234 global database name 6,870 0.0 0 N/A 0 1 outstanding_alerts 147 23.8 0 N/A 22 11 sch_lj_oids 13 38.5 0 N/A 0 4 ------------------------------------------------------------- Library Cache Activity DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 -> "Pct Misses" should be very low Get Pct Pin Pct Invali- Namespace Requests Miss Requests Miss Reloads dations --------------- ------------ ------ -------------- ------ ---------- -------- BODY 30,354 0.1 33,504 0.2 22 0 CLUSTER 19,779 0.0 8,934 0.1 0 0 DBLINK 3,853 0.0 0 N/A 0 0 EDITION 2,086 0.0 4,143 0.0 0 0 INDEX 14,203 14.1 14,047 29.7 782 0 OBJECT ID 1,466 100.0 0 N/A 0 0 QUEUE 228 0.4 546 0.9 2 0 RULESET 22 0.0 22 9.1 2 0 SCHEMA 11,101 0.0 0 N/A 0 0 SQL AREA 345,235 7.5 1,154,419 2.4 2,508 5,362 SUBSCRIPTION 31 9.7 31 9.7 0 0 TABLE/PROCEDURE 129,502 0.9 206,741 4.4 3,401 2 TRIGGER 28,066 0.1 28,145 0.2 1 0 ------------------------------------------------------------- Memory Dynamic Components DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 -> Min/Max sizes since instance startup -> Oper Types/Modes: INItializing,GROw,SHRink,STAtic/IMMediate,DEFerred -> ordered by Component Begin Snap Current Min Max Oper Last Op Component Size (Mb) Size (Mb) Size (Mb) Size (Mb) Count Typ/Mod --------------- ----------- ----------- ----------- ----------- ------ ------- ASM Buffer Cach .00 .00 .00 .00 0 STA/ DEFAULT 16K buf .00 .00 .00 .00 0 STA/ DEFAULT 2K buff .00 .00 .00 .00 0 STA/ DEFAULT 32K buf .00 .00 .00 .00 0 STA/ DEFAULT 4K buff .00 .00 .00 .00 0 STA/ DEFAULT 8K buff .00 .00 .00 .00 0 STA/ DEFAULT buffer 64.00 64.00 64.00 1,072.00 0 SHR/IMM KEEP buffer cac .00 .00 .00 .00 0 STA/ PGA Target 544.00 544.00 544.00 704.00 0 SHR/IMM RECYCLE buffer .00 .00 .00 .00 0 STA/ SGA Target 1,504.00 1,504.00 1,344.00 1,504.00 0 GRO/IMM Shared IO Pool .00 .00 .00 .00 0 STA/ java pool 16.00 16.00 16.00 16.00 0 STA/ large pool 992.00 992.00 48.00 1,056.00 0 GRO/IMM shared pool 384.00 384.00 336.00 384.00 0 GRO/DEF streams pool 32.00 32.00 16.00 32.00 0 GRO/IMM ------------------------------------------------------------- Memory Resize Operations Summary DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 No data exists for this section of the report. ------------------------------------------------------------- Memory Resize Ops DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 No data exists for this section of the report. ------------------------------------------------------------- Process Memory Summary DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 -> B: Begin Snap E: End Snap -> All rows below contain absolute values (i.e. not diffed over the interval) -> Max Alloc is Maximum PGA Allocation size at snapshot time -> Hist Max Alloc is the Historical Max Allocation for still-connected processes -> ordered by Begin/End snapshot, Alloc (MB) desc Hist Avg Std Dev Max Max Alloc Used Alloc Alloc Alloc Alloc Num Num Category (MB) (MB) (MB) (MB) (MB) (MB) Proc Alloc - -------- --------- --------- -------- -------- ------- ------- ------ ------ B Other 56.1 N/A 1.8 2.2 11 11 32 32 Freeable 36.4 .0 2.3 3.0 11 N/A 16 16 SQL 18.9 18.5 1.0 3.7 16 16 19 13 PL/SQL .7 .6 .0 .0 0 0 30 30 E Other 74.9 N/A 1.9 2.2 11 26 40 40 Freeable 22.7 .0 1.0 .9 4 N/A 22 22 SQL 12.2 11.0 .5 1.4 7 62 27 22 PL/SQL 1.1 1.0 .0 .0 0 1 38 38 ------------------------------------------------------------- SGA Memory Summary DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 End Size (Bytes) SGA regions Begin Size (Bytes) (if different) ------------------------------ ------------------- ------------------- Database Buffers 67,108,864 Fixed Size 1,337,548 Redo Buffers 10,858,496 Variable Size 1,962,936,116 ------------------- sum 2,042,241,024 ------------------------------------------------------------- SGA breakdown difference DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 -> ordered by Pool, Name -> N/A value for Begin MB or End MB indicates the size of that Pool/Name was insignificant, or zero in that snapshot Pool Name Begin MB End MB % Diff ------ ------------------------------ -------------- -------------- ------- java free memory 16.0 16.0 0.00 large PX msg pool 41.0 41.0 0.00 large free memory 951.0 951.0 0.00 shared ASH buffers 16.8 16.8 0.00 shared CCUR 11.9 11.5 -2.68 shared FileOpenBlock 15.2 15.2 0.00 shared KCB Table Scan Buffer 4.0 4.0 0.00 shared KGLH0 5.5 5.1 -7.37 shared KGLHD 6.4 5.4 -14.83 shared KGLS 8.6 5.4 -37.48 shared KQR M PO 5.4 N/A -100.00 shared KSFD SGA I/O b 4.0 4.0 0.00 shared KTI-UNDO 15.3 15.3 0.00 shared PCUR 9.9 10.6 6.77 shared PLMCD 5.8 5.6 -4.50 shared PX subheap 9.2 9.2 0.00 shared SQLA 31.6 38.2 20.77 shared VIRTUAL CIRCUITS 5.1 5.1 0.00 shared XDBSC 5.1 5.1 0.00 shared dbktb: trace buffer 21.9 21.9 0.00 shared dbwriter coalesce buffer 8.0 8.0 0.00 shared event statistics per sess 22.8 22.8 0.00 shared free memory 51.1 52.5 2.56 shared ksunfy : SSO free list 22.0 22.0 0.00 shared private strands 15.8 15.8 0.00 stream free memory 32.0 32.0 0.00 buffer_cache 64.0 64.0 0.00 fixed_sga 1.3 1.3 0.00 log_buffer 10.4 10.4 0.00 ------------------------------------------------------------- Streams CPU/IO Usage DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 -> Streams processes ordered by Foreground CPU Time, descending Foreground User IO SYS IO Session Type First Logon CPU time(s) Wait time(s) Wait time(s) -------------------- ------------- ------------ ------------ ------------ QMON Slaves 1004 16:30:34 0.2 1.3 0.0 QMON Coordinator 1004 16:30:24 0.0 0.0 0.0 ------------------------------------------------------------- Streams Capture DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 No data exists for this section of the report. ------------------------------------------------------------- Streams Capture Rate DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 No data exists for this section of the report. ------------------------------------------------------------- Streams Apply DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 No data exists for this section of the report. ------------------------------------------------------------- Streams Apply Rate DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 No data exists for this section of the report. ------------------------------------------------------------- Buffered Queues DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 No data exists for this section of the report. ------------------------------------------------------------- Buffered Queue Subscribers DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 No data exists for this section of the report. ------------------------------------------------------------- Rule Set DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 -> Rule Sets ordered by Evaluations -> * indicates Rule Set activity (re)started between Begin/End snaps -> SQL per Eval - average # of SQL statements executed for non-SQL free evals -> CPU(s),Ela(s) per Eval - avg CPU and Elapsed time per evaluation includes both SQL free and non-SQL free evals SQL Ela(s) CPU(s) No-SQL SQL Eval per per per Evals Eval% Execs CPU(s) Ela(s) /Sec Eval Eval Eval ---------- ------ ------- --------- --------- ------- ------- -------- -------- SYS.ALERT_QUE_R 22 0.0 0 0.1 2.0 0.0 0.0 0.09 0.00 ------------------------------------------------------------- Persistent Queues DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 -> Ordered by Queue Name -> * indicates queue (re)started between Begin/End snaps -> %Exp Msgs - % of msgs enqueued with expiry -> %Delay Msgs - % of msgs enqueued with delay -> %Trasf Time - % of Enqueue time spent in transformation -> %Eval Time - % of Enqueue time spent in rule evaluation Enq Deq %Exp %Delay Enq Deq %Transf %Eval Msgs Msgs Msgs Msgs Time(s) Time(s) Time Time ---------- ---------- ------ ------- -------- -------- --------- ------- SYS.ALERT_QUE(12343) 22 0 100.0 0.0 3.2 0.0 0.0 62.8 SYS.AQ$_ALERT_QT_E(12342) 10 0 0.0 0.0 0.1 0.0 0.0 0.0 SYSMAN.MGMT_TASK_Q(66554) 122 122 0.0 0.0 0.5 0.8 0.0 0.0 ------------------------------------------------------------- Persistent Queues Rate DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 -> Ordered by Queue Name -> * indicates queue (re)started between Begin/End snaps Enqueue Dequeue Avg Enqueue Avg Dequeue Msgs/sec Msgs/sec sec / msg sec / msg ---------- ---------- ------------- ------------- SYS.ALERT_QUE(12343) 0.00 0.00 0.14 N/A SYS.AQ$_ALERT_QT_E(12342) 0.00 0.00 0.01 N/A SYSMAN.MGMT_TASK_Q(66554) 0.02 0.02 0.00 0.01 ------------------------------------------------------------- Persistent Queue Subscribers DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 -> Ordered by Queue Name, Subscriber Name -> * indicates Subscriber activity (re)started between Begin/End snaps Enqueue Dequeue Expire Enqueue Dequeue Expire Msgs Msgs Msgs Msgs/sec Msgs/sec Msgs/sec ---------- ---------- ---------- ---------- ---------- ---------- HAE_SUB(1)/SYS.ALERT_QUE 0 0 0 N/A N/A N/A MBHIBISRV01_M_1831_DEVDWH01(41)/SYS.ALERT_QUE 22 0 10 0.00 0.00 0.00 ------------------------------------------------------------- Resource Limit Stats DB/Inst: DEVDWH01/DEVDWH01 Snap: 985 No data exists for this section of the report. ------------------------------------------------------------- Shared Servers Activity DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 -> Values represent averages for all samples Avg Total Avg Active Avg Total Avg Active Avg Total Avg Active Connections Connections Shared Srvrs Shared Srvrs Dispatchers Dispatchers ------------ ------------ ------------ ------------ ------------ ------------ 0 0 1 0 1 0 ------------------------------------------------------------- Shared Servers Rates DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 Common Disp Common Disp Server Queue Queue Server Server Queue Queue Total Server Per Sec Per Sec Msgs/Sec KB/Sec Total Total Msgs Total(KB) -------- -------- -------- --------- ---------- ---------- ---------- ---------- 0 0 0 0.0 0 0 0 0 ------------------------------------------------------------- Shared Servers Utilization DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 -> Statistics are combined for all servers -> Incoming and Outgoing Net % are included in %Busy Total Server Incoming Outgoing Time (s) %Busy %Idle Net % Net % -------------- -------- -------- --------- --------- 7,243 0.0 100.0 0.0 0.0 ------------------------------------------------------------- Shared Servers Common Queue DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 No data exists for this section of the report. ------------------------------------------------------------- Shared Servers Dispatchers DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 -> Ordered by %Busy, descending -> Total Queued, Total Queue Wait and Avg Queue Wait are for dispatcher queue -> Name suffixes: "(N)" - dispatcher started between begin and end snapshots "(R)" - dispatcher re-started between begin and end snapshots Avg Total Disp Total Total Queue Avg Queue Name Conns Time (s) %Busy %Idle Queued Wait (s) Wait (ms) ------- --------- ---------- -------- -------- ---------- ----------- ---------- D000 0.0 7,243 0.0 100.0 0 0 ------------------------------------------------------------- init.ora Parameters DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 -> if IP/Public/Source at End snap is different a '*' is displayed End value Parameter Name Begin value (if different) ----------------------------- --------------------------------- -------------- audit_file_dest /u01/app1/oracle/DEVDWH01/admin/ audit_trail DB client_result_cache_size 0 compatible 11.2.0.0.0 control_files /u01/app1/oracle/DEVDWH01/ctl/con db_block_size 8192 db_domain maxbupa.com db_name DEVDWH01 diagnostic_dest /u01/app1/oracle/DEVDWH01/admin/ dispatchers (PROTOCOL=TCP) (SERVICE=DEVDWH01X memory_target 2147483648 open_cursors 300 parallel_threads_per_cpu 4 processes 1400 query_rewrite_integrity trusted remote_login_passwordfile EXCLUSIVE sessions 2128 star_transformation_enabled TRUE undo_tablespace UNDOTBS1 ------------------------------------------------------------- Dynamic Remastering Stats DB/Inst: DEVDWH01/DEVDWH01 Snaps: 983-985 No data exists for this section of the report. ------------------------------------------------------------- End of Report