Oracle bottlenecks Identification
Oracle Tips by Burleson Consulting
Every Oracle database has at least one physical
constraint (bottleneck), and it is not always the disks. 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
Proactive tuning differs from ordinary Oracle
tuning because of the time dimension that allows the observation of
performance in a real-world fashion. Proactive tuning yields
complete “signatures”, patterns of behavior for all performance
metrics, and this is invaluable information that allows the
prediction of the future through the examination of the past. As
George Santayana said:
"Those who forget the past are doomed to repeat it."
Oracle does not run in a vacuum, and any tool must be able to detect
external bottlenecks from any area of the server environment:
scattered reads, such as full-table scans, and sequential reads,
such as index probes, are monitored.
SQL*Net metrics are monitored and can easily spot when an
application is network-bound.
The display tracks processor consumption.
Internally, the Oracle database has many shared resources and
potential bottlenecks, and Ion OEM and DBFlash visualize the
These waits result from serialized access latches.
Oracle touches the control file very frequently.
Segment header contention with the data buffers can cause
This can indicate internal latch and lock contention.
Events such as redo log space requests can cripple Oracle
Undo segments can cause serialization waits.
Internal locking and latching can cause contention.
By providing the experienced DBA with a detailed capability to slice
and dice wait interface data by SQL, User, Process and other
categories, these GUI tools provide unprecedented access to the wait
data vital to proper tuning.
One nice feature of third-party tools is the ability to quickly find
sub-optimal SQL statements. Any tuning tool must focus on the wait
events that comprise the total response time, both for the system
and for individual transactions.
Each submenu selection provides a graphical view of that cut across
the performance picture for that time frame. Figure 19.82 shows an
example slice for Oracle Events from the DBFlash tool for the 4:20
to 4:30 pm Time frame from Figure 19.81.
A ten minute
Time Slice for Oracle Event Waits
From Figure 19.82, it is possible to identify the contribution of
each event as it is clearly shown, in small, meaningful ten minute
periods. By selecting the Top SQL by resource utilization for the
same timeframe, as shown in Figure 19.83, it is simple to determine
the SQL statements that need attention.
Top SQL BY
As an alternative, the full SQL text can be displayed, as shown in
By clicking on any of the bar graphs or selecting the statement from
the menu tree at the left, the user is quickly shown the offending
SQL and given the opportunity to generate an explain plan.
Additional tuning actions are represented in Figures 19.85 and
Drill down on
These figures show some of the built-in intelligence of DBFlash.
The DBFlash tool recognizes the common causes of a scattered read
event and recommends some common solutions for the Oracle
The explain plan utility then provides the experienced DBA with the
information needed to resolve the issue. The other menu options
allow the DBA to isolate the offending SQL to a specific O/S or DB
user or a program or machine.
In using the product with an actual client, it quickly allows a
seasoned DBA to drill down to the problem SQL statements. Through
its explain plan access, it can also find and fix the issues with
The following section will present information on the Oracle10g OEM.
SEE CODE DEPOT FOR FULL SCRIPTS
This is an excerpt from my latest book "Oracle
Tuning: The Definitive Reference".
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts: