DBA
Mining Gold from the Library
By Donald K. Burleson
Oracle Magazine
How to view the internal behavior of your database tables and
indexes, so you'll know which database objects to adjust to improve
performance.
One of the most confounding problems with Oracle8i is that it
doesn't offer an easy way for DBAs to count the number of times a database
accesses an index. Until now, you could create an index but you had no
sure way of knowing how many SQL statements actually accessed it. Another
ongoing problem is the identification of full-table scans for large
tables. The foremost database-tuning problem is the unnecessary full-table
scan, but it has always been nearly impossible for DBAs to obtain counts
of the tables that are experiencing them.
This article explores a technique that runs the Oracle8i EXPLAIN
PLAN statement on all SQL statements in the library cache, analyzes
all the execution plans, and provides reports on all table and
index-access methods. Listing 1
contains the full script, and
Listing 2 contains the script that produces the reports.
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 the
database accesses the most frequently.
- Identifying tables for caching 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 Locate large tables
that have frequent index-range scans in order to resequence the rows,
to reduce I/O.
- Dropping unused indexes 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 turbocharge by adding a new index to
a table.
Let's begin by looking at the output this technique provides, and then
we'll examine the method for producing the reports.
Index Full Scans
Although index full scans are usually faster than disk sorts,
you can use one of several init.ora parameters to
make index full scans even faster. These are the V77_plans_enabled
parameters, renamed fast_full_scan_enabled in
Oracle8. You can use a fast full scan as an alternative to a
full-table scan when an index contains all the columns needed for
a query. A fast index full scan is faster than a regular index
full scan because it uses multiblock I/O as defined by the db_file_multiblock_read_count
parameter. It can also accept a parallel hint in order to invoke a
parallel query, just like a full-table scan.
|
Table Reports
The report in Listing 3 shows
all table-access methods. It begins with a listing of all full-table scans,
showing the number of table rows, the number of table blocks, and the number of
full-table scans. From it, you can easily identify those tables that experience
full-table scans and the frequency of the scans.
An Oracle database invokes a large-table, full-table scan when it cannot
service a query through indexes. If you can identify large tables that
experience excessive full-table scans, then you can take appropriate action to
add indexes. This is especially important when you migrate from Oracle7 to
Oracle8, because Oracle8 offers indexes that have built-in functions. Another
cause of a full-table scan is when the cost-based optimizer decides that a
full-table scan will be faster than an index-range scan. This occurs most
commonly with small tables, which are ideal for caching in Oracle7 or placing in
the KEEP pool in Oracle8.
This full-table scan report is critical for two types of SQL tuning:
- For a small-table, full-table scan, cache the table by using the
ALTER
TABLE xxx CACHE command, which will put the table rows at the most
recently used end of the data buffer, thereby reducing disk I/O for the
table. (Note that in Oracle8 you should place cached tables in the KEEP
pool.) Note that the ? column in Listing
3 indicates whether the table is currently cached.
- For a large-table, full-table scan, you can investigate the SQL
statements, to see if the use of indexes would eliminate the full-table
scan. The original sources for all the SQL statements are the
plan_table
and sqltemp tables.
You can also view a report of table access by ROWID, to see
which tables in your database are active (see
Listing
4). Although the Oracle database engine supports the SELECT WHERE
ROWID=xxx syntax, it commonly performs a table access by ROWID
when it accesses a table via an index. As you may know, the terminal node of an
Oracle-database index contains the ROWID, and the database fetches the row based
on this ID. When rows are returned via an index range or unique scan, you will
see a table access by ROWID. Hence, the report in Listing
4 is useful for seeing which tables are most frequently accessed via index
scans.
Although these table reports are quite useful, the real value of this script
is in displaying the index-access methods. The index reports show not only the
tables that are accessed by indexes but also the index names and the types of
index access.
Index Reports
The script produces three useful reports that show index access for full
scans, range scans, and unique scans.
Full Scans Listing 5 shows
an index full-scan report. The Oracle database engine commonly uses index full
scans to avoid sorting. Say you have a customer table with an index on the cust_nbr
column. The database could service the SQL command Select * from customer
order by cust_nbr; in two ways:
- It could perform a full-table scan and then sort the result set. The
full-table scan could be performed very quickly with
db_file_muiltiblock_read_count
set, or the table access could be parallelized by using a parallel
hint. However, the result set must then be sorted in the TEMP
tablespace.
- It could obtain the rows in customer-number order by reading the rows via
the index, thus avoiding a sort.
Schema Owner
To maximize the number of SQL statements that are successfully
explained, you may want to connect as the schema owner when you run the
script. Also, you may need to issue the following grants to
the schema owner:
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;
|
The cost-based optimizer chooses an access method depending on the table
statistics. When the Oracle database engine chooses a full scan of the entire
index, the rows are returned in their proper order without causing a potentially
disk-intensive sort in the TEMP tablespace (see the sidebar "Index
Full Scans").
Range Scans The most common method of index access is the range scan. An
index range scan is used when the SQL statement contains a restrictive clause
that requires a sequential range of values that are indexes for the table.
Common range-scan clauses include:
Where customer_date > sysdate Ð7;
and
Where weight > 200;
Listing 6 shows an index
range-scan report.
This type of report is critical for the following areas of tuning:
- Ensuring that the application is actually using a new index. DBAs can now
obtain empirical evidence that an index is actually being used after it has
been created.
- Finding out which tables might benefit from row resequencing. Tables that
have a large amount of index-range-scan activity will benefit from having
the rows re- sequenced into the same order as the index. Resequencing can
result in a tenfold performance improvement, depending on the row length.
Unique Scans Listing 7
shows a report that lists index unique scans, which occur when the Oracle
database engine uses an index to retrieve a specific row from a table. The
Oracle database commonly uses these types of "probe" accesses when it
performs a join and probes another table for the join key from the driving
table. This report is also useful for finding out those indexes that are used to
identify distinct table rows as opposed to indexes that are used to fetch a
range of rows.
Let's examine the procedure for extracting all the information in these
reports.
The Method for Obtaining Execution Plans
Because the Oracle data dictionary does not contain information about the
behavior of SQL against tables, the only option is to go to the library cache,
which contains all the recently executed SQL statements. The script in Listings 1
and 2
was originally an unsupported script from Oracle. I ammended it to provide
detailed table- and index-access reports. It performs the following steps:
Collect
All the SQL statements in the library cache are extracted and placed in a
temporary table called sqltemp. This holding table keeps a record
of the number of disk reads, the number of executions, and the number of parse
calls (see Listing 8).
Explain
The SQL statements from the sqltemp table are explained, and the
execution plan is placed in the plan_table for the user who is
running the script. Any errors in explaining the SQL statements are stored in
the REMARKS column of the plan_table.
Report
The report script in Listing 2
then reads and summarizes the execution plans from the plan_table.
Running the Report
The script is designed to be run from SQL*Plus. Note that the DBA must sign
on as the owner of the schema objects. 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.
Limitations
The technique for generating the reports is not as flawless as it may appear.
Because the "raw" SQL statements must be explained in order to obtain
the execution plans, you may not know the owner of the tables. One problem with
native SQL is that the table names are not always qualified with the table
owner. To ensure that all the SQL statements are completely explained, many DBAs
sign on to Oracle and run the reports as the schema owner.
A future enhancement would be to issue the following undocumented command
immediately before each SQL statement is explained so that any Oracle-database
user could run the reports:
ALTER SESSION SET current_schema =
'tableowner';
This would change the schema owner immediately before explaining the SQL
statement (see the sidebar "Schema Owner").
Conclusion
The reports this technique produces are indispensable for verifying index
usage and identifying tables that are the target of full-table scans. Once you
have identified these tables, you can cache small tables and investigate and
tune larger full-table scans by adding indexes.
Although not perfect, these reports offer the best available way for DBAs to
view the database-execution plans. Until Oracle provides a metadata repository
for execution-plan statistics, the best you can do is analyze the SQL statements
residing in the library cache.
|
