SQL Tuning Advisor – Oracle
In Oracle time and again we hit sql performance, So in this blog we will see how to run a sql tuning advisor.
We have to perform three main tasks :
a) Create a tuning task.
b) Execute a tuning task.
c) Report the results of the tuning task.
optional: if we dont have a latest snapshot in the database please use this statment to create one :
sqlplus > EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;
Create a tuning task.
DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
begin_snap => 8550,
end_snap => 8552,
sql_id => ‘3p7qbwascyz7n’,
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 400,
task_name => ‘3p7qbwascyz7n_AWR_tuning_task’,
description => ‘Tuning task for statement 3p7qbwascyz7n in AWR.’);
DBMS_OUTPUT.put_line(‘l_sql_tune_task_id: ‘ || l_sql_tune_task_id);
END;
/
Execute a tuning task.
EXEC DBMS_SQLTUNE.execute_tuning_task (task_name => ‘3p7qbwascyz7n_AWR_tuning_task’);
Report the results of the tuning task.
SET LONG 100000000;
SET PAGESIZE 1000
SET LINESIZE 200
SELECT DBMS_SQLTUNE.report_tuning_task(‘3p7qbwascyz7n_AWR_tuning_task’) AS recommendations FROM dual;
SET PAGESIZE 24
Application: Based on the report of the tuning task please apply the recommendations only if the recommendation is 95% and above.
#sqlquery #sql #oracle #tuning