==================================
= 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;
