Oracle Database 10g:为 DBA 提供的最佳特性－前9到12周（转贴）

来源：互联网发布：距离编辑算法实现编辑：程序博客网时间：2024/05/16 13:41

Oracle Database 10g:为 DBA 提供的最佳特性－前9到12周

第 9 周

RMAN

RMAN 的功能更强大，它具有重新设计的增量备份模式、增量备份的脱机恢复、预览恢复、复原日志进行恢复、文件压缩等功能。

大多数人都认同 RMAN 是用于 Oracle 数据库备份的实际工具。但是与它们所具有的强大功能相比，RMAN 的早期版本并未提供人们所期待的一些功能。就像许多 DBA 一样，如果它没有包含我认为必须具有的功能，我将会异常恼怒。

幸运的是，Oracle 数据库 10g 通过合并人们所想要的许多功能解决了很多这类问题，这使 RMAN 成为一种更强大、更有用的工具。让我们看一下这些功能。

再论增量备份 RMAN 包含一个用于增量备份的选项。但是老实讲，您多久使用一次呢？可能经常用，也可能永远也不会用。

该选项用于指示该工具以相同或较低的级别来备份自上一次增量备份后发生改变的块。例如，在第 1 天采用完全备份 (level_0)，而在第 2、3 天采用两个 level_1 的增量。后面的两个备份只是备份了第 1 天和第 2 天之间，及第 2 天和第 3 天之间更改过的块，而不是跨整个备份时间进行备份。这种策略减少了备份规模、需要的空间较少，并缩小了备份窗口，减少了网络间移动的数据量。

执行增量备份的最重要的原因是：与数据仓库环境关联起来，在该环境中许多操作都是在 NOLOGGING 模式下执行的，并且数据更改不会涉及到存档的日志文件—因此，不可能发生介质恢复。考虑到今天的数据仓库的巨大规模，以及其中的大部分数据并没有发生改变的事实，就会知道执行完全备份既不值得又不实际。相反，在 RMAN 中执行增量备份是一个理想的选择。

既然如此，那么为什么许多 DBA 极少执行增量备份呢？一个原因是：在 Oracle 9i 及其较低的版本中，RMAN 会扫描所有的数据块以确定要备份的内容。这个过程给系统施加了如此大的压力，以致于执行增量备份变得不实际。

Oracle 数据库 10g RMAN 以消除了该缺陷的方式来执行增量备份。它使用一个文件，类似于文件系统中的日志，来跟踪自上一次备份起更改过的块。RMAN 读取该文件来确定将要备份的块。

您可以通过发布以下命令来启用该跟踪机制：

SQL> alter database enable block change tracking using file '/rman_bkups/change.log';

该命令将创建一个名为 /rman_bkups/change.log 的二进制文件，以用于跟踪。相反，您可以使用以下命令来禁用跟踪：

SQL> alter database disable block change tracking;

要想查看当前是否启用了对更改的跟踪，您可以查询：

SQL> select filename, status from v$block_change_tracking;

快速恢复区在 Oracle 9i 中引入的闪回查询，依赖于撤消表空间来闪回到先前的版本，因此限制了它深入到过去的能力。快速恢复通过创建闪回日志提供了一个可选的解决方案，它类似于重做日志，用于将数据库恢复到先前的状态。总之，您为数据库创建了一个快速恢复区，指定了其大小，并用如下 SQL 命令将数据库置于快速恢复模式下：

alter system set db_recovery_file_dest = '/ora_flash_area';

alter system set db_recovery_file_dest_size = 2g;

alter system set db_flashback_retention_target = 1440;

alter database flashback on; 该数据库必须处于存档日志模式下以支持闪回。此过程在目录 /ora_flash_area 中创建了 Oracle 管理文件，其总大小高达 2GB。对数据库所作的更改将写入到这些文件中，并且可用于将数据库快速恢复到过去的某个点上。

默认情况下，RMAN 还使用 /ora_flash_area 来存储备份文件；因此，RMAN 是存储在磁盘上，而不是磁带上。鉴于此，您就有能力指定您需要备份的天数。在该期限之后，如果需要更多的空间，则会自动将这些文件删除。

快速恢复区不必是一个文件系统或一个目录，但是—，它可以是一个自动存储管理 (ASM) 磁盘组。如果是那样的话，就可以通过如下命令来指定快速恢复区：

alter system set db_recovery_file_dest = '+dskgrp1';

因此，结合使用 ASM 和 RMAN，您就可以使用廉价的磁盘（如 Serial ATA 或 SCSI 驱动）来构建一个高度可伸缩的、容错能力强的存储系统，而不需要额外的软件。（有关 ASM 的详细信息，请参阅本系列中的第 8 周的内容。）此过程不但使存储过程更快，也使之能用足够便宜的、基于磁带的方法来完成。

一个额外的好处是防止用户错误。由于 ASM 不是真正的文件系统，使其遭受 DBA 和系统管理员意外破坏的可能性也更小一些。

增量合并假如您有如下备份计划：

星期天 - 第 0 级（完全），带有标签 level_0

星期一 - 第 1 级（增量），带有标签 level_1_mon

星期二 - 第 1 级（增量），带有标签 level_1_tue

等等。如果数据库在星期天发生故障，在 Oracle 10g 之前的版本中，您将不得不恢复标签 level_0，然后应用所有六个增量。它将持续一段较长的时间，这是许多 DBA 不进行增量备份的另一个原因。

Oracle 数据库 10g RMAN 从根本上改变了此格局。现在，您的增量备份命令看起来如下所示：

RMAN> backup incremental level_1 for recover of copy with tag level_0 database;

在此，我们指示 RMAN 进行 level_1 增量备份，并将其与带有 level_0 标签的完全备份副本合并。在执行该命令之后，level_0 就成为了那一天的完全备份。

因此，在星期二，带有标签 level_0 的备份，当将其与 level_1 增量备份合并时，它就变得与完全的星期二备份相等。同样地，对于星期六采用的增量，当采用磁盘上的备份时，它将会与完全的 level_0 星期六备份相等。如果数据库在星期六发生故障，您只需恢复 level_0 备份外加一小份存档日志，使数据库一致；

在此不需要应用额外的增量。该方法显著地削减了恢复时间、加快了备份速度，并消除了再一次执行完全的数据库备份的需要。

压缩文件对于快速恢复区中基于磁盘的备份，仍有一个大的限制：磁盘空间。特别是当经网络进行时—通常情况下就是这样—那么创建一个尽可能小的备份集是明智的。在 Oracle 数据库 10g RMAN 中，您可以在备份命令内部压缩文件：

RMAN> backup as compressed backupset incremental level 1 database;

注意子句 COMPRESSED 的用法。它将用一个显著不同的方式压缩备份文件：在恢复时，RMAN 不用解压缩就能读取文件。为了确认压缩，检查如下的输出信息：

channel ORA_DISK_1:starting compressed incremental level 1 datafile backupset

此外，您可以通过检查 RMAN 列表输出来验证备份已被压缩：

RMAN> list output;

BS Key Type LV Size Device Type Elapsed Time Completion Time

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

3 Incr 1 2M DISK 00:00:00 26-FEB-04 BP Key:3 Status:AVAILABLE Compressed:YES Tag:TAG20040226T100154

Piece Name:/ora_flash_area/SMILEY10/backupset/2004_02_26/o1_mf_ncsn1_TAG20040226T100154_03w2m3lr_.bkp

Controlfile Included:Ckp SCN:318556 Ckp time:26-FEB-04

SPFILE Included:Modification time:26-FEB-04

对于任意的压缩过程，该方法都会对 CPU 产生压力。作为折衷，您可以在磁盘上保存更多的 RMAN 备份，它准备好为还原和恢复操作所用。此外，您可以在物理备用数据库上制作 RMAN 备份，它可用于恢复初始的数据库。该方法将备份源卸载到另一台主机上。

在您开始行动之前先看看：恢复预览在 Oracle 数据库 10g中，RMAN 通过提供执行恢复操作所需要的预览备份的能力向前迈进了一大步。

RMAN> restore database preview;

列表 1 显示了该操作的输出结果。您还可以预览特定的恢复操作；例如：

restore tablespace users preview;

预览允许您通过执行周期性的、有规则的检查，来确保您的备份基础架构的恢复准备就绪。

Resetlogs 和恢复假设您丢失了当前的联机重做日志文件，并且您不得不执行一个不完全的数据库恢复—一种很少见但听说过的情况。最大的问题是 resetlogs；在不完全的恢复之后，您必须用 resetlogs 子句打开数据库，它把日志线程的序列号设置为 1，会使您的 RMAN 中的早期备份作废并使恢复操作面临更多的挑战。

在 Oracle 9i 及其较低的版本中，如果您需要将数据库恢复到执行 resetlogs 操作之前的某个版本，您将不得不恢复到一个不同的拷贝。在 Oracle 数据库 10g 中，您不必这样做。由于控制文件中额外的基础架构，在执行 resetlogs 之前或之后，RMAN 现在都可以容易地使用所有备份来恢复 Oracle 数据库。它不需要关闭数据库来制作一个备份。这种新功能意味着在执行 resetlogs 操作之后，可以立即为用户社区重新打开数据库。

为 RMAN 作好准备

Oracle 数据库 10g RMAN 中的增强功能使它成为您的备份策略中的甚至更具强制性的工具。对增量备份过程的改进只会使 RMAN 难以被忽视。

有关 Oracle 10g 中的 RMAN 的更多信息，请参阅 Oracle Database Backup and Recovery Basics 10g 第 1 版 (10.1) 中第 4 章的内容。

第 10 周

审计告知一切

Oracle 数据库 10g 审计以一种非常详细的级别捕获用户行为，它可以消除手动的、基于触发器的审计假定用户 Joe 具有更新那张表的权限，并按如下所示的方式更新了表中的一行数据。

update SCOTT.EMP set salary = 12000 where empno = 123456;

您如何在数据库中跟踪这种行为呢？在 Oracle 9i 数据库及其较低版本中，审计只能捕获“谁”执行此操作，而不能捕获执行了“什么”内容。例如，它让您知道 Joe 更新了 SCOTT 所有的表 EMP，但它不会显示他更新了该表中员工号为 123456 的薪水列。它不会显示更改前的薪水列的值—要捕获如此详细的更改，您将不得不编写您自己的触发器来捕获更改前的值，或使用 Log Miner 将它们从存档日志中检索出来。

这两种方法都能让您跟踪更改的内容并记录更改前的值，但其成本非常高。使用触发器编写审计数据可能会对性能产生主要的影响；鉴于此，在某些情况下（如在第三方应用中）禁止使用用户定义的触发器。Log Miner 不会影响性能，但它是依赖于存档日志的可用性来跟踪更改的。

细粒度审计 (FGA)，是在 Oracle 9i 中引入的，能够记录 SCN 号和行级的更改以重建旧的数据，但是它们只能用于 select 语句，而不能用于 DML，如 update、insert 和 delete 语句。因此，对于 Oracle 数据库 10g 之前的版本，使用触发器虽然对于以行级跟踪用户初始的更改是没有吸引力的选择,但它也是唯一可靠的方法。

随着 Oracle 10g 的到来，由于审计能力的两个重大的改变，这些限制也随之而去。由于两种审计类型涉及到—标准审计（在所有版本中均可用）和细粒度审计（在 Oracle 9i 及其以上版本中可用）—我们将分别对它们进行处理，然后看看它们是如何相互补充以提供一个单一的、强大的跟踪功能。

新特性

首先，FGA 现在除了支持 select 语句外，还支持 DMA 语句。这些更改都记录在同一个位置，即表 FGA_LOG$ 中，并通过 DBA_FGA_AUDIT_TRAIL 视图显示出来。除了 DML 外，您现在可以选择只有在访问了所有或者甚至很少的相关的列后，才可以触发一个线索。（有关 FGA 在 Oracle 10g 中是如何工作的详细信息，请参阅该主题的我的技术文章的内容。）

标准审计，是由 SQL 命令 AUDIT 执行的，可用于为特定的对象快速、容易地设置跟踪。例如，如果您想跟踪对 Scott 所拥有的表 EMP 的所有更新，您可以发出如下命令：

audit UPDATE on SCOTT.EMP by access;

任何用户每一次更新表 SCOTT.EMP 时，该命令都会把所有的更新记录到审计跟踪表 AUD$ 中，可以通过 DBA_AUDIT_TRAIL 视图来查看。

这个功能对于 Oracle 10g 之前的版本也是可用的。但是，在那些版本中，写到跟踪中的信息仅限于少数相关的项，如：发出该语句的用户、时间、终端标识号等等；它缺少某些重要的信息，如绑定变量的值。

在 Oracle 10g 中，除了以前的版本中所收集到的内容之外，审计操作还捕获了许多这些重要的信息片断。

用于审计的原始表 AUD$，包含若干个用于记录它们的新列，相应地，DBA_AUDIT_TRAIL 视图也包含这些列。让我们详细地研究一下。

EXTENDED_TIMESTAMP。该列以 TIMESTAMP (6) 格式记录了审计记录的时间戳，它是用格林尼治标准时间（也称为全球统一时间）来记录时间的，其小数点后的秒数到 9 为止，并且带有时区信息。以这种格式存储的时间的一个例子如下所示。

2004-3-13 18.10.13.123456000 -5:0

日期表示为 2004 年 3 月 13 日，是美国的东部标准时间，它比全球统一时间晚 5 小时（用 -5.0 来表示）。

这种以扩展格式显示的时间有助于把审计跟踪精确定位到一个更窄的时间间隔中，从而增强了它们的用途，特别是在数据库横跨多个时区时更是如此。

GLOBAL_UID 和 PROXY_SESSIONID。当使用某种身份管理组件如 Oracle Internet Directory 进行身份验证时，用户对数据库的访问权限稍有不同。例如，当将他们访问数据库时，可能将他们视为企业用户。审计这些用户不会在 DBA_AUDIT_TRAIL 视图的 USERNAME 列中记录他们的企业用户标识号，以使该信息无用。在 Oracle 数据库 10g 中，全局（或企业）用户唯一的标识号记录在 GLOBAL_UID 列中，并且没有作进一步的处理或设置。该列可用于查询目录服务器，以查找有关该企业用户的完整的详细信息。

有时企业用户也许是通过一个代理用户连接到数据库，特别是在多层应用中。可以通过命令为用户提供代理身份验证 alter user scott grant connect to appuser;

该命令将允许用户 SCOTT 以 APPUSER 的身份，作为代理用户连接到数据库。在那种情况下，COMMENT_TEXT 列将通过存储值 PROXY 来记录事实；但是对于 Oracle 9i 而言，代理用户的会话标识号将不会进行记录。在 Oracle 10g 中，PROXY_SESSIONID 列记录了它，用于精确标识代理会话。 INSTANCE_NUMBER。在 Oracle 真正应用集群 (RAC) 环境中，它可能有助于知道在进行更改时用户连接的是哪一个特定的例程。在 Oracle 10g 中，该列记录了例程号，它是由该例程的初始化参数文件指定的。

OS_PROCESS。在 Oracle 9i 及其较低的版本中，只会在审计跟踪中记录 SID 值；而不会记录操作系统进程标识号。但是，服务器进程的操作系统进程标识号随后可能是必要的，例如，用于交叉引用一个线索文件。在 Oracle 10g 中，该值也记录在该列中。

TRANSACTIONID。在此就产生了最关键的信息价格。假定用户发出下面的命令

update CLASS set size = 10 where class_id = 123;

commit;

该命令获取一个事务项，并且生成一个审计记录。但是，您怎样知道该审计记录真正记录的是什么内容呢？

如果记录是一个事务，该事务标识号就会存储在该列中。您可以使用它把审计跟踪与

FLASHBACK_TRANSACTION_QUERY 视图联接起来。下面是该视图中的列的一个小示例。

select start_scn, start_timestamp, commit_scn, commit_timestamp, undo_change#, row_id, undo_sql from flashback_transaction_query where xid = '<the transaction id>';

除了记录对该事务所做的通常的统计外，如 undo change#、rowid 等等，Oracle 10g 还可以在 UNDO_SQL 列中记录撤消对事务所作更改 SQL 命令，以及在 ROW_ID 列显示的受影响行的 rowid。

系统更改号。最终，它记录更改前的值。您怎样执行该操作呢？按 Oracle 9i 中的 FGA 所指出的那样，更改前的值可以通过闪回查询来获取。但是您需要知道该更改的系统更改号 (SCN)，它可以在审计跟踪的该列中捕获到。您可以发出下面的命令 select size from class as of SCN 123456 where where class_id = 123;

这将显示用户所看到的内容或更改前的值。

扩展的 DB 审计记住我们最初的兴趣：为了捕获用户发出的 SQL 语句，以及在标准审计中无法捕获的绑定变量。在 Oracle 数据库 10g 中进入增强型审计，其中这些任务变得如同更改一个简单的初始化参数一样微不足道。只需把下列代码行放入参数文件中。

audit_trail = db_extended

如果使用该参数，该参数将在各列中记录 SQL 文本和绑定变量值。该值在早期的版本中不可用。

触发器何时是必要的避免误检。审计跟踪是通过来自于原始事务的自治事务生成的。因此，即使原始事务回滚，它们也会提交。有一个简单例子演示了这一点。假定我们已在表 CLASS 上为 UPDATE 设置了审计。用户发出一条语句以将数据值从 20 更新为 10，然后将其回滚，如下所示。

update class set size = 10 where class_id = 123; rollback

现在该列的 SIZE 值将变成 20，而不是 10，好像用户从未做过任何事情。但是，即使回滚，审计跟踪也将捕获该更改。在某些情况下这可能不是人们所想要的，尤其是用户执行了许多回滚时。在这种情况下，您也许不得不使用触发器仅捕获已提交的更改。如果表 CLASS 上有一个触发器用于将记录插入到用户定义的审计线索中，在回滚的基础上审计线索也被回滚。

捕获之前更改的值。 Oracle 提供的审计跟踪不会显示更改前后的值。例如，上述的更改将创建一个审计记录，它显示了语句和更改的 SCN 号，但没有显示更改前的值 (20)。可以使用闪回查询通过 SCN 号获取该值，但是它依赖于在撤消段中可用的信息。如果该信息无法在由 undo_retention 时间段指定的期限内捕获到，就永远不能检索出先前的值来。使用触发器保证了无需依赖于 undo_retention 时间段即可捕获到该值，并且有时很有用。在这两种环境下，您可以决定继续使用触发器以细粒度的级别来记录审计跟踪。

统一的审计跟踪

由于 FGA 和标准审计捕获的是相同类型的信息，当把它们结合起来使用时可以提供许多重要的信息。

Oracle 数据库 10g 把这些跟踪合并到一个称为 DBA_COMMON_AUDIT_TRAIL 的通用跟踪中，它是 DBA_AUDIT_TRAIL 视图和 DBA_FGA_AUDIT_TRAIL 视图的一个 UNION ALL 视图。但是，在这两种审计类型之间有一些重大的区别。

结论

在 Oracle 10g 中，审计已经从一个单纯的“操作记录者”成长为一个“事实记录机制”，它能以一个非常详细的级别来捕获用户的行为，这可以消除您对手动的、基于触发器的审计的需要。它还结合了标准审计和 FGA 的跟踪，这使其更易于跟踪数据库访问，而不用考虑它是如何生成的。

有关附加信息，请参阅 Oracle Database Security Guide 10g 第 1 版 (10.1)中的第 11 章的内容。

第 11 周

等待界面

10g 等待界面为还没有被 ADDM 捕获的即时性能问题提供了有价值的诊断数据

“数据库太慢了！”

这句话通常出自一位严格的用户之口。如果您和我一样，那么在您的 DBA 生涯中您肯定无数次听到过这句话。

那么，您又怎样解决该问题呢？除了对用户置之不理之外（这是我们大多数人都不敢奢望的想法），您可能要做的第一件事就是查看是否有任何会话在等待数据库内部或外部的任何事件。

Oracle 提供了一个简单但一流的机制来达到此目的：V$SESSION_WAIT 视图。该视图显示了有助于您的诊断的各种信息，如一个会话正在等待或已经等待的事件，以及等待了多长时间和多少次。例如，如果会话在等待事件 "db file sequential read"，列 P1 和 P2 将显示会话正在等待的块的 file_id 和 block_id。

对于大多数等待事件而言，这个视图足够了，但它还不是一个强健的调整工具，之所以如此说，至少是因为以下两个重要原因：

• 该视图是当前情况的一个快照。当等待不再存在时，会话先前出现的那些等待的历史也将消失，从而使得事后诊断非常困难。V$SESSION_EVENT 提供了累积的但不是非常详细的数据。

• V$SESSION_WAIT 包含了只与等待事件相关的信息；要获得所有其它的相关信息（如用户 ID 和终端），您必须将它和 V$SESSION 视图结合使用。

在 Oracle 数据库 10g 中，等待界面经过了彻底的重新设计，从而只需更少的 DBA 干预即可提供更多的信息。在本文中，我们将浏览这些新的特性，并了解它们如何帮助我们诊断性能问题。对于大多数性能问题，您可以从自动数据库诊断管理器 (ADDM) 中获得扩展分析，但对于还没有被 ADDM 捕获的即时问题，等待界面将提供有价值的诊断数据。

增强的会话等待第一个增强涉及到 V$SESSION_WAIT 本身。这一点通过示例可以很好地说明。

假定您的用户抱怨会话挂起了。您查明了该会话的 SID，并在 V$SESSION_WAIT 视图中选中了该 SID 的记录。输出显示如下。

SID : 269

SEQ# : 56

EVENT :enq:TX - row lock contention

P1TEXT :name|mode

P1 : 1415053318

P1RAW : 54580006

P2TEXT :usn<<16 | slot

P2 : 327681

P2RAW : 00050001

P3TEXT :sequence

P3 : 43

P3RAW :0000002B

WAIT_CLASS_ID : 4217450380

WAIT_CLASS# : 1 WAIT_CLASS : Application

WAIT_TIME : -2

SECONDS_IN_WAIT : 0

STATE :WAITED UNKNOWN TIME

注意以黑体显示的列；在这些列中，WAIT_CLASS_ID、WAIT_CLASS# 和 WAIT_CLASS 是 10g 中新增的列。列 WAIT_CLASS 指示等待的类型，必须将其作为有效的等待事件解决或者作为空闲的等待事件退出。

在上面的例子中，等待类显示为 Application，这表示它是一个需要您注意的等待。

该列突出显示那些能够证明与您的调整最相关的少数几条记录。例如，您可以使用如下查询来获取事件的等待会话。

select wait_class, event, sid, state, wait_time, seconds_in_wait

from v$session_wait

order by wait_class, event, sid

下面是一个样例输出：

WAIT_CLASS EVENT SID STATE WAIT_TIME

SECONDS_IN_WAIT

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

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

Application enq:TX - 269 WAITING 0

row lock contention

Idle Queue Monitor Wait 270 WAITING 0

Idle SQL*Net message from client 265 WAITING 0

Idle jobq slave wait 259 WAITING 0

8485

Idle pmon timer 280 WAITING 0

Idle rdbms ipc message 267 WAITING 0

184770

Idle wakeup time manager 268 WAITING 0

Network SQL*Net message to client 272 WAITED SHORT TIME -1

在这，您可以看到几个事件（如 Queue Monitor Wait 和 JobQueue Slave）被明确地归为 Idle 事件。

您可以将它们作为非阻塞等待消除掉；不过，有时这些“空闲”事件可能指示一个内在的问题。例如，与 SQL*Net 相关的事件可能指示高网络延迟（除其他因素外）。

另一件要注意的重要的事情是，WAIT_TIME 的值为 -2。某些平台（如 Windows）不支持快速计时机制。

如果在这些平台上没有设定初始化参数 TIMED_STATISTICS，那么将无法获得准确的计时统计数据。在这种情况下，在 Oracle9i 中，该列将显示一个非常大的数字，这使问题变得更加不清晰。在 10g 中，值 -2 指示这种情况 — 平台不支持快速定时机制并且没有设定 TIMED_STATISTICS。（对于本文剩下的部分，我们将假定存在一个快速计时机制。）

会话也显示等待记得长期以来一直需要将 V$SESSION_WAIT 与 V$SESSION 结合使用以获得有关会话的其他详细信息吗？嗯，这已经成为历史了。在 10g 中， V$SESSION 视图还显示由 V$SESSION_WAIT 显示的等待。

下面是 V$SESSION 视图其余的列，这些列显示了会话当前等待的等待事件。

EVENT# NUMBER

EVENT VARCHAR2(64)

P1TEXT VARCHAR2(64)

P1 NUMBER

P1RAW RAW(4)

P2TEXT VARCHAR2(64)

P2 NUMBER

P2RAW RAW(4)

P3TEXT VARCHAR2(64)

P3 NUMBER

P3RAW RAW(4)

WAIT_CLASS_ID NUMBER

WAIT_CLASS# NUMBER

WAIT_CLASS VARCHAR2(64)

WAIT_TIME NUMBER

SECONDS_IN_WAIT NUMBER

STATE VARCHAR2(19)

这些列与 V$SESSION_WAIT 中的那些列相同，且显示相同的信息，从而不再需要在那个视图中查看它们了。因此，对于等待任意事件的任意会话，您仅需要查看一个视图。

让我们回到原来的问题：SID 为 269 的会话正等待事件 enq:TX — row lock contention，指示它正等待被另一个会话占用的锁。要诊断该问题，您必须识别占用锁的那个会话。但您如何才能做到这一点？

在 Oracle9i 及更低版本中，您可能得编写复杂（和极耗资源）的查询来获得占用锁的会话的 SID。而在 10g 中，您所要做的就是执行以下查询：

select BLOCKING_SESSION_STATUS, BLOCKING_SESSION

from v$session

where sid = 269

BLOCKING_SE BLOCKING_SESSION

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

VALID 265

找到了：SID 为 265 的会话阻塞了会话 269。还能更容易吗？

有多少等待？

用户仍然在缠着您，因为用户的问题仍然没有得到满意的解答。为什么用户的会话花了这么长时间才完成？

您可以执行以下命令来找出原因：

select * from v$session_wait_class where sid = 269;

输出返回为：

SID SERIAL# WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS TOTAL_WAITS TIME_WAITED

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

269 1106 4217450380 1 Application 873 261537

269 1106 3290255840 2 Configuration 4 4

269 1106 3386400367 5 Commit 1 0 269 1106 2723168908 6 Idle 15 148408

269 1106 2000153315 7 Network 15 0

269 1106 1740759767 8 User I/O 26 1

注意这里有关会话等待的大量信息。现在您知道了，该会话已经为与应用程序相关的等待等待了 873 次（共 261,537 厘秒），在与网络相关的事件中等待了 15 次等等。

以此类推，您可以使用以下查询来查看系统范围的等待类的统计数据。同样，时间是以厘秒为单位的。

select * from v$system_wait_class;

WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS TOTAL_WAITS TIME_WAITED

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

1893977003 0 Other 2483 18108

4217450380 1 Application 1352 386101

3290255840 2 Configuration 82 230

3875070507 4 Concurrency 80 395

3386400367 5 Commit 2625 1925

2723168908 6 Idle 645527 219397953

2000153315 7 Network 2125 2

1740759767 8 User I/O 5085 3006

4108307767 9 System I/O 127979 18623

大多数问题不是孤立出现的；它们留下了揭示真相的线索，模式可以识别这些线索。可以按如下方式从等待类的一个历史视图中查看模式。

select * from v$waitclassmetric;

这个视图存储了最后一分钟内与等待类相关的统计数据。

select wait_class#, wait_class_id,

average_waiter_count "awc", dbtime_in_wait,

time_waited, wait_count

from v$waitclassmetric

WAIT_CLASS# WAIT_CLASS_ID AWC DBTIME_IN_WAIT TIME_WAITED WAIT_COUNT

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

0 1893977003 0 0 0 1

1 4217450380 2 90 1499 5

2 3290255840 0 0 4 3

3 4166625743 0 0 0 0

4 3875070507 0 0 0 1

5 3386400367 0 0 0 0

6 2723168908 59 0 351541 264

7 2000153315 0 0 0 25

8 1740759767 0 0 0 0

9 4108307767 0 0 8 100

10 2396326234 0 0 0 0

11 3871361733 0 0 0 0

注意 WAIT_CLASS_ID 和相关的统计数据。对于值 4217450380，我们看到 2 个会话在最后一分钟内总共等待了该类 5 次（1,499 厘秒）。但该等待类是什么？您可以从 V$SYSTEM_WAIT_CLASS 中获取这一信息（如上所示）— 就是 Application 类。

注意名称为 DBTIME_IN_WAIT 的列，这是一个非常有用的列。在我们第 6 周关于自动工作负载信息库 (AWR) 的部分中，您可能还记得在 10g 中是以更细粒化的方式来报告时间的，并且可以确定在数据库中花费的准确时间。DBTIME_IN_WAIT 显示在数据库中花费的时间。

一切都留有线索

用户终于离开了，您长舒了一口气。但您可能仍然想寻根究底，希望查明主要是哪些等待造成用户会话中的问题。当然，您可以通过查询 V$SESSION_WAIT 而轻易地得到答案 — 但不幸的是，等待事件现在不存在了，因此该视图没有它们的任何记录。您该怎么办？

在 10g 中，自动保留活动会话最后 10 个事件的会话等待历史。这个历史可通过

V$SESSION_WAIT_HISTORY 视图查看。要找出这些事件，您可以简单地执行：

select event, wait_time, wait_count

from v$session_wait_history

where sid = 265

EVENT WAIT_TIME WAIT_COUNT

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

log file switch completion 2 1

log file switch completion 1 1

log file switch completion 0 1

SQL*Net message from client 49852 1

SQL*Net message to client 0 1

enq:TX - row lock contention 28 1

SQL*Net message from client 131 1

SQL*Net message to client 0 1

log file sync 2 1

log buffer space 1 1

当会话变为非活动状态或断开时，记录从该视图中消失。不过，这些等待的历史保留在 AWR 表中，以便进一步分析。从 AWR 中显示会话等待的视图是 V$ACTIVE_SESSION_HISTORY。（同样，有关 AWR 的更多信息，请参考本系列的第 6 周。）

结论

通过 Oracle 数据库 10g 中的等待模型的增强，分析性能问题变得非常容易。提供的会话等待历史可以帮助您在会话经历等待后诊断问题。将等待归为各种等待类还有助于您了解每种类型等待所造成的影响，这在研究正确的纠正方法时将带来便利。

有关等待事件动态性能视图和等待事件本身的更多信息，请参考《Oracle 数据库性能调整指南 10g 第 1 版 (10.1)》的第 10 章。

第 12 周

物化视图

利用强制查询重写和新的强大的调整顾问程序 — 它们使您不再需要凭猜测进行工作 — 的引入，在 10g 中管理物化视图变得更加容易物化视图 (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 数据库 10g 之前，这是用 DBMS_MVIEW 程序包中的 EXPLAIN_MVIEW 和 EXPLAIN_REWRITE 过程来判断的。这些过程（在 10g 中仍然提供）非常简要地说明一种特定的功能 — 如快速刷新功能或查询重写功能 — 可能用于上述的物化视图，但不提供如何实现这些功能的建议。相反，需要对每一个物化视图的结构进行目视检查，这是非常不实际的。

在 10g 中，新的 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。在实际情况下，这个结果可能无法接受，因为无法提供所需的额外资源，因此您可能想自己重写查询。在这种情况下，您可以确保如果而且只有在查询被重写的情况下，才允许进行查询。

在 Oracle9i 数据库和更低版本中，决策是单向的：您可以禁用查询重写，但不能禁用基础表访问。不过 Oracle 数据库 10g 提供了一种机制 — 通过一个特殊的提示 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 是否被使用 — 即使名称没有表明段的本质。

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

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