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










Extracting SQL Source from the Library Cache

Oracle Tips by Burleson Consulting

This script is substantially similar to the STATSPACK extraction script except that the SQL is extracted directly from the v$sqlarea view. This report will rank and display all SQL that currently resides in the library cache, and it is a good starting point for investigating SQL when you do not have a STATSPACK database.


# First, we must set the environment . . . .
ORACLE_HOME=`cat /var/opt/oracle/oratab|\
   grep ^$ORACLE_SID:|cut -f2 -d':'`
export PATH

echo executions
echo loads
echo parse_calls
echo disk_reads
echo buffer_gets
echo rows_processed
echo sorts
echo "Enter sort key:"
read sortkey
$ORACLE_HOME/bin/sqlplus perfstat/perfstat<<!

set array 1;
set lines 80;
set wrap on;
set pages 999;
set echo off;
set feedback off;

column exec        format 9,999,999
column loads       format 999,999
column parse       format 999,999
column reads       format 9,999,999
column gets        format 9,999,999
column rows_proc   format 9,999,999
 column sorts       format 999,999

drop table temp2;

create table temp2 as
   executions                             exec,
   loads                                  loads,
   parse_calls                            parse,
   disk_reads                             reads,
   buffer_gets                            gets,
   rows_processed                         rows_proc,
   sorts                                  sorts,
order by
   $sortkey desc
spool off;

select * from temp2 where rownum < 11;


The output from this script is exactly the same as the output from the rpt_sql_STATSPACK.ksh script except that there is no display for the date. Next, let’s look at a method for extracting and explaining all of the SQL in your library cache.

Explaining all SQL in the Library Cache

One step that is commonly used to locate SQL is using a script to extract and explain all of the SQL that currently reside in the library cache. I will explain this procedure in detail in Chapter 8.

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 output from the access.sql script has several extremely useful reports. I will address them briefly here and go into greater detail in Chapter 8.

The Full-Table Scan Report

This is the most valuable report of all. Here you see all of the SQL statements that performed full-table scans, and the number of times that a full-table scan was performed. Also note the C and K columns. The C column indicates if an Oracle7 table is cached, and the K column indicates whether the Oracle8 table is assigned to the KEEP pool. As you will recall, small tables with full-table scans should be placed in the KEEP pool.

Mon Feb 29                                                   page    1
                          full table scans and counts
                   Note that "C" indicates the table is cached.
                   “K” indicates the table is in the KEEP Pool

OWNER          NAME                     NUM_ROWS C K   BLOCKS  NBR_FTS
-------------- -------------------- ------------ - - -------- --------
SYS            DUAL                              N          2   97,237
SYSTEM         SQLPLUS_PRODUCT                   N  K       2   16,178
DONALD         PAGE                    3,450,209 N    932,120    9,999
DONALD         RWU_PAGE                      434 N          8    7,355
DONALD         PAGE_IMAGE                 18,067 N      1,104    5,368
DONALD         SUBSCRIPTION                  476 N   K    192    2,087
DONALD         PRINT_PAGE_RANGE               10 N   K     32      874
ARSD           JANET_BOOKS                    20 N          8       64
PERFSTAT       STATS$TAB_STATS                   N         65       10

In the preceding report, you see several huge tables that are performing full-table scans. For tables that have less than 200 blocks and are doing legitimate full-table scans, we will want to place these in the KEEP pool. The larger table full-table scans should also be investigated, and the legitimate large-table full-table scans should be parallelized with the alter table parallel degree nn command.

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, 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, in Oracle7 you can cache the table by using the alter table xxx cache command, (where xxx = table name), which will put the table rows at the most recently used end of the data buffer, thereby reducing disk I/O for the table. In Oracle8, you should place cached tables in the KEEP pool with the alter table xxx storage (buffer_pool keep) command.

  • 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. Again, the original source for all the SQL statements is in the SQLTEMP table. I will talk about the process of finding and explaining the individual SQL statements in the next section.

Next, we see the index usage reports. These index reports are critical for the following areas of Oracle tuning:

Index usage  Ensuring that the application is actually using a new index. Remote DBAs can now obtain empirical evidence that an index is actually being used after it has been created.

Row resequencing  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.

Next, let’s look at the index range scan report.

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