 |
|
Reporting on SQL from the Library Cache
Oracle Tips by Burleson Consulting |
As was introduced in Chapter 7, it is often
useful to execute the Oracle8i explain plan
statement on all SQL statements in the library cache, analyzing all
the execution plans, and providing reports on all table and index
access methods.
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:
-
Identifying high-use tables and indexes
See what tables and indexes 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 intended.
-
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.
The script is too long to reproduce in this
book, but the source code for the scripts in this book can be found
at www.oraclepress.com.
Here are the steps to execute this script:
1.
Download the access.sql and access_report.sql
scripts.
2.
Issue the following statements for the schema owner of your
tables:
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;
3.
Go into SQL*Plus, connect as the schema owner, and run
access.sql.
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 ten minutes for most Oracle databases.
Using the access.sql Script with STATSPACK
The access.sql script can be easily
modified to use the stats$sql_summary tables to extract and
explain historical SQL statements. All you need to do is change the
reference to v$sqlarea to stats_sql_summary, and add
the following to the WHERE clause:
FROM
stats$sql_summary s,
stats$snapshot sn
WHERE
s.snapshot_id = sn.snapshot_id
AND
sn,snapshot_id = (select max(snapshot_id) from stats$snapshot;
Of course, you can modify the access.sql
script to extract, explain, and report on any SQL in the
stats$sql_summary table. 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 is 100).
-
disk_reads_th This is the
number of disk reads performed by the SQL statement (default is
1000).
-
parse_calls_th This is the
number of parse calls performed by the SQL statement (default is
1000).
-
buffer_gets_th This is the
number of buffer gets performed by the SQL statement (default is
10,000).
Remember, a SQL statement will be included in
the stats$sql_summary table if any one of the
thresholds is exceeded. Now, let’s get back to access.sql and
look at the valuable reports.
The access.sql Reports
As I noted in Chapter 7, 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 into a single plan table. This plan table is then queried
to produce the report that follows.
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 up the
report 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 number
of statements that cannot be explained is high, 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
page 1
Total SQL found in library cache
23907
Total SQL that could not be explained
65
This is an excerpt from "Oracle High-Performance SQL Tuning" by
Donald K. Burleson, published by Oracle Press.
