General process for Query Tuning

Create a plan table

SQL>@?/rdbms/admin/utlxplan.sql

Autotrace

To switch it on:

SQL>column plan_plus_exp format a100

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


To switch it off:

SQL>set autotrace off

Explain plan

SQL>explain plan for
select ...

or...
SQL>explain plan set statement_id = 'bad1' for
select...

Then to see the output...
SQL>set lines 100 pages 999
SQL>@?/rdbms/admin/utlxpls

Put something unique in the like clause

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

Grab the sql associated with a hash

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


Look at a query's stats in the sql area

SQL>select executions
, cpu_time
, disk_reads
, buffer_gets
, rows_processed
, buffer_gets / executions
from v$sqlarea
where hash_value = '&hash'
/