WORKLOAD REPOSITORY report for DB Name DB Id Instance Inst Num Startup Time Release RAC ------------ ----------- ------------ -------- --------------- ----------- --- ZIDW 240507324 zidw 1 26-May-13 18:00 11.2.0.3.0 NO Host Name Platform CPUs Cores Sockets Memory(GB) ---------------- -------------------------------- ---- ----- ------- ---------- zainbi Solaris[tm] OE (64-bit) 24 12 6 64.00 Snap Id Snap Time Sessions Curs/Sess --------- ------------------- -------- --------- Begin Snap: 1333 24-Jun-13 08:00:35 178 3.4 End Snap: 1334 24-Jun-13 09:00:41 151 3.8 Elapsed: 60.11 (mins) DB Time: 1,847.09 (mins) Cache Sizes Begin End ~~~~~~~~~~~ ---------- ---------- Buffer Cache: 22,688M 22,688M Std Block Size: 16K Shared Pool Size: 1,408M 1,408M Log Buffer: 24,616K Load Profile Per Second Per Transaction Per Exec Per Call ~~~~~~~~~~~~ --------------- --------------- ---------- ---------- DB Time(s): 30.7 22.2 0.00 4.04 DB CPU(s): 14.1 10.2 0.00 1.86 Redo size: 5,175,011.0 3,734,945.4 Logical reads: 279,330.7 201,600.5 Block changes: 2,792.9 2,015.7 Physical reads: 9,911.1 7,153.1 Physical writes: 636.6 459.4 User calls: 7.6 5.5 Parses: 2.2 1.6 Hard parses: 0.2 0.1 W/A MB processed: 5.3 3.9 Logons: 0.2 0.2 Executes: 78,205.9 56,443.3 Rollbacks: 0.0 0.0 Transactions: 1.4 Instance Efficiency Percentages (Target 100%) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Buffer Nowait %: 100.00 Redo NoWait %: 99.93 Buffer Hit %: 99.99 In-memory Sort %: 99.99 Library Hit %: 100.75 Soft Parse %: 93.01 Execute to Parse %: 100.00 Latch Hit %: 97.63 Parse CPU to Parse Elapsd %: 11.38 % Non-Parse CPU: 99.99 Shared Pool Statistics Begin End ------ ------ Memory Usage %: 71.69 71.33 % SQL with executions>1: 96.03 91.71 % Memory for SQL w/exec>1: 92.20 87.79 Top 5 Timed Foreground Events ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Avg wait % DB Event Waits Time(s) (ms) time Wait Class ------------------------------ ------------ ----------- ------ ------ ---------- direct path read 314,407 55,205 176 49.8 User I/O DB CPU 50,999 46.0 db file sequential read 101,277 1,725 17 1.6 User I/O cursor: pin S 12,786,682 1,293 0 1.2 Concurrenc enq: KO - fast object checkpoi 22 465 21137 .4 Applicatio Host CPU (CPUs: 24 Cores: 12 Sockets: 6) ~~~~~~~~ Load Average Begin End %User %System %WIO %Idle --------- --------- --------- --------- --------- --------- 18.87 4.28 58.5 3.2 0.0 38.3 Instance CPU ~~~~~~~~~~~~ % of total CPU for Instance: 59.3 % of busy CPU for Instance: 96.0 %DB time waiting for CPU - Resource Mgr: 0.0 Memory Statistics ~~~~~~~~~~~~~~~~~ Begin End Host Mem (MB): 65,536.0 65,536.0 SGA use (MB): 24,576.0 24,576.0 PGA use (MB): 1,493.3 4,302.4 % Host Mem used for SGA+PGA: 39.78 44.06 Time Model Statistics DB/Inst: ZIDW/zidw Snaps: 1333-1334 -> Total time in database user-calls (DB Time): 110825.2s -> 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 110,624.4 99.8 DB CPU 50,998.6 46.0 PL/SQL execution elapsed time 3,528.4 3.2 parse time elapsed 79.3 .1 hard parse elapsed time 75.8 .1 Java execution elapsed time 11.8 .0 repeated bind elapsed time 8.2 .0 connection management call elapsed time 5.3 .0 PL/SQL compilation elapsed time 2.9 .0 hard parse (sharing criteria) elapsed time 0.3 .0 failed parse elapsed time 0.1 .0 hard parse (bind mismatch) elapsed time 0.1 .0 sequence load elapsed time 0.0 .0 DB time 110,825.2 background elapsed time 3,394.9 background cpu time 356.6 ------------------------------------------------------------- Operating System Statistics DB/Inst: ZIDW/zidw Snaps: 1333-1334 -> *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 ------------------------- ---------------------- ---------------- AVG_BUSY_TIME 222,729 AVG_IDLE_TIME 138,038 AVG_SYS_TIME 11,370 AVG_USER_TIME 211,194 BUSY_TIME 5,349,779 IDLE_TIME 3,317,006 SYS_TIME 276,917 USER_TIME 5,072,862 LOAD 19 4 OS_CPU_WAIT_TIME 245,000 RSRC_MGR_CPU_WAIT_TIME 0 VM_IN_BYTES 41,099,264 PHYSICAL_MEMORY_BYTES 68,719,476,736 NUM_CPUS 24 NUM_CPU_CORES 12 NUM_CPU_SOCKETS 6 TCP_RECEIVE_SIZE_DEFAULT 49,152 TCP_RECEIVE_SIZE_MAX 1,048,576 TCP_SEND_SIZE_DEFAULT 49,152 TCP_SEND_SIZE_MAX 1,048,576 ------------------------------------------------------------- Operating System Statistics - Detail DB/Inst: ZIDW/zidw Snaps: 1333-1334 Snap Time Load %busy %user %sys %idle %iowait --------------- -------- -------- -------- -------- -------- -------- 24-Jun 08:00:35 18.9 N/A N/A N/A N/A N/A 24-Jun 09:00:41 4.3 61.7 58.5 3.2 38.3 0.0 ------------------------------------------------------------- Foreground Wait Class DB/Inst: ZIDW/zidw Snaps: 1333-1334 -> 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 99.4% of Total DB time 110,825.19 (s) -> Total FG Wait Time: 59,199.25 (s) DB CPU time: 50,998.55 (s) Avg %Time Total Wait wait Wait Class Waits -outs Time (s) (ms) %DB time -------------------- ---------------- ----- ---------------- -------- --------- User I/O 423,476 0 57,000 135 51.4 DB CPU 50,999 46.0 Concurrency 13,186,423 0 1,319 0 1.2 Application 807 0 470 582 0.4 System I/O 15,113 0 174 12 0.2 Configuration 526 0 85 161 0.1 Commit 1,971 0 77 39 0.1 Other 7,944 61 62 8 0.1 Network 15,952 0 13 1 0.0 ------------------------------------------------------------- Foreground Wait Events DB/Inst: ZIDW/zidw Snaps: 1333-1334 -> 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 -------------------------- ------------ ----- ---------- ------- -------- ------ direct path read 314,407 0 55,205 176 62.9 49.8 db file sequential read 101,277 0 1,725 17 20.3 1.6 cursor: pin S 12,786,682 0 1,293 0 2,558.9 1.2 enq: KO - fast object chec 22 0 465 21137 0.0 .4 control file sequential re 15,113 0 174 12 3.0 .2 log file sync 1,971 0 77 39 0.4 .1 latch free 327 0 58 176 0.1 .1 log file switch completion 366 0 57 156 0.1 .1 db file scattered read 857 0 26 30 0.2 .0 direct path read temp 3,241 0 20 6 0.6 .0 log buffer space 69 0 17 240 0.0 .0 os thread startup 107 0 12 109 0.0 .0 log file switch (private s 86 0 11 130 0.0 .0 direct path write temp 896 0 11 12 0.2 .0 latch: cache buffers chain 397,475 0 10 0 79.5 .0 SQL*Net message from dblin 11 0 6 584 0.0 .0 read by other session 285 0 6 20 0.1 .0 db file parallel read 34 0 5 135 0.0 .0 SQL*Net more data from cli 42 0 4 95 0.0 .0 SQL*Net break/reset to cli 764 0 3 4 0.2 .0 SQL*Net more data to clien 1,518 0 3 2 0.3 .0 library cache: mutex X 1,540 0 3 2 0.3 .0 enq: RO - fast object reus 12 0 2 156 0.0 .0 cursor: pin S wait on X 99 0 1 13 0.0 .0 PX Deq: Slave Session Stat 1,220 0 1 1 0.2 .0 PX Deq: Signal ACK RSG 612 0 1 2 0.1 .0 local write wait 27 0 1 35 0.0 .0 Disk file operations I/O 2,342 0 1 0 0.5 .0 kksfbc child completion 22 77 1 39 0.0 .0 PX qref latch 4,840 100 0 0 1.0 .0 enq: TX - row lock content 9 0 0 40 0.0 .0 direct path write 104 0 0 3 0.0 .0 library cache load lock 3 0 0 112 0.0 .0 CSS initialization 11 0 0 23 0.0 .0 enq: CR - block range reus 2 0 0 50 0.0 .0 CSS operation: action 12 0 0 8 0.0 .0 enq: PS - contention 52 0 0 1 0.0 .0 CSS operation: query 66 0 0 1 0.0 .0 PX Deq: Signal ACK EXT 612 0 0 0 0.1 .0 wait list latch free 5 0 0 10 0.0 .0 SQL*Net message to client 14,369 0 0 0 2.9 .0 enq: TX - index contention 2 0 0 16 0.0 .0 buffer busy waits 300 0 0 0 0.1 .0 library cache lock 1 0 0 19 0.0 .0 latch: parallel query allo 43 0 0 0 0.0 .0 latch: shared pool 121 0 0 0 0.0 .0 reliable message 41 0 0 0 0.0 .0 latch: object queue header 22 0 0 1 0.0 .0 latch: row cache objects 76 0 0 0 0.0 .0 external table read 4 0 0 1 0.0 .0 latch: call allocation 3 0 0 1 0.0 .0 latch: In memory undo latc 6 0 0 0 0.0 .0 latch: cache buffers lru c 13 0 0 0 0.0 .0 enq: TX - contention 2 0 0 1 0.0 .0 enq: HW - contention 5 0 0 0 0.0 .0 SQL*Net message from clien 14,367 0 1,619,331 112712 2.9 PX Deq Credit: send blkd 981,834 0 157,580 160 196.5 PX Deq: Execution Msg 7,913 0 123,432 15599 1.6 Streams AQ: waiting for me 2,150 90 15,944 7416 0.4 PX Deq: Execute Reply 977,378 0 12,929 13 195.6 Foreground Wait Events DB/Inst: ZIDW/zidw Snaps: 1333-1334 -> 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 -------------------------- ------------ ----- ---------- ------- -------- ------ PX Deq: Table Q Normal 517,693 0 2,171 4 103.6 PX Deq Credit: need buffer 27,200 0 1,819 67 5.4 jobq slave wait 2,064 100 1,030 499 0.4 PX Deq: Parse Reply 576 0 1 1 0.1 PX Deq: Join ACK 576 0 0 1 0.1 single-task message 1 0 0 253 0.0 ------------------------------------------------------------- Background Wait Events DB/Inst: ZIDW/zidw Snaps: 1333-1334 -> 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 parallel write 182,329 0 1,701 9 36.5 50.1 log file parallel write 16,616 0 984 59 3.3 29.0 control file sequential re 9,226 0 77 8 1.8 2.3 control file parallel writ 4,863 0 67 14 1.0 2.0 direct path read 415 0 52 126 0.1 1.5 direct path write 556 0 52 93 0.1 1.5 db file sequential read 1,027 0 8 8 0.2 .2 os thread startup 55 0 5 86 0.0 .1 log file single write 868 0 4 4 0.2 .1 log file sequential read 868 0 2 2 0.2 .1 enq: CF - contention 6 0 1 133 0.0 .0 ASM file metadata operatio 1,421 0 0 0 0.3 .0 log file switch completion 3 0 0 110 0.0 .0 latch: call allocation 366 0 0 1 0.1 .0 latch free 326 0 0 0 0.1 .0 latch: active service list 296 0 0 0 0.1 .0 db file async I/O submit 34,705 0 0 0 6.9 .0 ADR block file read 16 0 0 5 0.0 .0 db file scattered read 11 0 0 7 0.0 .0 latch: object queue header 63 0 0 1 0.0 .0 LGWR wait for redo copy 773 0 0 0 0.2 .0 Disk file operations I/O 249 0 0 0 0.0 .0 reliable message 43 0 0 0 0.0 .0 CSS initialization 1 0 0 13 0.0 .0 latch: cache buffers lru c 87 0 0 0 0.0 .0 latch: shared pool 77 0 0 0 0.0 .0 latch: cache buffers chain 95 0 0 0 0.0 .0 SQL*Net more data from cli 1 0 0 8 0.0 .0 log file sync 2 0 0 2 0.0 .0 CSS operation: action 1 0 0 3 0.0 .0 ADR block file write 5 0 0 0 0.0 .0 asynch descriptor resize 325 100 0 0 0.1 .0 CSS operation: query 6 0 0 0 0.0 .0 latch: checkpoint queue la 24 0 0 0 0.0 .0 SQL*Net message to client 593 0 0 0 0.1 .0 PX Idle Wait 719 0 63,634 88503 0.1 rdbms ipc message 36,090 56 50,797 1407 7.2 Space Manager: slave idle 2,574 91 12,216 4746 0.5 class slave wait 1,644 0 7,219 4391 0.3 DIAG idle wait 7,202 100 7,203 1000 1.4 Streams AQ: waiting for ti 47 66 4,182 88980 0.0 Streams AQ: qmn coordinato 327 39 3,612 11046 0.1 Streams AQ: qmn slave idle 129 0 3,612 28001 0.0 ASM background timer 722 0 3,611 5001 0.1 wait for unread message on 3,609 100 3,609 1000 0.7 smon timer 49 4 3,608 73627 0.0 pmon timer 1,280 89 3,605 2816 0.3 dispatcher timer 60 100 3,600 60002 0.0 shared server idle wait 120 100 3,600 30001 0.0 JOX Jit Process Sleep 238 100 3,570 15000 0.0 SQL*Net message from clien 756 0 14 19 0.2 KSV master wait 541 0 0 0 0.1 ------------------------------------------------------------- Wait Event Histogram DB/Inst: ZIDW/zidw Snaps: 1333-1334 -> 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 16 50.0 12.5 6.3 6.3 12.5 12.5 ADR block file write 5 100.0 ADR file lock 6 100.0 ASM file metadata operatio 1421 99.9 .1 CSS initialization 12 41.7 41.7 16.7 CSS operation: action 13 23.1 30.8 46.2 CSS operation: query 72 83.3 4.2 4.2 4.2 4.2 Disk file operations I/O 2589 95.9 1.4 2.2 .3 .0 .1 LGWR wait for redo copy 774 100.0 PX Deq Credit: free buffer 21 100.0 PX Deq: Signal ACK EXT 612 97.9 1.8 .2 .2 PX Deq: Signal ACK RSG 612 93.8 1.0 1.1 1.6 .7 .2 1.6 PX Deq: Slave Session Stat 1222 74.0 9.6 10.0 3.8 2.7 PX qref latch 4840 99.3 .0 .0 .6 .0 SQL*Net break/reset to cli 764 98.3 .3 1.4 SQL*Net message from dblin 11 18.2 27.3 18.2 18.2 9.1 9.1 SQL*Net message to client 15K 100.0 SQL*Net message to dblink 11 100.0 SQL*Net more data from cli 43 48.8 16.3 4.7 4.7 2.3 20.9 2.3 SQL*Net more data from dbl 1 100.0 SQL*Net more data to clien 1519 99.9 .1 asynch descriptor resize 325 100.0 buffer busy waits 302 99.7 .3 control file parallel writ 4872 28.1 7.9 17.9 27.0 9.1 2.6 7.4 control file sequential re 24.4K 45.6 13.9 20.3 9.1 2.6 2.1 6.3 cursor: mutex S 4 100.0 cursor: mutex X 7 100.0 cursor: pin S 12.7M 99.1 .0 .0 .0 .8 .0 .0 cursor: pin S wait on X 99 68.7 31.3 db file async I/O submit 34.8K 100.0 db file parallel read 34 5.9 91.2 2.9 db file parallel write 182.6 22.1 15.0 23.0 19.3 10.4 5.1 5.1 .0 db file scattered read 868 5.5 4.8 5.3 8.3 17.9 45.6 12.6 db file sequential read 102.6 2.7 2.1 8.2 17.5 23.1 39.0 7.4 .0 direct path read 315.5 1.7 1.6 2.7 4.2 7.0 12.3 68.5 1.9 direct path read temp 3241 22.0 27.3 27.1 10.0 4.7 4.5 4.4 direct path write 660 9.7 8.5 15.2 19.1 8.5 6.5 32.4 .2 direct path write temp 896 10.8 12.7 17.1 30.0 12.7 7.5 9.2 enq: BF - allocation conte 3 100.0 enq: CF - contention 6 16.7 16.7 66.7 enq: CR - block range reus 2 50.0 50.0 enq: FB - contention 2 100.0 enq: HW - contention 5 100.0 enq: KO - fast object chec 22 18.2 4.5 18.2 59.1 enq: PS - contention 52 84.6 1.9 1.9 11.5 enq: RO - fast object reus 12 25.0 16.7 16.7 8.3 33.3 enq: TX - contention 2 100.0 enq: TX - index contention 2 50.0 50.0 enq: TX - row lock content 9 11.1 11.1 11.1 22.2 11.1 33.3 external table open 2 100.0 external table read 4 100.0 kksfbc child completion 22 22.7 77.3 latch free 653 79.9 3.5 .6 .9 1.4 11.8 1.8 latch: In memory undo latc 6 83.3 16.7 latch: active service list 304 93.8 3.0 3.3 latch: cache buffers chain 396.6 99.9 .0 .0 .0 .0 .0 .0 latch: cache buffers lru c 101 100.0 latch: call allocation 369 62.9 35.8 1.1 .3 latch: checkpoint queue la 25 100.0 latch: enqueue hash chains 3 100.0 Wait Event Histogram DB/Inst: ZIDW/zidw Snaps: 1333-1334 -> 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: messages 1 100.0 latch: object queue header 85 71.8 16.5 5.9 4.7 1.2 latch: parallel query allo 43 93.0 4.7 2.3 latch: redo allocation 7 100.0 latch: redo writing 1 100.0 latch: row cache objects 76 98.7 1.3 latch: shared pool 196 99.5 .5 latch: undo global data 1 100.0 library cache load lock 3 100.0 library cache lock 1 100.0 library cache: mutex X 1531 85.7 .4 .1 .1 12.0 1.6 .1 local write wait 27 3.7 3.7 66.7 7.4 7.4 11.1 log buffer space 69 1.4 1.4 2.9 2.9 4.3 5.8 81.2 log file parallel write 16.6K 5.7 1.7 5.1 9.0 14.9 21.1 42.4 log file sequential read 872 31.0 21.6 33.6 11.5 1.8 .6 log file single write 872 24.4 8.5 23.7 34.4 7.9 .8 .2 log file switch (private s 90 2.2 3.3 16.7 77.8 log file switch completion 369 .3 .3 .3 3.3 7.0 88.9 log file sync 1977 11.1 13.7 15.7 26.5 12.6 4.6 15.8 os thread startup 162 100.0 read by other session 285 4.9 4.2 10.2 8.4 25.6 31.6 15.1 reliable message 84 91.7 7.1 1.2 wait list latch free 5 100.0 ASM background timer 722 100.0 DIAG idle wait 7205 100.0 JOX Jit Process Sleep 238 100.0 KSV master wait 543 100.0 PX Deq Credit: need buffer 27.2K 12.6 13.2 14.7 20.3 8.7 6.6 22.5 1.3 PX Deq Credit: send blkd 984.1 7.7 8.4 46.9 14.6 2.8 1.3 16.2 2.2 PX Deq: Execute Reply 979.7 98.4 .1 .2 .3 .3 .3 .4 .1 PX Deq: Execution Msg 7892 62.3 2.4 3.1 4.3 3.8 1.7 9.1 13.3 PX Deq: Join ACK 576 83.0 5.2 5.4 4.2 1.7 .2 .3 PX Deq: Parse Reply 576 81.6 3.5 4.9 4.9 4.7 .3 .2 PX Deq: Table Q Normal 516.7 91.0 3.1 2.6 1.8 .8 .3 .3 .0 PX Idle Wait 575 2.6 2.1 2.4 3.3 10.6 1.2 23.8 53.9 SQL*Net message from clien 15.2K 61.4 5.9 1.6 1.9 .6 .2 4.2 24.3 Space Manager: slave idle 2570 .0 .2 .1 1.8 97.9 Streams AQ: qmn coordinato 327 60.2 .3 39.4 Streams AQ: qmn slave idle 129 100.0 Streams AQ: waiting for me 2148 .2 .1 .2 .7 .7 .6 4.8 92.6 Streams AQ: waiting for ti 47 34.0 40.4 25.5 class slave wait 1650 34.5 .9 1.3 .8 4.8 20.8 36.8 dispatcher timer 60 100.0 jobq slave wait 2064 .1 99.9 pmon timer 1281 .1 .2 4.5 95.2 rdbms ipc message 36.1K 13.0 .7 2.1 2.1 2.8 4.7 38.1 36.5 shared server idle wait 121 100.0 single-task message 1 100.0 smon timer 47 2.1 97.9 wait for unread message on 3611 100.0 .0 ------------------------------------------------------------- Wait Event Histogram Detail (64 msec to 2 sec)DB/Inst: ZIDW/zidw Snaps: 1333 -> 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 -------------------------- ----- ----- ----- ----- ----- ----- ----- ----- ----- CSS initialization 2 83.3 16.7 Disk file operations I/O 2 99.9 .0 .0 PX Deq: Signal ACK RSG 10 98.4 .7 .8 .2 SQL*Net break/reset to cli 11 98.6 1.2 .3 SQL*Net message from dblin 1 81.8 9.1 9.1 SQL*Net more data from cli 10 76.7 2.3 11.6 4.7 2.3 2.3 control file parallel writ 359 92.6 2.6 2.3 1.7 .7 .1 control file sequential re 1531 93.7 2.2 1.9 1.6 .5 .1 cursor: pin S 206 100.0 .0 .0 .0 db file parallel read 32 5.9 35.3 50.0 5.9 2.9 db file parallel write 9231 94.9 2.7 1.4 .7 .2 .1 .0 db file scattered read 109 87.4 8.5 1.6 .5 1.2 .8 db file sequential read 7589 92.6 6.4 .7 .2 .1 .0 .0 direct path read 222.2 29.6 17.3 18.3 14.3 11.6 7.1 1.9 .0 direct path read temp 141 95.6 2.9 1.3 .2 direct path write 215 67.4 9.8 6.8 6.1 5.6 4.1 .2 direct path write temp 82 90.8 5.2 3.1 .8 enq: CF - contention 4 33.3 33.3 16.7 16.7 enq: CR - block range reus 1 50.0 50.0 enq: KO - fast object chec 4 22.7 4.5 4.5 9.1 59.1 enq: RO - fast object reus 4 66.7 8.3 8.3 16.7 enq: TX - row lock content 3 66.7 11.1 11.1 11.1 kksfbc child completion 17 22.7 77.3 latch free 89 86.4 .6 .9 4.4 5.8 1.8 latch: cache buffers chain 10 100.0 .0 library cache load lock 3 100.0 library cache: mutex X 2 99.9 .1 local write wait 3 88.9 7.4 3.7 log buffer space 56 18.8 8.7 11.6 33.3 13.0 14.5 log file parallel write 7062 57.6 13.4 16.0 9.5 3.1 .4 log file single write 2 99.8 .2 log file switch (private s 70 22.2 17.8 24.4 18.9 14.4 2.2 log file switch completion 328 11.1 14.6 32.8 25.5 11.9 4.1 log file sync 312 84.2 4.1 3.0 4.0 2.9 1.7 os thread startup 162 14.2 75.3 8.6 1.9 read by other session 43 84.9 13.3 .7 1.1 ------------------------------------------------------------- Wait Event Histogram Detail (4 sec to 2 min)DB/Inst: ZIDW/zidw Snaps: 1333-1 -> 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 -------------------------- ----- ----- ----- ----- ----- ----- ----- ----- ----- SQL*Net message from dblin 1 90.9 9.1 SQL*Net more data to clien 1 99.9 .1 direct path read 26 100.0 .0 enq: KO - fast object chec 13 40.9 4.5 54.5 ------------------------------------------------------------- Wait Event Histogram Detail (4 min to 1 hr)DB/Inst: ZIDW/zidw Snaps: 1333-13 No data exists for this section of the report. ------------------------------------------------------------- Service Statistics DB/Inst: ZIDW/zidw Snaps: 1333-1334 -> ordered by DB Time Physical Logical Service Name DB Time (s) DB CPU (s) Reads (K) Reads (K) ---------------------------- ------------ ------------ ------------ ------------ zidw 110,696 50,978 35,702 1,007,132 SYS$USERS 109 36 3 43 SYS$BACKGROUND 0 0 8 30 zidwXDB 0 0 0 0 ------------------------------------------------------------- Service Wait Class Stats DB/Inst: ZIDW/zidw Snaps: 1333-1334 -> 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 --------- --------- --------- --------- --------- --------- --------- --------- zidw 420236 56931 13186485 1319 0 0 14215 7 SYS$USERS 3185 66 38 0 0 0 1737 6 SYS$BACKGROUND 2253 112 151 5 0 0 0 0 ------------------------------------------------------------- SQL ordered by Elapsed Time DB/Inst: ZIDW/zidw Snaps: 1333-1334 -> 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 109.4% of Total DB Time (s): 110,825 -> Captured PL/SQL account for 9.5% of Total DB Time (s): 110,825 Elapsed Elapsed Time Time (s) Executions per Exec (s) %Total %CPU %IO SQL Id ---------------- -------------- ------------- ------ ------ ------ ------------- 12,017.7 0 N/A 10.8 98.7 .1 0jtzdh6qwgrkf Module: OWB_CCS SELECT /*+ parallel(l,32) */ TO_CHAR(transdate, 'YYYYMMDD')||'000000' dw_date_ time_key, get_subscriber_key (subno) dw_subscriber_key, get_called_operator_key (b_subno,upper(trim(chargetype))) dw_called_oper ator_key, get_calling_operator_key (subno) dw_calling_operator_k 11,645.7 0 N/A 10.5 98.8 .1 1u116zg71khyq Module: OWB_CCS SELECT /*+ parallel(l,32) */ TO_CHAR(transdate, 'YYYYMMDD')||'000000' dw_date_ time_key, get_subscriber_key (subno) dw_subscriber_key, get_called_operator_key (b_subno,upper(trim(chargetype))) dw_called_oper ator_key, get_calling_operator_key (subno) dw_calling_operator_k 10,936.6 0 N/A 9.9 98.5 .2 61rzu5641tg0n Module: OWB_CCS SELECT /*+ parallel(l,32) */ TO_CHAR(transdate, 'YYYYMMDD')||'000000' dw_date_ time_key, get_subscriber_key (subno) dw_subscriber_key, get_called_operator_key (b_subno,upper(trim(chargetype))) dw_called_oper ator_key, get_calling_operator_key (subno) dw_calling_operator_k 10,068.6 3 3,356.21 9.1 7.0 92.5 ac2dv445ufwab Module: SQL*Plus SELECT :B2 , DW_SUBSCRIBER_KEY, 'CFT', SUM (CONVERTED_AMOUNT01) * -1 REVENUE FRO M ADS.CFT_FACT WHERE DW_DATE_TIME_KEY = :B1 AND DW_EVENT_CAUSE_KEY IN (36, 37, 4 4, 45) AND DW_SUBSCRIBER_KEY > 0 GROUP BY DW_SUBSCRIBER_KEY UNION ALL SELECT :B2 , DW_SUBSCRIBER_KEY, 'OFFER_CHANGE', COUNT (*) * 2000 REVENUE FROM ADS.CFT_FACT 5,159.7 9,135,500 0.00 4.7 99.5 .0 2pj55mj4qqby8 Module: OWB_CCS SELECT /*+ index(o,idx_operator_code_flag)*/ DW_OPERATOR_KEY FROM DDS.OPERATOR_D IM O WHERE OPERATOR_CODE = SUBSTR (:B1 , 1, LENGTH (OPERATOR_CODE)) AND OPERATOR _FLAG = 'L' AND SOURCE_NAME IS NULL 4,820.4 1 4,820.39 4.3 6.8 92.1 0brw8ckhdtww7 Module: OWB_CCS SELECT /*+ parallel(l,4) index(c) nologging */ trunc(to_dat e(dw_date_time_key,'yyyymmddhh24miss')) transdate, 'VAS' activity_name, rds.get_subscriber_number(dw_subscriber_key) subno, b_subno, rds.get_sub_connection_type(dw_sub_connection_ty 4,658.0 1 4,657.96 4.2 23.7 74.4 fus3gcdmax6zv Module: OWB_CCS SELECT /*+ parallel(l,4) nologging */ trunc(to_date(dw_date_time_ke y,'yyyymmddhh24miss')) transdate, rds.get_subscriber_number(dw_subs criber_key) subno, sum (bill_amount) bill_amount, sysdate insert_date, user inserted_by, null update_date, 4,552.5 12,187,233 0.00 4.1 99.6 .0 2bhmgk5bcfgmd Module: OWB_CCS SELECT /*+ index(o,idx_operator_code_flag_source) */ DW_OPERATOR_KEY FROM DDS.OP ERATOR_DIM O WHERE OPERATOR_CODE LIKE SUBSTR (:B1 , 1, LENGTH (OPERATOR_CODE)) A ND OPERATOR_FLAG = 'L' AND SOURCE_NAME = '1' 4,162.7 12,222,774 0.00 3.8 97.2 .0 5fcy78kux8bju Module: OWB_CCS SELECT /*+ index(c,idx_country_code) */ DW_COUNTRY_KEY FROM DDS.COUNTRY_DIM C WH ERE COUNTRY_CODE = SUBSTR (:B1 , 1, LENGTH (COUNTRY_CODE)) 4,050.3 1 4,050.35 3.7 9.4 89.4 0gd8bgn1t4yk2 SQL ordered by Elapsed Time DB/Inst: ZIDW/zidw Snaps: 1333-1334 -> 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 109.4% of Total DB Time (s): 110,825 -> Captured PL/SQL account for 9.5% of Total DB Time (s): 110,825 Elapsed Elapsed Time Time (s) Executions per Exec (s) %Total %CPU %IO SQL Id ---------------- -------------- ------------- ------ ------ ------ ------------- Module: OWB_CCS select /*+parallel(l,8)*/ trunc(to_date(dw_date_time_key,'yyyymmddhh24miss')) tr ansdate, round(sum(bill_amount)/1170) Revenue_USD, null reven ue_type, sysdate insert_date, user inserted_by, null update_d ate, null updated_by, '23-JUN-13' business_date, 'PREPAID' s 4,005.0 1 4,004.95 3.6 15.6 83.2 g07cph4xbsstv Module: OWB_CCS SELECT /*+ parallel(l,4) */ trunc(to_date(dw_date_time_key,'yyyymmddhh2 4miss')) transdate, SUM (CASE WHEN dw_tariff_group_key = 48 AND dw_tariff_profile_key = 42 THEN actual_ duration ELSE 0 END / 1024 / 1024 ) ego_mb 3,993.8 1 3,993.84 3.6 13.3 85.6 6t76fd2mj8dam Module: OWB_CCS SELECT /*+ parallel(l,4) index(t) index(c) */ trunc(to_date(dw_da te_time_key,'yyyymmddhh24miss')) transdate, city_name,nvl(tariff_ profile_product,'Postpaid_All') product, (SUM (bill_amount) / 1170 ) bill_amount, sysdate insert_date, user inserted_by 3,987.2 1 3,987.21 3.6 11.9 87.0 754rr4kawf3ka Module: OWB_CCS SELECT /*+ parallel(l,4) index(c) nologging */ trunc(to_date(dw_date _time_key,'yyyymmddhh24miss')) transdate, CASE WHEN city_name = 'BA BIL' THEN 'Hilla' WHEN city_name ='MISSAN' THEN 'Amara' WHEN city_name = 'MUTHANA' THEN 'Samawa' WHEN city_name = 'NINAWA 3,985.6 1 3,985.62 3.6 11.5 87.4 0df5h3mcgcb3w Module: OWB_CCS SELECT /*+ parallel(l,4) */ trunc(to_date(dw_date_time_key,'yyy ymmddhh24miss')) transdate, dw_calling_operator_key, dw_called_operator_key, dw_call_type_key,dw_sub_connec tion_type_key, dw_tariff_class_key,dw_tariff_profile_ke 3,939.4 1 3,939.37 3.6 6.4 92.5 acwr8q576dmsm Module: OWB_CCS SELECT /*+ parallel(l,4) index(c) */ trunc(to_date(dw_date_t ime_key,'yyyymmddhh24miss')) transdate, city_name, rds.get_sub_connection_type (dw_sub_connection _type_key) subscri 3,938.3 1 3,938.29 3.6 1.2 97.8 8d202unu87sj6 Module: OWB_CCS SELECT /*+ parallel(l,8) */ trunc(to_date(dw_date_time_key,'yyyymmddhh2 4miss')) transdate, rds.get_sub_connection_type(dw_sub_connection_type_key ) subscriber_type, DECODE (tariff_group_code, 'MMS_PP', 'MMS', 'MMS_PS', 'MMS', 3,890.7 1 3,890.67 3.5 2.4 96.5 3bft9p301pagu Module: OWB_CCS SELECT /*+ parallel(l,4) index(t) */ trunc(to_date(dw_date_time_key ,'yyyymmddhh24miss')) transdate, SUM (bill_amount) revenue, sysdate insert_date, user inserted_by, null update_date, null updated_by , '23-JUN-13' business_date, 3,881.4 1 3,881.43 3.5 2.4 96.4 4p8dj2g01ym6y Module: OWB_CCS SELECT /*+ parallel(l,4) index(t) */ trunc(to_date(dw_date_time_key ,'yyyymmddhh24miss')) transdate, b_subno,rds.get_bill_code (dw_bill _code_key) bill_code, rds.get_sub_connection_type (dw_sub_connectio SQL ordered by Elapsed Time DB/Inst: ZIDW/zidw Snaps: 1333-1334 -> 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 109.4% of Total DB Time (s): 110,825 -> Captured PL/SQL account for 9.5% of Total DB Time (s): 110,825 Elapsed Elapsed Time Time (s) Executions per Exec (s) %Total %CPU %IO SQL Id ---------------- -------------- ------------- ------ ------ ------ ------------- n_type_key) subscriber_type, COUNT (*) no_of_calls, SUM (DURATION) 3,529.1 46,514,556 0.00 3.2 73.7 10.5 7aqsa764h80wz Module: OWB_CCS SELECT /*+ index(s) */ SUBNO FROM DDS.SUBSCRIBER_DIM S WHERE DW_SUBSCRIBER_KEY = :B1 2,942.3 1 2,942.35 2.7 79.3 1.8 6a78wuys8hrjg Module: SQL*Plus SELECT /*+ parallel(t,16) */ :B1 , DW_SUBSCRIBER_KEY, RDS.GET_SUBSCRIBER_NUMBER (DW_SUBSCRIBER_KEY) SUBNO, SUM (TOTAL_REVENUE) TOTAL_REVENUE, SYSDATE INSERT_DAT E, USER INSERTED_BY, NULL UPDATE_DATE, NULL UPDATED_BY, :B1 BUSINESS_DATE, 'ARPU _TEMP' SOURCE_NAME FROM ADS.ARPU_TEMP T GROUP BY DW_SUBSCRIBER_KEY 1,952.7 1 1,952.71 1.8 96.5 .0 0vm00ykpyb771 Module: OWB_CCS declare "$MAP_ENV$" owbsys.wb_rt_mapaudit.wb_rt_name_values;begin "$MAP_ENV$"(1) .param_name := 'OBJECT_ID';"$MAP_ENV$"(1).param_value := 2134;"$MAP_ENV$"(2).par am_name := 'AUDIT_ID';"$MAP_ENV$"(2).param_value := 66370;"$MAP_ENV$"(3).param_n ame := 'OPERATING_MODE';"$MAP_ENV$"(3).param_value := '3';"$MAP_ENV$"(4).param_n 1,931.8 1 1,931.84 1.7 96.5 .0 bggx5ryrzrrhn Module: OWB_CCS declare "$MAP_ENV$" owbsys.wb_rt_mapaudit.wb_rt_name_values;begin "$MAP_ENV$"(1) .param_name := 'OBJECT_ID';"$MAP_ENV$"(1).param_value := 2050;"$MAP_ENV$"(2).par am_name := 'AUDIT_ID';"$MAP_ENV$"(2).param_value := 66322;"$MAP_ENV$"(3).param_n ame := 'OPERATING_MODE';"$MAP_ENV$"(3).param_value := '3';"$MAP_ENV$"(4).param_n 1,865.0 1 1,864.97 1.7 49.9 47.1 f30z28tuvg5ax Module: OWB_CCS SELECT /*+ parallel(f,4) */ DW_DATE_TIME_KEY, DW_SUBSCRIBER_KEY, DW_CALLED_OPERA TOR_KEY, DW_CALLING_OPERATOR_KEY, DW_COUNTRY_KEY, DW_CALL_TYPE_KEY, DW_TARIFF_PR OFILE_KEY, DW_CHARGE_TYPE_KEY, DW_TARIFF_GROUP_KEY, DW_TARIFF_CLASS_KEY, DW_FILT ER_RES_KEY, DW_SUBSCRIBER_TYPE_KEY, DW_RATE_TYPE_KEY, DW_CATEGORY_KEY, DW_SUB_CO 1,859.6 1 1,859.58 1.7 96.3 .0 6t5vg4gdmz55w Module: OWB_CCS declare "$MAP_ENV$" owbsys.wb_rt_mapaudit.wb_rt_name_values;begin "$MAP_ENV$"(1) .param_name := 'OBJECT_ID';"$MAP_ENV$"(1).param_value := 2062;"$MAP_ENV$"(2).par am_name := 'AUDIT_ID';"$MAP_ENV$"(2).param_value := 66346;"$MAP_ENV$"(3).param_n ame := 'OPERATING_MODE';"$MAP_ENV$"(3).param_value := '3';"$MAP_ENV$"(4).param_n 1,600.1 3 533.36 1.4 79.4 13.1 1kgg2dd6mwdcd Module: SQL*Plus SELECT :B2 , DW_SUBSCRIBER_KEY, 'LC_DETAILS', SUM (BILL_AMOUNT) REVENUE FROM ADS .LC_DETAILS_FACT L WHERE DW_DATE_TIME_KEY = :B1 AND DW_SUB_CONNECTION_TYPE_KEY = 1 AND DW_SUBSCRIBER_KEY > 0 GROUP BY DW_SUBSCRIBER_KEY 1,549.1 0 N/A 1.4 92.0 .3 gfwrvhd66avwu Module: OWB_CCS declare begin "P_CALL_LC_DETAIL_FCT_HIST_DATA"(); end; 1,295.6 2 647.78 1.2 3.9 96.2 4k2303m5k8syc Module: SQL*Plus SELECT /*+parallel(c,8) */ :B2 , ADS.GET_SUBSCRIBER_KEY (SUB_NUMBER) DW_SUBSCRIB ER_KEY, 'ROAM_PREP', SUM (CALL_CHARGE / 100) REVENUE FROM ADS.CAT_FACT C WHERE D W_DATE_TIME_KEY = :B1 AND ROAMING_FLAG = '01' GROUP BY SUB_NUMBER 1,121.9 0 N/A 1.0 2.4 96.9 835r83v4x62k4 Module: SQL*Plus SQL ordered by Elapsed Time DB/Inst: ZIDW/zidw Snaps: 1333-1334 -> 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 109.4% of Total DB Time (s): 110,825 -> Captured PL/SQL account for 9.5% of Total DB Time (s): 110,825 Elapsed Elapsed Time Time (s) Executions per Exec (s) %Total %CPU %IO SQL Id ---------------- -------------- ------------- ------ ------ ------ ------------- DECLARE v_date DATE := '01-apr-2013'; BEGIN FOR i IN 1 .. 30 LOOP rds.loyalty_program_data.p_load_usage_outgoing_data (v_date); rds.loyal ty_program_data.p_load_usage_intl_minutes_data (v_date); rds.loyalty_progr am_data.p_load_usage_outgoing_sms_data (v_date); rds.loyalty_program_data. 1,115.4 0 N/A 1.0 2.3 97.0 60nuy7pb6fdyv Module: SQL*Plus INSERT /*+ APPEND */INTO RDS.USAGE_TEMP F VALUES (:B1 ,:B2 ,:B3 ,:B4 ,:B5 ,:B6 , :B7 ,:B8 ,:B9 ,:B10 ,:B11 ,:B12 ,:B13 ,:B14 ,:B15 ,:B16 ,:B17 ) ------------------------------------------------------------- SQL ordered by CPU Time DB/Inst: ZIDW/zidw Snaps: 1333-1334 -> 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 127.8% of Total CPU Time (s): 50,999 -> Captured PL/SQL account for 17.0% of Total CPU Time (s): 50,999 CPU CPU per Elapsed Time (s) Executions Exec (s) %Total Time (s) %CPU %IO SQL Id ---------- ------------ ---------- ------ ---------- ------ ------ ------------- 11,863.3 0 N/A 23.3 12,017.7 98.7 .1 0jtzdh6qwgrkf Module: OWB_CCS SELECT /*+ parallel(l,32) */ TO_CHAR(transdate, 'YYYYMMDD')||'000000' dw_date_ time_key, get_subscriber_key (subno) dw_subscriber_key, get_called_operator_key (b_subno,upper(trim(chargetype))) dw_called_oper ator_key, get_calling_operator_key (subno) dw_calling_operator_k 11,507.5 0 N/A 22.6 11,645.7 98.8 .1 1u116zg71khyq Module: OWB_CCS SELECT /*+ parallel(l,32) */ TO_CHAR(transdate, 'YYYYMMDD')||'000000' dw_date_ time_key, get_subscriber_key (subno) dw_subscriber_key, get_called_operator_key (b_subno,upper(trim(chargetype))) dw_called_oper ator_key, get_calling_operator_key (subno) dw_calling_operator_k 10,769.9 0 N/A 21.1 10,936.6 98.5 .2 61rzu5641tg0n Module: OWB_CCS SELECT /*+ parallel(l,32) */ TO_CHAR(transdate, 'YYYYMMDD')||'000000' dw_date_ time_key, get_subscriber_key (subno) dw_subscriber_key, get_called_operator_key (b_subno,upper(trim(chargetype))) dw_called_oper ator_key, get_calling_operator_key (subno) dw_calling_operator_k 5,135.5 9,135,500 0.00 10.1 5,159.7 99.5 .0 2pj55mj4qqby8 Module: OWB_CCS SELECT /*+ index(o,idx_operator_code_flag)*/ DW_OPERATOR_KEY FROM DDS.OPERATOR_D IM O WHERE OPERATOR_CODE = SUBSTR (:B1 , 1, LENGTH (OPERATOR_CODE)) AND OPERATOR _FLAG = 'L' AND SOURCE_NAME IS NULL 4,532.5 12,187,233 0.00 8.9 4,552.5 99.6 .0 2bhmgk5bcfgmd Module: OWB_CCS SELECT /*+ index(o,idx_operator_code_flag_source) */ DW_OPERATOR_KEY FROM DDS.OP ERATOR_DIM O WHERE OPERATOR_CODE LIKE SUBSTR (:B1 , 1, LENGTH (OPERATOR_CODE)) A ND OPERATOR_FLAG = 'L' AND SOURCE_NAME = '1' 4,046.9 12,222,774 0.00 7.9 4,162.7 97.2 .0 5fcy78kux8bju Module: OWB_CCS SELECT /*+ index(c,idx_country_code) */ DW_COUNTRY_KEY FROM DDS.COUNTRY_DIM C WH ERE COUNTRY_CODE = SUBSTR (:B1 , 1, LENGTH (COUNTRY_CODE)) 2,599.4 46,514,556 0.00 5.1 3,529.1 73.7 10.5 7aqsa764h80wz Module: OWB_CCS SELECT /*+ index(s) */ SUBNO FROM DDS.SUBSCRIBER_DIM S WHERE DW_SUBSCRIBER_KEY = :B1 2,332.5 1 2,332.54 4.6 2,942.3 79.3 1.8 6a78wuys8hrjg Module: SQL*Plus SELECT /*+ parallel(t,16) */ :B1 , DW_SUBSCRIBER_KEY, RDS.GET_SUBSCRIBER_NUMBER (DW_SUBSCRIBER_KEY) SUBNO, SUM (TOTAL_REVENUE) TOTAL_REVENUE, SYSDATE INSERT_DAT E, USER INSERTED_BY, NULL UPDATE_DATE, NULL UPDATED_BY, :B1 BUSINESS_DATE, 'ARPU _TEMP' SOURCE_NAME FROM ADS.ARPU_TEMP T GROUP BY DW_SUBSCRIBER_KEY 1,884.6 1 1,884.63 3.7 1,952.7 96.5 .0 0vm00ykpyb771 Module: OWB_CCS declare "$MAP_ENV$" owbsys.wb_rt_mapaudit.wb_rt_name_values;begin "$MAP_ENV$"(1) .param_name := 'OBJECT_ID';"$MAP_ENV$"(1).param_value := 2134;"$MAP_ENV$"(2).par am_name := 'AUDIT_ID';"$MAP_ENV$"(2).param_value := 66370;"$MAP_ENV$"(3).param_n ame := 'OPERATING_MODE';"$MAP_ENV$"(3).param_value := '3';"$MAP_ENV$"(4).param_n 1,864.7 1 1,864.70 3.7 1,931.8 96.5 .0 bggx5ryrzrrhn Module: OWB_CCS declare "$MAP_ENV$" owbsys.wb_rt_mapaudit.wb_rt_name_values;begin "$MAP_ENV$"(1) SQL ordered by CPU Time DB/Inst: ZIDW/zidw Snaps: 1333-1334 -> 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 127.8% of Total CPU Time (s): 50,999 -> Captured PL/SQL account for 17.0% of Total CPU Time (s): 50,999 CPU CPU per Elapsed Time (s) Executions Exec (s) %Total Time (s) %CPU %IO SQL Id ---------- ------------ ---------- ------ ---------- ------ ------ ------------- .param_name := 'OBJECT_ID';"$MAP_ENV$"(1).param_value := 2050;"$MAP_ENV$"(2).par am_name := 'AUDIT_ID';"$MAP_ENV$"(2).param_value := 66322;"$MAP_ENV$"(3).param_n ame := 'OPERATING_MODE';"$MAP_ENV$"(3).param_value := '3';"$MAP_ENV$"(4).param_n 1,790.4 1 1,790.39 3.5 1,859.6 96.3 .0 6t5vg4gdmz55w Module: OWB_CCS declare "$MAP_ENV$" owbsys.wb_rt_mapaudit.wb_rt_name_values;begin "$MAP_ENV$"(1) .param_name := 'OBJECT_ID';"$MAP_ENV$"(1).param_value := 2062;"$MAP_ENV$"(2).par am_name := 'AUDIT_ID';"$MAP_ENV$"(2).param_value := 66346;"$MAP_ENV$"(3).param_n ame := 'OPERATING_MODE';"$MAP_ENV$"(3).param_value := '3';"$MAP_ENV$"(4).param_n 1,425.2 0 N/A 2.8 1,549.1 92.0 .3 gfwrvhd66avwu Module: OWB_CCS declare begin "P_CALL_LC_DETAIL_FCT_HIST_DATA"(); end; 1,271.1 3 423.69 2.5 1,600.1 79.4 13.1 1kgg2dd6mwdcd Module: SQL*Plus SELECT :B2 , DW_SUBSCRIBER_KEY, 'LC_DETAILS', SUM (BILL_AMOUNT) REVENUE FROM ADS .LC_DETAILS_FACT L WHERE DW_DATE_TIME_KEY = :B1 AND DW_SUB_CONNECTION_TYPE_KEY = 1 AND DW_SUBSCRIBER_KEY > 0 GROUP BY DW_SUBSCRIBER_KEY 1,104.4 1 1,104.36 2.2 4,658.0 23.7 74.4 fus3gcdmax6zv Module: OWB_CCS SELECT /*+ parallel(l,4) nologging */ trunc(to_date(dw_date_time_ke y,'yyyymmddhh24miss')) transdate, rds.get_subscriber_number(dw_subs criber_key) subno, sum (bill_amount) bill_amount, sysdate insert_date, user inserted_by, null update_date, 943.6 0 N/A 1.9 1,081.1 87.3 9.0 1bcjupk9s6f3q Module: OWB_CCS declare begin "P_CALL_DLY_LC_DT_CELL_TRFC_DTA"(); end; 930.1 1 930.09 1.8 1,865.0 49.9 47.1 f30z28tuvg5ax Module: OWB_CCS SELECT /*+ parallel(f,4) */ DW_DATE_TIME_KEY, DW_SUBSCRIBER_KEY, DW_CALLED_OPERA TOR_KEY, DW_CALLING_OPERATOR_KEY, DW_COUNTRY_KEY, DW_CALL_TYPE_KEY, DW_TARIFF_PR OFILE_KEY, DW_CHARGE_TYPE_KEY, DW_TARIFF_GROUP_KEY, DW_TARIFF_CLASS_KEY, DW_FILT ER_RES_KEY, DW_SUBSCRIBER_TYPE_KEY, DW_RATE_TYPE_KEY, DW_CATEGORY_KEY, DW_SUB_CO 757.1 0 N/A 1.5 1,037.3 73.0 21.2 74n90xwzzhcs5 Module: SQL*Plus DECLARE v_date DATE := '01-apr-2013'; BEGIN FOR i IN 1 .. 30 LOOP ads.daily_subs_arpu_data.p_main (v_date); v_date := v_date + 1; END LOOP; END; 729.7 12,160,966 0.00 1.4 733.7 99.5 .0 gvy9dpb3srgxq Module: OWB_CCS SELECT DW_SUBSCRIBER_CONTRACT_KEY FROM DDS.SUBSCRIBER_CONTRACT_DIM C WHERE CONTR ACT_NUMBER = :B1 715.2 12,185,312 0.00 1.4 733.1 97.6 .7 c3r4g8knwqdy4 Module: OWB_CCS SELECT /*+ index(a) */ DW_SUBSCRIBER_KEY FROM DDS.SUBSCRIBER_DIM A WHERE SUBNO = TO_CHAR(:B1 ) 708.3 3 236.09 1.4 10,068.6 7.0 92.5 ac2dv445ufwab Module: SQL*Plus SELECT :B2 , DW_SUBSCRIBER_KEY, 'CFT', SUM (CONVERTED_AMOUNT01) * -1 REVENUE FRO M ADS.CFT_FACT WHERE DW_DATE_TIME_KEY = :B1 AND DW_EVENT_CAUSE_KEY IN (36, 37, 4 4, 45) AND DW_SUBSCRIBER_KEY > 0 GROUP BY DW_SUBSCRIBER_KEY UNION ALL SELECT :B2 SQL ordered by CPU Time DB/Inst: ZIDW/zidw Snaps: 1333-1334 -> 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 127.8% of Total CPU Time (s): 50,999 -> Captured PL/SQL account for 17.0% of Total CPU Time (s): 50,999 CPU CPU per Elapsed Time (s) Executions Exec (s) %Total Time (s) %CPU %IO SQL Id ---------- ------------ ---------- ------ ---------- ------ ------ ------------- , DW_SUBSCRIBER_KEY, 'OFFER_CHANGE', COUNT (*) * 2000 REVENUE FROM ADS.CFT_FACT 625.6 1 625.60 1.2 4,005.0 15.6 83.2 g07cph4xbsstv Module: OWB_CCS SELECT /*+ parallel(l,4) */ trunc(to_date(dw_date_time_key,'yyyymmddhh2 4miss')) transdate, SUM (CASE WHEN dw_tariff_group_key = 48 AND dw_tariff_profile_key = 42 THEN actual_ duration ELSE 0 END / 1024 / 1024 ) ego_mb 586.5 1 586.51 1.2 666.5 88.0 6.2 dumby3puzszwy Module: nqsserver@tabssb08 (TNS V1-V3) /* 7fe4c811 */WITH SAWITH0 AS (select sum(T48095.BILL_AMOUNT) as c1, T4878 3.CITY_NAME as c2, T48434.DAY as c3, T48731.SUB_CONNECTION_TYPE as c4 from SUB_CONNECTION_TYPE_DIM T48731, CELL_ID_DIM T48783, DATE_TI ME_DIM T48434, LC_DETAILS_FACT T48095 where ( T48095.DW_CELL_ID_KEY = T487 551.9 11,144,022 0.00 1.1 559.5 98.6 .0 fp5yksqm8b61h Module: OWB_CCS SELECT /*+ index (c) */ DW_CELL_ID_KEY FROM DDS.CELL_ID_DIM C WHERE CELL_ID_CODE = :B1 AND ACTIVE_YN = 'Y' 547.3 12,191,402 0.00 1.1 554.5 98.7 .0 1sjk4ftb4xmc9 Module: OWB_CCS SELECT /*+ index(IDX_PEAK_OFFPEAK_CODE) */ DW_PEAK_OFFPEAK_KEY FROM DDS.PEAK_OFF PEAK_DIM WHERE PEAK_OFFPEAK_CODE = :B1 532.4 1 532.43 1.0 3,993.8 13.3 85.6 6t76fd2mj8dam Module: OWB_CCS SELECT /*+ parallel(l,4) index(t) index(c) */ trunc(to_date(dw_da te_time_key,'yyyymmddhh24miss')) transdate, city_name,nvl(tariff_ profile_product,'Postpaid_All') product, (SUM (bill_amount) / 1170 ) bill_amount, sysdate insert_date, user inserted_by ------------------------------------------------------------- SQL ordered by User I/O Wait Time DB/Inst: ZIDW/zidw Snaps: 1333-1334 -> 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 93.6% of Total User I/O Wait Time (s): 57 -> Captured PL/SQL account for 2.5% 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 ---------- ------------ ---------- ------ ---------- ------ ------ ------------- 9,314.0 3 3,104.66 16.3 10,068.6 7.0 92.5 ac2dv445ufwab Module: SQL*Plus SELECT :B2 , DW_SUBSCRIBER_KEY, 'CFT', SUM (CONVERTED_AMOUNT01) * -1 REVENUE FRO M ADS.CFT_FACT WHERE DW_DATE_TIME_KEY = :B1 AND DW_EVENT_CAUSE_KEY IN (36, 37, 4 4, 45) AND DW_SUBSCRIBER_KEY > 0 GROUP BY DW_SUBSCRIBER_KEY UNION ALL SELECT :B2 , DW_SUBSCRIBER_KEY, 'OFFER_CHANGE', COUNT (*) * 2000 REVENUE FROM ADS.CFT_FACT 4,437.7 1 4,437.66 7.8 4,820.4 6.8 92.1 0brw8ckhdtww7 Module: OWB_CCS SELECT /*+ parallel(l,4) index(c) nologging */ trunc(to_dat e(dw_date_time_key,'yyyymmddhh24miss')) transdate, 'VAS' activity_name, rds.get_subscriber_number(dw_subscriber_key) subno, b_subno, rds.get_sub_connection_type(dw_sub_connection_ty 3,851.5 1 3,851.52 6.7 3,938.3 1.2 97.8 8d202unu87sj6 Module: OWB_CCS SELECT /*+ parallel(l,8) */ trunc(to_date(dw_date_time_key,'yyyymmddhh2 4miss')) transdate, rds.get_sub_connection_type(dw_sub_connection_type_key ) subscriber_type, DECODE (tariff_group_code, 'MMS_PP', 'MMS', 'MMS_PS', 'MMS', 3,754.6 1 3,754.64 6.6 3,890.7 2.4 96.5 3bft9p301pagu Module: OWB_CCS SELECT /*+ parallel(l,4) index(t) */ trunc(to_date(dw_date_time_key ,'yyyymmddhh24miss')) transdate, SUM (bill_amount) revenue, sysdate insert_date, user inserted_by, null update_date, null updated_by , '23-JUN-13' business_date, 3,741.4 1 3,741.41 6.6 3,881.4 2.4 96.4 4p8dj2g01ym6y Module: OWB_CCS SELECT /*+ parallel(l,4) index(t) */ trunc(to_date(dw_date_time_key ,'yyyymmddhh24miss')) transdate, b_subno,rds.get_bill_code (dw_bill _code_key) bill_code, rds.get_sub_connection_type (dw_sub_connectio n_type_key) subscriber_type, COUNT (*) no_of_calls, SUM (DURATION) 3,643.7 1 3,643.66 6.4 3,939.4 6.4 92.5 acwr8q576dmsm Module: OWB_CCS SELECT /*+ parallel(l,4) index(c) */ trunc(to_date(dw_date_t ime_key,'yyyymmddhh24miss')) transdate, city_name, rds.get_sub_connection_type (dw_sub_connection _type_key) subscri 3,622.8 1 3,622.80 6.3 4,050.3 9.4 89.4 0gd8bgn1t4yk2 Module: OWB_CCS select /*+parallel(l,8)*/ trunc(to_date(dw_date_time_key,'yyyymmddhh24miss')) tr ansdate, round(sum(bill_amount)/1170) Revenue_USD, null reven ue_type, sysdate insert_date, user inserted_by, null update_d ate, null updated_by, '23-JUN-13' business_date, 'PREPAID' s 3,482.1 1 3,482.13 6.1 3,985.6 11.5 87.4 0df5h3mcgcb3w Module: OWB_CCS SELECT /*+ parallel(l,4) */ trunc(to_date(dw_date_time_key,'yyy ymmddhh24miss')) transdate, dw_calling_operator_key, dw_called_operator_key, dw_call_type_key,dw_sub_connec tion_type_key, dw_tariff_class_key,dw_tariff_profile_ke 3,469.1 1 3,469.11 6.1 3,987.2 11.9 87.0 754rr4kawf3ka Module: OWB_CCS SELECT /*+ parallel(l,4) index(c) nologging */ trunc(to_date(dw_date _time_key,'yyyymmddhh24miss')) transdate, CASE WHEN city_name = 'BA SQL ordered by User I/O Wait Time DB/Inst: ZIDW/zidw Snaps: 1333-1334 -> 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 93.6% of Total User I/O Wait Time (s): 57 -> Captured PL/SQL account for 2.5% 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 ---------- ------------ ---------- ------ ---------- ------ ------ ------------- BIL' THEN 'Hilla' WHEN city_name ='MISSAN' THEN 'Amara' WHEN city_name = 'MUTHANA' THEN 'Samawa' WHEN city_name = 'NINAWA 3,466.7 1 3,466.70 6.1 4,658.0 23.7 74.4 fus3gcdmax6zv Module: OWB_CCS SELECT /*+ parallel(l,4) nologging */ trunc(to_date(dw_date_time_ke y,'yyyymmddhh24miss')) transdate, rds.get_subscriber_number(dw_subs criber_key) subno, sum (bill_amount) bill_amount, sysdate insert_date, user inserted_by, null update_date, 3,417.2 1 3,417.21 6.0 3,993.8 13.3 85.6 6t76fd2mj8dam Module: OWB_CCS SELECT /*+ parallel(l,4) index(t) index(c) */ trunc(to_date(dw_da te_time_key,'yyyymmddhh24miss')) transdate, city_name,nvl(tariff_ profile_product,'Postpaid_All') product, (SUM (bill_amount) / 1170 ) bill_amount, sysdate insert_date, user inserted_by 3,333.6 1 3,333.61 5.8 4,005.0 15.6 83.2 g07cph4xbsstv Module: OWB_CCS SELECT /*+ parallel(l,4) */ trunc(to_date(dw_date_time_key,'yyyymmddhh2 4miss')) transdate, SUM (CASE WHEN dw_tariff_group_key = 48 AND dw_tariff_profile_key = 42 THEN actual_ duration ELSE 0 END / 1024 / 1024 ) ego_mb 1,246.4 2 623.18 2.2 1,295.6 3.9 96.2 4k2303m5k8syc Module: SQL*Plus SELECT /*+parallel(c,8) */ :B2 , ADS.GET_SUBSCRIBER_KEY (SUB_NUMBER) DW_SUBSCRIB ER_KEY, 'ROAM_PREP', SUM (CALL_CHARGE / 100) REVENUE FROM ADS.CAT_FACT C WHERE D W_DATE_TIME_KEY = :B1 AND ROAMING_FLAG = '01' GROUP BY SUB_NUMBER 1,087.7 0 N/A 1.9 1,121.9 2.4 96.9 835r83v4x62k4 Module: SQL*Plus DECLARE v_date DATE := '01-apr-2013'; BEGIN FOR i IN 1 .. 30 LOOP rds.loyalty_program_data.p_load_usage_outgoing_data (v_date); rds.loyal ty_program_data.p_load_usage_intl_minutes_data (v_date); rds.loyalty_progr am_data.p_load_usage_outgoing_sms_data (v_date); rds.loyalty_program_data. 1,082.4 0 N/A 1.9 1,115.4 2.3 97.0 60nuy7pb6fdyv Module: SQL*Plus INSERT /*+ APPEND */INTO RDS.USAGE_TEMP F VALUES (:B1 ,:B2 ,:B3 ,:B4 ,:B5 ,:B6 , :B7 ,:B8 ,:B9 ,:B10 ,:B11 ,:B12 ,:B13 ,:B14 ,:B15 ,:B16 ,:B17 ) 878.9 1 878.91 1.5 1,865.0 49.9 47.1 f30z28tuvg5ax Module: OWB_CCS SELECT /*+ parallel(f,4) */ DW_DATE_TIME_KEY, DW_SUBSCRIBER_KEY, DW_CALLED_OPERA TOR_KEY, DW_CALLING_OPERATOR_KEY, DW_COUNTRY_KEY, DW_CALL_TYPE_KEY, DW_TARIFF_PR OFILE_KEY, DW_CHARGE_TYPE_KEY, DW_TARIFF_GROUP_KEY, DW_TARIFF_CLASS_KEY, DW_FILT ER_RES_KEY, DW_SUBSCRIBER_TYPE_KEY, DW_RATE_TYPE_KEY, DW_CATEGORY_KEY, DW_SUB_CO ------------------------------------------------------------- SQL ordered by Gets DB/Inst: ZIDW/zidw Snaps: 1333-1334 -> 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: 1,007,397,855 -> Captured SQL account for 125.4% of Total Buffer Gets Elapsed Gets Executions per Exec %Total Time (s) %CPU %IO SQL Id ----------- ----------- ------------ ------ ---------- ----- ----- ------------- 1.96147E+08 0 N/A 19.5 11,645.7 98.8 .1 1u116zg71khyq Module: OWB_CCS SELECT /*+ parallel(l,32) */ TO_CHAR(transdate, 'YYYYMMDD')||'000000' dw_date_ time_key, get_subscriber_key (subno) dw_subscriber_key, get_called_operator_key (b_subno,upper(trim(chargetype))) dw_called_oper ator_key, get_calling_operator_key (subno) dw_calling_operator_k 1.94037E+08 0 N/A 19.3 12,017.7 98.7 .1 0jtzdh6qwgrkf Module: OWB_CCS SELECT /*+ parallel(l,32) */ TO_CHAR(transdate, 'YYYYMMDD')||'000000' dw_date_ time_key, get_subscriber_key (subno) dw_subscriber_key, get_called_operator_key (b_subno,upper(trim(chargetype))) dw_called_oper ator_key, get_calling_operator_key (subno) dw_calling_operator_k 1.83536E+08 0 N/A 18.2 1,081.1 87.3 9 1bcjupk9s6f3q Module: OWB_CCS declare begin "P_CALL_DLY_LC_DT_CELL_TRFC_DTA"(); end; 1.80711E+08 0 N/A 17.9 10,936.6 98.5 .2 61rzu5641tg0n Module: OWB_CCS SELECT /*+ parallel(l,32) */ TO_CHAR(transdate, 'YYYYMMDD')||'000000' dw_date_ time_key, get_subscriber_key (subno) dw_subscriber_key, get_called_operator_key (b_subno,upper(trim(chargetype))) dw_called_oper ator_key, get_calling_operator_key (subno) dw_calling_operator_k 1.61431E+08 46,514,556 3.5 16.0 3,529.1 73.7 10.5 7aqsa764h80wz Module: OWB_CCS SELECT /*+ index(s) */ SUBNO FROM DDS.SUBSCRIBER_DIM S WHERE DW_SUBSCRIBER_KEY = :B1 78,180,683 1 7.818068E+07 7.8 2,942.3 79.3 1.8 6a78wuys8hrjg Module: SQL*Plus SELECT /*+ parallel(t,16) */ :B1 , DW_SUBSCRIBER_KEY, RDS.GET_SUBSCRIBER_NUMBER (DW_SUBSCRIBER_KEY) SUBNO, SUM (TOTAL_REVENUE) TOTAL_REVENUE, SYSDATE INSERT_DAT E, USER INSERTED_BY, NULL UPDATE_DATE, NULL UPDATED_BY, :B1 BUSINESS_DATE, 'ARPU _TEMP' SOURCE_NAME FROM ADS.ARPU_TEMP T GROUP BY DW_SUBSCRIBER_KEY 48,736,246 12,185,312 4.0 4.8 733.1 97.6 .7 c3r4g8knwqdy4 Module: OWB_CCS SELECT /*+ index(a) */ DW_SUBSCRIBER_KEY FROM DDS.SUBSCRIBER_DIM A WHERE SUBNO = TO_CHAR(:B1 ) 48,615,060 12,160,966 4.0 4.8 733.7 99.5 0 gvy9dpb3srgxq Module: OWB_CCS SELECT DW_SUBSCRIBER_CONTRACT_KEY FROM DDS.SUBSCRIBER_CONTRACT_DIM C WHERE CONTR ACT_NUMBER = :B1 48,553,612 12,187,233 4.0 4.8 4,552.5 99.6 0 2bhmgk5bcfgmd Module: OWB_CCS SELECT /*+ index(o,idx_operator_code_flag_source) */ DW_OPERATOR_KEY FROM DDS.OP ERATOR_DIM O WHERE OPERATOR_CODE LIKE SUBSTR (:B1 , 1, LENGTH (OPERATOR_CODE)) A ND OPERATOR_FLAG = 'L' AND SOURCE_NAME = '1' 36,513,117 12,179,759 3.0 3.6 475.1 97.5 0 5bk3rdmw87rm7 Module: OWB_CCS SELECT /*+ index(t) */ DW_TARIFF_GROUP_KEY FROM DDS.TARIFF_GROUP_DIM T WHERE TAR IFF_GROUP_CODE = :B1 36,427,089 12,175,780 3.0 3.6 492.7 98 0 a9vdnkjjykarr Module: OWB_CCS SQL ordered by Gets DB/Inst: ZIDW/zidw Snaps: 1333-1334 -> 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: 1,007,397,855 -> Captured SQL account for 125.4% of Total Buffer Gets Elapsed Gets Executions per Exec %Total Time (s) %CPU %IO SQL Id ----------- ----------- ------------ ------ ---------- ----- ----- ------------- SELECT /*+ index(b) */ DW_BILL_CODE_KEY FROM DDS.BILL_CODE_DIM B WHERE BILL_CODE = :B1 33,781,603 9,135,500 3.7 3.4 5,159.7 99.5 0 2pj55mj4qqby8 Module: OWB_CCS SELECT /*+ index(o,idx_operator_code_flag)*/ DW_OPERATOR_KEY FROM DDS.OPERATOR_D IM O WHERE OPERATOR_CODE = SUBSTR (:B1 , 1, LENGTH (OPERATOR_CODE)) AND OPERATOR _FLAG = 'L' AND SOURCE_NAME IS NULL 33,339,950 11,144,022 3.0 3.3 559.5 98.6 0 fp5yksqm8b61h Module: OWB_CCS SELECT /*+ index (c) */ DW_CELL_ID_KEY FROM DDS.CELL_ID_DIM C WHERE CELL_ID_CODE = :B1 AND ACTIVE_YN = 'Y' 32,453,647 1 3.245365E+07 3.2 4,658.0 23.7 74.4 fus3gcdmax6zv Module: OWB_CCS SELECT /*+ parallel(l,4) nologging */ trunc(to_date(dw_date_time_ke y,'yyyymmddhh24miss')) transdate, rds.get_subscriber_number(dw_subs criber_key) subno, sum (bill_amount) bill_amount, sysdate insert_date, user inserted_by, null update_date, 25,723,594 11,431,972 2.3 2.6 478.0 98.5 0 3cs7b4z78g6ur Module: OWB_CCS SELECT /*+ index(a) */ DW_ACTIVITY_KEY FROM DDS.ACTIVITY_DIM A WHERE ACTIVITY_CO DE = :B1 24,370,823 12,222,774 2.0 2.4 4,162.7 97.2 0 5fcy78kux8bju Module: OWB_CCS SELECT /*+ index(c,idx_country_code) */ DW_COUNTRY_KEY FROM DDS.COUNTRY_DIM C WH ERE COUNTRY_CODE = SUBSTR (:B1 , 1, LENGTH (COUNTRY_CODE)) 24,312,382 12,162,116 2.0 2.4 475.2 98.7 0 c9sh5yk0sdjng Module: OWB_CCS SELECT /*+ index(c) */ DW_CATEGORY_KEY FROM DDS.CATEGORY_DIM C WHERE CATEGORY_CO DE = :B1 24,217,518 12,191,402 2.0 2.4 554.5 98.7 0 1sjk4ftb4xmc9 Module: OWB_CCS SELECT /*+ index(IDX_PEAK_OFFPEAK_CODE) */ DW_PEAK_OFFPEAK_KEY FROM DDS.PEAK_OFF PEAK_DIM WHERE PEAK_OFFPEAK_CODE = :B1 ------------------------------------------------------------- SQL ordered by Reads DB/Inst: ZIDW/zidw Snaps: 1333-1334 -> %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: 35,744,164 -> Captured SQL account for 75.0% of Total Physical Reads Elapsed Reads Executions per Exec %Total Time (s) %CPU %IO SQL Id ----------- ----------- ---------- ------ ---------- ------ ------ ------------- 6,785,494 3 2.2618E+06 19.0 10,068.6 7.0 92.5 ac2dv445ufwab Module: SQL*Plus SELECT :B2 , DW_SUBSCRIBER_KEY, 'CFT', SUM (CONVERTED_AMOUNT01) * -1 REVENUE FRO M ADS.CFT_FACT WHERE DW_DATE_TIME_KEY = :B1 AND DW_EVENT_CAUSE_KEY IN (36, 37, 4 4, 45) AND DW_SUBSCRIBER_KEY > 0 GROUP BY DW_SUBSCRIBER_KEY UNION ALL SELECT :B2 , DW_SUBSCRIBER_KEY, 'OFFER_CHANGE', COUNT (*) * 2000 REVENUE FROM ADS.CFT_FACT 2,243,242 1 2.2432E+06 6.3 4,820.4 6.8 92.1 0brw8ckhdtww7 Module: OWB_CCS SELECT /*+ parallel(l,4) index(c) nologging */ trunc(to_dat e(dw_date_time_key,'yyyymmddhh24miss')) transdate, 'VAS' activity_name, rds.get_subscriber_number(dw_subscriber_key) subno, b_subno, rds.get_sub_connection_type(dw_sub_connection_ty 2,100,425 1 2.1004E+06 5.9 666.5 88.0 6.2 dumby3puzszwy Module: nqsserver@tabssb08 (TNS V1-V3) /* 7fe4c811 */WITH SAWITH0 AS (select sum(T48095.BILL_AMOUNT) as c1, T4878 3.CITY_NAME as c2, T48434.DAY as c3, T48731.SUB_CONNECTION_TYPE as c4 from SUB_CONNECTION_TYPE_DIM T48731, CELL_ID_DIM T48783, DATE_TI ME_DIM T48434, LC_DETAILS_FACT T48095 where ( T48095.DW_CELL_ID_KEY = T487 1,799,797 2 899,898.5 5.0 1,295.6 3.9 96.2 4k2303m5k8syc Module: SQL*Plus SELECT /*+parallel(c,8) */ :B2 , ADS.GET_SUBSCRIBER_KEY (SUB_NUMBER) DW_SUBSCRIB ER_KEY, 'ROAM_PREP', SUM (CALL_CHARGE / 100) REVENUE FROM ADS.CAT_FACT C WHERE D W_DATE_TIME_KEY = :B1 AND ROAMING_FLAG = '01' GROUP BY SUB_NUMBER 1,111,474 1 1.1115E+06 3.1 4,658.0 23.7 74.4 fus3gcdmax6zv Module: OWB_CCS SELECT /*+ parallel(l,4) nologging */ trunc(to_date(dw_date_time_ke y,'yyyymmddhh24miss')) transdate, rds.get_subscriber_number(dw_subs criber_key) subno, sum (bill_amount) bill_amount, sysdate insert_date, user inserted_by, null update_date, 1,110,586 1 1.1106E+06 3.1 3,987.2 11.9 87.0 754rr4kawf3ka Module: OWB_CCS SELECT /*+ parallel(l,4) index(c) nologging */ trunc(to_date(dw_date _time_key,'yyyymmddhh24miss')) transdate, CASE WHEN city_name = 'BA BIL' THEN 'Hilla' WHEN city_name ='MISSAN' THEN 'Amara' WHEN city_name = 'MUTHANA' THEN 'Samawa' WHEN city_name = 'NINAWA 1,110,509 1 1.1105E+06 3.1 3,993.8 13.3 85.6 6t76fd2mj8dam Module: OWB_CCS SELECT /*+ parallel(l,4) index(t) index(c) */ trunc(to_date(dw_da te_time_key,'yyyymmddhh24miss')) transdate, city_name,nvl(tariff_ profile_product,'Postpaid_All') product, (SUM (bill_amount) / 1170 ) bill_amount, sysdate insert_date, user inserted_by 1,110,500 1 1.1105E+06 3.1 3,938.3 1.2 97.8 8d202unu87sj6 Module: OWB_CCS SELECT /*+ parallel(l,8) */ trunc(to_date(dw_date_time_key,'yyyymmddhh2 4miss')) transdate, rds.get_sub_connection_type(dw_sub_connection_type_key ) subscriber_type, DECODE (tariff_group_code, 'MMS_PP', 'MMS', 'MMS_PS', 'MMS', 1,110,499 1 1.1105E+06 3.1 4,050.3 9.4 89.4 0gd8bgn1t4yk2 Module: OWB_CCS select /*+parallel(l,8)*/ trunc(to_date(dw_date_time_key,'yyyymmddhh24miss')) tr ansdate, round(sum(bill_amount)/1170) Revenue_USD, null reven ue_type, sysdate insert_date, user inserted_by, null update_d SQL ordered by Reads DB/Inst: ZIDW/zidw Snaps: 1333-1334 -> %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: 35,744,164 -> Captured SQL account for 75.0% of Total Physical Reads Elapsed Reads Executions per Exec %Total Time (s) %CPU %IO SQL Id ----------- ----------- ---------- ------ ---------- ------ ------ ------------- ate, null updated_by, '23-JUN-13' business_date, 'PREPAID' s 1,110,497 1 1.1105E+06 3.1 3,881.4 2.4 96.4 4p8dj2g01ym6y Module: OWB_CCS SELECT /*+ parallel(l,4) index(t) */ trunc(to_date(dw_date_time_key ,'yyyymmddhh24miss')) transdate, b_subno,rds.get_bill_code (dw_bill _code_key) bill_code, rds.get_sub_connection_type (dw_sub_connectio n_type_key) subscriber_type, COUNT (*) no_of_calls, SUM (DURATION) 1,110,496 1 1.1105E+06 3.1 3,890.7 2.4 96.5 3bft9p301pagu Module: OWB_CCS SELECT /*+ parallel(l,4) index(t) */ trunc(to_date(dw_date_time_key ,'yyyymmddhh24miss')) transdate, SUM (bill_amount) revenue, sysdate insert_date, user inserted_by, null update_date, null updated_by , '23-JUN-13' business_date, 1,110,496 1 1.1105E+06 3.1 3,939.4 6.4 92.5 acwr8q576dmsm Module: OWB_CCS SELECT /*+ parallel(l,4) index(c) */ trunc(to_date(dw_date_t ime_key,'yyyymmddhh24miss')) transdate, city_name, rds.get_sub_connection_type (dw_sub_connection _type_key) subscri 1,110,492 1 1.1105E+06 3.1 3,985.6 11.5 87.4 0df5h3mcgcb3w Module: OWB_CCS SELECT /*+ parallel(l,4) */ trunc(to_date(dw_date_time_key,'yyy ymmddhh24miss')) transdate, dw_calling_operator_key, dw_called_operator_key, dw_call_type_key,dw_sub_connec tion_type_key, dw_tariff_class_key,dw_tariff_profile_ke 1,110,200 1 1.1102E+06 3.1 4,005.0 15.6 83.2 g07cph4xbsstv Module: OWB_CCS SELECT /*+ parallel(l,4) */ trunc(to_date(dw_date_time_key,'yyyymmddhh2 4miss')) transdate, SUM (CASE WHEN dw_tariff_group_key = 48 AND dw_tariff_profile_key = 42 THEN actual_ duration ELSE 0 END / 1024 / 1024 ) ego_mb 1,051,512 3 350,504.0 2.9 1,600.1 79.4 13.1 1kgg2dd6mwdcd Module: SQL*Plus SELECT :B2 , DW_SUBSCRIBER_KEY, 'LC_DETAILS', SUM (BILL_AMOUNT) REVENUE FROM ADS .LC_DETAILS_FACT L WHERE DW_DATE_TIME_KEY = :B1 AND DW_SUB_CONNECTION_TYPE_KEY = 1 AND DW_SUBSCRIBER_KEY > 0 GROUP BY DW_SUBSCRIBER_KEY 656,479 1 656,479.0 1.8 1,865.0 49.9 47.1 f30z28tuvg5ax Module: OWB_CCS SELECT /*+ parallel(f,4) */ DW_DATE_TIME_KEY, DW_SUBSCRIBER_KEY, DW_CALLED_OPERA TOR_KEY, DW_CALLING_OPERATOR_KEY, DW_COUNTRY_KEY, DW_CALL_TYPE_KEY, DW_TARIFF_PR OFILE_KEY, DW_CHARGE_TYPE_KEY, DW_TARIFF_GROUP_KEY, DW_TARIFF_CLASS_KEY, DW_FILT ER_RES_KEY, DW_SUBSCRIBER_TYPE_KEY, DW_RATE_TYPE_KEY, DW_CATEGORY_KEY, DW_SUB_CO 645,436 0 N/A 1.8 1,037.3 73.0 21.2 74n90xwzzhcs5 Module: SQL*Plus DECLARE v_date DATE := '01-apr-2013'; BEGIN FOR i IN 1 .. 30 LOOP ads.daily_subs_arpu_data.p_main (v_date); v_date := v_date + 1; END LOOP; END; ------------------------------------------------------------- SQL ordered by Physical Reads (UnOptimized)DB/Inst: ZIDW/zidw Snaps: 1333-13 -> 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: 679,473 -> Captured SQL account for 52.8% of Total -> Total UnOptimized Read Requests: 679,473 -> Captured SQL account for 52.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 ----------- ----------- ---------- ------------ ------ ------ ------------- 51,685 51,685 0 N/A 0.0 7.6 835r83v4x62k4 Module: SQL*Plus DECLARE v_date DATE := '01-apr-2013'; BEGIN FOR i IN 1 .. 30 LOOP rds.loyalty_program_data.p_load_usage_outgoing_data (v_date); rds.loyal ty_program_data.p_load_usage_intl_minutes_data (v_date); rds.loyalty_progr am_data.p_load_usage_outgoing_sms_data (v_date); rds.loyalty_program_data. 51,606 51,606 0 N/A 0.0 7.6 60nuy7pb6fdyv Module: SQL*Plus INSERT /*+ APPEND */INTO RDS.USAGE_TEMP F VALUES (:B1 ,:B2 ,:B3 ,:B4 ,:B5 ,:B6 , :B7 ,:B8 ,:B9 ,:B10 ,:B11 ,:B12 ,:B13 ,:B14 ,:B15 ,:B16 ,:B17 ) 39,940 39,940 1 39,940.0 0.0 5.9 0brw8ckhdtww7 Module: OWB_CCS SELECT /*+ parallel(l,4) index(c) nologging */ trunc(to_dat e(dw_date_time_key,'yyyymmddhh24miss')) transdate, 'VAS' activity_name, rds.get_subscriber_number(dw_subscriber_key) subno, b_subno, rds.get_sub_connection_type(dw_sub_connection_ty 32,784 32,784 1 32,784.0 0.0 4.8 dumby3puzszwy Module: nqsserver@tabssb08 (TNS V1-V3) /* 7fe4c811 */WITH SAWITH0 AS (select sum(T48095.BILL_AMOUNT) as c1, T4878 3.CITY_NAME as c2, T48434.DAY as c3, T48731.SUB_CONNECTION_TYPE as c4 from SUB_CONNECTION_TYPE_DIM T48731, CELL_ID_DIM T48783, DATE_TI ME_DIM T48434, LC_DETAILS_FACT T48095 where ( T48095.DW_CELL_ID_KEY = T487 31,486 31,486 4.6515E+07 0.0 0.0 4.6 7aqsa764h80wz Module: OWB_CCS SELECT /*+ index(s) */ SUBNO FROM DDS.SUBSCRIBER_DIM S WHERE DW_SUBSCRIBER_KEY = :B1 18,358 18,358 1 18,358.0 0.0 2.7 fus3gcdmax6zv Module: OWB_CCS SELECT /*+ parallel(l,4) nologging */ trunc(to_date(dw_date_time_ke y,'yyyymmddhh24miss')) transdate, rds.get_subscriber_number(dw_subs criber_key) subno, sum (bill_amount) bill_amount, sysdate insert_date, user inserted_by, null update_date, 17,470 17,470 1 17,470.0 0.0 2.6 754rr4kawf3ka Module: OWB_CCS SELECT /*+ parallel(l,4) index(c) nologging */ trunc(to_date(dw_date _time_key,'yyyymmddhh24miss')) transdate, CASE WHEN city_name = 'BA BIL' THEN 'Hilla' WHEN city_name ='MISSAN' THEN 'Amara' WHEN city_name = 'MUTHANA' THEN 'Samawa' WHEN city_name = 'NINAWA 17,398 17,398 1 17,398.0 0.0 2.6 g07cph4xbsstv Module: OWB_CCS SELECT /*+ parallel(l,4) */ trunc(to_date(dw_date_time_key,'yyyymmddhh2 4miss')) transdate, SUM (CASE WHEN dw_tariff_group_key = 48 AND dw_tariff_profile_key = 42 THEN actual_ duration ELSE 0 END / 1024 / 1024 ) ego_mb 17,393 17,393 1 17,393.0 0.0 2.6 6t76fd2mj8dam Module: OWB_CCS SELECT /*+ parallel(l,4) index(t) index(c) */ trunc(to_date(dw_da te_time_key,'yyyymmddhh24miss')) transdate, city_name,nvl(tariff_ profile_product,'Postpaid_All') product, (SUM (bill_amount) / 1170 ) bill_amount, sysdate insert_date, user inserted_by 17,384 17,384 1 17,384.0 0.0 2.6 8d202unu87sj6 SQL ordered by Physical Reads (UnOptimized)DB/Inst: ZIDW/zidw Snaps: 1333-13 -> 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: 679,473 -> Captured SQL account for 52.8% of Total -> Total UnOptimized Read Requests: 679,473 -> Captured SQL account for 52.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 ----------- ----------- ---------- ------------ ------ ------ ------------- Module: OWB_CCS SELECT /*+ parallel(l,8) */ trunc(to_date(dw_date_time_key,'yyyymmddhh2 4miss')) transdate, rds.get_sub_connection_type(dw_sub_connection_type_key ) subscriber_type, DECODE (tariff_group_code, 'MMS_PP', 'MMS', 'MMS_PS', 'MMS', 17,383 17,383 1 17,383.0 0.0 2.6 0gd8bgn1t4yk2 Module: OWB_CCS select /*+parallel(l,8)*/ trunc(to_date(dw_date_time_key,'yyyymmddhh24miss')) tr ansdate, round(sum(bill_amount)/1170) Revenue_USD, null reven ue_type, sysdate insert_date, user inserted_by, null update_d ate, null updated_by, '23-JUN-13' business_date, 'PREPAID' s 17,381 17,381 1 17,381.0 0.0 2.6 4p8dj2g01ym6y Module: OWB_CCS SELECT /*+ parallel(l,4) index(t) */ trunc(to_date(dw_date_time_key ,'yyyymmddhh24miss')) transdate, b_subno,rds.get_bill_code (dw_bill _code_key) bill_code, rds.get_sub_connection_type (dw_sub_connectio n_type_key) subscriber_type, COUNT (*) no_of_calls, SUM (DURATION) 17,380 17,380 1 17,380.0 0.0 2.6 3bft9p301pagu Module: OWB_CCS SELECT /*+ parallel(l,4) index(t) */ trunc(to_date(dw_date_time_key ,'yyyymmddhh24miss')) transdate, SUM (bill_amount) revenue, sysdate insert_date, user inserted_by, null update_date, null updated_by , '23-JUN-13' business_date, 17,380 17,380 1 17,380.0 0.0 2.6 acwr8q576dmsm Module: OWB_CCS SELECT /*+ parallel(l,4) index(c) */ trunc(to_date(dw_date_t ime_key,'yyyymmddhh24miss')) transdate, city_name, rds.get_sub_connection_type (dw_sub_connection _type_key) subscri 17,376 17,376 1 17,376.0 0.0 2.6 0df5h3mcgcb3w Module: OWB_CCS SELECT /*+ parallel(l,4) */ trunc(to_date(dw_date_time_key,'yyy ymmddhh24miss')) transdate, dw_calling_operator_key, dw_called_operator_key, dw_call_type_key,dw_sub_connec tion_type_key, dw_tariff_class_key,dw_tariff_profile_ke 12,744 12,744 0 N/A 0.0 1.9 74n90xwzzhcs5 Module: SQL*Plus DECLARE v_date DATE := '01-apr-2013'; BEGIN FOR i IN 1 .. 30 LOOP ads.daily_subs_arpu_data.p_main (v_date); v_date := v_date + 1; END LOOP; END; 10,726 10,726 1 10,726.0 0.0 1.6 f30z28tuvg5ax Module: OWB_CCS SELECT /*+ parallel(f,4) */ DW_DATE_TIME_KEY, DW_SUBSCRIBER_KEY, DW_CALLED_OPERA TOR_KEY, DW_CALLING_OPERATOR_KEY, DW_COUNTRY_KEY, DW_CALL_TYPE_KEY, DW_TARIFF_PR OFILE_KEY, DW_CHARGE_TYPE_KEY, DW_TARIFF_GROUP_KEY, DW_TARIFF_CLASS_KEY, DW_FILT ER_RES_KEY, DW_SUBSCRIBER_TYPE_KEY, DW_RATE_TYPE_KEY, DW_CATEGORY_KEY, DW_SUB_CO ------------------------------------------------------------- SQL ordered by Executions DB/Inst: ZIDW/zidw Snaps: 1333-1334 -> %CPU - CPU Time as a percentage of Elapsed Time -> %IO - User I/O Time as a percentage of Elapsed Time -> Total Executions: 282,047,378 -> Captured SQL account for 62.3% of Total Elapsed Executions Rows Processed Rows per Exec Time (s) %CPU %IO SQL Id ------------ --------------- -------------- ---------- ----- ----- ------------- 46,514,556 41,923,010 0.9 3,529.1 73.7 10.5 7aqsa764h80wz Module: OWB_CCS SELECT /*+ index(s) */ SUBNO FROM DDS.SUBSCRIBER_DIM S WHERE DW_SUBSCRIBER_KEY = :B1 12,222,774 12,212,410 1.0 4,162.7 97.2 0 5fcy78kux8bju Module: OWB_CCS SELECT /*+ index(c,idx_country_code) */ DW_COUNTRY_KEY FROM DDS.COUNTRY_DIM C WH ERE COUNTRY_CODE = SUBSTR (:B1 , 1, LENGTH (COUNTRY_CODE)) 12,191,402 12,146,219 1.0 554.5 98.7 0 1sjk4ftb4xmc9 Module: OWB_CCS SELECT /*+ index(IDX_PEAK_OFFPEAK_CODE) */ DW_PEAK_OFFPEAK_KEY FROM DDS.PEAK_OFF PEAK_DIM WHERE PEAK_OFFPEAK_CODE = :B1 12,187,233 12,142,684 1.0 4,552.5 99.6 0 2bhmgk5bcfgmd Module: OWB_CCS SELECT /*+ index(o,idx_operator_code_flag_source) */ DW_OPERATOR_KEY FROM DDS.OP ERATOR_DIM O WHERE OPERATOR_CODE LIKE SUBSTR (:B1 , 1, LENGTH (OPERATOR_CODE)) A ND OPERATOR_FLAG = 'L' AND SOURCE_NAME = '1' 12,185,312 12,187,923 1.0 733.1 97.6 .7 c3r4g8knwqdy4 Module: OWB_CCS SELECT /*+ index(a) */ DW_SUBSCRIBER_KEY FROM DDS.SUBSCRIBER_DIM A WHERE SUBNO = TO_CHAR(:B1 ) 12,179,759 12,176,339 1.0 475.1 97.5 0 5bk3rdmw87rm7 Module: OWB_CCS SELECT /*+ index(t) */ DW_TARIFF_GROUP_KEY FROM DDS.TARIFF_GROUP_DIM T WHERE TAR IFF_GROUP_CODE = :B1 12,175,780 12,175,435 1.0 492.7 98 0 a9vdnkjjykarr Module: OWB_CCS SELECT /*+ index(b) */ DW_BILL_CODE_KEY FROM DDS.BILL_CODE_DIM B WHERE BILL_CODE = :B1 12,162,116 12,182,243 1.0 475.2 98.7 0 c9sh5yk0sdjng Module: OWB_CCS SELECT /*+ index(c) */ DW_CATEGORY_KEY FROM DDS.CATEGORY_DIM C WHERE CATEGORY_CO DE = :B1 12,160,966 12,171,616 1.0 733.7 99.5 0 gvy9dpb3srgxq Module: OWB_CCS SELECT DW_SUBSCRIBER_CONTRACT_KEY FROM DDS.SUBSCRIBER_CONTRACT_DIM C WHERE CONTR ACT_NUMBER = :B1 11,431,972 1,902,570 0.2 478.0 98.5 0 3cs7b4z78g6ur Module: OWB_CCS SELECT /*+ index(a) */ DW_ACTIVITY_KEY FROM DDS.ACTIVITY_DIM A WHERE ACTIVITY_CO DE = :B1 11,144,022 11,112,364 1.0 559.5 98.6 0 fp5yksqm8b61h Module: OWB_CCS SELECT /*+ index (c) */ DW_CELL_ID_KEY FROM DDS.CELL_ID_DIM C WHERE CELL_ID_CODE = :B1 AND ACTIVE_YN = 'Y' 9,135,500 6,682,973 0.7 5,159.7 99.5 0 2pj55mj4qqby8 Module: OWB_CCS SELECT /*+ index(o,idx_operator_code_flag)*/ DW_OPERATOR_KEY FROM DDS.OPERATOR_D IM O WHERE OPERATOR_CODE = SUBSTR (:B1 , 1, LENGTH (OPERATOR_CODE)) AND OPERATOR SQL ordered by Executions DB/Inst: ZIDW/zidw Snaps: 1333-1334 -> %CPU - CPU Time as a percentage of Elapsed Time -> %IO - User I/O Time as a percentage of Elapsed Time -> Total Executions: 282,047,378 -> Captured SQL account for 62.3% of Total Elapsed Executions Rows Processed Rows per Exec Time (s) %CPU %IO SQL Id ------------ --------------- -------------- ---------- ----- ----- ------------- _FLAG = 'L' AND SOURCE_NAME IS NULL ------------------------------------------------------------- SQL ordered by Parse Calls DB/Inst: ZIDW/zidw Snaps: 1333-1334 -> Total Parse Calls: 7,870 -> Captured SQL account for 37.4% of Total % Total Parse Calls Executions Parses SQL Id ------------ ------------ --------- ------------- 227 227 2.88 350f5yrnnmshs lock table sys.mon_mods$ in exclusive mode nowait 225 3 2.86 ac2dv445ufwab Module: SQL*Plus SELECT :B2 , DW_SUBSCRIBER_KEY, 'CFT', SUM (CONVERTED_AMOUNT01) * -1 REVENUE FRO M ADS.CFT_FACT WHERE DW_DATE_TIME_KEY = :B1 AND DW_EVENT_CAUSE_KEY IN (36, 37, 4 4, 45) AND DW_SUBSCRIBER_KEY > 0 GROUP BY DW_SUBSCRIBER_KEY UNION ALL SELECT :B2 , DW_SUBSCRIBER_KEY, 'OFFER_CHANGE', COUNT (*) * 2000 REVENUE FROM ADS.CFT_FACT 157 157 1.99 459f3z9u4fb3u select value$ from props$ where name = 'GLOBAL_DB_NAME' 156 156 1.98 0ws7ahf1d78qa select SYS_CONTEXT('USERENV', 'SERVER_HOST'), SYS_CONTEXT('USERENV', 'DB_UNIQUE_ NAME'), SYS_CONTEXT('USERENV', 'INSTANCE_NAME'), SYS_CONTEXT('USERENV', 'SERVICE _NAME'), INSTANCE_NUMBER, STARTUP_TIME, SYS_CONTEXT('USERENV', 'DB_DOMAIN') from v$instance where INSTANCE_NAME=SYS_CONTEXT('USERENV', 'INSTANCE_NAME') 109 173 1.39 28f86c7w594z6 SELECT WORKSPACE_ID FROM WORKSPACE_ASSIGNMENT WHERE UPPER(WORKSPACE_NAME ) = UPPER(:b1 ) AND UPPER(USER_NAME ) = UPPER(:b2 ) AND ISWORKSPACEOWNER = ' 1' 99 1,425 1.26 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 82 24,009 1.04 2sktvqfxpwxba SELECT SYS_CONTEXT('owb_workspace' , 'workspaceID' ) FROM DUAL 79 79 1.00 19x1189chq3xd SELECT LOCKID FROM DBMS_LOCK_ALLOCATED WHERE NAME = :B1 FOR UPDATE 79 79 1.00 ak5crjygnpk60 UPDATE DBMS_LOCK_ALLOCATED SET EXPIRATION = SYSDATE + (:B1 /86400) WHERE ROWID = :B2 78 110 0.99 dvmm43scyzqqg SELECT COUNT (*) FROM CMPWORKSPACE_V ------------------------------------------------------------- SQL ordered by Sharable Memory DB/Inst: ZIDW/zidw Snaps: 1333-1334 No data exists for this section of the report. ------------------------------------------------------------- SQL ordered by Version Count DB/Inst: ZIDW/zidw Snaps: 1333-1334 -> Only Statements with Version Count greater than 20 are displayed Version Count Executions SQL Id -------- ------------ ------------- 72 43 7ng34ruy5awxq select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags,i.property,i.pctf ree$,i.initrans,i.maxtrans,i.blevel,i.leafcnt,i.distkey,i.lblkkey,i.dblkkey,i.cl ufac,i.cols,i.analyzetime,i.samplesize,i.dataobj#,nvl(i.degree,1),nvl(i.instance s,1),i.rowcnt,mod(i.pctthres$,256),i.indmethod#,i.trunccnt,nvl(c.unicols,0),nvl( 72 43 7ng34ruy5awxq select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags,i.property,i.pctf ree$,i.initrans,i.maxtrans,i.blevel,i.leafcnt,i.distkey,i.lblkkey,i.dblkkey,i.cl ufac,i.cols,i.analyzetime,i.samplesize,i.dataobj#,nvl(i.degree,1),nvl(i.instance s,1),i.rowcnt,mod(i.pctthres$,256),i.indmethod#,i.trunccnt,nvl(c.unicols,0),nvl( 69 68 83taa7kaw59c1 select name,intcol#,segcol#,type#,length,nvl(precision#,0),decode(type#,2,nvl(sc ale,-127/*MAXSB1MINAL*/),178,scale,179,scale,180,scale,181,scale,182,scale,183,s cale,231,scale,0),null$,fixedstorage,nvl(deflength,0),default$,rowid,col#,proper ty, nvl(charsetid,0),nvl(charsetform,0),spare1,spare2,nvl(spare3,0) from col$ wh 51 68 2q93zsrvbdw48 select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0),2))from objauth$ w here obj#=:1 group by grantee#,privilege#,nvl(col#,0) order by grantee# 51 44 9dgfmx91mq3gr Module: JDBC Thin Client select granted_role from user_role_privs where default_role = 'YES' or granted_r ole in ('CONNECT', 'RESOURCE', 'DBA', 'OWB_USER' ) 49 20 1gu8t96d0bdmu select t.ts#,t.file#,t.block#,nvl(t.bobj#,0),nvl(t.tab#,0),t.intcols,nvl(t.cluco ls,0),t.audit$,t.flags,t.pctfree$,t.pctused$,t.initrans,t.maxtrans,t.rowcnt,t.bl kcnt,t.empcnt,t.avgspc,t.chncnt,t.avgrln,t.analyzetime,t.samplesize,t.cols,t.pro perty,nvl(t.degree,1),nvl(t.instances,1),t.avgspc_flb,t.flbcnt,t.kernelcols,nvl( 49 20 1gu8t96d0bdmu select t.ts#,t.file#,t.block#,nvl(t.bobj#,0),nvl(t.tab#,0),t.intcols,nvl(t.cluco ls,0),t.audit$,t.flags,t.pctfree$,t.pctused$,t.initrans,t.maxtrans,t.rowcnt,t.bl kcnt,t.empcnt,t.avgspc,t.chncnt,t.avgrln,t.analyzetime,t.samplesize,t.cols,t.pro perty,nvl(t.degree,1),nvl(t.instances,1),t.avgspc_flb,t.flbcnt,t.kernelcols,nvl( 46 3,244 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 40 67 6aq34nj2zb2n7 select col#, grantee#, privilege#,max(mod(nvl(option$,0),2)) from objauth$ where obj#=:1 and col# is not null group by privilege#, col#, grantee# order by col#, grantee# 36 235 0dcu16x7126p9 SELECT ELEMENTID FROM CMPWBUSER_V WHERE UPPER(NAME ) = UPPER(USER ) 36 26 5n1fs4m2n2y0r select pos#,intcol#,col#,spare1,bo#,spare2,spare3 from icol$ where obj#=:1 35 63 8swypbbr0m372 select order#,columns,types from access$ where d_obj#=:1 35 52 gx4mv66pvj3xz select con#,type#,condlength,intcols,robj#,rcon#,match#,refact,nvl(enabled,0),ro SQL ordered by Version Count DB/Inst: ZIDW/zidw Snaps: 1333-1334 -> Only Statements with Version Count greater than 20 are displayed Version Count Executions SQL Id -------- ------------ ------------- wid,cols,nvl(defer,0),mtime,nvl(spare1,0),spare2,spare3 from cdef$ where obj#=:1 34 79 19x1189chq3xd SELECT LOCKID FROM DBMS_LOCK_ALLOCATED WHERE NAME = :B1 FOR UPDATE 33 52 3w4qs0tbpmxr6 select con#,obj#,rcon#,enabled,nvl(defer,0),spare2,spare3 from cdef$ where robj# =:1 33 68 ga9j9xk5cy9s0 select /*+ index(idl_sb4$ i_idl_sb41) +*/ piece#,length,piece from idl_sb4$ wher e obj#=:1 and part=:2 and version=:3 order by piece# 32 22 0fr8zhn4ymu3v select intcol#,type,flags,lobcol,objcol,extracol,schemaoid, elemnum from opqtyp e$ where obj# = :1 order by intcol# asc 32 22 0fr8zhn4ymu3v select intcol#,type,flags,lobcol,objcol,extracol,schemaoid, elemnum from opqtyp e$ where obj# = :1 order by intcol# asc 29 68 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# 29 63 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# 29 68 c6awqs517jpj0 select /*+ index(idl_char$ i_idl_char1) +*/ piece#,length,piece from idl_char$ w here obj#=:1 and part=:2 and version=:3 order by piece# 29 68 cvn54b7yz0s8u select /*+ index(idl_ub1$ i_idl_ub11) +*/ piece#,length,piece from idl_ub1$ wher e obj#=:1 and part=:2 and version=:3 order by piece# 28 173 28f86c7w594z6 SELECT WORKSPACE_ID FROM WORKSPACE_ASSIGNMENT WHERE UPPER(WORKSPACE_NAME ) = UPPER(:b1 ) AND UPPER(USER_NAME ) = UPPER(:b2 ) AND ISWORKSPACEOWNER = ' 1' 28 22 6qz82dptj0qr7 select l.col#, l.intcol#, l.lobj#, l.ind#, l.ts#, l.file#, l.block#, l.chunk, l. pctversion$, l.flags, l.property, l.retention, l.freepools from lob$ l where l.o bj# = :1 order by l.intcol# asc 28 5 a14wcsg9mk8j3 WITH a as (SELECT a$.dbid, a$.snap_id, a$.INSTANCE_NUMBER, a$.PLSQL_ENTRY_OBJECT _ID, a$.PLSQL_ENTRY_SUBPROGRAM_ID, a$.PLSQL_OBJECT_ID, a$.PLSQL_SUBPROGRAM_ID, a $.TIME_MODEL FROM WRH$_ACTIVE_SESSION_HISTORY a$ WHERE a$.dbid = :dbid AND a $.instance_number = :inst AND a$.snap_id > :bid AND a$.snap_id <= :eid AND a$. 27 124 grwydz59pu6mc select text from view$ where rowid=:1 26 1,425 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 SQL ordered by Version Count DB/Inst: ZIDW/zidw Snaps: 1333-1334 -> Only Statements with Version Count greater than 20 are displayed Version Count Executions SQL Id -------- ------------ ------------- 25 5 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 25 22 9rfqm06xmuwu0 select intcol#, toid, version#, intcols, intcol#s, flags, synobj# from subcoltyp e$ where obj#=:1 order by intcol# asc 25 79 ak5crjygnpk60 UPDATE DBMS_LOCK_ALLOCATED SET EXPIRATION = SYSDATE + (:B1 /86400) WHERE ROWID = :B2 23 156 0ws7ahf1d78qa select SYS_CONTEXT('USERENV', 'SERVER_HOST'), SYS_CONTEXT('USERENV', 'DB_UNIQUE_ NAME'), SYS_CONTEXT('USERENV', 'INSTANCE_NAME'), SYS_CONTEXT('USERENV', 'SERVICE _NAME'), INSTANCE_NUMBER, STARTUP_TIME, SYS_CONTEXT('USERENV', 'DB_DOMAIN') from v$instance where INSTANCE_NAME=SYS_CONTEXT('USERENV', 'INSTANCE_NAME') 23 188 45094nduyxw0q SELECT SYSPRIVID FROM SYS_PRIV_ID_TO_NAME_TAB WHERE UPPER(SYSPRIVNAME ) = UP PER(:b1 ) 23 157 459f3z9u4fb3u select value$ from props$ where name = 'GLOBAL_DB_NAME' 22 47 7at5mdz17scjw SELECT COUNT (*) FROM CMPWBROLE_V R , CMPROLEASSIGNMENT_V A WHERE A.WBUSER = :b1 AND R.ELEMENTID = A.WBROLE AND R.ISADMIN = '1' 21 7,842 1v717nvrhgbn9 SELECT USER FROM SYS.DUAL 21 63 44w6vvj48vnt0 SELECT NAME FROM CMPWORKSPACE_V WHERE ELEMENTID = :b1 21 63 8ykmyd7ww34jk SELECT USER_NAME FROM WORKSPACE_ASSIGNMENT WHERE WORKSPACE_ID = :b1 AND ISWO RKSPACEOWNER = '1' ------------------------------------------------------------- Instance Activity Stats DB/Inst: ZIDW/zidw Snaps: 1333-1334 -> Ordered by statistic name Statistic Total per Second per Trans -------------------------------- ------------------ -------------- ------------- Batched IO (bound) vector count 6 0.0 0.0 Batched IO (full) vector count 0 0.0 0.0 Batched IO block miss count 18 0.0 0.0 Batched IO buffer defrag count 0 0.0 0.0 Batched IO double miss count 0 0.0 0.0 Batched IO same unit count 12 0.0 0.0 Batched IO single block count 6 0.0 0.0 Batched IO slow jump count 0 0.0 0.0 Batched IO vector block count 0 0.0 0.0 Batched IO vector read count 0 0.0 0.0 Block Cleanout Optim referenced 29 0.0 0.0 CCursor + sql area evicted 47 0.0 0.0 CPU used by this session 5,069,526 1,405.7 1,014.5 CPU used when call started 19,861,724 5,507.3 3,974.7 CR blocks created 9,345 2.6 1.9 Cached Commit SCN referenced 20,613,005 5,715.6 4,125.1 Commit SCN cached 583 0.2 0.1 DB time 136,079,784 37,732.1 27,232.3 DBWR checkpoint buffers written 1,976,986 548.2 395.6 DBWR checkpoints 251 0.1 0.1 DBWR object drop buffers written 32 0.0 0.0 DBWR parallel query checkpoint b 146,214 40.5 29.3 DBWR revisited being-written buf 4,812,164 1,334.3 963.0 DBWR tablespace checkpoint buffe 97 0.0 0.0 DBWR thread checkpoint buffers w 0 0.0 0.0 DBWR transaction table writes 7,111 2.0 1.4 DBWR undo block writes 47,787 13.3 9.6 DDL statements parallelized 0 0.0 0.0 DFO trees parallelized 36 0.0 0.0 Effective IO time 55,230,563,088 15,314,299.9 11,052,744.3 HSC Compressed Segment Block Cha 314,316 87.2 62.9 HSC Heap Segment Block Changes 2,803,773 777.4 561.1 HSC IDL Compressed Blocks 0 0.0 0.0 Heap Segment Array Inserts 2,670,555 740.5 534.4 Heap Segment Array Updates 13,213 3.7 2.6 IMU CR rollbacks 559 0.2 0.1 IMU Flushes 3,597 1.0 0.7 IMU Redo allocation size 34,845,676 9,662.0 6,973.3 IMU commits 1,344 0.4 0.3 IMU contention 22 0.0 0.0 IMU ktichg flush 1 0.0 0.0 IMU pool not allocated 772 0.2 0.2 IMU recursive-transaction flush 64 0.0 0.0 IMU undo allocation size 6,812,336 1,888.9 1,363.3 IMU- failed to get a private str 772 0.2 0.2 LOB table id lookup cache misses 0 0.0 0.0 Misses for writing mapping 0 0.0 0.0 Number of read IOs issued 560,720 155.5 112.2 PX local messages recv'd 1,502,876 416.7 300.8 PX local messages sent 1,502,643 416.7 300.7 Parallel operations downgraded t 0 0.0 0.0 Parallel operations not downgrad 36 0.0 0.0 Requests to/from client 14,366 4.0 2.9 RowCR - row contention 0 0.0 0.0 RowCR attempts 57,451,315 15,930.1 11,497.2 RowCR hits 57,451,333 15,930.1 11,497.2 SMON posted for dropping temp se 0 0.0 0.0 SMON posted for undo segment rec 0 0.0 0.0 SMON posted for undo segment shr 14 0.0 0.0 SQL*Net roundtrips to/from clien 14,374 4.0 2.9 Instance Activity Stats DB/Inst: ZIDW/zidw Snaps: 1333-1334 -> Ordered by statistic name Statistic Total per Second per Trans -------------------------------- ------------------ -------------- ------------- SQL*Net roundtrips to/from dblin 11 0.0 0.0 active txn count during cleanout 1,116,225 309.5 223.4 application wait time 47,006 13.0 9.4 background checkpoints completed 217 0.1 0.0 background checkpoints started 217 0.1 0.0 background timeouts 20,905 5.8 4.2 branch node splits 1 0.0 0.0 buffer is not pinned count 945,484,999 262,163.6 189,210.5 buffer is pinned count 1,171,603,318 324,861.5 234,461.3 bytes received via SQL*Net from 5,527,026 1,532.5 1,106.1 bytes received via SQL*Net from 6,170 1.7 1.2 bytes sent via SQL*Net to client 5,075,707 1,407.4 1,015.8 bytes sent via SQL*Net to dblink 4,395 1.2 0.9 calls to get snapshot scn: kcmgs 282,072,018 78,212.8 56,448.3 calls to kcmgas 402,807 111.7 80.6 calls to kcmgcs 12,749,691 3,535.2 2,551.5 cell physical IO interconnect by 662,394,381,312 183,668,346.4 1.3255841E+08 change write time 12,198 3.4 2.4 cleanout - number of ktugct call 3,140,491 870.8 628.5 cleanouts and rollbacks - consis 106 0.0 0.0 cleanouts only - consistent read 2,025,128 561.5 405.3 cluster key scan block gets 14,851 4.1 3.0 cluster key scans 14,493 4.0 2.9 commit batch performed 0 0.0 0.0 commit batch requested 0 0.0 0.0 commit batch/immediate performed 30 0.0 0.0 commit batch/immediate requested 30 0.0 0.0 commit cleanout failures: block 0 0.0 0.0 commit cleanout failures: buffer 63,096 17.5 12.6 commit cleanout failures: callba 13 0.0 0.0 commit cleanout failures: cannot 28 0.0 0.0 commit cleanouts 1,027,566 284.9 205.6 commit cleanouts successfully co 964,429 267.4 193.0 commit immediate performed 30 0.0 0.0 commit immediate requested 30 0.0 0.0 commit txn count during cleanout 2,047,358 567.7 409.7 concurrency wait time 132,330 36.7 26.5 consistent changes 9,941 2.8 2.0 consistent gets 995,480,200 276,026.2 199,215.6 consistent gets - examination 514,344,565 142,617.2 102,930.7 consistent gets direct 35,601,078 9,871.5 7,124.5 consistent gets from cache 959,879,115 266,154.8 192,091.1 consistent gets from cache (fast 361,560,162 100,253.2 72,355.5 cursor authentications 114 0.0 0.0 data blocks consistent reads - u 9,240 2.6 1.9 db block changes 10,072,458 2,792.9 2,015.7 db block gets 11,917,602 3,304.5 2,385.0 db block gets direct 1,036 0.3 0.2 db block gets from cache 11,916,566 3,304.2 2,384.7 db block gets from cache (fastpa 3,155,988 875.1 631.6 deferred (CURRENT) block cleanou 15,609 4.3 3.1 dirty buffers inspected 97,319 27.0 19.5 enqueue conversions 2,785 0.8 0.6 enqueue releases 136,399 37.8 27.3 enqueue requests 137,397 38.1 27.5 enqueue timeouts 1,055 0.3 0.2 enqueue waits 117 0.0 0.0 exchange deadlocks 0 0.0 0.0 execute count 282,047,378 78,205.9 56,443.3 failed probes on index block rec 0 0.0 0.0 Instance Activity Stats DB/Inst: ZIDW/zidw Snaps: 1333-1334 -> Ordered by statistic name Statistic Total per Second per Trans -------------------------------- ------------------ -------------- ------------- file io service time 33,587,894 9,313.2 6,721.6 file io wait time 342,613,349,863 94,999,639.5 68,563,808.3 frame signature mismatch 0 0.0 0.0 free buffer inspected 1,308,901 362.9 261.9 free buffer requested 1,593,153 441.8 318.8 heap block compress 4,680 1.3 0.9 hot buffers moved to head of LRU 1,592,968 441.7 318.8 immediate (CR) block cleanout ap 2,025,234 561.6 405.3 immediate (CURRENT) block cleano 750 0.2 0.2 in call idle wait time 37,286,107 10,338.7 7,461.7 index crx upgrade (positioned) 365 0.1 0.1 index crx upgrade (prefetch) 0 0.0 0.0 index fast full scans (direct re 0 0.0 0.0 index fast full scans (full) 75 0.0 0.0 index fast full scans (rowid ran 0 0.0 0.0 index fetch by key 188,730,188 52,331.0 37,768.7 index scans kdiixs1 59,643,150 16,537.8 11,935.8 java call heap collected bytes 31,727,688 8,797.4 6,349.4 java call heap collected count 436,464 121.0 87.4 java call heap gc count 97 0.0 0.0 java call heap live object count 176,647 49.0 35.4 java call heap live object count 178,763 49.6 35.8 java call heap live size 14,749,576 4,089.8 2,951.7 java call heap live size max 14,749,576 4,089.8 2,951.7 java call heap object count 231,573 64.2 46.3 java call heap object count max 275,348 76.4 55.1 java call heap total size 67,108,864 18,607.9 13,429.8 java call heap total size max 67,108,864 18,607.9 13,429.8 java call heap used size 18,797,416 5,212.1 3,761.7 java call heap used size max 20,828,536 5,775.3 4,168.2 java session heap live size 12,363,776 3,428.2 2,474.2 java session heap live size max 12,363,776 3,428.2 2,474.2 java session heap used size 12,363,776 3,428.2 2,474.2 java session heap used size max 12,363,776 3,428.2 2,474.2 leaf node 90-10 splits 40 0.0 0.0 leaf node splits 97 0.0 0.0 lob reads 288 0.1 0.1 lob writes 1,968 0.6 0.4 lob writes unaligned 1,968 0.6 0.4 logical read bytes from cache 1.592190037197E+13 4.41481570E+09 3.1862918E+09 logons cumulative 812 0.2 0.2 max cf enq hold time 0 0.0 0.0 messages received 211,168 58.6 42.3 messages sent 211,169 58.6 42.3 min active SCN optimization appl 463 0.1 0.1 no buffer to keep pinned count 0 0.0 0.0 no work - consistent read gets 385,498,429 106,890.8 77,146.0 non-idle wait count 14,570,834 4,040.2 2,915.9 non-idle wait time 6,218,235 1,724.2 1,244.4 opened cursors cumulative 282,045,728 78,205.5 56,443.0 parse count (describe) 4 0.0 0.0 parse count (failures) 25 0.0 0.0 parse count (hard) 550 0.2 0.1 parse count (total) 7,870 2.2 1.6 parse time cpu 583 0.2 0.1 parse time elapsed 5,124 1.4 1.0 physical read IO requests 679,473 188.4 136.0 physical read bytes 585,632,382,976 162,383,822.1 1.1719679E+08 physical read total IO requests 704,770 195.4 141.0 physical read total bytes 586,082,813,952 162,508,717.4 1.1728693E+08 Instance Activity Stats DB/Inst: ZIDW/zidw Snaps: 1333-1334 -> Ordered by statistic name Statistic Total per Second per Trans -------------------------------- ------------------ -------------- ------------- physical read total multi block 559,758 155.2 112.0 physical reads 35,744,164 9,911.1 7,153.1 physical reads cache 119,822 33.2 24.0 physical reads cache prefetch 16,544 4.6 3.3 physical reads direct 35,624,342 9,877.9 7,129.2 physical reads direct (lob) 271 0.1 0.1 physical reads direct temporary 22,088 6.1 4.4 physical reads prefetch warmup 0 0.0 0.0 physical write IO requests 1,481,684 410.8 296.5 physical write bytes 37,612,994,560 10,429,310.3 7,527,115.2 physical write total IO requests 1,568,016 434.8 313.8 physical write total bytes 76,311,567,360 21,159,629.0 15,271,476.4 physical write total multi block 111,182 30.8 22.3 physical writes 2,295,713 636.6 459.4 physical writes direct 30,068 8.3 6.0 physical writes direct (lob) 15 0.0 0.0 physical writes direct temporary 22,088 6.1 4.4 physical writes from cache 2,265,647 628.2 453.4 physical writes non checkpoint 1,419,375 393.6 284.1 pinned buffers inspected 16 0.0 0.0 prefetch warmup blocks aged out 0 0.0 0.0 prefetched blocks aged out befor 876 0.2 0.2 process last non-idle time 4,267 1.2 0.9 queries parallelized 35 0.0 0.0 recursive calls 295,292,008 81,878.4 59,093.9 recursive cpu usage 14,154,701 3,924.8 2,832.6 redo blocks checksummed by FG (e 34,205,226 9,484.4 6,845.2 redo blocks written 37,635,257 10,435.5 7,531.6 redo buffer allocation retries 4,059 1.1 0.8 redo entries 7,291,168 2,021.7 1,459.1 redo log space requests 5,123 1.4 1.0 redo log space wait time 6,868 1.9 1.4 redo ordering marks 104,195 28.9 20.9 redo size 18,663,521,892 5,175,011.0 3,734,945.4 redo size for direct writes 23,192 6.4 4.6 redo subscn max counts 1,209,698 335.4 242.1 redo synch long waits 20 0.0 0.0 redo synch poll writes 0 0.0 0.0 redo synch polls 0 0.0 0.0 redo synch time 7,703 2.1 1.5 redo synch time (usec) 77,036,282 21,360.6 15,416.5 redo synch writes 2,025 0.6 0.4 redo wastage 6,106,260 1,693.1 1,222.0 redo write time 99,421 27.6 19.9 redo writes 16,629 4.6 3.3 remote Oradebug requests 0 0.0 0.0 rollback changes - undo records 4 0.0 0.0 rollbacks only - consistent read 8,613 2.4 1.7 root node splits 0 0.0 0.0 rows fetched via callback 188,652,598 52,309.5 37,753.2 scheduler wait time 0 0.0 0.0 session connect time 0 0.0 0.0 session cursor cache hits 1,869,952 518.5 374.2 session logical reads 1,007,397,855 279,330.7 201,600.5 shared hash latch upgrades - no 80,856,888 22,420.0 16,181.1 shared hash latch upgrades - wai 1,185,935 328.8 237.3 sorts (disk) 1 0.0 0.0 sorts (memory) 13,447 3.7 2.7 sorts (rows) 15,761,462 4,370.3 3,154.2 sql area evicted 536 0.2 0.1 Instance Activity Stats DB/Inst: ZIDW/zidw Snaps: 1333-1334 -> Ordered by statistic name Statistic Total per Second per Trans -------------------------------- ------------------ -------------- ------------- sql area purged 37 0.0 0.0 summed dirty queue length 1,866,804 517.6 373.6 switch current to new buffer 40,960 11.4 8.2 table fetch by rowid 890,249,742 246,848.0 178,156.8 table fetch continued row 88,228,543 24,464.0 17,656.3 table scan blocks gotten 36,096,867 10,008.9 7,223.7 table scan rows gotten 2,659,379,266 737,391.2 532,195.2 table scans (direct read) 11,302 3.1 2.3 table scans (long tables) 11,331 3.1 2.3 table scans (rowid ranges) 10,019 2.8 2.0 table scans (short tables) 5,776 1.6 1.2 temp space allocated (bytes) 0 0.0 0.0 total cf enq hold time 191,695 53.2 38.4 total number of cf enq holders 536 0.2 0.1 total number of times SMON poste 46 0.0 0.0 transaction rollbacks 1 0.0 0.0 transaction tables consistent re 22 0.0 0.0 transaction tables consistent re 159 0.0 0.0 undo change vector size 558,240,496 154,788.6 111,715.1 user I/O wait time 5,713,999 1,584.4 1,143.5 user calls 27,423 7.6 5.5 user commits 4,994 1.4 1.0 user rollbacks 3 0.0 0.0 workarea executions - onepass 18 0.0 0.0 workarea executions - optimal 11,408 3.2 2.3 write clones created in backgrou 601 0.2 0.1 write clones created in foregrou 237,170 65.8 47.5 ------------------------------------------------------------- Instance Activity Stats - Absolute ValuesDB/Inst: ZIDW/zidw Snaps: 1333-1334 -> Statistics with absolute values (should not be diffed) Statistic Begin Value End Value -------------------------------- --------------- --------------- session pga memory 33,663,367,728 36,454,232,432 session pga memory max 1.941396015E+11 2.049452424E+11 session cursor cache count 1,965,365 1,969,220 session uga memory 5.683555922E+13 5.691564367E+13 opened cursors current 614 580 workarea memory allocated 1,741,932 4,142,629 logons current 178 151 session uga memory max 5.124513893E+12 5.168124765E+12 ------------------------------------------------------------- Instance Activity Stats - Thread Activity DB/Inst: ZIDW/zidw Snaps: 1333-1334 -> Statistics identified by '(derived)' come from sources other than SYSSTAT Statistic Total per Hour -------------------------------- ------------------ --------- log switches (derived) 217 216.61 ------------------------------------------------------------- IOStat by Function summary DB/Inst: ZIDW/zidw Snaps: 1333-1334 -> '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 544.6G 159.1 154.641 48M 0.9 .013309 0 N/A LGWR 56M 1.2 .015527 36G 22.5 10.2177 24.2K 41.7 DBWR 0M 0.0 0M 34.8G 411.9 9.87613 0 N/A Buffer Cache Re 1.8G 29.7 .520176 0M 0.0 0M 103.6K 16.7 Direct Writes 0M 0.0 0M 419M 1.9 .116180 0 N/A Others 325M 5.8 .090115 87M 1.5 .024123 23.5K 12.4 Streams AQ 0M 0.0 0M 0M 0.0 0M 1 16.0 TOTAL: 546.8G 195.8 155.267 71.3G 438.7 20.2474 151.3K 20.0 ------------------------------------------------------------- IOStat by Filetype summary DB/Inst: ZIDW/zidw Snaps: 1333-1334 -> '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 546.1G 187.4 155.067 34.9G 414.0 9.91052 43.1 487.3 Log File 0M 0.2 0M 35.9G 21.3 10.1988 2.0 N/A Temp File 345M 1.4 .095661 347M 0.7 .096215 18.4 10.5 Control File 381M 6.8 .105643 152M 2.7 .042146 9.9 N/A TOTAL: 546.8G 195.8 155.269 71.3G 438.7 20.2477 36.4 487.3 ------------------------------------------------------------- IOStat by Function/Filetype summary DB/Inst: ZIDW/zidw Snaps: 1333-1334 -> '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 544.6G 159.1 154.640 48M 0.9 .013309 0 N/A Direct Reads (Data File) 544.6G 159.1 154.640 48M 0.9 .013309 0 N/A LGWR 56M 1.2 .015527 36G 22.5 10.2177 5337 2.3 LGWR (Log File) 0M 0.2 0M 35.9G 21.3 10.1988 1744 2.8 LGWR (Control File) 56M 1.0 .015527 68M 1.2 .018855 3593 2.1 DBWR 0M 0.0 0M 34.8G 411.9 9.87613 0 N/A DBWR (Data File) 0M 0.0 0M 34.8G 411.9 9.87613 0 N/A Buffer Cache Reads 1.8G 29.7 .520453 0M 0.0 0M 103.6K 16.7 Buffer Cache Reads (Data File) 1.8G 29.7 .520453 0M 0.0 0M 103.6K 16.7 Direct Writes 0M 0.0 0M 419M 1.9 .116180 0 N/A Direct Writes (Data File) 0M 0.0 0M 419M 1.9 .116180 0 N/A Others 324M 5.8 .089838 87M 1.5 .024123 20.8K 11.2 Others (Control File) 324M 5.8 .089838 84M 1.5 .023291 20.8K 11.2 Others (Data File) 0M 0.0 0M 3M 0.0 .000831 32 17.3 Streams AQ 0M 0.0 0M 0M 0.0 0M 1 16.0 Streams AQ (Data File) 0M 0.0 0M 0M 0.0 0M 1 16.0 TOTAL: 546.8G 195.8 155.266 71.3G 438.7 20.2474 129.7K 15.2 ------------------------------------------------------------- Tablespace IO Stats DB/Inst: ZIDW/zidw Snaps: 1333-1334 -> 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) -------------- ------- ------- ------- ------------ -------- ---------- ------- ADS_NEW 285,578 79 0.1 63.5 594,281 165 396 6.7 LC_DETAIL_FACT_NEW 61,395 17 0.2 62.4 688,258 191 0 0.0 RDS 58,803 16 20.1 1.1 130,406 36 0 0.0 CFT_FACT_NEW 105,993 29 0.1 62.7 221 0 0 0.0 TDS 85,725 24 0.7 59.6 910 0 3 0.0 UNDOTBS1 684 0 0.8 1.0 43,542 12 16 0.0 DDS_NEW 32,644 9 12.0 1.1 241 0 157 17.8 CAT_FACT 30,780 9 0.1 58.5 233 0 0 0.0 LC_DETAIL 537 0 4.5 1.0 9,639 3 0 0.0 TEMP 5,062 1 0.0 4.4 2,562 1 0 0.0 OWF_MGR 1,919 1 5.8 1.0 1,080 0 47 3.6 SYSTEM 1,964 1 10.5 1.7 819 0 35 0.9 SYSAUX 1,155 0 6.1 1.0 727 0 0 0.0 DWHUSERS 989 0 28.1 1.0 651 0 0 0.0 IMEI_PRE 589 0 3.9 1.1 503 0 0 0.0 ID_CALL_TRACE 423 0 15.3 1.0 217 0 0 0.0 CAT_HIST_NEW 410 0 5.7 1.0 217 0 0 0.0 CFT_HIST 350 0 2.2 1.0 217 0 0 0.0 LC_PP_TRUNC 350 0 2.6 1.0 217 0 0 0.0 LC_PP_TRUNC_FACT 350 0 2.1 1.0 217 0 0 0.0 RDS2 283 0 1.2 1.0 217 0 0 0.0 IMEI_HIST_POSTPAID 232 0 0.4 1.0 262 0 0 0.0 EX_STOCK 258 0 7.3 1.0 217 0 0 0.0 ACTIVE_BASE 232 0 3.5 1.0 217 0 0 0.0 EDW 226 0 0.3 1.0 217 0 0 0.0 CIR_NEW 222 0 1.2 1.0 217 0 0 0.0 USERS 222 0 1.2 1.0 217 0 0 0.0 BICMS 220 0 3.2 1.0 217 0 0 0.0 BICMS_TABLELSPACE 220 0 0.1 1.0 217 0 0 0.0 ------------------------------------------------------------- File IO Stats DB/Inst: ZIDW/zidw Snaps: 1333-1334 -> 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) -------------- ------- ------- ------- ------------ -------- ---------- ------- ACTIVE_BASE +DATA4/zidw/datafile/active_base.264.817727301 232 0 3.5 1.0 217 0 0 0.0 ADS_NEW +DATA3/zidw/datafile/ads_new.259.814976167 285,578 79 0.1 63.5 594,281 165 396 6.7 BICMS +DATA4/zidw/datafile/bicms.258.814976307 220 0 3.2 1.0 217 0 0 0.0 BICMS_TABLELSPACE +DATA/zidw/datafile/bicms_tablelspace.261.814104147 220 0 0.1 1.0 217 0 0 0.0 CAT_FACT +DATA2/zidw/datafile/cat_fact.260.814103803 30,780 9 0.1 58.5 233 0 0 0.0 CAT_HIST_NEW +DATA3/zidw/datafile/cat_hist_new.258.814974803 410 0 5.7 1.0 217 0 0 0.0 CFT_FACT_NEW +DATA4/zidw/datafile/cft_fact_new.256.814975039 105,993 29 0.1 62.7 221 0 0 0.0 CFT_HIST +DATA/zidw/datafile/cft_hist.269.814273983 350 0 2.2 1.0 217 0 0 0.0 CIR_NEW +DATA4/zidw/datafile/cir_new.260.814976521 222 0 1.2 1.0 217 0 0 0.0 DDS_NEW +DATA4/zidw/datafile/dds_new.257.814975691 32,644 9 12.0 1.1 241 0 157 17.8 DWHUSERS +DATA4/zidw/datafile/dwhusers.259.814976409 491 0 47.7 1.0 217 0 0 0.0 DWHUSERS +DATA4/zidw/datafile/dwhusers.261.815497793 270 0 13.6 1.0 217 0 0 0.0 DWHUSERS +DATA4/zidw/datafile/dwhusers.262.815559937 228 0 3.2 1.0 217 0 0 0.0 EDW +DATA/zidw/datafile/edw.264.814264901 226 0 0.3 1.0 217 0 0 0.0 EX_STOCK +DATA4/zidw/datafile/crm.263.815670451 258 0 7.3 1.0 217 0 0 0.0 ID_CALL_TRACE +DATA3/zidw/datafile/id_call_trace.256.814726819 423 0 15.3 1.0 217 0 0 0.0 IMEI_HIST_POSTPAID +DATA/zidw/datafile/imei_hist_postpaid.263.814125221 232 0 0.4 1.0 262 0 0 0.0 IMEI_PRE +DATA2/zidw/datafile/imei_pre.259.814103493 589 0 3.9 1.1 503 0 0 0.0 LC_DETAIL +DATA2/zidw/datafile/lc_detail.261.814104037 537 0 4.5 1.0 9,639 3 0 0.0 LC_DETAIL_FACT_NEW +DATA3/zidw/datafile/lc_detail_fact_new.257.81497448 61,395 17 0.2 62.4 688,258 191 0 0.0 LC_PP_TRUNC +DATA2/zidw/datafile/lc_pp_trunc.263.814271251 350 0 2.6 1.0 217 0 0 0.0 LC_PP_TRUNC_FACT +DATA/zidw/datafile/lc_pp_trunc_fact.265.814271327 350 0 2.1 1.0 217 0 0 0.0 OWF_MGR +DSK_GRP1/zidw/datafile/owf_mgr.277.814442655 1,919 1 5.8 1.0 1,080 0 47 3.6 RDS +DATA/zidw/datafile/rds.257.814076871 58,803 16 20.1 1.1 130,406 36 0 0.0 RDS2 +DATA/zidw/datafile/rds2.262.814107651 283 0 1.2 1.0 217 0 0 0.0 SYSAUX +DSK_GRP1/zidw/datafile/sysaux.261.814064009 1,155 0 6.1 1.0 727 0 0 0.0 SYSTEM +DSK_GRP1/zidw/datafile/system.260.814064001 1,964 1 10.5 1.7 819 0 35 0.9 TDS +DATA2/zidw/datafile/tds.256.814090855 85,725 24 0.7 59.6 910 0 3 0.0 TEMP +DSK_GRP1/zidw/tempfile/temp.263.814064015 5,062 1 0.0 4.4 2,561 1 0 N/A TEMP +DSK_GRP1/zidw/tempfile/temp.279.816777507 0 0 N/A N/A 1 0 0 N/A File IO Stats DB/Inst: ZIDW/zidw Snaps: 1333-1334 -> 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) -------------- ------- ------- ------- ------------ -------- ---------- ------- UNDOTBS1 +DSK_GRP1/zidw/datafile/undotbs1.258.814071599 229 0 0.7 1.0 9,448 3 0 0.0 UNDOTBS1 +DSK_GRP1/zidw/datafile/undotbs1.262.814064013 227 0 1.3 1.0 20,924 6 16 0.0 UNDOTBS1 +DSK_GRP1/zidw/datafile/undotbs1.278.814291537 228 0 0.5 1.0 13,170 4 0 0.0 USERS +DSK_GRP1/zidw/datafile/users.264.814064025 222 0 1.2 1.0 217 0 0 0.0 ------------------------------------------------------------- Buffer Pool Statistics DB/Inst: ZIDW/zidw Snaps: 1333-1334 -> 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 1,422,954 100 -3.32564E+09 119,747 2,263,002 0 0 655 ------------------------------------------------------------- Checkpoint Activity DB/Inst: ZIDW/zidw Snaps: 1333-1334 -> Total Physical Writes: 2,295,713 Other Autotune Thread MTTR Log Size Log Ckpt Settings Ckpt Ckpt Writes Writes Writes Writes Writes Writes ----------- ----------- ----------- ----------- ----------- ----------- 0 1,830,672 0 0 0 0 ------------------------------------------------------------- Instance Recovery Stats DB/Inst: ZIDW/zidw Snaps: 1333-1334 -> 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 70 56 1017 24761 331776 331776 801232 N/A 38050 N/A E 71 56 1033 86739 331776 331776 927769 N/A 35246 N/A ------------------------------------------------------------- MTTR Advisory DB/Inst: ZIDW/zidw Snap: 1334 -> Only rows at end snap are displayed -> Estimated Writes and I/Os are in thousands Est Est Est Est Cache Est Total Est Total Size fo Dirty Cache Write Total Write Total IO Est (s) Limit Writes Fctr Writes Fctr IOs Fctr ------- ---------- ------------ ------ ------------ ------ ------------ ------ 59 1,000 1,464,024 1.1 1,817,287 1.1 N/A N/A 62 1,036 1,432,980 1.1 1,786,243 1.1 N/A N/A 65 1,601 1,363,176 1.1 1,716,438 1.0 N/A N/A 68 2,167 1,323,168 1.0 1,676,431 1.0 N/A N/A 71 2,798 1,294,392 1.0 1,647,654 1.0 N/A N/A ------------------------------------------------------------- Buffer Pool Advisory DB/Inst: ZIDW/zidw Snap: 1334 -> Only rows with estimated physical reads >0 are displayed -> ordered by Block Size, Buffers For Estimate Est Phys Estimated Est Size for Size Buffers Read Phys Reads Est Phys %DBtime P Est (M) Factor (thousands) Factor (thousands) Read Time for Rds --- -------- ------ ------------ ------ -------------- ------------ ------- D 2,240 .1 140 6.4 1,254,961 1 9.0E+06 D 4,480 .2 281 4.7 926,178 1 6.6E+06 D 6,720 .3 421 3.6 709,030 1 5.0E+06 D 8,960 .4 562 2.8 544,150 1 3.8E+06 D 11,200 .5 702 2.1 415,259 1 2.9E+06 D 13,440 .6 843 1.6 318,193 1 2.1E+06 D 15,680 .7 983 1.3 255,472 1 1.7E+06 D 17,920 .8 1,124 1.1 222,926 1 1.5E+06 D 20,160 .9 1,264 1.0 206,742 1 1.3E+06 D 22,400 1.0 1,405 1.0 198,102 1 1.3E+06 D 22,688 1.0 1,423 1.0 197,441 1 1.3E+06 D 24,640 1.1 1,545 1.0 193,050 1 1.2E+06 D 26,880 1.2 1,686 1.0 189,701 1 1.2E+06 D 29,120 1.3 1,826 0.9 187,190 1 1.2E+06 D 31,360 1.4 1,967 0.9 185,105 1 1.2E+06 D 33,600 1.5 2,107 0.9 183,269 1 1.2E+06 D 35,840 1.6 2,248 0.9 181,620 1 1.2E+06 D 38,080 1.7 2,388 0.9 180,128 1 1.1E+06 D 40,320 1.8 2,529 0.9 178,719 1 1.1E+06 D 42,560 1.9 2,669 0.9 177,387 1 1.1E+06 D 44,800 2.0 2,810 0.9 176,038 1 1.1E+06 ------------------------------------------------------------- PGA Aggr Summary DB/Inst: ZIDW/zidw Snaps: 1333-1334 -> 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 --------------- ------------------ -------------------------- 98.9 38,909 433 ------------------------------------------------------------- PGA Aggr Target Stats DB/Inst: ZIDW/zidw Snaps: 1333-1334 No data exists for this section of the report. ------------------------------------------------------------- PGA Aggr Target Histogram DB/Inst: ZIDW/zidw Snaps: 1333-1334 -> Optimal Executions are purely in-memory operations Low High Optimal Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs ------- ------- -------------- -------------- ------------ ------------ 2K 4K 8,830 8,830 0 0 64K 128K 1,416 1,416 0 0 128K 256K 14 14 0 0 256K 512K 2 2 0 0 512K 1024K 508 508 0 0 1M 2M 81 81 0 0 2M 4M 120 118 2 0 4M 8M 22 22 0 0 8M 16M 6 6 0 0 16M 32M 10 2 8 0 32M 64M 79 71 8 0 64M 128M 265 265 0 0 128M 256M 50 50 0 0 256M 512M 10 10 0 0 ------------------------------------------------------------- PGA Memory Advisory DB/Inst: ZIDW/zidw Snap: 1334 -> 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 ---------- ------- ---------------- ---------------- ------ -------- ------- 2,256 0.1 963,629.3 0.0 100.0 0 1.4E+09 4,512 0.3 963,629.3 0.0 100.0 0 1.4E+09 9,024 0.5 963,629.3 0.0 100.0 0 1.4E+09 13,536 0.8 963,629.3 0.0 100.0 0 1.4E+09 18,048 1.0 963,629.3 0.0 100.0 0 1.4E+09 21,658 1.2 963,629.3 0.0 100.0 0 1.4E+09 25,267 1.4 963,629.3 0.0 100.0 0 1.4E+09 28,877 1.6 963,629.3 0.0 100.0 0 1.4E+09 32,486 1.8 963,629.3 0.0 100.0 0 1.4E+09 36,096 2.0 963,629.3 0.0 100.0 0 1.4E+09 54,144 3.0 963,629.3 0.0 100.0 0 1.4E+09 72,192 4.0 963,629.3 0.0 100.0 0 1.4E+09 108,288 6.0 963,629.3 0.0 100.0 0 1.4E+09 144,384 8.0 963,629.3 0.0 100.0 0 1.4E+09 ------------------------------------------------------------- Shared Pool Advisory DB/Inst: ZIDW/zidw Snap: 1334 -> 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) -------- ----- -------- ------------ -------- ------ ------- ------ ------------ 1,248 .9 62 3,625 3.23E+08 1.0 7.9E+06 168.0 15,533 1,280 .9 93 4,620 3.25E+08 1.0 6.1E+06 129.2 352,418 1,312 .9 125 6,246 3.26E+08 1.0 4.6E+06 96.9 613,090 1,344 1.0 157 8,060 3.28E+08 1.0 3.1E+06 64.7 873,562 1,376 1.0 189 9,422 3.29E+08 1.0 1.5E+06 32.4 1,134,055 1,408 1.0 221 10,801 3.31E+08 1.0 47,285 1.0 1,388,536 1,440 1.0 253 11,814 3.31E+08 1.0 46,585 1.0 1,388,557 1,472 1.0 285 12,610 3.31E+08 1.0 46,364 1.0 1,388,564 1,504 1.1 317 13,406 3.31E+08 1.0 46,069 1.0 1,388,574 1,536 1.1 349 14,202 3.31E+08 1.0 45,821 1.0 1,388,581 1,568 1.1 381 14,944 3.31E+08 1.0 45,561 1.0 1,388,588 1,600 1.1 413 15,511 3.31E+08 1.0 45,245 1.0 1,388,597 1,632 1.2 445 16,078 3.31E+08 1.0 44,939 1.0 1,388,606 1,664 1.2 477 16,645 3.31E+08 1.0 44,639 .9 1,388,616 1,696 1.2 509 17,212 3.31E+08 1.0 44,395 .9 1,388,625 1,728 1.2 541 17,779 3.31E+08 1.0 44,123 .9 1,388,634 1,888 1.3 701 21,135 3.31E+08 1.0 42,472 .9 1,388,683 2,048 1.5 861 25,446 3.31E+08 1.0 40,769 .9 1,388,724 2,208 1.6 1,021 28,790 3.31E+08 1.0 39,632 .8 1,388,749 2,368 1.7 1,181 36,197 3.31E+08 1.0 38,804 .8 1,388,771 2,528 1.8 1,341 39,014 3.31E+08 1.0 38,173 .8 1,388,788 2,688 1.9 1,501 43,354 3.31E+08 1.0 37,631 .8 1,388,800 2,848 2.0 1,661 46,974 3.31E+08 1.0 37,200 .8 1,388,809 ------------------------------------------------------------- SGA Target Advisory DB/Inst: ZIDW/zidw Snap: 1334 SGA Target SGA Size Est DB Est Physical Size (M) Factor Time (s) Reads ---------- ---------- ------------ ---------------- 6,144 0.3 64,363,928 926,170,841 9,216 0.4 62,775,642 709,026,436 12,288 0.5 61,571,144 544,144,373 15,360 0.6 59,923,814 318,194,148 18,432 0.8 59,469,175 255,467,490 21,504 0.9 59,114,914 206,739,322 24,576 1.0 59,044,058 197,439,903 27,648 1.1 58,990,939 189,700,259 30,720 1.3 58,967,324 187,192,772 33,792 1.4 58,949,615 183,263,718 36,864 1.5 58,931,904 181,624,967 39,936 1.6 58,914,196 178,722,600 43,008 1.8 58,896,485 177,380,009 46,080 1.9 58,890,583 176,037,418 49,152 2.0 58,890,585 176,037,418 ------------------------------------------------------------- Streams Pool Advisory DB/Inst: ZIDW/zidw Snap: 1334 Size for Size Est Spill Est Spill Est Unspill Est Unspill Est (MB) Factor Count Time (s) Count Time (s) ---------- --------- ----------- ----------- ----------- ----------- 32 0.5 0 0 0 0 64 1.0 0 0 0 0 96 1.5 0 0 0 0 128 2.0 0 0 0 0 160 2.5 0 0 0 0 192 3.0 0 0 0 0 224 3.5 0 0 0 0 256 4.0 0 0 0 0 288 4.5 0 0 0 0 320 5.0 0 0 0 0 352 5.5 0 0 0 0 384 6.0 0 0 0 0 416 6.5 0 0 0 0 448 7.0 0 0 0 0 480 7.5 0 0 0 0 512 8.0 0 0 0 0 544 8.5 0 0 0 0 576 9.0 0 0 0 0 608 9.5 0 0 0 0 640 10.0 0 0 0 0 ------------------------------------------------------------- Java Pool Advisory DB/Inst: ZIDW/zidw Snap: 1334 Est LC Est LC Est LC Est LC Java JP Est LC Time Time Load Load Est LC Pool Size Size Est LC Saved Saved Time Time Mem Size(M) Factr (M) Mem Obj (s) Factr (s) Factr Obj Hits ---------- ----- -------- ------------ ------- ------ ------- ------ ----------- 192 1.0 26 458 1.8E+05 1.0 47,285 1.0 951,307 224 1.2 26 458 1.8E+05 1.0 47,282 1.0 951,327 256 1.3 26 458 1.8E+05 1.0 47,282 1.0 951,327 288 1.5 26 458 1.8E+05 1.0 47,282 1.0 951,327 320 1.7 26 458 1.8E+05 1.0 47,282 1.0 951,327 352 1.8 26 458 1.8E+05 1.0 47,282 1.0 951,327 384 2.0 26 458 1.8E+05 1.0 47,282 1.0 951,327 416 2.2 26 458 1.8E+05 1.0 47,282 1.0 951,327 448 2.3 26 458 1.8E+05 1.0 47,282 1.0 951,327 480 2.5 26 458 1.8E+05 1.0 47,282 1.0 951,327 ------------------------------------------------------------- Buffer Wait Statistics DB/Inst: ZIDW/zidw Snaps: 1333-1334 -> ordered by wait time desc, waits desc Class Waits Total Wait Time (s) Avg Time (ms) ------------------ ----------- ------------------- -------------- data block 404 5 14 1st level bmb 183 0 1 segment header 48 0 1 undo header 16 0 0 2nd level bmb 3 0 0 ------------------------------------------------------------- Enqueue Activity DB/Inst: ZIDW/zidw Snaps: 1333-1334 -> 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) ------------ ------------ ----------- ----------- ------------ -------------- KO-Multiple Object Checkpoint (fast object checkpoint) 219 220 0 22 465 21,137.41 RO-Multiple Object Reuse (fast object reuse) 120 120 0 12 2 155.75 CF-Controlfile Transaction 2,676 2,670 6 6 1 133.17 TX-Transaction (row lock contention) 9 9 0 9 0 39.67 CR-Reuse Block Range (block range reuse ckpt) 278 278 0 2 0 50.50 PS-PX Process Reservation 3,513 2,496 1,022 52 0 1.56 TX-Transaction (index contention) 2 2 0 2 0 16.00 HW-Segment High Water Mark 4,067 4,067 0 5 0 .40 BF-BLOOM FILTER (allocation contention) 63 46 17 3 0 .33 FB-Format Block 71,832 71,832 0 2 0 .50 TX-Transaction 7,067 7,068 0 2 0 .50 ------------------------------------------------------------- Undo Segment Summary DB/Inst: ZIDW/zidw Snaps: 1333-1334 -> 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 34.7 7,045 11,761 6 240/240 0/0 0/0/0/0/0/0 ------------------------------------------------------------- Undo Segment Stats DB/Inst: ZIDW/zidw Snaps: 1333-1334 -> 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 ------------ ----------- ------------ ------- ------- ------- ----- ------------ 24-Jun 08:59 12,395 2,661 1,337 6 240 0/0 0/0/0/0/0/0 24-Jun 08:49 9,032 1,079 736 6 240 0/0 0/0/0/0/0/0 24-Jun 08:39 7,683 1,991 968 5 240 0/0 0/0/0/0/0/0 24-Jun 08:29 2,722 606 11,761 5 240 0/0 0/0/0/0/0/0 24-Jun 08:19 1,824 253 11,159 4 240 0/0 0/0/0/0/0/0 24-Jun 08:09 1,013 455 10,557 5 240 0/0 0/0/0/0/0/0 ------------------------------------------------------------- Latch Activity DB/Inst: ZIDW/zidw Snaps: 1333-1334 -> "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 1 0.0 0 0 N/A AQ dequeue txn counter l 722 0.0 0 0 N/A ASM Keyed state latch 699 0.0 0 0 N/A ASM allocation 1,082 0.0 0 0 N/A ASM db client latch 2,587 0.0 0 0 N/A ASM map operation freeli 2,872 1.5 0.0 0 0 N/A ASM map operation hash t 4,566,294 0.0 0.0 0 0 N/A ASM network background l 1,985 0.0 0 0 N/A ASM network state latch 1,256 0.0 0 0 N/A AWR Alerted Metric Eleme 36,894 0.0 0 0 N/A Bloom filter list latch 9 0.0 0 0 N/A Change Notification Hash 1,202 0.0 0 0 N/A Consistent RBA 16,834 0.0 0 0 N/A DML lock allocation 26,793 0.0 0 0 N/A Event Group Locks 1,303 0.9 0.0 0 0 N/A FOB s.o list latch 6,405 3.1 0.0 0 0 N/A File State Object Pool P 1 0.0 0 0 N/A I/O Staticstics latch 1 0.0 0 0 N/A IPC stats buffer allocat 1 0.0 0 0 N/A In memory undo latch 565,955 0.1 0.0 0 6,631 0.0 JOX SGA heap latch 13,391 0.1 0.0 0 0 N/A JS Sh mem access 7 0.0 0 0 N/A JS mem alloc latch 6 0.0 0 0 N/A JS queue access latch 7 0.0 0 0 N/A JS queue state obj latch 26,220 0.0 0 0 N/A JS slv state obj latch 70 0.0 0 0 N/A KFC FX Hash Latch 1 0.0 0 0 N/A KFC Hash Latch 1 0.0 0 0 N/A KFCL LE Freelist 1 0.0 0 0 N/A KFK SGA Libload latch 4,598 0.0 0 0 N/A KFMD SGA 13 0.0 0 0 N/A KGNFS-NFS:SHM structure 1 0.0 0 0 N/A KGNFS-NFS:SVR LIST 1 0.0 0 0 N/A KJC message pool free li 1 0.0 0 0 N/A KJCT flow control latch 1 0.0 0 0 N/A KMG MMAN ready and start 1,203 0.0 0 0 N/A KTF sga latch 23 0.0 0 1,046 0.0 KWQMN job cache list lat 164 0.0 0 0 N/A KWQP Prop Status 2 0.0 0 0 N/A KWQS pqueue ctx latch 188 0.0 0 0 N/A Locator state objects po 1 0.0 0 0 N/A Lsod array latch 6,801 7.6 0.5 0 0 N/A MQL Tracking Latch 0 N/A 0 72 0.0 Memory Management Latch 1 0.0 0 1,203 0.0 Memory Queue 1 0.0 0 0 N/A Memory Queue Message Sub 1 0.0 0 0 N/A Memory Queue Message Sub 1 0.0 0 0 N/A Memory Queue Message Sub 1 0.0 0 0 N/A Memory Queue Message Sub 1 0.0 0 0 N/A Memory Queue Subscriber 1 0.0 0 0 N/A MinActiveScn Latch 40 0.0 0 0 N/A Mutex 1 0.0 0 0 N/A Mutex Stats 1 0.0 0 0 N/A OS process 2,258 0.0 0 0 N/A OS process allocation 8,283 0.4 0.0 0 0 N/A OS process: request allo 699 1.1 0.0 0 0 N/A PL/SQL warning settings 6,788 0.0 0.0 0 0 N/A PX hash array latch 1 0.0 0 0 N/A QMT 1 0.0 0 0 N/A Real-time plan statistic 17,550 0.7 0.4 0 0 N/A Latch Activity DB/Inst: ZIDW/zidw Snaps: 1333-1334 -> "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 ------------------------ -------------- ------ ------ ------ ------------ ------ SGA IO buffer pool latch 23,508 0.0 0.0 0 27,461 0.1 SGA blob parent 1 0.0 0 0 N/A SGA bucket locks 1 0.0 0 0 N/A SGA heap locks 1 0.0 0 0 N/A SGA pool locks 1 0.0 0 0 N/A SQL memory manager latch 173 19.7 1.0 0 1,165 0.1 SQL memory manager worka 85,319 0.1 0.0 0 0 N/A Shared B-Tree 467 0.0 0 0 N/A Streams Generic 1 0.0 0 0 N/A Testing 1 0.0 0 0 N/A Token Manager 1 0.0 0 0 N/A WCR: sync 1 0.0 0 0 N/A Write State Object Pool 1 0.0 0 0 N/A X$KSFQP 1 0.0 0 0 N/A XDB NFS Security Latch 1 0.0 0 0 N/A XDB unused session pool 1 0.0 0 0 N/A XDB used session pool 1 0.0 0 0 N/A active checkpoint queue 197,518 0.1 0.0 0 0 N/A active service list 12,058 5.0 0.7 0 3,294 0.0 begin backup scn array 435 0.0 0 0 N/A buffer pool 1 0.0 0 0 N/A business card 1 0.0 0 0 N/A cache buffer handles 1,143,688 0.0 0.0 0 0 N/A cache buffers chains 1,459,874,785 2.6 0.0 10 1,617,297 0.0 cache buffers lru chain 2,621,839 0.0 0.1 0 3,694,165 0.1 cache table scan latch 868 0.0 0 868 0.0 call allocation 7,143 8.7 0.9 0 0 N/A cas latch 1 0.0 0 0 N/A change notification clie 1 0.0 0 0 N/A channel handle pool latc 774 1.6 0.1 0 0 N/A channel operations paren 24,398 0.1 0.0 0 0 N/A checkpoint queue latch 10,622,492 0.0 0.0 0 2,131,320 0.0 client/application info 5,250 0.2 0.4 0 0 N/A compile environment latc 977 1.1 0.0 0 0 N/A corrupted undo seg latch 13 0.0 0 0 N/A cp cmon/server latch 1 0.0 0 0 N/A cp pool latch 1 0.0 0 0 N/A cp server hash latch 1 0.0 0 0 N/A cp sga latch 174 0.0 0 0 N/A cvmap freelist lock 1 0.0 0 0 N/A deferred cleanup latch 174 0.0 0 0 N/A dml lock allocation 190 0.0 0 0 N/A done queue latch 1 0.0 0 0 N/A dummy allocation 1,982 16.5 0.0 0 0 N/A enqueue freelist latch 1 0.0 0 231,912 0.0 enqueue hash chains 276,961 0.0 0.0 0 170 0.0 error message lists 1,258 8.7 0.0 0 0 N/A fifth spare latch 1 0.0 0 0 N/A file cache latch 2,426 0.0 0 0 N/A flashback copy 1 0.0 0 0 N/A fourth Audit Vault latch 1 0.0 0 0 N/A gc element 1 0.0 0 0 N/A gcs commit scn state 1 0.0 0 0 N/A gcs partitioned table ha 1 0.0 0 0 N/A gcs pcm hashed value buc 1 0.0 0 0 N/A gcs resource freelist 1 0.0 0 0 N/A gcs resource hash 1 0.0 0 0 N/A gcs resource scan list 1 0.0 0 0 N/A gcs shadows freelist 1 0.0 0 0 N/A ges domain table 1 0.0 0 0 N/A Latch Activity DB/Inst: ZIDW/zidw Snaps: 1333-1334 -> "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 enqueue table freeli 1 0.0 0 0 N/A ges group table 1 0.0 0 0 N/A ges process hash list 1 0.0 0 0 N/A ges process parent latch 1 0.0 0 0 N/A ges resource hash list 1 0.0 0 0 N/A ges resource scan list 1 0.0 0 0 N/A ges resource table freel 1 0.0 0 0 N/A ges value block free lis 1 0.0 0 0 N/A global KZLD latch for me 52 0.0 0 0 N/A global ctx hash table la 48 0.0 0 0 N/A global tx hash mapping 4 0.0 0 0 N/A granule operation 1 0.0 0 0 N/A hash table column usage 234 0.0 0 67,223 0.0 hash table modification 147 0.0 0 0 N/A heartbeat check 1 0.0 0 0 N/A internal temp table obje 22 0.0 0 0 N/A interrupt manipulation 5,335 5.8 0.0 0 0 N/A intra txn parallel recov 1 0.0 0 0 N/A io pool granule metadata 1 0.0 0 0 N/A job workq parent latch 20 0.0 0 19 0.0 job_queue_processes free 43 0.0 0 0 N/A job_queue_processes para 803 0.0 0 0 N/A k2q lock allocation 1 0.0 0 0 N/A kcbtsemkid latch 217 0.0 0 0 N/A kdlx hb parent latch 1 0.0 0 0 N/A kgb parent 1 0.0 0 0 N/A kgnfs mount latch 1 0.0 0 0 N/A kokc descriptor allocati 2,331 0.0 0 0 N/A ksfv messages 1 0.0 0 0 N/A ksim group membership ca 1 0.0 0 0 N/A kss move lock 43 0.0 0 0 N/A ksuosstats global area 365 0.0 0 0 N/A ksv allocation latch 254 0.0 0 0 N/A ksv class latch 4,958 0.7 0.0 0 0 N/A ksv instance latch 6 0.0 0 0 N/A ksv msg queue latch 1 0.0 0 0 N/A ksz_so allocation latch 699 2.7 0.0 0 0 N/A ktm global data 205 0.0 0 0 N/A kwqbsn:qsga 131 0.0 0 0 N/A lgwr LWN SCN 16,778 0.0 0.0 0 0 N/A list of block allocation 111,287 0.0 0.0 0 0 N/A loader state object free 25,658 0.1 0.0 0 0 N/A lob segment dispenser la 1 0.0 0 0 N/A lob segment hash table l 13 0.0 0 0 N/A lob segment query latch 1 0.0 0 0 N/A lock DBA buffer during m 1 0.0 0 0 N/A logical standby cache 1 0.0 0 0 N/A logminer context allocat 2 0.0 0 0 N/A logminer work area 1 0.0 0 0 N/A longop free list parent 41 0.0 0 17 0.0 mapped buffers lru chain 1 0.0 0 0 N/A message pool operations 238 0.0 0 0 N/A messages 482,990 0.1 0.0 0 0 N/A mostly latch-free SCN 18,226 6.1 0.0 0 0 N/A msg queue latch 1 0.0 0 0 N/A multiblock read objects 1,950 0.0 0 0 N/A name-service namespace b 1 0.0 0 0 N/A ncodef allocation latch 174 0.0 0 0 N/A object queue header heap 9,080 0.0 0 8,084 0.0 object queue header oper 10,055,474 0.0 0.1 0 0 N/A Latch Activity DB/Inst: ZIDW/zidw Snaps: 1333-1334 -> "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 ------------------------ -------------- ------ ------ ------ ------------ ------ object stats modificatio 7,068 0.0 0 0 N/A parallel query alloc buf 24,305 13.5 0.0 0 0 N/A parallel query stats 717 43.7 0.3 0 0 N/A parameter list 757 0.0 0 0 N/A parameter table manageme 10,864 8.1 0.0 0 0 N/A peshm 1 0.0 0 0 N/A pesom_free_list 9,413 0.0 0 0 N/A pesom_hash_node 30,019 0.0 0.0 0 0 N/A pesom_heap_alloc 112 0.0 0 0 N/A post/wait queue 11,013 0.4 0.0 0 9,870 0.3 process allocation 896 2.1 0.8 0 325 0.0 process group creation 699 1.0 0.0 0 0 N/A process queue 15,496 1.1 0.0 0 0 N/A process queue reference 27,044,839 0.0 0.0 0 2,629,785 0.2 qmn task queue latch 783 0.0 0 0 N/A query server freelists 14,564 3.5 0.0 0 0 N/A query server process 741 12.4 1.1 57 163 1.2 queued dump request 12 0.0 0 0 N/A queuing load statistics 1 0.0 0 0 N/A recovery domain hash lis 1 0.0 0 0 N/A redo allocation 271,558 0.2 0.0 0 7,285,314 0.1 redo copy 1 0.0 0 7,286,464 0.0 redo writing 263,088 0.1 0.0 0 0 N/A resmgr group change latc 877 0.3 0.0 0 0 N/A resmgr:active threads 1,982 0.0 0 0 N/A resmgr:actses change gro 780 0.0 0 0 N/A resmgr:actses change sta 1 0.0 0 0 N/A resmgr:free threads list 1,981 15.4 0.0 0 0 N/A resmgr:plan CPU method 1 0.0 0 0 N/A resmgr:resource group CP 1 0.0 0 0 N/A resmgr:schema config 81 0.0 0 0 N/A resmgr:session queuing 1 0.0 0 0 N/A rm cas latch 1 0.0 0 0 N/A row cache objects 310,474 0.5 0.1 0 222 0.0 rules engine rule set st 100 0.0 0 0 N/A second Audit Vault latch 1 0.0 0 0 N/A second spare latch 1 0.0 0 0 N/A sequence cache 1,048 0.0 0 0 N/A session allocation 10,193 0.5 0.0 0 8,226 0.0 session idle bit 62,463 0.0 0.0 0 0 N/A session queue latch 1 0.0 0 0 N/A session state list latch 2,194 10.5 0.0 0 0 N/A session switching 549 1.3 0.0 0 0 N/A session timer 1,280 0.0 0 0 N/A shared pool 257,935 3.6 0.0 0 0 N/A shared pool sim alloc 1 0.0 0 0 N/A shared pool simulator 2,433 0.0 0 0 N/A sim partition latch 1 0.0 0 0 N/A simulator hash latch 52,655,066 0.0 0.0 0 0 N/A simulator lru latch 1 0.0 0 52,492,945 1.5 sort extent pool 858 0.3 0.0 0 0 N/A space background state o 16 0.0 0 0 N/A space background task la 7,673 2.4 0.4 0 2,439 0.0 state object free list 12 0.0 0 0 N/A statistics aggregation 560 0.0 0 0 N/A tablespace key chain 1 0.0 0 0 N/A temp lob duration state 38 0.0 0 0 N/A temporary table state ob 1 0.0 0 0 N/A test excl. parent l0 1 0.0 0 0 N/A test excl. parent2 l0 1 0.0 0 0 N/A Latch Activity DB/Inst: ZIDW/zidw Snaps: 1333-1334 -> "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 ------------------------ -------------- ------ ------ ------ ------------ ------ third spare latch 1 0.0 0 0 N/A threshold alerts latch 407 0.0 0 0 N/A transaction allocation 5,168 0.0 0 0 N/A transaction branch alloc 1,281 0.0 0 0 N/A undo global data 3,240,542 0.0 0.0 0 2 0.0 virtual circuit buffers 1 0.0 0 0 N/A virtual circuit holder 1 0.0 0 0 N/A virtual circuit queues 1 0.0 0 0 N/A ------------------------------------------------------------- Latch Sleep Breakdown DB/Inst: ZIDW/zidw Snaps: 1333-1334 -> ordered by misses desc Get Spin Latch Name Requests Misses Sleeps Gets -------------------------- --------------- ------------ ----------- ----------- cache buffers chains 1,459,874,785 37,294,252 409,991 37,005,723 shared pool 257,935 9,212 198 9,021 parallel query alloc buffe 24,305 3,284 43 3,241 object queue header operat 10,055,474 2,872 174 2,789 row cache objects 310,474 1,456 78 1,385 simulator hash latch 52,655,066 1,302 1 1,301 checkpoint queue latch 10,622,492 1,042 24 1,018 cache buffers lru chain 2,621,839 925 103 825 parameter table management 10,864 878 14 864 call allocation 7,143 621 546 266 active service list 12,058 608 396 325 redo allocation 271,558 539 8 532 Lsod array latch 6,801 520 266 273 messages 482,990 442 1 441 In memory undo latch 565,955 404 6 398 ASM map operation hash tab 4,566,294 390 1 389 dummy allocation 1,982 328 8 320 redo writing 263,088 317 2 316 parallel query stats 717 313 104 226 resmgr:free threads list 1,981 305 7 298 session state list latch 2,194 231 3 228 space background task latc 7,673 186 75 111 Real-time plan statistics 17,550 131 54 84 enqueue hash chains 276,961 99 3 96 query server process 741 92 99 4 ASM map operation freelist 2,872 43 2 41 undo global data 3,240,542 40 1 39 SQL memory manager latch 173 34 34 0 process allocation 896 19 15 10 channel handle pool latch 774 12 1 11 client/application info 5,250 11 4 7 ------------------------------------------------------------- Latch Miss Sources DB/Inst: ZIDW/zidw Snaps: 1333-1334 -> only latches with sleeps are shown -> ordered by name, sleeps desc NoWait Waiter Latch Name Where Misses Sleeps Sleeps ------------------------ -------------------------- ------- ---------- -------- ASM map operation freeli kffmTranslate2 0 2 0 ASM map operation hash t kffmTranslate 0 1 0 In memory undo latch ktiFlush: child 0 6 6 In memory undo latch kticflush_2 0 1 0 In memory undo latch ktichg: child 0 1 2 Lsod array latch kfklsodtablCleanup 0 266 266 PC and Classifier lists No latch 0 5 0 Real-time plan statistic keswxAddNewPlanEntry 0 54 54 SQL memory manager latch qesmmIQueryRefreshBound 0 34 34 active service list kswslogon: session logout 0 428 421 active service list kswssetsvc: PX session swi 0 13 14 active service list kswslogon: session login 0 11 15 cache buffers chains kcbgtcr: fast path 0 650,968 91,526 cache buffers chains kcbgtcr_2 0 145,709 397,615 cache buffers chains kcbrls_2 0 26,644 334,438 cache buffers chains kcbso1: set no access 0 1,754 1,241 cache buffers chains kcbbxsv 0 450 102 cache buffers chains kcbgcur_2 0 145 43 cache buffers chains kcbgtcr: fast path (cr pin 0 126 637 cache buffers chains kcbzwb 0 103 73 cache buffers chains kcbrls: fast release 0 100 91 cache buffers chains kcbnew: new latch again 0 22 0 cache buffers chains kcbbic2 0 18 25 cache buffers chains kcbzgb: scan from tail. no 0 14 0 cache buffers chains kcbchg1: aux pin 0 8 47 cache buffers chains kcbgcur: fast path (shr) 0 6 8 cache buffers chains kcbchg1: mod cr pin 0 5 1 cache buffers chains kcbzar_fpin_1 0 5 89 cache buffers chains kcbchg1: clear MS bit 0 4 25 cache buffers chains kcbchg1: mod cur pin 0 3 154 cache buffers chains kcbnlc 0 3 1 cache buffers chains kcbgcur_4 0 2 0 cache buffers chains kcbget: fast exchange 0 2 0 cache buffers chains kcb_is_private 0 1 5 cache buffers chains kcbbwdb 0 1 22 cache buffers chains kcbchg1: no fast path 0 1 0 cache buffers chains kcbgtcr: kslbegin excl 0 1 7 cache buffers chains kcbgtcr: kslbegin shared 0 1 0 cache buffers chains kcbgtcr_1 0 1 0 cache buffers chains kcbzib: finish processing 0 1 0 cache buffers lru chain kcbzgws 0 98 0 cache buffers lru chain kcbzgb 0 3 14 cache buffers lru chain kcbbxsv: move to being wri 0 1 0 cache buffers lru chain kcbo_link_q 0 1 0 call allocation ksuxds 0 537 522 call allocation ksudlp: top call 0 7 12 call allocation kxfpRecvSessStats 0 2 12 channel handle pool latc ksrchconnect() 0 1 1 channel operations paren ksrchdelete_sub() 0 1 1 checkpoint queue latch kcbbwdl: thread checkpoint 0 23 1 checkpoint queue latch kcbnlc: Link buffers into 0 1 0 client/application info ksuinfos_modact 0 6 6 client/application info kskirefrattrmap 0 2 2 dummy allocation ktcscnfy 0 8 8 enqueue hash chains ksqrcl 0 3 3 messages ksarcv 0 1 0 object queue header oper kcbo_service_ockpt 0 109 0 object queue header oper kcbo_switch_q_bg 0 40 75 object queue header oper kcbo_write_q 0 28 1 object queue header oper kcbo_unlink_q 0 20 7 Latch Miss Sources DB/Inst: ZIDW/zidw Snaps: 1333-1334 -> only latches with sleeps are shown -> ordered by name, sleeps desc NoWait Waiter Latch Name Where Misses Sleeps Sleeps ------------------------ -------------------------- ------- ---------- -------- object queue header oper kcbo_switch_mq_bg 0 19 109 object queue header oper kcbo_link_q 0 16 8 object queue header oper kcbo_switch_cq 0 5 20 object queue header oper kcbo_unlink_q_bg 0 2 2 parallel query alloc buf kxfpbalo 0 43 43 parallel query stats kxfprst: KSLBEGIN:1 0 104 104 parameter table manageme kspptval 0 18 18 process allocation ksufap: active procs 0 13 15 process allocation ksu_reserve 0 2 0 query server process kxfpg1srv 0 98 98 query server process kxfprdp: ONERROR 0 1 1 redo allocation kcrfwnf:2 0 6 0 redo allocation kcrfw_redo_gen: redo alloc 0 2 0 redo writing kcrfwnf: new logfile 0 2 0 resmgr:free threads list kskthrcreate 0 7 7 row cache objects kqreqd: reget 0 41 4 row cache objects kqrpre: find obj 0 22 62 row cache objects kqreqd 0 15 12 session state list latch kpseqd 0 3 3 shared pool kghalo 0 129 126 shared pool kghfre 0 69 69 simulator hash latch kcbsacc: lookup dba 0 1 1 space background task la ktsj_grab_task 0 70 75 undo global data ktudba: KSLBEGIN 0 1 1 ------------------------------------------------------------- Mutex Sleep Summary DB/Inst: ZIDW/zidw Snaps: 1333-1334 -> ordered by number of sleeps desc Wait Mutex Type Location Sleeps Time (ms) --------------------- -------------------------------- ------------ ------------ Cursor Pin kksfbc [KKSCHLFSP2] 7,164,067 0 Cursor Pin kksLockDelete [KKSCHLPIN6] 5,864,420 0 Library Cache kgllkdl1 85 509 0 Library Cache kglget2 2 353 0 Library Cache kgllkc1 57 289 0 Library Cache kglhdgn2 106 281 0 Library Cache kglhdgn1 62 222 0 Cursor Pin kkslce [KKSCHLPIN2] 196 0 Library Cache kglpin1 4 143 0 Library Cache kglpndl1 95 135 0 Library Cache kglpnal1 90 131 0 Library Cache kglget1 1 7 0 Library Cache kglini1 32 7 0 Library Cache kglllal3 111 4 0 Cursor Pin kksfbc [KKSCHLPIN1] 4 0 Cursor Parent kkscsAddChildNode [KKSPRTLOC34] 3 0 hash table kkscsSearchChildList [KKSHBKLOC2 3 0 Library Cache kglobpn1 71 2 0 Library Cache kglrdtin1 44 2 0 Library Cache kgldtin1 42 1 0 Library Cache kglhdgh1 64 1 0 Library Cache kgllldl2 112 1 0 Cursor Stat kkocsStoreBindAwareStats [KKSSTA 1 0 Cursor Parent kksLoadChild [KKSPRTLOC4] 1 0 Cursor Parent kksLoadChild [KKSPRTLOC6] 1 0 Cursor Parent kkscsPruneChild [KKSPRTLOC35] 1 0 Cursor Parent kksheqd [KKSPRTLOC12] 1 0 ------------------------------------------------------------- Parent Latch Statistics DB/Inst: ZIDW/zidw Snaps: 1333-1334 No data exists for this section of the report. ------------------------------------------------------------- Child Latch Statistics DB/Inst: ZIDW/zidw Snaps: 1333-1334 No data exists for this section of the report. ------------------------------------------------------------- Segments by Logical Reads DB/Inst: ZIDW/zidw Snaps: 1333-1334 -> Total Logical Reads: 1,007,397,855 -> Captured Segments account for 89.5% of Total Tablespace Subobject Obj. Logical Owner Name Object Name Name Type Reads %Total ---------- ---------- -------------------- ---------- ----- ------------ ------- RDS RDS CELL_ID_WISE_SUB_REP TABLE 179,246,368 17.79 DDS DDS_NEW DW_SCUBSCRIBER_KEY_P INDEX 142,000,832 14.10 DDS DDS_NEW IDX_OPERATOR_CODE_FL INDEX 64,497,552 6.40 DDS DDS_NEW SUBSCRIBER_DIM TABLE 60,252,368 5.98 DDS DDS_NEW SUBSCRIBER_NO_INDX INDEX 36,872,272 3.66 ------------------------------------------------------------- Segments by Physical Reads DB/Inst: ZIDW/zidw Snaps: 1333-1334 -> Total Physical Reads: 35,744,164 -> Captured Segments account for 66.3% of Total Tablespace Subobject Obj. Physical Owner Name Object Name Name Type Reads %Total ---------- ---------- -------------------- ---------- ----- ------------ ------- ADS ADS_NEW LC_DETAILS_FACT_TEMP TABLE 17,308,460 48.42 TDS TDS LC_DETAILS_TEMP TABLE 5,007,736 14.01 ADS LC_DETAIL_ LC_DETAILS_FACT _22JUN2013 TABLE 1,158,458 3.24 ADS ADS_NEW DAILY_SUBSCRIBER_REV _01APR2013 TABLE 81,056 .23 ADS ADS_NEW ARPU_TEMP TABLE 52,547 .15 ------------------------------------------------------------- Segments by Physical Read Requests DB/Inst: ZIDW/zidw Snaps: 1333-1334 -> Total Physical Read Requests: 679,473 -> Captured Segments account for 60.7% of Total Tablespace Subobject Obj. Phys Read Owner Name Object Name Name Type Requests %Total ---------- ---------- -------------------- ---------- ----- ------------ ------- ADS ADS_NEW LC_DETAILS_FACT_TEMP TABLE 271,428 39.95 TDS TDS LC_DETAILS_TEMP TABLE 78,808 11.60 DDS DDS_NEW DW_SCUBSCRIBER_KEY_P INDEX 29,635 4.36 ADS LC_DETAIL_ LC_DETAILS_FACT _22JUN2013 TABLE 18,260 2.69 RDS RDS CELL_ID_TRANSDATE_IN INDEX 4,956 .73 ------------------------------------------------------------- Segments by UnOptimized Reads DB/Inst: ZIDW/zidw Snaps: 1333-1334 -> Total UnOptimized Read Requests: 679,473 -> Captured Segments account for 60.7% of Total Tablespace Subobject Obj. UnOptimized Owner Name Object Name Name Type Reads %Total ---------- ---------- -------------------- ---------- ----- ------------ ------- ADS ADS_NEW LC_DETAILS_FACT_TEMP TABLE 271,428 39.95 TDS TDS LC_DETAILS_TEMP TABLE 78,808 11.60 DDS DDS_NEW DW_SCUBSCRIBER_KEY_P INDEX 29,635 4.36 ADS LC_DETAIL_ LC_DETAILS_FACT _22JUN2013 TABLE 18,260 2.69 RDS RDS CELL_ID_TRANSDATE_IN INDEX 4,956 .73 ------------------------------------------------------------- Segments by Optimized Reads DB/Inst: ZIDW/zidw Snaps: 1333-1334 No data exists for this section of the report. ------------------------------------------------------------- Segments by Direct Physical Reads DB/Inst: ZIDW/zidw Snaps: 1333-1334 -> Total Direct Physical Reads: 35,624,342 -> Captured Segments account for 66.4% of Total Tablespace Subobject Obj. Direct Owner Name Object Name Name Type Reads %Total ---------- ---------- -------------------- ---------- ----- ------------ ------- ADS ADS_NEW LC_DETAILS_FACT_TEMP TABLE 17,307,340 48.58 TDS TDS LC_DETAILS_TEMP TABLE 5,007,246 14.06 ADS LC_DETAIL_ LC_DETAILS_FACT _22JUN2013 TABLE 1,158,271 3.25 ADS ADS_NEW DAILY_SUBSCRIBER_REV _01APR2013 TABLE 80,673 .23 ADS ADS_NEW ARPU_TEMP TABLE 52,477 .15 ------------------------------------------------------------- Segments by Physical Writes DB/Inst: ZIDW/zidw Snaps: 1333-1334 -> Total Physical Writes: 2,295,713 -> Captured Segments account for 92.0% of Total Tablespace Subobject Obj. Physical Owner Name Object Name Name Type Writes %Total ---------- ---------- -------------------- ---------- ----- ------------ ------- ADS LC_DETAIL_ LC_DETAILS_FACT _23JUN2013 TABLE 1,087,958 47.39 ADS ADS_NEW LC_DETAILS_FACT_TEMP TABLE 489,904 21.34 ADS ADS_NEW DAILY_SUBSCRIBER_REV _01APR2013 TABLE 143,092 6.23 ADS ADS_NEW DAILY_SUBSCRIBER_REV _02APR2013 TABLE 103,121 4.49 ADS ADS_NEW ARPU_TEMP TABLE 84,204 3.67 ------------------------------------------------------------- Segments by Physical Write Requests DB/Inst: ZIDW/zidw Snaps: 1333-1334 -> Total Physical Write Requestss: 1,481,684 -> Captured Segments account for 91.6% of Total Tablespace Subobject Obj. Phys Write Owner Name Object Name Name Type Requests %Total ---------- ---------- -------------------- ---------- ----- ------------ ------- ADS LC_DETAIL_ LC_DETAILS_FACT _23JUN2013 TABLE 679,499 45.86 ADS ADS_NEW LC_DETAILS_FACT_TEMP TABLE 319,468 21.56 ADS ADS_NEW DAILY_SUBSCRIBER_REV _02APR2013 TABLE 78,277 5.28 ADS ADS_NEW DAILY_SUBSCRIBER_REV _01APR2013 TABLE 73,273 4.95 ADS ADS_NEW ARPU_TEMP TABLE 51,864 3.50 ------------------------------------------------------------- Segments by Direct Physical Writes DB/Inst: ZIDW/zidw Snaps: 1333-1334 -> Total Direct Physical Writes: 30,068 -> Captured Segments account for 3.4% of Total Tablespace Subobject Obj. Direct Owner Name Object Name Name Type Writes %Total ---------- ---------- -------------------- ---------- ----- ------------ ------- RDS RDS INDX_MKT_VAS_TRANSDA _23JUN2013 INDEX 789 2.62 SYS SYSAUX WRH$_ACTIVE_SESSION_ 07324_1328 TABLE 232 .77 OWBSYS OWF_MGR SYS_LOB0000171203C00 LOB 15 .05 ------------------------------------------------------------- Segments by Table Scans DB/Inst: ZIDW/zidw Snaps: 1333-1334 -> Total Table Scans: 11,406 -> Captured Segments account for 68.6% of Total Tablespace Subobject Obj. Table Owner Name Object Name Name Type Scans %Total ---------- ---------- -------------------- ---------- ----- ------------ ------- ADS ADS_NEW LC_DETAILS_FACT_TEMP TABLE 5,620 49.27 TDS TDS LC_DETAILS_TEMP TABLE 1,496 13.12 ADS ADS_NEW ARPU_TEMP TABLE 350 3.07 ADS ADS_NEW ARPU_TEMP TABLE 349 3.06 RDS RDS CELL_ID_WISE_SUB_REP TABLE 1 .01 ------------------------------------------------------------- Segments by DB Blocks Changes DB/Inst: ZIDW/zidw Snaps: 1333-1334 -> % 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 ---------- ---------- -------------------- ---------- ----- ------------ ------- ADS LC_DETAIL_ LC_DETAILS_FACT _23JUN2013 TABLE 3,520,304 50.17 ADS ADS_NEW LC_DETAILS_FACT_TEMP TABLE 927,456 13.22 ADS ADS_NEW DAILY_SUBSCRIBER_REV _01APR2013 TABLE 444,016 6.33 ADS ADS_NEW DAILY_SUBSCRIBER_REV _02APR2013 TABLE 443,744 6.32 ADS ADS_NEW ARPU_TEMP TABLE 387,392 5.52 ------------------------------------------------------------- Segments by Row Lock Waits DB/Inst: ZIDW/zidw Snaps: 1333-1334 -> % 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 ---------- ---------- -------------------- ---------- ----- ------------ ------- OWBSYS OWF_MGR OWB$WB_RT_AUDIT_EXEC TABLE 9 81.82 OWBSYS OWF_MGR WB_RT_IDX_EX_W_A_EU INDEX 2 18.18 ------------------------------------------------------------- Segments by ITL Waits DB/Inst: ZIDW/zidw Snaps: 1333-1334 No data exists for this section of the report. ------------------------------------------------------------- Segments by Buffer Busy Waits DB/Inst: ZIDW/zidw Snaps: 1333-1334 -> % 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 ---------- ---------- -------------------- ---------- ----- ------------ ------- ADS ADS_NEW LC_DETAILS_FACT_TEMP TABLE 239 77.85 SYS SYSTEM AUD$ TABLE 31 10.10 OWBSYS OWF_MGR WB_RT_NOTIFY_QUEUE_T TABLE 30 9.77 TDS TDS LC_DETAILS_TEMP TABLE 3 .98 ADS ADS_NEW ARPU_TEMP TABLE 1 .33 ------------------------------------------------------------- Dictionary Cache Stats DB/Inst: ZIDW/zidw Snaps: 1333-1334 -> "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 65 0.0 0 N/A 2 1 dc_files 48 0.0 0 N/A 0 48 dc_global_oids 1,307 0.0 0 N/A 0 337 dc_histogram_data 5,059 0.1 0 N/A 0 993 dc_histogram_defs 25,299 4.4 0 N/A 0 10,641 dc_object_grants 997 8.1 0 N/A 0 292 dc_objects 9,481 1.2 0 N/A 37 4,526 dc_profiles 73 0.0 0 N/A 0 1 dc_rollback_segments 3,455 0.0 0 N/A 0 82 dc_segments 6,136 24.0 0 N/A 594 8,798 dc_sequences 17 5.9 0 N/A 17 9 dc_tablespaces 21,077 0.0 0 N/A 0 30 dc_users 36,211 0.0 0 N/A 0 189 global database name 2,847 0.0 0 N/A 0 1 outstanding_alerts 162 0.0 0 N/A 0 27 sch_lj_oids 5 0.0 0 N/A 0 13 ------------------------------------------------------------- Library Cache Activity DB/Inst: ZIDW/zidw Snaps: 1333-1334 -> "Pct Misses" should be very low Get Pct Pin Pct Invali- Namespace Requests Miss Requests Miss Reloads dations --------------- ------------ ------ -------------- ------ ---------- -------- ACCOUNT_STATUS 104 1.9 0 N/A 0 0 APP CONTEXT 63 0.0 1,134 0.0 0 0 BODY 1,250 0.3 18,171 0.1 19 1 CLUSTER 80 0.0 80 0.0 0 0 DBLINK 225 0.0 0 N/A 0 0 DIRECTORY 7 0.0 9 0.0 0 0 EDITION 431 0.0 502 0.0 0 0 INDEX 31 3.2 38 5.3 0 0 JAVA DATA 48 0.0 48 0.0 0 0 JAVA RESOURCE 64 0.0 112 0.0 0 0 QUEUE 873 0.0 3,265 0.0 0 0 SCHEMA 213 0.9 0 N/A 0 0 SQL AREA 6,362 5.7 278,303,601 -0.8 152 59 SQL AREA BUILD 465 82.8 0 N/A 0 0 SQL AREA STATS 446 88.1 446 88.1 0 0 SUBSCRIPTION 34 0.0 34 0.0 0 0 TABLE/PROCEDURE 235,685 0.0 293,491 0.1 146 0 TRANSFORMATION 361 0.0 361 0.0 0 0 TRIGGER 183 0.0 189 0.0 0 0 ------------------------------------------------------------- Memory Dynamic Components DB/Inst: ZIDW/zidw Snaps: 1333-1334 -> 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 22,688.00 22,688.00 15,136.00 23,072.00 0 GRO/DEF KEEP buffer cac .00 .00 .00 .00 0 STA/ PGA Target 18,048.00 18,048.00 16,384.00 24,224.00 0 GRO/MAN RECYCLE buffer .00 .00 .00 .00 0 STA/ SGA Target 24,576.00 24,576.00 16,768.00 24,576.00 0 SHR/DEF Shared IO Pool .00 .00 .00 .00 0 STA/ java pool 192.00 192.00 64.00 192.00 0 GRO/IMM large pool 96.00 96.00 32.00 800.00 0 SHR/DEF shared pool 1,408.00 1,408.00 1,216.00 1,536.00 0 SHR/DEF streams pool 64.00 64.00 32.00 96.00 0 SHR/DEF ------------------------------------------------------------- Memory Resize Operations Summary DB/Inst: ZIDW/zidw Snaps: 1333-1334 No data exists for this section of the report. ------------------------------------------------------------- Memory Resize Ops DB/Inst: ZIDW/zidw Snaps: 1333-1334 No data exists for this section of the report. ------------------------------------------------------------- Process Memory Summary DB/Inst: ZIDW/zidw Snaps: 1333-1334 -> 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 709.5 N/A 3.3 2.4 18 23 212 212 PL/SQL 347.8 339.0 1.7 12.5 106 267 210 210 SQL 229.5 4.4 1.2 1.1 4 955 192 152 JAVA 104.7 102.3 8.1 2.8 10 11 13 13 Freeable 102.0 .0 1.5 3.2 17 N/A 70 70 E SQL 2,855.0 2,846.5 20.0 41.6 140 332 143 125 Other 629.5 N/A 3.9 3.3 18 23 163 163 Freeable 443.3 .0 4.4 18.3 161 N/A 101 101 PL/SQL 312.8 308.1 1.9 17.7 211 267 161 154 JAVA 104.7 102.3 8.1 2.8 10 11 13 13 ------------------------------------------------------------- SGA Memory Summary DB/Inst: ZIDW/zidw Snaps: 1333-1334 End Size (Bytes) SGA regions Begin Size (Bytes) (if different) ------------------------------ ------------------- ------------------- Database Buffers 23,790,092,288 Fixed Size 2,186,536 Redo Buffers 25,206,784 Variable Size 1,845,494,488 ------------------- sum 25,662,980,096 ------------------------------------------------------------- SGA breakdown difference DB/Inst: ZIDW/zidw Snaps: 1333-1334 -> 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 JOXLE 29.7 29.7 0.00 java free memory 161.4 161.4 0.00 large PX msg pool 14.3 18.2 27.27 large free memory 81.3 77.4 -4.80 shared ASH buffers 48.0 48.0 0.00 shared ASM extent pointer array 172.9 172.9 0.00 shared KGH: NO ACCESS 31.8 31.8 0.00 shared KGLH0 147.1 148.2 0.77 shared KGLHD 21.7 21.9 0.88 shared KTI-UNDO 26.9 26.9 0.00 shared SQLA 111.4 108.5 -2.64 shared SQLP 20.6 19.4 -5.75 shared db_block_hash_buckets 89.0 89.0 0.00 shared free memory 398.7 403.7 1.28 shared private strands 53.5 53.5 0.00 shared state objects 15.0 15.0 0.00 shared write state object 14.6 14.6 0.00 stream free memory 64.0 64.0 0.00 buffer_cache 22,688.0 22,688.0 0.00 fixed_sga 2.1 2.1 0.00 log_buffer 24.0 24.0 0.00 ------------------------------------------------------------- Streams CPU/IO Usage DB/Inst: ZIDW/zidw Snaps: 1333-1334 -> Streams processes ordered by CPU Time, descending User IO SYS IO Session Type First Logon CPU time(s) Wait time(s) Wait time(s) -------------------- ------------- ------------ ------------ ------------ QMON Slaves 0526 18:00:25 0.3 0.0 0.0 QMON Coordinator 0526 18:00:15 0.1 0.0 0.0 ------------------------------------------------------------- Streams Capture DB/Inst: ZIDW/zidw Snaps: 1333-1334 No data exists for this section of the report. ------------------------------------------------------------- Streams Capture Rate DB/Inst: ZIDW/zidw Snaps: 1333-1334 No data exists for this section of the report. ------------------------------------------------------------- Streams Apply DB/Inst: ZIDW/zidw Snaps: 1333-1334 No data exists for this section of the report. ------------------------------------------------------------- Streams Apply Rate DB/Inst: ZIDW/zidw Snaps: 1333-1334 No data exists for this section of the report. ------------------------------------------------------------- Buffered Queues DB/Inst: ZIDW/zidw Snaps: 1333-1334 No data exists for this section of the report. ------------------------------------------------------------- Buffered Queue Subscribers DB/Inst: ZIDW/zidw Snaps: 1333-1334 No data exists for this section of the report. ------------------------------------------------------------- Rule Set DB/Inst: ZIDW/zidw Snaps: 1333-1334 No data exists for this section of the report. ------------------------------------------------------------- Persistent Queues DB/Inst: ZIDW/zidw Snaps: 1333-1334 -> 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 ---------- ---------- ------ ------- -------- -------- --------- ------- OWBSYS.AQ$_WB_RT_NOTIFY_QUEUE_TAB_E(171442) 32 0 0.0 0.0 0.0 0.0 0.0 0.0 OWBSYS.WB_RT_NOTIFY_QUEUE(171443) 64 32 100.0 0.0 0.1 237.0 0.0 0.0 OWBSYS.WB_RT_SERVICE_QUEUE(171467) 32 32 100.0 0.0 0.1 37.6 0.0 0.0 ------------------------------------------------------------- Persistent Queues Rate DB/Inst: ZIDW/zidw Snaps: 1333-1334 -> 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 ---------- ---------- ------------- ------------- OWBSYS.AQ$_WB_RT_NOTIFY_QUEUE_TAB_E(171442) 0.01 0.00 0.00 N/A OWBSYS.WB_RT_NOTIFY_QUEUE(171443) 0.02 0.01 0.00 7.41 OWBSYS.WB_RT_SERVICE_QUEUE(171467) 0.01 0.01 0.00 1.17 ------------------------------------------------------------- Persistent Queue Subscribers DB/Inst: ZIDW/zidw Snaps: 1333-1334 No data exists for this section of the report. ------------------------------------------------------------- Resource Limit Stats DB/Inst: ZIDW/zidw Snap: 1334 -> only rows with Current or Maximum Utilization > 80% of Limit are shown -> ordered by resource name Current Maximum Initial Resource Name Utilization Utilization Allocation Limit ------------------------------ ------------ ------------ ---------- ---------- processes 164 432 500 500 ------------------------------------------------------------- Shared Servers Activity DB/Inst: ZIDW/zidw Snaps: 1333-1334 -> 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: ZIDW/zidw Snaps: 1333-1334 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: ZIDW/zidw Snaps: 1333-1334 -> Statistics are combined for all servers -> Incoming and Outgoing Net % are included in %Busy Total Server Incoming Outgoing Time (s) %Busy %Idle Net % Net % -------------- -------- -------- --------- --------- 3,611 0.0 100.0 0.0 0.0 ------------------------------------------------------------- Shared Servers Common Queue DB/Inst: ZIDW/zidw Snaps: 1333-1334 No data exists for this section of the report. ------------------------------------------------------------- Shared Servers Dispatchers DB/Inst: ZIDW/zidw Snaps: 1333-1334 -> 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 3,611 0.0 100.0 0 0 ------------------------------------------------------------- init.ora Parameters DB/Inst: ZIDW/zidw Snaps: 1333-1334 End value Parameter Name Begin value (if different) ----------------------------- --------------------------------- -------------- _mutex_spin_count 100 _mutex_wait_scheme 2 _mutex_wait_time 10 audit_file_dest /db0/app/oracle/admin/zidw/adump audit_trail DB compatible 11.2.0.0.0 control_files +DSK_GRP1/zidw/controlfile/curren db_block_size 16384 db_cache_size 15032385536 db_create_file_dest +DSK_GRP1 db_domain db_name zidw diagnostic_dest /db0/app/oracle dispatchers (PROTOCOL=TCP) (SERVICE=zidwXDB) fast_start_mttr_target 2000 memory_max_target 42949672960 memory_target 42949672960 open_cursors 300 pga_aggregate_target 0 processes 500 remote_login_passwordfile EXCLUSIVE resource_manager_plan sessions 792 sga_max_size 25769803776 sga_target 0 spfile +DSK_GRP1/zidw/spfilezidw.ora transactions 4000 undo_retention 14400 undo_tablespace UNDOTBS1 ------------------------------------------------------------- init.ora Multi-Valued Parameters DB/Inst: ZIDW/zidw Snaps: 1333-1334 -> This section only displays parameters that have more one value ->'(NULL)' indicates a missing parameter value -> A blank in the End Snapshot indicates the same value as the BeginSnapshot End value Parameter Name Begin value (if different) ----------------------------- --------------------------------- -------------- control_files +DSK_GRP1/zidw/controlfile/curren +DSK_GRP1/zidw/controlfile/curren ------------------------------------------------------------- Dynamic Remastering Stats DB/Inst: ZIDW/zidw Snaps: 1333-1334 No data exists for this section of the report. ------------------------------------------------------------- End of Report