Skip to content

Run SQL Tuning Adviser Manually

Find sqlid, begin and end snapshot and put in here 

declare
l_sql_tune_task_id varchar2(100);
begin
l_sql_tune_task_id := dbms_sqltune.create_tuning_task (
begin_snap => 5175,
end_snap => 5176,
sql_id => ‘7v5ac8zqn9y65’,
scope => dbms_sqltune.scope_comprehensive,
time_limit => 300,
task_name => ‘sql_tune’,
description => ‘task description’);
dbms_output.put_line(‘l_sql_tune_task_id: ‘ || l_sql_tune_task_id);
end;
/

Run sql tune 

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => ‘sql_tune’);

Find commendation

SELECT
DBMS_SQLTUNE.report_tuning_task(‘sql_tune’) AS recommendations
FROM dual;

Run recommendation if any

execute dbms_sqltune.accept_sql_profile(task_name => ‘sql_tune’,task_owner => ‘SYS’, replace => TRUE);

 

 

 

No comments yet

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: