AWR Sample Report

From: raja <dextersunil_at_gmail.com>
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.4

Elapsed: 0.52 (mins)
DB Time: 1.63 (mins)

Report Summary
Cache Sizes

	          Begin  End
Buffer Cache:     6,832M 6,832M Std Block Size: 8K

Shared 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.00

Parse 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.00

commit 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.82

physical 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.18

shared 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.64

write 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

This message: [ Message body ]
Next message: raja: "With Clause"
Previous message: Mladen Gogala: "Re: What is an edition?"
Next in thread: sybrandb: "Re: AWR Sample Report"
Reply: sybrandb: "Re: AWR Sample Report"
Reply: Charles Hooper: "Re: AWR Sample Report"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message