The best way to isolate the bottlenecks in Oracle is to analyze the top five wait events for the database and look for any external waits that might be associated with disk, CPU and network
The best way to see system-level wait summaries is to run the awrrpt.sql script from the $ORACLE_HOME/rdbms/admin directory.
This will yield the top 5 timed events for the specific interval between AWR snapshots
Top wait events can also be quickly identified by using the Top Wait Events report in Ion to see the overall system bottleneck
External Bottlenecks
Oracle does not run in a vacuum, and any tool must be able to detect
external bottlenecks from any area of the server environment:
§
Disk: Both
scattered reads, such as full-table scans, and sequential reads,
such as index probes, are monitored.
§
Network: The
SQL*Net metrics are monitored and can easily spot when an
application is network-bound.
§
CPU:
The display tracks processor consumption.
Internal Bottlenecks
Internally, the Oracle database has many shared resources and
potential bottlenecks, and Ion OEM and DBFlash visualize the
following metrics:
§
latch and lock waits:
These waits result from serialized access latches.
§
control file waits:
Oracle touches the control file very frequently.
§
buffer busy waits:
Segment header contention with the data buffers can cause
bottlenecks.
§
enqueue waits:
This can indicate internal latch and lock contention.
§
log buffer waits:
Events such as redo log space requests can cripple Oracle
performance.
§
undo segment waits:
Undo segments can cause serialization waits.
§
buffer deadlocks:
Internal locking and latching can cause contention.
http://www.databasejournal.com/features/oracle/article.php/3917631/Identifying-and-Evaluating-IO-Bottlenecks-in-Oracle-Database.htm
No comments:
Post a Comment