How To Run SQL Tuning Advisor For A Sql_id

It provides tuning recommendations that can be done that query to improve performance.

Suppose the sql id is – 5pp28g92z5ygg

1. Create Tuning Task

DECLARE

l_sql_tune_task_id VARCHAR2(100);

BEGIN

l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (

sql_id => '5pp28g92z5ygg',

scope => DBMS_SQLTUNE.scope_comprehensive,

time_limit => 500,

task_name => '5pp28g92z5ygg_tuning_task11',

description => 'Tuning task1 for statement 5pp28g92z5ygg');

DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);

END;

/

2- Execute Tuning task

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '5pp28g92z5ygg_tuning_task11');

3- Get the Tuning advisor report.

set long 65536

set longchunksize 65536

set linesize 100

select dbms_sqltune.report_tuning_task('5pp28g92z5ygg_tuning_task11') from dual;

4- Get list of tuning task present in database

SELECT TASK_NAME, STATUS FROM DBA_ADVISOR_LOG WHERE TASK_NAME ;

5- Drop tuning Task

execute dbms_sqltune.drop_tuning_task('5pp28g92z5ygg_tuning_task11');