Performance - Query Tuning

Veremos aca algo de Tunning, el "QUE", "COMO", y "PARA QUE".

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

________________________________________________________

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'

______________________________________________________________
/

/