Oracle wait event
Oracle Tips by Burleson Consulting
How Does A Wait Bottleneck Get Fixed?
High waits on events do not always indicate a bottleneck or a
problem. As database users say: “time takes time.” In addition,
every Oracle database, no matter how well-tuned, spends time
performing activities. High waits may indicate a bottleneck, but
some waits are a normal part of database operations.
In general, the system wide wait events will show where the database
spends most of its time. For example, high waits on db file
sequential reads events may indicate a disk bottleneck, but the
average disk queue length for each disk spindle must be checked to
be sure that these waits are abnormal.
In one case, an Oracle shop ran the script for system-wide wait
events and discovered that their RAID-5 configuration was causing a
huge amount of disk enqueues. The shop reorganized their disk to
RAID 0+1 and experienced a 3x performance improvement for the whole
database. The following is just a small sample of some common wait
- SQL*Net waits: High SQL*Net waits could be due to poor
encapsulation of SQL statements within the application. For
example, a screen may need data from six different tables, and
there is much less network traffic and database overhead if all
of the information for an online screen is captured in a single
trip to the database. High SQL*Net waits can also signify an
error in the application programming logic or a serious network
- parallel query dequeue waits: The default degree of
parallelism for database objects need to be checked, and
parallelism at the system level should be turned off using
specific parallel hints. The value of parallel_threads_per_cpu
should be checked and adjusted to reduce automatic parallel
query and its influence on the CBO.
- db file scattered reads waits: These are caused by competing
demands for large-table full-table scans and are the most common
in data warehouse and decision support systems.
- db file sequential reads waits: These are sometimes due to
segment header contention on hot rows or indexes, but it could
also be due to disk-level contention. The first step in this
process is to increase the number of freelists on the indexes.
If the waits persist, the offending index should be striped
across multiple disk spindles. The DBA should check for segment
header contention/waits on index headers or create multiple
segment header blocks for stressed indexes using alter index xxx storage(freelists 4). The DBA could also distribute heavy impact
tables and indexes onto a faster disk or stripe the rows across
more data blocks by setting a high pctfree for a table and
reorganizing the table.
These are very general wait conditions, but they can sometimes be
fixed by changing parameters or object characteristics.
SEE CODE DEPOT FOR FULL SCRIPTS
If you like Oracle tuning, you may enjoy the book
Oracle Tuning: The Definitive Reference , with over 900 pages of BC's favorite tuning
tips & scripts.
You can buy it directly from the publisher and save 30%, and get
instant access to the code depot of Oracle tuning scripts.
Expert Remote DBA
BC is America's oldest and largest Remote DBA Oracle support
provider. Get real Remote DBA experts, call
BC Remote DBA today.