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