SQL Tunning

• 
  
  Tuning is a search for lost time
• 
  
  You need to identify where you are losing time and why. Then you can do something about it.
• 
  
  It nearly always comes down to a poorly performing SQL statement. The question is which statement and why.
•  An execution plan is a list of steps that Oracle will follow in order to execute a SQL statement. Each step is one of a finite number of basic operations known to the database server. Even the most complex SQL statement can be broken down into a series of basic operations.
• EXPLAIN PLAN is a statement that allows you to have Oracle generate the execution plan for any SQL statement without actually executing it. You will be able to examine the execution plan by querying the plan table.
• A plan table holds execution plans generated by the EXPLAIN PLAN statement.
• The typical name for a plan table is plan_table, but you may use any name you wish.
• Create the plan table by running utlxplan.sql, located in $ORACLE_HOME/rdbms/admin.

EXPLAIN PLAN


[SET STATEMENT_ID = ]

[INTO ]

FOR

;


******************************************
The autotrace feature in SQL*Plus


SET AUTOTRACE OFF ON TRACEONLY [EXPLAIN] [STATISTICS]


At the instance level:


sql_trace = true

timed_statistics = true (optional)

In your own session:

ALTER SESSION SET sql_trace = TRUE;

ALTER SESSION SET timed_statistics = TRUE; (optional)

In another session:

SYS.dbms_system.set_sql_trace_in_session

(, , TRUE)

Invoke TKPROF from the operating system prompt like this:




tkprof

\

[explain=] \

[sys=n] [insert=] \

[record=] [sort=]
 
  Examples:


1. tkprof ora_11598.trc /tmp/myfilename sys=no

2. tkprof ora_11598.trc /tmp/myfilename explain=ap/ap

3. tkprof ora_23532.trc myfilename sort=execpu (if timed_statistics=true)

How to get the values of Bind Variables:


Dump the event 10046 using,

In Init.ora set,

event = ‘10046 trace name context forever, level 4’;

OR

SQL> oradebug setospid (taken from V$PROCESS)

OR

SQL> oradebug event 10046 trace name context forever, level 4

TKProf does not know what data type bind variables. It assumes VARCHAR2. This may cause EXPLAIN PLAN to appear as though an index is not being used when in fact it is.