STATSPACK report for Database DB Id Instance Inst Num Startup Time Release RAC ~~~~~~~~ ----------- ------------ -------- --------------- ----------- --- 2384218455 o102ee1 1 27-Dec-06 17:34 10.2.0.3.0 NO Host Name: hpap0601 Num CPUs: 4 Phys Memory (MB): 0 ~~~~ Snapshot Snap Id Snap Time Sessions Curs/Sess Comment ~~~~~~~~ ---------- ------------------ -------- --------- ------------------- Begin Snap: 814 16-Jan-07 19:38:28 24 12.7 End Snap: 816 16-Jan-07 19:44:34 24 12.8 Elapsed: 6.10 (mins) Cache Sizes Begin End ~~~~~~~~~~~ ---------- ---------- Buffer Cache: 92M Std Block Size: 8K Shared Pool Size: 148M Log Buffer: 6,852K Load Profile Per Second Per Transaction ~~~~~~~~~~~~ --------------- --------------- Redo size: 6,679.73 46,127.92 Logical reads: 427.36 2,951.23 Block changes: 13.45 92.91 Physical reads: 345.70 2,387.26 Physical writes: 4.16 28.74 User calls: 2.13 14.74 Parses: 1.57 10.85 Hard parses: 0.00 0.00 Sorts: 1.63 11.26 Logons: 0.02 0.13 Executes: 4.54 31.32 Transactions: 0.14 % Blocks changed per Read: 3.15 Recursive Call %: 87.54 Rollback per transaction %: 7.55 Rows per Sort: 31.29 Instance Efficiency Percentages ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Buffer Nowait %: 100.00 Redo NoWait %: 100.00 Buffer Hit %: 19.11 In-memory Sort %: 100.00 Library Hit %: 100.00 Soft Parse %: 100.00 Execute to Parse %: 65.36 Latch Hit %: 100.00 Parse CPU to Parse Elapsd %: 0.00 % Non-Parse CPU: 100.00 Shared Pool Statistics Begin End ------ ------ Memory Usage %: 82.00 82.00 % SQL with executions>1: 88.18 88.25 % Memory for SQL w/exec>1: 68.35 68.37 Top 5 Timed Events Avg %Total ~~~~~~~~~~~~~~~~~~ wait Call Event Waits Time (s) (ms) Time ----------------------------------------- ------------ ----------- ------ ------ CPU time 8 48.1 control file parallel write 145 5 34 32.0 db file scattered read 3,810 2 1 12.6 log file parallel write 74 1 8 4.0 log file sync 35 0 9 2.0 ------------------------------------------------------------- Host CPU (CPUs: 4) ~~~~~~~~ Load Average Begin End User System Idle WIO WCPU ------- ------- ------- ------- ------- ------- -------- 0.01 0.15 0.52 0.21 99.25 0.79 Instance CPU ~~~~~~~~~~~~ % of total CPU for Instance: 0.54 % of busy CPU for Instance: 72.45 %DB time waiting for CPU - Resource Mgr: Memory Statistics Begin End ~~~~~~~~~~~~~~~~~ ------------ ------------ Host Mem (MB): .0 .0 SGA use (MB): 256.0 256.0 PGA use (MB): 76.3 76.0 % Host Mem used for SGA+PGA: 2042926.8 1364339.6 ------------------------------------------------------------- Time Model System Stats DB/Inst: O102EE1/o102ee1 Snaps: 814-816 -> Ordered by % of DB time desc, Statistic name Statistic Time (s) % of DB time ----------------------------------- -------------------- ------------ sql execute elapsed time 7.5 95.7 DB CPU 7.4 94.4 PL/SQL execution elapsed time 0.2 2.2 parse time elapsed 0.0 .4 connection management call elapsed 0.0 .1 repeated bind elapsed time 0.0 .0 DB time 7.8 background elapsed time 10.2 background cpu time 0.6 ------------------------------------------------------------- Wait Events DB/Inst: O102EE1/o102ee1 Snaps: 814-816 -> 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 --------------------------------- ------------ ------ ---------- ------ -------- control file parallel write 145 0 5 34 2.7 db file scattered read 3,810 0 2 1 71.9 log file parallel write 74 0 1 8 1.4 log file sync 35 0 0 9 0.7 db file sequential read 77 0 0 2 1.5 control file sequential read 594 0 0 0 11.2 SQL*Net break/reset to client 32 0 0 0 0.6 SQL*Net message from client 724 0 1,707 2358 13.7 Streams AQ: waiting for messages 74 100 361 4883 1.4 wait for unread message on broadc 366 100 358 977 6.9 Streams AQ: qmn coordinator idle 26 54 357 13748 0.5 jobq slave wait 122 95 357 2929 2.3 Streams AQ: qmn slave idle wait 12 0 330 27508 0.2 SQL*Net message to client 724 0 0 0 13.7 ------------------------------------------------------------- Background Wait Events DB/Inst: O102EE1/o102ee1 Snaps: 814-816 -> %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 145 0 5 34 2.7 log file parallel write 74 0 1 8 1.4 control file sequential read 77 0 0 0 1.5 rdbms ipc message 1,221 96 2,849 2333 23.0 pmon timer 122 100 357 2930 2.3 Streams AQ: qmn coordinator idle 26 54 357 13748 0.5 Streams AQ: qmn slave idle wait 12 0 330 27508 0.2 smon timer 1 100 293 ###### 0.0 ------------------------------------------------------------- Wait Event Histogram DB/Inst: O102EE1/o102ee1 Snaps: 814-816 -> 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 -------------------------- ----- ----- ----- ----- ----- ----- ----- ----- ----- SQL*Net break/reset to cli 32 100.0 control file parallel writ 145 4.8 12.4 82.8 control file sequential re 594 99.8 .2 db file scattered read 3810 99.9 .0 .0 .0 db file sequential read 75 66.7 1.3 6.7 17.3 8.0 direct path read 4 100.0 direct path write 4 100.0 log file parallel write 74 8.1 8.1 40.5 35.1 6.8 1.4 log file sync 35 14.3 5.7 45.7 22.9 8.6 2.9 SQL*Net message from clien 724 84.5 1.8 .6 1.1 12.0 SQL*Net message to client 724 100.0 SQL*Net more data from cli 2 100.0 Streams AQ: qmn coordinato 26 46.2 53.8 Streams AQ: qmn slave idle 12 100.0 Streams AQ: waiting for me 74 100.0 jobq slave wait 122 100.0 pmon timer 122 100.0 rdbms ipc message 1221 .9 .2 .3 .5 .5 30.8 66.7 smon timer 1 100.0 wait for unread message on 366 100.0 ------------------------------------------------------------- SQL ordered by CPU DB/Inst: O102EE1/o102ee1 Snaps: 814-816 -> Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code. -> Total DB CPU (s): 7 -> Captured SQL accounts for 99.8% 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 ---------- ------------ ---------- ------ ---------- --------------- ---------- 5.22 10 0.52 70.7 5.34 126,570 3122753897 Module: sqldeveloper.exe select ename from emp where empno = 100 0.85 1 0.85 11.5 0.91 16,395 1197340700 Module: SQL*Plus BEGIN statspack.snap(7); END; 0.64 1 0.64 8.6 0.72 3,300 2522684317 Module: SQL*Plus BEGIN statspack.snap; END; 0.18 219 0.00 2.4 0.18 876 1396250429 Module: OEM.SystemPool begin MGMT_JOB_ENGINE.get_scheduled_steps(:1, :2, :3, :4); end; ------------------------------------------------------------- SQL ordered by Elapsed DB/Inst: O102EE1/o102ee1 Snaps: 814-816 -> Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code. -> Total DB Time (s): 8 -> Captured SQL accounts for 97.5% 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 ---------- ------------ ---------- ------ ---------- --------------- ---------- 5.34 10 0.53 68.2 5.22 126,447 3122753897 Module: sqldeveloper.exe select ename from emp where empno = 100 0.91 1 0.91 11.7 0.85 36 1197340700 Module: SQL*Plus BEGIN statspack.snap(7); END; 0.72 1 0.72 9.2 0.64 38 2522684317 Module: SQL*Plus BEGIN statspack.snap; END; 0.18 219 0.00 2.3 0.18 0 1396250429 Module: OEM.SystemPool begin MGMT_JOB_ENGINE.get_scheduled_steps(:1, :2, :3, :4); end; ------------------------------------------------------------- SQL ordered by Gets DB/Inst: O102EE1/o102ee1 Snaps: 814-816 -> 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: 156,415 -> Captured SQL accounts for 90.7% 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 --------------- ------------ -------------- ------ -------- --------- ---------- 126,570 10 12,657.0 80.9 5.22 5.34 3122753897 Module: sqldeveloper.exe select ename from emp where empno = 100 16,395 1 16,395.0 10.5 0.85 0.91 1197340700 Module: SQL*Plus BEGIN statspack.snap(7); END; 11,072 1 11,072.0 7.1 0.07 0.07 4061365063 Module: SQL*Plus INSERT INTO STATS$SEG_STAT_OBJ ( TS# , OBJ# , DATAOBJ# , DBID , OWNER , OBJECT_NAME , SUBOBJECT_NAME , OBJECT_TYPE , TABLESPACE_ NAME ) SELECT /*+ ordered index(ss1) */ VS.TS# , VS.OBJ# , VS.DA TAOBJ# , :B2 , VS.OWNER , VS.OBJECT_NAME , VS.SUBOBJECT_NAME , V S.OBJECT_TYPE , VS.TABLESPACE_NAME FROM STATS$SEG_STAT SS1, V$SE 3,300 1 3,300.0 2.1 0.64 0.72 2522684317 Module: SQL*Plus BEGIN statspack.snap; END; ------------------------------------------------------------- SQL ordered by Reads DB/Inst: O102EE1/o102ee1 Snaps: 814-816 -> End Disk Reads Threshold: 1000 Total Disk Reads: 126,525 -> Captured SQL accounts for 99.9% 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 --------------- ------------ -------------- ------ -------- --------- ---------- 126,447 10 12,644.7 99.9 5.22 5.34 3122753897 Module: sqldeveloper.exe select ename from emp where empno = 100 ------------------------------------------------------------- SQL ordered by Executions DB/Inst: O102EE1/o102ee1 Snaps: 814-816 -> End Executions Threshold: 100 Total Executions: 1,660 -> Captured SQL accounts for 93.3% 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 ------------ --------------- ---------------- ----------- ---------- ---------- 225 225 1.0 0.00 0.00 1667689875 Module: EM_PING SELECT SYS_EXTRACT_UTC(SYSTIMESTAMP) FROM DUAL 219 219 1.0 0.00 0.00 1396250429 Module: OEM.SystemPool begin MGMT_JOB_ENGINE.get_scheduled_steps(:1, :2, :3, :4); end; 219 0 0.0 0.00 0.00 2360644236 Module: OEM.SystemPool SELECT JOB_ID, EXECUTION_ID, STEP_ID, STEP_NAME, STEP_TYPE, ITER ATE_PARAM, ITERATE_PARAM_INDEX, COMMAND_TYPE, TIMEZONE_REGION FR OM MGMT_JOB_EXECUTION J WHERE STEP_TYPE IN (:B7 , :B6 , :B5 ) AN D STEP_STATUS = :B4 AND COMMAND_TYPE = :B3 AND STEP_NAME LIKE :B 2 AND START_TIME <= SYS_EXTRACT_UTC(SYSTIMESTAMP) AND ROWNUM <= 115 180 1.6 0.00 0.00 4143084494 select privilege#,level from sysauth$ connect by grantee#=prior privilege# and privilege#>0 start with grantee#=:1 and privilege #>0 101 101 1.0 0.00 0.00 1938912649 Module: OEM.Loader UPDATE MGMT_CURRENT_METRICS SET COLLECTION_TIMESTAMP = :B3 , VAL UE = :B2 , STRING_VALUE = :B1 WHERE TARGET_GUID = :B6 AND METRIC _GUID = :B5 AND KEY_VALUE = :B4 AND COLLECTION_TIMESTAMP < :B3 74 6 0.1 0.00 0.00 1038353885 select job, nvl2(last_date, 1, 0) from sys.job$ where (((:1 <= n ext_date) and (next_date <= :2)) or ((last_date is null) and (next_date < :3))) and (field1 = :4 or (field1 = 0 and 'Y' = :5 )) and (this_date is null) order by next_date, job 68 68 1.0 0.00 0.00 1693927332 select count(*) from sys.job$ where (next_date > sysdate) and (n ext_date < (sysdate+5/86400)) 18 18 1.0 0.00 0.00 1103993458 Module: OEM.Loader SELECT STRING_VALUE FROM MGMT_CURRENT_METRICS WHERE TARGET_GUID = :B3 AND METRIC_GUID = :B2 AND KEY_VALUE = :B1 18 18 1.0 0.00 0.00 3521705928 Module: SELECT COUNT(*) FROM MGMT_PARAMETERS WHERE PARAMETER_NAME=:B1 AN D UPPER(PARAMETER_VALUE)='TRUE' ------------------------------------------------------------- SQL ordered by Parse Calls DB/Inst: O102EE1/o102ee1 Snaps: 814-816 -> End Parse Calls Threshold: 1000 Total Parse Calls: 575 -> Captured SQL accounts for 74.4% of Total Parse Calls -> SQL reported below exceeded 1.0% of Total Parse Calls % Total Old Parse Calls Executions Parses Hash Value ------------ ------------ -------- ---------- 115 115 20.00 4143084494 select privilege#,level from sysauth$ connect by grantee#=prior privilege# and privilege#>0 start with grantee#=:1 and privilege #>0 14 14 2.43 1566118407 declare vsn varchar2(20); begin vsn := dbms_rcvman.getPackageVersion; :p kg_vsn:pkg_vsn_i := vsn; if vsn is not null then :pkg_vsnub4 := to_number(subs tr(vsn,1,2) || substr(vsn,4,2) || s 13 13 2.26 794436051 Module: OEM.Loader SELECT INSTANTIABLE, supertype_owner, supertype_name, LOCAL_ATTR IBUTES FROM all_types WHERE type_name = :1 AND owner = :2 12 12 2.09 1206412919 Module: OEM.SystemPool BEGIN EMD_NOTIFICATION.QUEUE_READY(:1, :2, :3); END; 12 18 2.09 3521705928 Module: SELECT COUNT(*) FROM MGMT_PARAMETERS WHERE PARAMETER_NAME=:B1 AN D UPPER(PARAMETER_VALUE)='TRUE' 10 10 1.74 3122753897 Module: sqldeveloper.exe select ename from emp where empno = 100 7 7 1.22 3840591838 select privilege# from sysauth$ where (grantee#=:1 or grantee#=1 ) and privilege#>0 6 6 1.04 236131362 Module: EM_PING SELECT TGT.TARGET_GUID, TGT.EMD_URL, PING.STATUS FROM MGMT_EMD_P ING PING, MGMT_TARGETS TGT, MGMT_CURRENT_AVAILABILITY CAVAIL WHE RE PING.TARGET_GUID = TGT.TARGET_GUID AND PING.TARGET_GUID = CAV AIL.TARGET_GUID AND TGT.TARGET_TYPE = :B4 AND PING.MAX_INACTIVE_ TIME > 0 AND CAVAIL.CURRENT_STATUS != :B3 AND PING.STATUS = :B2 6 6 1.04 297937389 update sys.job$ set this_date=:1 where job=:2 6 6 1.04 718529565 SELECT C.TARGET_GUID, C.METRIC_GUID, C.STORE_METRIC, C.SCHEDULE, C.COLL_NAME, M.METRIC_NAME, M.EVAL_FUNC FROM MGMT_METRIC_COLLEC TIONS_REP R, MGMT_METRIC_COLLECTIONS C, MGMT_METRICS M WHERE C.S USPENDED = 0 AND C.IS_REPOSITORY = 1 AND (C.LAST_COLLECTED_TIMES TAMP IS NULL OR C.LAST_COLLECTED_TIMESTAMP + C.SCHEDULE / 1440 < SQL ordered by Parse Calls DB/Inst: O102EE1/o102ee1 Snaps: 814-816 -> End Parse Calls Threshold: 1000 Total Parse Calls: 575 -> Captured SQL accounts for 74.4% of Total Parse Calls -> SQL reported below exceeded 1.0% of Total Parse Calls % Total Old Parse Calls Executions Parses Hash Value ------------ ------------ -------- ---------- 6 6 1.04 884862163 Module: EM_PING DELETE FROM MGMT_JOB_EMD_STATUS_QUEUE 6 6 1.04 923291638 select sysdate + 1 / (24 * 60) from dual 6 11 1.04 1200253560 INSERT INTO MGMT_SYSTEM_PERFORMANCE_LOG (JOB_NAME, TIME, DURATIO N, MODULE, ACTION, IS_TOTAL, NAME, VALUE, CLIENT_DATA, HOST_URL) VALUES (:B9 , SYSDATE, :B8 , SUBSTR(:B7 , 1, 512), SUBSTR(:B6 , 1,32), :B5 , SUBSTR(:B4 ,1,128), SUBSTR(:B3 ,1,128), SUBSTR(:B2 ,1,128), SUBSTR(:B1 ,1,256)) 6 6 1.04 1651265719 Module: EM_PING SELECT COUNT(FAILOVER_ID) FROM MGMT_FAILOVER_TABLE WHERE SYSDATE -LAST_TIME_STAMP < :B1 /(24*60*60) 6 6 1.04 1666200974 Module: OEM.SystemPool BEGIN EM_PING.RECORD_BATCH_HEARTBEAT(:1, :2, :3); END; 6 225 1.04 1667689875 Module: EM_PING SELECT SYS_EXTRACT_UTC(SYSTIMESTAMP) FROM DUAL 6 6 1.04 1794066809 Module: SEVERITY EVALUATION SELECT UNIQUE(TARGET_GUID) FROM MGMT_METRIC_DEPENDENCY WHERE CAN _CALCULATE = 1 AND DISABLED = 0 6 6 1.04 2051271524 select u1.user#, u2.user#, u3.user#, failures, flag, interval#, what, nlsenv, env, field1 from sys.job$ j, sys.user$ u1, sys .user$ u2, sys.user$ u3 where job=:1 and (next_date <= sysdate or :2 != 0) and lowner = u1.name and powner = u2.name and cowne r = u3.name 6 6 1.04 2091416805 Module: EM_PING SELECT SYS_GUID() FROM SYS.DUAL 6 6 1.04 2100170746 Module: EM_PING SELECT EMD_URL, EVENT_TYPE, OCCUR_TIME FROM MGMT_JOB_EMD_STATUS_ QUEUE ORDER BY OCCUR_TIME 6 6 1.04 2439780263 Module: EM_PING UPDATE MGMT_EMD_PING SET LAST_CHECKED_UTC = :B1 WHERE MAX_INACTI VE_TIME > 0 AND ( (STATUS = :B3 ) OR (STATUS = :B2 ) ) AND (:B1 SQL ordered by Parse Calls DB/Inst: O102EE1/o102ee1 Snaps: 814-816 -> End Parse Calls Threshold: 1000 Total Parse Calls: 575 -> Captured SQL accounts for 74.4% of Total Parse Calls -> SQL reported below exceeded 1.0% of Total Parse Calls % Total Old Parse Calls Executions Parses Hash Value ------------ ------------ -------- ---------- - LAST_CHECKED_UTC)*24*60*60 >= (MAX_INACTIVE_TIME)/2 6 6 1.04 2561692322 Module: EM_PING SELECT COUNT(*) FROM MGMT_FAILOVER_TABLE WHERE SYSDATE-LAST_TIME _STAMP > (HEARTBEAT_INTERVAL*4)/(24*60*60) 6 6 1.04 2603722335 Module: EM_PING SELECT /*+ RULE */ STEP_ID FROM MGMT_JOB_EXECUTION E, MGMT_JOB J WHERE E.JOB_ID=J.JOB_ID AND STEP_STATUS IN (:B6 , :B5 , :B4 , : B3 , :B2 ) AND STEP_TYPE=:B1 AND J.EXECUTION_TIMEOUT > 0 AND (CA ST(SYS_EXTRACT_UTC(SYSTIMESTAMP) AS DATE)-E.START_TIME) > (J.EXE CUTION_TIMEOUT/24) 6 6 1.04 2689373535 DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate; broken BOOLEAN := FALSE; BEGIN EMD_MAINTENANCE.EXECUTE_EM_DBMS_J OB_PROCS(); :mydate := next_date; IF broken THEN :b := 1; ELSE : b := 0; END IF; END; 6 6 1.04 2921587358 Module: EM_PING SELECT /*+ RULE */ STATUS, EXECUTION_ID FROM MGMT_JOB_EXEC_SUMMA RY E WHERE STATUS IN (:B2 , :B1 ) AND SUSPEND_TIMEOUT > 0 AND (C ------------------------------------------------------------- Instance Activity Stats DB/Inst: O102EE1/o102ee1 Snaps: 814-816 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ CPU used by this session 750 2.1 14.2 CPU used when call started 738 2.0 13.9 CR blocks created 12 0.0 0.2 Cached Commit SCN referenced 0 0.0 0.0 Commit SCN cached 0 0.0 0.0 DB time 110,127 300.9 2,077.9 DBWR checkpoint buffers written 1,423 3.9 26.9 DBWR checkpoints 0 0.0 0.0 DBWR object drop buffers written 0 0.0 0.0 DBWR transaction table writes 10 0.0 0.2 DBWR undo block writes 469 1.3 8.9 IMU CR rollbacks 12 0.0 0.2 IMU Flushes 93 0.3 1.8 IMU Redo allocation size 72,736 198.7 1,372.4 IMU commits 46 0.1 0.9 IMU contention 0 0.0 0.0 IMU ktichg flush 0 0.0 0.0 IMU pool not allocated 0 0.0 0.0 IMU recursive-transaction flush 0 0.0 0.0 IMU undo allocation size 158,740 433.7 2,995.1 IMU- failed to get a private stra 0 0.0 0.0 Misses for writing mapping 0 0.0 0.0 SMON posted for undo segment shri 0 0.0 0.0 SQL*Net roundtrips to/from client 721 2.0 13.6 active txn count during cleanout 141 0.4 2.7 application wait time 1 0.0 0.0 background checkpoints completed 1 0.0 0.0 background checkpoints started 0 0.0 0.0 background timeouts 1,169 3.2 22.1 branch node splits 0 0.0 0.0 buffer is not pinned count 14,975 40.9 282.6 buffer is pinned count 7,482 20.4 141.2 bytes received via SQL*Net from c 75,174 205.4 1,418.4 bytes sent via SQL*Net to client 71,106 194.3 1,341.6 calls to get snapshot scn: kcmgss 2,929 8.0 55.3 calls to kcmgas 309 0.8 5.8 calls to kcmgcs 65 0.2 1.2 change write time 4 0.0 0.1 cleanout - number of ktugct calls 167 0.5 3.2 cleanouts and rollbacks - consist 0 0.0 0.0 cleanouts only - consistent read 0 0.0 0.0 cluster key scan block gets 6,722 18.4 126.8 cluster key scans 3,068 8.4 57.9 commit batch performed 0 0.0 0.0 commit batch requested 0 0.0 0.0 commit batch/immediate performed 5 0.0 0.1 commit batch/immediate requested 5 0.0 0.1 commit cleanout failures: block l 0 0.0 0.0 commit cleanout failures: buffer 0 0.0 0.0 commit cleanout failures: callbac 19 0.1 0.4 commit cleanout failures: cannot 0 0.0 0.0 commit cleanouts 591 1.6 11.2 commit cleanouts successfully com 572 1.6 10.8 commit immediate performed 5 0.0 0.1 commit immediate requested 5 0.0 0.1 commit txn count during cleanout 83 0.2 1.6 Instance Activity Stats DB/Inst: O102EE1/o102ee1 Snaps: 814-816 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ concurrency wait time 0 0.0 0.0 consistent changes 12 0.0 0.2 consistent gets 151,077 412.8 2,850.5 consistent gets - examination 6,181 16.9 116.6 consistent gets direct 0 0.0 0.0 consistent gets from cache 151,077 412.8 2,850.5 cursor authentications 1 0.0 0.0 data blocks consistent reads - un 12 0.0 0.2 db block changes 4,924 13.5 92.9 db block gets 5,338 14.6 100.7 db block gets direct 0 0.0 0.0 db block gets from cache 5,338 14.6 100.7 deferred (CURRENT) block cleanout 389 1.1 7.3 dirty buffers inspected 21 0.1 0.4 enqueue conversions 165 0.5 3.1 enqueue releases 3,470 9.5 65.5 enqueue requests 3,470 9.5 65.5 enqueue timeouts 0 0.0 0.0 enqueue waits 0 0.0 0.0 execute count 1,660 4.5 31.3 failed probes on index block recl 0 0.0 0.0 free buffer inspected 126,653 346.1 2,389.7 free buffer requested 126,695 346.2 2,390.5 heap block compress 1 0.0 0.0 hot buffers moved to head of LRU 133 0.4 2.5 immediate (CR) block cleanout app 0 0.0 0.0 immediate (CURRENT) block cleanou 157 0.4 3.0 index crx upgrade (found) 0 0.0 0.0 index crx upgrade (positioned) 900 2.5 17.0 index fast full scans (full) 4 0.0 0.1 index fetch by key 6,730 18.4 127.0 index scans kdiixs1 1,896 5.2 35.8 leaf node 90-10 splits 4 0.0 0.1 leaf node splits 55 0.2 1.0 lob reads 0 0.0 0.0 lob writes 0 0.0 0.0 lob writes unaligned 0 0.0 0.0 logons cumulative 7 0.0 0.1 messages received 514 1.4 9.7 messages sent 514 1.4 9.7 no buffer to keep pinned count 0 0.0 0.0 no work - consistent read gets 143,264 391.4 2,703.1 opened cursors cumulative 557 1.5 10.5 parse count (failures) 0 0.0 0.0 parse count (hard) 0 0.0 0.0 parse count (total) 575 1.6 10.9 parse time cpu 0 0.0 0.0 parse time elapsed 1 0.0 0.0 physical read IO requests 3,889 10.6 73.4 physical read bytes 1,036,492,800 2,831,947.5 ############ physical read total IO requests 4,483 12.3 84.6 physical read total bytes 1,046,224,896 2,858,538.0 ############ physical read total multi block r 3,810 10.4 71.9 physical reads 126,525 345.7 2,387.3 physical reads cache 126,521 345.7 2,387.2 physical reads cache prefetch 122,636 335.1 2,313.9 Instance Activity Stats DB/Inst: O102EE1/o102ee1 Snaps: 814-816 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ physical reads direct 4 0.0 0.1 physical reads direct (lob) 0 0.0 0.0 physical reads direct temporary t 0 0.0 0.0 physical reads prefetch warmup 0 0.0 0.0 physical write IO requests 485 1.3 9.2 physical write bytes 12,476,416 34,088.6 235,404.1 physical write total IO requests 994 2.7 18.8 physical write total bytes 22,191,616 60,632.8 418,709.7 physical write total multi block 226 0.6 4.3 physical writes 1,523 4.2 28.7 physical writes direct 4 0.0 0.1 physical writes direct (lob) 0 0.0 0.0 physical writes direct temporary 0 0.0 0.0 physical writes from cache 1,519 4.2 28.7 physical writes non checkpoint 1,212 3.3 22.9 pinned buffers inspected 0 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 0 0.0 0.0 recursive calls 5,489 15.0 103.6 recursive cpu usage 169 0.5 3.2 redo blocks written 5,055 13.8 95.4 redo buffer allocation retries 0 0.0 0.0 redo entries 2,787 7.6 52.6 redo log space requests 0 0.0 0.0 redo log space wait time 0 0.0 0.0 redo ordering marks 164 0.5 3.1 redo size 2,444,780 6,679.7 46,127.9 redo subscn max counts 250 0.7 4.7 redo synch time 32 0.1 0.6 redo synch writes 374 1.0 7.1 redo wastage 23,544 64.3 444.2 redo write time 65 0.2 1.2 redo writer latching time 0 0.0 0.0 redo writes 74 0.2 1.4 rollback changes - undo records a 6 0.0 0.1 rollbacks only - consistent read 12 0.0 0.2 rows fetched via callback 1,782 4.9 33.6 session connect time 0 0.0 0.0 session cursor cache hits 208 0.6 3.9 session logical reads 156,415 427.4 2,951.2 session pga memory 6,591,992 18,010.9 124,377.2 session pga memory max 36,541,944 99,841.4 689,470.6 session uga memory 25,769,768,312 70,409,203.0 ############ session uga memory max 4,630,028 12,650.4 87,359.0 shared hash latch upgrades - no w 999 2.7 18.9 shared hash latch upgrades - wait 0 0.0 0.0 sorts (disk) 0 0.0 0.0 sorts (memory) 597 1.6 11.3 sorts (rows) 18,679 51.0 352.4 sql area evicted 0 0.0 0.0 sql area purged 0 0.0 0.0 summed dirty queue length 38 0.1 0.7 switch current to new buffer 16 0.0 0.3 table fetch by rowid 4,276 11.7 80.7 table fetch continued row 2 0.0 0.0 Instance Activity Stats DB/Inst: O102EE1/o102ee1 Snaps: 814-816 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ table scan blocks gotten 126,875 346.7 2,393.9 table scan rows gotten 20,976,052 57,311.6 395,774.6 table scans (long tables) 10 0.0 0.2 table scans (short tables) 178 0.5 3.4 total number of times SMON posted 0 0.0 0.0 transaction rollbacks 5 0.0 0.1 undo change vector size 868,780 2,373.7 16,392.1 user I/O wait time 212 0.6 4.0 user calls 781 2.1 14.7 user commits 49 0.1 0.9 user rollbacks 4 0.0 0.1 workarea executions - onepass 0 0.0 0.0 workarea executions - optimal 318 0.9 6.0 write clones created in backgroun 0 0.0 0.0 write clones created in foregroun 0 0.0 0.0 ------------------------------------------------------------- Instance Activity Stats DB/Inst: O102EE1/o102ee1 Snaps: 814-816 -> Statistics with absolute values (should not be diffed) Statistic Begin Value End Value --------------------------------- --------------- --------------- logons current 24 24 opened cursors current 305 307 session cursor cache count 576,476 576,578 ------------------------------------------------------------- Instance Activity Stats DB/Inst: O102EE1/o102ee1 Snaps: 814-816 -> Statistics identified by '(derived)' come from sources other than SYSSTAT Statistic Total per Hour --------------------------------- ------------------ --------- log switches (derived) 0 .00 ------------------------------------------------------------- OS Statistics DB/Inst: O102EE1/o102ee1 Snaps: 814-816 -> ordered by statistic type (CPU use, Virtual Memory, Hardware Config), Name Statistic Total ------------------------- ---------------------- BUSY_TIME 1,098 IDLE_TIME 145,356 IOWAIT_TIME 1,160 NICE_TIME 4 SYS_TIME 313 USER_TIME 766 LOAD 0 PHYSICAL_MEMORY_BYTES 25,520 NUM_CPUS 4 NUM_CPU_SOCKETS 2 ------------------------------------------------------------- Tablespace IO Stats DB/Inst: O102EE1/o102ee1 Snaps: 814-816 ->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) -------------- ------- ------ ------- ------------ -------- ---------- ------ USERS 3,812 10 0.5 33.2 1 0 0 0.0 SYSAUX 75 0 2.5 1.0 430 1 0 0.0 UNDOTBS1 1 0 0.0 1.0 50 0 0 0.0 SYSTEM 1 0 0.0 1.0 4 0 0 0.0 ------------------------------------------------------------- File IO Stats DB/Inst: O102EE1/o102ee1 Snaps: 814-816 ->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) -------------- ------- ----- --- ------- ------------ -------- ---------- ------ SYSAUX /opt/app/oracle/oradata/o102ee1/sysaux01.dbf 75 0 2.5 16 1.0 430 1 0 SYSTEM /opt/app/oracle/oradata/o102ee1/system01.dbf 1 0 0.0 1.0 4 0 0 UNDOTBS1 /opt/app/oracle/oradata/o102ee1/undotbs01.dbf 1 0 0.0 1.0 50 0 0 USERS /opt/app/oracle/oradata/o102ee1/users01.dbf 3,812 10 0.5 1 33.2 1 0 0 ------------------------------------------------------------- File Read Histogram Stats DB/Inst: O102EE1/o102ee1 Snaps: 814-816 ->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 ------------ ------------ ------------ ------------ ------------ ------------ SYSAUX /opt/app/oracle/oradata/o102ee1/sysaux01.dbf 49 5 15 5 0 0 USERS /opt/app/oracle/oradata/o102ee1/users01.dbf 1 0 0 0 0 0 ------------------------------------------------------------- Buffer Pool Statistics DB/Inst: O102EE1/o102ee1 Snaps: 814-816 -> 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 11K 19 156,498 126,525 1,519 0 0 0 ------------------------------------------------------------- Instance Recovery Stats DB/Inst: O102EE1/o102ee1 Snaps: 814-816 -> 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 10 1421 16482 17553 184320 17553 E 0 9 458 3537 22124 184320 22124 ------------------------------------------------------------- Buffer Pool Advisory DB/Inst: O102EE1/o102ee1 End Snap: 816 -> 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 8 .1 1 1.1 42,153 1,962 19.5 D 16 .2 2 1.0 41,093 1,816 18.1 D 24 .3 3 1.0 40,645 1,754 17.4 D 32 .3 4 1.0 40,186 1,691 16.8 D 40 .4 5 1.0 39,665 1,619 16.1 D 48 .5 6 1.0 39,498 1,596 15.9 D 56 .6 7 1.0 39,446 1,589 15.8 D 64 .7 8 1.0 39,405 1,583 15.7 D 72 .8 9 1.0 39,359 1,577 15.7 D 80 .9 10 1.0 39,327 1,572 15.6 D 88 1.0 11 1.0 39,295 1,568 15.6 D 92 1.0 11 1.0 39,285 1,567 15.6 D 96 1.0 12 1.0 39,275 1,565 15.6 D 104 1.1 13 1.0 39,260 1,563 15.5 D 112 1.2 14 1.0 39,244 1,561 15.5 D 120 1.3 15 1.0 39,229 1,559 15.5 D 128 1.4 16 1.0 39,215 1,557 15.5 D 136 1.5 17 1.0 39,199 1,555 15.5 D 144 1.6 18 1.0 39,188 1,553 15.4 D 152 1.7 19 1.0 39,176 1,552 15.4 D 160 1.7 20 0.8 32,344 1,285 12.8 ------------------------------------------------------------- PGA Aggr Target Stats DB/Inst: O102EE1/o102ee1 Snaps: 814-816 -> 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 27 0 Warning: pga_aggregate_target was set too low for current workload, as this value was exceeded during this interval. Use the PGA Advisory view to help identify a different value for pga_aggregate_target. %PGA %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 16 4 76.3 0.0 .0 .0 .0 3,276 E 16 4 76.0 0.0 .0 .0 .0 3,276 ------------------------------------------------------------- PGA Aggr Target Histogram DB/Inst: O102EE1/o102ee1 Snaps: 814-816 -> Optimal Executions are purely in-memory operations Low High Optimal Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs ------- ------- -------------- ------------- ------------ ------------ 2K 4K 276 276 0 0 64K 128K 4 4 0 0 128K 256K 2 2 0 0 512K 1024K 36 36 0 0 ------------------------------------------------------------- PGA Memory Advisory DB/Inst: O102EE1/o102ee1 End Snap: 816 -> 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 ---------- ------- ---------------- ---------------- -------- ---------- 12 0.8 18,523.1 560.5 97.0 120 16 1.0 18,523.1 13.8 100.0 120 19 1.2 18,523.1 13.8 100.0 120 22 1.4 18,523.1 13.8 100.0 120 26 1.6 18,523.1 13.8 100.0 119 29 1.8 18,523.1 13.8 100.0 118 32 2.0 18,523.1 13.8 100.0 106 48 3.0 18,523.1 0.0 100.0 10 64 4.0 18,523.1 0.0 100.0 0 96 6.0 18,523.1 0.0 100.0 0 128 8.0 18,523.1 0.0 100.0 0 ------------------------------------------------------------- Process Memory Summary Stats DB/Inst: O102EE1/o102ee1 Snaps: 814-816 -> 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 -------- 76.3 41.8 9.1 3.2 5.4 22 22 24 Other 65.7 2.7 5.3 22 22 24 Freeable 9.1 .0 .8 .4 1 12 SQL 1.0 .4 .1 .1 0 8 15 PL/SQL .5 .2 .0 .0 0 0 24 E -------- 76.1 41.6 9.3 3.2 5.4 22 22 24 Other 65.2 2.7 5.3 22 22 24 Freeable 9.3 .0 .8 .5 2 12 SQL 1.0 .4 .1 .1 0 8 15 PL/SQL .5 .2 .0 .0 0 0 24 ------------------------------------------------------------- Top Process Memory (by component) DB/Inst: O102EE1/o102ee1 Snaps: 814-816 -> 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 -------- 21.8 10.3 .0 21.8 21.8 Other 21.8 21.8 21.8 PL/SQL .0 .0 .0 .0 5 DBW0 -------- 13.7 6.4 .1 13.7 15.4 Other 13.6 13.6 15.3 Freeable .1 .0 .1 PL/SQL .0 .0 .0 .0 20 ------------ 13.4 11.8 1.3 13.4 13.4 Other 11.9 11.9 11.9 Freeable 1.3 .0 1.3 SQL .1 .0 .1 2.2 PL/SQL .1 .0 .1 .1 22 TNS V1-V3 --- 4.3 2.4 1.1 4.3 6.7 Other 3.1 3.1 3.8 Freeable 1.1 .0 1.1 SQL .1 .1 .1 1.8 PL/SQL .0 .0 .0 .0 11 MMON -------- 2.7 1.3 .9 2.7 3.1 Other 1.7 1.7 1.7 Freeable .9 .0 .9 PL/SQL .1 .0 .1 .1 SQL .0 .0 .0 .8 23 ------------ 2.1 1.0 .8 2.1 13.9 Other 1.3 1.3 4.8 Freeable .8 .0 .8 PL/SQL .0 .0 .0 .0 SQL .0 .0 .0 8.3 8 SMON -------- 2.1 .5 1.4 2.1 2.2 Freeable 1.4 .0 1.4 Other .7 .7 .7 SQL .0 .0 .0 1.2 PL/SQL .0 .0 .0 .0 19 ------------ 1.9 1.4 .4 1.9 2.9 Other 1.3 1.3 1.3 Freeable .4 .0 .4 SQL .1 .0 .1 1.4 PL/SQL .1 .0 .1 .1 13 q000 -------- 1.8 .6 .8 1.8 1.8 Other .9 .9 .9 Freeable .8 .0 .8 SQL .1 .1 .1 .9 PL/SQL .0 .0 .0 .0 15 J000 -------- 1.5 .1 1.1 1.5 6.2 Freeable 1.1 .0 1.1 Other .3 .3 2.1 PL/SQL .1 .0 .1 .1 SQL .0 .0 .0 2.9 10 CJQ0 -------- 1.5 .6 .6 1.5 1.9 Other .8 .8 .8 Freeable .6 .0 .6 SQL .0 .0 .0 .9 PL/SQL .0 .0 .0 .0 7 CKPT -------- 1.5 .3 .0 1.5 1.5 Top Process Memory (by component) DB/Inst: O102EE1/o102ee1 Snaps: 814-816 -> ordered by Begin/End snapshot, Alloc (MB) desc Alloc Used Freeabl Max Hist Max PId Category (MB) (MB) (MB) Alloc (MB) Alloc (MB) - ------ ------------- ------- ------- -------- ---------- ---------- B 7 Other 1.4 1.4 1.4 SQL .0 .0 .0 .0 PL/SQL .0 .0 .0 .0 24 ------------ 1.3 1.2 .0 1.3 3.4 Other 1.2 1.2 1.6 SQL .1 .0 .1 1.7 PL/SQL .0 .0 .0 .0 21 ------------ 1.2 1.2 .0 1.2 2.3 Other 1.0 1.0 1.8 SQL .1 .1 .1 .5 PL/SQL .0 .0 .0 .0 17 ------------ 1.1 .4 .4 1.1 1.1 Other .6 .6 .6 Freeable .4 .0 .4 SQL .1 .0 .1 .5 PL/SQL .0 .0 .0 .0 9 RECO -------- 1.0 .4 .2 1.0 1.0 Other .8 .8 .8 Freeable .2 .0 .2 SQL .1 .0 .1 .5 PL/SQL .0 .0 .0 .0 E 6 LGWR -------- 21.8 10.3 .0 21.8 21.8 Other 21.8 21.8 21.8 PL/SQL .0 .0 .0 .0 5 DBW0 -------- 13.7 6.4 .1 13.7 15.4 Other 13.6 13.6 15.3 Freeable .1 .0 .1 PL/SQL .0 .0 .0 .0 20 ------------ 13.4 11.6 1.6 13.4 13.4 Other 11.6 11.6 11.6 Freeable 1.6 .0 1.6 SQL .1 .0 .1 2.2 PL/SQL .1 .0 .1 .1 22 TNS V1-V3 --- 4.2 2.5 .9 4.2 6.7 Other 3.1 3.1 3.9 Freeable .9 .0 .9 SQL .1 .1 .1 1.8 PL/SQL .0 .0 .0 .0 11 MMON -------- 2.7 1.3 .9 2.7 3.1 Other 1.7 1.7 1.7 Freeable .9 .0 .9 PL/SQL .1 .0 .1 .1 SQL .0 .0 .0 .8 8 SMON -------- 2.1 .5 1.4 2.1 2.2 Freeable 1.4 .0 1.4 Other .7 .7 .7 SQL .0 .0 .0 1.2 PL/SQL .0 .0 .0 .0 23 ------------ 2.0 1.0 .9 2.0 13.9 Other 1.0 1.0 4.7 Freeable .9 .0 .9 PL/SQL .0 .0 .0 .0 SQL .0 .0 .0 8.3 Top Process Memory (by component) DB/Inst: O102EE1/o102ee1 Snaps: 814-816 -> ordered by Begin/End snapshot, Alloc (MB) desc Alloc Used Freeabl Max Hist Max PId Category (MB) (MB) (MB) Alloc (MB) Alloc (MB) - ------ ------------- ------- ------- -------- ---------- ---------- E 19 ------------ 1.9 1.4 .4 1.9 2.9 Other 1.3 1.3 1.3 Freeable .4 .0 .4 SQL .1 .0 .1 1.4 PL/SQL .1 .0 .1 .1 13 q000 -------- 1.8 .6 .8 1.8 1.8 Other .9 .9 .9 Freeable .8 .0 .8 SQL .1 .1 .1 .9 PL/SQL .0 .0 .0 .0 15 J000 -------- 1.5 .1 1.1 1.5 6.2 Freeable 1.1 .0 1.1 Other .3 .3 2.1 PL/SQL .1 .0 .1 .1 SQL .0 .0 .0 2.9 10 CJQ0 -------- 1.5 .6 .6 1.5 1.9 Other .8 .8 .8 Freeable .6 .0 .6 SQL .0 .0 .0 .9 PL/SQL .0 .0 .0 .0 7 CKPT -------- 1.5 .3 .0 1.5 1.5 Other 1.4 1.4 1.4 SQL .0 .0 .0 .0 PL/SQL .0 .0 .0 .0 24 ------------ 1.3 1.2 .0 1.3 3.4 Other 1.2 1.2 1.6 SQL .1 .0 .1 1.7 PL/SQL .0 .0 .0 .0 21 ------------ 1.2 1.2 .0 1.2 2.3 Other 1.0 1.0 1.8 SQL .1 .1 .1 .5 PL/SQL .0 .0 .0 .0 17 ------------ 1.1 .4 .4 1.1 1.1 Other .6 .6 .6 Freeable .4 .0 .4 SQL .1 .0 .1 .5 PL/SQL .0 .0 .0 .0 9 RECO -------- 1.0 .4 .2 1.0 1.0 Other .8 .8 .8 Freeable .2 .0 .2 SQL .1 .0 .1 .5 PL/SQL .0 .0 .0 .0 ------------------------------------------------------------- Latch Activity DB/Inst: O102EE1/o102ee1 Snaps: 814-816 ->"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 db client latch 241 0.0 0 0 Consistent RBA 74 0.0 0 0 In memory undo latch 963 0.0 0 232 0.0 JS queue state obj latch 2,190 0.0 0 0 KMG MMAN ready and start 122 0.0 0 0 KTF sga latch 1 0.0 0 105 0.0 KWQP Prop Status 2 0.0 0 0 MQL Tracking Latch 0 0 7 0.0 Memory Management Latch 0 0 122 0.0 OS process 3 0.0 0 0 OS process allocation 124 0.0 0 0 OS process: request allo 2 0.0 0 0 PL/SQL warning settings 66 0.0 0 0 SQL memory manager latch 93 0.0 0 120 0.0 SQL memory manager worka 14,465 0.0 0 0 Shared B-Tree 19 0.0 0 0 active checkpoint queue 586 0.0 0 0 active service list 687 0.0 0 122 0.0 archive control 5 0.0 0 0 cache buffer handles 56 0.0 0 0 cache buffers chains 448,797 0.0 0 249,378 0.0 cache buffers lru chain 3,073 0.0 0 253,206 0.0 cache table scan latch 3,810 0.0 0 3,810 0.0 channel handle pool latc 2 0.0 0 0 channel operations paren 2,146 0.0 0 0 checkpoint queue latch 9,278 0.0 0 505 0.0 client/application info 154 0.0 0 0 compile environment latc 307 0.0 0 0 dml lock allocation 1,626 0.0 0 0 dummy allocation 14 0.0 0 0 enqueue hash chains 7,103 0.0 0 0 enqueues 5,393 0.0 0 0 event group latch 1 0.0 0 0 file cache latch 10 0.0 0 0 global KZLD latch for me 1 0.0 0 0 job workq parent latch 0 0 12 0.0 job_queue_processes para 12 0.0 0 0 ksuosstats global area 29 0.0 0 0 ktm global data 1 0.0 0 0 kwqbsn:qsga 19 0.0 0 0 lgwr LWN SCN 154 0.0 0 0 library cache 10,610 0.0 0 0 library cache lock 3,906 0.0 0 0 library cache lock alloc 134 0.0 0 0 library cache pin 5,064 0.0 0 0 library cache pin alloca 28 0.0 0 0 list of block allocation 45 0.0 0 0 loader state object free 12 0.0 0 0 messages 3,429 0.0 0 0 mostly latch-free SCN 154 0.0 0 0 Latch Activity DB/Inst: O102EE1/o102ee1 Snaps: 814-816 ->"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 ------------------------ -------------- ------ ------ ------ ------------ ------ multiblock read objects 7,622 0.0 0 0 ncodef allocation latch 6 0.0 0 0 object queue header heap 1,169 0.0 0 23 0.0 object queue header oper 258,165 0.0 0 0 object stats modificatio 722 0.0 0 0 parallel query alloc buf 48 0.0 0 0 parameter table allocati 7 0.0 0 0 post/wait queue 98 0.0 0 35 0.0 process allocation 2 0.0 0 1 0.0 process group creation 2 0.0 0 0 qmn task queue latch 48 0.0 0 0 redo allocation 842 0.0 0 2,785 0.0 redo copy 0 0 2,785 0.0 redo writing 1,060 0.0 0 0 resmgr group change latc 38 0.0 0 0 resmgr:actses active lis 16 0.0 0 0 resmgr:actses change gro 7 0.0 0 0 resmgr:free threads list 14 0.0 0 0 resmgr:schema config 2 0.0 0 0 row cache objects 5,655 0.0 0 0 rules engine rule set st 200 0.0 0 0 sequence cache 9 0.0 0 0 session allocation 799 0.0 0 0 session idle bit 1,862 0.0 0 0 session state list latch 12 0.0 0 0 session switching 6 0.0 0 0 session timer 122 0.0 0 0 shared pool 688 0.0 0 0 shared pool simulator 204 0.0 0 0 simulator hash latch 42,548 0.0 0 0 simulator lru latch 18,830 0.0 0 7,910 0.0 sort extent pool 11 0.0 0 0 threshold alerts latch 31 0.0 0 0 transaction allocation 12 0.0 0 0 transaction branch alloc 6 0.0 0 0 undo global data 940 0.0 0 0 user lock 16 0.0 0 0 ------------------------------------------------------------- Segments by Logical Reads DB/Inst: O102EE1/o102ee1 Snaps: 814-816 -> 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 ---------- ---------- -------------------- ------------ ----- ------------ ----- SCOTT USERS EMP TABLE 126,592 86.1 SYS SYSAUX SYS_IOT_TOP_8813 INDEX 5,264 3.6 SYS SYSTEM TS$ TABLE 3,776 2.6 SYS SYSTEM USER$ TABLE 2,944 2.0 SYS SYSTEM I_OBJ1 INDEX 1,392 .9 ------------------------------------------------------------- Segments by Physical Reads DB/Inst: O102EE1/o102ee1 Snaps: 814-816 -> End Segment Physical Reads Threshold: 1000 Subobject Obj. Physical Pct Owner Tablespace Object Name Name Type Reads Total ---------- ---------- -------------------- ------------ ----- ------------ ----- SCOTT USERS EMP TABLE 126,447 100.0 ------------------------------------------------------------- Dictionary Cache Stats DB/Inst: O102EE1/o102ee1 Snaps: 814-816 ->"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 7 0.0 0 0 1 dc_global_oids 506 0.0 0 0 47 dc_object_ids 506 0.0 0 0 810 dc_objects 8 0.0 0 0 931 dc_profiles 7 0.0 0 0 1 dc_rollback_segments 33 0.0 0 0 12 dc_segments 1 0.0 0 0 771 dc_tablespaces 108 0.0 0 0 6 dc_usernames 2 0.0 0 0 8 dc_users 708 0.0 0 0 37 outstanding_alerts 12 0.0 0 0 10 ------------------------------------------------------------- Library Cache Activity DB/Inst: O102EE1/o102ee1 Snaps: 814-816 ->"Pct Misses" should be very low Get Pct Pin Pct Invali- Namespace Requests Miss Requests Miss Reloads dations --------------- ------------ ------ -------------- ------ ---------- -------- BODY 53 0.0 615 0.0 0 0 TABLE/PROCEDURE 6 0.0 904 0.0 0 0 TRIGGER 2 0.0 21 0.0 0 0 ------------------------------------------------------------- Rule Sets DB/Inst: O102EE1/o102ee1 Snaps: 814-816 -> * 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: O102EE1/o102ee1 End Snap: 816 -> 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 ---------- ----- -------- ------------ ------- ------ ------- ------ ----------- 84 .6 18 2,137 ####### .8 86,375 ##### 15,616,119 100 .7 31 2,654 ####### .9 57,920 ##### 15,636,298 116 .8 46 4,379 ####### .9 30,705 63.2 15,649,882 132 .9 61 5,728 ####### 1.0 12,395 25.5 15,659,744 148 1.0 76 6,944 ####### 1.0 486 1.0 15,666,757 164 1.1 91 7,561 ####### 1.0 1 .0 15,671,767 180 1.2 106 8,218 ####### 1.0 1 .0 15,675,171 196 1.3 121 9,577 ####### 1.0 1 .0 15,676,985 212 1.4 136 11,247 ####### 1.1 1 .0 15,678,240 228 1.5 151 13,315 ####### 1.1 1 .0 15,678,928 244 1.6 166 14,955 ####### 1.1 1 .0 15,679,337 260 1.8 181 16,559 ####### 1.1 1 .0 15,679,598 276 1.9 196 18,216 ####### 1.1 1 .0 15,679,771 292 2.0 211 19,778 ####### 1.1 1 .0 15,679,901 308 2.1 226 21,382 ####### 1.1 1 .0 15,680,025 ------------------------------------------------------------- SGA Target Advisory DB/Inst: O102EE1/o102ee1 End Snap: 816 SGA Target SGA Size Est DB Est DB Est Physical Size (M) Factor Time (s) Time Factor Reads ---------- -------- -------- ----------- -------------- 128 .5 71,252 7.1 42,074,270 192 .8 10,462 1.0 42,074,270 256 1.0 10,058 1.0 39,285,033 320 1.3 10,033 1.0 39,116,107 384 1.5 9,751 1.0 32,284,440 448 1.8 9,751 1.0 32,284,440 512 2.0 9,751 1.0 32,284,440 ------------------------------------------------------------- SGA Memory Summary DB/Inst: O102EE1/o102ee1 Snaps: 814-816 End Size (Bytes) SGA regions Begin Size (Bytes) (if different) ------------------------------ -------------------- -------------------- Database Buffers 96,468,992 Fixed Size 1,261,248 Redo Buffers 7,127,040 Variable Size 163,578,176 -------------------- -------------------- sum 268,435,456 ------------------------------------------------------------- SGA breakdown difference DB/Inst: O102EE1/o102ee1 Snaps: 814-816 -> 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 4.0 4.0 0.00 large PX msg pool 1.0 1.0 0.00 large free memory 3.0 3.0 0.00 shared ASH buffers 5.1 5.1 0.00 shared CCursor 6.1 6.1 0.00 shared Heap0: KGL 2.1 2.1 0.00 shared KCB Table Scan Buffer 3.8 3.8 0.00 shared KGLS heap 3.2 3.2 0.00 shared KQR M PO 2.3 2.3 0.00 shared KSFD SGA I/O b 3.8 3.8 0.00 shared KTI-UNDO 3.6 3.6 0.00 shared PCursor 3.5 3.5 0.00 shared PL/SQL MPCODE 4.7 4.7 0.00 shared event statistics per sess 3.8 3.8 0.00 shared free memory 26.6 26.6 0.00 shared kglsim hash table bkts 2.0 2.0 0.00 shared kglsim heap 1.7 1.7 0.00 shared kglsim object batch 2.7 2.7 0.00 shared library cache 7.4 7.4 0.00 shared private strands 3.5 3.5 0.00 shared row cache 3.6 3.6 0.00 shared sessions 2.3 2.3 0.00 shared sql area 29.7 29.7 0.00 buffer_cache 92.0 92.0 0.00 fixed_sga 1.2 1.2 0.00 log_buffer 6.8 6.8 0.00 ------------------------------------------------------------- SQL Memory Statistics DB/Inst: O102EE1/o102ee1 Snaps: 814-816 Begin End % Diff -------------- -------------- -------------- Avg Cursor Size (KB): 21.42 21.42 .00 Cursor to Parent ratio: 1.19 1.19 .00 Total Cursors: 1,619 1,619 .00 Total Parents: 1,362 1,362 .00 ------------------------------------------------------------- init.ora Parameters DB/Inst: O102EE1/o102ee1 Snaps: 814-816 End value Parameter Name Begin value (if different) ----------------------------- --------------------------------- -------------- audit_file_dest /opt/app/oracle/admin/o102ee1/adu background_dump_dest /opt/app/oracle/admin/o102ee1/bdu compatible 10.2.0.3.0 control_files /opt/app/oracle/oradata/o102ee1/c db_block_size 8192 db_domain db_name o102ee1 db_recovery_file_dest /opt/app/oracle/flash_recovery_ar db_recovery_file_dest_size 2147483648 java_pool_size 0 job_queue_processes 10 nls_language JAPANESE nls_territory JAPAN open_cursors 1024 pga_aggregate_target 16777216 processes 150 sessions 500 sga_target 268435456 undo_management AUTO undo_tablespace UNDOTBS1 workarea_size_policy AUTO ------------------------------------------------------------- End of Report ( sp_814_816.txt )