我在Practical Oracle 8i一书的前言中曾经提到“如果我们想写一本关于Oracle的技术性书籍，那么在写完时，里面的内容肯定已经过时”。Addison-Wesley公司出版这本书的时候正好是Larry Ellison声明发布Oracle 9i的官方版本之时。这本书出版后两个星期，我接到了第一封E-mail询问是否已经计划撰写一本关于Oracle 9i的书。 在此之前，我拒绝了所有关于“升级”的请求，这是基于以下3个原因：(a)这需要太多的精力，(b)在我可以将Oracle 9i的信息融入到本书中之前，可能需要两年的时间来熟悉9i，(c)它仍然只是前面这本书的简单修改。 因此，我的计划就是：2003年9月开始撰写这本书(确实是2003年，我花了22个月的时间才写完)，正好是我决定撰写Practical Oracle 8i的4年之后。(请记住，在1999年9月，如果一个Oracle专家不会调试已经40岁高龄的COBOL语言，那么不会有哪家公司会雇佣他)。我花费了大量的时间来学习Oracle 9i，以保证能够学习一些全新的内容。当然，在这4年中，出现了很多Oracle 8i的升级版本(最终版为8.1.7.4)，出现了Oracle 9i的两个主要版本，当开始撰写本书时，Oracle公司已经推出了Oracle 10g。因此，我撰写Practical Oracle 9i这一想法可能已经过时了。 事实上，当结束了本书的撰写工作之后(2005年6月)，针对Oracle 10g R2的Linux端口已经在OTN上可用了！因此，您阅读本书之后所做的第一件事情就应该是在10g R2下运行本书的示例，以检查究竟有哪些地方发生了变化。 本书并不是对Practical Oracle 8i的简单升级，本书中介绍了关于基于成本优化的相关知识。这看起来是一个非常简单的任务—— 针对优化器所执行的操作和为什么执行这样的操作我可以谈上几个钟头；但是我要做的是要把这些知识成书出版。 遗憾的是，这一任务比想象的要困难得多。把很多词语堆砌起来是比较容易的—— 但是将它们组织成为一本比较有用的结构良好的书籍却是另一回事。给我关于CBO的一些问题，我能够解释其中的原因—— 也许是在创建和测试了一些理论之后。但是，要想为其他人解释关于优化器的一些通用的信息并让他们也能得出同样的结果，这完全不是一码事。 最终，我成功地构建了本书的框架，并意识到必须撰写3本书：基础知识、某些增强的内容和所有的外围知识。本书专门介绍关于基于成本的优化的基础知识。 0.1 写作本书的动机 为什么需要了解CBO的工作原理呢？这是因为当遇到优化器产生的执行计划非常差的情况时，需要理解这究竟是什么问题导致的，并给出正确的修复方法。 当然，我们可以通过为SQL添加一些提示或者进行一些巧妙的代码重写操作的方式来修复一些问题，但是如果采用这两种方式，当同样的问题出现在其他地方时，就必须一再重复执行类似的操作。 从另一方面讲，如果理解了问题的本质内涵，那么就可以一次性地修复该问题，而且在其他地方出现同样的问题时就可以知道该如何解决。 0.2 本书内容 本书涵盖了关于优化的基本信息。本书并不是为了成为优化器工作原理的完全参考手册—— 本书一共有14章，但是直到第10章才提到连接，由此您可能会意识到其中有大量的内容需要介绍。 优化过程中常见的重要术语有： ● 选择率(selectivity)和基数(cardinality)：谓词将标识多少数据片断以及可以转换为多少数据行。 ● 访问路径(access path)：当访问表时，查询应该使用B树索引、联合使用一对位图索引，还是完全忽略索引。 ● 连接顺序(join order)：查询应该首先访问哪一个表，为了获得指定的结果，从何处开始工作量最少。 虽然有时候我也会对需要考虑的一些微妙特性作些注释，但是，本书实际上只着重于少数核心概念的介绍。优化器如何计算谓词将产生多少数据？它如何用一个数值来表示表扫描的工作，如何将其与使用索引所需的工作进行对比？用哪些数据来估计排序或散列连接所用到的资源？ 我可以分析一个查询、查询中的对象以及10053 trace文件，并解释某条路径为什么比另一条路径优先使用。遗憾的是，我无法保证读者每次都能获得与书中trace文件同样的结果，因为示例中无法调试所有的选项(我从来也没有使用过全部的选项)，那样的话本书将变成一本非常冗长乏味的书。 尽管本书无法解决所有的问题，但是，我相信本书已经给出了所有的基本方法，利用它们可以解释绝大部分情况下的问题。 0.3 本书中没有包含的内容 本书不可避免地会忽略一些内容。有些内容被忽略是因为它们不属于优化器的核心行为，还有一些被忽略是因为不是很常用，出于篇幅的原因必须排除在外。 本书根本没有提到基于规则的优化器(Rule Based Optimizer，RBO)，这是因为每个人都应尽量避免使用它。本书也没有介绍关于可扩展优化器的任何信息(包括上下文和空间索引)，这是因为它们不是主流主题。本书也没有提到分析函数、模型子句(10g)和OLAP (联机分析处理)，这是因为它们在进行自身类型的数据处理之前必须获取相关的数据—— 而数据获取可能是时间要求最严格的工作。 本书没有提到对象—— 这是因为就优化器而言，它们并不存在。当您创建一个对象类型并创建对象类型的数据片断时，Oracle会将它们转换为简单的表和索引—— 优化器根本不关心对象。 最后，本书几乎没有介绍并行查询、分区表、分布式查询和Oracle中一些比较微妙的物理选项，比如群集和IOT。这出于以下两个原因：第一，篇幅不够；第二，避免影响对主题的介绍。对于优化器来说，存在多种不同的知识信息，要想集中介绍某一种知识将非常困难，因此，最好的办法就是在某个时间段内只介绍一部分知识。 0.4 本系列其他两本书中将出现的内容 本书是这一系列中的第一本。接下来的两本将介绍Oracle中的一些重要特性，尤其是分区表、并行执行、索引组织表、动态采样和查询重写。 对本书中已经提到的一些知识也将进一步进行介绍，比如B树索引的更多访问路径、群集访问和索引访问之间的比较以及其关于直方图的更为详细的信息。 关于基于成本优化的信息的最后一部分是支持优化的基础结构，有助于理解基于成本的优化。此处主要的问题是理解并解释执行计划，理解提示的含义并应用提示，最有效地使用dbms_stats包。 本书是基于Oracle 9.2撰写的，其中注意了与8i的一些区别，并介绍了Oracle 10g中对应内容的变化。接下来的两本书将基本上不介绍8i，更多地介绍Oracle 10g。 0.5 本书组织结构 本书一共14章，按照顺序涵盖了如下主题： ● 表扫描：比较简单，作为入门，介绍了一些关于CPU成本计算的知识。 ● 简单选择率：只有一个表，但包含了大量的关于算法的知识。 ● 简单B树索引：单块读取和多块读取的区别。 ● 群集因子：这也许是索引中最关键的特性。 ● 更微妙的选择率：介绍一个基本主题上的大量微小变化。 ● 直方图：为什么需要直方图，并介绍了OLTP和DSS/DW之间的区别。 ● 位图索引：并不是所有的索引都是相同的。 ● 变换：看到的不一定是得到的。 ● 连接：用4整章的内容来介绍两个表之间的连接。 ● 10053 trace：演示性示例。 ● 升级问题：从本书其余部分整理的警告和注意信息的集合。 每一章中都包含很多代码，它们都来自于一组SQL脚本，可以从Apress网站或www.tupwk.com.cn/downpage上下载。您可以在自己的系统上运行这些脚本，以重新产生和分析本书中给出的结果。必须认真地分析这些脚本，因为其中包含的一些额外注释和额外测试在书中并没有提及。我同时还在自己的个人网站上公布了这些脚本，并不时地增加新的内容。 这些脚本非常重要—— 如果进行修改，会对生产系统产生严重的影响。如果采用脚本来测试基本机制，则可以在每次升级时都重新运行这些测试，以观察会出现什么样的变化。 需要注意的一点是书中出现的脚本中经常会包含一些代码行来标准化测试环境， 例如： alter session set "_optimizer_system_stats_usage" = false; 不要因为本书中使用了就在生产系统中使用这样的命令。这并不是一个很好的编程习惯；采用这样的命令的目的在于避免当某个数据库(比如)的系统统计信息和另外一个的完全不同时所导致的副作用。 联机代码包中还包括3个init.ora文件和1个用于在Oracle 9i和10g下创建测试表空间tablespace的脚本。这4个文件必须被重新编辑，以避免出现文件路径命名导致的问题；Oracle 9i和10g下的init.ora文件也必须进行调整以适应于rollback/undo的管理选项。我选择针对Oracle 9i和10g运行init.ora文件以避免spfile的偶然变化，但是，您也可以选择将init.ora文件中的设置组合到spfile中。 0.6 必须指出的警告 每当有人要求我为Practical Oracle 8i一书签名时，我总是在签名的后面附上自己的座右铭：尽信书则不如无书(Never believe all you read)。(如果您要求我为本书签名，座右铭将是：印在书上的并不都是真理(Just because it’s printed, doesn’t mean it’s true))。因为总是会有一些特殊情况、不同的参数设置方式和bug是书中没有介绍的。(不用说我所不知道的数千种知识，就是我所知道的也不能保证全部正确。) 参见如下的简单示例(联机代码包中的脚本in-list.sql)—— 在一个本地托管的表空间中运行，采用手动片断空间管理，8KB的块大小： create table t1 as select trunc((rownum-1)/100) n1, rpad(x,100) padding from all_objects where rownum <= 1000 ; -- 此处使用dbms_stats收集统计信息 set autotrace traceonly explain select * from t1 where n1 in (1,2) ; 由于采用了函数trunc()，因此，列n1的值为0~9，每个值出现100次。因此，以上查询将返回200行数据。首先在Oracle 8.1.7.4下运行该测试，然后在Oracle 9.2.0.6下运行，并检查autotrace报告的基数。得到如下结果： Execution Plan (8.1.7.4 autotrace) ---------------------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=190 Bytes=19570) 1 0 TABLE ACCESS (FULL) OF T1 (Cost=3 Card=190 Bytes=19570) Execution Plan (9.2.0.6 autotrace) ---------------------------------------------------------------------- 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=200 Bytes=20400) 1 0 TABLE ACCESS (FULL) OF T1 (Cost=4 Card=200 Bytes=20400) 在 8.1.7.4下，优化器估计得出190行数据作为结果集的基数。在9.2.0.6下，优化器估计得出200行数据作为结果集的基数。这个结果是正确的—— 这是优化器代码中的因此误差被修正后的结果。 在Oracle 9i发布之前，我花了3天时间进行观察，发现8i中的计算看起来好像考虑了当混合索引访问路径和in-list之后的优点。Oracle 9i发布之后，我重新运行了以上测试，终于意识到这并不是Oracle 8i中巧妙的计算，而是一个bug。(参见第3章以获取更为详细的信息。) 因此，如果您的经历正好与我的相反，那么可能就是您的配置笔者未见过—— 我半生以来仅仅接触过几百个数据库，但世界上肯定会有数百万种不同的配置和数据分布形式的组合。绝大部分情况下，下载的代码都能产生与本书相同的结果—— 因此，至少您可以检查书中的测试示例，在自己的系统中得到的结果是否与预测的相一致。不管结果如何，会得到一些线索，可以推断出为什么您的观察与书中所描述的不同。 将前面的谓词 where n1 in (1,2); 修改为 where n1 in (11, 12); 并将字面值修改为较大的值，比较一下9.2.0.6下和10.1.0.4下的结果之异同，这一点留给读者作为练习。结果将令人非常吃惊—— 第3章中将进行进一步分析。 0.6.1 理论与实践 阅读本书时要记住，运行一个查询有两个独立的阶段。首先，优化器确定它“认为”将发生什么事情，然后运行时引擎执行应执行的任务。 理论上讲，优化器应该了解、描述并且将它的计算过程基于猜想运行时引擎会执行的动作。实际上，优化器和运行时引擎有时候对于应该执行什么样的任务其想法是不一致的。 0.6.2 解释计划 在本系列后续图书中，我将描述获取执行计划的方法，并列出为什么产生的计划会是错误的，或者至少是造成误导的各种原因。目前，本书为了简单起见，其中绝大部分示例都是利用autotrace或者dbms_xplan包来产生执行计划。 然而，当我实际应用中遇到一个问题，并且没有理由质疑这些理论工具时，我可能会选择运行所有的问题查询，以查看10053 trace文件、10046 trace文件中的统计信息和v$sql_plan的内容。对于包含了分区对象或者并行执行任务的情况，我可能会选用另外一组追踪事件来分析其中的详细信息，否则，这些详细信息在任何形式的执行计划中都看不到。 所有用于产生和分析执行计划的工具都存在一些缺陷。不能因为本书中仅仅使用了autotrace和dbms_xplan就简单地依赖它们。 0.7 结论 当读完本书之后(第一次)，希望您能够记住以下3个关键信息。 首先，通常情况下，对于商业问题来说，可能在优化器可用的执行路径中存在良好的解决方案。我们仅仅需要找出并使用这个路径。 其次，任何人都能够创建较好的和递增的测试，测试开始比较小且简单，但是，当条件允许也只有当条件允许时，测试的规模和复杂度都将增加。 最后，如果发现了一个性能问题，而且能够将其定位于数据内容，并且清楚访问需求和物理存储策略，那么我们就学会了本书中最重要的知识。 0.8 测试用例 下载包中对应于前言的文件如表0-1所示： 表0-1 前言的测试用例 脚 本 注 释 in_list.sql 演示in-list循环中的选择率计算的变化 setenv.sql 为SQL*Plus设置标准化环境