SQL Access Advisor: 根据系统的负载推荐SQL的访问路径, 主要是index和materiliazed view。并产生SQL脚本文件。

1 创建task

VARIABLE task_id NUMBER;
VARIABLE task_name VARCHAR2(255);
EXECUTE :task_name := 'MYTASK';
EXECUTE DBMS_ADVISOR.DELETE_TASK (:task_name);
EXECUTE DBMS_ADVISOR.CREATE_TASK ('SQL Access Advisor', :task_id, :task_name);

2 创建负载对象

VARIABLE workload_name VARCHAR2(255);
EXECUTE :workload_name := 'MYWORKLOAD';
EXECUTE DBMS_ADVISOR.CREATE_SQLWKLD(:workload_name,'This is my first workload');

3 建立task和workload的关系
EXECUTE DBMS_ADVISOR.ADD_SQLWKLD_REF('MYTASK', 'MYWORKLOAD');

4 加载一个query到workload中
VARIABLE sql_text VARCHAR2(400);
EXECUTE :sql_text := 'select object_type, count(*) from test group by object_type';
EXECUTE DBMS_ADVISOR.ADD_SQLWKLD_STATEMENT ( -
   'MYWORKLOAD', 'SQL*Plus', NULL, priority=>1, executions=>10, -
    username => 'RMAN',  sql_text => :sql_text);

5 从共享池中导入一个schema的queries
VARIABLE saved_stmts NUMBER;
VARIABLE failed_stmts NUMBER;

EXECUTE DBMS_ADVISOR.SET_SQLWKLD_PARAMETER ( -
   :workload_name, 'USERNAME_LIST', 'RMAN');

EXECUTE DBMS_ADVISOR.IMPORT_SQLWKLD_SQLCACHE ( -
:workload_name, 'APPEND', 2, :saved_stmts, :failed_stmts);
PRINT :saved_stmts;
PRINT :failed_stmts;

SELECT num_select_stmt, create_date
FROM user_advisor_sqlw_sum
WHERE workload_name = :workload_name;

SELECT sql_id, username, optimizer_cost, SUBSTR(sql_text, 1, 30)
FROM user_advisor_sqlw_stmts
WHERE workload_name = :workload_name
ORDER BY sql_id;

6 执行任务

exec DBMS_ADVISOR.EXECUTE_TASK ('MYTASK');

7 查看建议
SELECT REC_ID, RANK, BENEFIT
FROM USER_ADVISOR_RECOMMENDATIONS WHERE TASK_NAME ='MYTASK';

SELECT rec_id, action_id, SUBSTR(command,1,30) AS command
FROM user_advisor_actions WHERE task_name = :task_name
ORDER BY rec_id, action_id;

set serveroutput on size 99999
EXECUTE show_recm('MYTASK');

8 生成脚本
create or replace directory advisor_results as '/u01/';
grant read,write on directory advisor_results to public;
EXECUTE DBMS_ADVISOR.CREATE_FILE(DBMS_ADVISOR.GET_TASK_SCRIPT('MYTASK'), -
               'ADVISOR_RESULTS', 'advscript.sql');

9 使用quick_tune
VARIABLE task_name VARCHAR2(255);
VARIABLE sql_stmt VARCHAR2(4000);
EXECUTE :sql_stmt := 'select object_type, count(*) from test group by object_type';
EXECUTE :task_name  := 'MY_QUICKTUNE_TASK1';
EXECUTE DBMS_ADVISOR.QUICK_TUNE(DBMS_ADVISOR.SQLACCESS_ADVISOR, -
              :task_name, :sql_stmt);

SELECT rec_id, action_id, SUBSTR(command,1,30) AS command
FROM user_advisor_actions WHERE task_name = :task_name
ORDER BY rec_id, action_id;

10 使用tune_mview
VARIABLE task_cust_mv VARCHAR2(30);
VARIABLE create_mv_ddl VARCHAR2(4000);
EXECUTE :task_cust_mv := 'test_mv6';

EXECUTE :create_mv_ddl := ' -
CREATE MATERIALIZED VIEW test_mv3 -
ENABLE QUERY REWRITE AS -
select object_type, count(*) as cnt -
FROM test -
group by object_type  -
union all -
select status, count(*) as cnt -
FROM test -
group by status -
';

EXECUTE DBMS_ADVISOR.TUNE_MVIEW(:task_cust_mv, :create_mv_ddl);
EXECUTE DBMS_ADVISOR.DELETE_TASK (:task_name);

SELECT rec_id, action_id, SUBSTR(command,1,30) AS command
FROM user_advisor_actions WHERE task_name = :task_cust_mv
ORDER BY rec_id, action_id;

EXECUTE DBMS_ADVISOR.CREATE_FILE(DBMS_ADVISOR.GET_TASK_SCRIPT(:task_cust_mv), -
               'ADVISOR_RESULTS', 'advscript.sql');