BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation










Locate the High-Use SQL

Oracle Tips by Burleson Consulting

The first step is to locate the most frequently executed SQL statements. Since SQL may enter Oracle from a variety of sources (Pro*C programs, Visual Basic code, etc.), the Oracle Remote DBA must prepare an approach to sample the SQL that is currently in the library cache. There are two approaches to locating candidate SQL statement for tuning:

  • Use STATSPACK  The STATSPACK approach involves using the  stats$sql_summary table to capture SQL statements.

  • Fish through the library cache  This approach involves using utilities to explain all of the SQL that is currently in the library cache.

Let’s take a close look at each of these approaches.

Using the STATPACK to Capture SQL

When using STATSPACK, you will find that the stats$sql_summary table is the most highly populated of all of the STATSPACK tables. If your threshold values are set very low and you have a busy database, it's not uncommon to get several hundred rows added the stats$sql_summary table each and every time STATSPACK requests a snapshot. Hence, it is very important that the Remote DBA remove unwanted rows from the stats$sql_summary table once they are no longer used for SQL tuning. Remember though, that the SQL stored in the stats$sql_summary table is filtered by the thresholds stored in the stats$statspack_parameter table.

  •   executions_th  This is the number of executions of the SQL statement (default 100).

  • disk_reads_th  This is the number of disk reads performed by the SQL statement (default 1,000).

  • parse_calls_th  This is the number of parse calls performed by the SQL statement (default 1,000).

  • buffer_gets_th  This is the number of buffer gets performed by the SQL statement (default 10,000).

Remember, a SQL statement will be included in the stats$sql_summary table if any one of the thresholds is exceeded. Most Oracle Remote DBAs will schedule an hourly STATSPACK sample so that they will get a sample of all of the SQL that resides in the library cache at the time that the snapshot was gathered.

The major drawback to using STATSPACK for collecting SQL statements is the need to adjust the threshold values as your SQL tuning progresses. As you identify and tune SQL statements, you will need to lower the threshold values so that you can continue to tune the the less frequently executed SQL statements. Lowering the threshold causes more rows to be placed into the stats$sql_summary table and will cause the STATSPACK tablespace to fill rapidly. Most Oracle Remote DBAs will periodically delete the stats$sql_summary rows once they have tuned those SQL statements.

Fishing Through the Library Cache

Another popular approach to SQL tuning is to randomly extract SQL statements from the library cache. These are several techniques that are used to do this:

  • Using a third-party tool  Third-party GUI tools can be used to quickly display the SQL in the library cache and extract the most frequently executed and resource-intensive statements. These tools include Oracle’s Enterprise Manager Performance Pack, SQL*Lab, Q Diagnostic Center, SQL Expert, and many others.

  • Using SQL*Plus scripts  We will also explore a script called access.sql that will extract and explain all of the SQL that is currently in the library cache. We will go into great detail on access.sql in Chapter 9.

Once we have located the candidates for tuning, we proceed to individually tune each SQL statement.

This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald K. Burleson, published by Oracle Press.

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.



Remote DBA Service

Oracle Tuning Book


Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software







BC Remote Oracle Support

Remote DBA

Remote DBA Services

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

Oracle® is the registered trademark of Oracle Corporation.

Hit Counter