Basics for Tuning SQL Statements

To get the maximum performance from our application, we need to
tune our sql statements.Tuning a sql statement means discovering the execution plan.
Once we know the execution plan,we can attempt to improve it.

Discovering the execution plan for a statement can be done in at least two ways
  * Issue an EXPLAIN PLAN statement from SQL* Trace.
  * Using SQL* PLUS Autotrace.

Using Explain Plan Feature :
-----------------------------------
Oracle's Explain Plan feature allows us to discover the
execution plan for a sql statement.This can be done by using a
sql statement,EXPLAIN PLAN, to which we append the query of interest.

Example :
EXPLAIN PLAN
SET statement_id='stmt_id'
FOR
SELECT * FROM table_name;

After issuing this statement, we can query a special table known
as PLAN TABLE to find the execution plan that oracle intends to use
for the query.Before using this table we need to create a plan table to
hold the results.

CREATING THE PLAN TABLE:
------------------------------------
 The easiest way to create a plan table is to use the Oracle supplied script "utlxplan.sql".
The script will be in the location $ORACLE_HOME/rdbms/admin directory.

Example:
SQL>@e:\oracle\ora92\rdbms\admin\utlxplan

The name of the table created by the script is PLAN_TABLE. This is also the default
table name used by EXPLAIN PLAN.

EXPLAINING A QUERY :
------------------------------
EXPLAIN PLAN
SET statement_id='user_defined_statement_id'
FOR query;

Note : Explaining the plan does not execute the sql statement.

DELETING DATA FROM PLAN_TABLE :
----------------------------------------------
DELETE
FROM plan_table
WHERE statement_id='user_defined_statement_id';

Deleting is necessary because EXPLAIN PLAN always adds to the plan table.
Deleting helpls to avoid possibilities of Duplication.

DISPLAYING THE EXECUTION PLAN FOR A QUERY:
---------------------------------------------------------------
 We can make use of the following query to display an execution plan once
it has been generated.
SELECT id,
   LPAD(' ', 2*(LEVEL-1)) || operation
                || ' ' || options
   || ' ' || object_name
                || ' ' ||
                 DECODE(id,0,'Cost = ' || position)   step_description
   FROM plan_table
                START WITH id=0 AND statement_id='statement_id' 
   CONNECT BY PRIOR id=parent_id
   AND statement_id = 'statement_id'
                ORDER BY id,position;

USING SQL*PLUS AUTOTRACE:
-------------------------------------
 The process is simple, we turn autotrace on and we issue the query as we normally would.

SQL> SET AUTOTRACE ON EXPLAIN
SQL> SELECT field_name1
FROM table_name
ORDER BY field_name1;

Using Autotrace is convinient because we don't need to manually query the plan
table, it is done automatically.However the query will be executed.
We can avoid seeing the results of the query executed by using TRACEONLY setting.

Example:
SQL> SET AUTORACE TRACEONLY EXPLAIN 

Note : Even when we use TRACEONLY, sql plus still sends the query to database where it is executed.
The TRACEONLY  setting just prevents sql plus from pulling back the results.