STATSPACK report for Database DB Id Instance Inst Num Startup Time Release RAC ~~~~~~~~ ----------- ------------ -------- --------------- ----------- --- 4062147016 jonah 1 30-Oct-08 19:56 10.2.0.3.0 NO Host Name: dbsrvr06.op.xz.g Num CPUs: 4 Phys Memory (MB): 3 ~~~~ Snapshot Snap Id Snap Time Sessions Curs/Sess Comment ~~~~~~~~ ---------- ------------------ -------- --------- ------------------- Begin Snap: 2361 30-Oct-08 20:00:02 122 17.5 End Snap: 2581 31-Oct-08 22:00:11 153 22.3 Elapsed: 1,560.15 (mins) Cache Sizes Begin End ~~~~~~~~~~~ ---------- ---------- Buffer Cache: 2,128M Std Block Size: 8K Shared Pool Size: 176M Log Buffer: 13,921K Load Profile Per Second Per Transaction ~~~~~~~~~~~~ --------------- --------------- Redo size: 542,677.63 12,361.10 Logical reads: 14,292.54 325.56 Block changes: 3,278.24 74.67 Physical reads: 6,323.28 144.03 Physical writes: 143.56 3.27 User calls: 528.63 12.04 Parses: 44.57 1.02 Hard parses: 0.36 0.01 Sorts: 5.24 0.12 Logons: 0.03 0.00 Executes: 536.30 12.22 Transactions: 43.90 % Blocks changed per Read: 22.94 Recursive Call %: 45.61 Rollback per transaction %: 0.02 Rows per Sort: 178.95 Instance Efficiency Percentages ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Buffer Nowait %: 99.89 Redo NoWait %: 100.00 Buffer Hit %: 55.77 In-memory Sort %: 100.00 Library Hit %: 99.72 Soft Parse %: 99.18 Execute to Parse %: 91.69 Latch Hit %: 99.87 Parse CPU to Parse Elapsd %: 21.80 % Non-Parse CPU: 99.43 Shared Pool Statistics Begin End ------ ------ Memory Usage %: 79.45 82.28 % SQL with executions>1: 63.11 71.23 % Memory for SQL w/exec>1: 63.89 65.59 Top 5 Timed Events Avg %Total ~~~~~~~~~~~~~~~~~~ wait Call Event Waits Time (s) (ms) Time ----------------------------------------- ------------ ----------- ------ ------ log file sync 4,985,417 471,069 94 47.0 db file sequential read 9,247,119 313,685 34 31.3 log file parallel write 3,068,774 71,145 23 7.1 db file scattered read 36,521,488 58,008 2 5.8 CPU time 33,020 3.3 ------------------------------------------------------------- Host CPU (CPUs: 4) ~~~~~~~~ Load Average Begin End User System Idle WIO WCPU ------- ------- ------- ------- ------- ------- -------- 1.26 1.24 13.23 12.55 73.44 40.23 Instance CPU ~~~~~~~~~~~~ % of total CPU for Instance: 12.88 % of busy CPU for Instance: 48.49 %DB time waiting for CPU - Resource Mgr: Memory Statistics Begin End ~~~~~~~~~~~~~~~~~ ------------ ------------ Host Mem (MB): 3.5 1.3 SGA use (MB): 2,368.0 2,368.0 PGA use (MB): 680.4 876.7 % Host Mem used for SGA+PGA: 87893.7 256573.6 ------------------------------------------------------------- Time Model System Stats DB/Inst: jonah/jonah Snaps: 2361-2581 -> Ordered by % of DB time desc, Statistic name Statistic Time (s) % of DB time ----------------------------------- -------------------- ------------ sql execute elapsed time 407,790.5 45.4 DB CPU 45,551.6 5.1 PL/SQL execution elapsed time 8,044.5 .9 parse time elapsed 2,404.2 .3 hard parse elapsed time 2,162.3 .2 sequence load elapsed time 426.9 .0 PL/SQL compilation elapsed time 207.2 .0 RMAN cpu time (backup/restore) 110.2 .0 repeated bind elapsed time 56.7 .0 connection management call elapsed 56.4 .0 hard parse (sharing criteria) elaps 25.9 .0 hard parse (bind mismatch) elapsed 15.4 .0 inbound PL/SQL rpc elapsed time 9.0 .0 failed parse elapsed time 5.9 .0 DB time 898,395.2 background elapsed time 92,025.5 background cpu time 2,574.7 ------------------------------------------------------------- Wait Events DB/Inst: jonah/jonah Snaps: 2361-2581 -> s - second, cs - centisecond, ms - millisecond, us - microsecond -> %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0 -> Only events with Total Wait Time (s) >= .001 are shown -> ordered by Total Wait Time desc, Waits desc (idle events last) Avg %Time Total Wait wait Waits Event Waits -outs Time (s) (ms) /txn --------------------------------- ------------ ------ ---------- ------ -------- log file sync 4,985,417 0 471,069 94 1.2 db file sequential read 9,247,119 0 313,685 34 2.3 log file parallel write 3,068,774 0 71,145 23 0.7 db file scattered read 36,521,488 0 58,008 2 8.9 SQL*Net message from dblink 1,043,931 0 14,907 14 0.3 direct path write 475,121 0 11,827 25 0.1 direct path read 423,439 0 10,867 26 0.1 db file parallel write 1,173,582 0 7,426 6 0.3 RMAN backup & recovery I/O 301,252 0 2,724 9 0.1 control file parallel write 34,666 0 2,648 76 0.0 log file sequential read 51,538 0 1,174 23 0.0 control file sequential read 213,591 0 1,167 5 0.1 buffer busy waits 1,509,194 0 580 0 0.4 enq: TX - index contention 18,291 0 578 32 0.0 log file switch completion 1,046 22 419 401 0.0 rdbms ipc reply 6,461 0 221 34 0.0 read by other session 4,143 0 149 36 0.0 cursor: pin S wait on X 13,347 100 144 11 0.0 latch: cache buffers chains 622,567 0 136 0 0.2 enq: TC - contention 46 98 131 2852 0.0 Log archive I/O 145,934 0 122 1 0.0 Data file init write 34,566 0 101 3 0.0 library cache load lock 205 2 80 391 0.0 enq: TX - contention 85 18 61 719 0.0 log buffer space 4,301 6 59 14 0.0 enq: CF - contention 63 8 53 836 0.0 SQL*Net more data to client 2,372,670 0 44 0 0.6 enq: SQ - contention 50,109 0 33 1 0.0 direct path read temp 65,873 0 18 0 0.0 SQL*Net break/reset to client 3,663 0 15 4 0.0 direct path write temp 3,536 0 13 4 0.0 LGWR wait for redo copy 86,699 0 13 0 0.0 latch free 5,723 0 12 2 0.0 os thread startup 182 3 11 62 0.0 buffer exterminate 19 58 11 576 0.0 SGA: allocation forcing component 957 95 11 11 0.0 latch: In memory undo latch 26,634 0 9 0 0.0 enq: HW - contention 163 0 9 55 0.0 undo segment extension 2,200,897 100 8 0 0.5 db file single write 63 0 7 118 0.0 SQL*Net more data to dblink 336,596 0 7 0 0.1 latch: redo allocation 7,644 0 5 1 0.0 log file single write 200 0 4 22 0.0 latch: shared pool 2,761 0 3 1 0.0 reliable message 303 1 3 8 0.0 latch: enqueue hash chains 4,630 0 2 1 0.0 row cache lock 37 0 2 61 0.0 ASM background running 20 100 2 99 0.0 SQL*Net message to dblink 1,043,931 0 2 0 0.3 enq: JS - queue lock 5 0 1 289 0.0 Wait Events DB/Inst: jonah/jonah Snaps: 2361-2581 -> s - second, cs - centisecond, ms - millisecond, us - microsecond -> %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0 -> Only events with Total Wait Time (s) >= .001 are shown -> ordered by Total Wait Time desc, Waits desc (idle events last) Avg %Time Total Wait wait Waits Event Waits -outs Time (s) (ms) /txn --------------------------------- ------------ ------ ---------- ------ -------- latch: library cache 1,930 0 1 1 0.0 wait list latch free 98 0 1 11 0.0 PL/SQL lock timer 1 100 1 977 0.0 db file parallel read 31 0 1 30 0.0 kksfbc child completion 19 95 1 47 0.0 buffer deadlock 1,801 100 1 0 0.0 latch: row cache objects 729 0 1 1 0.0 latch: cache buffers lru chain 546 0 0 1 0.0 latch: session allocation 443 0 0 1 0.0 latch: object queue header operat 1,004 0 0 0 0.0 latch: undo global data 442 0 0 1 0.0 library cache pin 2 0 0 121 0.0 latch: library cache pin 480 0 0 0 0.0 enq: TX - row lock contention 4 0 0 40 0.0 recovery area: computing obsolete 353 0 0 0 0.0 single-task message 1 0 0 136 0.0 latch: library cache lock 124 0 0 1 0.0 enq: RO - fast object reuse 17 0 0 2 0.0 SQL*Net more data from dblink 1 0 0 33 0.0 cursor: pin S 75 0 0 0 0.0 control file single write 18 0 0 1 0.0 latch: redo writing 24 0 0 1 0.0 latch: redo copy 7 0 0 2 0.0 latch: messages 30 0 0 0 0.0 latch: checkpoint queue latch 12 0 0 1 0.0 enq: TX - allocate ITL entry 5 0 0 1 0.0 cursor: mutex X 491 0 0 0 0.0 cursor: mutex S 21 0 0 0 0.0 SQL*Net message from client 48,148,619 0 8,061,331 167 11.7 Streams AQ: waiting for messages 4,452,124 0 1,279,241 287 1.1 class slave wait 1,860 37 100,690 54134 0.0 ASM background timer 22,219 0 91,377 4113 0.0 Streams AQ: qmn slave idle wait 3,341 0 91,124 27274 0.0 Streams AQ: qmn coordinator idle 6,714 50 91,123 13572 0.0 virtual circuit status 3,121 100 91,115 29194 0.0 jobq slave wait 31,297 95 90,823 2902 0.0 Streams AQ: waiting for time mana 18,279 85 90,646 4959 0.0 wait for unread message on broadc 814,216 0 32,468 40 0.2 KSV master wait 1,128 2 142 126 0.0 SQL*Net more data from client 2,226,437 0 105 0 0.5 SQL*Net message to client 48,148,642 0 76 0 11.7 ------------------------------------------------------------- Background Wait Events DB/Inst: jonah/jonah Snaps: 2361-2581 -> %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0 -> Only events with Total Wait Time (s) >= .001 are shown -> ordered by Total Wait Time desc, Waits desc (idle events last) Avg %Time Total Wait wait Waits Event Waits -outs Time (s) (ms) /txn --------------------------------- ------------ ------ ---------- ------ -------- log file parallel write 3,068,794 0 71,149 23 0.7 db file parallel write 1,173,628 0 7,427 6 0.3 control file parallel write 32,244 0 2,432 75 0.0 log file sequential read 51,538 0 1,174 23 0.0 db file sequential read 18,115 0 489 27 0.0 control file sequential read 34,063 0 290 9 0.0 events in waitclass Other 95,533 0 255 3 0.0 Log archive I/O 145,934 0 122 1 0.0 log buffer space 4,288 6 54 13 0.0 db file scattered read 1,805 0 24 13 0.0 direct path read 3,626 0 20 5 0.0 direct path write 2,646 0 6 2 0.0 os thread startup 169 0 5 30 0.0 log file switch completion 16 13 5 314 0.0 log file single write 200 0 4 22 0.0 undo segment extension 932,645 100 3 0 0.2 cursor: pin S wait on X 107 100 1 11 0.0 buffer busy waits 9,398 0 1 0 0.0 read by other session 10 0 0 49 0.0 enq: TX - index contention 28 0 0 10 0.0 log file sync 2 0 0 90 0.0 latch: cache buffers chains 674 0 0 0 0.0 latch: shared pool 60 0 0 3 0.0 latch: In memory undo latch 229 0 0 0 0.0 latch: redo writing 24 0 0 1 0.0 row cache lock 1 0 0 15 0.0 latch: library cache 12 0 0 0 0.0 rdbms ipc message 4,215,458 7 989,841 235 1.0 pmon timer 32,826 100 91,391 2784 0.0 ASM background timer 22,220 0 91,381 4113 0.0 Streams AQ: qmn slave idle wait 3,341 0 91,124 27274 0.0 Streams AQ: qmn coordinator idle 6,714 50 91,123 13572 0.0 Streams AQ: waiting for time mana 18,279 85 90,646 4959 0.0 smon timer 1,854 2 88,838 47917 0.0 KSV master wait 649 4 80 123 0.0 ------------------------------------------------------------- Wait Event Histogram DB/Inst: jonah/jonah Snaps: 2361-2581 -> Total Waits - units: K is 1000, M is 1000000, G is 1000000000 -> % of Waits - column heading: <=1s is truly <1024ms, >1s is truly >=1024ms -> % of Waits - value: .0 indicates value was <.05%, null is truly 0 -> Ordered by Event (idle events last) Total ----------------- % of Waits ------------------ Event Waits <1ms <2ms <4ms <8ms <16ms <32ms <=1s >1s -------------------------- ----- ----- ----- ----- ----- ----- ----- ----- ----- ASM background running 20 100.0 Data file init write 34K 71.5 3.3 6.5 13.7 3.5 .5 1.1 LGWR wait for redo copy 86K 96.1 2.7 1.0 .2 .1 .0 .0 Log archive I/O 145K 98.8 .0 .0 .1 .2 .3 .7 PL/SQL lock timer 1 100.0 RMAN backup & recovery I/O 301K 56.5 5.7 14.6 11.9 3.5 1.9 5.9 .0 SGA: allocation forcing co 957 .1 96.9 2.6 .4 SQL*Net break/reset to cli 3663 96.3 2.6 .6 .1 .1 .2 .1 SQL*Net message from dblin 1043K .0 .0 81.8 17.4 .8 .0 SQL*Net message to dblink 1043K 100.0 SQL*Net more data from dbl 1 100.0 SQL*Net more data to clien 2372K 100.0 .0 .0 .0 SQL*Net more data to dblin 336K 100.0 .0 .0 .0 .0 buffer busy waits 1509K 89.9 8.4 1.4 .2 .0 .0 .0 buffer deadlock 1801 88.0 8.9 2.3 .6 .2 .1 .1 buffer exterminate 19 26.3 5.3 68.4 control file parallel writ 34K 8.5 13.7 2.8 2.4 4.1 7.1 61.2 .1 control file sequential re 213K 91.5 1.1 .7 1.1 .6 .6 4.5 .0 control file single write 18 22.2 77.8 cursor: mutex S 21 100.0 cursor: mutex X 491 100.0 cursor: pin S 75 92.0 4.0 4.0 cursor: pin S wait on X 13K .2 99.5 .3 .0 db file parallel read 31 16.1 29.0 6.5 6.5 16.1 12.9 12.9 db file parallel write 1173K 82.3 8.1 2.2 .5 .5 .5 5.8 .0 db file scattered read 36M 57.5 37.1 2.1 1.7 .8 .6 .3 .0 db file sequential read 9247K 21.3 1.7 7.0 22.1 13.2 5.1 29.7 .0 db file single write 63 7.9 1.6 1.6 88.9 direct path read 423K 60.8 6.6 2.8 2.7 1.4 1.1 24.7 .0 direct path read temp 65K 98.2 .9 .6 .1 .1 .0 .0 direct path write 475K 64.5 5.1 1.8 1.0 .8 1.3 25.4 .0 direct path write temp 3536 54.6 37.0 4.1 1.0 .8 .4 2.1 enq: CF - contention 63 1.6 3.2 69.8 25.4 enq: FB - contention 4 100.0 enq: HW - contention 163 39.3 6.7 5.5 9.8 8.0 3.7 26.4 .6 enq: JS - queue lock 5 20.0 20.0 60.0 enq: RO - fast object reus 17 82.4 11.8 5.9 enq: SQ - contention 50K 95.4 3.9 .6 .0 .0 .0 .0 .0 enq: TC - contention 46 2.2 97.8 enq: TX - allocate ITL ent 5 40.0 40.0 20.0 enq: TX - contention 85 21.2 3.5 1.2 3.5 3.5 8.2 37.6 21.2 enq: TX - index contention 18K 24.5 6.4 10.9 16.4 10.9 5.2 25.6 .1 enq: TX - row lock content 4 25.0 25.0 25.0 25.0 kksfbc child completion 19 5.3 94.7 latch free 5561 74.5 14.4 9.2 1.0 .1 .4 .4 .1 latch: In memory undo latc 25K 92.4 6.3 1.1 .1 .0 .0 latch: cache buffers chain 608K 95.1 4.1 .7 .1 .0 .0 .0 latch: cache buffers lru c 537 88.5 6.1 3.7 .7 .2 .2 .6 latch: checkpoint queue la 12 75.0 16.7 8.3 latch: enqueue hash chains 4446 83.6 12.8 3.3 .3 .1 latch: library cache 1870 83.4 11.2 3.7 1.1 .4 .2 .1 Wait Event Histogram DB/Inst: jonah/jonah Snaps: 2361-2581 -> Total Waits - units: K is 1000, M is 1000000, G is 1000000000 -> % of Waits - column heading: <=1s is truly <1024ms, >1s is truly >=1024ms -> % of Waits - value: .0 indicates value was <.05%, null is truly 0 -> Ordered by Event (idle events last) Total ----------------- % of Waits ------------------ Event Waits <1ms <2ms <4ms <8ms <16ms <32ms <=1s >1s -------------------------- ----- ----- ----- ----- ----- ----- ----- ----- ----- latch: library cache lock 123 80.5 14.6 4.1 .8 latch: library cache pin 463 87.0 10.8 1.7 .2 .2 latch: messages 30 83.3 16.7 latch: object queue header 987 90.8 6.8 1.8 .6 latch: redo allocation 7416 84.9 8.6 4.0 1.5 .7 .3 .1 latch: redo copy 6 16.7 83.3 latch: redo writing 24 75.0 16.7 8.3 latch: row cache objects 712 77.9 12.8 6.9 2.2 .1 latch: session allocation 427 80.6 11.5 5.2 1.6 .7 .2 .2 latch: shared pool 2431 71.0 13.8 8.2 3.9 1.7 1.0 .3 latch: undo global data 424 80.2 14.4 4.7 .2 .5 library cache load lock 205 7.8 5.4 6.3 5.9 5.9 7.8 47.8 13.2 library cache pin 2 100.0 log buffer space 4301 72.0 7.8 3.1 1.0 1.0 1.6 13.5 log file parallel write 3068K 40.8 30.9 6.5 1.3 .6 .8 19.1 .0 log file sequential read 51K .5 .0 .1 59.5 12.4 9.1 18.3 log file single write 200 75.5 1.5 .5 2.0 1.0 19.5 log file switch completion 1046 2.7 1.3 1.4 5.4 3.7 8.1 77.2 log file sync 4985K 15.6 25.9 11.4 2.5 .8 .8 43.0 os thread startup 182 .5 .5 3.8 5.5 3.8 42.9 42.9 rdbms ipc reply 6461 75.6 6.2 2.7 1.3 1.4 1.1 11.5 .1 read by other session 4143 12.1 6.9 12.2 17.2 12.2 7.5 32.0 recovery area: computing a 1 100.0 recovery area: computing b 1 100.0 recovery area: computing d 1 100.0 recovery area: computing o 353 97.5 1.7 .3 .3 .3 reliable message 303 80.9 11.6 4.6 2.0 1.0 row cache lock 37 59.5 2.7 2.7 8.1 2.7 24.3 single-task message 1 100.0 undo segment extension 2200K 100.0 .0 .0 .0 .0 wait list latch free 98 100.0 ASM background timer 22K 9.5 1.8 1.1 .6 .3 .2 1.6 84.9 KSV master wait 1128 38.9 17.6 11.1 6.0 5.9 2.2 13.0 5.1 SQL*Net message from clien 48M 74.6 5.5 2.1 .8 .8 1.9 10.2 4.1 SQL*Net message to client 48M 100.0 .0 .0 .0 SQL*Net more data from cli 2226K 99.3 .4 .2 .1 .0 .0 .0 Streams AQ: qmn coordinato 6714 49.3 .3 .1 .0 .0 .0 50.2 Streams AQ: qmn slave idle 3341 .1 99.9 Streams AQ: waiting for me 4451K 10.2 1.3 2.4 3.9 6.8 12.1 57.5 5.8 Streams AQ: waiting for ti 18K 15.3 16.3 68.4 class slave wait 1860 29.6 2.8 1.1 4.6 2.4 1.6 7.3 50.6 dispatcher timer 1560 100.0 jobq slave wait 31K .0 .3 99.7 pmon timer 32K 4.9 .1 .0 .0 95.0 rdbms ipc message 4215K 25.3 16.6 20.5 15.3 8.2 4.0 5.2 4.8 smon timer 1854 2.6 .1 .1 .4 .8 10.7 85.4 virtual circuit status 3121 100.0 wait for unread message on 814K 5.6 3.5 8.5 17.4 25.2 16.7 22.9 ------------------------------------------------------------- SQL ordered by CPU DB/Inst: jonah/jonah Snaps: 2361-2581 -> Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code. -> Total DB CPU (s): 45,552 -> Captured SQL accounts for 69.3% of Total DB CPU -> SQL reported below exceeded 1.0% of Total DB CPU CPU CPU per Elapsd Old Time (s) Executions Exec (s) %Total Time (s) Buffer Gets Hash Value ---------- ------------ ---------- ------ ---------- --------------- ---------- 12027.95 515 23.36 26.4 60719.17 569,159,926 3260211111 Module: DBMS_SCHEDULER SELECT ROWID FROM ABC_PRODUCT WHERE IS_ARCHIVED = 'N' AND INSERT ED_DATETIME <= :B1 AND ROWNUM < 10001 4168.06 1,862,398 0.00 9.2 36514.67 87,394,472 2532280527 insert into Compound_qualifier (qualifier_value, unit_id, qu alifier_type_id, Compound_id, Compound_qualifier_id) values ( :1, :2, :3, :4, :5) 2689.40 2,057,604 0.00 5.9 16222.81 65,071,409 3020135196 insert into Compound (Measured_value, uncertainty, suppresse d, special_value_indicator, Compound_type_id, Unit_id, object_id, Compound_id) values (:1, :2, :3, :4, :5, :6, :7, :8) 2404.50 699,773 0.00 5.3 2457.69 36,341,570 4104056464 begin dbms_aqin.aq$_dequeue_in( :1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17, :18, :19, :20, :21, :22, :23, :24, :25, :26, :27, :28, :29); end; 1797.65 568,928 0.00 3.9 2633.05 26,978,491 914488053 begin dbms_aqin.aq$_dequeue_in(queue_name => :1, subscrib er => :2, msgid => :3, correlation => : 4, dequeue_mode => :5, navigation => :6, visibility => :7, wait => :8, enqueue_time => :9, state => :10, out_msgid => :11, out_correlat 1158.91 740,973 0.00 2.5 1868.61 7,838,497 4073933611 begin dbms_aqin.aq$_jms_enqueue_object_message(queue_name => :1, sender_name => :2, sender_addr=> :3, sender_protocol => :4, ori ginal_msgid => :5, correlation => :6, visibility => :7, priority => :8, delay => :9, expiration=> :10, relative_msgid => :11, se quence_deviation => :12, exception_queue => :13, payload_type => 628.52 416,733 0.00 1.4 42505.40 5,921,797 3359610184 insert into box_product (box_product_uuid, original_data_source, format, product_data_form, created_datetime, product, observati on_datetime, generating_component, is_valid, product_id) values (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10) 628.38 269,205 0.00 1.4 26706.35 10,181,503 908723933 insert into DMF_object (box_object_uuid, observation_datetime, r eceived_datetime, client_station_name, box_station_identifier, l atitude, longitude, station_elevation, reference_datum, decoding _component, dataset_format_id, special_message_indicator, origin al_message, input_id, object_id) values (:1, :2, :3, :4, :5, :6, 549.15 3,130,536 0.00 1.2 551.90 59,784,965 2891249205 select selectors0_.Compound_id as Element5_1_, selectors0_ .Compound_qualifier_id as Element1_1_, selectors0_.Element_quali SQL ordered by CPU DB/Inst: jonah/jonah Snaps: 2361-2581 -> Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code. -> Total DB CPU (s): 45,552 -> Captured SQL accounts for 69.3% of Total DB CPU -> SQL reported below exceeded 1.0% of Total DB CPU CPU CPU per Elapsd Old Time (s) Executions Exec (s) %Total Time (s) Buffer Gets Hash Value ---------- ------------ ---------- ------ ---------- --------------- ---------- fier_id as Element1_17_0_, selectors0_.qualifier_value as quali fier2_17_0_, selectors0_.unit_id as unit3_17_0_, selectors0_.q ualifier_type_id as qualifier4_17_0_ from Compound_qualifier 535.39 269,193 0.00 1.2 18380.96 9,176,502 4261001096 insert into DMF_OBJECT_ATTRIBUTE (attribute_type_id, attribute_v alue, object_id, object_attribute_id) values (:1, :2, :3, :4) 475.55 514 0.93 1.0 579.84 45,126,473 3038684705 Module: DBMS_SCHEDULER UPDATE ABC_PRODUCT SET IS_ARCHIVED = 'P' WHERE ROWID = :1 ------------------------------------------------------------- SQL ordered by Elapsed DB/Inst: jonah/jonah Snaps: 2361-2581 -> Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code. -> Total DB Time (s): 898,395 -> Captured SQL accounts for 25.6% of Total DB Time -> SQL reported below exceeded 1.0% of Total DB Time Elapsed Elap per CPU Old Time (s) Executions Exec (s) %Total Time (s) Physical Reads Hash Value ---------- ------------ ---------- ------ ---------- --------------- ---------- 60719.17 515 117.90 6.8 12027.95 567,314,121 3260211111 Module: DBMS_SCHEDULER SELECT ROWID FROM ABC_PRODUCT WHERE IS_ARCHIVED = 'N' AND INSERT ED_DATETIME <= :B1 AND ROWNUM < 10001 42505.40 416,733 0.10 4.7 628.52 892,995 3359610184 insert into box_product (box_product_uuid, original_data_source, format, product_data_form, created_datetime, product, observati on_datetime, generating_component, is_valid, product_id) values (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10) 36514.67 1,862,398 0.02 4.1 4168.06 2,311,745 2532280527 insert into Compound_qualifier (qualifier_value, unit_id, qu alifier_type_id, Compound_id, Compound_qualifier_id) values ( :1, :2, :3, :4, :5) 26706.35 269,205 0.10 3.0 628.38 855,121 908723933 insert into DMF_object (box_object_uuid, observation_datetime, r eceived_datetime, client_station_name, box_station_identifier, l atitude, longitude, station_elevation, reference_datum, decoding _component, dataset_format_id, special_message_indicator, origin al_message, input_id, object_id) values (:1, :2, :3, :4, :5, :6, 18380.96 269,193 0.07 2.0 535.39 620,876 4261001096 insert into DMF_OBJECT_ATTRIBUTE (attribute_type_id, attribute_v alue, object_id, object_attribute_id) values (:1, :2, :3, :4) 16222.81 2,057,604 0.01 1.8 2689.40 829,217 3020135196 insert into Compound (Measured_value, uncertainty, suppresse d, special_value_indicator, Compound_type_id, Unit_id, object_id, Compound_id) values (:1, :2, :3, :4, :5, :6, :7, :8) 11162.42 102,091 0.11 1.2 146.03 207,193 4275100488 insert into Input_data (input_data_uuid, name, is_valid, receive d_datetime, content, source_channel, original_source, dataset_fo rmat_id, input_id) values (:1, :2, :3, :4, :5, :6, :7, :8, :9) ------------------------------------------------------------- SQL ordered by Gets DB/Inst: jonah/jonah Snaps: 2361-2581 -> Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code. -> End Buffer Gets Threshold: 10000 Total Buffer Gets: 1,337,910,401 -> Captured SQL accounts for 73.8% of Total Buffer Gets -> SQL reported below exceeded 1.0% of Total Buffer Gets CPU Elapsd Old Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value --------------- ------------ -------------- ------ -------- --------- ---------- 569,159,926 515 1,105,164.9 42.5 ######## 60719.17 3260211111 Module: DBMS_SCHEDULER SELECT ROWID FROM ABC_PRODUCT WHERE IS_ARCHIVED = 'N' AND INSERT ED_DATETIME <= :B1 AND ROWNUM < 10001 87,394,472 1,862,398 46.9 6.5 4168.06 36514.67 2532280527 insert into Compound_qualifier (qualifier_value, unit_id, qu alifier_type_id, Compound_id, Compound_qualifier_id) values ( :1, :2, :3, :4, :5) 65,071,409 2,057,604 31.6 4.9 2689.40 16222.81 3020135196 insert into Compound (Measured_value, uncertainty, suppresse d, special_value_indicator, Compound_type_id, Unit_id, object_id, Compound_id) values (:1, :2, :3, :4, :5, :6, :7, :8) 59,784,965 3,130,536 19.1 4.5 549.15 551.90 2891249205 select selectors0_.Compound_id as Element5_1_, selectors0_ .Compound_qualifier_id as Element1_1_, selectors0_.Element_quali fier_id as Element1_17_0_, selectors0_.qualifier_value as quali fier2_17_0_, selectors0_.unit_id as unit3_17_0_, selectors0_.q ualifier_type_id as qualifier4_17_0_ from Compound_qualifier 45,126,473 514 87,794.7 3.4 475.55 579.84 3038684705 Module: DBMS_SCHEDULER UPDATE ABC_PRODUCT SET IS_ARCHIVED = 'P' WHERE ROWID = :1 36,341,570 699,773 51.9 2.7 2404.50 2457.69 4104056464 begin dbms_aqin.aq$_dequeue_in( :1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17, :18, :19, :20, :21, :22, :23, :24, :25, :26, :27, :28, :29); end; 34,123,944 222,993 153.0 2.6 264.72 269.92 2291263707 update Compound_qualifier set Compound_id=:1 where Eleme nt_qualifier_id=:2 26,978,491 568,928 47.4 2.0 1797.65 2633.05 914488053 begin dbms_aqin.aq$_dequeue_in(queue_name => :1, subscrib er => :2, msgid => :3, correlation => : 4, dequeue_mode => :5, navigation => :6, visibility => :7, wait => :8, enqueue_time => :9, state => :10, out_msgid => :11, out_correlat 18,180,414 212,927 85.4 1.4 168.66 176.62 1478003908 update Compound set object_id=:1 where Compound_id=:2 ------------------------------------------------------------- SQL ordered by Reads DB/Inst: jonah/jonah Snaps: 2361-2581 -> End Disk Reads Threshold: 1000 Total Disk Reads: 591,915,757 -> Captured SQL accounts for 97.3% of Total Disk Reads -> SQL reported below exceeded 1.0% of Total Disk Reads CPU Elapsd Old Physical Reads Executions Reads per Exec %Total Time (s) Time (s) Hash Value --------------- ------------ -------------- ------ -------- --------- ---------- 567,314,121 515 1,101,580.8 95.8 ######## 60719.17 3260211111 Module: DBMS_SCHEDULER SELECT ROWID FROM ABC_PRODUCT WHERE IS_ARCHIVED = 'N' AND INSERT ED_DATETIME <= :B1 AND ROWNUM < 10001 ------------------------------------------------------------- SQL ordered by Executions DB/Inst: jonah/jonah Snaps: 2361-2581 -> End Executions Threshold: 100 Total Executions: 50,202,317 -> Captured SQL accounts for 80.0% of Total Executions -> SQL reported below exceeded 1.0% of Total Executions CPU per Elap per Old Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value ------------ --------------- ---------------- ----------- ---------- ---------- 7,277,744 7,277,731 1.0 0.00 0.00 3648207769 select Compound_qualifier_id_seq.nextval from dual 4,581,785 4,581,778 1.0 0.00 0.00 4066951463 select Compound_id_seq.nextval from dual 3,130,536 4,827,789 1.5 0.00 0.00 2891249205 select selectors0_.Compound_id as Element5_1_, selectors0_ .Compound_qualifier_id as Element1_1_, selectors0_.Element_quali fier_id as Element1_17_0_, selectors0_.qualifier_value as quali fier2_17_0_, selectors0_.unit_id as unit3_17_0_, selectors0_.q ualifier_type_id as qualifier4_17_0_ from Compound_qualifier 2,057,604 2,944,726 1.4 0.00 0.01 3020135196 insert into Compound (Measured_value, uncertainty, suppresse d, special_value_indicator, Compound_type_id, Unit_id, object_id, Compound_id) values (:1, :2, :3, :4, :5, :6, :7, :8) 1,862,398 4,587,931 2.5 0.00 0.02 2532280527 insert into Compound_qualifier (qualifier_value, unit_id, qu alifier_type_id, Compound_id, Compound_qualifier_id) values ( :1, :2, :3, :4, :5) 1,751,540 711,925 0.4 0.00 0.00 1080723844 select deviceted0_.station_id as station1_31_, deviceted0_.DMF _station_identifier as DMF2_31_, deviceted0_.latitude as latitu de31_, deviceted0_.longitude as longitude31_, deviceted0_.stat ion_elevation as station5_31_, deviceted0_.station_elev_datum_n ame as station6_31_, deviceted0_.station_elevation_unit_code as 1,610,969 6,538,170 4.1 0.00 0.00 3644847973 select stationnam0_.station_id as station2_1_, stationnam0_.stat ion_name_id as station1_1_, stationnam0_.station_name_id as stat ion1_30_0_, stationnam0_.station_id as station2_30_0_, stationna m0_.name_type_id as name3_30_0_, stationnam0_.language_id as lan guage4_30_0_, stationnam0_.Station_Name as Station5_30_0_, stati 1,399,926 1,399,925 1.0 0.00 0.00 473290331 select unit0_.unit_id as unit1_32_0_, unit0_.unit_name_english a s unit2_32_0_, unit0_.unit_code as unit3_32_0_, unit0_.is_standa rd as is4_32_0_, unit0_.unit_class_name_english as unit5_32_0_ f rom unit_of_measure unit0_ where unit0_.unit_code=:1 1,045,478 1,045,479 1.0 0.00 0.00 946959760 select unitclass0_.unit_class_id as unit1_33_0_, unitclass0_.uni t_class_name_english as unit2_33_0_, unitclass0_.unit_class_name _french as unit3_33_0_, unitclass0_.unit_class_description_engli sh as unit4_33_0_, unitclass0_.unit_class_description_french as unit5_33_0_ from unit_class unitclass0_ where unitclass0_.unit_c 1,039,604 897,071 0.9 0.00 0.00 2859309065 select stationnam0_.station_name_id as station1_30_, stationnam0 SQL ordered by Executions DB/Inst: jonah/jonah Snaps: 2361-2581 -> End Executions Threshold: 100 Total Executions: 50,202,317 -> Captured SQL accounts for 80.0% of Total Executions -> SQL reported below exceeded 1.0% of Total Executions CPU per Elap per Old Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value ------------ --------------- ---------------- ----------- ---------- ---------- _.station_id as station2_30_, stationnam0_.name_type_id as name3 _30_, stationnam0_.language_id as language4_30_, stationnam0_.St ation_Name as Station5_30_, stationnam0_.last_update as last6_30 _, stationnam0_.active_flag as active7_30_ from Station_Ninjo_na 967,471 967,471 1.0 0.00 0.00 1639853094 select object_attribute_id_seq.nextval from dual 740,973 740,930 1.0 0.00 0.00 4073933611 begin dbms_aqin.aq$_jms_enqueue_object_message(queue_name => :1, sender_name => :2, sender_addr=> :3, sender_protocol => :4, ori ginal_msgid => :5, correlation => :6, visibility => :7, priority => :8, delay => :9, expiration=> :10, relative_msgid => :11, se quence_deviation => :12, exception_queue => :13, payload_type => 707,520 707,520 1.0 0.00 0.00 3716207873 update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle#=:5, order$=:6,cache=:7,highwater=:8,audit$=:9,flags=:10 where obj#=: 1 699,773 699,763 1.0 0.00 0.00 4104056464 begin dbms_aqin.aq$_dequeue_in( :1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17, :18, :19, :20, :21, :22, :23, :24, :25, :26, :27, :28, :29); end; 588,604 588,600 1.0 0.00 0.00 1430669035 select product_id_seq.nextval from dual 568,928 568,882 1.0 0.00 0.00 914488053 begin dbms_aqin.aq$_dequeue_in(queue_name => :1, subscrib er => :2, msgid => :3, correlation => : 4, dequeue_mode => :5, navigation => :6, visibility => :7, wait => :8, enqueue_time => :9, state => :10, out_msgid => :11, out_correlat ------------------------------------------------------------- SQL ordered by Parse Calls DB/Inst: jonah/jonah Snaps: 2361-2581 -> End Parse Calls Threshold: 1000 Total Parse Calls: 4,171,968 -> Captured SQL accounts for 72.0% of Total Parse Calls -> SQL reported below exceeded 1.0% of Total Parse Calls % Total Old Parse Calls Executions Parses Hash Value ------------ ------------ -------- ---------- 707,519 707,520 16.96 3716207873 update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle#=:5, order$=:6,cache=:7,highwater=:8,audit$=:9,flags=:10 where obj#=: 1 568,947 568,928 13.64 914488053 begin dbms_aqin.aq$_dequeue_in(queue_name => :1, subscrib er => :2, msgid => :3, correlation => : 4, dequeue_mode => :5, navigation => :6, visibility => :7, wait => :8, enqueue_time => :9, state => :10, out_msgid => :11, out_correlat 351,333 351,335 8.42 1962769620 select nvl(retry_count, 0) from "DMF_DEV1"."AQ$_DMF_PG_QUEUE_TAB _H" where msgid = :1 and subscriber# = :2 330,908 330,911 7.93 1368375776 SELECT user_data FROM DMF_DEV1.DMF_PG_QUEUE_TAB where msgid = :1 304,055 304,055 7.29 3271465142 select nvl(retry_count, 0) from "DMF_DEV1"."AQ$_NINJO_INTER_QUEU E_TAB_H" where msgid = :1 and subscriber# = :2 242,388 242,391 5.81 3227107761 select nvl(retry_count, 0) from "DMF_DEV1"."AQ$_DMF_BULLETIN_QUE UE_TAB_H" where msgid = :1 and subscriber# = :2 149,183 149,183 3.58 928435216 select nvl(retry_count, 0) from "DMF_DEV1"."AQ$_DMF_DECODE_QUEUE _TAB_H" where msgid = :1 and subscriber# = :2 ------------------------------------------------------------- Instance Activity Stats DB/Inst: jonah/jonah Snaps: 2361-2581 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ CPU used by this session 3,301,964 35.3 0.8 CPU used when call started 1,865,425 19.9 0.5 CR blocks created 11,258,188 120.3 2.7 Cached Commit SCN referenced 305,499 3.3 0.1 Commit SCN cached 528 0.0 0.0 DB time 228,526,430 2,441.3 55.6 DBWR checkpoint buffers written 3,393,832 36.3 0.8 DBWR checkpoints 88 0.0 0.0 DBWR revisited being-written buff 4 0.0 0.0 DBWR tablespace checkpoint buffer 70,032 0.8 0.0 DBWR thread checkpoint buffers wr 3,323,800 35.5 0.8 DBWR transaction table writes 1,401 0.0 0.0 DBWR undo block writes 2,086,766 22.3 0.5 IMU CR rollbacks 3,515,227 37.6 0.9 IMU Flushes 2,532,170 27.1 0.6 IMU Redo allocation size 4,523,726,516 48,325.8 1,100.8 IMU commits 621,595 6.6 0.2 IMU contention 203,438 2.2 0.1 IMU ktichg flush 16 0.0 0.0 IMU pool not allocated 1,002,139 10.7 0.2 IMU recursive-transaction flush 4,238 0.1 0.0 IMU undo allocation size 5,838,678,832 62,373.1 1,420.7 IMU- failed to get a private stra 56,659 0.6 0.0 SMON posted for undo segment shri 1,662 0.0 0.0 SQL*Net roundtrips to/from client 48,147,888 514.4 11.7 SQL*Net roundtrips to/from dblink 1,043,931 11.2 0.3 active txn count during cleanout 21,035,927 224.7 5.1 application wait time 1,557 0.0 0.0 background checkpoints completed 49 0.0 0.0 background checkpoints started 50 0.0 0.0 background timeouts 327,340 3.5 0.1 branch node splits 770 0.0 0.0 buffer is not pinned count 63,259,374 675.8 15.4 buffer is pinned count 52,825,546 564.3 12.9 bytes received via SQL*Net from c 11,445,094,676 122,264.9 2,785.0 bytes received via SQL*Net from d 144,453,430 1,543.2 35.2 bytes sent via SQL*Net to client 12,373,427,202 132,182.0 3,010.8 bytes sent via SQL*Net to dblink 1,664,229,654 17,778.5 405.0 calls to get snapshot scn: kcmgss 91,523,497 977.7 22.3 calls to kcmgas 17,410,503 186.0 4.2 calls to kcmgcs 1,368,440 14.6 0.3 change write time 172,538 1.8 0.0 cleanout - number of ktugct calls 12,852,941 137.3 3.1 cleanouts and rollbacks - consist 8,245,096 88.1 2.0 cleanouts only - consistent read 82,113 0.9 0.0 cluster key scan block gets 559,062 6.0 0.1 cluster key scans 307,269 3.3 0.1 commit batch performed 2 0.0 0.0 commit batch requested 2 0.0 0.0 commit batch/immediate performed 6,484 0.1 0.0 commit batch/immediate requested 6,484 0.1 0.0 commit cleanout failures: block l 1,518 0.0 0.0 commit cleanout failures: buffer 812 0.0 0.0 commit cleanout failures: callbac 170,228 1.8 0.0 commit cleanout failures: cannot 186,770 2.0 0.1 commit cleanouts 45,303,251 484.0 11.0 Instance Activity Stats DB/Inst: jonah/jonah Snaps: 2361-2581 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ commit cleanouts successfully com 44,943,923 480.1 10.9 commit immediate performed 6,482 0.1 0.0 commit immediate requested 6,482 0.1 0.0 commit txn count during cleanout 6,587,374 70.4 1.6 concurrency wait time 154,671 1.7 0.0 consistent changes 78,631,117 840.0 19.1 consistent gets 847,040,844 9,048.7 206.1 consistent gets - examination 185,276,653 1,979.3 45.1 consistent gets direct 783,944 8.4 0.2 consistent gets from cache 846,256,900 9,040.3 205.9 current blocks converted for CR 89 0.0 0.0 cursor authentications 10,953 0.1 0.0 data blocks consistent reads - un 77,932,872 832.5 19.0 db block changes 306,872,308 3,278.2 74.7 db block gets 490,869,558 5,243.8 119.4 db block gets direct 804,813 8.6 0.2 db block gets from cache 490,064,745 5,235.2 119.3 deferred (CURRENT) block cleanout 16,883,671 180.4 4.1 dirty buffers inspected 5,005,021 53.5 1.2 enqueue conversions 1,040,495 11.1 0.3 enqueue releases 43,877,538 468.7 10.7 enqueue requests 43,879,278 468.8 10.7 enqueue timeouts 1,693 0.0 0.0 enqueue waits 67,754 0.7 0.0 exchange deadlocks 1,801 0.0 0.0 execute count 50,202,317 536.3 12.2 failed probes on index block recl 6,222 0.1 0.0 frame signature mismatch 1 0.0 0.0 free buffer inspected 597,980,256 6,388.1 145.5 free buffer requested 605,110,771 6,464.2 147.2 heap block compress 225,667 2.4 0.1 hot buffers moved to head of LRU 7,281,659 77.8 1.8 immediate (CR) block cleanout app 8,327,237 89.0 2.0 immediate (CURRENT) block cleanou 3,722,820 39.8 0.9 index crx upgrade (found) 128 0.0 0.0 index crx upgrade (positioned) 244,167 2.6 0.1 index fast full scans (full) 42,154 0.5 0.0 index fetch by key 27,318,108 291.8 6.7 index scans kdiixs1 21,349,374 228.1 5.2 leaf node 90-10 splits 22,645 0.2 0.0 leaf node splits 297,366 3.2 0.1 lob reads 1,401,772 15.0 0.3 lob writes 2,623,848 28.0 0.6 lob writes unaligned 2,007,885 21.5 0.5 logons cumulative 2,375 0.0 0.0 messages received 5,768,261 61.6 1.4 messages sent 5,768,261 61.6 1.4 no buffer to keep pinned count 0 0.0 0.0 no work - consistent read gets 647,889,154 6,921.2 157.7 opened cursors cumulative 4,447,757 47.5 1.1 parse count (failures) 428 0.0 0.0 parse count (hard) 34,033 0.4 0.0 parse count (total) 4,171,968 44.6 1.0 parse time cpu 18,769 0.2 0.0 parse time elapsed 86,096 0.9 0.0 physical read IO requests 46,259,534 494.2 11.3 Instance Activity Stats DB/Inst: jonah/jonah Snaps: 2361-2581 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ physical read bytes 4,848,973,881,344 51,800,295.7 1,179,906.1 physical read total IO requests 46,652,849 498.4 11.4 physical read total bytes 4,989,228,167,168 53,298,594.9 1,214,034.3 physical read total multi block r 37,003,215 395.3 9.0 physical reads 591,915,757 6,323.3 144.0 physical reads cache 590,997,307 6,313.5 143.8 physical reads cache prefetch 545,275,958 5,825.0 132.7 physical reads direct 918,450 9.8 0.2 physical reads direct (lob) 728,047 7.8 0.2 physical reads direct temporary t 139,184 1.5 0.0 physical reads prefetch warmup 101,657 1.1 0.0 physical write IO requests 7,042,418 75.2 1.7 physical write bytes 110,085,652,480 1,176,015.7 26,787.3 physical write total IO requests 13,321,878 142.3 3.2 physical write total bytes 333,386,076,160 3,561,474.6 81,123.2 physical write total multi block 7,833,503 83.7 1.9 physical writes 13,438,190 143.6 3.3 physical writes direct 919,186 9.8 0.2 physical writes direct (lob) 742,509 7.9 0.2 physical writes direct temporary 172,063 1.8 0.0 physical writes from cache 12,519,004 133.7 3.1 physical writes non checkpoint 12,231,231 130.7 3.0 pinned buffers inspected 4,895 0.1 0.0 prefetch warmup blocks aged out b 142,119 1.5 0.0 prefetched blocks aged out before 3,692,115 39.4 0.9 process last non-idle time 93,606 1.0 0.0 recursive aborts on index block r 6 0.0 0.0 recursive calls 41,491,231 443.2 10.1 recursive cpu usage 642,575 6.9 0.2 redo blocks written 104,090,180 1,112.0 25.3 redo buffer allocation retries 682 0.0 0.0 redo entries 153,387,894 1,638.6 37.3 redo log space requests 1,263 0.0 0.0 redo log space wait time 42,880 0.5 0.0 redo ordering marks 2,800 0.0 0.0 redo size 50,799,510,432 542,677.6 12,361.1 redo synch time 48,247,547 515.4 11.7 redo synch writes 4,869,933 52.0 1.2 redo wastage 748,719,784 7,998.4 182.2 redo write time 7,349,707 78.5 1.8 redo writer latching time 1,391 0.0 0.0 redo writes 3,068,801 32.8 0.8 rollback changes - undo records a 16,505,530 176.3 4.0 rollbacks only - consistent read 2,822,913 30.2 0.7 rows fetched via callback 12,533,641 133.9 3.1 session connect time 0 0.0 0.0 session cursor cache hits 4,188,392 44.7 1.0 session logical reads 1,337,910,401 14,292.5 325.6 session pga memory 8,954,664,064 95,660.3 2,179.0 session pga memory max 20,613,698,016 220,210.6 5,016.0 session uga memory 7,056,815,683,968 75,386,081.3 1,717,142.6 session uga memory max 7,353,964,936 78,560.5 1,789.5 shared hash latch upgrades - no w 18,185,123 194.3 4.4 shared hash latch upgrades - wait 952 0.0 0.0 sorts (disk) 3 0.0 0.0 sorts (memory) 490,060 5.2 0.1 Instance Activity Stats DB/Inst: jonah/jonah Snaps: 2361-2581 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ sorts (rows) 87,694,937 936.8 21.3 sql area evicted 13,217 0.1 0.0 sql area purged 555 0.0 0.0 summed dirty queue length 33,762,107 360.7 8.2 switch current to new buffer 573,897 6.1 0.1 table fetch by rowid 41,931,870 448.0 10.2 table fetch continued row 95,259 1.0 0.0 table scan blocks gotten 587,971,656 6,281.1 143.1 table scan rows gotten 3,563,864,105 38,071.8 867.2 table scans (cache partitions) 3 0.0 0.0 table scans (long tables) 579 0.0 0.0 table scans (short tables) 499,508 5.3 0.1 total number of times SMON posted 1,819 0.0 0.0 transaction rollbacks 6,484 0.1 0.0 transaction tables consistent rea 37 0.0 0.0 transaction tables consistent rea 2,979 0.0 0.0 undo change vector size 16,143,132,392 172,452.8 3,928.1 user I/O wait time 39,473,874 421.7 9.6 user calls 49,484,827 528.6 12.0 user commits 4,108,895 43.9 1.0 user rollbacks 732 0.0 0.0 workarea executions - multipass 6 0.0 0.0 workarea executions - onepass 6 0.0 0.0 workarea executions - optimal 272,231 2.9 0.1 write clones created in foregroun 3,182 0.0 0.0 ------------------------------------------------------------- Instance Activity Stats DB/Inst: jonah/jonah Snaps: 2361-2581 -> Statistics with absolute values (should not be diffed) Statistic Begin Value End Value --------------------------------- --------------- --------------- logons current 122 153 opened cursors current 2,137 3,409 session cursor cache count 1,595 43,003 workarea memory allocated 0 5,071 ------------------------------------------------------------- Instance Activity Stats DB/Inst: jonah/jonah Snaps: 2361-2581 -> Statistics identified by '(derived)' come from sources other than SYSSTAT Statistic Total per Hour --------------------------------- ------------------ --------- log switches (derived) 50 1.92 ------------------------------------------------------------- OS Statistics DB/Inst: jonah/jonah Snaps: 2361-2581 -> ordered by statistic type (CPU use, Virtual Memory, Hardware Config), Name Statistic Total ------------------------- ---------------------- BUSY_TIME 9,924,887 IDLE_TIME 27,441,356 IOWAIT_TIME 15,033,088 NICE_TIME 11 SYS_TIME 4,687,645 USER_TIME 4,941,988 NUM_CPUS 4 NUM_CPU_SOCKETS 2 ------------------------------------------------------------- Tablespace IO Stats DB/Inst: jonah/jonah Snaps: 2361-2581 ->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) -------------- ------- ------ ------- ------------ -------- ---------- ------ T8LDMF001 36,483,515 390 1.7 15.9 307,356 3 3,136 10.8 X8MDMF001 5,057,340 54 37.8 1.1 4,004,510 43 42,901 1.0 X8LDMF001 3,606,121 39 29.4 1.0 1,782,017 19 739 2.7 T8MADQ001 403,763 4 4.2 1.8 420,170 4 1,248,835 0.5 T8MDMF001 376,895 4 6.8 7.0 27,539 0 1,774 12.6 UNDOTBS1 436 0 17.5 1.0 379,476 4 215,769 0.2 L8MDMF001 116,790 1 22.6 1.0 83,985 1 121 8.3 SYSAUX 73,266 1 44.5 1.4 23,590 0 63 81.4 TEMP 70,443 1 0.1 2.8 7,795 0 0 0.0 SYSTEM 68,813 1 37.2 1.4 2,895 0 1,811 4.0 X8SDMF001 7,186 0 30.8 1.1 262 0 19 4.7 T8SDMF001 5,410 0 32.2 1.2 382 0 13 3.1 L8LDMF001 345 0 12.9 1.0 250 0 0 0.0 USERS 70 0 13.4 1.0 50 0 0 0.0 ------------------------------------------------------------- File IO Stats DB/Inst: jonah/jonah Snaps: 2361-2581 ->Mx Rd Bkt: Max bucket time for single block read ->ordered by Tablespace, File Tablespace Filename ------------------------ ---------------------------------------------------- Av Mx Av Av Rd Rd Av Av Buffer BufWt Reads Reads/s (ms) Bkt Blks/Rd Writes Writes/s Waits (ms) -------------- ------- ----- --- ------- ------------ -------- ---------- ------ L8LDMF001 +DATA/jonah/datafile/l8lbox001.306.666450437 69 0 13.5 ### 1.0 50 0 0 +DATA/jonah/datafile/l8lbox001.307.666450487 69 0 10.4 ### 1.0 50 0 0 +DATA/jonah/datafile/l8lbox001.308.666450533 69 0 13.3 ### 1.0 50 0 0 +DATA/jonah/datafile/l8lbox001.309.666450569 69 0 13.3 ### 1.0 50 0 0 +DATA/jonah/datafile/l8lbox001.310.666450607 69 0 14.1 ### 1.0 50 0 0 L8MDMF001 +DATA/jonah/datafile/l8mbox001.311.666450649 5,621 0 0.2 ### 1.0 50 0 0 +DATA/jonah/datafile/l8mbox001.312.666450679 5,731 0 1.0 ### 1.0 107 0 0 +DATA/jonah/datafile/l8mbox001.313.666450689 5,646 0 0.3 ### 1.0 50 0 0 +DATA/jonah/datafile/l8mbox001.314.666450697 94,051 1 27.8 ### 1.0 83,348 1 70 11.1 +DATA/jonah/datafile/l8mbox001.315.666450705 5,741 0 2.1 ### 1.0 430 0 51 4.3 SYSAUX +DATA/jonah/datafile/sysaux.257.666386697 73,266 1 44.5 ### 1.4 23,590 0 63 81.4 SYSTEM +DATA/jonah/datafile/system.256.666386697 68,813 1 37.2 ### 1.4 2,895 0 1,811 4.0 T8LDMF001 +DATA/jonah/datafile/t8lbox001.266.666449615 3,770,427 40 1.6 ### 15.9 50 0 0 +DATA/jonah/datafile/t8lbox001.267.666449661 4,843,383 52 1.6 ### 15.9 50 0 0 +DATA/jonah/datafile/t8lbox001.268.666449685 6,968,353 74 1.5 ### 15.9 74 0 1 530.0 +DATA/jonah/datafile/t8lbox001.269.666449707 3,758,029 40 1.6 ### 15.9 1,059 0 2 0.0 +DATA/jonah/datafile/t8lbox001.270.666449729 3,297,967 35 1.6 ### 15.9 127,733 1 546 26.4 +DATA/jonah/datafile/t8lbox001.271.666449743 2,789,269 30 1.6 ### 15.9 7,322 0 0 +DATA/jonah/datafile/t8lbox001.272.666449761 2,273,793 24 1.6 ### 15.8 59,183 1 78 6.0 +DATA/jonah/datafile/t8lbox001.273.666449789 2,726,028 29 1.7 ### 15.9 7,953 0 577 4.8 +DATA/jonah/datafile/t8lbox001.274.666449807 2,786,588 30 2.8 ### 15.4 78,361 1 1,672 8.4 +DATA/jonah/datafile/t8lbox001.275.666449825 3,269,678 35 1.8 ### 15.8 25,571 0 260 6.3 T8MADQ001 +DATA/jonah/datafile/t8madq001.276.666449853 File IO Stats DB/Inst: jonah/jonah Snaps: 2361-2581 ->Mx Rd Bkt: Max bucket time for single block read ->ordered by Tablespace, File Tablespace Filename ------------------------ ---------------------------------------------------- Av Mx Av Av Rd Rd Av Av Buffer BufWt Reads Reads/s (ms) Bkt Blks/Rd Writes Writes/s Waits (ms) -------------- ------- ----- --- ------- ------------ -------- ---------- ------ 132,796 1 3.5 ### 1.8 136,606 1 1,051 23.7 T8MADQ001 +DATA/jonah/datafile/t8madq001.277.666449875 50,263 1 5.6 ### 1.8 50,547 1 584 25.1 +DATA/jonah/datafile/t8madq001.278.666449901 18,831 0 15.0 ### 1.6 13,020 0 504 21.8 +DATA/jonah/datafile/t8madq001.279.666449913 201,873 2 3.4 ### 1.8 219,997 2 1,246,696 0.4 T8MDMF001 +DATA/jonah/datafile/t8mbox001.280.666449935 54,391 1 5.8 ### 6.9 4,317 0 228 20.4 +DATA/jonah/datafile/t8mbox001.281.666449947 53,056 1 7.2 ### 7.0 3,512 0 271 20.0 +DATA/jonah/datafile/t8mbox001.282.666449955 53,534 1 7.0 ### 7.0 3,760 0 294 8.3 +DATA/jonah/datafile/t8mbox001.283.666449973 53,560 1 6.5 ### 7.0 3,780 0 241 11.4 +DATA/jonah/datafile/t8mbox001.284.666450013 53,772 1 7.1 ### 6.9 4,412 0 268 10.9 +DATA/jonah/datafile/t8mbox001.285.666450027 54,154 1 7.6 ### 6.9 3,944 0 274 9.8 +DATA/jonah/datafile/t8mbox001.286.666450059 54,428 1 6.5 ### 6.9 3,814 0 198 7.6 T8SDMF001 +DATA/jonah/datafile/t8sbox001.287.666450073 1,517 0 31.1 ### 1.2 50 0 0 +DATA/jonah/datafile/t8sbox001.288.666450083 1,213 0 30.6 ### 1.2 50 0 0 +DATA/jonah/datafile/t8sbox001.289.666450101 1,432 0 39.4 ### 1.1 50 0 2 0.0 +DATA/jonah/datafile/t8sbox001.290.666450121 1,248 0 26.7 ### 1.3 232 0 11 3.6 TEMP +DATA/jonah/tempfile/temp.264.666386809 70,443 1 0.1 ### 2.8 7,795 0 0 UNDOTBS1 +DATA/jonah/datafile/undotbs1.258.666386697 436 0 17.5 ### 1.0 379,476 4 215,769 0.2 USERS +DATA/jonah/datafile/users.259.666386697 70 0 13.4 ### 1.0 50 0 0 X8LDMF001 +DATA/jonah/datafile/x8lbox001.291.666450155 488,257 5 30.0 ### 1.1 260,917 3 13 0.8 +DATA/jonah/datafile/x8lbox001.292.666450189 510,372 5 28.7 ### 1.0 270,383 3 309 0.4 +DATA/jonah/datafile/x8lbox001.293.666450197 519,720 6 28.2 ### 1.0 248,429 3 384 2.7 +DATA/jonah/datafile/x8lbox001.294.666450215 361,968 4 29.4 ### 1.0 151,792 2 3 3.3 +DATA/jonah/datafile/x8lbox001.295.666450229 File IO Stats DB/Inst: jonah/jonah Snaps: 2361-2581 ->Mx Rd Bkt: Max bucket time for single block read ->ordered by Tablespace, File Tablespace Filename ------------------------ ---------------------------------------------------- Av Mx Av Av Rd Rd Av Av Buffer BufWt Reads Reads/s (ms) Bkt Blks/Rd Writes Writes/s Waits (ms) -------------- ------- ----- --- ------- ------------ -------- ---------- ------ 489,128 5 29.1 ### 1.0 189,052 2 1 0.0 X8LDMF001 +DATA/jonah/datafile/x8lbox001.296.666450251 740,692 8 29.6 ### 1.0 381,630 4 10 3.0 +DATA/jonah/datafile/x8lbox001.297.666450271 495,984 5 30.4 ### 1.0 279,814 3 19 42.6 X8MDMF001 +DATA/jonah/datafile/x8mbox001.298.666450295 1,218,615 13 36.1 ### 1.1 922,308 10 16,399 0.5 +DATA/jonah/datafile/x8mbox001.299.666450311 1,162,600 12 38.9 ### 1.1 1,009,150 11 12,678 1.5 +DATA/jonah/datafile/x8mbox001.300.666450323 1,251,949 13 38.7 ### 1.1 1,009,924 11 13,696 1.0 +DATA/jonah/datafile/x8mbox001.301.666450335 1,424,176 15 37.5 ### 1.1 1,063,128 11 128 13.1 X8SDMF001 +DATA/jonah/datafile/x8sbox001.302.666450355 1,383 0 30.8 ### 1.1 50 0 2 10.0 +DATA/jonah/datafile/x8sbox001.303.666450367 1,546 0 36.5 ### 1.1 50 0 2 0.0 +DATA/jonah/datafile/x8sbox001.304.666450385 2,642 0 25.4 ### 1.1 50 0 1 0.0 +DATA/jonah/datafile/x8sbox001.305.666450405 1,615 0 34.1 ### 1.2 112 0 14 5.0 ------------------------------------------------------------- File Read Histogram Stats DB/Inst: jonah/jonah Snaps: 2361-2581 ->Number of single block reads in each time range ->ordered by Tablespace, File Tablespace Filename ------------------------ ---------------------------------------------------- 0 - 2 ms 2 - 4 ms 4 - 8 ms 8 - 16 ms 16 - 32 ms 32+ ms ------------ ------------ ------------ ------------ ------------ ------------ T8LDMF001 +DATA/jonah/datafile/t8lbox001.273.666449789 5,664 419 1,223 624 231 2,807 T8SDMF001 +DATA/jonah/datafile/t8sbox001.288.666450083 229 86 240 125 53 344 X8LDMF001 +DATA/jonah/datafile/x8lbox001.295.666450229 168,262 34,257 90,897 49,380 18,797 125,937 +DATA/jonah/datafile/x8lbox001.296.666450251 162,327 65,965 186,472 98,174 35,718 188,894 X8SDMF001 +DATA/jonah/datafile/x8sbox001.302.666450355 105 108 400 200 101 372 L8LDMF001 +DATA/jonah/datafile/l8lbox001.306.666450437 3 1 3 1 0 9 +DATA/jonah/datafile/l8lbox001.307.666450487 3 0 4 1 2 7 L8MDMF001 +DATA/jonah/datafile/l8mbox001.312.666450679 3 3 19 8 3 50 UNDOTBS1 +DATA/jonah/datafile/undotbs1.258.666386697 213 11 16 11 4 71 T8MDMF001 +DATA/jonah/datafile/t8mbox001.283.666449973 23,609 604 1,520 862 377 2,286 T8SDMF001 +DATA/jonah/datafile/t8sbox001.289.666450101 269 91 281 98 38 544 L8MDMF001 +DATA/jonah/datafile/l8mbox001.315.666450705 26 11 49 41 23 106 T8LDMF001 +DATA/jonah/datafile/t8lbox001.267.666449661 7,225 65 52 49 39 203 T8MADQ001 +DATA/jonah/datafile/t8madq001.279.666449913 7,273 1,012 2,634 1,683 770 6,132 T8MDMF001 +DATA/jonah/datafile/t8mbox001.286.666450059 24,410 559 1,456 752 331 2,335 X8LDMF001 +DATA/jonah/datafile/x8lbox001.293.666450197 122,513 45,126 126,554 70,429 26,011 126,368 X8MDMF001 +DATA/jonah/datafile/x8mbox001.299.666450311 197,880 69,942 258,046 164,168 64,202 396,703 +DATA/jonah/datafile/x8mbox001.300.666450323 200,116 77,207 284,509 183,967 71,613 422,849 +DATA/jonah/datafile/x8mbox001.301.666450335 263,226 90,210 316,315 197,144 77,244 467,528 File Read Histogram Stats DB/Inst: jonah/jonah Snaps: 2361-2581 ->Number of single block reads in each time range ->ordered by Tablespace, File Tablespace Filename ------------------------ ---------------------------------------------------- 0 - 2 ms 2 - 4 ms 4 - 8 ms 8 - 16 ms 16 - 32 ms 32+ ms ------------ ------------ ------------ ------------ ------------ ------------ L8MDMF001 +DATA/jonah/datafile/l8mbox001.311.666450649 3 2 3 3 4 13 T8SDMF001 +DATA/jonah/datafile/t8sbox001.287.666450073 282 99 314 181 74 422 +DATA/jonah/datafile/t8sbox001.290.666450121 261 93 228 114 53 308 X8MDMF001 +DATA/jonah/datafile/x8mbox001.298.666450295 232,123 76,233 274,859 173,293 67,163 383,238 X8SDMF001 +DATA/jonah/datafile/x8sbox001.305.666450405 99 142 420 256 94 472 L8MDMF001 +DATA/jonah/datafile/l8mbox001.313.666450689 3 2 5 3 0 15 SYSTEM +DATA/jonah/datafile/system.256.666386697 19,122 3,268 8,498 4,711 2,518 22,618 T8LDMF001 +DATA/jonah/datafile/t8lbox001.268.666449685 8,163 52 67 49 40 212 +DATA/jonah/datafile/t8lbox001.270.666449729 7,603 82 76 65 66 1,127 +DATA/jonah/datafile/t8lbox001.275.666449825 8,105 2,801 9,553 5,007 1,747 4,960 T8MADQ001 +DATA/jonah/datafile/t8madq001.276.666449853 6,498 709 1,649 1,026 503 4,173 T8MDMF001 +DATA/jonah/datafile/t8mbox001.282.666449955 23,497 570 1,603 864 386 2,501 X8LDMF001 +DATA/jonah/datafile/x8lbox001.292.666450189 128,311 43,803 119,715 65,606 24,011 126,179 L8LDMF001 +DATA/jonah/datafile/l8lbox001.309.666450569 4 1 1 1 0 10 SYSAUX +DATA/jonah/datafile/sysaux.257.666386697 20,255 3,313 8,194 5,202 2,761 28,687 T8LDMF001 +DATA/jonah/datafile/t8lbox001.266.666449615 4,845 38 48 33 24 153 +DATA/jonah/datafile/t8lbox001.269.666449707 4,978 33 40 25 33 122 +DATA/jonah/datafile/t8lbox001.272.666449761 13,396 358 924 409 150 365 +DATA/jonah/datafile/t8lbox001.274.666449807 22,424 5,815 19,671 11,910 5,053 33,703 File Read Histogram Stats DB/Inst: jonah/jonah Snaps: 2361-2581 ->Number of single block reads in each time range ->ordered by Tablespace, File Tablespace Filename ------------------------ ---------------------------------------------------- 0 - 2 ms 2 - 4 ms 4 - 8 ms 8 - 16 ms 16 - 32 ms 32+ ms ------------ ------------ ------------ ------------ ------------ ------------ T8MADQ001 +DATA/jonah/datafile/t8madq001.277.666449875 2,690 333 902 545 288 2,480 +DATA/jonah/datafile/t8madq001.278.666449901 4,338 384 831 495 275 2,628 T8MDMF001 +DATA/jonah/datafile/t8mbox001.281.666449947 23,413 446 1,364 673 307 2,624 L8LDMF001 +DATA/jonah/datafile/l8lbox001.308.666450533 3 2 3 0 0 9 +DATA/jonah/datafile/l8lbox001.310.666450607 3 2 2 0 0 10 USERS +DATA/jonah/datafile/users.259.666386697 2 2 3 1 1 8 T8LDMF001 +DATA/jonah/datafile/t8lbox001.271.666449743 4,737 45 44 26 35 140 T8MDMF001 +DATA/jonah/datafile/t8mbox001.280.666449935 24,524 599 1,666 785 342 1,962 X8LDMF001 +DATA/jonah/datafile/x8lbox001.291.666450155 113,962 42,008 115,529 62,610 23,837 127,025 +DATA/jonah/datafile/x8lbox001.297.666450271 95,594 44,809 129,098 69,659 25,491 129,164 L8MDMF001 +DATA/jonah/datafile/l8mbox001.314.666450697 16,765 1,678 2,187 1,591 1,078 24,286 T8MDMF001 +DATA/jonah/datafile/t8mbox001.284.666450013 23,116 671 1,737 939 400 2,615 +DATA/jonah/datafile/t8mbox001.285.666450027 23,524 589 1,404 799 366 2,920 X8LDMF001 +DATA/jonah/datafile/x8lbox001.294.666450215 108,371 27,995 74,106 40,869 15,491 93,817 X8SDMF001 +DATA/jonah/datafile/x8sbox001.303.666450367 145 114 384 205 101 504 +DATA/jonah/datafile/x8sbox001.304.666450385 472 389 874 221 81 517 TEMP +DATA/jonah/tempfile/temp.264.666386809 26 2 14 10 4 25 ------------------------------------------------------------- Buffer Pool Statistics DB/Inst: jonah/jonah Snaps: 2361-2581 -> Standard block size Pools D: default, K: keep, R: recycle -> Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32k -> Buffers: the number of buffers. Units of K, M, G are divided by 1000 Free Writ Buffer Pool Buffer Physical Physical Buffer Comp Busy P Buffers Hit% Gets Reads Writes Waits Wait Waits --- ------- ---- -------------- ------------ ----------- ------- ---- ---------- D 262K 56 1,336,026,652 590,842,219 12,516,172 0 0 1,513,303 ------------------------------------------------------------- Instance Recovery Stats DB/Inst: jonah/jonah Snaps: 2361-2581 -> B: Begin snapshot, E: End snapshot Targt Estd Log File Log Ckpt Log Ckpt MTTR MTTR Recovery Actual Target Size Timeout Interval (s) (s) Estd IOs Redo Blks Redo Blks Redo Blks Redo Blks Redo Blks - ----- ----- ---------- --------- --------- ---------- --------- ------------ B 0 0 28830 124882 3774870 3774870 E 0 0 97353 1538198 1534975 3774870 1534975 ------------------------------------------------------------- Buffer Pool Advisory DB/Inst: jonah/jonah End Snap: 2581 -> Only rows with estimated physical reads >0 are displayed -> ordered by Pool, Block Size, Buffers For Estimate Est Phys Estimated Est Size for Size Buffers Read Phys Reads Est Phys % dbtime P Est (M) Factr (thousands) Factr (thousands) Read Time for Rds --- -------- ----- ------------ ------ -------------- ------------ -------- D 208 .1 26 1.0 604,479 815,947 90.7 D 416 .2 51 1.0 596,992 563,189 62.6 D 624 .3 77 1.0 594,668 484,739 53.9 D 832 .4 103 1.0 594,131 466,624 51.9 D 1,040 .5 129 1.0 593,402 442,014 49.1 D 1,248 .6 154 1.0 592,786 421,200 46.8 D 1,456 .7 180 1.0 592,201 401,456 44.6 D 1,664 .8 206 1.0 591,840 389,279 43.3 D 1,872 .9 232 1.0 591,540 379,158 42.1 D 2,080 1.0 257 1.0 591,315 371,566 41.3 D 2,144 1.0 265 1.0 591,251 369,388 41.1 D 2,288 1.1 283 1.0 591,089 363,926 40.5 D 2,496 1.2 309 1.0 590,827 355,094 39.5 D 2,704 1.3 334 1.0 590,562 346,150 38.5 D 2,912 1.4 360 1.0 590,366 339,539 37.7 D 3,120 1.5 386 1.0 590,249 335,564 37.3 D 3,328 1.6 412 1.0 590,168 332,844 37.0 D 3,536 1.6 437 1.0 590,088 330,126 36.7 D 3,744 1.7 463 1.0 590,019 327,795 36.4 D 3,952 1.8 489 1.0 589,957 325,725 36.2 D 4,160 1.9 515 1.0 589,843 321,860 35.8 ------------------------------------------------------------- Buffer wait Statistics DB/Inst: jonah/jonah Snaps: 2361-2581 -> ordered by wait time desc, waits desc Class Waits Total Wait Time (s) Avg Time (ms) ---------------------- ----------- ------------------- ------------- data block 1,289,954 713 1 undo block 180,891 26 0 undo header 34,770 10 0 bitmap index block 5,799 2 0 file header block 28 1 51 1st level bmb 619 1 2 segment header 388 1 3 2nd level bmb 28 1 28 bitmap block 884 0 0 ------------------------------------------------------------- PGA Aggr Target Stats DB/Inst: jonah/jonah Snaps: 2361-2581 -> B: Begin snap E: End snap (rows identified with B or E contain data which is absolute i.e. not diffed over the interval) -> PGA cache hit % - percentage of W/A (WorkArea) data processed only in-memory -> Auto PGA Target - actual workarea memory target -> W/A PGA Used - amount of memory used for all Workareas (manual + auto) -> %PGA W/A Mem - percentage of PGA memory allocated to workareas -> %Auto W/A Mem - percentage of workarea memory controlled by Auto Mem Mgmt -> %Man W/A Mem - percentage of workarea memory under manual control PGA Cache Hit % W/A MB Processed Extra W/A MB Read/Written --------------- ---------------- ------------------------- 75.0 9,510 3,170 %PGA %Auto %Man PGA Aggr Auto PGA PGA Mem W/A PGA W/A W/A W/A Global Mem Target(M) Target(M) Alloc(M) Used(M) Mem Mem Mem Bound(K) - --------- --------- ---------- ---------- ------ ------ ------ ---------- B 788 420 680.4 0.0 .0 .0 .0 102,400 E 788 171 876.7 5.0 .6 100.0 .0 102,400 ------------------------------------------------------------- PGA Aggr Target Histogram DB/Inst: jonah/jonah Snaps: 2361-2581 -> Optimal Executions are purely in-memory operations Low High Optimal Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs ------- ------- -------------- ------------- ------------ ------------ 2K 4K 264,855 264,855 0 0 64K 128K 469 469 0 0 128K 256K 78 78 0 0 256K 512K 128 128 0 0 512K 1024K 5,031 5,031 0 0 1M 2M 2,440 2,440 0 0 2M 4M 9 9 0 0 4M 8M 10 10 0 0 8M 16M 8 8 0 0 32M 64M 1 1 0 0 64M 128M 10 4 0 6 128M 256M 6 0 6 0 ------------------------------------------------------------- PGA Memory Advisory DB/Inst: jonah/jonah End Snap: 2581 -> When using Auto Memory Mgmt, minimally choose a pga_aggregate_target value where Estd PGA Overalloc Count is 0 Estd Extra Estd PGA Estd PGA PGA Target Size W/A MB W/A MB Read/ Cache Overalloc Est (MB) Factr Processed Written to Disk Hit % Count ---------- ------- ---------------- ---------------- -------- ---------- 99 0.1 9,529.4 5,609.7 63.0 240 197 0.3 9,529.4 5,609.7 63.0 240 394 0.5 9,529.4 5,606.8 63.0 234 591 0.8 9,529.4 1,835.0 84.0 14 788 1.0 9,529.4 1,584.8 86.0 0 946 1.2 9,529.4 619.4 94.0 0 1,103 1.4 9,529.4 619.4 94.0 0 1,261 1.6 9,529.4 619.4 94.0 0 1,418 1.8 9,529.4 619.4 94.0 0 1,576 2.0 9,529.4 619.4 94.0 0 2,364 3.0 9,529.4 619.4 94.0 0 3,152 4.0 9,529.4 619.4 94.0 0 4,728 6.0 9,529.4 619.4 94.0 0 6,304 8.0 9,529.4 619.4 94.0 0 ------------------------------------------------------------- Process Memory Summary Stats DB/Inst: jonah/jonah Snaps: 2361-2581 -> B: Begin snap E: End snap -> All rows below contain absolute values (i.e. not diffed over the interval) -> Max Alloc is Maximum PGA Allocation size at snapshot time Hist Max Alloc is the Historical Max Allocation for still-connected processes -> Num Procs or Allocs: For Begin/End snapshot lines, it is the number of processes. For Category lines, it is the number of allocations -> ordered by Begin/End snapshot, Alloc (MB) desc Hist Num Avg Std Dev Max Max Procs Alloc Used Freeabl Alloc Alloc Alloc Alloc or Category (MB) (MB) (MB) (MB) (MB) (MB) (MB) Allocs - -------- --------- --------- -------- -------- ------- ------- ------ ------ B -------- 681.6 322.3 34.8 5.5 4.3 31 33 125 Other 634.5 5.1 4.2 29 31 125 Freeable 34.8 .0 .5 .4 2 76 SQL 9.8 6.3 .1 .1 1 2 109 PL/SQL 2.6 2.2 .0 .0 0 0 123 E -------- 877.7 598.4 102.7 5.6 5.4 48 48 156 Other 754.8 4.8 5.2 48 48 156 Freeable 102.1 .0 .7 1.2 14 138 SQL 16.6 9.9 .1 .1 1 7 138 PL/SQL 4.6 3.5 .0 .0 0 0 154 ------------------------------------------------------------- Top Process Memory (by component) DB/Inst: jonah/jonah Snaps: 2361-2581 -> ordered by Begin/End snapshot, Alloc (MB) desc Alloc Used Freeabl Max Hist Max PId Category (MB) (MB) (MB) Alloc (MB) Alloc (MB) - ------ ------------- ------- ------- -------- ---------- ---------- B 6 LGWR -------- 31.1 12.1 1.7 31.1 32.8 Other 29.4 29.4 31.1 Freeable 1.7 .0 1.7 PL/SQL .0 .0 .0 .0 19 ARC0 -------- 25.9 12.0 .0 25.9 25.9 Other 25.9 25.9 25.9 PL/SQL .0 .0 .0 .0 20 ARC1 -------- 25.9 12.0 .0 25.9 25.9 Other 25.9 25.9 25.9 PL/SQL .0 .0 .0 .0 5 DBW0 -------- 10.5 2.1 2.3 10.5 12.8 Other 8.2 8.2 10.5 Freeable 2.3 .0 2.3 PL/SQL .0 .0 .0 .0 22 J000 -------- 9.4 5.3 .4 9.4 9.4 Other 8.8 8.8 8.8 Freeable .3 .0 .3 SQL .2 .1 .2 1.6 PL/SQL .1 .0 .1 .1 56 ------------ 7.5 3.4 .8 7.5 7.8 Other 6.6 6.6 6.6 Freeable .8 .0 .8 SQL .1 .0 .1 2.2 PL/SQL .0 .0 .0 .0 35 ------------ 7.3 3.5 .6 7.3 7.3 Other 6.2 6.2 6.2 Freeable .6 .0 .6 SQL .5 .5 .5 2.2 PL/SQL .0 .0 .0 .0 66 ------------ 7.3 3.4 .1 7.3 7.3 Other 7.1 7.1 7.1 SQL .1 .0 .1 .1 Freeable .1 .0 .1 PL/SQL .0 .0 .0 .0 42 ------------ 7.2 3.2 1.1 7.2 7.4 Other 6.1 6.1 6.1 Freeable 1.1 .0 1.1 SQL .1 .0 .1 2.2 PL/SQL .0 .0 .0 .0 48 ------------ 7.2 3.7 .1 7.2 7.2 Other 6.9 6.9 6.9 SQL .2 .1 .2 .3 Freeable .1 .0 .1 PL/SQL .0 .0 .0 .0 104 ------------ 7.2 3.3 .0 7.2 7.2 Other 7.1 7.1 7.1 SQL .1 .0 .1 .1 PL/SQL .0 .0 .0 .0 31 ------------ 7.1 3.2 1.1 7.1 7.4 Other 5.9 5.9 5.9 Freeable 1.1 .0 1.1 SQL .1 .0 .1 2.2 PL/SQL .0 .0 .0 .0 Top Process Memory (by component) DB/Inst: jonah/jonah Snaps: 2361-2581 -> ordered by Begin/End snapshot, Alloc (MB) desc Alloc Used Freeabl Max Hist Max PId Category (MB) (MB) (MB) Alloc (MB) Alloc (MB) - ------ ------------- ------- ------- -------- ---------- ---------- B 93 ------------ 7.1 3.9 .3 7.1 7.3 Other 6.7 6.7 6.9 Freeable .3 .0 .3 SQL .1 .0 .1 .1 PL/SQL .0 .0 .0 .0 68 ------------ 7.0 3.6 .0 7.0 7.0 Other 6.9 6.9 6.9 SQL .1 .0 .1 .2 PL/SQL .0 .0 .0 .0 123 ------------ 7.0 3.6 .1 7.0 7.2 Other 6.7 6.7 6.9 Freeable .1 .0 .1 SQL .1 .0 .1 .1 PL/SQL .0 .0 .0 .0 28 ------------ 6.9 3.1 .7 6.9 7.2 Other 6.1 6.1 6.1 Freeable .7 .0 .7 SQL .1 .0 .1 2.2 PL/SQL .0 .0 .0 .0 40 ------------ 6.9 3.1 .7 6.9 7.2 Other 6.1 6.1 6.1 Freeable .7 .0 .7 SQL .1 .0 .1 2.2 PL/SQL .0 .0 .0 .0 26 ------------ 6.9 3.5 .1 6.9 6.9 Other 6.6 6.6 6.6 SQL .2 .1 .2 .2 Freeable .1 .0 .1 PL/SQL .0 .0 .0 .0 121 ------------ 6.9 3.3 .3 6.9 7.2 Other 6.5 6.5 6.7 Freeable .3 .0 .3 SQL .1 .0 .1 .1 PL/SQL .0 .0 .0 .0 124 ------------ 6.9 3.4 .0 6.9 6.9 Other 6.8 6.8 6.8 SQL .1 .0 .1 .2 PL/SQL .0 .0 .0 .0 E 5 DBW0 -------- 48.2 17.2 .7 48.2 48.2 Other 47.5 47.5 47.5 Freeable .7 .0 .7 PL/SQL .0 .0 .0 .0 6 LGWR -------- 29.5 12.2 .1 29.5 32.8 Other 29.4 29.4 32.7 Freeable .1 .0 .1 PL/SQL .0 .0 .0 .0 19 ARC0 -------- 25.9 12.1 .0 25.9 25.9 Other 25.9 25.9 25.9 PL/SQL .0 .0 .0 .0 20 ARC1 -------- 25.9 12.1 .0 25.9 25.9 Other 25.9 25.9 25.9 PL/SQL .0 .0 .0 .0 65 J001 -------- 23.9 1.9 13.9 23.9 23.9 Top Process Memory (by component) DB/Inst: jonah/jonah Snaps: 2361-2581 -> ordered by Begin/End snapshot, Alloc (MB) desc Alloc Used Freeabl Max Hist Max PId Category (MB) (MB) (MB) Alloc (MB) Alloc (MB) - ------ ------------- ------- ------- -------- ---------- ---------- E 65 Freeable 13.8 .0 13.8 Other 9.8 9.8 9.8 SQL .2 .1 .2 6.9 PL/SQL .1 .1 .1 .2 22 J000 -------- 22.8 21.5 .7 22.8 27.0 Other 21.8 21.8 22.9 Freeable .7 .0 .7 PL/SQL .2 .1 .2 .2 SQL .1 .0 .1 3.1 154 J004 -------- 10.1 4.2 2.9 10.1 10.1 Other 6.7 6.7 6.7 Freeable 2.9 .0 2.9 SQL .2 .1 .2 1.9 PL/SQL .2 .2 .2 .2 46 ------------ 9.9 8.4 .8 9.9 14.1 Other 9.0 9.0 11.9 Freeable .8 .0 .8 SQL .1 .0 .1 1.5 PL/SQL .0 .0 .0 .0 103 ------------ 9.8 8.5 .8 9.8 13.5 Other 9.0 9.0 11.2 Freeable .8 .0 .8 SQL .1 .0 .1 1.5 PL/SQL .0 .0 .0 .0 63 ------------ 9.4 8.1 .9 9.4 13.5 Other 8.3 8.3 11.5 Freeable .9 .0 .9 SQL .1 .0 .1 1.0 PL/SQL .0 .0 .0 .0 110 ------------ 9.4 8.1 .9 9.4 16.1 Other 8.4 8.4 14.4 Freeable .9 .0 .9 SQL .1 .0 .1 .8 PL/SQL .0 .0 .0 .0 130 ------------ 9.4 8.2 .9 9.4 13.6 Other 8.4 8.4 11.8 Freeable .9 .0 .9 SQL .1 .0 .1 .9 PL/SQL .0 .0 .0 .0 99 ------------ 9.3 8.5 .6 9.3 13.8 Other 8.6 8.6 11.7 Freeable .6 .0 .6 SQL .1 .0 .1 1.5 PL/SQL .0 .0 .0 .0 155 J005 -------- 9.2 5.4 1.1 9.2 9.2 Other 8.0 8.0 8.0 Freeable 1.0 .0 1.0 SQL .2 .1 .2 1.4 PL/SQL .0 .0 .0 .0 7 CKPT -------- 8.9 2.1 1.6 8.9 10.5 Other 7.3 7.3 8.9 Freeable 1.6 .0 1.6 PL/SQL .0 .0 .0 .0 Top Process Memory (by component) DB/Inst: jonah/jonah Snaps: 2361-2581 -> ordered by Begin/End snapshot, Alloc (MB) desc Alloc Used Freeabl Max Hist Max PId Category (MB) (MB) (MB) Alloc (MB) Alloc (MB) - ------ ------------- ------- ------- -------- ---------- ---------- E 11 MMON -------- 7.8 3.6 .9 7.8 8.0 Other 6.7 6.7 6.7 Freeable .9 .0 .9 SQL .1 .1 .1 1.2 PL/SQL .0 .0 .0 .1 8 SMON -------- 7.3 2.5 1.9 7.3 7.3 Other 5.3 5.3 5.3 Freeable 1.9 .0 1.9 SQL .1 .0 .1 1.3 PL/SQL .0 .0 .0 .0 134 ------------ 7.1 3.5 .8 7.1 14.5 Other 6.1 6.1 8.5 Freeable .8 .0 .8 SQL .1 .0 .1 5.1 PL/SQL .1 .0 .1 .1 157 m000 -------- 7.1 2.7 1.1 7.1 7.1 Other 5.9 5.9 5.9 Freeable 1.1 .0 1.1 SQL .2 .1 .2 1.3 PL/SQL .0 .0 .0 .0 143 ------------ 7.1 2.9 1.0 7.1 10.2 Other 6.0 6.0 6.7 Freeable 1.0 .0 1.0 SQL .1 .0 .1 2.5 PL/SQL .0 .0 .0 .0 ------------------------------------------------------------- Enqueue activity DB/Inst: jonah/jonah Snaps: 2361-2581 -> 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) ------------ ------------ ----------- ----------- ------------ -------------- TX-Transaction (index contention) 30,062 30,062 0 17,758 591 33.26 TC-Tablespace Checkpoint 3 3 0 1 135 135,290.00 TX-Transaction 5,580,715 5,579,888 0 57 63 1,097.89 CF-Controlfile Transaction 45,905 45,904 1 58 54 929.66 SQ-Sequence Cache 757,277 757,277 0 49,702 34 .68 HW-Segment High Water Mark 43,745 42,117 1,628 145 9 62.34 JS-Job Scheduler (queue lock) 338,609 338,609 0 5 1 296.00 TX-Transaction (row lock contention) 4 4 0 4 0 40.00 RO-Multiple Object Reuse (fast object reuse) 333 333 0 16 0 1.25 TX-Transaction (allocate ITL entry) 179 179 0 5 0 .00 FB-Format Block 29,251 29,251 0 3 0 .00 ------------------------------------------------------------- Undo Segment Summary DB/Inst: jonah/jonah Snaps: 2361-2581 -> Min/Max TR (mins) - Min and Max Tuned Retention (minutes) -> STO - Snapshot Too Old count, OOS - Out Of Space count -> Undo segment block stats: uS - unexpired Stolen, uR - unexpired Released, uU - unexpired reUsed eS - expired Stolen, eR - expired Released, eU - expired reUsed Undo Num Undo Number of Max Qry Max Tx Min/Max STO/ uS/uR/uU/ TS# Blocks (K) Transactions Len (s) Concy TR (mins) OOS eS/eR/eU ---- ---------- --------------- -------- ---------- --------- ----- ----------- 1 2,150.5 5,839,616 274 17 15/18.3 0/0 0/0/0/0/0/0 ------------------------------------------------------------- Undo Segment Stats DB/Inst: jonah/jonah Snaps: 2361-2581 -> Most recent 35 Undostat rows, ordered by End Time desc Num Undo Number of Max Qry Max Tx Tun Ret STO/ uS/uR/uU/ End Time Blocks Transactions Len (s) Concy (mins) OOS eS/eR/eU ------------ ----------- ------------ ------- ------- ------- ----- ----------- 31-Oct 21:56 9,749 24,893 110 15 15 0/0 0/0/0/0/0/0 31-Oct 21:46 11,367 27,812 59 12 15 0/0 0/0/0/0/0/0 31-Oct 21:36 13,733 33,113 164 13 15 0/0 0/0/0/0/0/0 31-Oct 21:26 12,021 33,713 102 12 15 0/0 0/0/0/0/0/0 31-Oct 21:16 14,412 36,775 171 12 15 0/0 0/0/0/0/0/0 31-Oct 21:06 14,818 46,678 83 15 15 0/0 0/0/0/0/0/0 31-Oct 20:56 6,739 17,607 89 13 15 0/0 0/0/0/0/0/0 31-Oct 20:46 8,035 19,498 163 12 15 0/0 0/0/0/0/0/0 31-Oct 20:36 8,527 20,816 84 12 15 0/0 0/0/0/0/0/0 31-Oct 20:26 8,889 22,987 143 12 15 0/0 0/0/0/0/0/0 31-Oct 20:16 15,346 39,005 93 14 15 0/0 0/0/0/0/0/0 31-Oct 20:06 26,253 68,708 186 15 15 0/0 0/0/0/0/0/0 31-Oct 19:56 8,335 20,558 59 12 15 0/0 0/0/0/0/0/0 31-Oct 19:46 8,558 22,173 87 12 15 0/0 0/0/0/0/0/0 31-Oct 19:36 8,757 20,466 163 13 15 0/0 0/0/0/0/0/0 31-Oct 19:26 21,196 48,031 35 12 15 0/0 0/0/0/0/0/0 31-Oct 19:16 31,332 80,523 59 15 15 0/0 0/0/0/0/0/0 31-Oct 19:06 24,998 81,042 120 15 15 0/0 0/0/0/0/0/0 31-Oct 18:56 26,144 62,312 169 11 15 0/0 0/0/0/0/0/0 31-Oct 18:46 34,021 56,108 84 12 15 0/0 0/0/0/0/0/0 31-Oct 18:36 33,010 55,018 158 11 16 0/0 0/0/0/0/0/0 31-Oct 18:26 27,217 76,781 86 13 15 0/0 0/0/0/0/0/0 31-Oct 18:16 27,383 79,921 161 15 16 0/0 0/0/0/0/0/0 31-Oct 18:06 28,460 82,982 0 15 15 0/0 0/0/0/0/0/0 31-Oct 17:56 34,867 82,370 92 12 15 0/0 0/0/0/0/0/0 31-Oct 17:46 34,180 83,277 135 13 15 0/0 0/0/0/0/0/0 31-Oct 17:36 27,689 65,537 52 13 15 0/0 0/0/0/0/0/0 31-Oct 17:26 33,020 83,405 101 13 15 0/0 0/0/0/0/0/0 31-Oct 17:16 33,259 84,130 175 13 16 0/0 0/0/0/0/0/0 31-Oct 17:06 28,251 76,986 94 15 15 0/0 0/0/0/0/0/0 31-Oct 16:56 27,266 64,494 45 12 15 0/0 0/0/0/0/0/0 31-Oct 16:46 34,238 72,090 88 13 15 0/0 0/0/0/0/0/0 31-Oct 16:36 33,995 80,493 174 13 16 0/0 0/0/0/0/0/0 31-Oct 16:26 34,950 78,302 123 13 15 0/0 0/0/0/0/0/0 ------------------------------------------------------------- Latch Activity DB/Inst: jonah/jonah Snaps: 2361-2581 ->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for willing-to-wait latch get requests ->"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests ->"Pct Misses" for both should be very close to 0.0 Pct Avg Wait Pct Get Get Slps Time NoWait NoWait Latch Requests Miss /Miss (s) Requests Miss ------------------------ -------------- ------ ------ ------ ------------ ------ ASM allocation 5,640 0.0 0 0 ASM db client latch 73,144 0.0 0 0 ASM map headers 5,731 0.1 0.6 0 0 ASM map load waiting lis 2,051 0.0 0 0 ASM map operation freeli 6,364 0.0 0 0 ASM map operation hash t 135,154,615 0.0 0.0 0 0 ASM network background l 44,668 0.0 0 0 AWR Alerted Metric Eleme 700,260 0.0 0 0 Consistent RBA 3,070,646 0.0 0.0 0 0 FAL request queue 1,965 0.0 0 0 FAL subheap alocation 1,965 0.0 0 0 FIB s.o chain latch 1,138 0.0 0 0 FOB s.o list latch 9,251 0.0 0.0 0 0 In memory undo latch 65,971,406 0.1 0.3 9 3,247,188 0.2 JS Sh mem access 4 0.0 0 0 JS mem alloc latch 274 0.0 0 2 0.0 JS queue access latch 274 0.0 0 0 JS queue state obj latch 677,254 0.0 0 0 JS slv state obj latch 4,339 0.0 0 0 KFK SGA context latch 32,818 0.0 0 0 KFMD SGA 862 0.0 0 0 KMG MMAN ready and start 31,496 0.0 0 0 KMG resize request state 78 0.0 0 0 KTF sga latch 255 0.4 0.0 0 30,823 0.0 KWQMN job cache list lat 1,511,539 0.3 0.0 0 0 KWQP Prop Status 78 0.0 0 0 MQL Tracking Latch 0 0 1,870 0.0 Memory Management Latch 793 0.1 0.0 0 31,470 0.0 Mutex Stats 2 0.0 0 0 NLS data objects 1 0.0 0 0 OS process 3,024 0.0 0 0 OS process allocation 33,262 0.0 0 0 OS process: request allo 1,453 0.0 0 0 PL/SQL warning settings 757,549 0.0 0 0 Reserved Space Latch 972 0.0 0 0 SGA IO buffer pool latch 68,529 0.0 0 71,994 0.0 SQL memory manager latch 52 0.0 0 30,365 0.0 SQL memory manager worka 2,160,734 0.0 0 0 Shared B-Tree 3,552 0.0 0 0 X$KSFQP 28 0.0 0 0 active checkpoint queue 2,729,618 0.0 0.0 0 2 0.0 active service list 199,237 0.0 0.0 0 32,828 0.0 alert log latch 0 0 2 0.0 archive control 4,006 0.0 0 0 archive process latch 32,331 0.0 0 0 begin backup scn array 472,645 0.2 0.0 0 0 buffer pool 757 0.0 0 0 cache buffer handles 644,820 0.0 0.0 0 0 cache buffers chains 4,085,526,685 0.1 0.2 136 ############ 0.0 cache buffers lru chain 26,876,053 0.4 0.0 0 ############ 0.0 Latch Activity DB/Inst: jonah/jonah Snaps: 2361-2581 ->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for willing-to-wait latch get requests ->"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests ->"Pct Misses" for both should be very close to 0.0 Pct Avg Wait Pct Get Get Slps Time NoWait NoWait Latch Requests Miss /Miss (s) Requests Miss ------------------------ -------------- ------ ------ ------ ------------ ------ cache table scan latch 0 0 36,520,698 0.0 channel handle pool latc 1,673 0.0 0 0 channel operations paren 4,561,797 0.1 0.5 2 0 checkpoint queue latch 29,308,872 0.0 0.0 0 12,466,516 0.0 client/application info 9,881 0.0 0 0 commit callback allocati 5,768,127 0.0 0.0 0 0 compile environment latc 9,197,565 0.0 0 0 database property servic 532 0.0 0 0 device information 108 0.0 0 0 dictionary lookup 129 0.0 0 0 dml lock allocation 72,172,591 2.3 0.0 1 0 dummy allocation 4,719 0.0 0 0 enqueue hash chains 88,869,195 0.9 0.0 2 226,956 0.0 enqueues 12,106,858 0.1 0.0 0 0 event group latch 742 0.0 0 0 event range base latch 1 0.0 0 0 file cache latch 19,709 0.0 0 0 global KZLD latch for me 561 0.0 0 0 global ctx hash table la 2 0.0 0 0 global tx hash mapping 1,530 0.0 0 0 hash table column usage 18,562 0.0 0.0 0 1,033,784 0.0 hash table modification 3,217 0.0 0 2 0.0 internal temp table obje 11 0.0 0 0 job workq parent latch 0 0 3,296 0.0 job_queue_processes free 3 0.0 0 2 0.0 job_queue_processes para 3,205 0.0 0 0 kks stats 157,104 0.0 0.0 0 0 kmcptab latch 6 0.0 0 0 kmcpvec latch 0 0 6 0.0 krbmrosl 694 0.3 1.0 1 0 ksuosstats global area 6,352 0.0 0 0 ksv instance 15 0.0 0 0 ktm global data 5,185 0.0 0 0 kwqbsn:qsga 3,526 0.0 0 0 lgwr LWN SCN 3,072,803 0.0 0.0 0 0 library cache 233,701,619 0.4 0.0 1 149,180 0.1 library cache load lock 160,054 0.0 0.0 0 1,105 0.0 library cache lock 80,217,124 0.1 0.0 0 0 library cache lock alloc 120,547 0.0 0 0 library cache pin 148,000,394 0.2 0.0 0 491 0.0 library cache pin alloca 1,829,858 0.0 0.0 0 0 list of block allocation 2,129,648 0.0 0.0 0 0 loader state object free 2,409,312 0.1 0.0 0 0 logminer context allocat 26 0.0 0 0 longop free list parent 30,192 0.0 0 751 0.0 message pool operations 1,627,943 0.0 0.0 0 0 messages 16,603,184 0.0 0.0 0 0 mostly latch-free SCN 3,101,356 0.7 0.0 0 0 msg queue 1,128 0.4 0.0 0 1,124 0.0 multiblock read objects 73,259,490 0.0 0.0 0 2 0.0 Latch Activity DB/Inst: jonah/jonah Snaps: 2361-2581 ->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for willing-to-wait latch get requests ->"NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests ->"Pct Misses" for both should be very close to 0.0 Pct Avg Wait Pct Get Get Slps Time NoWait NoWait Latch Requests Miss /Miss (s) Requests Miss ------------------------ -------------- ------ ------ ------ ------------ ------ ncodef allocation latch 1,563 0.0 0 0 object queue header heap 5,466,234 0.0 0.0 0 20,588 0.0 object queue header oper 1,231,349,132 0.0 0.0 0 13,832 0.0 object stats modificatio 1,313 0.1 0.0 0 14 0.0 parallel query alloc buf 12,164 0.0 0 0 parameter list 528 0.0 0 0 parameter table allocati 2,469 0.0 0.0 0 0 post/wait queue 10,021,032 0.1 0.0 0 4,989,633 0.0 process allocation 1,453 0.0 0 742 0.0 process group creation 1,443 0.0 0 0 qmn task queue latch 13,368 0.0 0.0 0 0 redo allocation 165,974,171 0.4 0.0 5 0 redo copy 9 66.7 1.2 0 153,394,422 0.2 redo writing 11,991,948 0.0 0.0 0 0 reservation so alloc lat 212 0.0 0 0 row cache objects 294,191,549 0.3 0.0 1 106,837 0.3 rules engine aggregate s 449 0.0 0 0 rules engine evaluation 0 0 2 0.0 rules engine rule set st 6,098 0.0 0 0 segmented array pool 1,065 0.0 0 0 sequence cache 43,898,226 0.1 0.0 0 0 session allocation 35,236,011 0.3 0.0 0 0 session idle bit 108,171,865 0.0 0.0 0 0 session state list latch 5,454 0.0 0 0 session switching 1,563 0.0 0 0 session timer 32,828 0.0 0 0 shared pool 32,361,917 0.4 0.0 3 0 shared pool sim alloc 561 0.0 0 0 shared pool simulator 3,334,720 0.0 0.0 0 0 simulator hash latch 227,519,078 0.0 0.0 0 0 simulator lru latch 116,335,948 0.1 0.0 1 37,993,363 0.0 slave class 3,458 0.4 0.0 0 2 0.0 slave class create 2,805 1.5 1.0 7 0 sort extent pool 5,488 0.0 0 0 state object free list 52 0.0 0 0 statistics aggregation 3,640 0.0 0 0 temp lob duration state 54 0.0 0 0 temporary table state ob 4 0.0 0 0 threshold alerts latch 12,019 0.0 0 0 trace latch 38 0.0 0 0 transaction allocation 36,534 0.0 0 0 transaction branch alloc 1,769 0.0 0 0 undo global data 146,831,133 0.1 0.0 0 0 user lock 5,591 0.1 0.0 0 0 ------------------------------------------------------------- Latch Sleep breakdown DB/Inst: jonah/jonah Snaps: 2361-2581 -> ordered by misses desc Get Spin Latch Name Requests Misses Sleeps Gets -------------------------- --------------- ------------ ----------- ----------- cache buffers chains 4,085,526,685 3,633,327 622,584 3,024,196 dml lock allocation 72,172,591 1,662,334 2,521 1,659,903 row cache objects 294,191,549 983,306 729 982,594 library cache 233,701,619 968,513 1,930 966,643 enqueue hash chains 88,869,195 768,421 4,630 763,975 redo allocation 165,974,171 598,283 7,644 590,867 library cache pin 148,000,394 282,388 480 281,925 shared pool 32,361,917 131,589 2,763 129,156 undo global data 146,831,133 117,540 442 117,116 cache buffers lru chain 26,876,053 114,392 546 113,855 library cache lock 80,217,124 109,400 124 109,277 simulator lru latch 116,335,948 107,747 851 106,945 session allocation 35,236,011 90,820 444 90,392 In memory undo latch 65,971,406 89,158 26,634 63,662 object queue header operat 1,231,349,132 73,341 1,005 72,354 sequence cache 43,898,226 33,916 33 33,883 session idle bit 108,171,865 26,703 178 26,528 mostly latch-free SCN 3,101,356 22,451 44 22,408 enqueues 12,106,858 9,284 31 9,253 messages 16,603,184 5,421 30 5,391 post/wait queue 10,021,032 5,089 13 5,076 KWQMN job cache list latch 1,511,539 4,918 180 4,747 channel operations parent 4,561,797 3,919 1,787 2,137 checkpoint queue latch 29,308,872 2,774 12 2,762 ASM map operation hash tab 135,154,615 2,020 1 2,019 loader state object freeli 2,409,312 1,579 4 1,575 redo writing 11,991,948 1,223 24 1,199 lgwr LWN SCN 3,072,803 1,067 7 1,060 begin backup scn array 472,645 1,044 5 1,039 simulator hash latch 227,519,078 785 3 782 Consistent RBA 3,070,646 705 1 704 commit callback allocation 5,768,127 599 8 592 library cache pin allocati 1,829,858 415 1 414 message pool operations pa 1,627,943 283 7 276 list of block allocation 2,129,648 78 3 75 slave class create 2,805 42 41 1 redo copy 9 6 7 0 ASM map headers 5,731 5 3 2 krbmrosl 694 2 2 0 ------------------------------------------------------------- Latch Miss Sources DB/Inst: jonah/jonah Snaps: 2361-2581 -> only latches with sleeps are shown -> ordered by name, sleeps desc NoWait Waiter Latch Name Where Misses Sleeps Sleeps ------------------------ -------------------------- ------- ---------- -------- ASM map headers kffmFree 0 3 0 ASM map operation hash t kffmTranslate 0 1 1 In memory undo latch ktiFlush: child 0 18,996 9,915 In memory undo latch kturbk 0 6,592 5,636 In memory undo latch ktichg: child 0 754 10,994 In memory undo latch kticmt: child 0 167 75 In memory undo latch ktiTxnPoolFree 0 118 0 In memory undo latch ktiBRacquire 0 6 0 In memory undo latch ktbgfc 0 1 26 KWQMN job cache list lat kwqmnuji: update job item 0 4,195 4,192 KWQMN job cache list lat kwqmndej: dequeue 0 3 6 active service list kswssyis: find service ent 0 2 0 begin backup scn array get begin backup scn for f 0 5 5 cache buffers chains kcbchg: kslbegin: bufs not 0 441,615 388,473 cache buffers chains kcbgtcr: kslbegin excl 0 433,145 155,127 cache buffers chains kcbgcur: kslbegin 0 149,135 68,206 cache buffers chains kcbzwb 0 23,843 13,508 cache buffers chains kcbgtcr: fast path 0 17,512 1,578 cache buffers chains kcbget: pin buffer 0 10,410 4,149 cache buffers chains kcbchg: kslbegin: call CR 0 4,222 192,985 cache buffers chains kcbrls: kslbegin 0 3,773 129,716 cache buffers chains kcbzgb: scan from tail. no 0 1,705 0 cache buffers chains kcb_is_private 0 936 106,137 cache buffers chains kcbnlc 0 883 3,589 cache buffers chains kcbcge 0 732 24,092 cache buffers chains kcbget: exchange rls 0 234 189 cache buffers chains kcbget: exchange 0 177 431 cache buffers chains kcbzib: multi-block read: 0 41 0 cache buffers chains kcbzhngcbk1: get hash chai 0 31 0 cache buffers chains kcbw_next_free 0 24 0 cache buffers chains kcbnew: new latch again 0 13 0 cache buffers chains kcb_post_apply: kcbhq62 0 7 82 cache buffers chains kcb_pre_apply: kcbhq61 0 5 656 cache buffers chains kcbso1: set no access 0 4 6 cache buffers chains kcbesc: escalate 0 4 1 cache buffers chains kcbgtcr: kslbegin shared 0 3 2 cache buffers chains kcbbic2 0 2 13 cache buffers chains kcbbxsv 0 1 16 cache buffers chains kcbkzs 0 1 0 cache buffers chains kcbbic1 0 1 21 cache buffers lru chain kcbzgws 0 454 41 cache buffers lru chain kcbgtcr:CR Scan:KCBRSKIP 0 34 0 cache buffers lru chain kcbw_activate_granule 0 33 0 cache buffers lru chain kcbbxsv: move to being wri 0 11 254 cache buffers lru chain kcbzswcu 0 5 6 cache buffers lru chain kcbzgb: posted for free bu 0 5 45 cache buffers lru chain kcbzar: KSLNBEGIN 0 2 0 cache buffers lru chain kcbbic2 0 2 169 channel operations paren ksrapublish() 0 2,888 2,461 channel operations paren ksrwait() 0 1 350 channel operations paren ksrsconsume() 0 1 63 checkpoint queue latch kcbklbc: Link buffer into 0 9 0 checkpoint queue latch kcbbwthc: thread checkpoin 0 1 4 Latch Miss Sources DB/Inst: jonah/jonah Snaps: 2361-2581 -> only latches with sleeps are shown -> ordered by name, sleeps desc NoWait Waiter Latch Name Where Misses Sleeps Sleeps ------------------------ -------------------------- ------- ---------- -------- checkpoint queue latch kcbswcu: Switch buffers 0 1 1 checkpoint queue latch kcbk_ncv_check 0 1 0 commit callback allocati ktccaco 0 5 5 commit callback allocati ktcccod 0 3 3 dml lock allocation ktaidm 0 1,755 1,217 dml lock allocation ktaiam 0 766 1,304 enqueue hash chains ksqrcl 0 3,386 2,455 enqueue hash chains ksqgtl3 0 1,233 2,158 enqueue hash chains ksqcnl 0 11 15 enqueues ksqies 0 18 26 enqueues ksqgel: create enqueue 0 8 4 enqueues ksqdel 0 5 1 krbmrosl krbmmrsr 0 2 0 lgwr LWN SCN kcs023 0 21 0 library cache kglpndl: child: after proc 0 950 38 library cache kglobpn: child: 0 241 963 library cache kglpnp: child 0 171 179 library cache kglpndl: child: before pro 0 133 554 library cache kgldte: child 0 0 89 135 library cache kglScanDependency 0 52 0 library cache kglic 0 38 0 library cache kglLockCursor 0 18 2 library cache kglhdgn: child: 0 17 12 library cache kglrtl 0 11 0 library cache kgldti: 2child 0 4 2 library cache kglpin 0 3 2 library cache kglhdgc: child: 0 2 0 library cache kglukp: child 0 2 0 library cache kglivl: child 0 2 0 library cache kglati 0 1 0 library cache kglobld 0 1 2 library cache kglivr: child 0 1 0 library cache lock kgllkdl: child: cleanup 0 93 76 library cache lock kgllkal: child: multiinsta 0 31 49 library cache lock kgllkdl: child: no lock ha 0 12 6 library cache pin kglpndl 0 342 194 library cache pin kglpnp: child 0 73 42 library cache pin kglpnal: child: alloc spac 0 65 244 library cache pin alloca kglpnget 0 1 0 list of block allocation ktlabl 0 2 3 list of block allocation ktlbbl 0 1 0 loader state object free kcblfs 0 3 4 loader state object free kcblgs 0 1 0 message pool operations ksrmalloc() 0 8 8 message pool operations ksrmfree() 0 2 2 messages ksaamb: after wakeup 0 19 11 messages ksarcv 0 9 17 messages ksarcv: after wait 0 2 2 mostly latch-free SCN kcslcu3 0 25 51 mostly latch-free SCN kcs024 0 5 0 mostly latch-free SCN kcsnew_scn_rba 0 1 0 object queue header oper kcbw_unlink_q 0 492 532 object queue header oper kcbo_switch_cq 0 430 564 Latch Miss Sources DB/Inst: jonah/jonah Snaps: 2361-2581 -> 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 400 335 object queue header oper kcbw_link_q 0 293 77 object queue header oper kcbo_swap_buf_bg 0 39 144 object queue header oper kcbo_switch_q_bg 0 28 34 object queue header oper kcbo_swap_buf 0 7 5 object queue header oper kcbw_unlink_q_bg 0 1 0 object queue header oper kcbo_ivbo 0 1 0 post/wait queue ksliwat:remove 0 11 10 post/wait queue ksliwat:add:nowait 0 2 0 redo allocation kcrfw_redo_gen 0 7,241 6,363 redo allocation kcrfw_redo_write: before w 0 228 515 redo allocation kcrfw_redo_write: more spa 0 174 765 redo allocation kcrf_pvt_strand_bind 0 1 1 redo copy kcrfw_redo_gen: nowait 0 5 0 redo copy kcrfw_redo_gen: wait 0 2 7 redo writing kcrfw_post: rba scn pair 0 10 0 redo writing kcrfw_cal_target_rba 0 5 2 redo writing kcrfwcr 0 5 19 redo writing kcrfw_post: after write 0 4 3 row cache objects kqreqd: reget 0 519 17 row cache objects kqrpre: find obj 0 125 619 row cache objects kqreqd 0 53 61 row cache objects kqrso 0 10 0 sequence cache kdnssd 0 32 1 sequence cache kdnnxt: cached seq 0 1 1 session allocation ksuxds: not user session 0 156 211 session allocation ksuprc 0 110 38 session allocation ksucri 0 70 166 session allocation ksudlc 0 59 28 session allocation ksufap: active sessions 0 30 0 session allocation ksuxds: KSUSFCLC not set 0 9 1 session allocation kspallmod 0 5 0 session allocation ksuxds: KSUSFCLC set 0 3 0 session allocation ksudlp 0 2 0 session idle bit ksupuc: set busy 0 219 72 session idle bit ksupuc: clear busy 0 119 238 session idle bit ksuxds 0 82 110 shared pool kghalo 0 1,284 1,401 shared pool kghfre 0 770 765 shared pool kghfrunp: alloc: session d 0 283 2 shared pool kghfrunp: clatch: nowait 0 172 0 shared pool kghupr1 0 158 465 shared pool kgh_next_free 0 106 13 shared pool kghfrunp: alloc: wait 0 74 13 shared pool kghalp 0 60 89 shared pool kghfrunp: clatch: wait 0 31 52 shared pool kgh: quiesce extents 0 8 0 shared pool kghasp 0 7 10 shared pool kgh: add extent to quiesce 0 3 0 shared pool kgh: sim resz update 0 3 2 shared pool kghfen: not perm alloc cla 0 2 1 shared pool kghfrh 0 2 0 shared pool kgh: add new extent 0 2 1 Latch Miss Sources DB/Inst: jonah/jonah Snaps: 2361-2581 -> only latches with sleeps are shown -> ordered by name, sleeps desc NoWait Waiter Latch Name Where Misses Sleeps Sleeps ------------------------ -------------------------- ------- ---------- -------- shared pool kghquiesce: clatch: nowait 0 1 0 shared pool kgh: add extent to reserve 0 1 1 simulator hash latch kcbs_get_or_extract_simbuf 0 1 0 simulator hash latch kcbsacc: lookup dba 0 1 3 simulator hash latch kcbs_lookup_setid: lookup 0 1 0 simulator lru latch kcbs_simulate: simulate se 0 625 785 simulator lru latch kcbs_simbufseq 0 208 66 simulator lru latch kcbssck 0 14 0 simulator lru latch kcbs_lookup_setid 0 2 0 simulator lru latch kcbs_free_granule_sim_buff 0 1 0 simulator lru latch kcbs_resize_pool : interpo 0 1 0 slave class create ksvsubmit 0 27 1 slave class create ksvcreate 0 10 0 slave class create ksvgetwork1 0 4 27 undo global data ktudba: KSLBEGIN 0 208 353 undo global data kturimugur: child 0 196 21 undo global data ktudnx:child 0 31 38 undo global data ktubnd:child 0 7 3 ------------------------------------------------------------- Mutex Sleep DB/Inst: jonah/jonah Snaps: 2361-2581 -> ordered by Wait Time desc Wait Mutex Type Location Sleeps Time (s) ------------------ -------------------------------- -------------- ------------ Cursor Pin kkslce [KKSCHLPIN2] 10,493 113.3 Cursor Pin kksfbc [KKSCHLFSP2] 2,631 28.2 Cursor Pin kksfbc [KKSCHLPIN1] 259 2.8 Cursor Pin kksxsccmp [KKSCHLPIN5] 7 0.0 Cursor Pin kksLockDelete [KKSCHLPIN6] 31 0.0 Cursor Parent kksfbc [KKSPRTLOC1] 489 0.0 Cursor Parent kkspsc0 [KKSPRTLOC27] 14 0.0 Cursor Parent kkspsc0 [KKSPRTLOC26] 7 0.0 Cursor Stat kksFindCursorStat [KKSSTALOC3] 1 0.0 Cursor Parent opiaba [KKSPRTLOC17] 1 0.0 ------------------------------------------------------------- Dictionary Cache Stats DB/Inst: jonah/jonah Snaps: 2361-2581 ->"Pct Misses" should be very low (< 2% in most cases) ->"Final Usage" is the number of cache entries being used in End Snapshot Get Pct Scan Pct Mod Final Cache Requests Miss Reqs Miss Reqs Usage ------------------------- ------------ ------ ------- ----- -------- ---------- dc_awr_control 1,787 1.0 0 54 1 dc_constraints 312 36.2 0 312 0 dc_database_links 445 2.2 0 0 0 dc_files 648 84.3 0 0 54 dc_global_oids 25,093,282 0.0 0 0 24 dc_histogram_data 205,525 32.5 0 2,961 560 dc_histogram_defs 301,041 41.6 0 2,396 684 dc_object_grants 5,442 23.7 0 0 60 dc_object_ids 26,115,905 0.1 0 92 355 dc_objects 128,506 21.8 0 474 278 dc_profiles 2,791 2.4 0 0 3 dc_rollback_segments 22,490 0.0 0 0 22 dc_segments 137,403 23.8 0 916 183 dc_sequences 707,447 0.0 0 707,447 8 dc_table_scns 25 100.0 0 0 0 dc_tablespace_quotas 2,335 9.9 0 0 2 dc_tablespaces 16,782,228 0.0 0 0 14 dc_usernames 54,741 0.3 0 0 8 dc_users 27,600,064 0.0 0 3 32 outstanding_alerts 5,034 24.6 0 183 1 ------------------------------------------------------------- Library Cache Activity DB/Inst: jonah/jonah Snaps: 2361-2581 ->"Pct Misses" should be very low Get Pct Pin Pct Invali- Namespace Requests Miss Requests Miss Reloads dations --------------- ------------ ------ -------------- ------ ---------- -------- BODY 20,139 2.0 2,967,874 0.0 735 0 CLUSTER 1,922 3.1 4,060 3.0 63 0 INDEX 1,318 58.4 4,803 23.0 148 0 SQL AREA 62,991 42.3 52,281,182 0.3 18,889 4,903 TABLE/PROCEDURE 94,952 15.4 34,118,165 0.2 30,677 0 TRIGGER 1,962 6.7 364,014 0.1 149 0 ------------------------------------------------------------- Rule Sets DB/Inst: jonah/jonah Snaps: 2361-2581 -> * indicates Rule Set activity (re)started between Begin/End snaps -> Top 25 ordered by Evaluations desc No-SQL SQL Rule * Eval/sec Reloads/sec Eval % Eval % ----------------------------------- - ------------ ----------- ------ ------ SYS.ALERT_QUE_R 6 0 0 0 ------------------------------------------------------------- Streams Pool Advisory DB/Inst: jonah/jonah End Snap: 2581 Streams Pool Streams Pool Est Spill Est Spill Est Unspill Est Unspill Size (M) Size Factor Count Time (s) Count Time (s) ------------ ------------ --------- --------- ----------- ----------- .0 1.0 0 0 0 0 .0 2.0 0 0 0 0 .0 3.0 0 0 0 0 .0 4.0 0 0 0 0 .0 5.0 0 0 0 0 .0 6.0 0 0 0 0 .0 7.0 0 0 0 0 .0 8.0 0 0 0 0 .0 9.0 0 0 0 0 .0 10.0 0 0 0 0 .0 11.0 0 0 0 0 .0 12.0 0 0 0 0 .0 13.0 0 0 0 0 .0 14.0 0 0 0 0 .0 15.0 0 0 0 0 .0 16.0 0 0 0 0 .0 17.0 0 0 0 0 .0 18.0 0 0 0 0 .0 19.0 0 0 0 0 .0 20.0 0 0 0 0 ------------------------------------------------------------- Shared Pool Advisory DB/Inst: jonah/jonah End Snap: 2581 -> SP: Shared Pool Est LC: Estimated Library Cache Factr: Factor -> Note there is often a 1:Many correlation between a single logical object in the Library Cache, and the physical number of memory objects associated with it. Therefore comparing the number of Lib Cache objects (e.g. in v$librarycache), with the number of Lib Cache Memory Objects is invalid Est LC Est LC Est LC Est LC Shared SP Est LC Time Time Load Load Est LC Pool Size Size Est LC Saved Saved Time Time Mem Size (M) Factr (M) Mem Obj (s) Factr (s) Factr Obj Hits ---------- ----- -------- ------------ ------- ------ ------- ------ ----------- 112 .6 37 2,818 ####### 1.0 ####### 48.3 40,483,985 144 .8 67 4,846 ####### 1.0 29,152 12.1 40,505,969 176 1.0 97 6,316 ####### 1.0 2,402 1.0 40,516,446 208 1.2 128 7,500 ####### 1.0 1 .0 40,520,501 240 1.4 159 9,161 ####### 1.0 1 .0 40,522,579 272 1.5 190 10,613 ####### 1.0 1 .0 40,523,522 304 1.7 221 11,452 ####### 1.0 1 .0 40,524,055 336 1.9 252 13,139 ####### 1.0 1 .0 40,524,295 368 2.1 283 14,921 ####### 1.0 1 .0 40,524,554 ------------------------------------------------------------- Cache Size Changes DB/Inst: jonah/jonah Snaps: 2361-2581 -> Not all cache size changes may be captured. Only cache changes which are evident at snapshot time are shown Prior New Difference Snap Id Cache Size (MB) Size (MB) (MB) --------- ------------ --------- --------- ---------- 2371 Buffer Cache 2,128 2,112 -16 Shared Pool 176 192 16 2381 Shared Pool 192 176 -16 Buffer Cache 2,112 2,128 16 ------------------------------------------------------------- SGA Target Advisory DB/Inst: jonah/jonah End Snap: 2581 SGA Target SGA Size Est DB Est DB Est Physical Size (M) Factor Time (s) Time Factor Reads ---------- -------- -------- ----------- -------------- 592 .3 ######## 1.2 596,793,529 1,184 .5 997,063 1.1 593,956,440 1,776 .8 931,750 1.0 592,005,941 2,368 1.0 899,633 1.0 591,060,245 2,960 1.3 876,357 1.0 590,350,973 3,552 1.5 863,055 1.0 589,996,337 4,144 1.8 855,950 1.0 589,759,912 4,736 2.0 852,084 .9 589,641,700 ------------------------------------------------------------- SGA Memory Summary DB/Inst: jonah/jonah Snaps: 2361-2581 End Size (Bytes) SGA regions Begin Size (Bytes) (if different) ------------------------------ -------------------- -------------------- Database Buffers 2,231,369,728 Fixed Size 2,074,792 Redo Buffers 14,700,544 Variable Size 234,882,904 -------------------- -------------------- sum 2,483,027,968 ------------------------------------------------------------- SGA breakdown difference DB/Inst: jonah/jonah Snaps: 2361-2581 -> Top 35 rows by size, ordered by Pool, Name (note rows with null values for Pool column, or Names showing free memory are always shown) -> Null value for Begin MB or End MB indicates the size of that Pool/Name was insignificant, or zero in that snapshot Pool Name Begin MB End MB % Diff ------ ------------------------------ -------------- -------------- -------- java p free memory 16.0 16.0 0.00 large ASM map operations hashta .4 .4 0.00 large free memory 15.6 15.6 0.00 shared ASH buffers 8.0 8.0 0.00 shared CCursor 2.4 3.1 26.10 shared Checkpoint queue 2.0 -100.00 shared FileIdentificatonBlock 2.1 -100.00 shared FileOpenBlock 13.6 13.6 0.00 shared Heap0: KGL 2.7 ######## shared KCB Table Scan Buffer 4.0 4.0 0.00 shared KGH: NO ACCESS 46.4 ######## shared KGLS heap 4.4 -100.00 shared KSFD SGA I/O b 4.0 4.0 0.00 shared PCursor 2.0 2.6 28.76 shared PL/SQL DIANA 1.9 -100.00 shared PL/SQL MPCODE 2.5 2.8 16.00 shared db_block_hash_buckets 22.5 22.5 0.00 shared event statistics per sess 3.0 3.0 0.00 shared free memory 36.2 31.2 -13.76 shared kglsim hash table bkts 4.0 4.0 0.00 shared library cache 7.3 10.6 45.70 shared private strands 2.3 2.3 0.00 shared row cache 7.2 7.2 0.00 shared sql area 9.8 8.9 -9.27 shared trace buffer 2.0 2.6 30.16 stream free memory 16.0 16.0 0.00 buffer_cache 2,128.0 2,128.0 0.00 fixed_sga 2.0 2.0 0.00 log_buffer 14.0 14.0 0.00 ------------------------------------------------------------- SQL Memory Statistics DB/Inst: jonah/jonah Snaps: 2361-2581 Begin End % Diff -------------- -------------- -------------- Avg Cursor Size (KB): 21.96 14.95 -46.87 Cursor to Parent ratio: 1.03 1.37 24.77 Total Cursors: 695 980 29.08 Total Parents: 675 716 5.73 ------------------------------------------------------------- init.ora Parameters DB/Inst: jonah/jonah Snaps: 2361-2581 End value Parameter Name Begin value (if different) ----------------------------- --------------------------------- -------------- audit_file_dest /opt/oracle/admin/jonah/adump background_dump_dest /opt/oracle/admin/jonah/bdump compatible 10.2.0.1.0 control_files +DATA/jonah/controlfile/curren core_dump_dest /opt/oracle/admin/jonah/cdump db_block_size 8192 db_create_file_dest +DATA db_create_online_log_dest_1 +DATA db_create_online_log_dest_2 +FRA db_domain op.xz.gc.ca db_file_multiblock_read_count 16 db_files 1000 db_name jonah db_recovery_file_dest +FRA db_recovery_file_dest_size 1073741824000 dispatchers (PROTOCOL=TCP) (SERVICE=jonahX job_queue_processes 10 local_listener LISTENER_jonah log_archive_dest_1 LOCATION=USE_DB_RECOVERY_FILE_DES log_archive_format arcjonah%t_%s_%r.dbf nls_date_format hh24:mi:ss nls_length_semantics BYTE open_cursors 300 pga_aggregate_target 826277888 processes 305 remote_login_passwordfile EXCLUSIVE session_max_open_files 20 sessions 340 sga_max_size 2483027968 sga_target 2483027968 spfile +DATA/jonah/spfilejonah.ora streams_pool_size 0 transactions 187 undo_management AUTO undo_retention 900 undo_tablespace UNDOTBS1 user_dump_dest /opt/oracle/admin/jonah/udump ------------------------------------------------------------- End of Report ( sp_2361_2581.lst )