Script to execute SQL Tuning Advisor manually

The below script can be used to run SQL tuning advisor manually.

CREATE TUNING TASK

DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sql_id => '<PUT_SQL_ID>',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 500,
task_name => '<PUT_SQL_ID>_tuning_task11',
description => 'Tuning task1 for statement <PUT_SQL_ID>');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

EXECUTE TUNING TASK

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '<PUT_SQL_ID>_tuning_task11');

REPORT TUNING TASK

set long 65536
set longchunksize 65536
set linesize 100
select dbms_sqltune.report_tuning_task('<PUT_SQL_ID>_tuning_task11') from dual;

Author
Recent Posts

Brijesh Gogia

I’m an experienced Cloud/Oracle Applications/DBA Architect with more than 15 years of full-time DBA/Architect experience. I have gained wide knowledge on Oracle and Non-Oracle software stack running on-prem and on Cloud and have worked on several big projects for multi-national companies. I enjoy working with leading-edge technology and have a passion for Cloud architecture, automation, database performance, and stability. Thankfully my work allows me time for researching new technologies (and to write about them).

Script to execute SQL Tuning Advisor manually

Related posts: