oracle10g新特性—物化视图

Advisor

在g将查询重写并且引进了新的强大的调优建议者使管理物化视图变得容易多了

物化视图(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 rhotel_id = hhotel_id;

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

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

这有一个例子由于第一个参数是一个输出参数所以你必须定义一个变量 SQL> first define a variable to hold the OUT parameter

SQL> var adv_name varchar()

SQL> begin

dbms_advisortune_mview

(

:adv_name

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 rhotel_id = hhotel_id);

* end;

Now you can find out the name of the Advisor from the variable

SQL> print adv_name

ADV_NAME

TASK_

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

from dba_tune_mview

where task_name = TASK_

order by script_type action_id;

CRIPT_TYPE STATEMENT

IMPLEMENTATION CREATE MATERIALIZED VIEW LOG ON ARUPHOTELS WITH ROWID

SEQUENCE (HOTEL_IDCITY) INCLUDING NEW VALUES

IMPLEMENTATION ALTER MATERIALIZED VIEW LOG FORCE ON ARUPHOTELS ADD

ROWID SEQUENCE (HOTEL_IDCITY) INCLUDING NEW VALUES

IMPLEMENTATION CREATE MATERIALIZED VIEW LOG ON ARUPRESERVATIONS WITH

ROWID SEQUENCE (RESV_IDHOTEL_IDCUST_NAME)

INCLUDING NEW VALUES

IMPLEMENTATION ALTER MATERIALIZED VIEW LOG FORCE ON ARUPRESERVATIONS

ADD ROWID SEQUENCE (RESV_IDHOTEL_IDCUST_NAME)

INCLUDING NEW VALUES

IMPLEMENTATION CREATE MATERIALIZED VIEW ARUPMV_HOTEL_RESV REFRESH FAST

WITH ROWID ENABLE QUERY REWRITE AS SELECT

ARUPRESERVATIONSCUST_NAME C ARUPRESERVATIONSRESV_ID

C ARUPHOTELSCITY C COUNT(*) M FROM ARUPRESERVATIONS

ARUPHOTELS WHERE ARUPHOTELSHOTEL_ID =

ARUPRESERVATIONSHOTEL_ID GROUP BY

ARUPRESERVATIONSCUST_NAME ARUPRESERVATIONSRESV_ID

ARUPHOTELSCITY

UNDO DROP MATERIALIZED VIEW ARUPMV_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_advisorcreate_file (

dbms_advisorget_task_script (TASK_)

MVTUNE_OUTDIR

mvtune_scriptsql

);

end;

/

这一存储过程是假定你已经定义了一个目录对象的情况下调用的如 create directory mvtune_outdir as /home/oracle/mvtune_outdir;

调用包dbms_advisor的这个存储过程会在目录/home/oracle/mvtune_outdir中生成一个名叫mvtune_scriptsql的脚本文件如果查看文件它有如下内容 Rem SQL Access Advisor: Version Production

Rem

Rem Username: ARUP

Rem Task: TASK_

Rem Execution date:

Rem

set feedback

set linesize

set trimspool on

set tab off

set pagesize

whenever sqlerror CONTINUE

CREATE MATERIALIZED VIEW LOG ON

ARUPHOTELS

WITH ROWID SEQUENCE(HOTEL_IDCITY)

INCLUDING NEW VALUES;

ALTER MATERIALIZED VIEW LOG FORCE ON

ARUPHOTELS

ADD ROWID SEQUENCE(HOTEL_IDCITY)

INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON

ARUPRESERVATIONS

WITH ROWID SEQUENCE(RESV_IDHOTEL_IDCUST_NAME)

INCLUDING NEW VALUES;

ALTER MATERIALIZED VIEW LOG FORCE ON

ARUPRESERVATIONS

ADD ROWID SEQUENCE(RESV_IDHOTEL_IDCUST_NAME)

INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW ARUPMV_HOTEL_RESV

REFRESH FAST WITH ROWID

ENABLE QUERY REWRITE

AS SELECT ARUPRESERVATIONSCUST_NAME C

RUPRESERVATIONSRESV_ID C ARUPHOTELSCITY

C COUNT(*) M FROM ARUPRESERVATIONS ARUPHOTELS WHERE

RUPHOTELSHOTEL_ID

= ARUPRESERVATIONSHOTEL_ID GROUP BY

RUPRESERVATIONSCUST_NAME ARUPRESERVATIONSRESV_ID

ARUPHOTELSCITY;

whenever sqlerror EXIT SQLSQLCODE

begin

dbms_advisormark_recommendation(TASK_IMPLEMENTED);

end;

/

这一文件包含了所有你需要实施的建议的内容而不需要你手工去创建一个脚本机器DBA又一次替你做了你需要做的工作

重写还是报错

现在你可能已经认识到了查询重写特性是多么有用和重要它能大大降低I/O和处理过程返回结果更快

还是假定以上的例子用户执行一个下面的查询 SQL> Select city sum(actual_rate)

from hotels h reservations r trans t

where tresv_id = rresv_id

and hhotel_id = rhotel_id

group by city;

recursive calls

db block gets

consistent gets

physical reads

redo size

bytes sent via SQL*Net to client

bytes received via SQL*Net from client

SQL*Net roundtrips to/from client

sorts (memory)

sorts (disk)

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

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

SQL> alter session set query_rewrite_enabled = false;

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

db block gets

consistent gets

physical reads

redo size

bytes sent via SQL*Net to client

bytes received via SQL*Net from client

SQL*Net roundtrips to/from client

sorts (memory)

sorts (disk)

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

在Oracle i和以下版本中可能只有一个方法实现使查询重写失效而不是使基础表的访问失败在g中通过一个特殊的提示可以提供这样的机制REWRITE_OR_ERROR上面这个查询就可以这样写了 SQL> select /*+ REWRITE_OR_ERROR */ city sum(actual_rate)

from hotels h reservations r trans t

where tresv_id = rresv_id

and hhotel_id = rhotel_id

group by city;

from hotels h reservations r trans t

*

ERROR at line :

ORA: a query block in the statement did not rewrite

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

SELECT STATEMENT Optimizer=ALL_ROWS (Cost= Card= Bytes=)

SORT (GROUP BY) (Cost= Card= Bytes=)

HASH JOIN (Cost= Card= Bytes=)

MERGE JOIN (Cost= Card= Bytes=)

TABLE ACCESS (BY INDEX ROWID) OF HOTELS (TABLE) (Cost= Card= Bytes=)

INDEX (FULL SCAN) OF PK_HOTELS (INDEX (UNIQUE)) (Cost= Card=)

SORT (JOIN) (Cost= Card= Bytes=)

TABLE ACCESS (FULL) OF RESERVATIONS (TABLE) (Cost= Card= Bytes=)

MAT_VIEW REWRITE ACCESS (FULL) OF MV_ACTUAL_SALES (MAT_VIEW REWRITE) (Cost= Card= Bytes=)

这一查询使用了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

总结

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