Oracle Database 10 g : 为 DBA 提供的最佳前 20 位的特性（十二）

Oracle Database 10 g : 为 DBA 提供的最佳前 20 位的特性（十二）

作者 Arup Nanda 来源： OTN

lign=left> 

第 12 周
物化视图

利用强制查询重写和新的强大的调整顾问程序 — 它们使您不再需要凭猜测进行工作 — 的引入，在 10 g 中管理物化视图变得更加容易

物化视图 (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';

您如何才能知道已经为这个物化视图创建了其正常工作所必需的所有对象？在 Oracle 数据库 10 g 之前，这是用 DBMS_MVIEW 程序包中的 EXPLAIN_MVIEW 和 EXPLAIN_REWRITE 过程来判断的。这些过程（在 10 g 中仍然提供）非常简要地说明一种特定的功能 — 如快速刷新功能或查询重写功能 — 可能用于上述的物化视图，但不提供如何实现这些功能的建议。相反，需要对每一个物化视图的结构进行目视检查，这是非常不实际的。

在 10 g 中，新的 DBMS_ADVISOR 程序包中的一个名为 TUNE_MVIEW 的过程使得这项工作变得非常容易：您利用 IN 参数来调用程序包，这构造了物化视图创建脚本的全部内容。该过程创建一个顾问程序任务 (Advisor Task) ，它拥有一个特定的名称，仅利用 OUT 参数就能够把这个名称传回给您。

下面是一个例子。因为第一个参数是一个 OUT 参数，所以您需要在 SQL*Plus 中定义一个变量来保存它。

SQL> -- 首先定义一个变量来保存 OUT 参数

SQL> var adv_name varchar2(20)

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;

现在您可以在该变量中找出顾问程序的名称。

SQL> print adv_name

ADV_NAME

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

TASK_117

接下来，通过查询一个新的 DBA_TUNE_MVIEW 来获取由这个顾问程序提供的建议。务必在运行该命令之前执行 SET LONG 999999 ，因为该视图中的列语句是一个 CLOB ，默认情况下只显示 80 个字符。

select script_type, statement

from dba_tune_mview

where task_name = 'TASK_117'

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 语句。

在实施的最后一个步骤中，顾问程序建议改变创建物化视图的方式。注意我们的例子中的不同之处：将一个 count(*) 添加到了物化视图中。因为我们将这个物化视图定义为可快速刷新的，所以必须有 count(*) ，以便顾问程序纠正遗漏。

TUNE_MVIEW 过程不仅在建议方面超越了在 EXPLAIN_MVIEW 和 EXPLAIN_REWRITE 中提供的功能，还为创建相同的物化视图指出了更容易和更高效的途径。有时，顾问程序可以实际推荐多个物化视图，以使查询更加高效。

您可能会问，如果任何一个经验丰富的 DBA 都能够找出 MV 创建脚本中缺了什么，然后自己纠正它，那这还有什么用？嗯，顾问程序正是用来完成这项工作的：它是一位经验丰富、高度自觉的自动数据库管理员，它可以生成 能与人的建议相媲美的建议，但有一个非常重要的不同之处：它免费工作，并且不会要求休假或加薪。这一好处使高级 DBA 解放出来，将日常的工作交给较低级的 DBA ，从而允许他们将其专业技能应用到更具有战略意义的目标上。

您还可以将顾问程序的名称作为值传递给 TUNE_MVIEW 过程中的参数，这将使用该名称而非系统生成的名称生成一个的顾问程序。

更容易的实施

既然您可以看到建议，那么您可能想实施它们。一种方式是选择列 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, 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;

whenever sqlerror EXIT SQL.SQLCODE

begin

dbms_advisor.mark_recommendation('TASK_117',1,'IMPLEMENTED');

end;

/

这个文件包含了您实施建议所需的一切，从而为您省去了相当大的手动创建文件的麻烦。这个自动数据库管理员又一次能够为您完成工作。

重写或退出！

至此，您一定意识到了查询重写特性有多重要和多有用。它显著地减少了 I/O 和处理，并能够更快地返回结果。

让我们基于上述例子假定一种情况。用户执行以下查询：

Select city, sum(actual_rate)

from hotels h, reservations r, trans t

where t.resv_id = r.resv_id

and h.hotel_id = r.hotel_id

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 — 一个非常低的值。这个结果基于的事实是，重写了查询来使用在三个表上创建的两个物化视图。选择不是从表中进行的，而是从物化视图中进行，从而消耗了更少的资源（如 I/O 和 CPU ）。

但如果查询重写失败了，那该怎么办？它失败的原因可能有以下几种：如果初始化参数 query_rewrite_integrity 的值被设为 TRUSTED ，且 MV 的状态是 STALE ，那么将不会重写该查询。您可以通过在查询之前在会话中设定这个值来模拟这个过程。

alter session set query_rewrite_enabled = false;

在这条命令之后，说明计划 (EXPLAIN PLAN) 显示是从所有三个表中而不是从 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 。在实际情况下，这个结果可能无法接受，因为无法提供所需的额外资源，因此您可能想自己重写查询。在这种情况下，您可以确保如果而且只有在查询被重写的情况下，才允许进行查询。

在 Oracle9 i 数据库和更低版本中，决策是单向的：您可以禁用查询重写，但不能禁用基础表访问。不过 Oracle 数据库 10 g 提供了一种机制 — 通过一个特殊的提示 REWRITE_OR_ERROR 来实现这一目的。上述查询将利用该提示写为：

select /*+ REWRITE_OR_ERROR */ city, sum(actual_rate)

from hotels h, reservations r, trans t

where t.resv_id = r.resv_id

and h.hotel_id = r.hotel_id

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_HOTEL_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 时您将注意到这种情况。

虽然您可以始终利用资源管理器来控制资源使用情况，但使用该提示将防止执行查询，即使在调用资源管理器之前。资源管理器根据优化器统计数据估计所需的资源，因此是否存在足够准确的统计数据将影响这个过程。不过，重写或错误特性将停止表访问，而不管统计数据如何。

说明计划更好地进行说明

在上一个例子中，请注意说明计划输出中的行：

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

这种访问方法 — MAT_VIEW REWRITE — 是新增的；它显示正在访问 MV ，而非表或段。该过程立即告诉您表或 MV 是否被使用 — 即使名称没有表明段的本质。

结论

在 10 g 中，通过引入强大的新调整顾问程序 — 它们能够告诉您许多有关 MV 的设计的信息，从而使您不再需要凭猜测进行工作，管理 MV 变得更加容易。我尤其喜欢能够生成一个完整的脚本的调整建议，这种脚本可以快速实施，从而显著地节省时间和精力。强制重写或退出查询的能力在决策支持系统 中会非常有帮助 — 在这种系统中必须保留资源，并且未重写的查询将不允许在数据库内随意运行。

有关在 10 g 中管理物化视图的更多信息，请参考《 Oracle 数据库数据仓库指南 10 g 第 1 版 (10.1) 》中的 第 8 章 。