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 






Oracle Technology Network
Cost Control: Inside the Oracle Optimizer

By Donald K. Burleson
OTN Member since 2001

Designing new applications for the Oracle Cost-Based Optimizer? Here's the latest information about how it works.

This article has the following sections:


Part 8 - Locating Sub-optimal SQL

While complex queries may have extremely complex execution plans, most Oracle professionals must tune SQL with the following problems:

  • Sub-optimal index access to a table — This problem occurs when the optimizer cannot find an index or the most restrictive where clause in the SQL is not matched with an index. When the optimizer cannot find an appropriate index to access table rows, the optimizer will always invoke a full-table scan, reading every row in the table. Hence, a large-table full-table scan might indicate a sub-optimal SQL statement that can be tuned by adding an index that matches the where clause of the query.
  • Sub-optimal join methods — The optimizer has many join methods available including a merge join, a nested loop join, hash join and a star join. To choose the right join method, the optimizer must guess at the size of the intermediate result sets from multi-way table joins. To make this guess, the optimizer has incomplete information. Even if histograms are present, the optimizer cannot know for certain the exact number of rows returned from a join. The most common remedy is to use hints to change the join (use_nl, use_hash) or re-analyze the statistics on the target tables.

Let's examine how the v$sql_plan view can help us locate SQL tuning opportunities. When searching for tuning opportunities, we start by interrogating the v$sql_plan view to find these large-table full-table scans as shown in Listing 3.

Listing 3:

column nbr_FTS  format 999,999
column num_rows format 999,999,999
column blocks   format 999,999
column owner    format a14;
column name     format a24;
column ch       format a1;

set heading on;
set feedback on;
set echo off;
set pages 999;

ttitle 'full table scans and counts|  |The "K" indicates that the table is in the KEEP Pool (Oracle8).'
   ltrim(t.cache) ch,
   decode(t.buffer_pool,'KEEP','Y','DEFAULT','N') K,
   s.blocks blocks,
   sum(a.executions) nbr_FTS
   dba_tables   t,
   dba_segments s,
   v$sqlarea    a,
   (select distinct
     object_owner owner,
     object_name name
      operation = 'TABLE ACCESS'
      options = 'FULL') p
   a.address = p.address
   t.owner = s.owner
   t.table_name = s.segment_name
   t.table_name =
   t.owner = p.owner
   t.owner not in ('SYS','SYSTEM')
   sum(a.executions) > 9
group by
   p.owner,, t.num_rows, t.cache, t.buffer_pool, s.blocks
order by
   sum(a.executions) desc;

Then, we extract the corresponding SQL and see if the full-table scan is warranted or due to a missing index.

How can we locate small tables that are subject to full-table scans? One method is to search the SQL that is currently in the library cache. Oracle can then generate a report that lists all the full-table scans in the database at that time. The script in Listing 3 examines the execution plans from v$sql_plan and reports on the frequency of full-table scans.

The report (see Listing 4) has the following columns:

  • OWNER — The schema owner for the table
  • NAME — The table name from dba_tables
  • NUM_ROWS — The number of rows in the table as of the last compute statistics from dba_tables
  • C (Oracle7 only) — An Oracle7 column that displays Y if the table is cached, N if it is not cached
  • K (Oracle8 and later only) — Displays "K" if the table is assigned to the KEEP pool
  • BLOCKS — Number of blocks in the table as defined in dba_segments
  • NBR_FTS — The number of full-table scans against the table (for SQL currently in the library cache).

Listing 4: Full table scans and counts:

OWNER      NAME                     NUM_ROWS C K   BLOCKS  NBR_FTS      
---------- -------------------- ------------ - - -------- --------      
APPLSYS    FND_CONC_RELEASE_DISJS         39 N K        2   98,864      
APPLSYS    FND_CONC_RELEASE_PERIODS       39 N K        2   98,864      
APPLSYS    FND_CONC_RELEASE_STATES         1 N K        2   98,864      
APPLSYS    FND_CONC_PP_ACTIONS         7,021 N      1,262   52,036      
APPLSYS    FND_CONC_REL_CONJ_MEMBER        0 N K       22   50,174      
APPLSYS    FND_CONC_REL_DISJ_MEMBER       39 N K        2   50,174      
APPLSYS    FND_FILE_TEMP                   0 N         22   48,611      
APPLSYS    FND_RUN_REQUESTS               99 N         32   48,606      
INV        MTL_PARAMETERS                  6 N K        6   21,478      
APPLSYS    FND_PRODUCT_GROUPS              1 N          2   12,555      
APPLSYS    FND_CONCURRENT_QUEUES_TL       13 N K       10   12,257      
AP         AP_SYSTEM_PARAMETERS_ALL        1 N K        6    4,521  

This report gives information about two tuning areas:

  • Tables & indexes for the KEEP pool—SQL speed may benefit from placing small tables (and associated indexes) that have frequent full-table scans in the KEEP pool. The report above shows full-table scans on both large and small tables. Examining this report, we can quickly identify possible candidates for the KEEP pool by selecting the tables with less than 50 blocks that have no "K" designation. Assigning a table, partition or index to the KEEP pool is easy, and objects can be added or removed at-will with alter system commands:
    alter table CUSTOMER storage (buffer_pool KEEP);


  • Possible missing indexes — Large-table full-table scans can sometimes indicate a missing index. Oracle function-based indexes are especially useful for this purpose because any where clause can be matched with a function-based index. For example, here is a function-based index that uses the substr and to_char BIFs:
    create index
    on book

In summary, the information contained in the v$sql_plan is a great way to perform system-wide SQL tuning.

This script will find all current "slow" SQL statements with a high execution time. 

You pass the minimum execution time as the exec_time argument:  

    max(tm) tm,
    (sysdate-sql_exec_start) * 3600*24 ela_tm
       ( ( Cast(sample_time AS DATE) ) -
             ( Cast(sql_exec_start AS DATE) ) ) * ( 3600 * 24 ) tm
see code depot for full script       
order by

Our Ion tool is the easiest way to analyze Oracle performance and Ion allows you to spot hidden performance trends.



Donald K. Burleson [] is one of the world's most widely-read Oracle database experts. He has written 19 books, published more than 100 articles in national magazines, and serves as editor-in-chief of Oracle Internals, a leading Oracle database journal. Burleson's latest book is Creating a Self-Tuning Database by Rampant TechPress. Don's Web sites are , and .




Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


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 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.