Oracle Consulting Oracle Training Oracle Support Development
Home
Catalog
Oracle Books
SQL Server Books
IT Books
Job Interview Books
eBooks
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
image
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
 Phone
 800-766-1884
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books
image
image

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle

STATSPACK Viewer

Privacy Policy

 

 
 

Why Wait Event Tuning for Oracle?

Wait event tuning has always been mysterious because the wait events happen so fast that it is often difficult to see what’s happening without taking a detailed task dump. Now with ASH, not only can specific details about individual sessions, SQL statements, and users be seen, but wait information can be tracked over periods of time, revealing the all important signatures that help schedule just-in-time resources to relieve instance related wait bottlenecks.

Time-based wait tuning is especially useful for tracking changes to SQL execution over time as well as database-wide stress. The Oracle ASH tables can be used to show trends in wait event over long time periods, and fluctuations in waits can often provide useful information.

A top-down approach starts at a high level and shows scripts that can be used to track system wide events and show events for background processes. Time-based wait event analysis for Oracle can be broken down into several areas:

  • External wait event analysis: The advent of the new mainframe-like SMP servers means that servers may share hardware resources with dozens of other applications and databases. These external resources demand server resources, such as RAM, CPU, and disk channels, which can affect Oracle waits, and the ASH tables provide insights into external network waits, such as SQL*Net waits, and disk waits such as db file sequential and scattered read waits.

  • Internal wait event analysis: Oracle internal mechanisms, such as buffer busy waits and latch waits, can change over time, depending on the overall database load. Time-series analysis with the ASH tables allows the DBA to find the cause of internal bottlenecks, even after the transaction has completed.

  • Session-level wait event analysis: Oracle resources associated with a session can be traced to the exact external or internal wait event.

  • SQL event analysis: In most OLTP systems, the same SQL statements may execute thousands of times per hour. Because SQL is the interface between applications and Oracle, tracing waits on an SQL statement over time can be especially useful.


When running the system-wide time-based wait event scripts, the DBA will be able to identify areas where the database is spending most of its time waiting. Before custom ASH scripts are presented, the following section provides a quick tour of the Oracle10g OEM as well as information on how it materializes the information from the ASH component.
 

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 50%-off and get instant access to the code depot of Oracle tuning scripts:

http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm

 

 

   

 Copyright © 1996 -2016 by Burleson. All rights reserved.


Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks