SQL Access Advisor（SQL访问顾问）

SQL Access Advisor是一种调优工具，它可提供有关物化视图、索引、物化视图日志和分区的建议。

分析的情景

• 考虑只有索引、只有物理化视图还是二者都有能够获得最大的效益。
• 在推荐生成新索引或者物理化视图时，在存储、维护方面的开销与性能提高之间进行平衡。
• 如果指定的是全部负荷(full workload)，那么生成DROP推荐意见来删除未用的索引或物理化视图。
• 优化物理化视图，在可能的情况下实现快速刷新
• 推荐物理化视图日志以便快速刷新
• 推荐在适合的场所将多个索引组合成一个索引

体系架构

流程图

使用SQL Access Advisor调优

所需的权限

grant advisor to user_name;
grant select on tab_name to user_name;
grant ADMINISTER SQL TUNING SET to user_name;

创建SQL tuning set

通过DBMS_SQLTUNE.CREATE_SQLSETorDBMS_SQLSET.CREATE_SQLSET过程创建STS

SET SERVEROUTPUT ON;
VARIABLE task_id NUMBER;
VARIABLE task_name VARCHAR2(255);
VARIABLE workload_name VARCHAR2(255);
EXECUTE :workload_name := 'MY_STS_WORKLOAD';
EXECUTE DBMS_SQLTUNE.CREATE_SQLSET(:workload_name, 'test purpose');

加载SQL tuning set

创建任务

DBMS_ADVISOR.CREATE_TASK过程创建任务

EXEC :task_name := 'MYTASK';
EXEC DBMS_ADVISOR.CREATE_TASK('SQL Access Advisor', :task_id, :task_name);

执行任务

EXECUTE DBMS_ADVISOR.EXECUTE_TASK(:task_name);

查看任务状态

COL TASK_ID FORMAT 999
COL TASK_NAME FORMAT a25
COL STATUS_MESSAGE FORMAT a25

SELECT TASK_ID, TASK_NAME, STATUS, STATUS_MESSAGE 
FROM   DBA_ADVISOR_LOG;

查看优化建议结果

VARIABLE workload_name VARCHAR2(255); 
VARIABLE task_name VARCHAR2(255);
EXECUTE :task_name := 'MYTASK';
EXECUTE :workload_name := 'MY_STS_WORKLOAD'; 

SELECT REC_ID, RANK, BENEFIT
FROM   DBA_ADVISOR_RECOMMENDATIONS 
WHERE  TASK_NAME = :task_name
ORDER BY RANK;

-- 确定哪个查询受益于哪个推荐
SELECT SQL_ID, REC_ID, PRECOST, POSTCOST,
       (PRECOST-POSTCOST)*100/PRECOST AS PERCENT_BENEFIT
FROM   DBA_ADVISOR_SQLA_WK_STMTS
WHERE  TASK_NAME = :task_name
AND    WORKLOAD_NAME = :workload_name
ORDER BY percent_benefit DESC;

-- 显示这组建议的不同操作的数量
SELECT 'Action Count', COUNT(DISTINCT action_id) cnt
FROM   DBA_ADVISOR_ACTIONS 
WHERE  TASK_NAME = :task_name;

-- 显示这组建议的操作
SELECT REC_ID, ACTION_ID, SUBSTR(COMMAND,1,30) AS command
FROM   DBA_ADVISOR_ACTIONS 
WHERE  TASK_NAME = :task_name
ORDER BY rec_id, action_id;

-- 显示推荐的属性
CREATE OR REPLACE PROCEDURE show_recm (in_task_name IN VARCHAR2) IS 
CURSOR curs IS
  SELECT DISTINCT action_id, command, attr1, attr2, attr3, attr4
  FROM user_advisor_actions
  WHERE task_name = in_task_name
  ORDER BY action_id;
  v_action        number;
  v_command     VARCHAR2(32);
  v_attr1       VARCHAR2(4000);
  v_attr2       VARCHAR2(4000);
  v_attr3       VARCHAR2(4000);
  v_attr4       VARCHAR2(4000);
  v_attr5       VARCHAR2(4000);
BEGIN
  OPEN curs;
  DBMS_OUTPUT.PUT_LINE('=========================================');
  DBMS_OUTPUT.PUT_LINE('Task_name = ' || in_task_name);
  LOOP
     FETCH curs INTO  
       v_action, v_command, v_attr1, v_attr2, v_attr3, v_attr4 ;
   EXIT when curs%NOTFOUND;
   DBMS_OUTPUT.PUT_LINE('Action ID: ' || v_action);
   DBMS_OUTPUT.PUT_LINE('Command : ' || v_command);
   DBMS_OUTPUT.PUT_LINE('Attr1 (name)      : ' || SUBSTR(v_attr1,1,30));
   DBMS_OUTPUT.PUT_LINE('Attr2 (tablespace): ' || SUBSTR(v_attr2,1,30));
   DBMS_OUTPUT.PUT_LINE('Attr3             : ' || SUBSTR(v_attr3,1,30));
   DBMS_OUTPUT.PUT_LINE('Attr4             : ' || v_attr4);
   DBMS_OUTPUT.PUT_LINE('Attr5             : ' || v_attr5);
   DBMS_OUTPUT.PUT_LINE('----------------------------------------');  
   END LOOP;   
   CLOSE curs;      
   DBMS_OUTPUT.PUT_LINE('=========END RECOMMENDATIONS============');
END show_recm;
/

SET SERVEROUTPUT ON SIZE 99999
EXECUTE show_recm(:task_name);

附录

参考文档

https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/sql-access-advisor.html#GUID-561EC9B4-0930-4915-B5E1-17F2C5ACD261