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;
Latest posts by Brijesh Gogia (see all)
- Oracle Database Service for Azure (ODSA) 3 – Database Services - May 1, 2023
- Oracle Database Service for Azure (ODSA) 2 – Interconnect Details - April 30, 2023
- Oracle Database Service for Azure (ODSA) 1 – Fundamentals - April 29, 2023