AWR Sample Report
Date: Wed, 5 Nov 2008 01:54:20 -0800 (PST)
Message-ID: <f04e51c8-57d0-49b2-a3dd-368dd07be2ab@u18g2000pro.googlegroups.com>
Hi,
I have a small part ( output ) of AWR Report. Can anyone help me on this, to interpret the AWR Output...
Snap Id Snap Time Sessions Cursors/Session Begin Snap: 8193 17-Oct-08 16:00:54 90 62.7 End Snap: 8194 17-Oct-08 16:01:26 89 63.4Elapsed: 0.52 (mins)
DB Time: 1.63 (mins)
Report Summary
Cache Sizes
Begin End Buffer Cache: 6,832M 6,832M Std Block Size: 8KShared Pool Size: 1,264M 1,264M Log Buffer: 14,344K
Load Profile
Per Second Per Transaction Redo size: 42,200.64 120,268.00 Logical reads: 2,312.58 6,590.64 Block changes: 133.69 381.00 Physical reads: 359.82 1,025.45 Physical writes:540.97 1,541.73 User calls: 20.45 58.27 Parses: 8.93 25.45 Hard parses: 0.19 0.55 Sorts: 4.27 12.18 Logons: 0.03 0.09 Executes: 16.27 46.36 Transactions: 0.35
% Blocks changed per Read: 5.78 Recursive Call %: 67.11
Rollback per transaction %: 0.00 Rows per Sort: 2095.84
Instance Efficiency Percentages (Target 100%)
Buffer Nowait %: 100.00 Redo NoWait %: 100.00 Buffer Hit %: 84.50 In-memory Sort %: 100.00 Library Hit %: 97.78 Soft Parse %: 97.86 Execute to Parse %: 45.10 Latch Hit %: 100.00Parse CPU to Parse Elapsd %: 72.41 % Non-Parse CPU: 99.11
Shared Pool Statistics
Begin End Memory Usage %: 92.02 92.04
% SQL with executions>1: 99.23 99.08
% Memory for SQL w/exec>1: 99.18 99.24
Top 5 Timed Events
Event Waits Time(s) Avg Wait(ms) % Total Call Time Wait Class db file sequential read 11,529 32 3 32.6 User I/O CPU time 23 24.0 Log archive I/O 411 17 42 17.5 System I/O db file parallel write 674 12 18 12.6 System I/O Backup: sbtwrite2 491 8 15 7.7 Administrative
Wait Events
Event Waits %Time -outs Total Wait Time (s) Avg wait (ms) Waits /txn
db file sequential read 11,529 0.00 32 3 1,048.09 Log archive I/O 411 0.00 17 42 37.36 db file parallel write 674 0.00 12 18 61.27 Backup: sbtwrite2 491 0.00 8 15 44.64 log file sequential read 342 0.00 2 5 31.09 RMAN backup & recovery I/O 46 0.00 0 4 4.18 control file parallel write 30 0.00 0 5 2.73 log file parallel write 22 0.00 0 6 2.00 control file sequential read 19,914 0.00 0 0 1,810.36 log file sync 3 0.00 0 11 0.27 Backup: sbtbackup 1 0.00 0 22 0.09 direct path read 40 0.00 0 0 3.64 direct path write 40 0.00 0 0 3.64 SQL*Net more data to client 71 0.00 0 0 6.45 SQL*Net message to client 517 0.00 0 0 47.00 SQL*Net more data from client 8 0.00 0 0 0.73 direct path write temp 9 0.00 0 0 0.82 SQL*Net message from client 517 0.00 4,112 7954 47.00 Streams AQ: qmn slave idle wait 1 0.00 27 27344 0.09 Streams AQ: qmn coordinator idle wait 2 50.00 27 13672 0.18 jobq slave wait 3 100.00 9 2930 0.27
Background Wait Events
Event Waits %Time -outs Total Wait Time (s) Avg wait (ms) Waits / txn
db file parallel write 667 0.00 12 18 60.64 log file parallel write 22 0.00 0 6 2.00 control file parallel write 22 0.00 0 5 2.00 direct path read 40 0.00 0 0 3.64 direct path write 40 0.00 0 0 3.64 control file sequential read 17 0.00 0 0 1.55 rdbms ipc message 174 90.23 408 2347 15.82 pmon timer 10 100.00 29 2930 0.91 Streams AQ: qmn slave idle wait 1 0.00 27 27344 0.09 Streams AQ: qmn coordinator idle wait 2 50.00 27 13672 0.18
Operating System Statistics
Statistic Total NUM_LCPUS 0 NUM_VCPUS 0 AVG_BUSY_TIME 808 AVG_IDLE_TIME 2,298 AVG_IOWAIT_TIME 556 AVG_SYS_TIME 154 AVG_USER_TIME 652 BUSY_TIME 11,330 IDLE_TIME 32,202 IOWAIT_TIME 7,812 SYS_TIME 2,178 USER_TIME 9,152 LOAD 0 OS_CPU_WAIT_TIME 12,600
RSRC_MGR_CPU_WAIT_TIME 0
PHYSICAL_MEMORY_BYTES 82,678,116,352
NUM_CPUS 14 NUM_CPU_CORES 7
Instance Activity Stats
Statistic Total per Second per Trans
CPU used by this session 691 22.04 62.82
CPU used when call started 874 27.88 79.45
CR blocks created 5 0.16 0.45
Cached Commit SCN referenced 39,329 1,254.55 3,575.36
Commit SCN cached 3 0.10 0.27
DB time 11,961 381.54 1,087.36
DBWR checkpoint buffers written 9,285 296.18 844.09
DBWR checkpoints 0 0.00 0.00
DBWR object drop buffers written 0 0.00 0.00
DBWR parallel query checkpoint buffers written 0 0.00 0.00
DBWR revisited being-written buffer 0 0.00 0.00
DBWR tablespace checkpoint buffers written 0 0.00 0.00
DBWR thread checkpoint buffers written 0 0.00 0.00
DBWR transaction table writes 0 0.00 0.00
DBWR undo block writes 3,343 106.64 303.91
DFO trees parallelized 0 0.00 0.00
IMU CR rollbacks 0 0.00 0.00
IMU Flushes 3 0.10 0.27
IMU Redo allocation size 30,844 983.89 2,804.00
IMU commits 8 0.26 0.73
IMU contention 0 0.00 0.00
IMU ktichg flush 0 0.00 0.00
IMU pool not allocated 0 0.00 0.00
IMU recursive-transaction flush 0 0.00 0.00
IMU undo allocation size 61,992 1,977.48 5,635.64
IMU- failed to get a private strand 0 0.00 0.00
Misses for writing mapping 0 0.00 0.00
PX local messages recv'd 0 0.00 0.00
PX local messages sent 0 0.00 0.00
Parallel operations not downgraded 0 0.00 0.00
SMON posted for undo segment shrink 0 0.00 0.00
SQL*Net roundtrips to/from client 503 16.05 45.73
SQL*Net roundtrips to/from dblink 0 0.00 0.00
active txn count during cleanout 35 1.12 3.18
application wait time 0 0.00 0.00
background checkpoints completed 1 0.03 0.09
background checkpoints started 0 0.00 0.00
background timeouts 160 5.10 14.55
branch node splits 0 0.00 0.00
buffer is not pinned count 45,762 1,459.76 4,160.18
buffer is pinned count 418,313 13,343.74 38,028.45
bytes received via SQL*Net from client 82,383 2,627.93 7,489.36
bytes received via SQL*Net from dblink 0 0.00 0.00
bytes sent via SQL*Net to client 271,245 8,652.43 24,658.64
bytes sent via SQL*Net to dblink 0 0.00 0.00
calls to get snapshot scn: kcmgss 847 27.02 77.00
calls to kcmgas 106 3.38 9.64
calls to kcmgcs 58 1.85 5.27
change write time 5 0.16 0.45
cleanout - number of ktugct calls 300 9.57 27.27
cleanouts and rollbacks - consistent read gets 0 0.00 0.00
cleanouts only - consistent read gets 264 8.42 24.00
cluster key scan block gets 125 3.99 11.36
cluster key scans 63 2.01 5.73
commit batch performed 0 0.00 0.00
commit batch requested 0 0.00 0.00
commit batch/immediate performed 0 0.00 0.00
commit batch/immediate requested 0 0.00 0.00
commit cleanout failures: block lost 0 0.00 0.00 commit cleanout failures: buffer being written 0 0.00 0.00 commit cleanout failures: callback failure 2 0.06 0.18 commit cleanout failures: cannot pin 0 0.00 0.00commit cleanouts 331 10.56 30.09
commit cleanouts successfully completed 329 10.49 29.91 commit immediate performed 0 0.00 0.00
commit immediate requested 0 0.00 0.00
commit txn count during cleanout 303 9.67 27.55 concurrency wait time 0 0.00 0.00
consistent changes 9 0.29 0.82
consistent gets 69,089 2,203.87 6,280.82 consistent gets - examination 3,549 113.21 322.64 consistent gets direct 0 0.00 0.00
consistent gets from cache 69,089 2,203.87 6,280.82 current blocks converted for CR 0 0.00 0.00 cursor authentications 4 0.13 0.36
data blocks consistent reads - undo records applied 5 0.16 0.45 db block changes 4,191 133.69 381.00
db block gets 3,357 107.08 305.18
db block gets direct 0 0.00 0.00
db block gets from cache 3,357 107.08 305.18 deferred (CURRENT) block cleanout applications 250 7.97 22.73 dirty buffers inspected 3,050 97.29 277.27
enqueue conversions 9 0.29 0.82 enqueue releases 474 15.12 43.09 enqueue requests 477 15.22 43.36
enqueue timeouts 0 0.00 0.00
enqueue waits 0 0.00 0.00
execute count 510 16.27 46.36
failed probes on index block reclamation 0 0.00 0.00 free buffer inspected 13,135 418.99 1,194.09 free buffer requested 11,327 361.32 1,029.73 heap block compress 24 0.77 2.18
hot buffers moved to head of LRU 4,565 145.62 415.00 immediate (CR) block cleanout applications 264 8.42 24.00 immediate (CURRENT) block cleanout applications 59 1.88 5.36 index crx upgrade (found) 0 0.00 0.00
index crx upgrade (positioned) 35 1.12 3.18 index fast full scans (full) 2 0.06 0.18 index fast full scans (rowid ranges) 0 0.00 0.00 index fetch by key 1,416 45.17 128.73
index scans kdiixs1 528 16.84 48.00
java call heap collected bytes 0 0.00 0.00 java call heap collected count 0 0.00 0.00 java call heap gc count 0 0.00 0.00
java call heap live object count 0 0.00 0.00 java call heap live object count max 0 0.00 0.00 java call heap live size 0 0.00 0.00
java call heap live size max 0 0.00 0.00 java call heap object count 0 0.00 0.00
java call heap object count max 0 0.00 0.00 java call heap total size 0 0.00 0.00
java call heap total size max 0 0.00 0.00 java call heap used size 0 0.00 0.00
java call heap used size max 0 0.00 0.00 leaf node 90-10 splits 6 0.19 0.55
leaf node splits 9 0.29 0.82
lob reads 1 0.03 0.09
lob writes 75 2.39 6.82
lob writes unaligned 75 2.39 6.82
logons cumulative 1 0.03 0.09
messages received 706 22.52 64.18
messages sent 706 22.52 64.18
no buffer to keep pinned count 0 0.00 0.00 no work - consistent read gets 64,846 2,068.52 5,895.09 opened cursors cumulative 269 8.58 24.45
parse count (failures) 0 0.00 0.00 parse count (hard) 6 0.19 0.55 parse count (total) 280 8.93 25.45
parse time cpu 21 0.67 1.91
parse time elapsed 29 0.93 2.64
physical read IO requests 11,280 359.82 1,025.45 physical read bytes 92,405,760 2,947,646.18 8,400,523.64 physical read total IO requests 31,707 1,011.42 2,882.45 physical read total bytes 755,145,216 24,088,335.07 68,649,565.09 physical read total multi block requests 482 15.38 43.82 physical reads 11,280 359.82 1,025.45
physical reads cache 11,240 358.54 1,021.82 physical reads cache prefetch 0 0.00 0.00 physical reads direct 40 1.28 3.64
physical reads direct (lob) 0 0.00 0.00
physical reads direct temporary tablespace 0 0.00 0.00 physical reads prefetch warmup 0 0.00 0.00 physical write IO requests 3,598 114.77 327.09
physical write bytes 138,928,128 4,431,660.60 12,629,829.82 physical write total IO requests 3,942 125.75 358.36 physical write total bytes 472,314,368 15,066,329.64 42,937,669.82physical write total multi block requests 1,960 62.52 178.18 physical writes 16,959 540.97 1,541.73
physical writes direct 190 6.06 17.27
physical writes direct (lob) 0 0.00 0.00 physical writes direct temporary tablespace 150 4.78 13.64 physical writes from cache 16,769 534.91 1,524.45 physical writes non checkpoint 12,468 397.72 1,133.45 pinned buffers inspected 0 0.00 0.00
prefetch warmup blocks aged out before use 0 0.00 0.00 prefetched blocks aged out before use 0 0.00 0.00 process last non-idle time 17 0.54 1.55
queries parallelized 0 0.00 0.00
recursive aborts on index block reclamation 0 0.00 0.00 recursive calls 1,308 41.72 118.91
recursive cpu usage 364 11.61 33.09
redo blocks written 2,778 88.62 252.55
redo buffer allocation retries 0 0.00 0.00 redo entries 2,947 94.01 267.91
redo log space requests 0 0.00 0.00
redo log space wait time 0 0.00 0.00
redo ordering marks 55 1.75 5.00
redo size 1,322,948 42,200.64 120,268.00 redo subscn max counts 414 13.21 37.64
redo synch time 6 0.19 0.55
redo synch writes 104 3.32 9.45
redo wastage 6,808 217.17 618.91
redo write time 18 0.57 1.64
redo writer latching time 0 0.00 0.00
redo writes 25 0.80 2.27
rollback changes - undo records applied 0 0.00 0.00 rollbacks only - consistent read gets 5 0.16 0.45 rows fetched via callback 1,087 34.67 98.82 session connect time 0 0.00 0.00
session cursor cache hits 173 5.52 15.73 session logical reads 72,497 2,312.58 6,590.64
session pga memory 66,216,800 2,112,246.00 6,019,709.09 session pga memory max 33,121,120 1,056,528.76 3,011,010.91 session uga memory 82,856,048 2,643,020.45 7,532,368.00 session uga memory max 87,060,448 2,777,136.37 7,914,586.18shared hash latch upgrades - no wait 139 4.43 12.64 shared hash latch upgrades - wait 0 0.00 0.00
sorts (disk) 0 0.00 0.00 sorts (memory) 134 4.27 12.18 sorts (rows) 280,843 8,958.60 25,531.18
sql area evicted 0 0.00 0.00
sql area purged 0 0.00 0.00
summed dirty queue length 4,669 148.94 424.45 switch current to new buffer 2 0.06 0.18 table fetch by rowid 231,204 7,375.16 21,018.55 table fetch continued row 3 0.10 0.27
table scan blocks gotten 18,943 604.26 1,722.09 table scan rows gotten 1,789,586 57,085.90 162,689.64
table scans (cache partitions) 0 0.00 0.00 table scans (direct read) 0 0.00 0.00 table scans (long tables) 0 0.00 0.00 table scans (rowid ranges) 0 0.00 0.00 table scans (short tables) 53 1.69 4.82
total number of times SMON posted 0 0.00 0.00 transaction rollbacks 0 0.00 0.00
transaction tables consistent read rollbacks 0 0.00 0.00 transaction tables consistent reads - undo records applied 0 0.00 0.00
undo change vector size 285,652 9,112.00 25,968.36 user I/O wait time 3,105 99.05 282.27
user calls 641 20.45 58.27
user commits 11 0.35 1.00
user rollbacks 0 0.00 0.00
workarea executions - multipass 0 0.00 0.00 workarea executions - onepass 0 0.00 0.00 workarea executions - optimal 117 3.73 10.64write clones created in background 0 0.00 0.00 write clones created in foreground 0 0.00 0.00
Buffer Pool Statistics
P Number of Buffers Pool Hit% Buffer Gets Physical Reads Physical Writes Free Buff Wait Writ Comp Wait Buffer Busy Waits D 845,033 85 75,249 11,310 16,769 0 0 0
PGA Aggr Summary
PGA Cache Hit % W/A MB Processed Extra W/A MB Read/Written 100.00 28 0
Please explain me the AWR Report.
Thanks in Advance.
With Regards,
Raja.
Received on Wed Nov 05 2008 - 03:54:20 CST