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