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

查找未最优化的 SQL

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

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

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

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

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

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

• 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 执行过程内部的有用了解。

 

列表2：

LISTING 2: plan9i.sql:

--*****************************************************

-- Object Access script report
--
-- (c) 2003 by Donald K. Burleson
-- This is freeware, Never to be sold

--*****************************************************


column nbr_FTS  format 999,999
column num_rows format 999,999,999

column blocks   format 999,999
column owner    format a14;
column name     format a24;
column ch       format a1;

set heading on;

set feedback on;
set echo off;
set pages 999;

ttitle 'full table scans and counts|  |The "K" indicates that the table is in the KEEP Pool (Oracle8).'
select 
   p.owner, 

   p.name, 
   t.num_rows,
   ltrim(t.cache) ch,
   decode(t.buffer_pool,'KEEP','Y','DEFAULT','N') K,
   s.blocks blocks,
   sum(a.executions) nbr_FTS
from 
   dba_tables   t,

   dba_segments s,
   v$sqlarea    a,
   (select distinct 
     address,
     object_owner owner, 
     object_name name
   from 
      v$sql_plan
   where 

      operation = 'TABLE ACCESS'
      and
      options = 'FULL') p
where 
   a.address = p.address
   and
   t.owner = s.owner
   and
   t.table_name = s.segment_name
   and

   t.table_name = p.name
   and
   t.owner = p.owner
   and
   t.owner not in ('SYS','SYSTEM')
having
   sum(a.executions) > 9
group by 
   p.owner, p.name, t.num_rows, t.cache, t.buffer_pool, s.blocks
order by 
   sum(a.executions) desc;

列表3：

Listing 3: Full table scans and counts: 
OWNER      NAME                     NUM_ROWS C K   BLOCKS  NBR_FTS      

---------- -------------------- ------------ - - -------- --------      
APPLSYS    FND_CONC_RELEASE_DISJS         39 N K        2   98,864      
APPLSYS    FND_CONC_RELEASE_PERIODS       39 N K        2   98,864      
APPLSYS    FND_CONC_RELEASE_STATES         1 N K        2   98,864      

APPLSYS    FND_CONC_PP_ACTIONS         7,021 N      1,262   52,036      
APPLSYS    FND_CONC_REL_CONJ_MEMBER        0 N K       22   50,174      
APPLSYS    FND_CONC_REL_DISJ_MEMBER       39 N K        2   50,174      
APPLSYS    FND_FILE_TEMP                   0 N         22   48,611      
APPLSYS    FND_RUN_REQUESTS               99 N         32   48,606      

INV        MTL_PARAMETERS                  6 N K        6   21,478      
APPLSYS    FND_PRODUCT_GROUPS              1 N          2   12,555      
APPLSYS    FND_CONCURRENT_QUEUES_TL       13 N K       10   12,257      
AP         AP_SYSTEM_PARAMETERS_ALL        1 N K        6    4,521