Search Oracle Related Sites

Wednesday, June 8, 2011

AWR Top 5 Timed Events - Quick Reference Guide

Wait Events Quick Reference Guide

    Wait Events                                                    Wait Problem Potential Fix

DB File Scattered Read       ----  Indicates many full table scans: tune the code; cache small tables.

DB File Sequential Read      ----  Indicates many index reads: tune the code (especially joins).

Free Buffer                           --- Increase the DB_CACHE_SIZE; shorten the checkpoint; tune the code.

Buffer Busy                          ---Segment header: add freelists or freelist groups.

Buffer Busy                          ---Data block: separate "hot" data; use reverse key indexes and/or smaller
                                                  blocks.

Buffer Busy                          ---Data block: increase initrans and/or maxtrans.

Buffer Busy                          ---Undo header: add rollback segments or areas.

Buffer Busy                          ---Undo block: commit more often; use larger rollback segments or areas.

Latch Free                            ---Investigate the latch detail.

Enqueue                               ---ST Use LMTs or preallocate large extents.

Enqueue                               ---HW Preallocate extents above high-water mark.

Enqueue                               ---TX4 Increase initrans and/or maxtrans on the table or index.

Enqueue                               ---TM Index foreign keys; check application locking of tables.

Log Buffer                            ---Space Increase the log buffer; use faster disks for the redo logs.

Log File                                ---Switch Archive destination slow or full; add more or larger redo logs.

Log File Sync                        --- Commit more records at a time; use faster redo log disks .

Idle Event                              ---Ignore it.

*** Potential Fixes discussed above are subject to change based on the version/feature.

No comments: