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 and AI Architect with over 19 years in Oracle Applications, Databases, and Cloud ecosystems. My work spans large-scale, global projects where I’ve designed and optimized solutions across both Oracle and non-Oracle stacks—on-prem and in the cloud. Lately, my focus has shifted toward integrating AI into enterprise architectures, leveraging GenAI, automation, and data intelligence to drive performance, resilience, and modernization. I’m passionate about exploring emerging AI tools, agentic workflows, and practical use cases—and I'm fortunate that my role gives me space to experiment and share my learnings.

Script to execute SQL Tuning Advisor manually

Related posts: