BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation










Reporting on SQL from the Library Cache

Oracle Tips by Burleson Consulting

This section 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.

This tool will be used extensively throughout this text, so it is important that you understand how this tool can be used to aid you in pursuit of suboptimal SQL statements.

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  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  To reduce I/O, you can locate large tables that have frequent index range scans in order to resequence the rows.

  • 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 http://www.osborne.com/oracle/code_archive.html. 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:

   stats$sql_summary s,
   stats$snapshot sn
   s.snapshot_id = sn.snapshot_id
   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 1,000).

  • parse_calls_th  This is the number of parse calls performed by the SQL statement (default is 1,000).

  • 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, 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.

You should then see a report similar to the one listed here. 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 your value for 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


Mon Jan 29                                                             page    1
                     Total SQL that could not be explained


This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald K. Burleson, published by Oracle Press.

Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.



Remote DBA Service

Oracle Tuning Book


Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software







BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.

Hit Counter