统计信息 Jonathan Lewis DBA 性能和可用性
2024-07-21 02:37:59
供稿：网友
了解向一个 Oracle 数据库新版本移植时，优化程序使用系统统计将给移植带来很大的不同。
Oracle 数据库引擎最重要的组件是什么？我认为是优化程序。数据库所做的一切都涉及到 SQL，必须转换每一段 SQL，使其能够有效运行。 而无论您对数据进行什么操作，都要涉及到优化程序。
基于成本的优化程序 (CBO) 是一段精细复杂的代码，因此当它第一次出现在 Oracle7 中时，存在一些问题就不足为怪了。不过，在经过为数不多的几个补丁版本之后，它用来实施纯粹的基于成本的系统就变得完全可行了。我记得在 7.1 中安装了几个这样的系统，在 7.2 中至少安装了一个重大的系统。
尽管如此，仍有惊人数量的站点很长时间不愿使用 CBO — 原因是许多开发人员和 DBA 仍以“基于规则”的方式进行思考，因此他们所作的事情使 CBO 很难进行工作。从根本上讲，他们没有为优化程序提供真实的信息，然后却迷惑为什么它会作出糟糕的决策。
我猜想我最早成功的原因是我碰巧注重到了一些现在已理解透彻的事情：当参数 db_file_multiblock_read_count 的值较大时，CBO “喜欢”执行表扫描，而当这个值较小时，CBO “更喜欢”使用索引的事实。正如我们现在所了解的，其它一些参数表现出类似的特性 — 也就是说，RBO 喜欢它们的值大，而 CBO 需要它们的值小。当然，有一个小细节：在最后的比较中，CBO 将在 from 子句中沿着表的列表向下处理，而 RBO 将沿着该列表向上处理。
CBO 与 RBO 很不相同 — 但只有一些非凡要害的差异您必须了解，因为假如您不了解它们而仍用与对待 RBO 相同的方式来对待 CBO，那么您将给自己带来很多不必要的问题。
历史总在重演
CBO 总在发展。一些 Oracle 版本引进了次要增强，一些版本引进了主要增强，一些版本改变了范例。
除非您发现了主要增强和范例转变，您才能结束多年来一直打击 CBO，试图解决旧缺点而不是发挥新优势 — 就像许多人在从 RBO 向 CBO 转变时所做的那样。
那么在 CBO 中是否有任何新变化能够对我们的思维方式产生和从 RBO 向 CBO 转变一样大的影响呢？答案是肯定的。
在 Oracle9i 数据库中，Oracle 增加了系统统计（我认为它是一种新的范例）和自动工作区大小调整（我认为它是一个主要增强）。在 Oracle 数据库 10g 中，我们获得了各种调整顾问工具，这些顾问工具通过帮助开发人员更快速地创建更多有效的 SQL、索引、物化视图或 PL/SQL 来优化开发人员的时间。我们还获得了查询概要文件，这是一个主要增强，它将统计信息存储在数据库中，以帮助优化程序作出更好的决策。这种信息在您必须处理无法接触的第三方 SQL 时非凡有用。
在这两个版本中，我们获得了动态采样 (dynamic_sampling)，这是在 9i 中引进的一种非常有用的特性，对数据仓库和决策支持系统非凡有好处。但假如您没有留意 9i 中的动态采样特性，那么当您在 10g 上运行 OLTP 系统时就可能变为麻烦事，因为动态采样是高效地默认启用的，它可能成为不必要的开销。
所有这些变化中最重要的是引进了系统统计。实际上，我甚至可以说从 Oracle8 向 Oracle9i 移植的要害步骤之一是启用系统统计，并充分了解它们对系统的影响。同样，系统统计真的对充分利用 10g 中的优化程序相当要害，熟悉系统统计的最佳时间是您决定从 8i 中进行移植的时候。
这个特性如此重要，因此我将在本文剩下的部分中专门对其进行说明，而将动态采样和概要文件留待改天再进行讨论。
系统统计
在 Oracle9i 之前，CBO 的计算基于满足一个查询所需的 I/O 请求的数量，它使用各种常量来为表扫描处理数字，并引入了一些规则来解决诸如小型索引高速缓存之类的事情。（请参阅我的 DBAzine.com 文章“为什么 Oracle 不使用我的索引？”[获取对该主题的介绍。）
最早在优化程序中建立的一些假设有点幼稚，但随着时间的推移，这些假设经过了提炼，算法得到了改进，新的特性也得到了实施。然而，估计 I/O 请求的副作用成为一个持久的限制。
在 9i 中，Oracle 引进了 cpu_costing，这是答应将一次操作的 CPU 成本包含在总体估计的一部分中的一种机制。在 9i 中，只有当您收集了系统统计数据时，才启用这个特性；在 10g 中，它的启用是默认的。
那么 cpu_costing 做些什么，系统统计具体是什么？让我们从系统统计开始，并使用几个对 dbms_stats 程序包的调用来进行演示。（这个例子使用 9.2.0.4，要进行工作，您的帐号将需要拥有 gather_system_statistics 权限。）
execute dbms_stats.gather_system_stats('Start');
— 当数据库处于典型工作负载下时可能延迟一段时间
execute dbms_stats.gather_system_stats('Stop');
要看看您做了什么，您可以查询一个名称为 aux_stats$ 的表（由 SYS 模式所有）。
在收集了系统统计数据之后，这个表将包含一些由新的优化器算法用来计算成本的要害数字。（不过，您必须刷新 shared_pool，以使现有的执行计划无效。）以下查询将向您显示当前的设置：
select pname, pval1
from sys.aux_stats$
where sname = 'SYSSTATS_MAIN';
确切的结果列表取决于版本（代码仍在发展，Oracle 的一些版本比其它版本收集更多的统计数据），但您将可能看到类似以下的内容：
PNAME PVAL1
------------------------------ ----------
CPUSPEED 564
MAXTHR 13899776
MBRC 6
MREADTIM 10.496
SLAVETHR 182272
SREADTIM 1.468
10g 还引进了一些额外的行，这些行的值在数据库启动时设置：
CPUSPEEDNW 904.86697
IOSEEKTIM 10
IOTFRSPEED 4096
为方便起见，我这里提到的是 SQL；实际上，批准查看这些信息的方法是 dbms_stats 程序包中的 get_system_stats 过程。假如您想“调整”这些值（没有正确地收集它们），那么还有一个 set_system_stats 过程。
表扫描
当提供系统统计时，在优化程序成本计算上有两个显著的变化。首先您将注重到 sys.aux_stats$ 保存了以下参数的值：
sreadtim：一次单块读请求的平均时间（以毫秒为单位）
mreadtim：一次多块读请求的平均时间（以毫秒为单位）
MBRC： 一次多块读操作中的平均块数。
利用这些信息，Oracle 可以估计执行一次表扫描（或索引快速全扫描）要花多长时间。计算很轻易：它就是执行扫描所需的多块读操作的数量乘以执行一次多块读操作的平均时间。忽略因自动段空间治理造成的小的变化，我们只注重高水线标记，并从那里开始工作：
完成时间＝ mreadtim * HWM / MBRC。
Oracle 根据等价的单块读操作数重新声明了“完成时间”，而不是将“完成时间”报告为查询的成本。为此，只需将完成时间除以一次单块读操作的平均时间。
成本＝完成时间/ sreadtim
或者，将这两个公式放在一起并重新排列项目：
表扫描的成本＝ (HWM / MBRC) * (mreadtim / sreadtim)
从这个例子中您可以看到，查询的成本是查询的完成时间，但用单块读操作为单位进行表示而不是使用正常的时间单位。
当您开始使用系统统计时，优化程序在表扫描和索引访问路径之间作选择时自动开始变得更“明智”，因为用于表扫描的多块读操作的成本将包含一个正确和适当的时间组成部分。
过去，表扫描的成本很简单：
表扫描的成本＝ HWM /（修改后的 db_file_multiblock_read_count）。
您为参数 db_file_multiblock_read_count 选择的值可能不实际，而这个公式几乎没有考虑这一事实，也没有考虑与单块读操作相比，一个极大的 db_file_multiblock_read_count 将花费的额外时间。
这个缺点是 Oracle 在 8.1.6 中创建 optimizer_index_cost_adj 参数的主要原因，这个参数使您能够引进实质上类似于您在系统统计中收集的 mreadtim 的一个因子。（您可能已经发现了新的成本公式中的 mreadtim/sreadtim 部分和估计一个切合实际的 optimizer_index_cost_adj 的常见方法之间的相似性。）但使用 optimizer_index_cost_adj 参数存在一些可能产生问题的意外副作用，而在您开始使用系统统计时开始起作用的一些机制变得更强健得多。
顺便提一下，甚至在使用系统统计时，使用 optimizer_index_cost_adj 作为表高速缓存效果（非凡是，多少百分比的单块表读操作可能转变成实际的读请求）的一个线索仍然很有意义。不过，在 10g 中会提供一些信息，使得甚至这种线索在不远的将来也将变得不必要。
CPU 成本
系统统计不仅能够修正单块读操作和多块读操作之间的 I/O 和时间权衡。它们还考虑到了对成本计算的两个进一步的增强（或修正）：首先，Oracle 甚至能够更好地根据索引访问路径平衡表扫描；其次，Oracle 能够智能地重新安排猜测顺序。
注重统计如何包含表面的 CPU 速度（名义上以 MHz 为单位）。假如这与您的系统的实际 CPU 速度一点也不相同，请不用大惊小怪 — 这个数字可能只是一个基准线操作的内部校准，Oracle 使用这种基准操作来
