STATSPACK report for Database DB Id Instance Inst Num Startup Time Release RAC ~~~~~~~~ ----------- ------------ -------- --------------- ----------- --- 1144990915 orcl 1 14-Mar-07 15:18 10.2.0.1.0 NO Host Name: ADAPTIME75 Num CPUs: 2 Phys Memory (MB): 991 ~~~~ Snapshot Snap Id Snap Time Sessions Curs/Sess Comment ~~~~~~~~ ---------- ------------------ -------- --------- ------------------- Begin Snap: 2 13-Mar-07 17:15:22 20 15.0 End Snap: 4 13-Mar-07 17:18:23 20 15.0 Elapsed: 3.02 (mins) Cache Sizes Begin End ~~~~~~~~~~~ ---------- ---------- Buffer Cache: 176M Std Block Size: 8K Shared Pool Size: 76M Log Buffer: 6,864K Load Profile Per Second Per Transaction ~~~~~~~~~~~~ --------------- --------------- Redo size: 7,171.43 649,014.00 Logical reads: 17.28 1,564.00 Block changes: 10.82 979.00 Physical reads: 0.00 0.00 Physical writes: 0.81 73.00 User calls: 0.30 27.00 Parses: 0.69 62.00 Hard parses: 0.01 0.50 Sorts: 0.72 65.50 Logons: 0.00 0.00 Executes: 1.18 106.50 Transactions: 0.01 % Blocks changed per Read: 62.60 Recursive Call %: 95.44 Rollback per transaction %: 0.00 Rows per Sort: 84.35 Instance Efficiency Percentages ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Buffer Nowait %: 100.00 Redo NoWait %: 99.81 Buffer Hit %: 100.00 In-memory Sort %: 100.00 Library Hit %: 99.32 Soft Parse %: 99.19 Execute to Parse %: 41.78 Latch Hit %: 100.00 Parse CPU to Parse Elapsd %: % Non-Parse CPU: 100.00 Shared Pool Statistics Begin End ------ ------ Memory Usage %: 95.56 95.58 % SQL with executions>1: 82.08 83.57 % Memory for SQL w/exec>1: 93.01 94.01 Top 5 Timed Events Avg %Total ~~~~~~~~~~~~~~~~~~ wait Call Event Waits Time (s) (ms) Time ----------------------------------------- ------------ ----------- ------ ------ CPU time 2 74.0 control file sequential read 411 1 1 15.8 log file switch completion 2 0 118 7.1 control file parallel write 94 0 1 1.9 log file sequential read 2 0 8 .5 ------------------------------------------------------------- Host CPU (CPUs: 2) ~~~~~~~~ Load Average Begin End User System Idle WIO WCPU ------- ------- ------- ------- ------- ------- -------- 0.81 0.35 98.85 Instance CPU ~~~~~~~~~~~~ % of total CPU for Instance: 0.74 % of busy CPU for Instance: 63.74 %DB time waiting for CPU - Resource Mgr: Memory Statistics Begin End ~~~~~~~~~~~~~~~~~ ------------ ------------ Host Mem (MB): 990.7 990.7 SGA use (MB): 268.0 268.0 PGA use (MB): 28.9 28.5 % Host Mem used for SGA+PGA: 30.0 29.9 ------------------------------------------------------------- Time Model System Stats DB/Inst: ORCL/orcl Snaps: 2-4 -> Ordered by % of DB time desc, Statistic name Statistic Time (s) % of DB time ----------------------------------- -------------------- ------------ sql execute elapsed time 2.8 95.1 DB CPU 2.5 85.4 PL/SQL execution elapsed time 0.0 .3 parse time elapsed 0.0 .2 hard parse elapsed time 0.0 .0 repeated bind elapsed time 0.0 .0 DB time 3.0 background elapsed time 0.7 background cpu time 0.1 ------------------------------------------------------------- Wait Events DB/Inst: ORCL/orcl Snaps: 2-4 -> 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 sequential read 411 0 1 1 205.5 log file switch completion 2 0 0 118 1.0 control file parallel write 94 0 0 1 47.0 log file sequential read 2 0 0 8 1.0 log file parallel write 11 0 0 1 5.5 db file parallel write 21 0 0 0 10.5 log file sync 2 0 0 2 1.0 virtual circuit status 7 100 210 30000 3.5 Streams AQ: qmn coordinator idle 17 76 196 11529 8.5 Streams AQ: waiting for messages 36 100 180 5000 18.0 wait for unread message on broadc 180 100 180 1000 90.0 SQL*Net message from client 49 0 178 3622 24.5 Streams AQ: qmn slave idle wait 4 0 168 42000 2.0 ------------------------------------------------------------- Background Wait Events DB/Inst: ORCL/orcl Snaps: 2-4 -> %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 sequential read 49 0 0 7 24.5 control file parallel write 94 0 0 1 47.0 log file sequential read 2 0 0 8 1.0 db file parallel write 21 0 0 0 10.5 log file parallel write 9 0 0 1 4.5 rdbms ipc message 585 99 1,436 2455 292.5 Streams AQ: qmn coordinator idle 17 76 196 11529 8.5 pmon timer 61 100 183 3000 30.5 Streams AQ: qmn slave idle wait 4 0 168 42000 2.0 ------------------------------------------------------------- Wait Event Histogram DB/Inst: ORCL/orcl Snaps: 2-4 -> 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 -------------------------- ----- ----- ----- ----- ----- ----- ----- ----- ----- control file parallel writ 94 98.9 1.1 control file sequential re 411 89.8 3.2 .5 .7 3.9 1.9 db file parallel write 21 66.7 33.3 log file parallel write 11 81.8 18.2 log file sequential read 2 50.0 50.0 log file single write 2 100.0 log file switch completion 2 100.0 log file sync 2 50.0 50.0 SQL*Net message from clien 49 79.6 10.2 10.2 SQL*Net message to client 49 100.0 Streams AQ: qmn coordinato 17 23.5 76.5 Streams AQ: qmn slave idle 4 100.0 Streams AQ: waiting for me 36 100.0 dispatcher timer 3 100.0 pmon timer 61 100.0 rdbms ipc message 586 .3 .3 .3 31.4 67.6 virtual circuit status 7 100.0 wait for unread message on 180 100.0 ------------------------------------------------------------- SQL ordered by CPU DB/Inst: ORCL/orcl Snaps: 2-4 -> Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code. -> Total DB CPU (s): 3 -> Captured SQL accounts for 46.1% 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 ---------- ------------ ---------- ------ ---------- --------------- ---------- 1.16 1 1.16 45.7 1.51 1,506 2522684317 Module: SQL*Plus BEGIN statspack.snap; END; ------------------------------------------------------------- SQL ordered by Elapsed DB/Inst: ORCL/orcl Snaps: 2-4 -> Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code. -> Total DB Time (s): 3 -> Captured SQL accounts for 51.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 ---------- ------------ ---------- ------ ---------- --------------- ---------- 1.51 1 1.51 51.2 1.16 0 2522684317 Module: SQL*Plus BEGIN statspack.snap; END; ------------------------------------------------------------- SQL ordered by Gets DB/Inst: ORCL/orcl Snaps: 2-4 -> 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: 3,128 -> Captured SQL accounts for 4.9% 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,506 1 1,506.0 48.1 1.16 1.51 2522684317 Module: SQL*Plus BEGIN statspack.snap; END; 72 36 2.0 2.3 0.00 0.00 1316169839 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 36 9 4.0 1.2 0.00 0.00 1254950678 select file# from file$ where ts#=:1 36 36 1.0 1.2 0.00 0.00 1693927332 select count(*) from sys.job$ where (next_date > sysdate) and (n ext_date < (sysdate+5/86400)) ------------------------------------------------------------- SQL ordered by Executions DB/Inst: ORCL/orcl Snaps: 2-4 -> End Executions Threshold: 100 Total Executions: 213 -> Captured SQL accounts for 47.9% 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 ------------ --------------- ---------------- ----------- ---------- ---------- 36 0 0.0 0.00 0.00 1316169839 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 36 36 1.0 0.00 0.00 1693927332 select count(*) from sys.job$ where (next_date > sysdate) and (n ext_date < (sysdate+5/86400)) 9 9 1.0 0.00 0.00 1254950678 select file# from file$ where ts#=:1 6 120 20.0 0.00 0.00 87488304 select size_for_estimate, size_factor * 100 f, estd_physical_read_time, e std_physical_reads from v$db_cache_advice where id = '3' 6 12 2.0 0.00 0.00 303149640 select java_pool_size_for_estimate s, java_pool_size_f actor * 100 f, estd_lc_load_time l, 0 from v$java_pool_advice 6 72 12.0 0.00 0.00 1301067080 select shared_pool_size_for_estimate s, shared_pool_siz e_factor * 100 f, estd_lc_load_time l, 0 fr om v$shared_pool_advice ------------------------------------------------------------- SQL ordered by Parse Calls DB/Inst: ORCL/orcl Snaps: 2-4 -> End Parse Calls Threshold: 1000 Total Parse Calls: 124 -> Captured SQL accounts for 9.7% of Total Parse Calls -> SQL reported below exceeded 1.0% of Total Parse Calls % Total Old Parse Calls Executions Parses Hash Value ------------ ------------ -------- ---------- 9 9 7.26 1254950678 select file# from file$ where ts#=:1 ------------------------------------------------------------- Instance Activity Stats DB/Inst: ORCL/orcl Snaps: 2-4 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ CPU used by this session 245 1.4 122.5 CPU used when call started 254 1.4 127.0 CR blocks created 0 0.0 0.0 DB time 18,297 101.1 9,148.5 DBWR checkpoint buffers written 146 0.8 73.0 DBWR checkpoints 1 0.0 0.5 DBWR object drop buffers written 0 0.0 0.0 DBWR transaction table writes 6 0.0 3.0 DBWR undo block writes 122 0.7 61.0 DDL statements parallelized 0 0.0 0.0 DFO trees parallelized 0 0.0 0.0 IMU CR rollbacks 0 0.0 0.0 IMU Flushes 7 0.0 3.5 IMU Redo allocation size 0 0.0 0.0 IMU commits 0 0.0 0.0 IMU contention 0 0.0 0.0 IMU ktichg flush 0 0.0 0.0 IMU pool not allocated 100 0.6 50.0 IMU recursive-transaction flush 0 0.0 0.0 IMU undo allocation size 468 2.6 234.0 IMU- failed to get a private stra 100 0.6 50.0 PX local messages recv'd 0 0.0 0.0 PX local messages sent 0 0.0 0.0 Parallel operations not downgrade 0 0.0 0.0 SMON posted for undo segment shri 0 0.0 0.0 SQL*Net roundtrips to/from client 49 0.3 24.5 active txn count during cleanout 64 0.4 32.0 application wait time 0 0.0 0.0 background checkpoints started 1 0.0 0.5 background timeouts 576 3.2 288.0 buffer is not pinned count 180 1.0 90.0 buffer is pinned count 125 0.7 62.5 bytes received via SQL*Net from c 6,812 37.6 3,406.0 bytes sent via SQL*Net to client 7,823 43.2 3,911.5 calls to get snapshot scn: kcmgss 535 3.0 267.5 calls to kcmgas 139 0.8 69.5 calls to kcmgcs 33 0.2 16.5 change write time 0 0.0 0.0 cleanout - number of ktugct calls 75 0.4 37.5 cleanouts and rollbacks - consist 0 0.0 0.0 cleanouts only - consistent read 2 0.0 1.0 cluster key scan block gets 0 0.0 0.0 cluster key scans 0 0.0 0.0 commit batch performed 0 0.0 0.0 commit batch requested 0 0.0 0.0 commit batch/immediate performed 5 0.0 2.5 commit batch/immediate requested 5 0.0 2.5 commit cleanout failures: callbac 8 0.0 4.0 commit cleanouts 238 1.3 119.0 commit cleanouts successfully com 230 1.3 115.0 commit immediate performed 5 0.0 2.5 commit immediate requested 5 0.0 2.5 commit txn count during cleanout 41 0.2 20.5 concurrency wait time 0 0.0 0.0 consistent changes 0 0.0 0.0 consistent gets 861 4.8 430.5 Instance Activity Stats DB/Inst: ORCL/orcl Snaps: 2-4 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ consistent gets - examination 232 1.3 116.0 consistent gets from cache 861 4.8 430.5 cursor authentications 37 0.2 18.5 data blocks consistent reads - un 0 0.0 0.0 db block changes 1,958 10.8 979.0 db block gets 2,267 12.5 1,133.5 db block gets direct 0 0.0 0.0 db block gets from cache 2,267 12.5 1,133.5 deferred (CURRENT) block cleanout 147 0.8 73.5 enqueue conversions 128 0.7 64.0 enqueue releases 1,921 10.6 960.5 enqueue requests 1,921 10.6 960.5 enqueue timeouts 0 0.0 0.0 enqueue waits 0 0.0 0.0 execute count 213 1.2 106.5 free buffer inspected 0 0.0 0.0 free buffer requested 157 0.9 78.5 heap block compress 0 0.0 0.0 immediate (CR) block cleanout app 2 0.0 1.0 immediate (CURRENT) block cleanou 70 0.4 35.0 index fetch by key 182 1.0 91.0 index scans kdiixs1 219 1.2 109.5 leaf node 90-10 splits 2 0.0 1.0 leaf node splits 30 0.2 15.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 0 0.0 0.0 messages received 29 0.2 14.5 messages sent 29 0.2 14.5 no buffer to keep pinned count 0 0.0 0.0 no work - consistent read gets 209 1.2 104.5 opened cursors cumulative 122 0.7 61.0 parse count (failures) 0 0.0 0.0 parse count (hard) 1 0.0 0.5 parse count (total) 124 0.7 62.0 parse time cpu 0 0.0 0.0 parse time elapsed 0 0.0 0.0 physical read IO requests 0 0.0 0.0 physical read bytes 0 0.0 0.0 physical read total IO requests 413 2.3 206.5 physical read total bytes 6,734,848 37,209.1 3,367,424.0 physical read total multi block r 0 0.0 0.0 physical reads 0 0.0 0.0 physical reads cache 0 0.0 0.0 physical reads cache prefetch 0 0.0 0.0 physical reads direct 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 21 0.1 10.5 physical write bytes 1,196,032 6,607.9 598,016.0 physical write total IO requests 314 1.7 157.0 physical write total bytes 6,918,656 38,224.6 3,459,328.0 physical write total multi block 20 0.1 10.0 physical writes 146 0.8 73.0 physical writes direct 0 0.0 0.0 Instance Activity Stats DB/Inst: ORCL/orcl Snaps: 2-4 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ physical writes direct (lob) 0 0.0 0.0 physical writes from cache 146 0.8 73.0 physical writes non checkpoint 112 0.6 56.0 process last non-idle time 0 0.0 0.0 recursive calls 1,130 6.2 565.0 recursive cpu usage 228 1.3 114.0 redo blocks written 2,151 11.9 1,075.5 redo buffer allocation retries 2 0.0 1.0 redo entries 1,076 5.9 538.0 redo log space requests 2 0.0 1.0 redo log space wait time 24 0.1 12.0 redo ordering marks 102 0.6 51.0 redo size 1,298,028 7,171.4 649,014.0 redo synch time 0 0.0 0.0 redo synch writes 2 0.0 1.0 redo wastage 2,692 14.9 1,346.0 redo write time 0 0.0 0.0 redo writer latching time 0 0.0 0.0 redo writes 9 0.1 4.5 rollback changes - undo records a 10 0.1 5.0 rollbacks only - consistent read 0 0.0 0.0 rows fetched via callback 4 0.0 2.0 session connect time 0 0.0 0.0 session cursor cache hits 11 0.1 5.5 session logical reads 3,128 17.3 1,564.0 session pga memory max 0 0.0 0.0 session uga memory 0 0.0 0.0 session uga memory max 0 0.0 0.0 shared hash latch upgrades - no w 223 1.2 111.5 shared hash latch upgrades - wait 0 0.0 0.0 sorts (memory) 131 0.7 65.5 sorts (rows) 11,050 61.1 5,525.0 sql area purged 0 0.0 0.0 switch current to new buffer 0 0.0 0.0 table fetch by rowid 5 0.0 2.5 table fetch continued row 0 0.0 0.0 table scan blocks gotten 36 0.2 18.0 table scan rows gotten 439 2.4 219.5 table scans (long tables) 0 0.0 0.0 table scans (rowid ranges) 0 0.0 0.0 table scans (short tables) 12 0.1 6.0 total number of times SMON posted 0 0.0 0.0 transaction rollbacks 5 0.0 2.5 undo change vector size 486,092 2,685.6 243,046.0 user I/O wait time 0 0.0 0.0 user calls 54 0.3 27.0 user commits 2 0.0 1.0 user rollbacks 0 0.0 0.0 workarea executions - optimal 98 0.5 49.0 write clones created in backgroun 0 0.0 0.0 ------------------------------------------------------------- Instance Activity Stats DB/Inst: ORCL/orcl Snaps: 2-4 -> Statistics with absolute values (should not be diffed) Statistic Begin Value End Value --------------------------------- --------------- --------------- logons current 20 20 opened cursors current 300 300 session cursor cache count 2,311 2,312 ------------------------------------------------------------- Instance Activity Stats DB/Inst: ORCL/orcl Snaps: 2-4 -> Statistics identified by '(derived)' come from sources other than SYSSTAT Statistic Total per Hour --------------------------------- ------------------ --------- log switches (derived) 1 19.89 ------------------------------------------------------------- OS Statistics DB/Inst: ORCL/orcl Snaps: 2-4 -> ordered by statistic type (CPU use, Virtual Memory, Hardware Config), Name Statistic Total ------------------------- ---------------------- BUSY_TIME 416 IDLE_TIME 35,631 SYS_TIME 125 USER_TIME 291 VM_IN_BYTES 53,248 VM_OUT_BYTES 0 PHYSICAL_MEMORY_BYTES 1,038,815,232 NUM_CPUS 2 NUM_CPU_CORES 1 ------------------------------------------------------------- Tablespace IO Stats DB/Inst: ORCL/orcl Snaps: 2-4 ->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) -------------- ------- ------ ------- ------------ -------- ---------- ------ SYSTEM 0 0 0.0 11 0 0 0.0 UNDOTBS1 0 0 0.0 10 0 1 0.0 ------------------------------------------------------------- File IO Stats DB/Inst: ORCL/orcl Snaps: 2-4 ->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) -------------- ------- ----- --- ------- ------------ -------- ---------- ------ SYSTEM D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF 0 0 11 0 0 UNDOTBS1 D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF 0 0 10 0 1 0.0 ------------------------------------------------------------- Buffer Pool Statistics DB/Inst: ORCL/orcl Snaps: 2-4 -> 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 21K 100 3,319 0 146 0 0 1 ------------------------------------------------------------- Instance Recovery Stats DB/Inst: ORCL/orcl Snaps: 2-4 -> 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 12 2761 24891 25029 184320 25029 E 0 12 2785 28206 27556 184320 27556 ------------------------------------------------------------- Buffer Pool Advisory DB/Inst: ORCL/orcl End Snap: 4 -> 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 16 .1 2 1.9 17 88 72.7 D 32 .2 4 1.2 11 55 45.5 D 48 .3 6 1.1 10 49 40.5 D 64 .4 8 1.0 9 45 37.2 D 80 .5 10 1.0 9 44 36.4 D 96 .5 12 1.0 9 43 35.5 D 112 .6 14 1.0 9 43 35.5 D 128 .7 16 1.0 9 43 35.5 D 144 .8 18 1.0 9 43 35.5 D 160 .9 20 1.0 9 43 35.5 D 176 1.0 22 1.0 9 43 35.5 D 192 1.1 24 1.0 9 43 35.5 D 208 1.2 26 1.0 9 43 35.5 D 224 1.3 28 1.0 9 43 35.5 D 240 1.4 30 1.0 9 43 35.5 D 256 1.5 32 1.0 9 43 35.5 D 272 1.5 34 1.0 9 43 35.5 D 288 1.6 36 1.0 9 43 35.5 D 304 1.7 38 1.0 9 43 35.5 D 320 1.8 40 0.8 8 35 28.9 ------------------------------------------------------------- PGA Aggr Target Stats DB/Inst: ORCL/orcl Snaps: 2-4 -> 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 26 0 %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 89 65 28.9 0.0 .0 .0 .0 18,227 E 89 65 28.5 0.0 .0 .0 .0 18,227 ------------------------------------------------------------- PGA Aggr Target Histogram DB/Inst: ORCL/orcl Snaps: 2-4 -> Optimal Executions are purely in-memory operations Low High Optimal Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs ------- ------- -------------- ------------- ------------ ------------ 2K 4K 58 58 0 0 64K 128K 4 4 0 0 512K 1024K 36 36 0 0 ------------------------------------------------------------- PGA Memory Advisory DB/Inst: ORCL/orcl End Snap: 4 -> 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 ---------- ------- ---------------- ---------------- -------- ---------- 11 0.1 134.0 8.1 94.0 1 22 0.3 134.0 0.0 100.0 0 45 0.5 134.0 0.0 100.0 0 67 0.8 134.0 0.0 100.0 0 89 1.0 134.0 0.0 100.0 0 107 1.2 134.0 0.0 100.0 0 125 1.4 134.0 0.0 100.0 0 142 1.6 134.0 0.0 100.0 0 160 1.8 134.0 0.0 100.0 0 178 2.0 134.0 0.0 100.0 0 267 3.0 134.0 0.0 100.0 0 356 4.0 134.0 0.0 100.0 0 534 6.0 134.0 0.0 100.0 0 712 8.0 134.0 0.0 100.0 0 ------------------------------------------------------------- Process Memory Summary Stats DB/Inst: ORCL/orcl Snaps: 2-4 -> 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 -------- 29.1 16.7 .0 1.3 1.8 9 10 22 Other 27.7 1.3 1.8 9 10 22 SQL 1.1 .4 .1 .0 0 4 13 PL/SQL .3 .1 .0 .0 0 0 20 E -------- 28.7 16.7 .0 1.3 1.8 9 10 22 Other 27.4 1.2 1.8 9 10 22 SQL 1.1 .4 .1 .0 0 4 13 PL/SQL .3 .1 .0 .0 0 0 20 ------------------------------------------------------------- Top Process Memory (by component) DB/Inst: ORCL/orcl Snaps: 2-4 -> 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 -------- 9.1 4.2 .0 9.1 9.6 Other 9.1 9.1 9.6 PL/SQL .0 .0 .0 .0 31 SHAD -------- 2.3 1.8 .0 2.3 3.9 Other 2.1 2.1 2.6 SQL .2 .1 .2 1.2 PL/SQL .1 .0 .1 .1 15 SHAD -------- 2.0 .7 .0 2.0 3.9 Other 1.8 1.8 1.8 SQL .1 .1 .1 2.2 PL/SQL .0 .0 .0 .0 18 SHAD -------- 1.8 1.0 .0 1.8 8.2 Other 1.7 1.7 4.4 SQL .1 .0 .1 3.7 PL/SQL .0 .0 .0 .0 11 MMON -------- 1.5 1.2 .0 1.5 2.7 Other 1.4 1.4 1.9 SQL .1 .0 .1 .8 PL/SQL .0 .0 .0 .1 33 SHAD -------- 1.4 1.3 .0 1.4 3.6 Other 1.3 1.3 1.8 SQL .1 .0 .1 1.8 PL/SQL .0 .0 .0 .0 30 SHAD -------- 1.3 .9 .0 1.3 3.0 Other 1.2 1.2 2.0 SQL .1 .0 .1 .9 PL/SQL .0 .0 .0 .0 10 CJQ0 -------- 1.2 .4 .0 1.2 1.5 Other 1.2 1.2 1.2 SQL .1 .0 .1 .4 PL/SQL .0 .0 .0 .0 32 SHAD -------- 1.1 1.1 .0 1.1 1.7 Other 1.0 1.0 1.2 SQL .1 .1 .1 .5 PL/SQL .0 .0 .0 .0 8 SMON -------- 1.1 .5 .0 1.1 1.7 Other 1.0 1.0 1.5 SQL .1 .0 .1 .2 PL/SQL .0 .0 .0 .0 E 6 LGWR -------- 9.1 4.2 .0 9.1 9.6 Other 9.1 9.1 9.6 PL/SQL .0 .0 .0 .0 31 SHAD -------- 2.3 1.8 .0 2.3 3.9 Other 2.1 2.1 2.6 SQL .2 .1 .2 1.2 PL/SQL .1 .0 .1 .1 18 SHAD -------- 1.8 1.0 .0 1.8 8.2 Other 1.7 1.7 4.4 SQL .1 .0 .1 3.7 PL/SQL .0 .0 .0 .0 15 SHAD -------- 1.6 .7 .0 1.6 3.9 Other 1.5 1.5 1.7 SQL .1 .1 .1 2.2 Top Process Memory (by component) DB/Inst: ORCL/orcl Snaps: 2-4 -> ordered by Begin/End snapshot, Alloc (MB) desc Alloc Used Freeabl Max Hist Max PId Category (MB) (MB) (MB) Alloc (MB) Alloc (MB) - ------ ------------- ------- ------- -------- ---------- ---------- E 15 PL/SQL .0 .0 .0 .0 11 MMON -------- 1.5 1.2 .0 1.5 2.7 Other 1.4 1.4 1.9 SQL .1 .0 .1 .8 PL/SQL .0 .0 .0 .1 33 SHAD -------- 1.4 1.3 .0 1.4 3.6 Other 1.3 1.3 1.8 SQL .1 .0 .1 1.8 PL/SQL .0 .0 .0 .0 30 SHAD -------- 1.3 .9 .0 1.3 3.0 Other 1.2 1.2 2.0 SQL .1 .0 .1 .9 PL/SQL .0 .0 .0 .0 10 CJQ0 -------- 1.2 .4 .0 1.2 1.5 Other 1.2 1.2 1.2 SQL .1 .0 .1 .4 PL/SQL .0 .0 .0 .0 32 SHAD -------- 1.1 1.1 .0 1.1 1.7 Other 1.0 1.0 1.2 SQL .1 .1 .1 .5 PL/SQL .0 .0 .0 .0 8 SMON -------- 1.1 .5 .0 1.1 1.7 Other 1.0 1.0 1.5 SQL .1 .0 .1 .2 PL/SQL .0 .0 .0 .0 ------------------------------------------------------------- Latch Activity DB/Inst: ORCL/orcl Snaps: 2-4 ->"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 ------------------------ -------------- ------ ------ ------ ------------ ------ Consistent RBA 11 0.0 0 0 In memory undo latch 30 0.0 0 110 0.0 JS queue state obj latch 1,296 0.0 0 0 KMG MMAN ready and start 60 0.0 0 0 KWQP Prop Status 2 0.0 0 0 MQL Tracking Latch 0 0 2 0.0 Memory Management Latch 0 0 60 0.0 OS process allocation 60 0.0 0 0 PL/SQL warning settings 2 0.0 0 0 SQL memory manager latch 2 0.0 0 60 0.0 SQL memory manager worka 4,266 0.0 0 0 Shared B-Tree 10 0.0 0 0 active checkpoint queue 83 0.0 0 0 active service list 360 0.0 0 61 0.0 cache buffer handles 40 0.0 0 0 cache buffers chains 10,458 0.0 0 159 0.0 cache buffers lru chain 598 0.0 0 0 channel operations paren 1,031 0.0 0 0 checkpoint queue latch 1,422 0.0 0 169 0.0 compile environment latc 11 0.0 0 0 dml lock allocation 823 0.0 0 0 enqueue hash chains 3,982 0.0 0 0 enqueues 2,971 0.0 0 0 file cache latch 7 0.0 0 0 job_queue_processes para 3 0.0 0 0 kks stats 2 0.0 0 0 ksuosstats global area 16 0.0 0 0 kwqbsn:qsga 10 0.0 0 0 lgwr LWN SCN 64 0.0 0 0 library cache 2,247 0.0 0 0 library cache load lock 2 0.0 0 0 library cache lock 917 0.0 0 0 library cache pin 1,047 0.0 0 0 list of block allocation 17 0.0 0 0 messages 1,223 0.0 0 0 mostly latch-free SCN 64 0.0 0 0 ncodef allocation latch 3 0.0 0 0 object queue header oper 703 0.0 0 0 parallel query alloc buf 24 0.0 0 0 post/wait queue 5 0.0 0 4 0.0 qmn task queue latch 16 0.0 0 0 redo allocation 322 0.0 0 1,100 0.0 redo copy 0 0 1,100 0.0 redo writing 247 0.0 0 0 resmgr:actses active lis 2 0.0 0 0 resmgr:schema config 2 0.0 0 0 row cache objects 532 0.0 0 0 rules engine rule set st 200 0.0 0 0 sequence cache 6 0.0 0 0 session allocation 134 0.0 0 0 Latch Activity DB/Inst: ORCL/orcl Snaps: 2-4 ->"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 idle bit 119 0.0 0 0 session switching 3 0.0 0 0 session timer 61 0.0 0 0 shared pool 461 0.0 0 0 simulator hash latch 243 0.0 0 0 simulator lru latch 216 0.0 0 9 0.0 sort extent pool 3 0.0 0 0 threshold alerts latch 4 0.0 0 0 transaction branch alloc 3 0.0 0 0 undo global data 380 0.0 0 0 ------------------------------------------------------------- Dictionary Cache Stats DB/Inst: ORCL/orcl Snaps: 2-4 ->"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 3 0.0 0 0 1 dc_objects 3 0.0 0 0 1,972 dc_rollback_segments 5 0.0 0 0 22 dc_segments 6 0.0 0 0 926 dc_tablespaces 78 0.0 0 0 8 dc_users 88 0.0 0 0 46 ------------------------------------------------------------- Library Cache Activity DB/Inst: ORCL/orcl Snaps: 2-4 ->"Pct Misses" should be very low Get Pct Pin Pct Invali- Namespace Requests Miss Requests Miss Reloads dations --------------- ------------ ------ -------------- ------ ---------- -------- SQL AREA 1 100.0 223 0.9 0 0 TABLE/PROCEDURE 3 0.0 214 0.5 1 0 ------------------------------------------------------------- Rule Sets DB/Inst: ORCL/orcl Snaps: 2-4 -> * 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: ORCL/orcl End Snap: 4 -> 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 ---------- ----- -------- ------------ ------- ------ ------- ------ ----------- 68 .9 8 1,744 684 1.0 58 1.5 100,548 76 1.0 15 2,825 704 1.0 38 1.0 105,186 84 1.1 22 4,287 709 1.0 33 .9 106,847 92 1.2 29 5,130 713 1.0 29 .8 107,564 100 1.3 36 6,028 713 1.0 29 .8 107,767 108 1.4 43 6,904 713 1.0 29 .8 107,866 116 1.5 50 7,960 713 1.0 29 .8 107,924 124 1.6 57 8,846 713 1.0 29 .8 107,973 132 1.7 64 9,824 713 1.0 29 .8 108,056 140 1.8 71 10,647 713 1.0 29 .8 108,073 148 1.9 78 11,284 713 1.0 29 .8 108,093 156 2.1 85 12,385 713 1.0 29 .8 108,104 ------------------------------------------------------------- SGA Target Advisory DB/Inst: ORCL/orcl End Snap: 4 SGA Target SGA Size Est DB Est DB Est Physical Size (M) Factor Time (s) Time Factor Reads ---------- -------- -------- ----------- -------------- 134 .5 159 1.3 10,022 201 .8 121 1.0 9,118 268 1.0 121 1.0 9,118 335 1.3 121 1.0 9,118 402 1.5 121 1.0 9,118 469 1.8 112 .9 7,694 536 2.0 112 .9 7,694 ------------------------------------------------------------- SGA Memory Summary DB/Inst: ORCL/orcl Snaps: 2-4 End Size (Bytes) SGA regions Begin Size (Bytes) (if different) ------------------------------ -------------------- -------------------- Database Buffers 184,549,376 Fixed Size 1,248,528 Redo Buffers 7,139,328 Variable Size 88,081,136 -------------------- -------------------- sum 281,018,368 ------------------------------------------------------------- SGA breakdown difference DB/Inst: ORCL/orcl Snaps: 2-4 -> 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 .9 .9 0.00 large free memory 3.1 3.1 0.00 shared ASH buffers 4.0 4.0 0.00 shared CCursor 1.3 1.3 0.23 shared FileOpenBlock .8 .8 0.00 shared Heap0: KGL .9 .9 0.11 shared KCB Table Scan Buffer 3.8 3.8 0.00 shared KGLS heap 2.0 2.0 0.00 shared KQR M PO 3.9 3.9 0.00 shared KSFD SGA I/O b 3.8 3.8 0.00 shared KSXR pending messages que .8 .8 0.00 shared KSXR receive buffers 1.0 1.0 0.00 shared KTI-UNDO 1.2 1.2 0.00 shared PCursor .8 .8 0.26 shared PL/SQL DIANA 1.1 1.1 0.00 shared dbwriter coalesce buffer 1.0 1.0 0.00 shared event statistics per sess 1.5 1.5 0.00 shared free memory 3.4 3.4 -0.32 shared kglsim hash table bkts 2.0 2.0 0.00 shared kglsim heap .7 .7 0.00 shared kglsim object batch .9 .9 0.00 shared kks stbkt .9 .9 0.00 shared library cache 6.6 6.6 0.01 shared private strands 1.1 1.1 0.00 shared returns from metrics req .7 .7 0.00 shared row cache 3.6 3.6 0.00 shared sessions .8 .8 0.00 shared sql area 5.3 5.3 0.07 buffer_cache 176.0 176.0 0.00 fixed_sga 1.2 1.2 0.00 log_buffer 6.8 6.8 0.00 ------------------------------------------------------------- SQL Memory Statistics DB/Inst: ORCL/orcl Snaps: 2-4 Begin End % Diff -------------- -------------- -------------- Avg Cursor Size (KB): 46.84 46.74 -.20 Cursor to Parent ratio: 1.00 1.00 -.00 Total Cursors: 415 416 .24 Total Parents: 413 414 .24 ------------------------------------------------------------- init.ora Parameters DB/Inst: ORCL/orcl Snaps: 2-4 End value Parameter Name Begin value (if different) ----------------------------- --------------------------------- -------------- audit_file_dest D:\ORACLE\PRODUCT\10.2.0\ADMIN\OR background_dump_dest D:\ORACLE\PRODUCT\10.2.0\ADMIN\OR compatible 10.2.0.1.0 control_files D:\ORACLE\PRODUCT\10.2.0\ORADATA\ core_dump_dest D:\ORACLE\PRODUCT\10.2.0\ADMIN\OR db_block_size 8192 db_domain db_file_multiblock_read_count 16 db_name orcl db_recovery_file_dest D:\oracle\product\10.2.0/flash_re db_recovery_file_dest_size 2147483648 dispatchers (PROTOCOL=TCP) (SERVICE=orclXDB) job_queue_processes 10 open_cursors 300 pga_aggregate_target 93323264 processes 150 remote_login_passwordfile EXCLUSIVE sga_target 281018368 spfile D:\ORACLE\PRODUCT\10.2.0\DB_3\DBS undo_management AUTO undo_tablespace UNDOTBS1 user_dump_dest D:\ORACLE\PRODUCT\10.2.0\ADMIN\OR ------------------------------------------------------------- End of Report ( testsnap.txt )