A brief write up on running the SQL Tuning Advisor. It seems that every time, I run an ADDM report I see output that includes “Run SQL Tuning Advisor on the SELECT statement with SQL_ID”
Findings and Recommendations
----------------------------
Finding 1: Top SQL Statements
Impact is .22 active sessions, 95% of total activity.
-----------------------------------------------------
SQL statements consuming significant database time were found. These
statements offer a good opportunity for performance improvement.
Recommendation 1: SQL Tuning
Estimated benefit is .22 active sessions, 95% of total activity.
----------------------------------------------------------------
Action
Run SQL Tuning Advisor on the SELECT statement with SQL_ID
"gbwan1836hj3c".
Related Object
SQL statement with SQL_ID gbwan1836hj3c.
SELECT time_key + (:"SYS_B_00" / :"SYS_B_01") period,
in_value,
out_value
FROM (SELECT time_key,
Create an SQL Tuning task as follows:
DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sql_id => 'gbwan1836hj3c',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 500,
task_name => 'gbwan1836hj3c_tuning_task',
description => 'Tuning task1 for statement gbwan1836hj3c');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/
Run the tuning task
EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'gbwan1836hj3c_tuning_task');
The above will take a couple of minutes. Depending on the complexity of the query, the time_limit may need to be increased.
After the task is complete, query the output:
set long 65536
set longchunksize 65536
set linesize 100
select dbms_sqltune.report_tuning_task('gbwan1836hj3c_tuning_task11') from dual;
I was using SQL Developer and the output will look a bit strange. Copy and paste it into Notepad to see it properly.