DBMS_SQLTUNE的使用方法

所需权限

grant advisor to user;

grant select_catalog_role to user;  --通过OEM管理必不可少

grant execute on dbms_sqltune to user;

获取绑定变量的方法

    此时分两种情况，一种是在v$sql中存在的SQL，一种是在v$sql中不存在的SQL

转化v$sql中对应的绑定变量值

   跟据SQL_ID取v$sql中bind_data的值

select position, value_string

 from table(dbms_sqltune.extract_binds('beda0a200500521ffd700'));

如果v$sql中不存在，可以用此方法获取绑定变量值

select dbms_sqltune.extract_bind(bind_data,1).value_string || '-' ||

      dbms_sqltune.extract_bind(bind_data, 2).value_string || '-' ||

      dbms_sqltune.extract_bind(bind_data, 3).value_string || '-' ||

      dbms_sqltune.extract_bind(bind_data, 4).value_string || '-' ||

      dbms_sqltune.extract_bind(bind_data, 5).value_string || '-' ||

      dbms_sqltune.extract_bind(bind_data, 6).value_string

from sys.wrh$_sqlstat

 where sql_id = '1nnrufx6sw8sd'

1、创建调优任务

1.1 通过sql_text创建（绑定变量SQL）

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  => 60,

        task_name   =>'test_sql_tuning',

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

END;

/

1.2 无绑定变量SQL的优化

declare

 my_task_name VARCHAR2(30);

 my_sqltext   CLOB;

BEGIN

 my_sqltext   := 'select * fromorabpel.cube_scope';

 my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_text    => my_sqltext,

                                                 user_name   => 'orabpel',

                  scope => 'COMPREHENSIVE',

                  time_limit => 60,  --优化限时60s

                  task_name   => 'wxw_sql_tuning_task',

                  description => 'tune thebad sql');

 dbms_sqltune.Execute_tuning_task(task_name =>'TEST_sql_tuning_task');

END;

1.3 通过SQL_ID进行优化

    这种情况通常常用于v$sql中的异常SQL优化

DECLARE 

    my_task_name VARCHAR2(40); 

BEGIN 

    my_task_name :=  DBMS_SQLTUNE.create_tuning_task(

        sql_id         => '1hudpukz651tt',

        plan_hash_value    => NULL,

        scope           =>  dbms_sqltune.SCOPE_COMPREHENSIVE,

        time_limit   =>  dbms_sqltune.TIME_LIMIT_DEFAULT,

        task_name    => 'test_sql_tuning_1hudpukz651tt',

        description   => '1hudpukz651tt'); 

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

    dbms_output.put_line(my_task_name); 

END; 

1.4 通过指定AWR中的Snap_id进行优化

declare

    my_task_name varchar2(30);

begin

    my_task_name := dbms_sqltune.create_tuning_task(

         begin_snap      => 24365,

         end_snap        => 24366,

         sql_id          =>'d40kghyfbg8sj',

         plan_hash_value => null,

         scope           =>'comprehensive',

         time_limit      => 60,

         task_name       =>'wxw_sql_tuning_task',

         description     => 'tune thebad sql'

    );

    dbms_sqltune.execute_tuning_task (task_name =>'wxw_sql_tuning_task');

 end;

2、执行调优任务

---execute

execdbms_sqltune.execute_tuning_task('TEST_tuning_task');

3、查看执行情况

---checek

SELECT status FROM USER_ADVISOR_TASKS WHERElower(task_name) ='test_tuning_task';

select * from user_advisor_tasks;

---report

SET LONG 999999

set serveroutput on size 999999

SET LINESIZE 1000

SELECTDBMS_SQLTUNE.REPORT_TUNING_TASK('TEST_tuning_task') FROM DUAL;

4、删除调优任务

---drop

execdbms_sqltune.drop_tuning_task('TEST_tuning_task');