Using Oracle DBMS_SQLTUNE

A sample usage of DBMS_SQLTUNE

set timing on;
select count(*) from slow_table where col1 = ‘XYZ’ and col2 = ‘XYZ’;
— Elapsed: 00:00:06.631

declare
l_sql clob;
l_task_id clob;
begin
l_sql := ‘select count(*) from slow_table where col1 = :B1 and col2 = :B2’;
l_task_id := dbms_sqltune.create_tuning_task(
sql_text => l_sql,
bind_list => sql_binds(
anydata.ConvertVarchar2(‘XYZ’),
anydata.ConvertVarchar2(‘XYZ’)
),
user_name => ‘OWNER_NAME’,
scope => dbms_sqltune.scope_comprehensive,
time_limit => 300,
task_name => ‘sql_tuning_task_test_01’
);
dbms_output.put_line(l_task_id);
end;
/

select task_id, task_name, status from user_advisor_log where task_name = ‘sql_tuning_task_test_01’;
exec dbms_sqltune.execute_tuning_task(task_name => ‘sql_tuning_task_test_01’);
select task_id, task_name, status from user_advisor_log where task_name = ‘sql_tuning_task_test_01’;
select dbms_sqltune.report_tuning_task(‘sql_tuning_task_test_01’) as recommendations from dual;
exec dbms_sqltune.drop_tuning_task(task_name => ‘sql_tuning_task_test_01’);