Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 








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:

  1. 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.
  2. 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:

  1. 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.
  2. 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;


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:



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).



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.



The report script in Listing 2 then reads and summarizes the execution plans from the plan_table.

Running the Report

For more information on Oracle database management, visit Donald Burleson's Web site at

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.


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 =

This would change the schema owner immediately before explaining the SQL statement (see the sidebar "Schema Owner").


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.


Copyright 2000 Oracle Corporation. All Rights Reserved.

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational