DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sql_id => 'xxxxxxxxxxxxxxxx',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 1800,
task_name => 'TUNING_TASK_T9999',
description => 'Tuning task for statement xxxxxxxxxxxxxxxx');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

---

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'TUNING_TASK_T9999');

---

-- Get the report:
Spool xxxxxxxxxxxxxxxx.lst
SET LONG 10000;
SET PAGESIZE 10000
SET LINESIZE 3000
SELECT DBMS_SQLTUNE.report_tuning_task('TUNING_TASK_T9999') AS recommendations FROM dual;
Spool off
set lines 3000 DECLARE l_sql_tune_task_id VARCHAR2(100); BEGIN l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task ( sql_id => '&&1', scope => DBMS_SQLTUNE.scope_comprehensive, time_limit => 1800, task_name => 'TUNING_TASK_T&&2', description => 'Tuning task for statement &&1'); DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id); END; / EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'TUNING_TASK_T&&2'); Spool &&1.lst SET LONG 50000; SET PAGESIZE 50000 SET LINESIZE 6000 SELECT DBMS_SQLTUNE.report_tuning_task('TUNING_TASK_T&&2') AS recommendations FROM dual; Spool off
HOME