This article has the following sections:

• Part 1 - Using SQL CBO Parameters
• Part 2 - Using CBO statistics
• Part 3 - Proper SQL development environment
• Part 4 - Using histograms to tune SQL
• Part 5 - Clustering and SQL tuning
• Part 6 - External costs and SQL execution
• Part 7 - Using hints to tune SQL
• Part 8 - Locating sub-optimal SQL

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).'
select
   p.owner,
   p.name,
   t.num_rows,
   ltrim(t.cache) ch,
   decode(t.buffer_pool,'KEEP','Y','DEFAULT','N') K,
   s.blocks blocks,
   sum(a.executions) nbr_FTS
from
   dba_tables   t,
   dba_segments s,
   v$sqlarea    a,
   (select distinct
     address,
     object_owner owner,
     object_name name
   from
      v$sql_plan
   where
      operation = 'TABLE ACCESS'
      and
      options = 'FULL') p
where
   a.address = p.address
   and
   t.owner = s.owner
   and
   t.table_name = s.segment_name
   and
   t.table_name = p.name
   and
   t.owner = p.owner
   and
   t.owner not in ('SYS','SYSTEM')
having
   sum(a.executions) > 9
group by
   p.owner, p.name, 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
  fbi_book
  on book
  (
  substr(book_key,1,3)
  ||
  to_char(book_retail_price)
  );
  

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:  

select
    sql_id,
    sql_exec_id,
    sql_exec_start,
    max(tm) tm,
    (sysdate-sql_exec_start) * 3600*24 ela_tm
from
    (select
       sql_id,
       sql_exec_id,
       sql_exec_start,
       ( ( Cast(sample_time AS DATE) ) -
             ( Cast(sql_exec_start AS DATE) ) ) * ( 3600 * 24 ) tm
     from
         v$active_session_history
     where
see code depot for full script       
order by
     sql_exec_start;

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

Donald K. Burleson [info@remote-dba.net] 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 http://www.dba-oracle.com , http://www.remote-dba.net/ and
http://rampant.cc .