SQL Tuning

Tuning Tips

=========

1. Using NOT EXISTS instead of NOT IN adds a limiting condition to your queries that can reduce the number of full table scans necessary.

2. If Plan Table does not exist

$ORACLE_HOME/rdbms/admin/utlxplan.sql

X================================================================X

This script help to analyze the SQL ID and find the best optimal plan

set serveroutput on

set long 10000 longchunksize 5000

DECLARE

my_task_name VARCHAR2(30);

BEGIN

my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(

sql_id => 'f1ph9kjpb88va',

scope => 'COMPREHENSIVE',

time_limit => 3000,

task_name => 'balaji_tune',

description => 'Task to tune a query');

END;

/

exec dbms_sqltune.execute_tuning_task('balaji_tune');

---

set long 20000

set longchunksize 20000

set linesize 400

select dbms_sqltune.report_tuning_task('balaji_tune') from dual;

Reference : http://apunhiran.blogspot.com/2008/09/dbmssqltune-usage-example.html