oracle10g新特性——物化视图-技术开发专区

oracle10g新特性——物化视图

作者：fuyuncat 编辑：阿雪 2007-07-26 10:27

【IT168技术文档】
Advisor
在10g将查询重写并且引进了新的强大的调优建议者使管理物化视图变得容易多了。

物化视图(Materialized Views MVs)，也被称为快照，现在已经被广泛应用了。MV将一个查询的结果存储在一个段中，并且当用户提交查询时返回查询结果，而不需要重新执行查询——如果查询会被执行多次（经常出现在数据仓库环境中），这就会非常有效。MV可以从基础表中完全刷新或通过使用快速刷新机制增量刷新。

假如你有如下定义的MV：

create materialized view mv_hotel_resv
refresh fast
enable query rewrite
as
select distinct city, resv_id, cust_name
from hotels h, reservations r
where r.hotel_id = h.hotel_id';

你如何知道使这个MV正常工作的所有必须对象都已经被创建呢？在10g之前，这一检测是通过包DBMS_MVIEW的存储过程EXPLAIN_MVIEW和EXPLAIN_REWRITE实现的。这些存储过程在10g还存在，它们的功能很简单——检测MV是否具备快速刷新能力和查询重新能力，但它们并不提供如何使这些能力有效的建议。相反，要求对于每个MV的结构都做检查是不切实际的。

在10g中，有一个新的包DBMS_ADVISOR，它有一个存储过程TUNE_MVIEW使这项工作变得非常容易：你可以在调用这个包时输入一个输入参数，参数内容为创建MV的整个脚本。这个存储过程创建了一个建议者任务(Advisor Task)，它的名字会通常存储过程唯一的输出参数返回给用户。

这有一个例子。由于第一个参数是一个输出参数，所以你必须定义一个变量：

SQL> -- first define a variable to hold the OUT parameter 

SQL> var adv_name varchar2(20)
1 SQL> begin
2 dbms_advisor.tune_mview
3 (
4 :adv_name,
5 'create materialized view mv_hotel_resv refresh fast enable query rewrite as
select distinct city, resv_id, cust_name from hotels h,
reservations r where r.hotel_id = h.hotel_id');
6* end;
Now you can find out the name of the Advisor from the variable.
SQL> print adv_name
ADV_NAME
----------------------- 

TASK_117

接下来，可以通过一个新视图DBA_TUNE_MVIEW从Advisor那获取到所提供的建议。在执行查询前记得先执行设置SET LONG 999999，因为这个视图中的这个字段是一个CLOB类型，而默认知会显示80个字符。

SQL> select script_type, statement
2 from dba_tune_mview
3 where task_name = 'TASK_117'
4 order by script_type, action_id;
SCRIPT_TYPE STATEMENT
-------------- ------------------------------------------------------------ 

IMPLEMENTATION CREATE MATERIALIZED VIEW LOG ON "ARUP"."HOTELS" WITH ROWID,
SEQUENCE ("HOTEL_ID","CITY") INCLUDING NEW VALUES
IMPLEMENTATION ALTER MATERIALIZED VIEW LOG FORCE ON "ARUP"."HOTELS" ADD
ROWID, SEQUENCE ("HOTEL_ID","CITY") INCLUDING NEW VALUES
IMPLEMENTATION CREATE MATERIALIZED VIEW LOG ON "ARUP"."RESERVATIONS" WITH
ROWID, SEQUENCE ("RESV_ID","HOTEL_ID","CUST_NAME")
INCLUDING NEW VALUES 

IMPLEMENTATION ALTER MATERIALIZED VIEW LOG FORCE ON "ARUP"."RESERVATIONS"
ADD ROWID, SEQUENCE ("RESV_ID","HOTEL_ID","CUST_NAME")
INCLUDING NEW VALUES
IMPLEMENTATION CREATE MATERIALIZED VIEW ARUP.MV_HOTEL_RESV REFRESH FAST
WITH ROWID ENABLE QUERY REWRITE AS SELECT
ARUP.RESERVATIONS.CUST_NAME C1, ARUP.RESERVATIONS.RESV_ID
C2, ARUP.HOTELS.CITY C3, COUNT(*) M1 FROM ARUP.RESERVATIONS,
ARUP.HOTELS WHERE ARUP.HOTELS.HOTEL_ID =
ARUP.RESERVATIONS.HOTEL_ID GROUP BY
ARUP.RESERVATIONS.CUST_NAME, ARUP.RESERVATIONS.RESV_ID,
ARUP.HOTELS.CITY
UNDO DROP MATERIALIZED VIEW ARUP.MV_HOTEL_RESV

     字段SCRIPT_TYPE的内容就是建议。大多数行都是要被实施的，因此被命名成IMPLEMENTATION。如果接受了这些建议，需要从字段ACTION_ID中得到一个特殊的序列号。

    如果重新仔细检查一下这些自动产生的建议，你会发现它们和你自己分析得出需要做的操作很相似。这些建议是逻辑上的；如果存在快速刷新，那就需要通过包括这些新值的子句在基础表上建立物化视图日志(MATERIALIZED VIEW LOG)。STATEMENT字段甚至提供了一个实施这些建议的准确的SQL语句。

     在实施的最后步骤，Advisor建议对MV的创建方式做一些修改。注意我们例子中的一个不同点：在MV上加了一个count(*)。由于我们定义这个MV是快速刷新，而count(*)又是必须的，所以Advisor修正了这一冗余。

    存储过程TUNE_MVIEW与EXPLAIN_MVIEW和EXPLAIN_REWRITE的不同之处不仅仅在于建议，它还能更容易鉴别出并提供一个效率更好的方式建立相同的MV。有时候Advisor能建议比使用一个单一的MV效率更高的的查询。

    你可能会问，如果一个经验丰富的DBA能找出MV创建脚本中的却些并且能自己调整它，那这些有什么用？当然，Advisor就是一个经验丰富、精力充沛、机器人似的的DBA，它能给出和人差不多的建议。但是和人有一个很大的不同：它可以随时工作而不需要假期和涨薪。这一好处可以使有经验的DBA从日常任务中解放出来，把这些工作留给普通的DBA去做。而把它们自己的经验发挥到更具战略意义的任务中。

    你也可以在嗲用TUNE_MVIEW时传入Advisor的名字，这样就不会使用系统自己生产的名字了。
更容易实施
    既然你知道了这些建议，你当然希望去实施它们了。一个方法就是将字段STATEMENT中内容取出，存到一个脚本文件中，并执行它。另外一个方法就是执行一个包里面的存储过程：

begin
dbms_advisor.create_file (
dbms_advisor.get_task_script ('TASK_117'),
'MVTUNE_OUTDIR',
'mvtune_script.sql'
);
end;
/

这一存储过程是假定你已经定义了一个目录对象的情况下调用的，如：

create directory mvtune_outdir as '/home/oracle/mvtune_outdir';

调用包dbms_advisor的这个存储过程会在目录/home/oracle/mvtune_outdir中生成一个名叫mvtune_script.sql的脚本文件。如果查看文件，它有如下内容：

Rem SQL Access Advisor: Version 10.1.0.1 - Production
Rem
Rem Username: ARUP
Rem Task: TASK_117
Rem Execution date:
Rem
set feedback 1
set linesize 80
set trimspool on
set tab off
set pagesize 60
whenever sqlerror CONTINUE
CREATE MATERIALIZED VIEW LOG ON
"ARUP"."HOTELS"
WITH ROWID, SEQUENCE("HOTEL_ID","CITY")
INCLUDING NEW VALUES;
ALTER MATERIALIZED VIEW LOG FORCE ON
"ARUP"."HOTELS"
ADD ROWID, SEQUENCE("HOTEL_ID","CITY")
INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW LOG ON
"ARUP"."RESERVATIONS"
WITH ROWID, SEQUENCE("RESV_ID","HOTEL_ID","CUST_NAME")
INCLUDING NEW VALUES;
ALTER MATERIALIZED VIEW LOG FORCE ON
"ARUP"."RESERVATIONS"
ADD ROWID, SEQUENCE("RESV_ID","HOTEL_ID","CUST_NAME")
INCLUDING NEW VALUES;
CREATE MATERIALIZED VIEW ARUP.MV_HOTEL_RESV
REFRESH FAST WITH ROWID
ENABLE QUERY REWRITE
AS SELECT ARUP.RESERVATIONS.CUST_NAME C1,
RUP.RESERVATIONS.RESV_ID C2, ARUP.HOTELS.CITY
C3, COUNT(*) M1 FROM ARUP.RESERVATIONS, ARUP.HOTELS WHERE
RUP.HOTELS.HOTEL_ID
= ARUP.RESERVATIONS.HOTEL_ID GROUP BY
RUP.RESERVATIONS.CUST_NAME, ARUP.RESERVATIONS.RESV_ID,
ARUP.HOTELS.CITY;
whenever sqlerror EXIT SQL.SQLCODE
begin
dbms_advisor.mark_recommendation('TASK_117',1,'IMPLEMENTED');
end;
/

这一文件包含了所有你需要实施的建议的内容，而不需要你手工去创建一个脚本。机器DBA又一次替你做了你需要做的工作。重写还是报错
现在你可能已经认识到了查询重写特性是多么有用和重要。它能大大降低I/O和处理过程、返回结果更快。

还是假定以上的例子，用户执行一个下面的查询：

1 SQL> Select city, sum(actual_rate)
2 from hotels h, reservations r, trans t
3 where t.resv_id = r.resv_id
4 and h.hotel_id = r.hotel_id
5 group by city;
0 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
478 bytes sent via SQL*Net to client
496 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)

请注意consistent gets的值是6——一个非常低的值。这一结果是基于这个查询已经基于从3张表创建的2个视图的查询重写。不是从表查询，而是从MV查询，一次消耗了更少的如磁盘IO和CPU的资源。

但是如果查询重写失败了会怎么样呢？可能会以为几个原因失败：如果初始化参数query_rewrite_integrity被设置为TRUSTED并且MV的状态为STALE，查询就不会被重写。你可以通过设置会话的参数来模拟这一过程。

SQL> alter session set query_rewrite_enabled = false;

执行这一命令后，查询计划显示是从3张表查询数据，而不是从MV：

0 recursive calls
0 db block gets
16 consistent gets
0 physical reads
0 redo size
478 bytes sent via SQL*Net to client
496 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)

请注意consistent gets的值：从6上升到了16。在真实环境中，这一结果恐怕是无法接受的。因为多出来的资源请求可能无法获得，而你就必须自己重写这一查询了。在那样的情况下，你就必须确保查询一定能被重写。

在Oracle 9i和以下版本中，可能只有一个方法实现：使查询重写失效而不是使基础表的访问失败。在10g中通过一个特殊的提示可以提供这样的机制：REWRITE_OR_ERROR。上面这个查询就可以这样写了：

1 SQL> select /*+ REWRITE_OR_ERROR */ city, sum(actual_rate)
2 from hotels h, reservations r, trans t
3 where t.resv_id = r.resv_id
4 and h.hotel_id = r.hotel_id
5 group by city;
from hotels h, reservations r, trans t
*
ERROR at line 2:
ORA-30393: a query block in the statement did not rewrite

这样就会产生一个ora-30393的错误信息。这个信息表示查询不能通过使用MV来重写，因此语句失败。这一错误保护可以防止查询长期运行后系统发生资源缺乏问题。但是还要注意一个潜在问题：如果一个查询成功了，而不是所有都成功了，这些MV就能被用于查询的重写。因此，如果MV_ACTUAL_SALES而不是MV_HOTL_RESV能被使用，查询将会重写，错误也不会产生。这种情况下，查询计划就如以下：

Execution Plan
---------------------------------------------------------- 

0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=11 Card=6 Bytes=156)
1 0 SORT (GROUP BY) (Cost=11 Card=6 Bytes=156)
2 1 HASH JOIN (Cost=10 Card=80 Bytes=2080)
3 2 MERGE JOIN (Cost=6 Card=80 Bytes=1520)
4 3 TABLE ACCESS (BY INDEX ROWID) OF 'HOTELS' (TABLE) (Cost=2 Card=8 Bytes=104)
5 4 INDEX (FULL SCAN) OF 'PK_HOTELS' (INDEX (UNIQUE)) (Cost=1 Card=8)
6 3 SORT (JOIN) (Cost=4 Card=80 Bytes=480)
7 6 TABLE ACCESS (FULL) OF 'RESERVATIONS' (TABLE) (Cost=3 Card=80 Bytes=480)
8 2 MAT_VIEW REWRITE ACCESS (FULL) OF 'MV_ACTUAL_SALES' (MAT_VIEW REWRITE) (Cost=3 Card=80 Bytes=560)

     这一查询使用了MV_ACTUAL_SALES而不是MV_HOTEL_RESV，这样，表HOTELS和RESERVATIONS就能够访问。这种情况下，特别使对后面两种表会做全表扫描的情况下，将会消耗更多的资源——在你创建MV和设计查询语句时要特别注意。

    尽管你已经通过资源管理器（Resource Manager）控制资源使用，使用这一提示能防止在资源管理器被调用前查询被执行。资源管理器基于优化器的统计数据来降低资源的消耗，因此统计数据的有无将会影响这一过程。而“重写还是报错”这一特性将会不管有误统计数据都会阻值表的访问。更佳的查询计划
    在前面的例子中，请注意在查询计划中有这样一行：

MAT_VIEW REWRITE ACCESS (FULL) OF 'MV_ACTUAL_SALES' (MAT_VIEW REWRITE)

这个访问MAT_VIEW REWRITE的方法是新出现的。它表示已经访问了MV，而不是表或者段。这就可以让你在从名字上无法区分时知道是在使用表还是MV。

总结
在10g中，由于增加了新的Tuning Advisor，它能向你提供很多关于MV设计方面的建议而无需通过猜测方式进行。这使得管理MV容易多了。我特别细化能将建议生成一个完整的脚本使实施更加迅速，能节省很多时间这一特性。强制重写或取消查询这一特性在决策支持系统中非常有用。因为这样的系统不允许一个没有被重写的查询在数据库内疯狂执行。

关注我们