使用DBMS_ADVISOR.TUNE_MVIEW过程物化视图创建语句

郑全

不少人大概和我一样在创建物化视图的时候会犯头痛，怎样合理的改写SQL语句以及添加物化视图日志需要经过慎重精密的考虑。有了DBMS_ADVISOR.TUNE_MVIEW存储过程这个帮手后，极大地方便了DBA或应用设计人员创建和优化物化视图。该TUNE_MVIEW存储过程可以做到优化物化视图中的查询定义，修正物化视图日志的问题，此外它还能为原先不能refresh fast的物化视图提出建议以使得其可以快速刷新

1. 首先看看我们的SQL语句
select department_id,sum(nvl(salary+(salary*commission_pct),salary)) total_compensation from employees group by department_id;
2. 登录数据库查询步骤1中的语句
[oracle@snow ~]$ sqlplus / as sysdba
> conn hr/hr
> select department_id,sum(nvl(salary+(salary*commission_pct),salary)) total_compensation from employees group by department_id;
DEPARTMENT_ID TOTAL_COMPENSATION
------------- ------------------
100           51608
30            24900
80            50
90            58000
20            19000
70            10000
110           20308
50            156400
80            377140
40            6500
60            28800
10            4400


3. 使用DBMS_ADVISOR.TUNE_MVIEW和DBMS_ADVISOR.CREATE_FILE将步骤一的sql语句创建成一个物化视图脚本。使用生成的脚本可以方便的创建出物化视图。
vi /home/oracle/tune_mv.sql
VARIABLE my_tune_mview_task VARCHAR2(30);
VARIABLE SQL VARCHAR2(4000);
--清理之前残留的任务
--EXECUTE DBMS_ADVISOR.DELETE_TASK('my_tune_mview_task');
--给变量赋值
EXECUTE :my_tune_mview_task := 'mview_task';
EXECUTE :SQL := 'CREATE MATERIALIZED VIEW MY_MV REFRESH FAST AS -
                 select department_id,sum(nvl(salary+(salary*commission_pct),salary)) total_compensation  -
                   from employees group by department_id';
--使用系统包分析:SQL中的创建语句
EXECUTE DBMS_ADVISOR.TUNE_MVIEW (:my_tune_mview_task,:sql);
--创建目录并授权，用来保存“分析诊断结果”
CREATE OR REPLACE DIRECTORY TUNE_RESULTS AS '/home/oracle' ;
GRANT READ, WRITE ON DIRECTORY TUNE_RESULTS TO PUBLIC;

--使用系统包创建诊断结果，/home/oracle/my_tune_mview_create.sql
EXECUTE DBMS_ADVISOR.CREATE_FILE(DBMS_ADVISOR.GET_TASK_SCRIPT(:my_tune_mview_task), -
'TUNE_RESULTS','my_tune_mview_create.sql');

4. 执行脚本tuen_mv.sql来完成对创建物化视图语句的分析，并给出建议的全部过程。
> @/home/oracle/tune_mv.sql


5. 经过诊断Oracle给出的建议脚本如下, 如果我们需要查询重写特性，直接修改DISABLE QUERY REWRITE为enable query rewrite。
[oracle@snow scripts]$ vi my_tune_mview_create.sql
Rem SQL Access Advisor: Version 11.2.0.4.0 - Production
Rem
Rem Username: HR
Rem Task: mview_task
Rem Execution date:
Rem


CREATE MATERIALIZED VIEW LOG ON
"HR"."EMPLOYEES"
WITH ROWID, SEQUENCE("SALARY","COMMISSION_PCT","DEPARTMENT_ID")
INCLUDING NEW VALUES;


ALTER MATERIALIZED VIEW LOG FORCE ON
"HR"."EMPLOYEES"
ADD ROWID, SEQUENCE("SALARY","COMMISSION_PCT","DEPARTMENT_ID")
INCLUDING NEW VALUES;


CREATE MATERIALIZED VIEW HR.MY_MV
REFRESH FAST WITH ROWID
DISABLE QUERY REWRITE
AS SELECT HR.EMPLOYEES.DEPARTMENT_ID C1, SUM(NVL("HR"."EMPLOYEES"."SALARY"+"HR"."EMPLOYEES"."SALARY"
*"HR"."EMPLOYEES"."COMMISSION_PCT","HR"."EMPLOYEES"."SALARY")) M1, COUNT(NVL("HR"."EMPLOYEES"."SALARY"+"HR"."EMPLOYEES"."SALARY"
*"HR"."EMPLOYEES"."COMMISSION_PCT","HR"."EMPLOYEES"."SALARY")) M2, COUNT(
*) M3 FROM HR.EMPLOYEES GROUP BY HR.EMPLOYEES.DEPARTMENT_ID;


修改后脚本如下
vi my_tune_mview_create.sql
CREATE MATERIALIZED VIEW LOG ON
"HR"."EMPLOYEES"
WITH ROWID, SEQUENCE("SALARY","COMMISSION_PCT","DEPARTMENT_ID")
INCLUDING NEW VALUES;


ALTER MATERIALIZED VIEW LOG FORCE ON
"HR"."EMPLOYEES"
ADD ROWID, SEQUENCE("SALARY","COMMISSION_PCT","DEPARTMENT_ID")
INCLUDING NEW VALUES;


CREATE MATERIALIZED VIEW HR.MY_MV
REFRESH FAST WITH ROWID
ENABLE QUERY REWRITE
AS SELECT HR.EMPLOYEES.DEPARTMENT_ID C1, SUM(NVL("HR"."EMPLOYEES"."SALARY"+"HR"."EMPLOYEES"."SALARY"
*"HR"."EMPLOYEES"."COMMISSION_PCT","HR"."EMPLOYEES"."SALARY")) M1, COUNT(NVL("HR"."EMPLOYEES"."SALARY"+"HR"."EMPLOYEES"."SALARY"
*"HR"."EMPLOYEES"."COMMISSION_PCT","HR"."EMPLOYEES"."SALARY")) M2, COUNT(
*) M3 FROM HR.EMPLOYEES GROUP BY HR.EMPLOYEES.DEPARTMENT_ID;


6. 执行该脚本创建物化视图，包括快速刷新物化视图日志，和可查询重写的物化视图。
> @/home/oracle/my_tune_mview_create.sql


7. 测试查询重写的效果，执行预期的sql语句，优化器判断可以走物化视图。
> set autot trace exp;
> select department_id,sum(nvl(salary+(salary*commission_pct),salary)) total_compensation from employees group by department_id;


Execution Plan
----------------------------------------------------------
Plan hash value: 4040700093
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 84 | 3 (0)| 00:00:01 |
| 1 | MAT_VIEW REWRITE ACCESS FULL| MY_MV | 12 | 84 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------