Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

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 system bottleneck.

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."

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.

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.

 

Figure 19.82: 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.

 

Figure 19.83: Top SQL BY Resource Utilization

 

As an alternative, the full SQL text can be displayed, as shown in Figure 19.84.

Figure 19.84: SQL Text Display

 

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 19.86.

Figure 19.85: Drill down on SQL statement

 

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 professional.
 

Figure 19.86:  DBFlash SQL View Screen

 

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 statements.

 

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:

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

 


 

 

  
 

 
 
 
 
 

 
 
 
 
 
 
Oracle training Excel
 
Oracle performance tuning software 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2014

All rights reserved by Burleson

Oracle © is the registered trademark of Oracle Corporation.