Oracle Database Discussions

Hi, We are facing big performance problem in the database. Here I am posting the AWR Report. can you please suggest which areas needs to be improved... ``` WORKLOAD REPOSITORY report for DB Name DB Id Instance Inst Num Release RAC Host ------------ ----------- ------------ -------- ----------- --- ------------ PROD 3800788184 PROD 1 10.2.0.1.0 NO PROD Snap Id Snap Time Sessions Curs/Sess --------- ------------------- -------- --------- Begin Snap: 7814 27-Jul-08 09:00:24 82 5.7 End Snap: 7818 27-Jul-08 12:20:22 89 7.1 Elapsed: 199.98 (mins) DB Time: 16.88 (mins) Cache Sizes ~~~~~~~~~~~ Begin End ---------- ---------- Buffer Cache: 3,088M 2,960M Std Block Size: 8K Shared Pool Size: 416M 544M Log Buffer: 14,420K Load Profile ~~~~~~~~~~~~ Per Second Per Transaction --------------- --------------- Redo size: 2,196.70 7,823.43 Logical reads: 3,460.86 12,325.69 Block changes: 16.79 59.79 Physical reads: 0.92 3.26 Physical writes: 1.38 4.91 User calls: 23.20 82.63 Parses: 6.10 21.72 Hard parses: 0.31 1.11 Sorts: 3.13 11.16 Logons: 0.05 0.19 Executes: 14.06 50.06 Transactions: 0.28 % Blocks changed per Read: 0.49 Recursive Call %: 73.53 Rollback per transaction %: 3.56 Rows per Sort: 18.41 Instance Efficiency Percentages (Target 100%) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Buffer Nowait %: 100.00 Redo NoWait %: 100.00 Buffer Hit %: 99.98 In-memory Sort %: 100.00 Library Hit %: 97.64 Soft Parse %: 94.88 Execute to Parse %: 56.61 Latch Hit %: 99.99 Parse CPU to Parse Elapsd %: 92.63 % Non-Parse CPU: 96.92 Shared Pool Statistics Begin End ------ ------ Memory Usage %: 89.55 77.22 % SQL with executions>1: 82.61 75.11 % Memory for SQL w/exec>1: 87.22 64.18 Top 5 Timed Events Avg %Total ~~~~~~~~~~~~~~~~~~ wait Call Event Waits Time (s) (ms) Time Wait Class ------------------------------ ------------ ----------- ------ ------ ---------- CPU time 951 93.9 log file parallel write 3,684 74 20 7.3 System I/O control file parallel write 4,061 65 16 6.4 System I/O log file sync 2,433 49 20 4.8 Commit os thread startup 183 32 176 3.2 Concurrenc ------------------------------------------------------------- Time Model Statistics DB/Inst: PROD/PROD Snaps: 7814-7818 -> Total time in database user-calls (DB Time): 1012.6s -> Statistics including the word "background" measure background process time, and so do not contribute to the DB time statistic -> Ordered by % or DB time desc, Statistic name Statistic Name Time (s) % of DB Time ------------------------------------------ ------------------ ------------ DB CPU 951.0 93.9 parse time elapsed 685.5 67.7 hard parse elapsed time 677.9 66.9 sql execute elapsed time 598.4 59.1 hard parse (sharing criteria) elapsed time 287.9 28.4 PL/SQL execution elapsed time 7.4 .7 PL/SQL compilation elapsed time 2.2 .2 connection management call elapsed time 1.1 .1 failed parse elapsed time 0.1 .0 hard parse (bind mismatch) elapsed time 0.0 .0 repeated bind elapsed time 0.0 .0 sequence load elapsed time 0.0 .0 DB time 1,012.6 N/A background elapsed time 317.7 N/A background cpu time 35.2 N/A ------------------------------------------------------------- Wait Class DB/Inst: PROD/PROD Snaps: 7814-7818 -> 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 Avg %Time Total Wait wait Waits Wait Class Waits -outs Time (s) (ms) /txn -------------------- ---------------- ------ ---------------- ------- --------- System I/O 19,191 .0 139 7 5.7 Commit 2,433 .0 49 20 0.7 Concurrency 218 .0 34 157 0.1 User I/O 6,182 .0 15 2 1.8 Network 259,481 .0 1 0 77.0 Other 63 .0 1 9 0.0 Application 1,054 .0 0 0 0.3 ------------------------------------------------------------- Wait Events DB/Inst: PROD/PROD Snaps: 7814-7818 -> 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 %Time Total Wait wait Waits Event Waits -outs Time (s) (ms) /txn ---------------------------- -------------- ------ ----------- ------- --------- log file parallel write 3,684 .0 74 20 1.1 control file parallel write 4,061 .0 65 16 1.2 log file sync 2,433 .0 49 20 0.7 os thread startup 183 .0 32 176 0.1 db file sequential read 5,165 .0 15 3 1.5 library cache pin 3 .0 2 635 0.0 latch free 34 .0 1 16 0.0 db file scattered read 240 .0 1 2 0.1 control file sequential read 11,446 .0 0 0 3.4 SQL*Net more data from clien 4,354 .0 0 0 1.3 SQL*Net break/reset to clien 1,044 .0 0 0 0.3 SQL*Net more data to client 7,410 .0 0 0 2.2 SQL*Net message to client 247,717 .0 0 0 73.5 read by other session 17 .0 0 2 0.0 enq: TX - row lock contentio 10 .0 0 2 0.0 latch: shared pool 8 .0 0 1 0.0 library cache load lock 5 .0 0 0 0.0 latch: cache buffers chains 3 .0 0 0 0.0 latch: library cache 6 .0 0 0 0.0 LGWR wait for redo copy 29 .0 0 0 0.0 direct path write temp 206 .0 0 0 0.1 buffer busy waits 10 .0 0 0 0.0 direct path read temp 206 .0 0 0 0.1 direct path write 320 .0 0 0 0.1 direct path read 28 .0 0 0 0.0 SQL*Net message from client 247,710 .0 292,676 1182 73.5 wait for unread message on b 12,221 97.8 11,715 959 3.6 Streams AQ: waiting for mess 2,464 96.5 11,708 4752 0.7 Streams AQ: qmn coordinator 867 50.6 11,693 13487 0.3 Streams AQ: qmn slave idle w 428 .0 11,693 27320 0.1 virtual circuit status 400 100.0 11,658 29144 0.1 Streams AQ: waiting for time 444 49.3 11,582 26085 0.1 jobq slave wait 3,701 99.6 10,818 2923 1.1 class slave wait 4 100.0 20 4881 0.0 SGA: MMAN sleep for componen 22 77.3 0 7 0.0 ------------------------------------------------------------- Background Wait Events DB/Inst: PROD/PROD Snaps: 7814-7818 -> ordered by wait time desc, waits desc (idle events last) Avg %Time Total Wait wait Waits Event Waits -outs Time (s) (ms) /txn ---------------------------- -------------- ------ ----------- ------- --------- log file parallel write 3,683 .0 74 20 1.1 control file parallel write 4,061 .0 65 16 1.2 os thread startup 183 .0 32 176 0.1 db file sequential read 116 .0 0 4 0.0 db file scattered read 135 .0 0 3 0.0 control file sequential read 2,940 .0 0 0 0.9 events in waitclass Other 57 .0 0 0 0.0 latch: library cache 2 .0 0 0 0.0 buffer busy waits 2 .0 0 0 0.0 rdbms ipc message 46,099 93.0 124,778 2707 13.7 pmon timer 4,031 100.0 11,710 2905 1.2 Streams AQ: qmn coordinator 867 50.6 11,693 13487 0.3 Streams AQ: qmn slave idle w 428 .0 11,693 27320 0.1 Streams AQ: waiting for time 444 49.3 11,582 26085 0.1 smon timer 102 18.6 11,426 112021 0.0 SGA: MMAN sleep for componen 22 77.3 0 7 0.0 ------------------------------------------------------------- Operating System Statistics DB/Inst: PROD/PROD Snaps: 7814-7818 Statistic Total -------------------------------- -------------------- AVG_BUSY_TIME 30,581 AVG_IDLE_TIME 1,168,662 AVG_IOWAIT_TIME 7,800 AVG_SYS_TIME 3,465 AVG_USER_TIME 26,856 BUSY_TIME 123,483 IDLE_TIME 4,675,877 IOWAIT_TIME 32,427 SYS_TIME 14,959 USER_TIME 108,524 LOAD 0 OS_CPU_WAIT_TIME 25,905,577,100 RSRC_MGR_CPU_WAIT_TIME 0 VM_IN_BYTES 2,347,008 VM_OUT_BYTES 24,576 PHYSICAL_MEMORY_BYTES 17,159,045,120 NUM_CPUS 4 NUM_CPU_SOCKETS 4 ------------------------------------------------------------- Service Statistics DB/Inst: PROD/PROD Snaps: 7814-7818 -> ordered by DB Time Physical Logical Service Name DB Time (s) DB CPU (s) Reads Reads -------------------------------- ------------ ------------ ---------- ---------- SYS$USERS 960.5 927.3 7,121 ########## PROD 52.7 24.2 946 1,443,203 PRODXDB 0.0 0.0 0 0 SYS$BACKGROUND 0.0 0.0 2,906 238,664 ------------------------------------------------------------- Service Wait Class Stats DB/Inst: PROD/PROD Snaps: 7814-7818 -> Wait Class info for services in the Service Statistics section. -> Total Waits and Time Waited displayed for the following wait classes: User I/O, Concurrency, Administrative, Network -> Time Waited (Wt Time) in centisecond (100th of a second) Service Name ---------------------------------------------------------------- User I/O User I/O Concurcy Concurcy Admin Admin Network Network Total Wts Wt Time Total Wts Wt Time Total Wts Wt Time Total Wts Wt Time --------- --------- --------- --------- --------- --------- --------- --------- SYS$USERS 3430 676 20 190 0 0 229946 55 PROD 929 226 11 1 0 0 28434 2 SYS$BACKGROUND 1827 631 189 3230 0 0 0 0 ------------------------------------------------------------- SQL ordered by Elapsed Time DB/Inst: PROD/PROD Snaps: 7814-7818 Instance Activity Stats DB/Inst: PROD/PROD Snaps: 7814-7818 Statistic Total per Second per Trans -------------------------------- ------------------ -------------- ------------- CPU used by this session 99,936 8.3 29.7 CPU used when call started 97,964 8.2 29.1 CR blocks created 386 0.0 0.1 Cached Commit SCN referenced 0 0.0 0.0 Commit SCN cached 0 0.0 0.0 DB time 3,608,653 300.8 1,071.1 DBWR checkpoint buffers written 12,328 1.0 3.7 DBWR checkpoints 0 0.0 0.0 DBWR revisited being-written buf 0 0.0 0.0 DBWR tablespace checkpoint buffe 0 0.0 0.0 DBWR thread checkpoint buffers w 0 0.0 0.0 DBWR transaction table writes 394 0.0 0.1 DBWR undo block writes 1,652 0.1 0.5 DFO trees parallelized 0 0.0 0.0 IMU CR rollbacks 357 0.0 0.1 IMU Flushes 5,816 0.5 1.7 IMU Redo allocation size 2,728,736 227.4 810.0 IMU commits 2,900 0.2 0.9 IMU contention 9 0.0 0.0 IMU ktichg flush 51 0.0 0.0 IMU pool not allocated 0 0.0 0.0 IMU recursive-transaction flush 2 0.0 0.0 IMU undo allocation size 19,076,872 1,589.9 5,662.5 IMU- failed to get a private str 0 0.0 0.0 Misses for writing mapping 0 0.0 0.0 PX local messages recv'd 0 0.0 0.0 PX local messages sent 0 0.0 0.0 Parallel operations not downgrad 0 0.0 0.0 SMON posted for undo segment shr 0 0.0 0.0 SQL*Net roundtrips to/from clien 246,941 20.6 73.3 active txn count during cleanout 328 0.0 0.1 application wait time 15 0.0 0.0 background checkpoints completed 0 0.0 0.0 background checkpoints started 0 0.0 0.0 background timeouts 38,596 3.2 11.5 branch node splits 0 0.0 0.0 buffer is not pinned count 36,827,531 3,069.3 10,931.3 buffer is pinned count 31,194,791 2,599.9 9,259.4 bytes received via SQL*Net from 33,412,987 2,784.8 9,917.8 bytes sent via SQL*Net to client 148,800,281 12,401.5 44,167.5 calls to get snapshot scn: kcmgs 2,900,907 241.8 861.1 calls to kcmgas 9,164 0.8 2.7 calls to kcmgcs 492 0.0 0.2 change write time 77 0.0 0.0 cleanout - number of ktugct call 373 0.0 0.1 cleanouts and rollbacks - consis 6 0.0 0.0 cleanouts only - consistent read 15 0.0 0.0 cluster key scan block gets 61,363 5.1 18.2 cluster key scans 37,957 3.2 11.3 commit batch performed 3 0.0 0.0 commit batch requested 3 0.0 0.0 commit batch/immediate performed 112 0.0 0.0 commit batch/immediate requested 112 0.0 0.0 commit cleanout failures: block 0 0.0 0.0 commit cleanout failures: buffer 0 0.0 0.0 commit cleanout failures: callba 26 0.0 0.0 commit cleanout failures: cannot 5 0.0 0.0 commit cleanouts 21,549 1.8 6.4 commit cleanouts successfully co 21,518 1.8 6.4 commit immediate performed 109 0.0 0.0 Instance Activity Stats DB/Inst: PROD/PROD Snaps: 7814-7818 Statistic Total per Second per Trans -------------------------------- ------------------ -------------- ------------- commit immediate requested 109 0.0 0.0 commit txn count during cleanout 121 0.0 0.0 concurrency wait time 3,421 0.3 1.0 consistent changes 58,355 4.9 17.3 consistent gets 41,238,491 3,437.0 12,240.6 consistent gets - examination 18,063,987 1,505.5 5,361.8 consistent gets direct 202 0.0 0.1 consistent gets from cache 41,238,289 3,436.9 12,240.5 cursor authentications 972 0.1 0.3 data blocks consistent reads - u 390 0.0 0.1 db block changes 201,430 16.8 59.8 db block gets 286,769 23.9 85.1 db block gets direct 175 0.0 0.1 db block gets from cache 286,594 23.9 85.1 deferred (CURRENT) block cleanou 11,410 1.0 3.4 dirty buffers inspected 0 0.0 0.0 enqueue conversions 2,408 0.2 0.7 enqueue releases 143,576 12.0 42.6 enqueue requests 143,576 12.0 42.6 enqueue timeouts 0 0.0 0.0 enqueue waits 10 0.0 0.0 exchange deadlocks 0 0.0 0.0 execute count 168,661 14.1 50.1 frame signature mismatch 0 0.0 0.0 free buffer inspected 4,775 0.4 1.4 free buffer requested 23,795 2.0 7.1 heap block compress 334 0.0 0.1 hot buffers moved to head of LRU 807 0.1 0.2 immediate (CR) block cleanout ap 21 0.0 0.0 immediate (CURRENT) block cleano 5,199 0.4 1.5 index fast full scans (direct re 0 0.0 0.0 index fast full scans (full) 468 0.0 0.1 index fast full scans (rowid ran 0 0.0 0.0 index fetch by key 15,764,217 1,313.8 4,679.2 index scans kdiixs1 9,950,943 829.4 2,953.7 leaf node 90-10 splits 38 0.0 0.0 leaf node splits 100 0.0 0.0 lob reads 8,731 0.7 2.6 lob writes 14,129 1.2 4.2 lob writes unaligned 14,129 1.2 4.2 logons cumulative 649 0.1 0.2 messages received 6,557 0.6 2.0 messages sent 6,557 0.6 2.0 no buffer to keep pinned count 0 0.0 0.0 no work - consistent read gets 12,518,060 1,043.3 3,715.7 opened cursors cumulative 93,101 7.8 27.6 parse count (failures) 4 0.0 0.0 parse count (hard) 3,744 0.3 1.1 parse count (total) 73,180 6.1 21.7 parse time cpu 2,930 0.2 0.9 parse time elapsed 3,163 0.3 0.9 physical read IO requests 5,385 0.5 1.6 physical read bytes 89,989,120 7,500.0 26,710.9 physical read total IO requests 17,149 1.4 5.1 physical read total bytes 932,225,024 77,694.9 276,706.8 physical read total multi block 1,314 0.1 0.4 physical reads 10,985 0.9 3.3 physical reads cache 6,797 0.6 2.0 physical reads cache prefetch 1,820 0.2 0.5 physical reads direct 4,188 0.4 1.2 Instance Activity Stats DB/Inst: PROD/PROD Snaps: 7814-7818 Statistic Total per Second per Trans -------------------------------- ------------------ -------------- ------------- physical reads direct (lob) 28 0.0 0.0 physical reads direct temporary 3,986 0.3 1.2 physical reads prefetch warmup 0 0.0 0.0 physical write IO requests 8,670 0.7 2.6 physical write bytes 135,430,144 11,287.2 40,198.9 physical write total IO requests 32,013 2.7 9.5 physical write total bytes 421,256,192 35,109.0 125,038.9 physical write total multi block 7,885 0.7 2.3 physical writes 16,532 1.4 4.9 physical writes direct 4,161 0.4 1.2 physical writes direct (lob) 175 0.0 0.1 physical writes direct temporary 3,986 0.3 1.2 physical writes from cache 12,371 1.0 3.7 physical writes non checkpoint 6,702 0.6 2.0 pinned buffers inspected 1 0.0 0.0 prefetch warmup blocks aged out 0 0.0 0.0 prefetched blocks aged out befor 0 0.0 0.0 process last non-idle time 11,972 1.0 3.6 queries parallelized 0 0.0 0.0 recursive calls 773,346 64.5 229.6 recursive cpu usage 72,715 6.1 21.6 redo blocks written 27,997 2.3 8.3 redo buffer allocation retries 0 0.0 0.0 redo entries 58,326 4.9 17.3 redo log space requests 0 0.0 0.0 redo log space wait time 0 0.0 0.0 redo ordering marks 643 0.1 0.2 redo size 26,357,144 2,196.7 7,823.4 redo synch time 4,973 0.4 1.5 redo synch writes 20,199 1.7 6.0 redo wastage 1,943,928 162.0 577.0 redo write time 7,703 0.6 2.3 redo writer latching time 0 0.0 0.0 redo writes 3,682 0.3 1.1 rollback changes - undo records 178 0.0 0.1 rollbacks only - consistent read 380 0.0 0.1 rows fetched via callback 9,630,136 802.6 2,858.5 session connect time 0 0.0 0.0 session cursor cache hits 67,467 5.6 20.0 session logical reads 41,525,260 3,460.9 12,325.7 session pga memory 283,473,040 23,625.6 84,141.6 session pga memory max 352,385,488 29,369.0 104,596.5 session uga memory 820,366,151,104 68,372,164.5 ############# session uga memory max 754,732,912 62,902.1 224,022.8 shared hash latch upgrades - no 8,023,203 668.7 2,381.5 shared hash latch upgrades - wai 54 0.0 0.0 sorts (disk) 0 0.0 0.0 sorts (memory) 37,611 3.1 11.2 sorts (rows) 692,488 57.7 205.6 sql area purged 0 0.0 0.0 summed dirty queue length 0 0.0 0.0 switch current to new buffer 1,438 0.1 0.4 table fetch by rowid 19,740,366 1,645.2 5,859.4 table fetch continued row 1,749,467 145.8 519.3 table scan blocks gotten 979,396 81.6 290.7 table scan rows gotten 85,923,637 7,161.2 25,504.2 table scans (direct read) 0 0.0 0.0 table scans (long tables) 0 0.0 0.0 table scans (rowid ranges) 0 0.0 0.0 table scans (short tables) 874,416 72.9 259.6 Instance Activity Stats DB/Inst: PROD/PROD Snaps: 7814-7818 Statistic Total per Second per Trans -------------------------------- ------------------ -------------- ------------- total number of times SMON poste 83 0.0 0.0 transaction rollbacks 112 0.0 0.0 transaction tables consistent re 0 0.0 0.0 transaction tables consistent re 0 0.0 0.0 undo change vector size 8,547,600 712.4 2,537.1 user I/O wait time 1,539 0.1 0.5 user calls 278,377 23.2 82.6 user commits 3,249 0.3 1.0 user rollbacks 120 0.0 0.0 workarea executions - onepass 30 0.0 0.0 workarea executions - optimal 19,534 1.6 5.8 write clones created in backgrou 0 0.0 0.0 write clones created in foregrou 18 0.0 0.0 ------------------------------------------------------------- Instance Activity Stats - Absolute ValuesDB/Inst: PROD/PROD Snaps: 781 -> Statistics with absolute values (should not be diffed) Statistic Begin Value End Value -------------------------------- --------------- --------------- session cursor cache count 148,018 154,611 opened cursors current 466 635 logons current 82 89 ------------------------------------------------------------- Instance Activity Stats - Thread ActivityDB/Inst: PROD/PROD Snaps: 7814 -> Statistics identified by '(derived)' come from sources other than SYSSTAT Statistic Total per Hour -------------------------------- ------------------ --------- log switches (derived) 0 .00 ------------------------------------------------------------- Tablespace IO Stats DB/Inst: PROD/PROD Snaps: 7814-7818 -> 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 1,358 0 4.5 1.0 4,085 0 3 0.0 SIEBEL_INDEX_TBS 553 0 1.4 1.0 2,467 0 6 1.7 SIEBEL_DATA_TBS 1,559 0 2.5 1.2 607 0 18 1.7 SYSTEM 1,696 0 2.6 1.9 470 0 0 0.0 UNDOTBS1 0 0 0.0 .0 835 0 0 0.0 SIEBEL_TEMP_TBS 206 0 0.1 19.3 206 0 0 0.0 TEMP 1 0 0.0 1.0 0 0 0 0.0 ------------------------------------------------------------- File IO Stats DB/Inst: PROD/PROD Snaps: 7814-7818 -> 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) -------------- ------- ------ ------- ------------ -------- ---------- ------ SIEBEL_DATA_TBS /oradata/dbfiles/PROD/siebel_data_tbs01.dbf 584 0 2.5 1.6 203 0 2 0.0 SIEBEL_DATA_TBS /oradata/dbfiles/PROD/siebel_data_tbs02.dbf 331 0 2.9 1.0 103 0 3 3.3 SIEBEL_DATA_TBS /oradata/dbfiles/PROD/siebel_data_tbs03.dbf 307 0 2.5 1.0 81 0 6 0.0 SIEBEL_DATA_TBS /oradata/dbfiles/PROD/siebel_data_tbs04.dbf 185 0 2.7 1.0 84 0 4 2.5 SIEBEL_DATA_TBS /oradata/dbfiles/PROD/siebel_data_tbs05.dbf 152 0 1.8 1.0 136 0 3 3.3 SIEBEL_INDEX_TBS /oradata/dbfiles/PROD/siebel_index_tbs01.dbf 333 0 1.5 1.0 1,029 0 4 2.5 SIEBEL_INDEX_TBS /oradata/dbfiles/PROD/siebel_index_tbs02.dbf 96 0 1.3 1.0 803 0 2 0.0 SIEBEL_INDEX_TBS /oradata/dbfiles/PROD/siebel_index_tbs03.dbf 124 0 1.5 1.0 635 0 0 0.0 SIEBEL_TEMP_TBS /oradata/dbfiles/PROD/siebel_temp_tbs.dbf 206 0 0.1 19.3 206 0 0 N/A SYSAUX /oradata/dbfiles/PROD/sysaux01.dbf 926 0 4.4 1.0 2,690 0 1 0.0 SYSAUX /oradata/dbfiles/PROD/sysaux02.dbf 432 0 4.6 1.0 1,395 0 2 0.0 SYSTEM /oradata/dbfiles/PROD/system01.dbf 1,535 0 2.6 2.0 436 0 0 0.0 SYSTEM /oradata/dbfiles/PROD/system02.dbf 161 0 2.7 1.0 34 0 0 0.0 TEMP /oradata/dbfiles/PROD/temp01.dbf 1 0 0.0 1.0 0 0 0 N/A UNDOTBS1 /oradata/dbfiles/PROD/undotbs01.dbf 0 0 N/A N/A 835 0 0 0.0 ------------------------------------------------------------- Buffer Pool Statistics DB/Inst: PROD/PROD Snaps: 7814-7818 -> Standard block size Pools D: default, K: keep, R: recycle -> Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32k Free Writ Buffer Number of Pool Buffer Physical Physical Buff Comp Busy P Buffers Hit% Gets Reads Writes Wait Wait Waits --- ---------- ---- -------------- ------------ ----------- ---- ---- ---------- D 367,034 100 41,522,904 6,791 12,371 0 0 27 ------------------------------------------------------------- Instance Recovery Stats DB/Inst: PROD/PROD Snaps: 7814-7818 -> B: Begin snapshot, E: End snapshot Targt Estd Log File Log Ckpt Log Ckpt MTTR MTTR Recovery Actual Target Size Timeout Interval (s) (s) Estd IOs Redo Blks Redo Blks Redo Blks Redo Blks Redo Blks - ----- ----- ---------- --------- --------- ---------- --------- ------------ B 0 10 753 1319 2534 368640 2534 N/A E 0 10 453 548 5069 368640 5069 N/A ------------------------------------------------------------- Buffer Pool Advisory DB/Inst: PROD/PROD Snap: 7818 -> Only rows with estimated physical reads >0 are displayed -> ordered by Block Size, Buffers For Estimate Est Phys Size for Size Buffers for Read Estimated P Est (M) Factor Estimate Factor Physical Reads --- -------- ------ ---------------- ------ ------------------ D 288 .1 35,712 1.4 8,888,661 D 576 .2 71,424 1.3 8,467,880 D 864 .3 107,136 1.2 7,968,348 D 1,152 .4 142,848 1.2 7,729,305 D 1,440 .5 178,560 1.2 7,542,669 D 1,728 .6 214,272 1.1 7,342,834 D 2,016 .7 249,984 1.1 7,162,784 D 2,304 .8 285,696 1.1 6,843,539 D 2,592 .9 321,408 1.0 6,552,870 D 2,880 1.0 357,120 1.0 6,414,400 D 2,960 1.0 367,040 1.0 6,392,075 D 3,168 1.1 392,832 1.0 6,334,044 D 3,456 1.2 428,544 1.0 6,230,736 D 3,744 1.3 464,256 0.9 6,071,853 D 4,032 1.4 499,968 0.9 5,881,994 D 4,320 1.5 535,680 0.9 5,646,942 D 4,608 1.6 571,392 0.8 5,380,774 D 4,896 1.7 607,104 0.8 5,099,634 D 5,184 1.8 642,816 0.8 4,809,244 D 5,472 1.8 678,528 0.7 4,537,397 D 5,760 1.9 714,240 0.7 4,264,951 ------------------------------------------------------------- PGA Aggr Summary DB/Inst: PROD/PROD Snaps: 7814-7818 -> PGA cache hit % - percentage of W/A (WorkArea) data processed only in-memory PGA Cache Hit % W/A MB Processed Extra W/A MB Read/Written --------------- ------------------ -------------------------- 95.4 3,251 157 ------------------------------------------------------------- PGA Aggr Target Stats DB/Inst: PROD/PROD Snaps: 7814-7818 -> B: Begin snap E: End snap (rows dentified with B or E contain data which is absolute i.e. not diffed over the interval) -> 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 %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 807 602 266.3 0.0 .0 .0 .0 102,400 E 807 591 282.4 0.0 .0 .0 .0 102,400 ------------------------------------------------------------- PGA Aggr Target Histogram DB/Inst: PROD/PROD Snaps: 7814-7818 -> Optimal Executions are purely in-memory operations Low High Optimal Optimal Total Execs Optimal Execs 1-Pass Execs M-Pass Execs ------- ------- -------------- -------------- ------------ ------------ 2K 4K 17,195 17,195 0 0 64K 128K 37 37 0 0 128K 256K 20 20 0 0 256K 512K 23 23 0 0 512K 1024K 1,027 1,027 0 0 1M 2M 912 912 0 0 2M 4M 222 222 0 0 4M 8M 128 98 30 0 ------------------------------------------------------------- PGA Memory Advisory DB/Inst: PROD/PROD Snap: 7818 -> 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 ---------- ------- ---------------- ---------------- -------- ---------- 101 0.1 159,562.9 148,963.0 52.0 2,409 202 0.3 159,562.9 86,724.7 65.0 659 404 0.5 159,562.9 25,602.6 86.0 0 605 0.8 159,562.9 24,840.5 87.0 0 807 1.0 159,562.9 6,779.2 96.0 0 968 1.2 159,562.9 1,497.2 99.0 0 1,130 1.4 159,562.9 1,497.2 99.0 0 1,291 1.6 159,562.9 1,497.2 99.0 0 1,453 1.8 159,562.9 1,497.2 99.0 0 1,614 2.0 159,562.9 1,497.2 99.0 0 2,421 3.0 159,562.9 1,497.2 99.0 0 3,228 4.0 159,562.9 1,497.2 99.0 0 4,842 6.0 159,562.9 1,497.2 99.0 0 6,456 8.0 159,562.9 1,497.2 99.0 0 ------------------------------------------------------------- Shared Pool Advisory DB/Inst: PROD/PROD Snap: 7818 -> 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 ---------- ----- -------- ------------ ------- ------ ------- ------ ----------- 352 .6 58 3,124 ####### 1.0 15,115 1.2 40,036,939 416 .8 116 5,699 ####### 1.0 13,916 1.1 40,089,602 480 .9 176 6,638 ####### 1.0 13,021 1.1 40,134,489 544 1.0 239 8,344 ####### 1.0 12,281 1.0 40,170,990 608 1.1 302 9,522 ####### 1.0 11,721 1.0 40,199,588 672 1.2 365 13,708 ####### 1.0 11,272 .9 40,222,592 736 1.4 428 20,036 ####### 1.0 10,926 .9 40,239,745 800 1.5 491 26,446 ####### 1.0 10,665 .9 40,252,781 864 1.6 554 32,738 ####### 1.0 10,464 .9 40,263,104 928 1.7 617 39,572 ####### 1.0 10,309 .8 40,271,482 992 1.8 680 44,686 ####### 1.0 10,181 .8 40,278,622 1,056 1.9 744 49,521 ####### 1.0 10,065 .8 40,285,212 1,120 2.1 744 49,521 ####### 1.0 9,953 .8 40,291,631 ------------------------------------------------------------- SGA Target Advisory DB/Inst: PROD/PROD Snap: 7818 SGA Target SGA Size Est DB Est Physical Size (M) Factor Time (s) Reads ---------- ---------- ------------ ---------------- 888 0.3 115,525 8,670,817 1,776 0.5 109,430 7,772,734 2,664 0.8 106,383 7,162,932 3,552 1.0 102,607 6,392,051 4,440 1.3 100,873 5,922,874 5,328 1.5 97,456 4,974,294 6,216 1.8 93,147 4,160,586 7,104 2.0 91,802 4,160,586 ------------------------------------------------------------- Streams Pool Advisory DB/Inst: PROD/PROD Snap: 7818 No data exists for this section of the report. ------------------------------------------------------------- Java Pool Advisory DB/Inst: PROD/PROD Snap: 7818 No data exists for this section of the report. ------------------------------------------------------------- Buffer Wait Statistics DB/Inst: PROD/PROD Snaps: 7814-7818 -> ordered by wait time desc, waits desc Class Waits Total Wait Time (s) Avg Time (ms) ------------------ ----------- ------------------- -------------- data block 27 0 1 ------------------------------------------------------------- Enqueue Activity DB/Inst: PROD/PROD Snaps: 7814-7818 -> only enqueues with waits are shown -> Enqueue stats gathered prior to 10g should not be compared with 10g data -> ordered by Wait Time desc, Waits desc Enqueue Type (Request Reason) ------------------------------------------------------------------------------ Requests Succ Gets Failed Gets Waits Wt Time (s) Av Wt Time(ms) ------------ ------------ ----------- ----------- ------------ -------------- TX-Transaction (row lock contention) 10 10 0 10 0 1.40 ------------------------------------------------------------- Undo Segment Summary DB/Inst: PROD/PROD Snaps: 7814-7818 -> 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) Concurcy TR (mins) OOS eS/eR/eU ---- ---------- --------------- -------- -------- --------- ----- -------------- 1 1.4 7,009 2,448 3 30/51.8 0/0 0/0/0/0/0/0 ------------------------------------------------------------- Undo Segment Stats DB/Inst: PROD/PROD Snaps: 7814-7818 -> Most recent 35 Undostat rows, ordered by 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 ------------ ----------- ------------ ------- ------- ------- ----- ------------ 27-Jul 12:25 149 307 1,458 3 35 0/0 0/0/0/0/0/0 27-Jul 12:15 34 487 1,276 1 32 0/0 0/0/0/0/0/0 27-Jul 12:05 160 385 676 3 30 0/0 0/0/0/0/0/0 27-Jul 11:55 27 151 1,326 3 33 0/0 0/0/0/0/0/0 27-Jul 11:45 29 299 725 2 30 0/0 0/0/0/0/0/0 27-Jul 11:35 27 203 126 3 30 0/0 0/0/0/0/0/0 27-Jul 11:25 39 299 629 2 30 0/0 0/0/0/0/0/0 27-Jul 11:15 41 543 2,448 3 52 0/0 0/0/0/0/0/0 27-Jul 11:05 184 315 1,847 3 42 0/0 0/0/0/0/0/0 27-Jul 10:55 37 259 2,202 3 48 0/0 0/0/0/0/0/0 27-Jul 10:45 26 151 319 1 30 0/0 0/0/0/0/0/0 27-Jul 10:35 20 256 585 2 30 0/0 0/0/0/0/0/0 27-Jul 10:25 40 329 535 3 30 0/0 0/0/0/0/0/0 27-Jul 10:15 27 223 357 1 30 0/0 0/0/0/0/0/0 27-Jul 10:05 237 698 1,907 3 43 0/0 0/0/0/0/0/0 27-Jul 09:55 33 319 1,310 1 33 0/0 0/0/0/0/0/0 27-Jul 09:45 28 144 1,031 3 30 0/0 0/0/0/0/0/0 27-Jul 09:35 18 328 430 2 30 0/0 0/0/0/0/0/0 27-Jul 09:25 33 156 1,149 3 30 0/0 0/0/0/0/0/0 27-Jul 09:15 41 642 549 1 30 0/0 0/0/0/0/0/0 27-Jul 09:05 169 515 134 3 30 0/0 0/0/0/0/0/0 ------------------------------------------------------------- Latch Activity DB/Inst: PROD/PROD Snaps: 7814-7818 -> "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 Name Requests Miss /Miss (s) Requests Miss ------------------------ -------------- ------ ------ ------ ------------ ------ AWR Alerted Metric Eleme 57,484 0.0 N/A 0 0 N/A Consistent RBA 3,682 0.0 N/A 0 0 N/A FAL request queue 240 0.0 N/A 0 0 N/A FAL subheap alocation 240 0.0 N/A 0 0 N/A FIB s.o chain latch 32 0.0 N/A 0 0 N/A FOB s.o list latch 1,140 0.0 N/A 0 0 N/A In memory undo latch 54,907 0.0 0.0 0 12,631 0.0 JS mem alloc latch 51 0.0 N/A 0 0 N/A JS queue access latch 51 0.0 N/A 0 0 N/A JS queue state obj latch 86,520 0.0 N/A 0 0 N/A JS slv state obj latch 563 0.0 N/A 0 0 N/A KMG MMAN ready and start 4,022 0.0 N/A 0 0 N/A KMG resize request state 16 0.0 N/A 0 0 N/A KTF sga latch 13,284 0.0 N/A 0 3,325 0.0 KWQMN job cache list lat 991 3.0 0.9 0 0 N/A MQL Tracking Latch 0 N/A N/A 0 240 0.0 Memory Management Latch 182 0.0 N/A 0 4,022 0.0 NLS data objects 1 0.0 N/A 0 0 N/A OS process 2,451 0.0 N/A 0 0 N/A OS process allocation 5,640 0.0 0.0 0 0 N/A OS process: request allo 909 0.0 N/A 0 0 N/A PL/SQL warning settings 3,451 0.0 N/A 0 0 N/A QMT 1 0.0 N/A 0 0 N/A QOL Name Generation Latc 1,602 0.0 N/A 0 0 N/A SQL memory manager latch 4 0.0 N/A 0 3,973 0.0 SQL memory manager worka 275,815 0.0 N/A 0 0 N/A Shared B-Tree 488 0.0 N/A 0 0 N/A active checkpoint queue 7,205 0.0 N/A 0 0 N/A active service list 27,163 0.0 N/A 0 4,031 0.0 archive control 514 0.0 N/A 0 0 N/A archive process latch 4,213 0.0 N/A 0 0 N/A begin backup scn array 175 0.0 N/A 0 0 N/A buffer pool 16 0.0 N/A 0 0 N/A cache buffer handles 8,168,042 0.0 0.0 0 0 N/A cache buffers chains 65,494,648 0.0 0.0 0 41,221 0.0 cache buffers lru chain 70,317 0.0 N/A 0 27,109 0.0 cache table scan latch 0 N/A N/A 0 240 0.0 channel handle pool latc 1,075 0.1 0.0 0 0 N/A channel operations paren 84,095 0.0 N/A 0 0 N/A checkpoint queue latch 198,581 0.0 N/A 0 26,091 0.0 client/application info 7,385 0.0 N/A 0 0 N/A commit callback allocati 638 0.0 N/A 0 0 N/A compile environment latc 21,752 0.0 N/A 0 0 N/A dml lock allocation 61,060 0.1 0.0 0 0 N/A dummy allocation 1,291 0.1 0.0 0 0 N/A enqueue hash chains 289,586 0.0 0.0 0 76 0.0 enqueues 234,085 0.0 0.3 0 0 N/A event group latch 458 0.0 N/A 0 0 N/A file cache latch 795 0.0 N/A 0 0 N/A global KZLD latch for me 275 0.0 N/A 0 0 N/A hash table column usage 2,623 0.0 0.0 0 5,541,386 0.0 hash table modification 358 0.0 N/A 0 0 N/A job workq parent latch 0 N/A N/A 0 382 0.0 job_queue_processes para 391 0.0 N/A 0 0 N/A kks stats 11,882 0.0 N/A 0 0 N/A kmcptab latch 1 0.0 N/A 0 0 N/A kmcpvec latch 0 N/A N/A 0 1 0.0 ksuosstats global area 810 0.0 N/A 0 0 N/A ktm global data 102 0.0 N/A 0 0 N/A kwqbsn:qsga 488 0.0 N/A 0 0 N/A Latch Activity DB/Inst: PROD/PROD Snaps: 7814-7818 -> "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 Name Requests Miss /Miss (s) Requests Miss ------------------------ -------------- ------ ------ ------ ------------ ------ lgwr LWN SCN 5,964 0.0 0.0 0 0 N/A library cache 1,872,200 0.0 0.0 0 10,331 0.0 library cache load lock 17,056 0.0 N/A 0 0 N/A library cache lock 496,574 0.0 0.0 0 0 N/A library cache lock alloc 9,709 0.0 N/A 0 0 N/A library cache pin 1,102,742 0.0 0.0 0 13 0.0 library cache pin alloca 5,535 0.0 N/A 0 0 N/A list of block allocation 821 0.0 N/A 0 0 N/A loader state object free 692 0.0 N/A 0 0 N/A message pool operations 796 0.0 N/A 0 0 N/A messages 102,914 0.0 N/A 0 0 N/A mostly latch-free SCN 5,965 0.0 0.0 0 0 N/A multiblock read objects 736 0.0 N/A 0 0 N/A ncodef allocation latch 192 0.0 N/A 0 0 N/A object queue header heap 446 0.0 N/A 0 461 0.0 object queue header oper 112,920 0.0 N/A 0 0 N/A object stats modificatio 29 0.0 N/A 0 0 N/A parallel query alloc buf 1,592 0.0 N/A 0 0 N/A parameter list 153 0.0 N/A 0 0 N/A parameter table allocati 1,271 0.2 0.0 0 0 N/A post/wait queue 4,549 0.0 N/A 0 2,431 0.0 process allocation 909 0.0 N/A 0 458 0.0 process group creation 909 0.1 0.0 0 0 N/A qm_init_sga 1 0.0 N/A 0 0 N/A qmn task queue latch 1,712 0.0 N/A 0 0 N/A redo allocation 40,476 0.0 N/A 0 58,332 0.0 redo copy 0 N/A N/A 0 58,333 0.1 redo writing 26,645 0.0 0.0 0 0 N/A resmgr group change latc 1,460 0.0 N/A 0 0 N/A resmgr:actses active lis 1,322 0.0 N/A 0 0 N/A resmgr:actses change gro 548 0.0 N/A 0 0 N/A resmgr:free threads list 1,276 0.1 0.0 0 0 N/A resmgr:schema config 5 0.0 N/A 0 0 N/A row cache objects 10,349,933 0.0 0.0 0 3,756 0.0 rules engine aggregate s 122 0.0 N/A 0 0 N/A rules engine rule set st 244 0.0 N/A 0 0 N/A sequence cache 1,485 0.0 N/A 0 0 N/A session allocation 476,782 0.0 0.0 0 0 N/A session idle bit 577,857 0.0 0.0 0 0 N/A session state list latch 1,092 0.0 N/A 0 0 N/A session switching 192 0.0 N/A 0 0 N/A session timer 4,031 0.0 N/A ```