Blog

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
Connected.


STEP 2: Run the SQL Advisor Manually


I. Create a tuning task

DECLARE
l_sql VARCHAR2(500);
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql := 'SELECT * ' ||
'FROM TESTTABLE3 ' ||
'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);
END;
/



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 LINES 150
SET pages 50000
SET long 5000000
SET longc 5000000

SELECT
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

OR

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.