Oracle 10g:How to use SQL Tuning Advisor.

SQL Tuning Advisor

Dear User,

Read More about SQL Tuning Advisor from Oracle site

http://docs.oracle.com/cd/B28359_01/server.111/b28274/sql_tune.htm#CHDJDFGE

***Tuning SQL Queries by Oracle SQL Tuning Advisor***

A)SQL Query tuning by SQL_TEXT.

1.Get the Query info from AWR Report like  Top CPU Consuming Queries or SQL Queries by Elapsed time.

SQL>select SQL_TEXT,sql_id from v$sqlarea where sql_id='&sql_id';

2.Get bind variable details from V$SQL_BIND_CAPTURE or DBA_HIST_SQLBIND.

SQL>select address, hash_value,name,last_captured,DATATYPE_STRING,VALUE_STRING from  V$SQL_BIND_CAPTURE where sql_id='&sql_id'; order by last_captured;

SQL>select sql_id,name,last_captured,DATATYPE_STRING,VALUE_STRING from DBA_HIST_SQLBIND where sql_id='&sql_id'  and trunc(last_captured)=trunc(sysdate);

3.Prepare Query and add bind variable values(VALUE_STRING) into SQL Queries.Values should match with DATATYPE that mentioned in DATATYPE_STRING

4.Declare new SQL Tuning task.

Original Text with Bind variables.

SQ>SELECT ''Y'' FROM ***** WHERE ****= '':B3'' AND **** IN ('':B1'','':B2'')';

--Declate Task

DECLARE

 my_task_name VARCHAR2(30);

 my_sqltext   CLOB;

BEGIN

 my_sqltext := 'SELECT ''Y'' FROM **** WHERE ***= ''***'' AND *** IN (''B'',''C'')';

 my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(

         sql_text    => my_sqltext,

         user_name   => '&Schema Name',

         scope       => 'COMPREHENSIVE',

         time_limit  => 2000,

         task_name   => 'my_sql_tuning_task4',

         description => 'Task to tune a query on a specified employee');

END;

/

5.Execute task.
BEGIN
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task4' );
END;
/

6.Once the step 5 completed.Check SQL Tuning Advisor recommendation.


SQL>SET LONG 1000
SQL>SET LONGCHUNKSIZE 1000
SQL>SET LINESIZE 100
SQL>SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'my_sql_tuning_task4')
  FROM DUAL;

7.Drop Task if it is not benefits more than 80%.

  

Begin

  DBMS_SQLTUNE.DROP_TUNING_TASK('my_sql_tuning_task3');

 end;

 /
  B)SQL Query tuning by SQL_ID. 1.Save this queries to .sql file and provide  input.
INPUT SQL_ID


INPUT SQL_TUNING_TASK
Change spool location.


undefine tsk_name_of_tune;
undefine sql_id;


set head on;
set feedback on;
set pages 9000;
set lines 150;
set serveroutput on size unlimited;


DECLARE
 my_task_name VARCHAR2(30);
 my_sqltext   CLOB;
BEGIN
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
  sql_id          => '&&sql_id',
  plan_hash_value => null,
  scope           => 'COMPREHENSIVE',
  time_limit      => 1000,
  task_name       =>'&&tsk_name_of_tune',
  description     => 'Task to tune a query on a specified employee');
end;
/
clear scr ;

PROMPT ================================
PROMPT Executing the Tuning Task 
PROMPT ================================

BEGIN
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => '&&tsk_name_of_tune' );
END;
/


PROMPT ================================
PROMPT Fetching The Plan 
PROMPT ================================

spool /tmp/Month_end_Sep13/&&tsk_name_of_tune
SET LONG 9999999
SET LONGCHUNKSIZE 1000
SET LINESIZE 200
set pages 0

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( '&&tsk_name_of_tune') FROM DUAl;

spool off;

clear scr ; 

PROMPT ==================================================================================
PROMPT New PLan details are available in : /tmp/Month_end_Sep13/&&tsk_name_of_tune..lst
PROMPT ========================================================================i==========


PROMPT Execute the following cmd to accept the new plan
set long 5000;
col New_plan for a32765 word_wrap;

select  'exec dbms_sqltune.accept_sql_profile(task_name =>'||''''|| '&&tsk_name_of_tune'||''''||',name => '||''''||'&&tsk_name_of_tune'||''''||', replace => TRUE);'  from dual;