Performance - Query Tuning

• Create a plan table
• Autotrace
• Explain plan
• Find a query's hash
• Grab the sql associated with a hash
• Look at a query's stats in the sql area

Create a plan table

@?/rdbms/admin/utlxplan.sql 

Autotrace

To switch it on:

column plan_plus_exp format a100

set autotrace on explain   # Displays the execution plan only. 
set autotrace traceonly explain # dont run the query
set autotrace on  # Shows the execution plan as well as statistics of the statement. 
set autotrace on statistics  # Displays the statistics only. 
set autotrace traceonly      # Displays the execution plan and the statistics

To switch it off:

set autotrace off  

Explain plan

explain plan for
select ...

or...

explain plan set statement_id = 'bad1' for
select...

Then to see the output...

set lines 100 pages 999
@?/rdbms/admin/utlxpls

Find a query's hash

Put something unique in the like clause

select hash_value, sql_text
from v$sqlarea
where sql_text like '%TIMINGLINKS%FOLDERREF%'
/

Grab the sql associated with a hash

select sql_text
from v$sqlarea
where hash_value = '&hash'
/

Look at a query's stats in the sql area
select executions
, cpu_time
, disk_reads
, buffer_gets
, rows_processed
, buffer_gets / executions
from v$sqlarea
where hash_value = '&hash'
/