Oracle 优化器内幕，第 2 部分（续） Oracle Database Tips by Donald Burleson

 

成本控制：Oracle 优化器内幕，第 2 部分（续）

 

作者 Donald K. Burleson
自 2001 年起成为 OTN 成员

回到第 1 部分

查找未最优化的 SQL

尽管复杂查询可能具有极为复杂的执行计划，大多数 Oracle 专业人员必须调整具有以下问题的 SQL：

 

• 对表进行未最优化的索引访问 - 这种问题在优化器无法找到索引或者 SQL 中最有限制性的 where 子句与索引不匹配时发生。当优化器无法找到访问表的行的适当索引时，优化器会始终调用全表扫描，读取表中的每一行。因此，大型表的全表扫描可能显示有未最优化的 SQL 语句，可以通过添加一个与查询的 where 子句相匹配的索引来调整该语句。

   

   

• 未最优化的联接方法 - 优化器具有许多可用的联接方法，包括合并联接、嵌套循环联接、散列联接和星型联接。为选择正确的联接方法，优化器必须猜测多项表联接的中间结果集的大小。要进行这种猜测，优化器拥有的信息不完整。即使提供了柱状图，优化器也不能确切了解联接所返回的确切行数。最常见的补救方法是使用提示来更改联接 (use_nl、use_hash) 或重新分析目标表的统计量。

 

让我们来分析 v$sql_plan 视图如何能够帮助我们寻找 SQL 调整的机会。在搜索调整机会时，我们从查询 v$sql_plan 视图开始，以便找出这些大型表的全表扫描，如列表 2 所示。然后，我们提取相应的 SQL 并查看全表扫描是否适当还是由于缺失索引而造成的。

我们如何找到适于进行全表扫描的小型表？一种方法是搜索当前处于库高速缓存中的 SQL。Oracle 随后可以生成一个报表，列出在该时刻数据库中所有的全表扫描。列表 2 中的脚本从 v$sql_plan 中检查执行计划，并以全表扫描的频率生成报表。

报表（参见列表 3）具有以下的列：

 

• OWNER - 表的模式拥有者
• NAME - dba_tables 中的表名
• NUM_ROWS - dba_tables 最后计算统计信息的表中的行数
• C（只在 Oracle7 中具有） - 一个 Oracle7 特有的列，如果表被高速缓存则显示 Y，如果没有被高速缓存则显示 N
• K（只在 Oracle8 及以后版本中具有） - 如果表被分配到 KEEP 池中则显示?K?
• BLOCKS - 在 dba_segments 中定义的表中的块数
• NBR_FTS - 对表进行的全表扫描次数（用于当前处于库高速缓存中的 SQL）。

 

该报表提供关于两个调整方面的信息：

 

• KEEP池的表和索引 - SQL 的速度可以得益于将频繁进行全表扫描的小型表（以及相关索引）放置在 KEEP 池中。以上的报表显示大型表和小型表上进行的全表扫描。分析该报表，我们可以通过选择具有少于 50 个块并且没有 ?K? 标注的表，快速识别出 KEEP 池的可选对象。

   

  将表、分区或索引分配到 KEEP 池很容易，利用 alter 系统命令可以随意添加或删除对象：

   

  alter table CUSTOMER storage (buffer_pool KEEP);
  
  

   

   

• 可能缺失的索引 - 大型表的全表扫描有时可能指示缺失索引。Oracle 的基于函数的索引特别适用于这种目的，因为任何 where 子句都可以与基于函数的索引相匹配。

   

  例如，以下是一个使用 substr 和 to_char BIF 的基于函数的索引：

   

  create index
  fbi_book
  on book
  (
  substr(book_key,1,3)
  ||
  to_char(book_retail_price)
  
  );
  

   

  总之，v$sql_plan 中包含的信息是一种极好的执行系统级 SQL 调整的方法。

 

在 Oracle Database 10g 中有什么新特性？

随着 Oracle Database 10g 的出现，我们现在看到基于成本的 SQL 优化器有了显著的内在提高，并且自动的 SQL 优化机制更加方便。对 Oracle Database 10g 的 SQL 优化器的重大更改包括以下激动人心的主题：

 

• 不支持基于规则的优化器 - 尽管在 Oracle Database 10g 中存在基于规则的优化器 (RBO)，Oracle 强烈建议那些使用基于规则的优化的客户不要再迟疑了。那些仍在使用 RBO 的网站可以切换到 first_rows optimizer_mode 并将参数 optimizer_index_cost_adj 调整到一个小的数字 (< 25)，使基于成本的优化器模拟 RBO 的行为。那些不希望在其 Oracle Database 10g 移植过程中更改其执行计划的商店可以使用 Oracle 的优化器计划稳定性功能，保留其移植前的基于规则的执行计划。

用户启动的缓冲区高速缓存刷新 - 现在您可以在运行测试查询过程之间手动地刷新缓冲区高速缓存，这样便于您诊断和测试 SQL 运行时执行过程。对于 SQL 单元测试，这种清除数据缓冲区的功能确保了唯一的 SQL 响应时间测试，并去除了与 RAM 数据高速缓存相关的性能易变性。     SQLAccess Advisor - SQLAccess Advisor 是 dbms_advisor 程序包内部的一个专家系统，可识别（并提供解决方案的建议）SQL 执行的性能问题。它从库高速缓存中分析 SQL 并建议哪些索引或物化视图需要创建、删除或保留。   成本的基本知识 尽管我们已经非常详细地了解了优化器内幕，还要不断学习更多内容，因为每个新版本的优化器都变得功能更加强大（和复杂）。本文的要点包括调整优化器行为的一般性原则：   在极个别情况下，索引列的值将会更改最优的执行计划，柱状图为优化器提供详细的列信息。因此，只应该在已证明是合适的时候使用柱状图。     编写出可获得正确数据的 SQL 是不够的。应该让开发人员负责调整他们的 SQL 并以最优的 SQL 格式化进行培训，他们应该了解如何使用 explain plan 和 TKPROF。     SQL 优化最常见的问题是缺失索引（或无选择性的索引）以及未最优化的表联接方法。     Oracle 提示用于更改查询的执行计划，但只应该当作最后的手段。     v$sql_plan 视图显示库高速缓存中所有 SQL 的执行计划，您可以查询该视图来获得对 SQL 执行过程内部的有用了解。   特别感谢 Oracle SQL 精英 Andrew Holdsworth 的帮助。 Donald K. Burleson 是世界上最优秀的 Oracle 数据库专家之一。他已经撰写了 19 本书籍，在全国性杂志上发表了 100 多篇文章，并担任 Oracle Internals（一本领先的 Oracle 数据库期刊）的主编。Burleson 最新的一本书是创建自调整的数据库，由 Rampant TechPress 出版。Don 的网站是 http://www.dba-oracle.com 和 http://www.remote-dba.net/。