- 12 Dec, 2021
- 0 Comments
- 1 Min Read
SQL TUNING ADVISOR
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;
