Commands to execute SQL tuning advisor based on SQL Text

DECLARE
l_sql VARCHAR2(32766);
l_sql_tune_task_id VARCHAR2(1000);
BEGIN
l_sql := ‘SELECT c1, c2 from Emp where c3=”Something”;
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sql_text => l_sql,
user_name => ‘scott’,
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 1800,
task_name => ‘Task_r10’,
description => ‘Task 10.’);
DBMS_OUTPUT.put_line(‘l_sql_tune_task_id: ‘ || l_sql_tune_task_id);
END;
/

————————-

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => ‘Task_r10’);

————————–

SET LONG 1000000;
SET PAGESIZE 50000
SET LINESIZE 180
SELECT DBMS_SQLTUNE.report_tuning_task(‘Task_r10’) AS recommendations FROM dual;

For executing SQL Tuning Advisor based on SQL_ID Click Here

Hope it helps!!!

Advertisements

Commands to execute SQL tuning advisor based on SQL ID

set serveroutput on

DECLARE
stmt_task VARCHAR2(64);
BEGIN
stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => ‘8bk0dw24d58jg’);
dbms_output.Put_line(stmt_task);
END;

——–

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => ‘TASK_1342’);

———

set long 9999999
SET PAGESIZE 1000
SET LINESIZE 32767
SELECT DBMS_SQLTUNE.report_tuning_task(‘TASK_1342’) AS recommendations FROM dual;

———

For executing SQL Tuning Advisor based on SQL Text Click Here