Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle




The SQL Tuning Process

Oracle Tips by Burleson

The process of tuning Oracle SQL is both iterative and time consuming. We begin by locating offensive SQL statements either by “fishing” them from the library cache or extracting them from the stats$sql_summary table.

Next, we explain the SQL, tune each statement, and make the changes permanent.

NOTE: If you tune your SQL by adding an index, you can go backwards in time and reexplain historical SQL in the stats$sql_summary table. This technique will verify that the new indexes have improved historical SQL.

There are several steps that are repeated until all major SQL is tuned:

1.      Locate offensive and high-impact SQL statements.

2.      Extract the offensive SQL syntax.

3.      Explain the SQL to get the execution plan.

4.      Tune the SQL with indexes and/or hints.

5.      Make the tuning permanent with stored outlines or by changing
the SQL source.

(2)Step 1: Identify High-Impact SQL in the Library Cache

We begin our investigation into Oracle SQL tuning by viewing the SQL that currently resides inside our library cache. Many people ask where they should start when tuning Oracle SQL. Tuning Oracle SQL is like a fishing expedition; you must first “fish” in the Oracle library cache to extract SQL statements, and rank the statements by their amount of activity.

Oracle makes it quite easy to locate frequently executed SQL statements. The SQL statements in the v$sqlarea view are rank ordered by several values:

·        rows_processedQueries that process a large number of rows will have high I/O, and may also have an impact on the TEMP tablespace.

·        buffer_getsHigh buffer gets may indicate a resource-intensive query.

·        disk_readsHigh disk reads indicates a query that is causing excessive I/O.

·        sortsSorts can be a huge slowdown, especially if the sorts are being done on disk in the TEMP tablespace.

·        executionsThe more frequently executed SQL statements should be tuned first, since they will have the greatest impact on overall performance.

Starting in Oracle, we see two new columns added to v$sqlarea:

  • cpu_timeThis column is useful for identifying computationally intensive SQL statements
  • elapsed_timeThis column identifies that total elapsed time (within Oracle) for the query execution.

You can get lists of SQL statements from the v$sqlarea view or stats$sql_summary table in descending order of any of these variables.

The executions column of the v$sqlarea view and the stats$sql_summary table can be used to locate the most frequently used SQL. When fishing for SQL, you can use a tool to display the SQL in the library cache. The next section will cover two ways to extract high-impact SQL:

·        Extract SQL from stats$sql_summary with a STATSPACK SQL top-10 report

·        Extract and analyze SQL from the library cache using access.sql.

Please note that either of these techniques can be used with either the historical STATSPACK sql_summary table or with the v$sqlarea view. The columns are identical.

In Oracle, the new v$sql_plan view will store the execution plans for all SQL and it is not necessary to extract and explain all of the SQL.

(3)STATSPACK SQL Top-10 Report

Here is an easy-to-use Korn shell script that can be run against the STATSPACK tables to identify high-use SQL statements.


L 11-27

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

echo “How many days back to search?”
read days_back

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 mydate      format a8
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 inval       format 9,999
column sorts       format 999,999

drop table temp1;
create table temp1 as
   select min(snap_id) min_snap
   from stats\$snapshot where snap_time > sysdate-$days_back;

drop table temp2;

create table temp2 as
   to_char(snap_time,'dd Mon HH24:mi:ss') mydate,
   executions                             exec,
   loads                                  loads,
   parse_calls                            parse,
   disk_reads                             reads,
   buffer_gets                            gets,
   rows_processed                         rows_proc,
   sorts                                  sorts,
   perfstat.stats\$sql_summary sql,
   perfstat.stats\$snapshot     sn
   sql.snap_id >
   (select min_snap from temp1)
   sql.snap_id = sn.snap_id
order by $sortkey desc
spool off;

select * from temp2 where rownum < 11;


Here is the listing from running this valuable script. Note that the DBA is prompted to define how many days back to search, and the define the sort key for extracting the SQL.


L 11-28

How many days back to search?

Enter sort key:

SQL*Plus: Release - Production on Thu Dec 14 09:14:46 2000

(c) Copyright 1999 Oracle Corporation. All rights reserved.

Connected to:
Oracle8i Enterprise Edition Release - 64bit Production
With the Partitioning option
JServer Release - 64bit Production

-------- ---------- -------- -------- ---------- ---------- ---------- -----SQL_TEXT
11 Dec 1        866        1      866    246,877  2,795,211        865        4:00:09
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;  broken BOOLEAN :
= FALSE; BEGIN statspack.snap; :mydate := next_date; IF broken THEN :b := 1;
  ELSE :b := 0; END IF; END;

11 Dec 1        863        1      863    245,768  2,784,834        862        1:00:29
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;  broken BOOLEAN :
= FALSE; BEGIN statspack.snap; :mydate := next_date; IF broken THEN :b := 1; ELS
E :b := 0; END IF; END;

11 Dec 1        866        1      866    245,325    597,647    129,993      866 4:00:09

11 Dec 0        861        1      861    245,029  2,778,052        860        0 9:00:24
DECLARE job BINARY_INTEGER := :job; next_date DATE := :mydate;  broken BOOLEAN :
= FALSE; BEGIN statspack.snap; :mydate := next_date; IF broken THEN :b := 1; ELSE
E :b := 0; END IF; END;

11 Dec 1        864        1      864    244,587    595,861    129,605      864 2:00:02


It is interesting to note in this output that we see the STATSPACK insert statement for the stats$sql_summary table. Next, let's look at a technique that is probably the most valuable script in this book.

(3)Reporting on SQL from the Library Cache

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.

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 indexesSee what tables the database accesses more frequently.

·        Identifying tables for cachingYou can quickly find small, frequently accessed tables for placement in the KEEP pool (Oracle8 and beyond) 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.)  In Oracle, you can dedicate specific buffer pools based on blocksize, and assign all tables to be cached into a tablespace of the same blocksize.  For example, you could create a db_2k_cache_size buffer pool, and assign all tables to be cached into a 2K tablespace.

Don: Should Oracle9 be added to the parenthses where Oracle8 is? Since this book is on Oracle 9, it should be mentioned somewhere here too. Don’t you think? –Lee  Fixed.

·        Identifying tables for row resequencingYou can locate large tables that have frequent index range scans in order to resequence the rows, to reduce I/O.

·        Dropping unused indexesYou 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 indexesQuickly 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 index.html?o60dba.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.

(4)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:

L 11-29

   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_thThis is the number of executions of the SQL statement (default is 100).

·        disk_reads_thThis is the number of disk reads performed by the SQL statement (default is 1,000).

·        parse_calls_thThis is the number of parse calls performed by the SQL statement (default is 1,000).

·        buffer_gets_thThis 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.

(4)The access.sql Reports

As we 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 in a single plan table. This plan table then queries to produce the following report.

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

(3)Report from access.sql

L 11-30

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


(4)The Full Table Scan Report

This is the most valuable report of all. Next we 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 we recall, small tables with full table scans should be placed in the KEEP pool.

L 11-31

Mon Jan 29                                                             page    1
                          full table scans and counts
                Note that “C” indicates in the table is cached.

OWNER          NAME                         NUM_ROWS C K   BLOCKS  NBR_FTS
-------------- ------------------------ ------------ - - -------- --------
SYS            DUAL                                  N          2   97,237
SYSTEM         SQLPLUS_PRODUCT_PROFILE               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 this report we 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, 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. (Note that in Oracle8 you should place cached tables in the KEEP pool.)

·        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. We 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 usageEnsuring 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.

·        Row resequencingFinding 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 resequenced into the same order as the index. Resequencing can result in a tenfold performance improvement, depending on the row length. For details on row resequencing techniques, see Chapter 10.

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

(4)The Index Range Scan Report

Here we see the report for index range scans. The most common method of index access in Oracle is the index 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.

L 11-32

Mon Jan 29                                                             page    1
                          Index range scans and counts

--------- -------------------- ------------------ ------------ ------------
DONALD    ANNO_HIGHLIGHT       HL_PAGE_USER_IN_IDX          16        7,975
DONALD    ANNO_STICKY          ST_PAGE_USER_IN_IDX           8        7,296
DONALD    PAGE                 ISBN_SEQ_IDX                120        3,859
DONALD    TOC_ENTRY            ISBN_TOC_SEQ_IDX             40        2,830
DONALD    PRINT_HISTORY        PH_KEY_IDX                   32        1,836
DONALD    SUBSCRIPTION         SUBSC_ISBN_USER_IDX         192          210
ARSD      JANET_BOOK_RANGES    ROV_BK_RNG_BOOK_ID_           8          170
PERFSTAT  STATS$SYSSTAT        STATS$SYSSTAT_PK            845           32
12 rows selected.

(4)The Index Unique Scan Report

Here is 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.

L 11-33

Mon Jan 29                                                             page    1
                         Index unique scans and counts

OWNER     TABLE_NAME           INDEX_NAME              NBR_SCANS
--------- -------------------- -------------------- ------------
DONALD    BOOK                 BOOK_ISBN                  44,606
DONALD    PAGE                 ISBN_SEQ_IDX               39,973
DONALD    BOOK                 BOOK_UNIQUE_ID              6,450
DONALD    ANNO_DOG_EAR         DE_PAGE_USER_IDX            5,339
DONALD    TOC_ENTRY            ISBN_TOC_SEQ_IDX            5,186
DONALD    PRINT_PERMISSIONS    PP_KEY_IDX                  1,836
DONALD    RDRUSER              USER_UNIQUE_ID_IDX          1,065
ARSD      JANET_BOOKS          BOOKS_BOOK_ID_PK               54
DONALD    ERROR_MESSAGE        ERROR_MSG_IDX                  48

(4)The Full Index Scan Report

The next report shows all index full scans. As we recall, the Oracle optimizer will sometimes perform an index full scan in lieu of a large sort in the TEMP tablespace. You will commonly see full index scans in SQL that have the ORDER BY clause.

L 11-34

Mon Jan 29                                                             page    1
                         Index full scans and counts

OWNER     TABLE_NAME           INDEX_NAME              NBR_SCANS
--------- -------------------- -------------------- ------------
DONALD    BOOK                 BOOK_ISBN                   2,295
DONALD    PAGE                 ISBN_SEQ_IDX                  744

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. The fast_full_scan_enabled parameter was discontinued in Oracle8i and had become the default behavior in Oracle. 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. The Oracle database engine commonly uses index full scans to avoid sorting. For example, if 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 init.ora parameter 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.

(4)Limitations of the access.sql Reports

The technique for generating these 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:

L 11-35

ALTER SESSION SET current_schema = 'tableowner';

This would change the schema owner immediately before explaining the SQL statement.

Now that we have covered the SQL reporting, let's move on to look at how the individual SQL statements are extracted and explained.


This is an excerpt from "Oracle High Performance tuning with STATSPACK" by Oracle Press.

If you like Oracle tuning, you may enjoy the new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.


Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books



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

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter