Oracle Database Discussions

Hello, I have a Unix server running HP-UX and 3 Oracle instances. One particulay instance is consuming a great deal of CPU resource. Below is the top session stats and also a Statspack report. I have increased the DB\_CACHE and it has not helped much. Any suggestions? ``` System: oradb01p Tue Jun 3 13:56:33 2008 Load averages: 1.08, 1.43, 1.40 238 processes: 223 sleeping, 14 running, 1 zombie Cpu states: LOAD USER NICE SYS IDLE BLOCK SWAIT INTR SSYS 1.08 90.1% 0.0% 9.9% 0.0% 0.0% 0.0% 0.0% 0.0% Memory: 6771220K (4904492K) real, 10403224K (7564968K) virtual, 90184K free Page# 1/10 TTY PID USERNAME PRI NI SIZE RES STATE TIME %WCPU %CPU COMMAND ? 23073 oracle 220 20 4300M 2320K run 0:07 78.09 28.30 oracleRMDB ? 2425 oracle 154 20 518M 1776K sleep 1304:35 2.53 2.53 tnslsnr ? 34 root 152 20 1728K 1728K run 807:06 0.51 0.51 vxfsd ? 23091 oracle 154 20 4300M 2672K sleep 0:00 6.81 0.33 oracleRMDB ? 23089 oracle 154 20 4300M 2196K sleep 0:00 3.41 0.32 oracleRMDB ? 23083 oracle 154 20 4300M 2196K sleep 0:00 1.62 0.29 oracleRMDB ? 23085 oracle 154 20 4300M 2196K sleep 0:00 1.62 0.29 oracleRMDB ? 23076 oracle 154 20 4300M 2196K sleep 0:00 1.25 0.28 oracleRMDB ? 23034 oracle 154 20 4300M 2352K sleep 0:00 0.48 0.22 oracleRMDB ? 21814 oracle 154 20 4301M 1392K sleep 0:18 0.21 0.21 oracleESDB ? 0 root 127 20 32K 0K sleep 61:44 0.21 0.21 swapper ? 23054 oracle 154 20 4300M 2196K sleep 0:00 0.49 0.21 oracleRMDB ? 23028 oracle 154 20 4301M 2684K sleep 0:00 0.40 0.20 oracleRMDB ? 23032 oracle 154 20 4301M 2680K sleep 0:00 0.39 0.19 oracleRMDB ? 23069 oracle 154 20 4300M 2240K sleep 0:00 0.48 0.19 oracleRMDB ? 23071 oracle 154 20 4300M 2240K sleep 0:00 0.48 0.19 oracleRMDB ? 2430 oracle 152 20 35576K 2868K run 15:27 0.18 0.18 dbsnmp pts/7 23077 oracle 178 20 7028K 5064K run 0:00 0.78 0.17 top ? 2448 root 152 20 24072K 1352K run 1:30 0.15 0.15 vxsvc ? 23495 oracle 156 20 4307M 1416K sleep 0:13 0.14 0.14 ora_lgwr_RMDB ? 23017 oracle 154 20 4300M 2264K sleep 0:00 0.21 0.13 oracleRMDB ? 23015 oracle 154 20 4301M 2376K sleep 0:00 0.17 0.12 oracleRMDB ? 23011 oracle 154 20 4300M 2288K sleep 0:00 0.15 0.10 oracleRMDB -- -- -- STATSPACK report for DB Name DB Id Instance Inst Num Release Cluster Host ------------ ----------- ------------ -------- ----------- ------- ------------ ESDB 1992573124 ESDB 1 9.2.0.8.0 NO oradb01p Snap Id Snap Time Sessions Curs/Sess Comment --------- ------------------ -------- --------- ------------------- Begin Snap: 181 03-Jun-08 12:49:02 22 4.0 End Snap: 187 03-Jun-08 13:49:04 21 4.2 Elapsed: 60.03 (mins) Cache Sizes (end) ~~~~~~~~~~~~~~~~~ Buffer Cache: 3,072M Std Block Size: 8K Shared Pool Size: 512M Log Buffer: 2,048K Load Profile ~~~~~~~~~~~~ Per Second Per Transaction --------------- --------------- Redo size: 2,100.20 2,829.07 Logical reads: 1,286.98 1,733.63 Block changes: 11.13 14.99 Physical reads: 0.49 0.66 Physical writes: 1.31 1.76 User calls: 15.81 21.30 Parses: 3.66 4.93 Hard parses: 0.00 0.00 Sorts: 0.64 0.86 Logons: 0.09 0.12 Executes: 7.44 10.03 Transactions: 0.74 % Blocks changed per Read: 0.86 Recursive Call %: 39.93 Rollback per transaction %: 0.00 Rows per Sort: 60.86 Instance Efficiency Percentages (Target 100%) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Buffer Nowait %: 100.00 Redo NoWait %: 100.00 Buffer Hit %: 99.96 In-memory Sort %: 100.00 Library Hit %: 100.00 Soft Parse %: 99.99 Execute to Parse %: 50.87 Latch Hit %: 100.00 Parse CPU to Parse Elapsd %: 64.91 % Non-Parse CPU: 99.48 Shared Pool Statistics Begin End ------ ------ Memory Usage %: 10.12 9.65 % SQL with executions>1: 76.82 76.47 % Memory for SQL w/exec>1: 80.88 80.80 Top 5 Timed Events ~~~~~~~~~~~~~~~~~~ % Total Event Waits Time (s) Ela Time -------------------------------------------- ------------ ----------- -------- CPU time 214 31.27 log file parallel write 3,596 200 29.17 log file sync 3,288 190 27.78 control file parallel write 1,174 71 10.31 db file sequential read 1,768 9 1.32 ------------------------------------------------------------- Wait Events for DB: ESDB Instance: ESDB Snaps: 181 -187 -> s - second -> cs - centisecond - 100th of a second -> ms - millisecond - 1000th of a second -> us - microsecond - 1000000th of a second -> ordered by wait time desc, waits desc (idle events last) Avg Total Wait wait Waits Event Waits Timeouts Time (s) (ms) /txn ---------------------------- ------------ ---------- ---------- ------ -------- log file parallel write 3,596 0 200 56 1.3 log file sync 3,288 0 190 58 1.2 control file parallel write 1,174 0 71 60 0.4 db file sequential read 1,768 0 9 5 0.7 process startup 17 0 1 38 0.0 control file sequential read 1,644 0 0 0 0.6 latch free 35 0 0 2 0.0 LGWR wait for redo copy 6 1 0 3 0.0 reliable message 1 0 0 3 0.0 buffer busy waits 2 0 0 0 0.0 SQL*Net message from client 56,563 0 27,839 492 21.2 wakeup time manager 119 119 3,379 28393 0.0 jobq slave wait 390 375 1,133 2905 0.1 SQL*Net message to client 56,562 0 0 0 21.2 SQL*Net more data from clien 308 0 0 0 0.1 ------------------------------------------------------------- Background Wait Events for DB: ESDB Instance: ESDB Snaps: 181 -187 -> ordered by wait time desc, waits desc (idle events last) Avg Total Wait wait Waits Event Waits Timeouts Time (s) (ms) /txn ---------------------------- ------------ ---------- ---------- ------ -------- log file parallel write 3,596 0 200 56 1.3 control file parallel write 1,174 0 71 60 0.4 rdbms ipc reply 2 0 0 75 0.0 control file sequential read 1,308 0 0 0 0.5 latch free 18 0 0 2 0.0 LGWR wait for redo copy 6 1 0 3 0.0 buffer busy waits 2 0 0 0 0.0 rdbms ipc message 7,074 3,538 19,027 2690 2.6 pmon timer 1,228 1,228 3,514 2862 0.5 smon timer 13 11 3,370 ###### 0.0 ------------------------------------------------------------- SQL ordered by Gets for DB: ESDB Instance: ESDB Snaps: 181 -187 -> End Buffer Gets Threshold: 10000 -> Note that resources reported for PL/SQL includes the resources used by all SQL statements called within the PL/SQL code. As individual SQL statements are also reported, it is possible and valid for the summed total % to exceed 100 CPU Elapsd Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value --------------- ------------ -------------- ------ -------- --------- ---------- 4,365,564 308 14,173.9 94.2 186.03 225.06 1449953046 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ CPU used by this session 21,417 6.0 8.0 CPU used when call started 21,169 5.9 7.9 CR blocks created 120 0.0 0.0 Cached Commit SCN referenced 37,059 10.3 13.9 Commit SCN cached 0 0.0 0.0 DBWR checkpoint buffers written 4,699 1.3 1.8 DBWR checkpoints 0 0.0 0.0 DBWR transaction table writes 20 0.0 0.0 DBWR undo block writes 293 0.1 0.1 PX local messages recv'd 0 0.0 0.0 PX local messages sent 0 0.0 0.0 SQL*Net roundtrips to/from client 55,650 15.5 20.8 active txn count during cleanout 115 0.0 0.0 background timeouts 4,329 1.2 1.6 branch node splits 1 0.0 0.0 buffer is not pinned count 4,487,690 1,245.9 1,678.3 buffer is pinned count 37,997,044 10,548.9 14,209.8 bytes received via SQL*Net from c 3,569,589 991.0 1,334.9 bytes sent via SQL*Net to client 2,356,937 654.3 881.4 calls to get snapshot scn: kcmgss 55,135 15.3 20.6 calls to kcmgas 12,561 3.5 4.7 calls to kcmgcs 78 0.0 0.0 change write time 340 0.1 0.1 cleanout - number of ktugct calls 152 0.0 0.1 cleanouts and rollbacks - consist 1 0.0 0.0 cleanouts only - consistent read 1 0.0 0.0 cluster key scan block gets 72 0.0 0.0 cluster key scans 12 0.0 0.0 commit cleanout failures: callbac 0 0.0 0.0 commit cleanouts 8,588 2.4 3.2 commit cleanouts successfully com 8,588 2.4 3.2 commit txn count during cleanout 163 0.1 0.1 consistent changes 285 0.1 0.1 consistent gets 4,605,984 1,278.7 1,722.5 consistent gets - examination 30,653 8.5 11.5 cursor authentications 0 0.0 0.0 data blocks consistent reads - un 285 0.1 0.1 db block changes 40,081 11.1 15.0 db block gets 29,735 8.3 11.1 deferred (CURRENT) block cleanout 1,917 0.5 0.7 enqueue conversions 152 0.0 0.1 enqueue releases 11,887 3.3 4.5 enqueue requests 11,887 3.3 4.5 enqueue timeouts 0 0.0 0.0 enqueue waits 0 0.0 0.0 execute count 26,809 7.4 10.0 free buffer requested 10,536 2.9 3.9 immediate (CR) block cleanout app 2 0.0 0.0 immediate (CURRENT) block cleanou 144 0.0 0.1 index crx upgrade (positioned) 3,348 0.9 1.3 index fast full scans (full) 0 0.0 0.0 index fetch by key 8,262 2.3 3.1 index scans kdiixs1 13,614 3.8 5.1 leaf node 90-10 splits 5 0.0 0.0 leaf node splits 68 0.0 0.0 logons cumulative 333 0.1 0.1 Instance Activity Stats for DB: ESDB Instance: ESDB Snaps: 181 -187 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ messages received 4,479 1.2 1.7 messages sent 4,479 1.2 1.7 no buffer to keep pinned count 0 0.0 0.0 no work - consistent read gets 4,551,034 1,263.5 1,702.0 opened cursors cumulative 7,511 2.1 2.8 parse count (failures) 0 0.0 0.0 parse count (hard) 1 0.0 0.0 parse count (total) 13,170 3.7 4.9 parse time cpu 111 0.0 0.0 parse time elapsed 171 0.1 0.1 physical reads 1,768 0.5 0.7 physical reads direct 0 0.0 0.0 physical writes 4,711 1.3 1.8 physical writes direct 0 0.0 0.0 physical writes non checkpoint 2,535 0.7 1.0 prefetched blocks 0 0.0 0.0 process last non-idle time 3,602 1.0 1.4 recovery blocks read 0 0.0 0.0 recursive calls 37,868 10.5 14.2 recursive cpu usage 310 0.1 0.1 redo blocks written 15,950 4.4 6.0 redo entries 20,072 5.6 7.5 redo ordering marks 210 0.1 0.1 redo size 7,564,928 2,100.2 2,829.1 redo synch time 19,503 5.4 7.3 redo synch writes 3,295 0.9 1.2 redo wastage 8,407,736 2,334.2 3,144.3 redo write time 20,459 5.7 7.7 redo writer latching time 2 0.0 0.0 redo writes 3,596 1.0 1.3 rollback changes - undo records a 6 0.0 0.0 rollbacks only - consistent read 238 0.1 0.1 rows fetched via callback 1,949 0.5 0.7 session connect time 0 0.0 0.0 session logical reads 4,635,719 1,287.0 1,733.6 session pga memory 8,788,976 2,440.0 3,286.8 session pga memory max 8,395,760 2,330.9 3,139.8 session uga memory 25,774,419,040 7,155,585.5 9,638,900.2 session uga memory max 51,243,152 14,226.3 19,163.5 shared hash latch upgrades - no w 3,374 0.9 1.3 sorts (memory) 2,297 0.6 0.9 sorts (rows) 139,785 38.8 52.3 summed dirty queue length 0 0.0 0.0 switch current to new buffer 8,252 2.3 3.1 table fetch by rowid 19,058,283 5,291.0 7,127.3 table fetch continued row 0 0.0 0.0 table scan blocks gotten 4,367,106 1,212.4 1,633.2 table scan rows gotten 157,703,614 43,782.2 58,976.7 table scans (long tables) 616 0.2 0.2 table scans (short tables) 6,952 1.9 2.6 transaction rollbacks 3 0.0 0.0 user calls 56,959 15.8 21.3 user commits 2,674 0.7 1.0 workarea executions - optimal 2,972 0.8 1.1 write clones created in foregroun 0 0.0 0.0 Instance Activity Stats for DB: ESDB Instance: ESDB Snaps: 181 -187 Statistic Total per Second per Trans --------------------------------- ------------------ -------------- ------------ ------------------------------------------------------------- Tablespace IO Stats for DB: ESDB Instance: ESDB Snaps: 181 -187 ->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) -------------- ------- ------ ------- ------------ -------- ---------- ------ OraSKU01 1,002 0 4.6 1.0 3,924 1 0 0.0 OraSKUIX 719 0 5.8 1.0 0 0 0 0.0 PERFSTAT 12 0 13.3 1.0 437 0 0 0.0 UNDOTBS1 0 0 0.0 335 0 3 0.0 SYSTEM 0 0 0.0 35 0 0 0.0 OraData 25 0 10.0 1.0 0 0 0 0.0 OraDataIX 10 0 14.0 1.0 0 0 0 0.0 ------------------------------------------------------------- File IO Stats for DB: ESDB Instance: ESDB Snaps: 181 -187 ->ordered by Tablespace, File Tablespace Filename ------------------------ ---------------------------------------------------- Av Av Av Av Buffer Av Buf Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms) -------------- ------- ------ ------- ------------ -------- ---------- ------ OraData //Oacle1/oradata/ESDB/OraData01.dbf 11 0 10.0 1.0 0 0 0 //Oacle2/oradata/ESDB/OraData02.dbf 14 0 10.0 1.0 0 0 0 OraDataIX //Oacle1/oradata/ESDB/OraDataIX02.dbf 2 0 0.0 1.0 0 0 0 //Oacle3/oradata/ESDB/OraDataIX01.dbf 8 0 17.5 1.0 0 0 0 OraSKU01 //Oacle2/oradata/ESDB/Orasku01a.dbf 508 0 4.1 1.0 2,002 1 0 //Oacle3/oradata/ESDB/Orasku01b.dbf 494 0 5.0 1.0 1,922 1 0 OraSKUIX //Oacle1/oradata/ESDB/OraskuIX02.dbf 329 0 5.9 1.0 0 0 0 //Oacle3/oradata/ESDB/OraskuIX01.dbf 390 0 5.7 1.0 0 0 0 PERFSTAT //Oacle2/oradata/RMDB/perfstat.dbf 12 0 13.3 1.0 437 0 0 SYSTEM //Oacle1/oradata/ESDB/SYS.dbf 0 0 35 0 0 UNDOTBS1 //Oacle1/oradata/ESDB/Undo01.dbf 0 0 335 0 3 0.0 ------------------------------------------------------------- Buffer Pool Statistics for DB: ESDB Instance: ESDB Snaps: 181 -187 -> Standard block size Pools D: default, K: keep, R: recycle -> Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32k Free Write Buffer Number of Cache Buffer Physical Physical Buffer Complete Busy P Buffers Hit % Gets Reads Writes Waits Waits Waits --- ---------- ----- ----------- ----------- ---------- ------- -------- ------ D 381,120 100.0 4,643,354 1,768 4,731 0 0 3 ------------------------------------------------------------- Instance Recovery Stats for DB: ESDB Instance: ESDB Snaps: 181 -187 -> 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 300 78 2416 7160 6892 117963 6892 E 300 102 3295 8751 8676 117963 8676 ------------------------------------------------------------- Buffer wait Statistics for DB: ESDB Instance: ESDB Snaps: 181 -187 -> ordered by wait time desc, waits desc Tot Wait Avg Class Waits Time (s) Time (ms) ------------------ ----------- ---------- --------- undo header 2 0 0 ------------------------------------------------------------- PGA Aggr Target Stats for DB: ESDB Instance: ESDB Snaps: 181 -187 -> B: Begin snap E: End snap (rows dentified 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 12 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 505 439 25.3 0.0 .0 .0 .0 25,856 E 505 439 24.8 0.0 .0 .0 .0 25,856 ------------------------------------------------------------- PGA Aggr Target Histogram for DB: ESDB Instance: ESDB Snaps: 181 -187 -> Optimal Executions are purely in-memory operations Low High Optimal Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs ------- ------- -------------- ------------- ------------ ------------ 2K 4K 2,758 2,758 0 0 4K 8K 130 130 0 0 8K 16K 16 16 0 0 16K 32K 42 42 0 0 32K 64K 12 12 0 0 64K 128K 6 6 0 0 128K 256K 6 6 0 0 ------------------------------------------------------------- PGA Memory Advisory for DB: ESDB Instance: ESDB End Snap: 187 -> 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 ---------- ------- ---------------- ---------------- -------- ---------- 63 0.1 86.3 0.0 100.0 0 126 0.3 86.3 0.0 100.0 0 253 0.5 86.3 0.0 100.0 0 379 0.8 86.3 0.0 100.0 0 505 1.0 86.3 0.0 100.0 0 606 1.2 86.3 0.0 100.0 0 707 1.4 86.3 0.0 100.0 0 808 1.6 86.3 0.0 100.0 0 909 1.8 86.3 0.0 100.0 0 1,010 2.0 86.3 0.0 100.0 0 1,515 3.0 86.3 0.0 100.0 0 2,020 4.0 86.3 0.0 100.0 0 3,030 6.0 86.3 0.0 100.0 0 4,040 8.0 86.3 0.0 100.0 0 ------------------------------------------------------------- Rollback Segment Stats for DB: ESDB Instance: ESDB Snaps: 181 -187 ->A high value for "Pct Waits" suggests more rollback segments may be required ->RBS stats may not be accurate between begin and end snaps when using Auto Undo managment, as RBS may be dynamically created and dropped as needed Trans Table Pct Undo Bytes RBS No Gets Waits Written Wraps Shrinks Extends ------ -------------- ------- --------------- -------- -------- -------- 0 19.0 0.00 0 0 0 0 1 984.0 0.00 267,688 0 0 0 2 4,314.0 0.05 279,374 6 1 3 3 1,034.0 0.00 359,746 7 0 3 4 949.0 0.00 113,996 2 0 0 5 925.0 0.00 104,070 0 0 0 6 1,044.0 0.00 538,734 0 0 0 7 944.0 0.00 138,092 0 0 0 8 1,004.0 0.10 283,924 6 1 2 9 941.0 0.00 100,472 0 0 0 10 919.0 0.00 103,734 0 0 0 ------------------------------------------------------------- Rollback Segment Storage for DB: ESDB Instance: ESDB Snaps: 181 -187 ->Optimal Size should be larger than Avg Active RBS No Segment Size Avg Active Optimal Size Maximum Size ------ --------------- --------------- --------------- --------------- 0 450,560 0 450,560 1 2,220,032 104,857 2,220,032 2 385,024 149,283 516,096 3 450,560 130,246 450,560 4 1,171,456 109,594 1,171,456 5 2,220,032 114,916 2,220,032 6 1,171,456 96,648 1,171,456 7 2,220,032 114,916 2,220,032 8 253,952 124,721 450,560 9 2,220,032 114,916 2,220,032 10 2,220,032 114,916 2,220,032 ------------------------------------------------------------- Undo Segment Summary for DB: ESDB Instance: ESDB Snaps: 181 -187 -> Undo segment block stats: -> uS - unexpired Stolen, uR - unexpired Released, uU - unexpired reUsed -> eS - expired Stolen, eR - expired Released, eU - expired reUsed Undo Undo Num Max Qry Max Tx Snapshot Out of uS/uR/uU/ TS# Blocks Trans Len (s) Concurcy Too Old Space eS/eR/eU ---- -------------- ---------- -------- ---------- -------- ------ ------------- 1 429 104,509 138 1 0 0 0/0/0/0/0/0 ------------------------------------------------------------- Undo Segment Stats for DB: ESDB Instance: ESDB Snaps: 181 -187 -> ordered by Time desc Undo Num Max Qry Max Tx Snap Out of uS/uR/uU/ End Time Blocks Trans Len (s) Concy Too Old Space eS/eR/eU ------------ ------------ -------- ------- -------- ------- ------ ------------- 03-Jun 13:54 77 17,022 138 1 0 0 0/0/0/0/0/0 03-Jun 13:44 56 16,293 2 1 0 0 0/0/0/0/0/0 03-Jun 13:34 57 15,600 3 1 0 0 0/0/0/0/0/0 03-Jun 13:24 71 14,886 3 1 0 0 0/0/0/0/0/0 03-Jun 13:14 57 14,196 3 1 0 0 0/0/0/0/0/0 03-Jun 13:04 58 13,550 2 1 0 0 0/0/0/0/0/0 03-Jun 12:54 53 12,962 2 1 0 0 0/0/0/0/0/0 ------------------------------------------------------------- Latch Activity for DB: ESDB Instance: ESDB Snaps: 181 -187 ->"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 3,598 0.0 0 0 FAL request queue 72 0.0 0 0 FIB s.o chain latch 2 0.0 0 0 FOB s.o list latch 106 0.0 0 0 SQL memory manager latch 6 0.0 0 1,174 0.0 SQL memory manager worka 80,352 0.0 0 0 active checkpoint queue 2,102 0.0 0 0 archive control 126 0.0 0 0 archive process latch 96 12.5 1.0 0 0 cache buffer handles 496 0.0 0 0 cache buffers chains 9,332,248 0.0 0 9,150 0.0 cache buffers lru chain 15,284 0.1 1.0 0 4,328,235 0.1 cas latch 23,529 0.0 0 0 channel handle pool latc 657 0.0 0 0 channel operations paren 3,394 0.0 0 0 checkpoint queue latch 103,732 0.0 1.0 0 5,482 0.0 child cursor hash table 7 0.0 0 0 dml lock allocation 9,589 0.0 0 0 dummy allocation 667 0.0 0 0 enqueue hash chains 23,924 0.0 1.0 0 0 enqueues 20,457 0.0 1.0 0 0 event group latch 327 0.0 0 0 hash table column usage 9 0.0 0 0 job workq parent latch 0 0 12 0.0 job_queue_processes para 66 0.0 0 0 ktm global data 17 0.0 0 0 kwqit: protect wakeup ti 119 0.0 0 0 lgwr LWN SCN 3,610 0.0 0 0 library cache 212,385 0.0 1.0 0 0 library cache pin 131,897 0.0 1.0 0 0 library cache pin alloca 55,697 0.0 1.0 0 0 list of block allocation 777 0.0 0 0 message pool operations 3 0.0 0 0 messages 21,895 0.0 0 0 mostly latch-free SCN 16,174 0.0 0 0 ncodef allocation latch 59 0.0 0 0 post/wait queue 5,325 0.0 0 3,291 0.0 process allocation 655 0.0 0 327 0.0 process group creation 655 0.0 0 0 redo allocation 34,123 0.0 1.0 0 20,061 0.0 redo copy 5 0.0 0 20,060 0.1 redo on-disk SCN 13,362 0.0 0 0 redo writing 20,634 0.0 0 0 row cache enqueue latch 9,612 0.0 0 0 row cache objects 13,088 0.0 0 0 sequence cache 974 0.0 0 0 session allocation 30,639 0.0 1.0 0 0 session idle bit 127,780 0.0 0 0 session switching 59 0.0 0 0 session timer 1,228 0.0 0 0 Latch Activity for DB: ESDB Instance: ESDB Snaps: 181 -187 ->"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 ------------------------ -------------- ------ ------ ------ ------------ ------ shared pool 34,992 0.0 0 0 sim partition latch 0 0 13 0.0 simulator hash latch 21,147 0.0 0 0 simulator lru latch 16 0.0 0 197 0.0 sort extent pool 72 0.0 0 0 spilled msgs queues list 119 0.0 0 0 transaction allocation 642 0.0 0 0 transaction branch alloc 59 0.0 0 0 undo global data 14,036 0.0 0 0 user lock 1,244 0.0 0 0 ------------------------------------------------------------- Latch Sleep breakdown for DB: ESDB Instance: ESDB Snaps: 181 -187 -> ordered by misses desc Get Spin & Latch Name Requests Misses Sleeps Sleeps 1->4 -------------------------- -------------- ----------- ----------- ------------ archive process latch 96 12 12 0/12/0/0/0 cache buffers lru chain 15,284 9 9 0/9/0/0/0 redo allocation 34,123 5 5 0/5/0/0/0 library cache 212,385 3 3 0/3/0/0/0 checkpoint queue latch 103,732 1 1 0/1/0/0/0 session allocation 30,639 1 1 0/1/0/0/0 library cache pin allocati 55,697 1 1 0/1/0/0/0 library cache pin 131,897 1 1 0/1/0/0/0 enqueue hash chains 23,924 1 1 0/1/0/0/0 enqueues 20,457 1 1 0/1/0/0/0 ------------------------------------------------------------- Latch Miss Sources for DB: ESDB Instance: ESDB Snaps: 181 -187 -> only latches with sleeps are shown -> ordered by name, sleeps desc NoWait Waiter Latch Name Where Misses Sleeps Sleeps ------------------------ -------------------------- ------- ---------- -------- archive process latch kcrrpa 0 12 0 cache buffers lru chain kcbzgb: posted for free bu 2,136 4 6 cache buffers lru chain kcbzar: KSLNBEGIN 0 3 0 cache buffers lru chain kcbzgb: wait 1,609 2 2 checkpoint queue latch kcbswcu: Switch buffers 0 1 2 enqueue hash chains ksqcnl 0 1 0 enqueues ksqgel: create enqueue 0 1 0 library cache kglhdgn: child: 0 1 0 library cache kglpnc: child 0 1 11 library cache kglupc: child 0 1 1 library cache pin kglpndl 0 1 0 library cache pin alloca kgllkdl 0 1 1 redo allocation kcrfwr: redo allocation 1 0 5 0 session allocation ksudlp 0 1 0 ------------------------------------------------------------- Dictionary Cache Stats for DB: ESDB Instance: ESDB Snaps: 181 -187 ->"Pct Misses" should be very low (< 2% in most cases) ->"Cache Usage" is the number of cache entries being used ->"Pct SGA" is the ratio of usage to allocated size for that cache Get Pct Scan Pct Mod Final Cache Requests Miss Reqs Miss Reqs Usage ------------------------- ------------ ------ ------- ----- -------- ---------- dc_object_ids 3 0.0 0 0 237 dc_objects 546 0.0 0 0 359 dc_profiles 314 0.0 0 0 2 dc_rollback_segments 556 0.0 0 0 22 dc_segments 246 0.0 0 0 174 dc_sequences 16 0.0 0 16 2 dc_tablespaces 31 0.0 0 0 5 dc_user_grants 1,848 0.0 0 0 6 dc_usernames 616 0.0 0 0 8 dc_users 4,086 0.0 0 0 12 ------------------------------------------------------------- Library Cache Activity for DB: ESDB Instance: ESDB Snaps: 181 -187 ->"Pct Misses" should be very low Get Pct Pin Pct Invali- Namespace Requests Miss Requests Miss Reloads dations --------------- ------------ ------ -------------- ------ ---------- -------- BODY 36 0.0 36 0.0 0 0 INDEX 238 0.0 238 0.0 0 0 SQL AREA 12,660 0.0 64,156 0.0 0 0 TABLE/PROCEDURE 579 0.0 923 0.0 0 0 ------------------------------------------------------------- Shared Pool Advisory for DB: ESDB Instance: ESDB End Snap: 187 -> 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 Estd Shared Pool SP Estd Estd Estd Lib LC Time Size for Size Lib Cache Lib Cache Cache Time Saved Estd Lib Cache Estim (M) Factr Size (M) Mem Obj Saved (s) Factr Mem Obj Hits ----------- ----- ---------- ------------ ------------ ------- --------------- 256 .5 9 1,555 856 1.0 65,621 320 .6 9 1,555 856 1.0 65,621 384 .8 9 1,555 856 1.0 65,621 448 .9 9 1,555 856 1.0 65,621 512 1.0 9 1,555 856 1.0 65,621 576 1.1 9 1,555 856 1.0 65,621 640 1.3 9 1,555 856 1.0 65,621 704 1.4 9 1,555 856 1.0 65,621 768 1.5 9 1,555 856 1.0 65,621 832 1.6 9 1,555 856 1.0 65,621 896 1.8 9 1,555 856 1.0 65,621 960 1.9 9 1,555 856 1.0 65,621 1,024 2.0 9 1,555 856 1.0 65,621 ------------------------------------------------------------- SGA Memory Summary for DB: ESDB Instance: ESDB Snaps: 181 -187 SGA regions Size in Bytes ------------------------------ ---------------- Database Buffers 3,221,225,472 Fixed Size 740,704 Redo Buffers 12,591,104 Variable Size 1,073,741,824 ---------------- sum 4,308,299,104 ------------------------------------------------------------- SGA breakdown difference for DB: ESDB Instance: ESDB Snaps: 181 -187 Pool Name Begin value End value % Diff ------ ------------------------------ ---------------- ---------------- ------- java free memory 167,772,160 167,772,160 0.00 large PX msg pool 122,880 122,880 0.00 large free memory 301,867,008 301,867,008 0.00 shared 1M buffer 2,099,200 2,099,200 0.00 shared Checkpoint queue 513,280 513,280 0.00 shared FileIdentificatonBlock 349,824 349,824 0.00 shared FileOpenBlock 2,307,528 2,307,528 0.00 shared KGK heap 7,000 7,000 0.00 shared KGLS heap 1,570,360 1,570,360 0.00 shared KQR L PO 610,304 610,304 0.00 shared KQR M PO 184,344 184,344 0.00 shared KQR M SO 2,048 2,048 0.00 shared KQR S SO 1,536 1,536 0.00 shared KSXR pending messages que 853,952 853,952 0.00 shared KSXR receive buffers 1,034,000 1,034,000 0.00 shared MTTR advisory 69,992 74,264 6.10 shared PL/SQL DIANA 536,336 536,336 0.00 shared PL/SQL MPCODE 261,656 261,656 0.00 shared PLS non-lib hp 2,088 2,088 0.00 shared PX subheap 98,840 98,840 0.00 shared db_block_hash_buckets 10,582,608 10,582,608 0.00 shared db_handles 348,000 348,000 0.00 shared dictionary cache 4,274,432 4,274,432 0.00 shared enqueue 651,040 651,040 0.00 shared errors 15,384 15,384 0.00 shared event statistics per sess 3,808,280 3,808,280 0.00 shared fixed allocation callback 1,992 1,992 0.00 shared free memory 527,799,600 530,528,408 0.52 shared joxs heap init 4,240 4,240 0.00 shared ksm_file2sga region 370,496 370,496 0.00 shared library cache 4,922,192 4,927,776 0.11 shared message pool freequeue 771,984 771,984 0.00 shared miscellaneous 11,818,288 11,790,312 -0.24 shared parameters 2,128 2,128 0.00 shared processes 412,800 412,800 0.00 shared sessions 905,840 905,840 0.00 shared sql area 6,668,400 6,670,992 0.04 shared table definiti 3,360 3,360 0.00 shared transaction 625,240 625,240 0.00 shared trigger inform 688 688 0.00 buffer_cache 3,221,225,472 3,221,225,472 0.00 fixed_sga 740,704 740,704 0.00 log_buffer 12,582,912 12,582,912 0.00 ------------------------------------------------------------- init.ora Parameters for DB: ESDB Instance: ESDB Snaps: 181 -187 End value Parameter Name Begin value (if different) ----------------------------- --------------------------------- -------------- aq_tm_processes 1 audit_sys_operations TRUE audit_trail DB background_dump_dest /u01/app/oracle/admin/ESDB/bd compatible 9.2.0.0.0 control_files //Oacle1/oradata/ESDB/control core_dump_dest /u01/app/oracle/admin/ESDB/cd cursor_sharing SIMILAR db_block_size 8192 db_cache_advice ON OFF db_cache_size 3221225472 db_domain world db_file_multiblock_read_count 8 db_name ESDB fast_start_mttr_target 300 hash_join_enabled FALSE instance_name ESDB java_pool_size 167772160 job_queue_processes 10 large_pool_size 301989888 log_archive_dest_1 location=//Oacle_arch/ESDB/lo log_archive_format arch%t_%s.dbf log_archive_start TRUE log_buffer 2097152 log_parallelism 6 nls_date_format YYYY-MM-DD HH24:MI:SS nls_timestamp_format YYYY-MM-DD HH24:MI:SS open_cursors 300 parallel_adaptive_multi_user TRUE parallel_automatic_tuning TRUE parallel_threads_per_cpu 4 pga_aggregate_target 529530880 processes 300 query_rewrite_enabled FALSE recovery_parallelism 6 remote_login_passwordfile EXCLUSIVE sga_max_size 4308299104 shared_pool_size 536870912 sort_area_size 524288 star_transformation_enabled FALSE timed_statistics TRUE undo_management AUTO undo_retention 900 undo_tablespace UNDOTBS1 user_dump_dest /u01/app/oracle/admin/ESDB/ud ------------------------------------------------------------- End of Report ``` Message was edited by: JesusLuvR