使用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 |

--------------------------------------------------------------------------------