SQL Tuning Advisor - Performance Tuning

SQL Tuning Advisor

How to run SQL Tuning Advisor Manually

SQL Tuning Advisor is SQL diagnostic software in the Oracle Database Tuning Pack. SQL Tuning Advisor is a mechanism for resolving problems related to suboptimally performing SQL statements. Use SQL Tuning Advisor to obtain recommendations for improving performance of high-load SQL statements, and prevent regressions by only executing optimal plans.

STEP 1: Simulate the issue.

SQL> grant dba to HR;
SQL> connect HR/abc123

STEP 2: Run the SQL Advisor Manually

I. Create a tuning task

l_sql VARCHAR2(500);
l_sql_tune_task_id VARCHAR2(100);
l_sql := 'SELECT * ' ||
'WHERE id = 1';
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sql_text => l_sql,
user_name => 'PDBUSER4',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 60,
task_name => 'emp_dept_tuning_task2',
description => 'Tuning task for an TESTTABLE3 table.');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);

STEP 3: Check the status of newly created task:

SELECT task_name, status, execution_start
FROM dba_advisor_log WHERE owner = 'HR';

STEP 4: Execute your SQL Tuning task

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

STEP 5: Check the status again

SELECT task_name, status, execution_start
FROM dba_advisor_log WHERE owner = 'HR';

STEP 6: Review the recommendations by SQL Tuning Advisor

SET pages 50000
SET long 5000000
SET longc 5000000

DBMS_SQLTUNE.report_tuning_task('emp_dept_tuning_task2') AS recommendations
FROM dual;

STEP 7: You can also generate the apply script to apply the suggested optimization

SELECT dbms_sqltune.script_tuning_task('emp_dept_tuning_task2', 'ALL') FROM dual;

STEP 8: Implement the Recommendations

Note: Implement only after you satisfy the recommendations

Step 9: Drop_tuning_task

After review recommendations, we can remove the task by drop_tuning_task


If you are not satisfied with the result you can drop the tuning task with

EXECUTE dbms_sqltune.drop_tuning_task('emp_dept_tuning_task2');

******************************THE END***********************************************

Follow me

Contact us for Training/ Job Support

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.