The SQL Tuning Toolkit
Oracle Tips by Burleson Consulting
Well, this will be whatever URL Oracle Press
uses to store the scripts from this book. It was my
understanding that Oracle Press on longer uses CD’s in favor of
keeping the contents on the web.
The basic scripts that we will introduce in
this chapter include the following:
access.sql This is a script that
will explain all of the SQL from the library cache and create a
series of reports showing the type of table access and table names
for all SQL that currently exists in the library cache.
This is a set of reports that will show various summaries of
SQL activity. This includes reports on full-table scans,
full-index scans, index range scans, and accesses by ROWID.
get_sql.sql This is
a simple script that will list all matching SQL statements from
the library cache.
plan.sql This is a
generic script that is used to display the execution plan for any
We will be returning to these scripts many
times during the course of this book, so this is a good time to
become familiar with these scripts.
Reporting on SQL
from the Library Cache
The Oracle Remote DBA must always be on the lookout
for new SQL statements that may appear in the library cache. This
section explores a technique that runs the Oracle explain plan
statement on all SQL statements in the library cache, analyzes all
the execution plans, and provides reports on all table and
At first glance, it may be hard to fully
appreciate the value of this technique and the information produced
by the reports. But if your database has a large library cache, you
can get some great insights into the internal behavior of the tables
and indexes. The information also offers some great clues about what
database objects you need to adjust. The reports are invaluable for
the following database activities:
high-use tables and indexes See what tables the database
accesses the most frequently.
Identifying tables for caching
You can quickly find small, frequently accessed tables for
placement in the KEEP pool (Oracle8) or for use with the CACHE
option (Oracle7). You can enhance the technique to automatically
cache tables when they meet certain criteria for the number of
blocks and the number of accesses. (I automatically cache all
tables with fewer than 200 blocks when a table has experienced
more than 100 full-table scans.)
Identifying tables for row resequencing
You can locate large tables that have frequent index-range
scans in order to resequence the rows, to reduce I/O.
Dropping unused indexes You can
reclaim space occupied by unused indexes. Studies have found that
an Oracle database never uses more than a quarter of all indexes
available or doesn't use them in the way for which they were
Stopping full-table scans by adding new
indexes Quickly find the full-table scans that you can
speed up by adding a new index to a table.
Here are the steps to execute the access.sql script:
Download the access.sql, access_report.sql, and
Issue the following statements for the schema owner of your
grant select on v_$sqltext to schema_owner;
grant select on v_$sqlarea to schema_owner;
grant select on v_$session to schema_owner;
grant select on v_$mystat to schema_owner;
Go into SQL*Plus, connect as the schema owner, and run
You must be signed on as the schema owner in order to
explain SQL statements with unqualified table names. Also, remember
that you will get statistics only for the SQL statements that
currently reside in your library cache. For very active databases,
you may want to run this report script several times—it takes less
than 10 minutes for most Oracle databases.
The access.sql Reports
As we noted, the access.sql script grabs all
of the SQL in the library cache and stores it in a table called
sqltemp. From this table, all of the SQL is explained and placed
into a single PLAN table. This PLAN table is then queried.
You should then see a report similar to the
one listed next. Let's begin by looking at the output this technique
provides, and then we'll examine the method for producing the
reports. For the purpose of illustration, let’s break the report up
into several sections. The first section shows the total number of
SQL statements in the library cache, and the total number that could
not be explained. Some statements cannot be explained because they
do not indicate the owner of the table. If the value for statements
that cannot be explained is high, then you are probably not
connected as the proper schema owner when running the script.
Report from access.sql
PL/SQL procedure successfully completed.
Mon Jan 29
Total SQL found in library cache
Mon Jan 29
that could not be explained
This is an excerpt from "Oracle High-Performance SQL Tuning" by
Donald K. Burleson, published by Oracle Press.