利用sql_tuning_Advisor调优sql

1.赋权给调优用户

grant ADVISOR  to xxxxxx;

2.创建调优任务

使用sql_text创建

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

使用sql_id进行创建

DECLARE
   my_task_name   VARCHAR2 (30);
BEGIN
   my_task_name :=
      DBMS_SQLTUNE.
       CREATE_TUNING_TASK (
         SQL_ID        => 'ddw7j6yfnw0vz',
         scope         => 'COMPREHENSIVE',
         time_limit    => 300,
         task_name     => 'tunning_task_ddw7j6yfnw0vz',
         description   => 'Task to tune a query on  ddw7j6yfnw0vz');
END;
/

3.调整优化任务

--这里以调整调优任务时间为例
BEGIN
   DBMS_SQLTUNE.
    set_tuning_task_parameter (task_name   => 'my_sql_tuning_task',
                               parameter   => 'TIME_LIMIT',
                               VALUE       => 300);
END;

4.执行sql调优任务

BEGIN
   DBMS_SQLTUNE.EXECUTE_TUNING_TASK (task_name => 'test_sql_tuning');
END;
/

5.查看调优任务状态

SELECT status
  FROM USER_ADVISOR_TASKS
 WHERE task_name = 'test_sql_tuning';

6.查看调优任务结果

SET LONG 10000
SET LONGCHUNKSIZE 1000
SET LINESIZE 100
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('test_sql_tuning')
FROM   DUAL;

7.其他

7.1.sql_profile

查看数据库的sql_profile

SELECT * FROM DBA_SQL_PROFILES;

删除sql的sql_profile

exec dbms_sqltune.drop_sql_profile('SYS_SQLPROF_014b39f084c88000'); 

 7.2.删除调优

查看数据库中的调优任务

SELECT * FROM dba_advisor_tasks;
SELECT * FROM user_advisor_tasks;

删除调优任务

exec dbms_sqltune.drop_tuning_task('tunning_task_ddw7j6yfnw0vz');

7.3.将SQL踢出SharePool

-- 用SYS用户
SELECT A.SQL_ID,A.ADDRESS,A.HASH_VALUE  FROM V$SQLAREA A WHERE SQL_ID='chy30jy1cdg8q';
EXEC dbms_shared_pool.purge('00000000B6C61FC0,4094900530', 'c');

7.4.不分步的sql调优

/* Formatted on 2023/5/17 上午 09:31:36 (QP5 v5.163.1008.3004) */
DECLARE
   v_task_name   VARCHAR2 (30);
BEGIN
   v_task_name :=
      DBMS_SQLTUNE.CREATE_TUNING_TASK (
         sql_id       => '',
         task_name    => 'tunning_task_ddw7j6yfnw0vz',
         time_limit   => 600);
   DBMS_SQLTUNE.execute_tuning_task (v_task_name);
   DBMS_OUTPUT.put_line (v_task_name);
END;

SELECT DBMS_SQLTUNE.report_tuning_task ('tunning_task_ddw7j6yfnw0vz') FROM DUAL;