PDXSTATSPACK report for Database DB Id Instance Inst Num Startup Time Release RAC ~~~~~~~~ ----------- ------------ -------- --------------- ----------- --- 2813377685 PDXPROD 1 26-Sep-13 01:22 10.2.0.2.0 NO Host Name: db1.avinor.osl3. Num CPUs: 2 Phys Memory (MB): 16,376 ~~~~ Snapshot Snap Id Snap Time Sessions Curs/Sess Comment ~~~~~~~~ ---------- ------------------ -------- --------- ------------------- Begin Snap: 2625 02-Oct-13 08:15:02 139 8.2 End Snap: 2635 02-Oct-13 10:45:00 164 8.5 Elapsed: 149.97 (mins) Cache Sizes Begin End ~~~~~~~~~~~ ---------- ---------- Buffer Cache: 448M Std Block Size: 8K Shared Pool Size: 704M Log Buffer: 13,924K Load Profile Per Second Per Transaction ~~~~~~~~~~~~ --------------- --------------- Redo size: 22,936.28 103,552.74 Logical reads: 145,324.74 656,112.40 Block changes: 127.49 575.58 Physical reads: 3.68 16.61 Physical writes: 3.39 15.29 User calls: 414.85 1,872.97 Parses: 6.94 31.31 Hard parses: 0.11 0.48 Sorts: 68.61 309.75 Logons: 0.08 0.36 Executes: 192.89 870.86 Transactions: 0.22 % Blocks changed per Read: 0.09 Recursive Call %: 19.63 Rollback per transaction %: 0.00 Rows per Sort: 30.47 Instance Efficiency Percentages ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Buffer Nowait %: 100.00 Redo NoWait %: 100.00 Buffer Hit %: 100.00 In-memory Sort %: 100.00 Library Hit %: 99.82 Soft Parse %: 98.47 Execute to Parse %: 96.40 Latch Hit %: 99.90 Parse CPU to Parse Elapsd %: 4.39 % Non-Parse CPU: 99.95 Shared Pool Statistics Begin End ------ ------ Memory Usage %: 76.55 73.10 % SQL with executions>1: 49.35 55.85 % Memory for SQL w/exec>1: 52.57 58.65 Top 5 Timed Events Avg %Total ~~~~~~~~~~~~~~~~~~ wait Call Event Waits Time (s) (ms) Time ----------------------------------------- ------------ ----------- ------ ------ latch: cache buffers chains 53,712 31,764 591 49.8 CPU time 16,758 26.2 Backup: sbtwrite2 8,909 11,126 1249 17.4 latch free 3,198 1,851 579 2.9 latch: cache buffer handles 1,967 931 473 1.5 ------------------------------------------------------------- Host CPU (CPUs: 2) ~~~~~~~~ Load Average Begin End User System Idle WIO WCPU ------- ------- ------- ------- ------- ------- -------- 70.08 93.06 94.66 5.34 0.00 0.00 ####### Instance CPU ~~~~~~~~~~~~ % of total CPU for Instance: 93.65 % of busy CPU for Instance: 93.65 %DB time waiting for CPU - Resource Mgr: Memory Statistics Begin End ~~~~~~~~~~~~~~~~~ ------------ ------------ Host Mem (MB): 16,375.5 16,375.5 SGA use (MB): 1,200.0 1,200.0 PGA use (MB): 269.8 392.1 % Host Mem used for SGA+PGA: 9.0 9.7 ------------------------------------------------------------- Time Model System Stats DB/Inst: PDXPROD/PDXPROD Snaps: 2625-2635 -> Ordered by % of DB time desc, Statistic name Statistic Time (s) % of DB time ----------------------------------- -------------------- ------------ sql execute elapsed time 578,732.7 99.2 DB CPU 16,749.8 2.9 parse time elapsed 300.2 .1 PL/SQL execution elapsed time 252.0 .0 hard parse elapsed time 189.5 .0 connection management call elapsed 115.6 .0 RMAN cpu time (backup/restore) 96.7 .0 repeated bind elapsed time 55.9 .0 PL/SQL compilation elapsed time 5.7 .0 hard parse (sharing criteria) elaps 2.9 .0 sequence load elapsed time 1.9 .0 failed parse elapsed time 0.0 .0 hard parse (bind mismatch) elapsed 0.0 .0 DB time 583,482.3 background elapsed time 12,739.5 background cpu time 131.8 ------------------------------------------------------------- Wait Events DB/Inst: PDXPROD/PDXPROD Snaps: 2625-2635 -> 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: cache buffers chains 53,712 0 31,764 591 27.0 Backup: sbtwrite2 8,909 0 11,126 1249 4.5 latch free 3,198 0 1,851 579 1.6 latch: cache buffer handles 1,967 0 931 473 1.0 control file parallel write 3,392 0 256 75 1.7 db file parallel write 7,980 0 224 28 4.0 SQL*Net break/reset to client 7,047 0 181 26 3.5 log file parallel write 3,313 0 135 41 1.7 log file sync 1,714 0 115 67 0.9 direct path read 12,906 0 73 6 6.5 os thread startup 84 39 64 759 0.0 Backup: sbtinit 4 0 64 15901 0.0 db file sequential read 10,768 0 42 4 5.4 cursor: pin S wait on X 1,102 100 38 34 0.6 local write wait 466 2 37 80 0.2 control file sequential read 15,842 0 35 2 7.9 latch: enqueue hash chains 78 0 21 264 0.0 SQL*Net more data to client 80,901 0 20 0 40.6 enq: RO - fast object reuse 129 1 19 149 0.1 RMAN backup & recovery I/O 8,821 0 14 2 4.4 log file sequential read 492 0 11 22 0.2 cursor: pin S 64,033 0 10 0 32.1 enq: DX - contention 18 0 10 539 0.0 latch: session allocation 23 0 8 343 0.0 Log archive I/O 1,445 0 7 5 0.7 latch: shared pool 64 0 6 96 0.0 reliable message 131 0 5 36 0.1 rdbms ipc reply 145 0 4 31 0.1 direct path write temp 35 0 4 122 0.0 enq: CF - contention 3 33 4 1404 0.0 Backup: sbtbackup 3 0 4 1191 0.0 db file scattered read 251 0 1 4 0.1 enq: SQ - contention 2 0 1 453 0.0 wait list latch free 25 0 0 18 0.0 log file switch completion 2 0 0 218 0.0 db file parallel read 6 0 0 13 0.0 direct path write 1,136 0 0 0 0.6 latch: library cache 5 0 0 4 0.0 direct path read temp 102 0 0 0 0.1 log file single write 2 0 0 6 0.0 undo segment extension 9 100 0 1 0.0 latch: redo writing 8 0 0 0 0.0 latch: row cache objects 3 0 0 1 0.0 buffer busy waits 8 0 0 0 0.0 SQL*Net message from client 3,702,902 0 563,548 152 1,858.0 jobq slave wait 3,072 95 8,940 2910 1.5 virtual circuit status 300 100 8,753 29178 0.2 Streams AQ: qmn slave idle wait 320 0 8,745 27327 0.2 Streams AQ: qmn coordinator idle 652 51 8,744 13412 0.3 Streams AQ: waiting for time mana 2 100 933 ###### 0.0 Wait Events DB/Inst: PDXPROD/PDXPROD Snaps: 2625-2635 -> 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 --------------------------------- ------------ ------ ---------- ------ -------- SQL*Net message to client 3,702,890 0 76 0 1,857.9 class slave wait 34 0 6 189 0.0 SQL*Net more data from client 832 0 4 5 0.4 ------------------------------------------------------------- Background Wait Events DB/Inst: PDXPROD/PDXPROD Snaps: 2625-2635 -> %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 --------------------------------- ------------ ------ ---------- ------ -------- control file parallel write 3,355 0 239 71 1.7 db file parallel write 7,980 0 224 28 4.0 log file parallel write 3,314 0 135 41 1.7 os thread startup 84 39 64 759 0.0 log file sequential read 492 0 11 22 0.2 events in waitclass Other 67 1 10 145 0.0 Log archive I/O 1,445 0 7 5 0.7 latch: cache buffers chains 11 0 5 459 0.0 control file sequential read 3,270 0 0 0 1.6 direct path read 17 0 0 3 0.0 direct path write 17 0 0 1 0.0 log file single write 2 0 0 6 0.0 latch: redo writing 8 0 0 0 0.0 buffer busy waits 8 0 0 0 0.0 rdbms ipc message 32,851 87 94,122 2865 16.5 pmon timer 2,972 100 8,765 2949 1.5 Streams AQ: qmn slave idle wait 320 0 8,745 27327 0.2 Streams AQ: qmn coordinator idle 652 51 8,744 13412 0.3 smon timer 150 13 8,403 56018 0.1 Streams AQ: waiting for time mana 2 100 933 ###### 0.0 ------------------------------------------------------------- Wait Event Histogram DB/Inst: PDXPROD/PDXPROD Snaps: 2625-2635 -> 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 -------------------------- ----- ----- ----- ----- ----- ----- ----- ----- ----- Backup: sbtbackup 3 66.7 33.3 Backup: sbtinit 4 100.0 Backup: sbtinit2 4 100.0 Backup: sbtwrite2 8909 .0 39.5 2.1 34.9 23.6 LGWR wait for redo copy 3 100.0 Log archive I/O 1445 97.2 .1 .3 .3 2.1 RMAN backup & recovery I/O 8821 99.5 .1 .0 .0 .0 .1 .2 .0 SQL*Net break/reset to cli 7047 27.0 3.4 3.9 5.3 17.6 26.8 15.8 .2 SQL*Net more data to clien 80K 99.9 .0 .0 .0 buffer busy waits 8 100.0 control file parallel writ 3392 .0 1.4 15.4 56.0 26.8 .3 control file sequential re 15K 99.6 .2 .0 .0 .0 .1 .1 cursor: pin S 64K 99.9 .0 .0 .0 .0 .0 .0 cursor: pin S wait on X 1102 15.1 77.8 7.2 db file parallel read 6 33.3 33.3 33.3 db file parallel write 7980 1.3 .5 1.7 52.1 23.4 12.4 8.6 .1 db file scattered read 251 97.6 .4 .4 .4 1.2 db file sequential read 10K 90.3 .1 .2 3.5 3.6 .8 1.4 direct path read 12K 90.1 1.0 .9 2.0 2.1 1.1 2.8 direct path read temp 102 98.0 1.0 1.0 direct path write 1139 99.8 .1 .1 direct path write temp 35 80.0 5.7 11.4 2.9 enq: CF - contention 3 33.3 66.7 enq: DX - contention 18 5.6 5.6 72.2 16.7 enq: RO - fast object reus 129 6.2 49.6 41.9 2.3 enq: SQ - contention 2 50.0 50.0 latch free 3178 4.8 .4 .7 .5 1.2 1.0 77.5 14.0 latch: cache buffer handle 1959 .6 .2 .1 .3 .6 .5 90.1 7.7 latch: cache buffers chain 53K .6 .0 .1 .1 .2 .4 90.1 8.4 latch: enqueue hash chains 78 35.9 1.3 3.8 3.8 5.1 50.0 latch: library cache 5 60.0 20.0 20.0 latch: redo writing 8 100.0 latch: row cache objects 3 100.0 latch: session allocation 23 4.3 13.0 8.7 4.3 69.6 latch: shared pool 63 39.7 4.8 14.3 6.3 4.8 30.2 local write wait 466 .2 65.9 10.7 23.2 log file parallel write 3312 .0 .1 3.0 37.3 29.6 11.7 18.1 .2 log file sequential read 492 92.1 4.7 3.3 log file single write 2 100.0 log file switch completion 2 100.0 log file sync 1713 .3 .2 3.1 22.7 36.4 10.4 26.6 .3 os thread startup 84 97.6 2.4 rdbms ipc reply 145 79.3 .7 2.8 .7 2.1 1.4 13.1 reliable message 131 84.7 .8 .8 2.3 11.5 undo segment extension 9 88.9 11.1 wait list latch free 15 33.3 20.0 46.7 SQL*Net message from clien 3702K 69.0 15.1 3.5 1.3 2.7 6.9 1.1 .5 SQL*Net message to client 3702K 100.0 .0 .0 .0 .0 .0 SQL*Net more data from cli 832 93.4 3.5 1.8 .1 .4 .6 .2 Streams AQ: qmn coordinato 652 42.5 .5 .6 .3 1.7 1.8 1.5 51.1 Streams AQ: qmn slave idle 320 100.0 Wait Event Histogram DB/Inst: PDXPROD/PDXPROD Snaps: 2625-2635 -> 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 -------------------------- ----- ----- ----- ----- ----- ----- ----- ----- ----- Streams AQ: waiting for ti 2 100.0 class slave wait 34 11.8 5.9 5.9 76.5 dispatcher timer 150 100.0 jobq slave wait 3072 .0 .1 .0 .0 .6 99.2 pmon timer 2971 100.0 rdbms ipc message 32K 2.4 .4 .2 2.2 .6 .8 31.9 61.5 smon timer 150 13.3 7.3 2.0 4.7 .7 22.7 49.3 virtual circuit status 300 .3 99.7 ------------------------------------------------------------- SQL ordered by CPU DB/Inst: PDXPROD/PDXPROD Snaps: 2625-2635 -> Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code. -> Total DB CPU (s): 16,750 -> Captured SQL accounts for 98.7% 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 ---------- ------------ ---------- ------ ---------- --------------- ---------- 14456.36 7,000 2.07 86.3 497518.80 1,182,585,272 314328380 SELECT DISTINCT CM_NODE.NODE_ID, CM_NODE.CM_MODIFIED_DATE FROM C M_NODE, CM_OBJECT_CLASS A1, CM_PROPERTY_V A2, CM_PROPERTY_V A3, CM_PROPERTY_V A4 WHERE CM_NODE.NODE_ID = A3.NODE_ID AND CM_NODE. NODE_ID = A4.NODE_ID AND CM_NODE.NODE_ID = A2.NODE_ID AND CM_NOD E.LIFECYCLE_STATUS = 4 AND (A1.OBJECT_CLASS_ID = CM_NODE.OBJECT_ 1155.44 4,748 0.24 6.9 47043.22 83,644,586 3890761349 SELECT DISTINCT CM_NODE.NODE_ID FROM CM_NODE, CM_OBJECT_CLASS A1 , CM_PROPERTY_V A2, CM_PROPERTY_V A3, CM_PROPERTY_V A4, CM_PROPE RTY_V A5, (SELECT CM_NODE.NODE_ID FROM CM_NODE, CM_PROPERTY_V A1 WHERE CM_NODE.NODE_ID = A1.NODE_ID AND CM_NODE.LIFECYCLE_STAT US = 4 AND (A1.PROPERTY_NAME = :1 AND (A1.PROPERTY_TYPE = :2 AND 225.90 620 0.36 1.3 8978.55 10,922,763 3684285932 SELECT DISTINCT CM_NODE.NODE_ID, CM_NODE.meta_custom_priority, C M_NODE.meta_custom_date FROM CM_NODE, CM_OBJECT_CLASS A1, CM_PRO PERTY_V A2, CM_PROPERTY_V A3, CM_PROPERTY_V A4, (SELECT CM_NOD E.NODE_ID, CM_NODE.meta_custom_priority, CM_NODE.meta_custom_dat e FROM CM_NODE, CM_PROPERTY_V A1 WHERE CM_NODE.NODE_ID = A1.NODE 173.74 113,833 0.00 1.0 4853.27 1,545,582 304344946 select P.PROPERTY_DEFINITION_ID, P.PROPERTY_ID, P.PROPERTY_NAME, P.ORDERED_POSITION, P.NESTED_GROUP_ID, P.NODE_ID, P.PROPERTY_TY PE, P.BOOLEAN_VALUE, P.DATETIME_VALUE, P.DOUBLE_VALUE, P.LONG_VA LUE, P.TEXT_VALUE, P.LINKED_NODE_REPOSITORY_NAME, P.LINKED_NODE_ UID, P.BLOB_CONTENT_TYPE, P.BLOB_FILE_NAME, P.BLOB_FILE_SIZE, P. ------------------------------------------------------------- SQL ordered by Elapsed DB/Inst: PDXPROD/PDXPROD Snaps: 2625-2635 -> Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code. -> Total DB Time (s): 583,482 -> Captured SQL accounts for 99.0% 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 ---------- ------------ ---------- ------ ---------- --------------- ---------- 497518.80 7,000 71.07 85.3 14456.36 0 314328380 SELECT DISTINCT CM_NODE.NODE_ID, CM_NODE.CM_MODIFIED_DATE FROM C M_NODE, CM_OBJECT_CLASS A1, CM_PROPERTY_V A2, CM_PROPERTY_V A3, CM_PROPERTY_V A4 WHERE CM_NODE.NODE_ID = A3.NODE_ID AND CM_NODE. NODE_ID = A4.NODE_ID AND CM_NODE.NODE_ID = A2.NODE_ID AND CM_NOD E.LIFECYCLE_STATUS = 4 AND (A1.OBJECT_CLASS_ID = CM_NODE.OBJECT_ 47043.22 4,748 9.91 8.1 1155.44 0 3890761349 SELECT DISTINCT CM_NODE.NODE_ID FROM CM_NODE, CM_OBJECT_CLASS A1 , CM_PROPERTY_V A2, CM_PROPERTY_V A3, CM_PROPERTY_V A4, CM_PROPE RTY_V A5, (SELECT CM_NODE.NODE_ID FROM CM_NODE, CM_PROPERTY_V A1 WHERE CM_NODE.NODE_ID = A1.NODE_ID AND CM_NODE.LIFECYCLE_STAT US = 4 AND (A1.PROPERTY_NAME = :1 AND (A1.PROPERTY_TYPE = :2 AND 8978.55 620 14.48 1.5 225.90 0 3684285932 SELECT DISTINCT CM_NODE.NODE_ID, CM_NODE.meta_custom_priority, C M_NODE.meta_custom_date FROM CM_NODE, CM_OBJECT_CLASS A1, CM_PRO PERTY_V A2, CM_PROPERTY_V A3, CM_PROPERTY_V A4, (SELECT CM_NOD E.NODE_ID, CM_NODE.meta_custom_priority, CM_NODE.meta_custom_dat e FROM CM_NODE, CM_PROPERTY_V A1 WHERE CM_NODE.NODE_ID = A1.NODE ------------------------------------------------------------- SQL ordered by Gets DB/Inst: PDXPROD/PDXPROD Snaps: 2625-2635 -> 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,307,632,015 -> Captured SQL accounts for 100.1% 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 --------------- ------------ -------------- ------ -------- --------- ---------- 1,182,585,272 7,000 168,940.8 90.4 ######## ######### 314328380 SELECT DISTINCT CM_NODE.NODE_ID, CM_NODE.CM_MODIFIED_DATE FROM C M_NODE, CM_OBJECT_CLASS A1, CM_PROPERTY_V A2, CM_PROPERTY_V A3, CM_PROPERTY_V A4 WHERE CM_NODE.NODE_ID = A3.NODE_ID AND CM_NODE. NODE_ID = A4.NODE_ID AND CM_NODE.NODE_ID = A2.NODE_ID AND CM_NOD E.LIFECYCLE_STATUS = 4 AND (A1.OBJECT_CLASS_ID = CM_NODE.OBJECT_ 83,644,586 4,748 17,616.8 6.4 1155.44 47043.22 3890761349 SELECT DISTINCT CM_NODE.NODE_ID FROM CM_NODE, CM_OBJECT_CLASS A1 , CM_PROPERTY_V A2, CM_PROPERTY_V A3, CM_PROPERTY_V A4, CM_PROPE RTY_V A5, (SELECT CM_NODE.NODE_ID FROM CM_NODE, CM_PROPERTY_V A1 WHERE CM_NODE.NODE_ID = A1.NODE_ID AND CM_NODE.LIFECYCLE_STAT US = 4 AND (A1.PROPERTY_NAME = :1 AND (A1.PROPERTY_TYPE = :2 AND ------------------------------------------------------------- SQL ordered by Reads DB/Inst: PDXPROD/PDXPROD Snaps: 2625-2635 -> End Disk Reads Threshold: 1000 Total Disk Reads: 33,106 -> Captured SQL accounts for 29.6% 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 --------------- ------------ -------------- ------ -------- --------- ---------- 7,228 2 3,614.0 21.8 2.34 90.05 3860948734 Module: SQL Developer select to_char(trunc((sample_time),'HH'),'HH24:MI'), state, coun t(*)/360 from (select sample_time, sample_id , CASE WHEN session_state = 'ON CPU' THEN 'CPU' WHEN session_state = 'WAITING' AND wait_class IN ('User I/O') T HEN 'IO' WHEN session_state = 'WAITING' AND wait_clas 1,769 9 196.6 5.3 0.36 11.04 3693960384 SELECT count(*) over () as total_count, sd_xe_ash_nm.even t_name, sd_xe_ash_nm.event_id, sd_xe_ash_nm.parame ter1 as p1text, (CASE WHEN (sd_xe_ash_nm.parameter1 is NU LL OR sd_xe_ash_nm.paramet er1 = '0') THEN 0 ELSE 1 END) 1,492 13 114.8 4.5 8.24 335.91 1645547616 Module: SQL*Plus BEGIN LOAD_FLIGHTDATA; END; 1,018 2 509.0 3.1 1.63 60.63 3479051921 begin prvt_hdm.auto_execute( :db_id, :inst_id, :end_snap ); end; 581 20,313 0.0 1.8 5.75 707.63 901677360 Module: rman@db1.avinor.osl3.asap-asp.net (TNS V1-V3) declare thread number ; sequence number ; recid number ; alRec d bms_rcvman . alRec_t ; begin dbms_rcvman . getArchivedLog ( alRe c => alRec ) ; if ( :rlscn = alRec . rlgSCN and :stopthr = alRec . thread and ( ( alRec . sequence >= to_number ( :stopseq ) and :toclause = 0 ) or ( alRec . sequence > to_number ( :stopseq ) ------------------------------------------------------------- SQL ordered by Executions DB/Inst: PDXPROD/PDXPROD Snaps: 2625-2635 -> End Executions Threshold: 100 Total Executions: 1,735,621 -> Captured SQL accounts for 99.8% 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 ------------ --------------- ---------------- ----------- ---------- ---------- 497,779 497,778 1.0 0.00 0.00 2258273984 Select count(*) as num_children from CM_NODE where PARENT_NODE_I D = :1 381,734 381,733 1.0 0.00 0.00 26393183 Select 1 from CM_NODE where NODE_ID = :1 276,154 491,327 1.8 0.00 0.00 1645225549 select * from CM_NODE N where N.PARENT_NODE_ID = :1 ORDER BY N.C HILD_NODE_POSITION 227,115 227,115 1.0 0.00 0.00 3577070343 select * from CM_NODE N, (SELECT COUNT(*) as HASCHILDREN FROM CM _NODE WHERE PARENT_NODE_ID = :1) V where NODE_ID = :2 113,833 2,254,948 19.8 0.00 0.04 304344946 select P.PROPERTY_DEFINITION_ID, P.PROPERTY_ID, P.PROPERTY_NAME, P.ORDERED_POSITION, P.NESTED_GROUP_ID, P.NODE_ID, P.PROPERTY_TY PE, P.BOOLEAN_VALUE, P.DATETIME_VALUE, P.DOUBLE_VALUE, P.LONG_VA LUE, P.TEXT_VALUE, P.LINKED_NODE_REPOSITORY_NAME, P.LINKED_NODE_ UID, P.BLOB_CONTENT_TYPE, P.BLOB_FILE_NAME, P.BLOB_FILE_SIZE, P. 113,833 0 0.0 0.00 0.01 3619114294 select P.PROPERTY_DEFINITION_ID, P.PROPERTY_ID, P.PROPERTY_NAME, P.ORDERED_POSITION, P.NESTED_GROUP_ID, P.NODE_ID, P.PROPERTY_TY PE, P.BOOLEAN_VALUE, P.DATETIME_VALUE, P.DOUBLE_VALUE, P.LONG_VA LUE, P.TEXT_VALUE, P.LINKED_NODE_REPOSITORY_NAME, P.LINKED_NODE_ UID, P.BLOB_CONTENT_TYPE, P.BLOB_FILE_NAME, P.BLOB_FILE_SIZE, P. 20,313 20,309 1.0 0.00 0.03 901677360 Module: rman@db1.avinor.osl3.asap-asp.net (TNS V1-V3) declare thread number ; sequence number ; recid number ; alRec d bms_rcvman . alRec_t ; begin dbms_rcvman . getArchivedLog ( alRe c => alRec ) ; if ( :rlscn = alRec . rlgSCN and :stopthr = alRec . thread and ( ( alRec . sequence >= to_number ( :stopseq ) and :toclause = 0 ) or ( alRec . sequence > to_number ( :stopseq ) ------------------------------------------------------------- SQL ordered by Parse Calls DB/Inst: PDXPROD/PDXPROD Snaps: 2625-2635 -> End Parse Calls Threshold: 1000 Total Parse Calls: 62,410 -> Captured SQL accounts for 92.9% of Total Parse Calls -> SQL reported below exceeded 1.0% of Total Parse Calls % Total Old Parse Calls Executions Parses Hash Value ------------ ------------ -------- ---------- 11,204 13,658 17.95 3876449241 SELECT 1 FROM DUAL 6,983 7,000 11.19 314328380 SELECT DISTINCT CM_NODE.NODE_ID, CM_NODE.CM_MODIFIED_DATE FROM C M_NODE, CM_OBJECT_CLASS A1, CM_PROPERTY_V A2, CM_PROPERTY_V A3, CM_PROPERTY_V A4 WHERE CM_NODE.NODE_ID = A3.NODE_ID AND CM_NODE. NODE_ID = A4.NODE_ID AND CM_NODE.NODE_ID = A2.NODE_ID AND CM_NOD E.LIFECYCLE_STATUS = 4 AND (A1.OBJECT_CLASS_ID = CM_NODE.OBJECT_ 6,959 6,959 11.15 496164800 select local_tran_id, state, status, heuristic_dflt, session_vec tor, reco_vector, global_commit#, type# from pending_ trans$ pt where global_tran_fmt = :1 and (global_oracle_id = :2 or global_foreign_id = :3) and session_vector != '0000 0000' and exists (select * from pending_sessions$ where lo 1,300 3,489 2.08 2426148114 select BLOB_VALUE from CM_PROPERTY where NODE_ID = :1 AND PROPER TY_NAME = :2 1,207 1,207 1.93 4143084494 select privilege#,level from sysauth$ connect by grantee#=prior privilege# and privilege#>0 start with grantee#=:1 and privilege #>0 1,195 6,012 1.91 2902459408 select * from CM_NODE N where N.NODE_ID = :1 1,185 4,980 1.90 3560369712 select * from CM_NODE N, (SELECT COUNT(*) as HASCHILDREN FROM CM _NODE WHERE FULL_PATH LIKE :1) V where FULL_PATH = :2 1,074 4,748 1.72 3890761349 SELECT DISTINCT CM_NODE.NODE_ID FROM CM_NODE, CM_OBJECT_CLASS A1 , CM_PROPERTY_V A2, CM_PROPERTY_V A3, CM_PROPERTY_V A4, CM_PROPE RTY_V A5, (SELECT CM_NODE.NODE_ID FROM CM_NODE, CM_PROPERTY_V A1 WHERE CM_NODE.NODE_ID = A1.NODE_ID AND CM_NODE.LIFECYCLE_STAT US = 4 AND (A1.PROPERTY_NAME = :1 AND (A1.PROPERTY_TYPE = :2 AND 851 851 1.36 2803285 update sys.mon_mods$ set inserts = inserts + :ins, updates = upd ates + :upd, deletes = deletes + :del, flags = (decode(bitand(fl ags, :flag), :flag, flags, flags + :flag)), drop_segments = drop _segments + :dropseg, timestamp = :time where obj# = :objn 851 851 1.36 2396279102 lock table sys.mon_mods$ in exclusive mode nowait 772 276,154 1.24 1645225549 select * from CM_NODE N where N.PARENT_NODE_ID = :1 ORDER BY N.C HILD_NODE_POSITION SQL ordered by Parse Calls DB/Inst: PDXPROD/PDXPROD Snaps: 2625-2635 -> End Parse Calls Threshold: 1000 Total Parse Calls: 62,410 -> Captured SQL accounts for 92.9% of Total Parse Calls -> SQL reported below exceeded 1.0% of Total Parse Calls % Total Old Parse Calls Executions Parses Hash Value ------------ ------------ -------- ---------- 731 1,006 1.17 3955477075 SELECT DISTINCT CM_NODE.NODE_ID, CM_NODE.CM_MODIFIED_DATE FROM C M_NODE, CM_OBJECT_CLASS A1, CM_PROPERTY_V A2, CM_PROPERTY_V A3, (SELECT CM_NODE.NODE_ID, CM_NODE.CM_MODIFIED_DATE FROM CM_NODE , CM_PROPERTY_V A1 WHERE CM_NODE.NODE_ID = A1.NODE_ID AND CM_NOD E.LIFECYCLE_STATUS = 4 AND (A1.PROPERTY_NAME = :1 AND (A1.PROPER 661 381,734 1.06 26393183 Select 1 from CM_NODE where NODE_ID = :1 641 641 1.03 2850132846 update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts =:8,extsize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13 , 65535, NULL, :13),groups=decode(:14, 65535, NULL, :14), cacheh int=:15, hwmincr=:16, spare1=DECODE(:17,0,NULL,:17),scanhint=:18 where ts#=:1 and file#=:2 and block#=:3 ------------------------------------------------------------- SQL ordered by Sharable Memory DB/Inst: PDXPROD/PDXPROD Snaps: 2625-2635 -> End Sharable Memory Threshold: 1048576 Sharable Mem (b) Executions % Total Old Hash Value ---------------- ------------ ------- -------------- 1,200,308 25 0.2 1017781443 SELECT DISTINCT CM_NODE.NODE_ID, CM_NODE.meta_custom_date FROM C M_NODE, CM_OBJECT_CLASS A1, CM_PROPERTY_V A2, CM_PROPERTY_V A3, CM_PROPERTY_V A4, (SELECT CM_NODE.NODE_ID, CM_NODE.meta_custom _date FROM CM_NODE, CM_PROPERTY_V A1 WHERE CM_NODE.NODE_ID = A1. NODE_ID AND CM_NODE.LIFECYCLE_STATUS = 4 AND (A1.PROPERTY_NAME = ------------------------------------------------------------- SQL ordered by Version Count DB/Inst: PDXPROD/PDXPROD Snaps: 2625-2635 -> End Version Count Threshold: 20 Version Count Executions Old Hash Value -------- ------------ --------------- 21 28 3800831987 Module: SQL*Plus insert into plan_table (statement_id, timestamp, operation, opti ons,object_node, object_owner, object_name, object_instance, obj ect_type,search_columns, id, parent_id, position, other,optimize r, cost, cardinality, bytes, other_tag, partition_start, partiti on_stop, partition_id, distribution, cpu_cost, io_cost, temp_spa ------------------------------------------------------------- Instance Activity Stats DB/Inst: PDXPROD/PDXPROD Snaps: 2625-2635 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ CPU used by this session 1,675,794 186.2 840.8 CPU used when call started 1,673,257 186.0 839.6 CR blocks created 1,740 0.2 0.9 Cached Commit SCN referenced 30,404 3.4 15.3 Commit SCN cached 894 0.1 0.5 DB time 60,455,102 6,718.7 30,333.7 DBWR checkpoint buffers written 25,366 2.8 12.7 DBWR checkpoints 131 0.0 0.1 DBWR object drop buffers written 635 0.1 0.3 DBWR revisited being-written buff 42 0.0 0.0 DBWR tablespace checkpoint buffer 0 0.0 0.0 DBWR thread checkpoint buffers wr 0 0.0 0.0 DBWR transaction table writes 264 0.0 0.1 DBWR undo block writes 10,415 1.2 5.2 IMU CR rollbacks 4 0.0 0.0 IMU Flushes 152 0.0 0.1 IMU Redo allocation size 561,336 62.4 281.7 IMU commits 1,892 0.2 1.0 IMU contention 1 0.0 0.0 IMU ktichg flush 1 0.0 0.0 IMU pool not allocated 8 0.0 0.0 IMU recursive-transaction flush 0 0.0 0.0 IMU undo allocation size 9,231,240 1,025.9 4,631.8 IMU- failed to get a private stra 8 0.0 0.0 Misses for writing mapping 0 0.0 0.0 PX local messages recv'd 0 0.0 0.0 PX local messages sent 0 0.0 0.0 SMON posted for undo segment shri 9 0.0 0.0 SQL*Net roundtrips to/from client 3,703,083 411.6 1,858.0 active txn count during cleanout 11,618 1.3 5.8 application wait time 19,977 2.2 10.0 background checkpoints completed 1 0.0 0.0 background checkpoints started 1 0.0 0.0 background timeouts 28,570 3.2 14.3 branch node splits 5 0.0 0.0 buffer is not pinned count 932,911,492 103,679.9 468,094.1 buffer is pinned count 27,831,093,142 3,093,031.0 ############ bytes received via SQL*Net from c 592,706,153 65,870.9 297,394.0 bytes sent via SQL*Net to client 798,678,987 88,761.8 400,742.1 calls to get snapshot scn: kcmgss 2,552,235 283.6 1,280.6 calls to kcmgas 15,864 1.8 8.0 calls to kcmgcs 11,389 1.3 5.7 change write time 10,985 1.2 5.5 cleanout - number of ktugct calls 12,612 1.4 6.3 cleanouts and rollbacks - consist 4 0.0 0.0 cleanouts only - consistent read 926 0.1 0.5 cluster key scan block gets 87,756 9.8 44.0 cluster key scans 39,371 4.4 19.8 commit batch/immediate performed 4 0.0 0.0 commit batch/immediate requested 4 0.0 0.0 commit cleanout failures: block l 0 0.0 0.0 commit cleanout failures: buffer 0 0.0 0.0 commit cleanout failures: callbac 126 0.0 0.1 commit cleanout failures: cannot 0 0.0 0.0 commit cleanouts 19,914 2.2 10.0 commit cleanouts successfully com 19,788 2.2 9.9 Instance Activity Stats DB/Inst: PDXPROD/PDXPROD Snaps: 2625-2635 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ commit immediate performed 4 0.0 0.0 commit immediate requested 4 0.0 0.0 commit txn count during cleanout 1,808 0.2 0.9 concurrency wait time 3,193,628 354.9 1,602.4 consistent changes 17,380 1.9 8.7 consistent gets 1,306,587,656 145,208.7 655,588.4 consistent gets - examination 212,742,353 23,643.3 106,744.8 consistent gets direct 20,215 2.3 10.1 consistent gets from cache 1,306,567,441 145,206.4 655,578.2 current blocks converted for CR 0 0.0 0.0 cursor authentications 124 0.0 0.1 data blocks consistent reads - un 2,995 0.3 1.5 db block changes 1,147,132 127.5 575.6 db block gets 1,044,354 116.1 524.0 db block gets direct 3,392 0.4 1.7 db block gets from cache 1,040,962 115.7 522.3 deferred (CURRENT) block cleanout 13,216 1.5 6.6 dirty buffers inspected 2 0.0 0.0 enqueue conversions 2,990,134 332.3 1,500.3 enqueue releases 4,198,646 466.6 2,106.7 enqueue requests 4,198,711 466.6 2,106.7 enqueue timeouts 3 0.0 0.0 enqueue waits 150 0.0 0.1 execute count 1,735,621 192.9 870.9 failed probes on index block recl 0 0.0 0.0 free buffer inspected 5,034 0.6 2.5 free buffer requested 27,034 3.0 13.6 heap block compress 1,651 0.2 0.8 hot buffers moved to head of LRU 16,496 1.8 8.3 immediate (CR) block cleanout app 930 0.1 0.5 immediate (CURRENT) block cleanou 4,971 0.6 2.5 index fast full scans (full) 2,117 0.2 1.1 index fetch by key 225,719,309 25,085.5 113,256.1 index scans kdiixs1 209,042,696 23,232.1 104,888.5 leaf node 90-10 splits 301 0.0 0.2 leaf node splits 735 0.1 0.4 lob reads 13,622 1.5 6.8 lob writes 5,997 0.7 3.0 lob writes unaligned 5,984 0.7 3.0 logons cumulative 711 0.1 0.4 messages received 12,092 1.3 6.1 messages sent 12,092 1.3 6.1 no buffer to keep pinned count 0 0.0 0.0 no work - consistent read gets 883,978,357 98,241.7 443,541.6 opened cursors cumulative 57,057 6.3 28.6 parse count (failures) 4 0.0 0.0 parse count (hard) 956 0.1 0.5 parse count (total) 62,410 6.9 31.3 parse time cpu 769 0.1 0.4 parse time elapsed 17,525 2.0 8.8 physical read IO requests 24,027 2.7 12.1 physical read bytes 271,204,352 30,140.5 136,078.5 physical read total IO requests 45,011 5.0 22.6 physical read total bytes 3,915,815,936 435,187.4 1,964,784.7 physical read total multi block r 9,142 1.0 4.6 physical reads 33,106 3.7 16.6 Instance Activity Stats DB/Inst: PDXPROD/PDXPROD Snaps: 2625-2635 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ physical reads cache 12,293 1.4 6.2 physical reads cache prefetch 1,340 0.2 0.7 physical reads direct 20,813 2.3 10.4 physical reads direct (lob) 20,177 2.2 10.1 physical reads direct temporary t 582 0.1 0.3 physical reads prefetch warmup 0 0.0 0.0 physical write IO requests 8,885 1.0 4.5 physical write bytes 249,577,472 27,737.0 125,227.0 physical write total IO requests 22,959 2.6 11.5 physical write total bytes 1,141,903,360 126,906.4 572,957.0 physical write total multi block 6,636 0.7 3.3 physical writes 30,466 3.4 15.3 physical writes direct 3,990 0.4 2.0 physical writes direct (lob) 53 0.0 0.0 physical writes direct temporary 583 0.1 0.3 physical writes from cache 26,476 2.9 13.3 physical writes non checkpoint 14,892 1.7 7.5 pinned buffers inspected 35 0.0 0.0 prefetch warmup blocks aged out b 0 0.0 0.0 prefetched blocks aged out before 0 0.0 0.0 process last non-idle time 8,998 1.0 4.5 recovery blocks read 0 0.0 0.0 recursive aborts on index block r 0 0.0 0.0 recursive calls 911,850 101.3 457.5 recursive cpu usage 6,189 0.7 3.1 redo blocks read for recovery 0 0.0 0.0 redo blocks written 419,406 46.6 210.4 redo buffer allocation retries 2 0.0 0.0 redo entries 575,689 64.0 288.9 redo log space requests 2 0.0 0.0 redo log space wait time 45 0.0 0.0 redo ordering marks 6,782 0.8 3.4 redo size 206,380,604 22,936.3 103,552.7 redo synch time 11,855 1.3 6.0 redo synch writes 7,765 0.9 3.9 redo wastage 997,008 110.8 500.3 redo write time 13,540 1.5 6.8 redo writer latching time 0 0.0 0.0 redo writes 3,314 0.4 1.7 rollback changes - undo records a 8 0.0 0.0 rollbacks only - consistent read 1,591 0.2 0.8 rows fetched via callback 1,135,809 126.2 569.9 session connect time 0 0.0 0.0 session cursor cache hits 44,156 4.9 22.2 session logical reads 1,307,632,015 145,324.7 656,112.4 session pga memory 358,533,952 39,846.0 179,896.6 session pga memory max 1,095,927,968 121,796.8 549,888.6 session uga memory 700,123,436,648 77,808,783.8 ############ session uga memory max 1,908,610,112 212,114.9 957,656.9 shared hash latch upgrades - no w 209,039,860 23,231.8 104,887.0 shared hash latch upgrades - wait 2,730 0.3 1.4 sorts (disk) 1 0.0 0.0 sorts (memory) 617,329 68.6 309.8 sorts (rows) 18,809,543 2,090.4 9,437.8 sql area evicted 199 0.0 0.1 sql area purged 72 0.0 0.0 Instance Activity Stats DB/Inst: PDXPROD/PDXPROD Snaps: 2625-2635 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ summed dirty queue length 2 0.0 0.0 switch current to new buffer 1,625 0.2 0.8 table fetch by rowid 14,132,632,243 1,570,641.5 7,091,135.1 table fetch continued row 510,590 56.7 256.2 table scan blocks gotten 111,879,589 12,433.8 56,136.3 table scan rows gotten 6,495,817,031 721,917.9 3,259,316.1 table scans (long tables) 0 0.0 0.0 table scans (short tables) 282,257 31.4 141.6 total number of times SMON posted 130 0.0 0.1 transaction rollbacks 4 0.0 0.0 transaction tables consistent rea 0 0.0 0.0 transaction tables consistent rea 0 0.0 0.0 undo change vector size 74,915,948 8,325.8 37,589.5 user I/O wait time 15,839 1.8 8.0 user calls 3,732,826 414.9 1,873.0 user commits 1,993 0.2 1.0 user rollbacks 0 0.0 0.0 workarea executions - onepass 2 0.0 0.0 workarea executions - optimal 666,538 74.1 334.4 write clones created in backgroun 455 0.1 0.2 write clones created in foregroun 3 0.0 0.0 ------------------------------------------------------------- Instance Activity Stats DB/Inst: PDXPROD/PDXPROD Snaps: 2625-2635 -> Statistics with absolute values (should not be diffed) Statistic Begin Value End Value --------------------------------- --------------- --------------- logons current 139 164 opened cursors current 1,136 1,391 session cursor cache count 1,050,311 1,058,261 workarea memory allocated 19,333 29,947 ------------------------------------------------------------- Instance Activity Stats DB/Inst: PDXPROD/PDXPROD Snaps: 2625-2635 -> Statistics identified by '(derived)' come from sources other than SYSSTAT Statistic Total per Hour --------------------------------- ------------------ --------- log switches (derived) 1 .40 ------------------------------------------------------------- OS Statistics DB/Inst: PDXPROD/PDXPROD Snaps: 2625-2635 -> ordered by statistic type (CPU use, Virtual Memory, Hardware Config), Name Statistic Total ------------------------- ---------------------- BUSY_TIME 1,802,616 IDLE_TIME 0 SYS_TIME 96,260 USER_TIME 1,706,356 LOAD 93 OS_CPU_WAIT_TIME 69,269,700 VM_IN_BYTES 69,025,792 VM_OUT_BYTES 0 PHYSICAL_MEMORY_BYTES 17,171,005,440 NUM_CPUS 2 ------------------------------------------------------------- Tablespace IO Stats DB/Inst: PDXPROD/PDXPROD Snaps: 2625-2635 ->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) -------------- ------- ------ ------- ------------ -------- ---------- ------ WEBLOGIC_DATA 13,334 1 11.9 1.5 448 0 0 0.0 SYSAUX 8,545 1 3.0 1.0 1,398 0 0 0.0 PERFSTAT 154 0 9.0 1.0 3,506 0 0 0.0 AVINORINTERNETT_DATA 1,154 0 0.8 2.1 1,790 0 0 0.0 UNDOTBS1 1 0 0.0 1.0 1,145 0 8 0.0 SYSTEM 402 0 19.4 1.0 367 0 0 0.0 WEBLOGIC_INDEX 313 0 13.1 1.0 197 0 0 0.0 TEMP 102 0 950.9 5.7 24 0 0 0.0 AVINORINTERNETT_INDEX 1 0 10.0 1.0 1 0 0 0.0 AVINORINTERNETT2_INDEX 1 0 30.0 1.0 1 0 0 0.0 USERS 1 0 30.0 1.0 1 0 0 0.0 WEBLOGIC2_DATA 1 0 20.0 1.0 1 0 0 0.0 WEBLOGIC2_INDEX 1 0 40.0 1.0 1 0 0 0.0 AVINORINTERNETT2_DATA 1 0 50.0 1.0 1 0 0 0.0 BF_LTOM 1 0 40.0 1.0 1 0 0 0.0 ------------------------------------------------------------- File IO Stats DB/Inst: PDXPROD/PDXPROD Snaps: 2625-2635 ->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) -------------- ------- ----- --- ------- ------------ -------- ---------- ------ AVINORINTERNETT2_DATA /global/data/oracle/PDXPROD/avinorinternett2_data.db 1 0 50.0 1.0 1 0 0 AVINORINTERNETT2_INDEX /global/data/oracle/PDXPROD/avinorinternett2_index.d 1 0 30.0 1.0 1 0 0 AVINORINTERNETT_DATA /global/data/oracle/PDXPROD/avinorinternett_data.dbf 496 0 0.1 1 2.4 756 0 0 /global/data/oracle/PDXPROD/avinorinternett_data02.d 658 0 1.2 1 1.9 1,034 0 0 AVINORINTERNETT_INDEX /global/data/oracle/PDXPROD/avinorinternett_index.db 1 0 10.0 1.0 1 0 0 BF_LTOM /global/data/oracle/PDXPROD/ltom01.dbf 1 0 40.0 1.0 1 0 0 PERFSTAT /global/data/oracle/PDXPROD/perfstat.dbf 154 0 9.0 ### 1.0 3,506 0 0 SYSAUX /global/data/oracle/PDXPROD/sysaux01.dbf 8,545 1 3.0 ### 1.0 1,398 0 0 SYSTEM /global/data/oracle/PDXPROD/system01.dbf 402 0 19.4 ### 1.0 367 0 0 TEMP /global/data/oracle/PDXPROD/temp01.dbf 102 0 ##### 1 5.7 24 0 0 UNDOTBS1 /global/data/oracle/PDXPROD/undotbs01.dbf 1 0 0.0 1.0 1,145 0 8 0.0 USERS /global/data/oracle/PDXPROD/users01.dbf 1 0 30.0 1.0 1 0 0 WEBLOGIC2_DATA /global/data/oracle/PDXPROD/weblogic2_data.dbf 1 0 20.0 1.0 1 0 0 WEBLOGIC2_INDEX /global/data/oracle/PDXPROD/weblogic2_index.dbf 1 0 40.0 1.0 1 0 0 WEBLOGIC_DATA /global/data/oracle/PDXPROD/weblogic_data.dbf 61 0 17.9 ### 1.3 36 0 0 /global/data/oracle/PDXPROD/weblogic_data02.dbf 30 0 44.7 ### 1.0 81 0 0 /global/data/oracle/PDXPROD/weblogic_data03.dbf 13,243 1 11.8 ### 1.5 331 0 0 WEBLOGIC_INDEX /global/data/oracle/PDXPROD/weblogic_index.dbf 313 0 13.1 ### 1.0 197 0 0 File IO Stats DB/Inst: PDXPROD/PDXPROD Snaps: 2625-2635 ->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) -------------- ------- ----- --- ------- ------------ -------- ---------- ------ ------------------------------------------------------------- File Read Histogram Stats DB/Inst: PDXPROD/PDXPROD Snaps: 2625-2635 ->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 ------------ ------------ ------------ ------------ ------------ ------------ WEBLOGIC_INDEX /global/data/oracle/PDXPROD/weblogic_index.dbf 173 1 32 58 16 12 AVINORINTERNETT_DATA /global/data/oracle/PDXPROD/avinorinternett_data02.d 552 0 0 0 0 0 WEBLOGIC_DATA /global/data/oracle/PDXPROD/weblogic_data.dbf 29 0 3 9 4 4 SYSAUX /global/data/oracle/PDXPROD/sysaux01.dbf 7,874 18 246 217 35 79 PERFSTAT /global/data/oracle/PDXPROD/perfstat.dbf 129 0 4 5 5 10 SYSTEM /global/data/oracle/PDXPROD/system01.dbf 242 1 56 51 19 32 AVINORINTERNETT_DATA /global/data/oracle/PDXPROD/avinorinternett_data.dbf 365 0 0 0 0 0 WEBLOGIC_DATA /global/data/oracle/PDXPROD/weblogic_data03.dbf 327 0 33 41 9 15 /global/data/oracle/PDXPROD/weblogic_data02.dbf 12 0 4 8 1 3 TEMP /global/data/oracle/PDXPROD/temp01.dbf 1 0 0 0 0 0 ------------------------------------------------------------- Buffer Pool Statistics DB/Inst: PDXPROD/PDXPROD Snaps: 2625-2635 -> 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 55K 100 -3,098,896,690 12,295 26,475 0 0 8 ------------------------------------------------------------- Instance Recovery Stats DB/Inst: PDXPROD/PDXPROD Snaps: 2625-2635 -> 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 20 1150 26718 147329 1843200 147329 E 0 21 1561 31678 148539 1843200 148539 ------------------------------------------------------------- Buffer Pool Advisory DB/Inst: PDXPROD/PDXPROD End Snap: 2635 -> 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 32 .1 4 32.6 82,400 50,715 3.2 D 64 .1 8 2.4 6,066 3,148 .2 D 96 .2 12 1.6 4,081 1,911 .1 D 128 .3 16 1.4 3,583 1,601 .1 D 160 .4 20 1.3 3,236 1,384 .1 D 192 .4 24 1.2 2,960 1,212 .1 D 224 .5 28 1.1 2,782 1,102 .1 D 256 .6 32 1.1 2,711 1,057 .1 D 288 .6 36 1.1 2,656 1,023 .1 D 320 .7 40 1.0 2,628 1,006 .1 D 352 .8 44 1.0 2,602 989 .1 D 384 .9 48 1.0 2,581 976 .1 D 416 .9 52 1.0 2,554 960 .1 D 448 1.0 56 1.0 2,526 942 .1 D 480 1.1 59 1.0 2,486 917 .1 D 512 1.1 63 1.0 2,459 900 .1 D 544 1.2 67 1.0 2,442 889 .1 D 576 1.3 71 1.0 2,432 883 .1 D 608 1.4 75 1.0 2,421 877 .1 D 640 1.4 79 1.0 2,413 872 .1 ------------------------------------------------------------- Buffer wait Statistics DB/Inst: PDXPROD/PDXPROD Snaps: 2625-2635 -> ordered by wait time desc, waits desc Class Waits Total Wait Time (s) Avg Time (ms) ---------------------- ----------- ------------------- ------------- undo header 8 0 0 ------------------------------------------------------------- PGA Aggr Target Stats DB/Inst: PDXPROD/PDXPROD Snaps: 2625-2635 -> B: Begin snap E: End snap (rows identified with B or E contain data which is absolute i.e. not diffed over the interval) -> PGA cache hit % - percentage of W/A (WorkArea) data processed only in-memory -> Auto PGA Target - actual workarea memory target -> W/A PGA Used - amount of memory used for all Workareas (manual + auto) -> %PGA W/A Mem - percentage of PGA memory allocated to workareas -> %Auto W/A Mem - percentage of workarea memory controlled by Auto Mem Mgmt -> %Man W/A Mem - percentage of workarea memory under manual control PGA Cache Hit % W/A MB Processed Extra W/A MB Read/Written --------------- ---------------- ------------------------- 100.0 118,553 10 %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 399 260 269.8 12.4 4.6 95.9 4.1 81,715 E 399 193 392.1 41.4 10.6 100.0 .0 15,307 ------------------------------------------------------------- PGA Aggr Target Histogram DB/Inst: PDXPROD/PDXPROD Snaps: 2625-2635 -> Optimal Executions are purely in-memory operations Low High Optimal Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs ------- ------- -------------- ------------- ------------ ------------ 2K 4K 517,901 517,901 0 0 64K 128K 9,579 9,579 0 0 128K 256K 791 791 0 0 256K 512K 20 20 0 0 512K 1024K 117,837 117,837 0 0 1M 2M 20,655 20,655 0 0 2M 4M 6 6 0 0 4M 8M 4 2 2 0 ------------------------------------------------------------- PGA Memory Advisory DB/Inst: PDXPROD/PDXPROD End Snap: 2635 -> 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 ---------- ------- ---------------- ---------------- -------- ---------- 50 0.1 11,245,434.3 1,488,085.9 88.0 74,097 100 0.3 11,245,434.3 588,976.4 95.0 10,859 200 0.5 11,245,434.3 82,453.8 99.0 3,386 299 0.8 11,245,434.3 38,205.0 100.0 1,885 399 1.0 11,245,434.3 997.3 100.0 1,876 479 1.2 11,245,434.3 992.3 100.0 1,858 559 1.4 11,245,434.3 990.2 100.0 1,340 638 1.6 11,245,434.3 990.2 100.0 1,337 718 1.8 11,245,434.3 990.2 100.0 1,337 798 2.0 11,245,434.3 990.2 100.0 1,336 1,197 3.0 11,245,434.3 990.2 100.0 1,334 1,596 4.0 11,245,434.3 990.2 100.0 1,333 2,394 6.0 11,245,434.3 990.2 100.0 1,331 3,192 8.0 11,245,434.3 990.2 100.0 1,330 ------------------------------------------------------------- Process Memory Summary Stats DB/Inst: PDXPROD/PDXPROD Snaps: 2625-2635 -> 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 -------- 269.9 122.2 92.4 1.9 3.1 22 24 141 Other 160.6 1.1 3.1 22 24 141 Freeable 92.4 .0 .9 .3 2 101 SQL 16.4 14.2 .1 .5 4 9 112 PL/SQL .5 .1 .0 .0 0 0 139 E -------- 392.4 225.4 46.3 2.3 5.3 39 40 167 Other 295.8 1.8 5.3 39 39 167 SQL 48.7 45.1 .3 1.0 6 11 139 Freeable 46.3 .0 .7 .4 2 65 PL/SQL 1.6 .7 .0 .0 0 0 165 ------------------------------------------------------------- Top Process Memory (by component) DB/Inst: PDXPROD/PDXPROD Snaps: 2625-2635 -> 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 -------- 22.1 10.4 .0 22.1 23.8 Other 22.1 22.1 23.8 PL/SQL .0 .0 .0 .0 18 ARC0 -------- 21.9 10.4 .0 21.9 21.9 Other 21.9 21.9 21.9 PL/SQL .0 .0 .0 .0 19 ARC1 -------- 21.9 10.4 .0 21.9 21.9 Other 21.9 21.9 21.9 PL/SQL .0 .0 .0 .0 92 ------------ 5.3 5.0 .0 5.3 6.5 SQL 4.5 4.5 4.5 5.6 Other .8 .8 .9 PL/SQL .0 .0 .0 .0 81 ------------ 4.0 1.8 .0 4.0 4.0 Other 4.0 4.0 4.0 PL/SQL .0 .0 .0 .0 SQL .0 .0 .0 .4 23 J000 -------- 3.5 1.0 2.0 3.5 10.5 Freeable 2.0 .0 2.0 Other 1.3 1.3 1.7 PL/SQL .1 .0 .1 .1 SQL .1 .1 .1 6.7 13 MMON -------- 3.4 1.5 1.4 3.4 3.6 Other 1.8 1.8 1.8 Freeable 1.4 .0 1.4 SQL .1 .0 .1 1.1 PL/SQL .0 .0 .0 .1 107 ------------ 3.2 3.0 .0 3.2 6.4 SQL 2.5 2.5 2.5 5.6 Other .7 .7 .8 PL/SQL .0 .0 .0 .0 148 ------------ 3.2 3.0 .0 3.2 6.4 SQL 2.5 2.5 2.5 5.6 Other .7 .7 .8 PL/SQL .0 .0 .0 .0 7 CKPT -------- 2.3 .4 1.4 2.3 3.8 Freeable 1.4 .0 1.4 Other .8 .8 2.3 SQL .0 .0 .0 .1 PL/SQL .0 .0 .0 .0 10 CJQ0 -------- 2.3 .8 1.1 2.3 2.6 Other 1.2 1.2 1.2 Freeable 1.1 .0 1.1 SQL .1 .0 .1 1.0 PL/SQL .0 .0 .0 .0 75 ------------ 2.2 2.1 .0 2.2 10.2 SQL 1.4 1.4 1.4 9.2 Other .8 .8 1.0 PL/SQL .0 .0 .0 .0 8 SMON -------- 2.1 .6 1.4 2.1 2.6 Freeable 1.4 .0 1.4 Other .7 .7 .7 SQL .0 .0 .0 1.3 Top Process Memory (by component) DB/Inst: PDXPROD/PDXPROD Snaps: 2625-2635 -> ordered by Begin/End snapshot, Alloc (MB) desc Alloc Used Freeabl Max Hist Max PId Category (MB) (MB) (MB) Alloc (MB) Alloc (MB) - ------ ------------- ------- ------- -------- ---------- ---------- B 8 PL/SQL .0 .0 .0 .0 51 ------------ 2.0 .8 1.1 2.0 5.3 Freeable 1.1 .0 1.1 Other .9 .9 3.2 PL/SQL .0 .0 .0 .0 SQL .0 .0 .0 1.0 96 ------------ 2.0 .8 1.1 2.0 8.3 Freeable 1.1 .0 1.1 Other .8 .8 1.4 SQL .0 .0 .0 5.8 PL/SQL .0 .0 .0 .0 108 ------------ 2.0 .8 1.1 2.0 7.2 Freeable 1.1 .0 1.1 Other .8 .8 .8 SQL .0 .0 .0 5.8 PL/SQL .0 .0 .0 .0 76 ------------ 1.9 .9 1.0 1.9 7.1 Freeable 1.0 .0 1.0 Other .9 .9 .9 SQL .0 .0 .0 5.8 PL/SQL .0 .0 .0 .0 84 ------------ 1.9 .7 1.1 1.9 2.0 Freeable 1.1 .0 1.1 Other .8 .8 .8 SQL .0 .0 .0 1.2 PL/SQL .0 .0 .0 .0 125 ------------ 1.9 1.7 .0 1.9 6.4 SQL 1.2 1.2 1.2 5.6 Other .7 .7 .8 PL/SQL .0 .0 .0 .0 30 ------------ 1.8 .7 .8 1.8 5.0 Other 1.0 1.0 4.1 Freeable .8 .0 .8 PL/SQL .0 .0 .0 .0 SQL .0 .0 .0 .1 E 116 TNS V1-V3 --- 39.3 17.8 .0 39.3 39.6 Other 39.2 39.2 39.3 PL/SQL .1 .1 .1 .1 SQL .0 .0 .0 .2 177 TNS V1-V3 --- 39.3 17.9 .0 39.3 39.6 Other 39.2 39.2 39.3 PL/SQL .1 .1 .1 .1 SQL .0 .0 .0 .2 6 LGWR -------- 22.1 10.4 .0 22.1 23.8 Other 22.1 22.1 23.8 PL/SQL .0 .0 .0 .0 18 ARC0 -------- 21.9 10.4 .0 21.9 21.9 Other 21.9 21.9 21.9 PL/SQL .0 .0 .0 .0 19 ARC1 -------- 21.9 10.4 .0 21.9 21.9 Other 21.9 21.9 21.9 PL/SQL .0 .0 .0 .0 147 TNS V1-V3 --- 19.8 17.9 .0 19.8 39.6 Top Process Memory (by component) DB/Inst: PDXPROD/PDXPROD Snaps: 2625-2635 -> ordered by Begin/End snapshot, Alloc (MB) desc Alloc Used Freeabl Max Hist Max PId Category (MB) (MB) (MB) Alloc (MB) Alloc (MB) - ------ ------------- ------- ------- -------- ---------- ---------- E 147 Other 19.7 19.7 39.3 PL/SQL .1 .1 .1 .1 SQL .0 .0 .0 .2 123 TNS V1-V3 --- 9.7 4.6 .0 9.7 9.7 Other 9.5 9.5 9.5 PL/SQL .2 .0 .2 .2 SQL .0 .0 .0 .2 31 TNS V1-V3 --- 7.7 6.5 .0 7.7 11.1 SQL 5.6 4.7 5.6 9.5 Other 2.1 2.1 2.1 PL/SQL .1 .1 .1 .1 167 ------------ 5.9 4.4 .9 5.9 20.8 Other 4.6 4.6 8.3 Freeable .9 .0 .9 PL/SQL .2 .0 .2 .2 SQL .2 .1 .2 11.3 126 ------------ 5.8 5.5 .0 5.8 5.8 SQL 5.1 5.1 5.1 5.1 Other .7 .7 .7 PL/SQL .0 .0 .0 .0 53 ------------ 5.3 5.0 .0 5.3 6.5 SQL 4.5 4.4 4.5 5.6 Other .8 .8 .9 PL/SQL .0 .0 .0 .0 74 ------------ 5.3 5.0 .0 5.3 6.6 SQL 4.4 4.4 4.4 5.6 Other .8 .8 1.0 PL/SQL .0 .0 .0 .0 128 ------------ 5.2 4.9 .1 5.2 6.5 SQL 4.3 4.3 4.3 5.6 Other .7 .7 .8 Freeable .1 .0 .1 PL/SQL .0 .0 .0 .0 102 ------------ 4.8 4.4 .1 4.8 6.5 SQL 4.0 4.0 4.0 5.6 Other .7 .7 .8 Freeable .1 .0 .1 PL/SQL .0 .0 .0 .0 82 ------------ 4.1 2.1 1.9 4.1 6.5 Freeable 1.9 .0 1.9 SQL 1.5 1.5 1.5 5.6 Other .8 .8 .8 PL/SQL .0 .0 .0 .0 110 ------------ 3.8 3.3 .3 3.8 6.5 SQL 2.8 2.7 2.8 5.6 Other .8 .8 .8 Freeable .3 .0 .3 PL/SQL .0 .0 .0 .0 23 J000 -------- 3.5 1.0 1.9 3.5 4.1 Freeable 1.9 .0 1.9 Other 1.4 1.4 1.4 PL/SQL .1 .0 .1 .1 SQL .1 .1 .1 2.4 Top Process Memory (by component) DB/Inst: PDXPROD/PDXPROD Snaps: 2625-2635 -> ordered by Begin/End snapshot, Alloc (MB) desc Alloc Used Freeabl Max Hist Max PId Category (MB) (MB) (MB) Alloc (MB) Alloc (MB) - ------ ------------- ------- ------- -------- ---------- ---------- E 114 TNS V1-V3 --- 3.5 1.5 .0 3.5 3.5 Other 3.4 3.4 3.4 PL/SQL .1 .0 .1 .1 SQL .0 .0 .0 .2 172 ------------ 3.4 3.2 .0 3.4 6.6 SQL 2.6 2.6 2.6 5.6 Other .8 .8 1.1 PL/SQL .0 .0 .0 .0 138 ------------ 3.3 3.1 .0 3.3 6.6 SQL 2.6 2.5 2.6 5.6 Other .8 .8 1.0 PL/SQL .0 .0 .0 .0 ------------------------------------------------------------- Enqueue activity DB/Inst: PDXPROD/PDXPROD Snaps: 2625-2635 -> 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) ------------ ------------ ----------- ----------- ------------ -------------- RO-Multiple Object Reuse (fast object reuse) 1,170 1,170 0 128 20 153.80 DX-Distributed Transaction 7,093,938 7,093,938 0 18 10 552.00 CF-Controlfile Transaction 4,339 4,339 0 2 4 2,156.00 SQ-Sequence Cache 32 32 0 2 1 463.00 ------------------------------------------------------------- Undo Segment Summary DB/Inst: PDXPROD/PDXPROD Snaps: 2625-2635 -> 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 11.6 5,146 2,114 5 15/47.3 0/0 0/0/0/0/0/0 ------------------------------------------------------------- Undo Segment Stats DB/Inst: PDXPROD/PDXPROD Snaps: 2625-2635 -> 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 ------------ ----------- ------------ ------- ------- ------- ----- ----------- 02-Oct 10:52 1,427 550 61 4 15 0/0 0/0/0/0/0/0 02-Oct 10:42 1,294 521 61 3 15 0/0 0/0/0/0/0/0 02-Oct 10:32 1,391 309 2,114 3 47 0/0 0/0/0/0/0/0 02-Oct 10:22 805 395 1,508 4 37 0/0 0/0/0/0/0/0 02-Oct 10:12 1,265 237 902 2 27 0/0 0/0/0/0/0/0 02-Oct 10:02 1,472 584 59 5 15 0/0 0/0/0/0/0/0 02-Oct 09:52 806 353 82 4 15 0/0 0/0/0/0/0/0 02-Oct 09:42 42 124 112 2 15 0/0 0/0/0/0/0/0 02-Oct 09:32 191 300 205 3 16 0/0 0/0/0/0/0/0 02-Oct 09:22 190 185 141 3 15 0/0 0/0/0/0/0/0 02-Oct 09:12 46 255 110 2 15 0/0 0/0/0/0/0/0 02-Oct 09:02 246 237 1,761 3 42 0/0 0/0/0/0/0/0 02-Oct 08:52 167 230 1,156 3 32 0/0 0/0/0/0/0/0 02-Oct 08:42 67 203 548 2 22 0/0 0/0/0/0/0/0 02-Oct 08:32 930 264 99 4 15 0/0 0/0/0/0/0/0 02-Oct 08:22 1,273 399 78 3 15 0/0 0/0/0/0/0/0 ------------------------------------------------------------- Latch Activity DB/Inst: PDXPROD/PDXPROD Snaps: 2625-2635 ->"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 ------------------------ -------------- ------ ------ ------ ------------ ------ AWR Alerted Metric Eleme 41,307 0.0 0 0 Consistent RBA 3,315 0.0 0 0 FAL request queue 182 0.0 0 0 FAL subheap alocation 182 0.0 0 0 FIB s.o chain latch 130 0.0 0 0 FOB s.o list latch 2,399 0.0 0 0 In memory undo latch 19,356 0.0 0 4,320 0.0 JS mem alloc latch 12 0.0 0 0 JS queue access latch 12 0.0 0 0 JS queue state obj latch 64,652 0.0 0 0 JS slv state obj latch 44 0.0 0 0 KMG MMAN ready and start 2,981 0.0 0 0 KTF sga latch 23 0.0 0 2,282 0.0 KWQMN job cache list lat 2 0.0 0 0 KWQP Prop Status 10 0.0 0 0 MQL Tracking Latch 0 0 178 0.0 Memory Management Latch 0 0 2,981 0.0 OS process 1,875 0.0 0 0 OS process allocation 4,255 0.0 0 0 OS process: request allo 1,071 0.0 0 0 PL/SQL warning settings 1,414 0.0 0 0 SQL memory manager latch 12 0.0 0 2,900 0.0 SQL memory manager worka 748,728 0.0 0.3 1 0 Shared B-Tree 350 0.0 0 0 X$KSFQP 24 0.0 0 0 active checkpoint queue 12,296 0.0 0 0 active service list 21,759 0.0 0 2,972 0.0 archive control 3,531 0.0 0 0 archive process latch 3,082 0.0 0 0 begin backup scn array 611 0.0 0 0 cache buffer handles 82,907,422 0.1 0.0 931 0 cache buffers chains 2,405,284,970 0.1 0.0 31829 24,586 0.0 cache buffers lru chain 113,432 0.0 0.0 0 11,511 0.1 cache table scan latch 0 0 251 0.0 channel handle pool latc 1,235 0.0 0 0 channel operations paren 46,537 0.0 0.0 0 0 checkpoint queue latch 118,620 0.0 0 25,440 0.0 client/application info 7,573 0.0 0 0 compile environment latc 556,293 0.0 0 0 dictionary lookup 156 0.0 0 0 dml lock allocation 15,184 0.0 0 0 dummy allocation 1,397 0.0 0 0 enqueue hash chains 11,387,793 0.1 0.0 21 2,249 0.0 enqueues 8,927,704 0.1 0.0 32 0 event group latch 548 0.0 0 0 event range base latch 28 0.0 0 0 file cache latch 959 0.0 0 0 global KZLD latch for me 441 0.0 0 0 global tx hash mapping 5,759,641 0.0 0.1 1 6,939 0.0 hash table column usage 273 0.0 0 64,417 0.0 Latch Activity DB/Inst: PDXPROD/PDXPROD Snaps: 2625-2635 ->"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 ------------------------ -------------- ------ ------ ------ ------------ ------ hash table modification 155 0.0 0 0 internal temp table obje 7 0.0 0 0 job workq parent latch 0 0 326 0.0 job_queue_processes para 312 0.0 0 0 kks stats 5,317 0.0 0 0 kmcptab latch 1 0.0 0 0 kmcpvec latch 0 0 1 0.0 krbmrosl 74 0.0 0 0 ksuosstats global area 624 0.0 0 0 ktm global data 168 0.0 0 0 kwqbsn:qsga 350 0.0 0 0 lgwr LWN SCN 4,440 0.0 0 0 library cache 893,546 0.0 0.1 0 5,609 0.0 library cache load lock 1,028 0.0 0 0 library cache lock 116,349 0.0 0.0 0 0 library cache lock alloc 7,538 0.0 0 0 library cache pin 139,594 0.0 0.0 0 0 library cache pin alloca 1,036 0.0 0 0 list of block allocation 1,639 0.0 0 0 loader state object free 11,517 0.0 0 0 longop free list parent 4,739 0.0 0 13 0.0 message pool operations 658 0.2 0.0 0 0 messages 87,001 0.0 0.0 0 0 mostly latch-free SCN 4,441 0.0 0 0 multiblock read objects 524 0.0 0 0 ncodef allocation latch 141 0.0 0 0 object queue header heap 12,541 0.0 0 330 0.0 object queue header oper 133,266 0.0 0.0 0 0 object stats modificatio 9,362 0.0 0.0 0 0 parallel query alloc buf 1,160 0.0 0 0 parameter list 272 0.0 0 0 parameter table allocati 703 0.0 0 0 post/wait queue 3,159 0.0 0 1,717 0.0 process allocation 1,071 0.0 0 548 0.0 process group creation 1,071 0.0 0 0 qmn task queue latch 1,280 0.0 0 0 redo allocation 21,084 0.0 0.0 0 575,740 0.0 redo copy 0 0 575,741 0.0 redo writing 28,136 0.0 0.7 0 0 resmgr group change latc 1,671 0.0 0 0 resmgr:actses active lis 1,323 0.0 0 0 resmgr:actses change gro 660 0.0 0 0 resmgr:free threads list 1,311 0.0 0 0 resmgr:schema config 12 0.0 0 0 row cache objects 1,918,025 0.0 0.0 0 17 0.0 rules engine rule set st 1,000 0.0 0 0 sequence cache 1,673 0.0 0 0 session allocation 1,187,524 0.0 0.1 8 0 session idle bit 8,031,939 0.0 0.1 42 0 session state list latch 1,893 0.0 0 0 Latch Activity DB/Inst: PDXPROD/PDXPROD Snaps: 2625-2635 ->"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 ------------------------ -------------- ------ ------ ------ ------------ ------ session switching 141 0.0 0 0 session timer 2,972 0.0 0 0 shared pool 1,185,694 0.2 0.0 7 0 shared pool simulator 69,524 0.0 0 0 simulator hash latch 58,168,659 0.0 0 0 simulator lru latch 58,161,099 0.3 0.0 1765 5,436 0.0 slave class 43 0.0 0 0 slave class create 172 1.2 1.0 1 0 sort extent pool 399 0.0 0 0 state object free list 4 0.0 0 0 statistics aggregation 280 0.0 0 0 temp lob duration state 5 0.0 0 0 temporary table state ob 1 0.0 0 0 threshold alerts latch 658 0.0 0 0 trace latch 33 0.0 0 0 transaction allocation 13,307 0.0 0 0 transaction branch alloc 4,655,902 0.1 0.0 9 0 undo global data 40,582 0.0 0 0 user lock 2,070 0.0 0 0 ------------------------------------------------------------- Latch Sleep breakdown DB/Inst: PDXPROD/PDXPROD Snaps: 2625-2635 -> ordered by misses desc Get Spin Latch Name Requests Misses Sleeps Gets -------------------------- --------------- ------------ ----------- ----------- cache buffers chains 2,405,284,970 2,385,250 53,801 2,331,617 simulator lru latch 58,161,099 156,777 2,885 153,912 cache buffer handles 82,907,422 79,077 1,967 77,118 enqueues 8,927,704 11,438 168 11,270 enqueue hash chains 11,387,793 7,156 78 7,078 transaction branch allocat 4,655,902 3,634 56 3,578 shared pool 1,185,694 2,006 67 1,940 session idle bit 8,031,939 1,271 82 1,189 row cache objects 1,918,025 287 3 284 session allocation 1,187,524 190 23 167 global tx hash mapping 5,759,641 60 3 57 library cache 893,546 47 5 42 redo writing 28,136 11 8 3 SQL memory manager workare 748,728 3 1 2 slave class create 172 2 2 0 ------------------------------------------------------------- Latch Miss Sources DB/Inst: PDXPROD/PDXPROD Snaps: 2625-2635 -> only latches with sleeps are shown -> ordered by name, sleeps desc NoWait Waiter Latch Name Where Misses Sleeps Sleeps ------------------------ -------------------------- ------- ---------- -------- SQL memory manager worka qesmmIUnRegisterWorkArea 0 1 0 cache buffer handles kcbzgs 0 1,041 257 cache buffer handles kcbzfs 0 926 1,710 cache buffers chains kcbgtcr: fast path 0 22,966 19,432 cache buffers chains kcbgtcr: kslbegin excl 0 15,588 33,748 cache buffers chains kcbrls: kslbegin 0 15,570 989 cache buffers chains kcbchg: kslbegin: call CR 0 56 1 cache buffers chains kcbget: exchange 0 10 0 cache buffers chains kcbbic1 0 5 1 cache buffers chains kcbso1: set no access 0 4 3 cache buffers chains kcbget: exchange rls 0 1 0 cache buffers chains kcbchg: kslbegin: bufs not 0 1 1 enqueue hash chains ksqrcl 0 63 10 enqueue hash chains ksqgtl3 0 8 64 enqueue hash chains ksqcnl 0 7 4 enqueues ksqdel 0 99 24 enqueues ksqgel: create enqueue 0 39 143 enqueues ksqies 0 32 3 global tx hash mapping K2GTBlatch 0 3 3 library cache kgldte: child 0 0 4 5 library cache kglScanDependency 0 1 0 redo writing kcrfsr: rba scn pair 0 8 0 row cache objects kqreqd: reget 0 3 0 session allocation ksuxds: not user session 0 23 2 session idle bit ksupuc: set busy 0 70 32 session idle bit ksupuc: clear busy 0 8 32 session idle bit ksuxds 0 7 21 shared pool kghalo 0 38 26 shared pool kghfre 0 27 41 shared pool kghupr1 0 2 0 simulator lru latch kcbs_simulate: simulate se 0 2,880 2,884 simulator lru latch kcbs_simbufseq 0 5 1 slave class create ksvcreate 0 2 0 transaction branch alloc ksupuc 0 23 38 transaction branch alloc ktcbba 0 13 10 transaction branch alloc ktcbod 0 12 1 transaction branch alloc ktcdbr 0 7 0 transaction branch alloc ktcsbr 0 1 7 ------------------------------------------------------------- Mutex Sleep DB/Inst: PDXPROD/PDXPROD Snaps: 2625-2635 -> ordered by Wait Time desc Wait Mutex Type Location Sleeps Time (s) ------------------ -------------------------------- -------------- ------------ Cursor Pin kksfbc [KKSCHLFSP2] 63,701 46.4 Cursor Pin kkslce [KKSCHLPIN2] 22 0.9 Cursor Pin kksLockDelete [KKSCHLPIN6] 1,409 0.7 Cursor Pin kksfbc [KKSCHLPIN1] 1 0.0 ------------------------------------------------------------- Segments by Logical Reads DB/Inst: PDXPROD/PDXPROD Snaps: 2625-2635 -> End Segment Logical Reads Threshold: 10000 -> Pct Total shows % of logical reads for each top segment compared with total logical reads for all segments captured by the Snapshot Subobject Obj. Logical Pct Owner Tablespace Object Name Name Type Reads Total ---------- ---------- -------------------- ------------ ----- ------------ ----- WEBLOGIC2 WEBLOGIC_D CM_PROPERTY TABLE 788,781,968 60.3 WEBLOGIC2 WEBLOGIC_D FX1_CM_PROPERTY INDEX 465,153,280 35.5 WEBLOGIC2 WEBLOGIC_D CM_NODE TABLE 45,102,304 3.4 WEBLOGIC2 WEBLOGIC_D PK_CM_NODE INDEX 2,158,752 .2 WEBLOGIC2 WEBLOGIC_D AX1_CM_NODE INDEX 2,024,816 .2 ------------------------------------------------------------- Segments by Physical Reads DB/Inst: PDXPROD/PDXPROD Snaps: 2625-2635 -> End Segment Physical Reads Threshold: 1000 Subobject Obj. Physical Pct Owner Tablespace Object Name Name Type Reads Total ---------- ---------- -------------------- ------------ ----- ------------ ----- WEBLOGIC2 WEBLOGIC_D SYS_LOB0001911984C00 LOB 20,612 68.5 SYS SYSAUX WRH$_ACTIVE_SESSION_ 77685_9743 TABLE 1,818 6.0 SYS SYSAUX WRH$_UNDOSTAT TABLE 1,058 3.5 SYS SYSAUX WRH$_ACTIVE_SESSION_ 77685_9452 TABLE 1,048 3.5 SYS SYSAUX WRH$_ACTIVE_SESSION_ 77685_9287 TABLE 1,021 3.4 ------------------------------------------------------------- Segments by Row Lock Waits DB/Inst: PDXPROD/PDXPROD Snaps: 2625-2635 -> End Segment Row Lock Waits Threshold: 100 Row Subobject Obj. Lock Pct Owner Tablespace Object Name Name Type Waits Total ---------- ---------- -------------------- ------------ ----- ------------ ----- WEBLOGIC2 WEBLOGIC_D FX1_CM_PROPERTY INDEX 4 17.4 PERFSTAT PERFSTAT STATS$SEG_STAT_PK INDEX 4 17.4 WEBLOGIC2 WEBLOGIC_D PK_CM_NODE INDEX 4 17.4 WEBLOGIC WEBLOGIC_I FX1_CM_PROPERTY INDEX 3 13.0 WEBLOGIC WEBLOGIC_I PK_CM_NODE INDEX 3 13.0 ------------------------------------------------------------- Dictionary Cache Stats DB/Inst: PDXPROD/PDXPROD Snaps: 2625-2635 ->"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 238 0.0 0 4 1 dc_files 102 0.0 0 0 17 dc_global_oids 217 2.8 0 0 48 dc_histogram_data 9,539 0.8 0 0 4,357 dc_histogram_defs 15,141 2.2 0 3 5,542 dc_object_grants 148 24.3 0 0 94 dc_object_ids 26,672 0.6 0 1 1,427 dc_objects 5,771 1.6 0 156 1,529 dc_profiles 608 0.0 0 0 1 dc_rollback_segments 1,551 0.0 0 0 22 dc_segments 10,289 0.3 0 645 1,348 dc_sequences 30 0.0 0 30 8 dc_table_scns 52 0.0 0 0 18 dc_tablespace_quotas 547 0.0 0 479 4 dc_tablespaces 282,568 0.0 0 0 16 dc_usernames 1,319 0.2 0 0 20 dc_users 291,652 0.0 0 0 81 outstanding_alerts 240 0.0 0 0 15 ------------------------------------------------------------- Library Cache Activity DB/Inst: PDXPROD/PDXPROD Snaps: 2625-2635 ->"Pct Misses" should be very low Get Pct Pin Pct Invali- Namespace Requests Miss Requests Miss Reloads dations --------------- ------------ ------ -------------- ------ ---------- -------- BODY 1,727 0.0 26,911 0.0 0 0 CLUSTER 13 0.0 24 0.0 0 0 INDEX 192 0.0 858 0.0 0 0 SQL AREA 503 99.4 1,756,199 0.2 400 320 TABLE/PROCEDURE 1,504 5.7 37,738 1.5 174 0 TRIGGER 112 0.9 2,319 0.2 4 0 ------------------------------------------------------------- Rule Sets DB/Inst: PDXPROD/PDXPROD Snaps: 2625-2635 -> * indicates Rule Set activity (re)started between Begin/End snaps -> Top 25 ordered by Evaluations desc No-SQL SQL Rule * Eval/sec Reloads/sec Eval % Eval % ----------------------------------- - ------------ ----------- ------ ------ SYS.ALERT_QUE_R 0 0 0 0 ------------------------------------------------------------- Shared Pool Advisory DB/Inst: PDXPROD/PDXPROD End Snap: 2635 -> 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 ---------- ----- -------- ------------ ------- ------ ------- ------ ----------- 224 .3 98 14,423 ####### 1.0 ####### 37.5 55,908,995 304 .4 173 18,082 ####### 1.0 ####### 17.3 55,952,091 384 .5 252 25,647 ####### 1.0 47,942 7.3 55,960,571 464 .7 331 28,794 ####### 1.0 28,857 4.4 55,964,044 544 .8 410 32,167 ####### 1.0 19,135 2.9 55,965,903 624 .9 489 35,458 ####### 1.0 11,035 1.7 55,967,492 704 1.0 568 38,950 ####### 1.0 6,551 1.0 55,968,925 784 1.1 647 42,457 ####### 1.0 4,926 .8 55,969,989 864 1.2 726 46,047 ####### 1.0 2,767 .4 55,971,066 944 1.3 805 49,420 ####### 1.0 1 .0 55,973,044 1,024 1.5 884 52,902 ####### 1.0 1 .0 55,973,672 1,104 1.6 963 56,376 ####### 1.0 1 .0 55,975,528 1,184 1.7 1,042 59,503 ####### 1.0 1 .0 55,978,044 1,264 1.8 1,121 63,056 ####### 1.0 1 .0 55,978,716 1,344 1.9 1,200 66,813 ####### 1.0 1 .0 55,979,355 1,424 2.0 1,279 70,404 ####### 1.0 1 .0 55,979,938 ------------------------------------------------------------- SGA Target Advisory DB/Inst: PDXPROD/PDXPROD End Snap: 2635 SGA Target SGA Size Est DB Est DB Est Physical Size (M) Factor Time (s) Time Factor Reads ---------- -------- -------- ----------- -------------- 600 .5 ######## 1.0 2,762,208 900 .8 ######## 1.0 3,058,538 1,200 1.0 ######## 1.0 2,526,256 1,500 1.3 ######## 1.0 2,099,066 1,800 1.5 ######## 1.0 2,059,657 2,100 1.8 ######## 1.0 2,059,926 2,400 2.0 ######## 1.0 2,059,926 ------------------------------------------------------------- SGA Memory Summary DB/Inst: PDXPROD/PDXPROD Snaps: 2625-2635 End Size (Bytes) SGA regions Begin Size (Bytes) (if different) ------------------------------ -------------------- -------------------- Database Buffers 469,762,048 Fixed Size 2,070,840 Redo Buffers 14,704,640 Variable Size 771,753,672 -------------------- -------------------- sum 1,258,291,200 ------------------------------------------------------------- SGA breakdown difference DB/Inst: PDXPROD/PDXPROD Snaps: 2625-2635 -> 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 PX msg pool .9 .9 0.00 large free memory 15.1 15.1 0.00 shared CCursor 54.7 51.5 -5.77 shared KGLS heap 7.8 8.2 5.63 shared PCursor 39.8 37.3 -6.25 shared free memory 165.1 189.4 14.73 shared kglsim heap 7.7 7.7 0.00 shared kglsim object batch 12.5 12.5 0.00 shared library cache 35.7 34.7 -2.98 shared row cache 7.1 7.1 0.00 shared sql area 272.2 251.5 -7.60 buffer_cache 448.0 448.0 0.00 fixed_sga 2.0 2.0 0.00 log_buffer 14.0 14.0 0.00 ------------------------------------------------------------- SQL Memory Statistics DB/Inst: PDXPROD/PDXPROD Snaps: 2625-2635 Begin End % Diff -------------- -------------- -------------- Avg Cursor Size (KB): 30.93 31.29 1.13 Cursor to Parent ratio: 1.07 1.08 .85 Total Cursors: 10,160 9,271 -9.59 Total Parents: 9,530 8,622 -10.53 ------------------------------------------------------------- init.ora Parameters DB/Inst: PDXPROD/PDXPROD Snaps: 2625-2635 End value Parameter Name Begin value (if different) ----------------------------- --------------------------------- -------------- audit_file_dest /global/data/oracle/log/admin/PDX background_dump_dest /global/data/oracle/log/admin/PDX compatible 10.2.0.2.0 control_files /global/data/oracle/PDXPROD/contr core_dump_dest /global/data/oracle/log/admin/PDX db_block_size 8192 db_domain ASAP-ASP.NET db_file_multiblock_read_count 16 db_name PDXPROD db_recovery_file_dest /global/data/oracle/log/PDXPROD/f db_recovery_file_dest_size 2147483648 dispatchers (PROTOCOL=TCP) (SERVICE=PDXPRODXD job_queue_processes 10 log_archive_dest_1 LOCATION=/global/data/oracle/log/ log_archive_format %t_%s_%r.arc open_cursors 300 pga_aggregate_target 418381824 processes 400 remote_login_passwordfile EXCLUSIVE sga_target 1258291200 undo_management AUTO undo_tablespace UNDOTBS1 user_dump_dest /global/data/oracle/log/admin/PDX ------------------------------------------------------------- End of Report ( sp_2625_2635_OK.lst )