Blog

Learnomate Technologies > Blog > Oracle DBA > SQL TUNING ADVISOR

SQL TUNING ADVISOR

  • December 12, 2021
  • Posted by: Ankush Thavali
  • Category: Oracle DBA
No Comments

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***********************************************

SQL Tuning Advisor