Re: AWR Sample Report

On 5 nov, 10:54, raja <dextersu..._at_gmail.com> wrote:
> 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.45Physical 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.

I don't think this is 'part' of an AWR report, it is almost all. Even worse: it is unformatted and barely legible.

Even worse: it is yet another attempt of you in this and various other forums to request others to abstract the online documentation. If you are not prepared to read the online documentation, Oracle is not for you. You can not expect volunteers to do for free what you can do easily on your own.

I would thank you in advance for not posting any RTFM questions in the furture or better still staying away here.

With regards,

--
Sybrand Bakker
Senior Oracle DBA