grant ADVISOR  to xxxxxx;

DECLARE

   my_task_name   VARCHAR2 (30);

   my_sqltext     CLOB;

BEGIN

   my_sqltext := 'select * from emp where ename= :name and DEPTNO= :deptno';

   my_task_name :=

      DBMS_SQLTUNE.

       CREATE_TUNING_TASK (

         sql_text      => my_sqltext,

         bind_list     => sql_binds (anydata.convertvarchar2 (10),

                                     anydata.convertnumber (2)),

         user_name     => 'TEST',

         scope         => 'COMPREHENSIVE',

         time_limit    => 300,

         task_name     => 'test_sql_tuning',

         description   => 'Task to tune a query on emp');

END;

/

DECLARE

   my_task_name   VARCHAR2 (30);

BEGIN

   my_task_name :=

      DBMS_SQLTUNE.

       CREATE_TUNING_TASK (

         SQL_ID        => 'ddw7j6yfnw0vz',

         scope         => 'COMPREHENSIVE',

         time_limit    => 60,

         task_name     => 'tunning_task_ddw7j6yfnw0vz',

         description   => 'Task to tune a query on  ddw7j6yfnw0vz');

END;

/

--这里以调整调优任务时间为例

BEGIN

   DBMS_SQLTUNE.

    set_tuning_task_parameter (task_name   => 'my_sql_tuning_task',

                               parameter   => 'TIME_LIMIT',

                               VALUE       => 300);

END;

BEGIN

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

END;

/

SELECT status

  FROM USER_ADVISOR_TASKS

 WHERE task_name = 'test_sql_tuning';

SET LONG 10000

SET LONGCHUNKSIZE 1000

SET LINESIZE 100

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('test_sql_tuning')

FROM   DUAL;

SELECT * FROM DBA_SQL_PROFILES;

exec dbms_sqltune.drop_sql_profile('SYS_SQLPROF_014b39f084c88000');