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.