(10G) How to tune a SQL with DMBS_SQLTUNE

We must tune a select. Possibly we've got this select from the Statspack/ADDM report or from a developer team. In the first example it have no bind variables, only literals.

1) Select with literals:

DECLARE tarea_de_tuning varchar2(30); sql_txt clob;
BEGIN
sql_txt := 'select username from dba_users order by 1';
tarea_de_tuning := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => sql_txt,
user_name => 'OWNER',
scope => 'COMPREHENSIVE',
time_limit => 300, -- 5 minutes
task_name => 'Tuning',
description => 'Tuning');
END;
/

SQL> execute dbms_sqltune.execute_tuning_task (task_name => 'Tuning’);

OK, now we obtain the recommendations:

set long 50000
set pagesize 0
select dbms_sqltune.report_tuning_task('Tuning') from dual;


2) With Bind variables:

DECLARE tarea_de_tuning varchar2(30); sql_txt clob;
BEGIN
sql_txt := 'select username from dba_users where username > :b1';
tarea_de_tuning := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => sql_txt,
bind_list => sql_binds(anydata.ConvertVarchar2('SYSTEM')),
user_name => 'OWNER',
scope => 'COMPREHENSIVE',
time_limit => 300,
task_name => 'Tuning with Binds',
description => 'Tuning with Binds');
END;
/

execute dbms_sqltune.execute_tuning_task (task_name => 'Tuning with Binds’);

OK, again, we obtain the recommendations:

set long 5000
set pagesize 0
select dbms_sqltune.report_tuning_task('Tuning with Binds') from dual;


And, finally, drop the task:

exec DBMS_SQLTUNE.DROP_TUNING_TASK (task_name => 'Tuning’);