STATSPACK report for Database DB Id Instance Inst Num Startup Time Release RAC ~~~~~~~~ ----------- ------------ -------- --------------- ----------- --- 3306974235 moto2 1 03-Sep-09 12:49 10.2.0.1.0 NO Host Name: honeybeecore Num CPUs: 4 Phys Memory (MB): 3 ~~~~ Snapshot Snap Id Snap Time Sessions Curs/Sess Comment ~~~~~~~~ ---------- ------------------ -------- --------- ------------------- Begin Snap: 12 03-Sep-09 15:28:39 20 4.6 End Snap: 13 03-Sep-09 16:56:14 20 3.2 Elapsed: 87.58 (mins) Cache Sizes Begin End ~~~~~~~~~~~ ---------- ---------- Buffer Cache: 500M Std Block Size: 8K Shared Pool Size: 300M Log Buffer: 10,921K Load Profile Per Second Per Transaction ~~~~~~~~~~~~ --------------- --------------- Redo size: 388.80 227,018.22 Logical reads: 331,244.35 193,409,897.33 Block changes: 1.21 705.78 Physical reads: 0.03 18.11 Physical writes: 0.17 102.00 User calls: 0.20 116.00 Parses: 0.36 211.22 Hard parses: 0.03 17.33 Sorts: 0.28 164.44 Logons: 0.00 1.00 Executes: 110,413.20 64,469,041.56 Transactions: 0.00 % Blocks changed per Read: 0.00 Recursive Call %: 100.00 Rollback per transaction %: 0.00 Rows per Sort: 22.99 Instance Efficiency Percentages ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Buffer Nowait %: 100.00 Redo NoWait %: 100.00 Buffer Hit %: 100.00 In-memory Sort %: 100.00 Library Hit %: 100.01 Soft Parse %: 91.79 Execute to Parse %: 100.00 Latch Hit %: 72.96 Parse CPU to Parse Elapsd %: 111.11 % Non-Parse CPU: 100.00 Shared Pool Statistics Begin End ------ ------ Memory Usage %: 75.30 76.81 % SQL with executions>1: 74.30 64.75 % Memory for SQL w/exec>1: 88.22 81.50 Top 5 Timed Events Avg %Total ~~~~~~~~~~~~~~~~~~ wait Call Event Waits Time (s) (ms) Time ----------------------------------------- ------------ ----------- ------ ------ CPU time 21,011 94.3 PL/SQL lock timer 1 879 ###### 3.9 latch: cache buffers chains 21,580 267 12 1.2 latch: library cache pin 4,340 58 13 .3 latch: library cache 3,992 53 13 .2 ------------------------------------------------------------- Host CPU (CPUs: 4) ~~~~~~~~ Load Average Begin End User System Idle WIO WCPU ------- ------- ------- ------- ------- ------- -------- 5.14 5.21 87.17 12.81 0.02 Instance CPU ~~~~~~~~~~~~ % of total CPU for Instance: 98.76 % of busy CPU for Instance: 98.78 %DB time waiting for CPU - Resource Mgr: Memory Statistics Begin End ~~~~~~~~~~~~~~~~~ ------------ ------------ Host Mem (MB): 3.0 3.0 SGA use (MB): 824.0 824.0 PGA use (MB): 39.8 39.2 % Host Mem used for SGA+PGA: 28326.8 28401.0 ------------------------------------------------------------- Time Model System Stats DB/Inst: moto2/moto2 Snaps: 12-13 -> Ordered by % of DB time desc, Statistic name Statistic Time (s) % of DB time ----------------------------------- -------------------- ------------ sql execute elapsed time 25,651.5 100.0 DB CPU 20,749.6 80.9 PL/SQL execution elapsed time 3,177.0 12.4 parse time elapsed 0.6 .0 hard parse elapsed time 0.6 .0 connection management call elapsed 0.1 .0 PL/SQL compilation elapsed time 0.0 .0 hard parse (sharing criteria) elaps 0.0 .0 repeated bind elapsed time 0.0 .0 hard parse (bind mismatch) elapsed 0.0 .0 DB time 25,651.7 background elapsed time 9.8 background cpu time 7.6 ------------------------------------------------------------- Wait Events DB/Inst: moto2/moto2 Snaps: 12-13 -> 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 --------------------------------- ------------ ------ ---------- ------ -------- PL/SQL lock timer 1 100 879 ###### 0.1 latch: cache buffers chains 21,580 0 267 12 2,397.8 latch: library cache pin 4,340 0 58 13 482.2 latch: library cache 3,992 0 53 13 443.6 control file parallel write 1,750 0 3 2 194.4 db file sequential read 85 0 1 8 9.4 log file parallel write 75 0 0 6 8.3 os thread startup 4 0 0 17 0.4 db file scattered read 19 0 0 3 2.1 log file sync 10 0 0 4 1.1 control file sequential read 1,143 0 0 0 127.0 Streams AQ: qmn slave idle wait 188 0 5,122 27247 20.9 Streams AQ: qmn coordinator idle 376 50 5,122 13623 41.8 Streams AQ: waiting for time mana 1 100 1,972 ###### 0.1 SQL*Net message from client 753 0 244 324 83.7 jobq slave wait 40 100 117 2936 4.4 class slave wait 2 100 10 4884 0.2 SQL*Net more data from client 59 0 0 0 6.6 ------------------------------------------------------------- Background Wait Events DB/Inst: moto2/moto2 Snaps: 12-13 -> %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 1,750 0 3 2 194.4 log file parallel write 75 0 0 5 8.3 os thread startup 4 0 0 17 0.4 db file sequential read 5 0 0 9 0.6 control file sequential read 234 0 0 0 26.0 rdbms ipc message 16,878 100 46,287 2742 1,875.3 smon timer 19 95 5,200 ###### 2.1 pmon timer 1,836 100 5,133 2795 204.0 Streams AQ: qmn slave idle wait 188 0 5,122 27247 20.9 Streams AQ: qmn coordinator idle 376 50 5,122 13623 41.8 Streams AQ: waiting for time mana 1 100 1,972 ###### 0.1 ------------------------------------------------------------- Wait Event Histogram DB/Inst: moto2/moto2 Snaps: 12-13 -> 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 -------------------------- ----- ----- ----- ----- ----- ----- ----- ----- ----- PL/SQL lock timer 1 100.0 SQL*Net more data to clien 6 100.0 buffer busy waits 13 100.0 control file parallel writ 1750 96.5 .1 3.3 .2 control file sequential re 1143 100.0 db file scattered read 19 68.4 15.8 15.8 db file sequential read 85 23.5 2.4 23.5 38.8 11.8 direct path write 8 100.0 latch: cache buffers chain 17K 81.8 .7 3.1 .6 .3 .7 12.7 latch: library cache 3186 77.2 .4 5.4 1.0 .2 .7 15.1 latch: library cache pin 3404 76.2 .7 5.4 .7 .2 .8 15.9 log file parallel write 75 66.7 9.3 2.7 1.3 8.0 9.3 2.7 log file sync 10 60.0 10.0 10.0 10.0 10.0 os thread startup 4 25.0 75.0 rdbms ipc reply 11 100.0 undo segment extension 66 100.0 SQL*Net message from clien 753 93.6 1.6 .4 .7 .1 .7 2.0 .9 SQL*Net message to client 753 100.0 SQL*Net more data from cli 59 100.0 Streams AQ: qmn coordinato 376 44.1 .3 .8 .5 .5 3.7 50.0 Streams AQ: qmn slave idle 188 100.0 Streams AQ: waiting for ti 1 100.0 class slave wait 2 100.0 jobq slave wait 40 100.0 pmon timer 1836 4.6 95.4 rdbms ipc message 16K .2 .0 .0 .0 .0 31.3 68.5 smon timer 19 100.0 ------------------------------------------------------------- SQL ordered by CPU DB/Inst: moto2/moto2 Snaps: 12-13 -> Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code. -> Total DB CPU (s): 20,750 -> Captured SQL accounts for 50.3% of Total DB CPU -> SQL reported below exceeded 1.0% of Total DB CPU CPU CPU per Elapsd Old Time (s) Executions Exec (s) %Total Time (s) Buffer Gets Hash Value ---------- ------------ ---------- ------ ---------- --------------- ---------- 10426.01 576,638,575 0.00 50.2 12944.01 1,737,711,111 3488755652 Module: mg SELECT COUNT(*) FROM CUSTOMERS WHERE STATUS='MON' ------------------------------------------------------------- SQL ordered by Elapsed DB/Inst: moto2/moto2 Snaps: 12-13 -> Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code. -> Total DB Time (s): 25,652 -> Captured SQL accounts for 50.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 ---------- ------------ ---------- ------ ---------- --------------- ---------- 12944.01 576,638,575 0.00 50.5 10426.01 0 3488755652 Module: mg SELECT COUNT(*) FROM CUSTOMERS WHERE STATUS='MON' ------------------------------------------------------------- SQL ordered by Gets DB/Inst: moto2/moto2 Snaps: 12-13 -> 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,740,689,076 -> Captured SQL accounts for 99.8% of Total Buffer Gets -> SQL reported below exceeded 1.0% of Total Buffer Gets CPU Elapsd Old Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value --------------- ------------ -------------- ------ -------- --------- ---------- 1,737,711,111 576,638,575 3.0 99.8 ######## 12944.01 3488755652 Module: mg SELECT COUNT(*) FROM CUSTOMERS WHERE STATUS='MON' ------------------------------------------------------------- SQL ordered by Reads DB/Inst: moto2/moto2 Snaps: 12-13 -> End Disk Reads Threshold: 1000 Total Disk Reads: 163 -> Captured SQL accounts for 16.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 --------------- ------------ -------------- ------ -------- --------- ---------- 374 4 93.5 229.4 0.46 1.39 3479051921 begin prvt_hdm.auto_execute( :db_id, :inst_id, :end_snap ); end; 22 1 22.0 13.5 0.63 0.71 2522684317 Module: SQL*Plus BEGIN statspack.snap; END; 9 9 1.0 5.5 0.03 0.08 199663413 select /*+ rule */ bucket, endpoint, col#, epvalue from histgrm$ where obj#=:1 and intcol#=:2 and row#=:3 order by bucket 6 8 0.8 3.7 0.01 0.05 1198893840 select order#,columns,types from access$ where d_obj#=:1 5 8 0.6 3.1 0.01 0.03 2062595902 select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_ obj#, nvl(property,0),subname,d_attrs from dependency$ d, obj$ o where d_obj#=:1 and p_obj#=obj#(+) order by order# 2 53 0.0 1.2 0.00 0.01 297749871 INSERT INTO SYS.WRI$_ADV_PARAMETERS (TASK_ID,NAME,DATATYPE,VALUE ,FLAGS,DESCRIPTION) VALUES (:B6 , :B5 , :B4 , :B3 , :B2 , :B1 ) 2 476 0.0 1.2 0.04 0.06 3665763022 update sys.col_usage$ set equality_preds = equality_preds + decode(bitand(:flag,1),0,0,1), equijoin_preds = equijoi n_preds + decode(bitand(:flag,2),0,0,1), nonequijoin_preds = nonequijoin_preds + decode(bitand(:flag,4),0,0,1), range_pre ds = range_preds + decode(bitand(:flag,8),0,0,1), 2 192 0.0 1.2 0.00 0.02 4274598960 select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, tim estamp#, sample_size, minimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, avgcln from hist_head$ where obj# =:1 and intcol#=:2 ------------------------------------------------------------- SQL ordered by Executions DB/Inst: moto2/moto2 Snaps: 12-13 -> End Executions Threshold: 100 Total Executions: 580,221,374 -> Captured SQL accounts for 99.4% 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 ------------ --------------- ---------------- ----------- ---------- ---------- 576,638,575 579,588,262 1.0 0.00 0.00 3488755652 Module: mg SELECT COUNT(*) FROM CUSTOMERS WHERE STATUS='MON' ------------------------------------------------------------- SQL ordered by Parse Calls DB/Inst: moto2/moto2 Snaps: 12-13 -> End Parse Calls Threshold: 1000 Total Parse Calls: 1,901 -> Captured SQL accounts for 48.9% of Total Parse Calls -> SQL reported below exceeded 1.0% of Total Parse Calls % Total Old Parse Calls Executions Parses Hash Value ------------ ------------ -------- ---------- 148 148 7.79 2554034351 lock table sys.col_usage$ in exclusive mode nowait 148 476 7.79 3665763022 update sys.col_usage$ set equality_preds = equality_preds + decode(bitand(:flag,1),0,0,1), equijoin_preds = equijoi n_preds + decode(bitand(:flag,2),0,0,1), nonequijoin_preds = nonequijoin_preds + decode(bitand(:flag,4),0,0,1), range_pre ds = range_preds + decode(bitand(:flag,8),0,0,1), 117 117 6.15 1307714173 Module: SQL*Plus BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END; 116 116 6.10 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 116 116 6.10 2396279102 lock table sys.mon_mods$ in exclusive mode nowait 68 68 3.58 4143084494 select privilege#,level from sysauth$ connect by grantee#=prior privilege# and privilege#>0 start with grantee#=:1 and privilege #>0 66 66 3.47 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 28 192 1.47 4274598960 select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, tim estamp#, sample_size, minimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, avgcln from hist_head$ where obj# =:1 and intcol#=:2 ------------------------------------------------------------- Instance Activity Stats DB/Inst: moto2/moto2 Snaps: 12-13 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ CPU used by this session 2,101,102 399.8 233,455.8 CPU used when call started 174 0.0 19.3 CR blocks created 11 0.0 1.2 Cached Commit SCN referenced 581 0.1 64.6 Commit SCN cached 3 0.0 0.3 DB time 102,231 19.5 11,359.0 DBWR checkpoint buffers written 878 0.2 97.6 DBWR checkpoints 0 0.0 0.0 DBWR object drop buffers written 0 0.0 0.0 DBWR transaction table writes 56 0.0 6.2 DBWR undo block writes 181 0.0 20.1 IMU Flushes 3 0.0 0.3 IMU Redo allocation size 104,188 19.8 11,576.4 IMU commits 6 0.0 0.7 IMU undo allocation size 144,100 27.4 16,011.1 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 3 0.0 0.3 SQL*Net roundtrips to/from client 750 0.1 83.3 active txn count during cleanout 70 0.0 7.8 application wait time 0 0.0 0.0 background timeouts 16,807 3.2 1,867.4 buffer is not pinned count 24,060 4.6 2,673.3 buffer is pinned count 89,643 17.1 9,960.3 bytes received via SQL*Net from c 291,182 55.4 32,353.6 bytes sent via SQL*Net to client 303,349 57.7 33,705.4 calls to get snapshot scn: kcmgss 580,221,887 110,413.3 ############ calls to kcmgas 496 0.1 55.1 calls to kcmgcs 95 0.0 10.6 change write time 7 0.0 0.8 cleanout - number of ktugct calls 116 0.0 12.9 cleanouts only - consistent read 34 0.0 3.8 cluster key scan block gets 6,207 1.2 689.7 cluster key scans 100 0.0 11.1 commit batch/immediate performed 1 0.0 0.1 commit batch/immediate requested 1 0.0 0.1 commit cleanout failures: callbac 8 0.0 0.9 commit cleanouts 808 0.2 89.8 commit cleanouts successfully com 800 0.2 88.9 commit immediate performed 1 0.0 0.1 commit immediate requested 1 0.0 0.1 commit txn count during cleanout 74 0.0 8.2 concurrency wait time 37,759 7.2 4,195.4 consistent changes 11 0.0 1.2 consistent gets 1,740,682,446 331,243.1 ############ consistent gets - examination 1,160,440,988 220,826.1 ############ consistent gets direct 2 0.0 0.2 consistent gets from cache 1,740,682,450 331,243.1 ############ cursor authentications 43 0.0 4.8 data blocks consistent reads - un 11 0.0 1.2 db block changes 6,352 1.2 705.8 db block gets 6,624 1.3 736.0 db block gets direct 40 0.0 4.4 db block gets from cache 6,584 1.3 731.6 deferred (CURRENT) block cleanout 503 0.1 55.9 enqueue conversions 1,102 0.2 122.4 Instance Activity Stats DB/Inst: moto2/moto2 Snaps: 12-13 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ enqueue releases 38,379 7.3 4,264.3 enqueue requests 38,380 7.3 4,264.4 enqueue timeouts 1 0.0 0.1 enqueue waits 0 0.0 0.0 execute count 580,221,374 110,413.2 ############ free buffer inspected 1 0.0 0.1 free buffer requested 338 0.1 37.6 heap block compress 41 0.0 4.6 immediate (CR) block cleanout app 34 0.0 3.8 immediate (CURRENT) block cleanou 109 0.0 12.1 index fast full scans (full) 5 0.0 0.6 index fetch by key 5,463 1.0 607.0 index scans kdiixs1 580,221,539 110,413.2 ############ leaf node 90-10 splits 11 0.0 1.2 leaf node splits 27 0.0 3.0 lob reads 5 0.0 0.6 lob writes 96 0.0 10.7 lob writes unaligned 96 0.0 10.7 logons cumulative 9 0.0 1.0 messages received 628 0.1 69.8 messages sent 628 0.1 69.8 no buffer to keep pinned count 0 0.0 0.0 no work - consistent read gets 19,310 3.7 2,145.6 opened cursors cumulative 2,038 0.4 226.4 parse count (failures) 0 0.0 0.0 parse count (hard) 156 0.0 17.3 parse count (total) 1,901 0.4 211.2 parse time cpu 10 0.0 1.1 parse time elapsed 9 0.0 1.0 physical read IO requests 99 0.0 11.0 physical read bytes 1,335,296 254.1 148,366.2 physical read total IO requests 1,248 0.2 138.7 physical read total bytes 20,111,360 3,827.1 2,234,595.6 physical read total multi block r 17 0.0 1.9 physical reads 163 0.0 18.1 physical reads cache 161 0.0 17.9 physical reads cache prefetch 64 0.0 7.1 physical reads direct 2 0.0 0.2 physical reads direct temporary t 0 0.0 0.0 physical reads prefetch warmup 12 0.0 1.3 physical write IO requests 565 0.1 62.8 physical write bytes 7,520,256 1,431.1 835,584.0 physical write total IO requests 5,891 1.1 654.6 physical write total bytes 95,648,768 18,201.5 ############ physical write total multi block 176 0.0 19.6 physical writes 918 0.2 102.0 physical writes direct 40 0.0 4.4 physical writes direct (lob) 2 0.0 0.2 physical writes from cache 878 0.2 97.6 physical writes non checkpoint 543 0.1 60.3 process last non-idle time 5,480 1.0 608.9 recovery blocks read 0 0.0 0.0 recursive calls 580,241,150 110,417.0 ############ recursive cpu usage 1,693,360 322.2 188,151.1 redo blocks read for recovery 0 0.0 0.0 redo blocks written 4,126 0.8 458.4 Instance Activity Stats DB/Inst: moto2/moto2 Snaps: 12-13 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ redo entries 3,743 0.7 415.9 redo ordering marks 110 0.0 12.2 redo size 2,043,164 388.8 227,018.2 redo synch time 5 0.0 0.6 redo synch writes 80 0.0 8.9 redo wastage 19,868 3.8 2,207.6 redo write time 37 0.0 4.1 redo writer latching time 0 0.0 0.0 redo writes 75 0.0 8.3 rollback changes - undo records a 2 0.0 0.2 rollbacks only - consistent read 11 0.0 1.2 rows fetched via callback 2,771 0.5 307.9 session connect time 0 0.0 0.0 session cursor cache hits 1,274 0.2 141.6 session logical reads 1,740,689,076 331,244.4 ############ session pga memory 842,920 160.4 93,657.8 session pga memory max 973,992 185.4 108,221.3 session uga memory 8,589,949,396 1,634,624.1 ############ session uga memory max 11,664,176 2,219.6 1,296,019.6 shared hash latch upgrades - no w 234,623,623 44,647.7 ############ shared hash latch upgrades - wait 345,597,927 65,765.5 ############ sorts (memory) 1,480 0.3 164.4 sorts (rows) 34,023 6.5 3,780.3 sql area purged 2 0.0 0.2 switch current to new buffer 2 0.0 0.2 table fetch by rowid 50,917 9.7 5,657.4 table fetch continued row 0 0.0 0.0 table scan blocks gotten 2,090 0.4 232.2 table scan rows gotten 46,928 8.9 5,214.2 table scans (short tables) 135 0.0 15.0 total number of times SMON posted 3 0.0 0.3 transaction rollbacks 1 0.0 0.1 undo change vector size 747,836 142.3 83,092.9 user I/O wait time 75 0.0 8.3 user calls 1,044 0.2 116.0 user commits 9 0.0 1.0 workarea executions - optimal 318 0.1 35.3 write clones created in backgroun 0 0.0 0.0 ------------------------------------------------------------- Instance Activity Stats DB/Inst: moto2/moto2 Snaps: 12-13 -> Statistics with absolute values (should not be diffed) Statistic Begin Value End Value --------------------------------- --------------- --------------- logons current 20 20 opened cursors current 92 64 session cursor cache count 578 699 ------------------------------------------------------------- Instance Activity Stats DB/Inst: moto2/moto2 Snaps: 12-13 -> Statistics identified by '(derived)' come from sources other than SYSSTAT Statistic Total per Hour --------------------------------- ------------------ --------- log switches (derived) 0 .00 ------------------------------------------------------------- OS Statistics DB/Inst: moto2/moto2 Snaps: 12-13 -> ordered by statistic type (CPU use, Virtual Memory, Hardware Config), Name Statistic Total ------------------------- ---------------------- BUSY_TIME 2,101,319 IDLE_TIME 394 NICE_TIME 62 SYS_TIME 269,157 USER_TIME 1,832,100 LOAD 5 NUM_CPUS 4 NUM_CPU_SOCKETS 1 ------------------------------------------------------------- Tablespace IO Stats DB/Inst: moto2/moto2 Snaps: 12-13 ->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) -------------- ------- ------ ------- ------------ -------- ---------- ------ SYSAUX 39 0 6.2 2.1 203 0 0 0.0 PERFSTAT 35 0 7.1 1.9 151 0 0 0.0 UNDOTBS1 0 0 0.0 125 0 13 0.0 SYSTEM 32 0 9.4 1.0 86 0 0 0.0 ------------------------------------------------------------- File IO Stats DB/Inst: moto2/moto2 Snaps: 12-13 ->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) -------------- ------- ----- --- ------- ------------ -------- ---------- ------ PERFSTAT /u01/oradata/moto2/perfstat01.dbf 35 0 7.1 32 1.9 151 0 0 SYSAUX /u01/oradata/moto2/sysaux01.dbf 39 0 6.2 32 2.1 203 0 0 SYSTEM /u01/oradata/moto2/system01.dbf 32 0 9.4 32 1.0 86 0 0 UNDOTBS1 /u01/oradata/moto2/undotbs01.dbf 0 0 125 0 13 0.0 ------------------------------------------------------------- File Read Histogram Stats DB/Inst: moto2/moto2 Snaps: 12-13 ->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 ------------ ------------ ------------ ------------ ------------ ------------ PERFSTAT /u01/oradata/moto2/perfstat01.dbf 1 0 8 10 3 0 SYSAUX /u01/oradata/moto2/sysaux01.dbf 10 0 4 11 3 0 SYSTEM /u01/oradata/moto2/system01.dbf 6 0 9 13 4 0 ------------------------------------------------------------- Buffer Pool Statistics DB/Inst: moto2/moto2 Snaps: 12-13 -> 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 62K 100 1,734,478,834 168 878 0 0 13 ------------------------------------------------------------- Instance Recovery Stats DB/Inst: moto2/moto2 Snaps: 12-13 -> 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 81 13 322 1878 3592 4608000 3592 10000 E 81 12 197 653 108 4608000 108 10000 ------------------------------------------------------------- Buffer Pool Advisory DB/Inst: moto2/moto2 End Snap: 13 -> 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 48 .1 6 1.0 8 21 .0 D 96 .2 12 1.0 8 20 .0 D 144 .3 18 1.0 8 20 .0 D 192 .4 24 1.0 8 20 .0 D 240 .5 30 1.0 8 20 .0 D 288 .6 36 1.0 8 20 .0 D 336 .7 42 1.0 8 20 .0 D 384 .8 48 1.0 8 20 .0 D 432 .9 54 1.0 8 20 .0 D 480 1.0 60 1.0 8 20 .0 D 500 1.0 62 1.0 8 20 .0 D 528 1.1 66 1.0 8 20 .0 D 576 1.2 72 1.0 8 20 .0 D 624 1.2 78 1.0 8 20 .0 D 672 1.3 84 1.0 8 20 .0 D 720 1.4 90 1.0 8 20 .0 D 768 1.5 96 1.0 8 20 .0 D 816 1.6 102 1.0 8 20 .0 D 864 1.7 108 1.0 8 20 .0 D 912 1.8 114 1.0 8 20 .0 D 960 1.9 120 1.0 8 20 .0 ------------------------------------------------------------- Buffer wait Statistics DB/Inst: moto2/moto2 Snaps: 12-13 -> ordered by wait time desc, waits desc Class Waits Total Wait Time (s) Avg Time (ms) ---------------------- ----------- ------------------- ------------- undo header 14 0 0 ------------------------------------------------------------- PGA Aggr Target Stats DB/Inst: moto2/moto2 Snaps: 12-13 -> 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 40 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 200 164 39.8 0.0 .0 .0 .0 40,960 E 200 164 39.2 0.0 .0 .0 .0 40,960 ------------------------------------------------------------- PGA Aggr Target Histogram DB/Inst: moto2/moto2 Snaps: 12-13 -> Optimal Executions are purely in-memory operations Low High Optimal Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs ------- ------- -------------- ------------- ------------ ------------ 2K 4K 258 258 0 0 64K 128K 8 8 0 0 128K 256K 2 2 0 0 512K 1024K 52 52 0 0 ------------------------------------------------------------- PGA Memory Advisory DB/Inst: moto2/moto2 End Snap: 13 -> 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 ---------- ------- ---------------- ---------------- -------- ---------- 25 0.1 127.9 0.0 100.0 0 50 0.3 127.9 0.0 100.0 0 100 0.5 127.9 0.0 100.0 0 150 0.8 127.9 0.0 100.0 0 200 1.0 127.9 0.0 100.0 0 240 1.2 127.9 0.0 100.0 0 280 1.4 127.9 0.0 100.0 0 320 1.6 127.9 0.0 100.0 0 360 1.8 127.9 0.0 100.0 0 400 2.0 127.9 0.0 100.0 0 600 3.0 127.9 0.0 100.0 0 800 4.0 127.9 0.0 100.0 0 1,200 6.0 127.9 0.0 100.0 0 1,600 8.0 127.9 0.0 100.0 0 ------------------------------------------------------------- Process Memory Summary Stats DB/Inst: moto2/moto2 Snaps: 12-13 -> 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 -------- 39.8 17.8 3.8 2.0 4.8 22 22 20 Other 35.4 1.8 4.7 22 22 20 Freeable 3.8 .0 1.0 .7 2 4 SQL .4 .2 .0 .0 0 2 13 PL/SQL .2 .2 .0 .0 0 0 20 E -------- 39.2 17.9 3.7 2.0 4.8 22 22 20 Other 34.9 1.7 4.8 22 22 20 Freeable 3.7 .0 .9 .7 2 4 SQL .4 .2 .0 .0 0 2 13 PL/SQL .2 .2 .0 .0 0 0 20 ------------------------------------------------------------- Top Process Memory (by component) DB/Inst: moto2/moto2 Snaps: 12-13 -> 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.0 10.4 .1 22.0 22.1 Other 21.8 21.8 22.0 Freeable .1 .0 .1 PL/SQL .0 .0 .0 .0 21 TNS V1-V3 --- 3.6 .8 1.8 3.6 3.8 Freeable 1.8 .0 1.8 Other 1.6 1.6 1.6 SQL .1 .1 .1 2.2 PL/SQL .0 .0 .0 .0 11 MMON -------- 2.7 1.1 1.1 2.7 2.8 Other 1.5 1.5 1.5 Freeable 1.1 .0 1.1 SQL .0 .0 .0 .8 PL/SQL .0 .0 .0 .0 5 DBW0 -------- 1.8 .3 .0 1.8 1.8 Other 1.8 1.8 1.8 PL/SQL .0 .0 .0 .0 10 CJQ0 -------- 1.4 .4 .8 1.4 1.4 Freeable .8 .0 .8 Other .6 .6 .6 SQL .1 .0 .1 .4 PL/SQL .0 .0 .0 .0 8 SMON -------- 1.2 .5 .0 1.2 1.2 Other 1.1 1.1 1.1 SQL .0 .0 .0 .2 PL/SQL .0 .0 .0 .0 E 6 LGWR -------- 22.0 10.4 .1 22.0 22.1 Other 21.8 21.8 22.0 Freeable .1 .0 .1 PL/SQL .0 .0 .0 .0 21 TNS V1-V3 --- 3.2 .8 1.7 3.2 3.2 Freeable 1.7 .0 1.7 Other 1.4 1.4 1.4 SQL .1 .0 .1 2.1 PL/SQL .0 .0 .0 .0 11 MMON -------- 2.7 1.1 1.1 2.7 2.8 Other 1.5 1.5 1.5 Freeable 1.1 .0 1.1 SQL .0 .0 .0 .8 PL/SQL .0 .0 .0 .0 5 DBW0 -------- 1.8 .3 .0 1.8 1.8 Other 1.8 1.8 1.8 PL/SQL .0 .0 .0 .0 10 CJQ0 -------- 1.4 .4 .8 1.4 1.4 Freeable .8 .0 .8 Other .6 .6 .6 SQL .1 .0 .1 .4 PL/SQL .0 .0 .0 .0 8 SMON -------- 1.2 .5 .0 1.2 1.2 Other 1.1 1.1 1.1 SQL .0 .0 .0 .2 PL/SQL .0 .0 .0 .0 Top Process Memory (by component) DB/Inst: moto2/moto2 Snaps: 12-13 -> ordered by Begin/End snapshot, Alloc (MB) desc Alloc Used Freeabl Max Hist Max PId Category (MB) (MB) (MB) Alloc (MB) Alloc (MB) - ------ ------------- ------- ------- -------- ---------- ---------- ------------------------------------------------------------- Undo Segment Summary DB/Inst: moto2/moto2 Snaps: 12-13 -> Min/Max TR (mins) - Min and Max Tuned Retention (minutes) -> STO - Snapshot Too Old count, OOS - Out Of Space count -> Undo segment block stats: uS - unexpired Stolen, uR - unexpired Released, uU - unexpired reUsed eS - expired Stolen, eR - expired Released, eU - expired reUsed Undo Num Undo Number of Max Qry Max Tx Min/Max STO/ uS/uR/uU/ TS# Blocks (K) Transactions Len (s) Concy TR (mins) OOS eS/eR/eU ---- ---------- --------------- -------- ---------- --------- ----- ----------- 1 .2 429 111 4 15/15.9 0/0 0/0/0/0/0/0 ------------------------------------------------------------- Undo Segment Stats DB/Inst: moto2/moto2 Snaps: 12-13 -> 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 ------------ ----------- ------------ ------- ------- ------- ----- ----------- 03-Sep 16:49 0 5 42 0 15 0/0 0/0/0/0/0/0 03-Sep 16:39 0 5 39 0 15 0/0 0/0/0/0/0/0 03-Sep 16:29 0 1 111 0 16 0/0 0/0/0/0/0/0 03-Sep 16:19 2 65 0 1 15 0/0 0/0/0/0/0/0 03-Sep 16:09 0 9 39 0 15 0/0 0/0/0/0/0/0 03-Sep 15:59 56 108 109 4 16 0/0 0/0/0/0/0/0 03-Sep 15:49 0 2 36 0 15 0/0 0/0/0/0/0/0 03-Sep 15:39 3 191 33 1 15 0/0 0/0/0/0/0/0 03-Sep 15:29 98 43 33 2 15 0/0 0/0/0/0/0/0 ------------------------------------------------------------- Latch Activity DB/Inst: moto2/moto2 Snaps: 12-13 ->"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 12,742 0.0 0 0 Consistent RBA 77 0.0 0 0 FOB s.o list latch 35 0.0 0 0 In memory undo latch 2,476 0.0 0 1,129 0.0 JS mem alloc latch 6 0.0 0 0 JS queue access latch 6 0.0 0 0 JS queue state obj latch 31,620 0.0 0 0 JS slv state obj latch 16 0.0 0 0 KMG MMAN ready and start 1,751 0.0 0 0 KTF sga latch 10 0.0 0 1,748 0.0 KWQMN job cache list lat 1 0.0 0 0 KWQP Prop Status 1 0.0 0 0 MQL Tracking Latch 0 0 104 0.0 Memory Management Latch 0 0 1,751 0.0 OS process 45 0.0 0 0 OS process allocation 1,781 0.0 0 0 OS process: request allo 14 0.0 0 0 PL/SQL warning settings 248 0.0 0 0 SQL memory manager latch 2 0.0 0 1,750 0.0 SQL memory manager worka 117,772 0.0 0 0 Shared B-Tree 191 0.0 0 0 active checkpoint queue 2,308 0.0 0 0 active service list 9,542 0.0 0 1,836 0.0 archive control 14 0.0 0 0 begin backup scn array 8 0.0 0 0 cache buffer handles 45 0.0 0 0 cache buffers chains 2,666,352,251 38.5 0.0 267 395 0.3 cache buffers lru chain 2,745 0.0 0 371 0.0 cache table scan latch 0 0 11 0.0 channel handle pool latc 14 0.0 0 0 channel operations paren 24,686 0.0 0 0 checkpoint queue latch 71,963 0.0 0 637 0.0 client/application info 43 0.0 0 0 compile environment latc 113 0.0 0 0 dml lock allocation 1,043 0.0 0 0 dummy allocation 18 0.0 0 0 enqueue hash chains 77,875 0.0 0 0 enqueues 75,400 0.0 0.0 0 0 event group latch 7 0.0 0 0 file cache latch 133 0.0 0 0 global KZLD latch for me 2 0.0 0 0 hash table column usage 300 0.0 0 8,053 0.0 hash table modification 80 0.0 0 0 internal temp table obje 7 0.0 0 0 job workq parent latch 0 0 4 0.0 job_queue_processes para 90 0.0 0 0 kks stats 310 0.0 0 0 ksuosstats global area 354 0.0 0 0 ktm global data 27 0.0 0 0 kwqbsn:qsga 191 0.0 0 0 Latch Activity DB/Inst: moto2/moto2 Snaps: 12-13 ->"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 ------------------------ -------------- ------ ------ ------ ------------ ------ lgwr LWN SCN 1,786 0.0 0 0 library cache 1,160,453,992 11.3 0.0 53 0 library cache load lock 84 0.0 0 0 library cache lock 8,221 0.0 0 0 library cache lock alloc 129 0.0 0 0 library cache pin 1,160,444,707 16.5 0.0 58 0 library cache pin alloca 60 0.0 0 0 list of block allocation 38 0.0 0 0 loader state object free 14 0.0 0 0 messages 35,017 0.0 0.0 0 0 mostly latch-free SCN 1,803 0.2 0.0 0 0 multiblock read objects 34 0.0 0 0 ncodef allocation latch 87 0.0 0 0 object queue header heap 17 0.0 0 0 object queue header oper 7,415 0.0 0 0 object stats modificatio 18 0.0 0 0 parallel query alloc buf 700 0.0 0 0 parameter table allocati 11 0.0 0 0 post/wait queue 31 0.0 0 10 0.0 process allocation 14 0.0 0 7 0.0 process group creation 14 0.0 0 0 qmn task queue latch 752 0.0 0 0 redo allocation 5,907 0.0 0 3,685 0.0 redo copy 0 0 3,685 0.0 redo writing 6,216 0.0 0 0 resmgr group change latc 13 0.0 0 0 resmgr:actses active lis 16 0.0 0 0 resmgr:actses change gro 5 0.0 0 0 resmgr:free threads list 14 0.0 0 0 resmgr:schema config 2 0.0 0 0 row cache objects 31,612 0.0 0 0 rules engine rule set st 100 0.0 0 0 sequence cache 94 0.0 0 0 session allocation 5,218 0.0 0 0 session idle bit 2,192 0.0 0 0 session state list latch 42 0.0 0 0 session switching 87 0.0 0 0 session timer 1,836 0.0 0 0 shared pool 11,655 0.0 0 0 simulator hash latch 2,245 0.0 0 0 simulator lru latch 2,133 0.0 0 62 0.0 slave class 2 0.0 0 0 slave class create 8 0.0 0 0 sort extent pool 135 0.0 0 0 state object free list 2 0.0 0 0 statistics aggregation 112 0.0 0 0 threshold alerts latch 229 0.0 0 0 trace latch 1 0.0 0 0 transaction allocation 26 0.0 0 0 transaction branch alloc 87 0.0 0 0 Latch Activity DB/Inst: moto2/moto2 Snaps: 12-13 ->"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 ------------------------ -------------- ------ ------ ------ ------------ ------ undo global data 3,436 0.1 0.0 0 0 user lock 12 0.0 0 0 ------------------------------------------------------------- Latch Sleep breakdown DB/Inst: moto2/moto2 Snaps: 12-13 -> ordered by misses desc Get Spin Latch Name Requests Misses Sleeps Gets -------------------------- --------------- ------------ ----------- ----------- cache buffers chains 2,666,352,251 ############ 21,568 ########### library cache pin 1,160,444,707 191,626,123 4,340 ########### library cache 1,160,453,992 130,644,185 3,992 ########### ------------------------------------------------------------- Latch Miss Sources DB/Inst: moto2/moto2 Snaps: 12-13 -> only latches with sleeps are shown -> ordered by name, sleeps desc NoWait Waiter Latch Name Where Misses Sleeps Sleeps ------------------------ -------------------------- ------- ---------- -------- cache buffers chains kcbrls: kslbegin 0 24,259 2,792 cache buffers chains kcbgtcr: fast path 0 20,748 53,869 cache buffers chains kcbgtcr: kslbegin excl 0 17,757 7,511 cache buffers chains kcbbxsv 0 2 2 library cache kglpndl: child: before pro 0 2,061 2,002 library cache kglpnc: child 0 1,930 1,989 library cache kglobpn: child: 0 1 2 library cache pin kglpnc: child 0 2,242 1,890 library cache pin kglpndl 0 2,097 2,450 library cache pin kglpnal: child: alloc spac 0 1 2 ------------------------------------------------------------- Dictionary Cache Stats DB/Inst: moto2/moto2 Snaps: 12-13 ->"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 94 0.0 0 2 1 dc_global_oids 378 1.3 0 0 32 dc_histogram_data 1,482 0.6 0 0 1,076 dc_histogram_defs 2,238 8.6 0 21 3,286 dc_object_grants 32 15.6 0 0 61 dc_object_ids 1,547 1.2 0 2 670 dc_objects 432 6.3 0 9 848 dc_profiles 6 0.0 0 0 1 dc_rollback_segments 2,919 0.0 0 0 56 dc_segments 523 2.9 0 9 516 dc_sequences 2 0.0 0 2 7 dc_tablespaces 687 0.1 0 0 7 dc_usernames 31 0.0 0 0 11 dc_users 1,272 0.0 0 0 46 outstanding_alerts 63 0.0 0 0 7 ------------------------------------------------------------- Library Cache Activity DB/Inst: moto2/moto2 Snaps: 12-13 ->"Pct Misses" should be very low Get Pct Pin Pct Invali- Namespace Requests Miss Requests Miss Reloads dations --------------- ------------ ------ -------------- ------ ---------- -------- BODY 28 0.0 245 0.0 0 0 INDEX 1 0.0 4 0.0 0 0 SQL AREA 140 100.0 579,905,826 -0.0 0 0 TABLE/PROCEDURE 216 7.4 1,782 2.4 0 0 TRIGGER 9 44.4 13 30.8 0 0 ------------------------------------------------------------- Rule Sets DB/Inst: moto2/moto2 Snaps: 12-13 -> * 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: moto2/moto2 End Snap: 13 -> 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 ---------- ----- -------- ------------ ------- ------ ------- ------ ----------- 204 .7 38 5,179 61 1.0 9 1.0 20,396 236 .8 38 5,179 61 1.0 9 1.0 20,396 268 .9 38 5,179 61 1.0 9 1.0 20,396 300 1.0 38 5,179 61 1.0 9 1.0 20,396 332 1.1 38 5,179 61 1.0 9 1.0 20,396 364 1.2 38 5,179 61 1.0 9 1.0 20,396 396 1.3 38 5,179 61 1.0 9 1.0 20,396 428 1.4 38 5,179 61 1.0 9 1.0 20,396 460 1.5 38 5,179 61 1.0 9 1.0 20,396 492 1.6 38 5,179 61 1.0 9 1.0 20,396 524 1.7 38 5,179 61 1.0 9 1.0 20,396 556 1.9 38 5,179 61 1.0 9 1.0 20,396 588 2.0 38 5,179 61 1.0 9 1.0 20,396 620 2.1 38 5,179 61 1.0 9 1.0 20,396 ------------------------------------------------------------- SGA Memory Summary DB/Inst: moto2/moto2 Snaps: 12-13 End Size (Bytes) SGA regions Begin Size (Bytes) (if different) ------------------------------ -------------------- -------------------- Database Buffers 524,288,000 Fixed Size 1,222,264 Redo Buffers 11,358,208 Variable Size 327,158,152 -------------------- -------------------- sum 864,026,624 ------------------------------------------------------------- SGA breakdown difference DB/Inst: moto2/moto2 Snaps: 12-13 -> 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 free memory 8.0 8.0 0.00 shared ASH buffers 8.0 8.0 0.00 shared CCursor 3.2 ######## shared FileOpenBlock 3.7 3.7 0.00 shared KCB Table Scan Buffer 3.8 3.8 0.00 shared KGLS heap 4.5 4.6 1.15 shared KSFD SGA I/O b 3.8 3.8 0.00 shared KTI-UNDO 31.7 31.7 0.00 shared buffer handles 3.6 3.6 0.00 shared enqueue 3.8 3.8 0.00 shared event statistics per sess 33.6 33.6 0.00 shared free memory 74.1 69.6 -6.11 shared library cache 3.5 3.8 8.28 shared private strands 30.7 30.7 0.00 shared procs: ksunfy 5.6 5.6 0.00 shared row cache 3.6 3.6 0.00 shared sessions 20.1 20.1 0.00 shared sql area 10.5 13.3 26.49 shared transaction 6.1 6.1 0.00 buffer_cache 500.0 500.0 0.00 fixed_sga 1.2 1.2 0.00 log_buffer 10.8 10.8 0.00 ------------------------------------------------------------- SQL Memory Statistics DB/Inst: moto2/moto2 Snaps: 12-13 Begin End % Diff -------------- -------------- -------------- Avg Cursor Size (KB): 47.00 43.14 -8.96 Cursor to Parent ratio: 1.04 1.04 -.51 Total Cursors: 747 901 17.09 Total Parents: 716 868 17.51 ------------------------------------------------------------- init.ora Parameters DB/Inst: moto2/moto2 Snaps: 12-13 End value Parameter Name Begin value (if different) ----------------------------- --------------------------------- -------------- _b_tree_bitmap_plans FALSE audit_file_dest /home/oracle/admin/moto2/adump audit_trail FALSE background_dump_dest /home/oracle/admin/moto2/bdump compatible 10.2.0.1.0 control_files /u01/oradata/moto2/control01.ctl core_dump_dest /home/oracle/admin/moto2/cdump db_block_size 8192 db_cache_size 524288000 db_domain db_file_multiblock_read_count 16 db_name moto2 db_recovery_file_dest /home/oracle/flash_recovery_area db_recovery_file_dest_size 2147483648 dml_locks 100 fast_start_mttr_target 300 instance_name moto2 java_pool_size 4194304 job_queue_processes 5 large_pool_size 8388608 log_buffer 11183104 log_checkpoint_interval 10000 max_dump_file_size 10240 open_cursors 1000 pga_aggregate_target 209715200 processes 4000 remote_login_passwordfile EXCLUSIVE shared_pool_size 314572800 streams_pool_size 0 timed_statistics TRUE undo_management AUTO undo_tablespace UNDOTBS1 user_dump_dest /home/oracle/admin/moto2/udump ------------------------------------------------------------- End of Report ( sp_moto2.lst )