Creación de un SQL Tuning Advisor

==================================

= Eliminamos la tarea de tuning

==================================

exec dbms_sqltune.drop_tuning_task('task1');

==================================

= Creamos la tarea de tuning

==================================

DECLARE

tarea_tuning VARCHAR2(30);

sql CLOB;

BEGIN

sql := 'SELECT * from dual';

tarea_tuning := DBMS_SQLTUNE.CREATE_TUNING_TASK(

sql_text => sql,

user_name => USER,

scope => 'COMPREHENSIVE',

time_limit => 60,

task_name => 'task1',

description => 'prueba de tarea de tuning');

END;

/

/* hay un parametro mas de bind_list */

==================================

= Verificamos que la tarea este creada

==================================

SELECT task_name FROM DBA_ADVISOR_LOG WHERE owner = USER;

======================================

= Ejecutamos la tarea

======================================

BEGIN

DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'task1' );

END;

/

===================================

= Controlamos el estado de la tarea

===================================

SELECT task_id, status FROM USER_ADVISOR_TASKS WHERE task_name = 'task1';

======================================

= Controlamos el progreso del SQL Tuning Advisor

======================================

SELECT sofar, totalwork

FROM V$ADVISOR_PROGRESS

WHERE task_id = (SELECT task_id FROM USER_ADVISOR_TASKS WHERE task_name = 'task1');

=====================================

= Mostramos el resultado del SQL Tuning Advisor

=====================================

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'task1') FROM DUAL;