2.2.5  报告的其他部分
statspack报告的其他部分包括了 Instance Activity Stats，Tablespace IO Stats，Buffer Pool Statistics，Buffer wait Statistics，Rollback Segment Stats，Latch Activity，Dictionary Cache Stats，Library Cache Activity，SGA breakdown difference 以及 init.ora 参数，等等。目前本文不对这些内容进行详细讨论，请参加其他详细文档。

2.3 trace session  （……）

2.4 基于成本的优化器技术内幕
Oracle基于成本的优化器（Oracle''''s cost-based SQL optimizer ，简称CBO)，是Oracle里面非常复杂的一个部分, 它决定了Oracle里面每个SQL的执行路径。CBO是一项评价SQL语句和产生最好执行计划的具有挑战性的工作，所以也使它成Oracle最复杂的软件组成部分。
众所周知，SQL的执行计划，几乎是Oracle性能调整最重要的方面了。所以想要学会如何调整Oracle数据库的性能，就要学会如何对SQL进行调整，就需要深入透彻理解CBO。
CBO的执行路径，取决于一些外部因素，内部的Oracle统计数据，以及数据是如何分布的。
我们将要讨论下面的话题：
CBO的参数：我们从基本的优化器参数开始学习，然后学习每个优化器参数是如何影响Oracle的优化器的执行的。

CBO的统计：这里我们将讨论，使用Analyze或者DBMS_STATS来收集正确的统计数据，对Oracle 优化器而言，是多么的重要。我们还将学习如何把优化器的统计数据，从一个系统拷贝到另外一个系统，这样可以确保开发环境和产品数据库环境下，SQL的执行路径不会变化。

下面我们开始讨论CBO优化模式以及影响CBO的Oracle参数

2.4.1  CBO的参数
CBO受一些重要参数的影响，修改这些参数后可以看到CBO性能上戏剧性的变化。首先从设置CBO的optimizer_mode参数开始，然后讨论其他重要参数的设置。

在 Oracle 9i 中，optimizer_mode 参数有四种取值，决定了四种优化模式： rule, choose, all_rows, 和 first_rows，其中 rule 和 choose 两种模式表示目前已经过时的基于规则的优化器模式（rule-based optimizer，简称RBO），所以我们在此着重讨论后两种CBO模式。

优化模式的设置可以在系统级进行，也可以对某个会话（session）进行设置，或者对某个SQL语句进行设置。对应的语句如下：
alter system set optimizer_mode=first_rows_10;
alter session set optimizer_goal = all_rows;
select /*+ first_rows(100) */ from student;

我们首先需要知道对一个SQL语句来说，什么是最好的执行计划（the best execution plan）？是使SQL语句返回结果的速度最快，还是使SQL语句占用系统资源最少？显然，这个答案取决于数据库的处理方式。

举一个简单的例子，比如有下列SQL语句：
select customer_name
from
   customer
where
   region = ''''south''''
order by
   customer_name;

如果最好的执行计划是返回结果的速度最快，那么就需要使用 region 列和 customer_name 列上的索引，从 customer 表中按照正确的顺序快速读取所有的列，而不用管是否从物理上读取了很多不连续的数据块导致的大量IO操作。（见下图）
 

假设这个执行计划从开始到返回结果耗时 0.0001 秒，同时产生了 10000 个 db_block_gets ，但是如果你的目标是计算资源的最小化呢？如果这个SQL语句是在一个批处理程序中执行，也许对返回结果的速度要求就不那么重要了，而另一个执行计划则可能耗费更少的系统资源。
在下图所示的例子中，并行的全表扫描由于不需要按照排序重新读取数据块，所以耗系统资源较少，并且IO操作也不多。当然，由于SQL语句执行过程中没有排序，得到预期结果的时间就长了，而资源耗费少了。假设这个执行计划从开始到返回结果耗时 10 秒，同时产生了 5000 个 db_block_gets
 

Oracle提供了几个 optimizer_mode 的设置参数，使你能够得到想要的最好的执行计划。

optimizer_mode = first_rows
设置为这种CBO模式以后，SQL语句返回结果的速度会尽可能的快，而不管系统全部的查询是否会耗时较长或者耗系统资源过多。由于利用索引会使查询速度加快，所以 first_rows 优化模式会在全表扫描上进行索引扫描。这种优化模式一般适合于一些OLTP系统，满足用户能够在较短时间内看到较小查询结果集的要求。

optimizer_mode = all_rows
设置为这种CBO模式以后，将保证消耗的所有计算资源最小，尽管有时查询结束以后没有结果返回。all_rows 的优化模式更倾向于全表扫描，而不是全索引扫描和利用索引排序，因此这种优化模式适合于数据查看实时性不是那么强的数据仓库、决策支持系统和面向批处理的数据库（batch-oriented databases）等。

optimizer_mode = first_rows_n
Oracle 9i 对一些预期返回结果集的数据量小的SQL语句优化模式进行了加强，增加了四个参数值：first_rows_1、first_rows_10、 first_rows_100、first_rows_1000。CBO通过 first_rows_n 中的 n 值，决定了返回结果集数量的基数，我们可能仅仅需要查询结果集中的一部分，CBO就根据这样的 n 值来决定是否使用索引扫描。

optimizer_mode = rule
基于规则的优化器模式，RBO，是早期Oracle版本使用过的一种优化模式。由于RBO不支持自1994年Oracle版本的新特性，如 bitmap indexes，table partitions，function-based indexes等，所以在以后Oracle版本中已经不再更新RBO，并且也不推荐用户使用RBO这种优化模式了。

从上面的讨论可以看出，optimizer_mode 参数的设置对CBO是非常重要的，决定了CBO的基本模式，同时还有一些其他的参数也对CBO有着极大的影响。由于CBO的重要性，Oracle提供了一些系统级的参数来调整CBO的全局性能，这些调整参数包括索引扫描与全部扫描的选择、表连接方式的选择，等等。下面简单讨论一下。

optimizer_index_cost_adj
这个参数用于调整使用索引的访问路径的成本算法，参数值越小，索引访问的成本就越低。

optimizer_index_caching
这个参数告诉Oracle在内存缓冲区中索引的数量。该参数的设置会影响CBO如何决定使用表连接（嵌套循环）的索引还是使用全表扫描。

db_file_multiblock_read_count
这个参数的值被设置较大的时候，CBO就会认为离散的、多数据块的读取会比顺序读取的代价更低，使得CBO更倾向于全表扫描。

parallel_automatic_tuning
这个参数值被设置为 on 的时候，表示使用并行的全表扫描，由于并行的全表扫描比较快，所以CBO认为索引的访问是高成本的，同时就更倾向于全表扫描。

hash_area_size
如果不使用 pga_aggregate_target 参数的话，该参数有效。该参数的设置大小决定CBO是否更加倾向于 hash joins ，而不是嵌套循环和表连接的索引合并。

sort_area_size
如果不使用 pga_aggregate_target 参数的话，该参数有效。该参数的设置大小影响CBO决定是否进行索引访问和结果集的排序，参数值越大，在内存中排序的可能性就越大，CBO也就更加倾向于排序。

由于对这些参数值的修改会影响到系统中成千上万的SQL语句的执行计划，所以Oracle并不推荐修改这些参数的缺省值。

在对CBO的参数有了大致的了解以后，下面讨论如何根据提供给CBO的数据帮助CBO制定出一个好的执行计划。

2.4.2  CBO的统计
对于CBO来说，最重要的是定义和管理好你的统计数据，为了使CBO能够为你的SQL语句产生一个最好的执行计划，必须要有与SQL语句相关的表和索引统计数据。只有当CBO知道了相关的信息，如表的大小、分布、基数以及列值的可选性等，才能对SQL语句作出正确的判断，从而得到最好的执行计划。

下面讨论一下如何获得高质量的CBO统计数据，如何为你的数据库系统创建一个适当的CBO环境。

CBO产生最好执行计划的能力来自于统计数据的有效性，获得统计数据的比较过时的方法是 analyze table 和 dbms_utility ，这两种方法对SQL语句的性能有一些危害，因为我们知道，CBO是使用对象统计数据（object statistics）来为所有的SQL语句选择最好的执行计划。
dbms_stats 应用功能包是产生统计数据较好的方法，特别对大型分区表而言。下面看一个使用 dbms_stats 的例子。
exec dbms_stats.gather_schema_stats(
  ownname          => ''''SCOTT'''',
  options            => ''''GATHER AUTO'''',
  estimate_percent    => dbms_stats.auto_sample_size,
  method_opt        => ''''for all columns size repeat'''',
  degree            => 34
   )

上面例子中的options参数的几个可选值需要说明一下。
GATHER  重新分析整个schema，产生统计数据；

  GATHER EMPTY 仅分析那些还没有统计数据的表；

  GATHER STALE 仅重新分析那些发生了10％变化的表（变化原因可能是 inserts, updates , deletes ）

  GATHER AUTO 仅重新分析那些还没有统计数据和发生了10％变化的表，该选项相当于 GATHER EMPTY 和 GATHER STALE 同时使用。

使用 GATHER AUTO 和 GATHER STALE 两个选项需要进行监控，如果你执行了 ALTER TABLE XXX MONITORING 命令，Oracle利用 dba_tab_modifications 视图跟踪表的变化，记录了最近一次统计数据分析以来的 insert , update , delete 的准确记录数。
SQL> desc dba_tab_modifications;
 Name                     Type
 -----------------              ---------------
 TABLE_OWNER          VARCHAR2(30)
 TABLE_NAME           VARCHAR2(30)
 PARTITION_NAME       VARCHAR2(30)
 SUBPARTITION_NAME   VARCHAR2(30)
 INSERTS                 NUMBER
 UPDATES                NUMBER
 DELETES                NUMBER
 TIMESTAMP             DATE
 TRUNCATED             VARCHAR2(3)

比较有趣的一个选项是 GATHER STALE ，比如在一个数据更新频繁的OLTP系统中，几乎所有的统计数据都会很快的过时，而我们必须记住 GATHER STALE 选项是在表中10％的记录发生变化时才对该表重新分析产生统计数据，因此除了只读表以外的所有表几乎使用 GATHER STALE 选项重新分析产生统计数据，所以 GATHER STALE 选项主要还是用于一些主要是只读表组成的系统中。

在上面使用 dbms_stats 的例子中，我们看到了一个参数 estimate_percent ，它的值是 dbms_stats.auto_sample_size, 这个参数值是 Oracle 9i 才开始使用的，这个参数值的出现极大方便了统计数据的分析产生。
我们知道，统计数据的质量越高，CBO产生最好执行计划的能力就越强，但是由于数据库统计采样大小的问题，对一个大型数据库系统做一个完整的统计数据分析产生将会耗时数天，最好的办法就是在高质量的统计数据和数据库统计采样大小之间得到一个平衡点。
在早一些的Oracle版本中，为了得到统计数据，DBA不得不猜测一个最好的数据采样大小百分比。但是从 Oracle 9i 开始，可以通过 dbms_stats 包来自己指定 estimate_percent 参数的值了，那就是 dbms_stats.auto_sample_size
通过这种方式设置了自动采样大小以后，我们可以通过下列数据字典视图的 sample_size 字段来验证这些自动产生的统计采样大小。
DBA_ALL_TABLES
DBA_INDEXES
DBA_IND_PARTITIONS
DBA_IND_SUBPARTITIONS
DBA_OBJECT_TABLES
DBA_PART_COL_STATISTICS
DBA_SUBPART_COL_STATISTICS
DBA_TABLES
DBA_TAB_COLS
DBA_TAB_COLUMNS
DBA_TAB_COL_STATISTICS
DBA_TAB_PARTITIONS
DBA_TAB_SUBPARTITIONS

使用自动统计采样以后，Oracle会根据表的大小和列值的分布在5％到20％之间取值。记住：你的统计数据质量越高，CBO作出的决定就越对你有利。

现在我们对CBO统计数据应该有一些了解了，下面来看看在一个成功的Oracle系统是如何管理CBO统计数据。

2.4.3  CBO的正确环境
成功使用CBO的关键是稳定性，下面是一些成功使用CBO的基本事项。

●只在必需的时候才进行统计数据的重新分析
Oracle DBA们最容易犯的一个普遍错误就是经常性的对系统的统计数据进行重新分析。记住：做这件事的唯一目的是改变SQL语句的执行计划，如果这个执行计划没有被破坏，就不要去修复它。如果你对SQL语句的性能还满意的话，重新分析产生统计数据以后可能会产生较大的性能问题，并给开发团队带来影响。实际运用中，也是极少数的Oracle系统才会周期性的对统计数据进行重新分析。
一般来讲，一个数据库应用系统的基本架构是不会轻易改变，大数据量的表仍然是很大，索引列的分布、基数值等等也很少变化。只有下列几种情况的数据库才可能经常对整个系统的统计数据重新分析：
1、用于数据分析的数据库
   有一些由于科学试验数据分析的数据库系统，经常会更换整个一套的试验数据，那么这种情况下当数据库重新load了一套数据以后，可以立即重新对统计数据进行分析。
2、高度变化的数据库
   这是极少数的例子，表的大小或者索引列的数据在剧烈的变化，比如一张表有100条记录，一周以后就变成10000条记录。这种情况下也可以考虑周期性的进行统计数据分析。

●强迫开发人员调整自己的SQL
很多开发人员错误的认为，他们的任务就是编写SQL语句然后从数据库中获得正确的数据。但是实际上编写出SQL语句只是开发人员一半的工作，在一个成功的Oracle应用系统中，会要求开发人员的SQL语句采用最优化的方式访问数据库，并且保证SQL语句的执行计划在新的SQL之间的可移植性。
令人惊讶的是，在许多Oracle应用系统中都不怎么考虑具体SQL语句的执行计划，认为CBO是很智能的，无论如何都可以为我们提供最好的SQL语句执行计划。
同一个查询在SQL语句中可能有不同方式的写法，而每一种写法都可能有不同的执行计划。观察下面的例子，每一个查询的结果都是一样的，但是执行计划却相去甚远。

-- 使用了不正确的子查询
select
  book_title
from
  book
where
  book_key not in (select book_key from sales);
 
Execution Plan
----------------------------------------------------------
0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=64)
1    0   FILTER
2    1     TABLE ACCESS (FULL) OF ''''BOOK'''' (Cost=1 Card=1 Bytes=64)
3    1     TABLE ACCESS (FULL) OF ''''SALES'''' (Cost=1 Card=5 Bytes=25)

-- 使用了两张表的外连接
select
  book_title
from
  book  b,
  sales  s
where
  b.book_key = s.book_key(+)
and
  quantity is null;

Execution Plan
----------------------------------------------------------
0   SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=100 Bytes=8200)

1  0 FILTER
2  1   FILTER

上一页  [1] [2] [3] [4] [5] [6] [7]  下一页