优化参数

3. db_file_multiblock_read_count
在多块（multiblock）读的情况下（例如，在全表扫描或快速全索引扫描时），数据库引擎使用的最大I/O大小取决于初始化参数db_file_multiblock_read_count和db_block_size的乘积。

因此在多块读时最大的块数是用最大的I/O吞吐量除以一个块的大小得到的。

换言之，对默认的块尺寸来说，初始化参数db_file_multiblock_read_count的值指定了一次最多可读取的数据块数目。

这“仅仅”是一个理想的最大值，因为通常至少有下列三种情况可能导致一次多块读的数目少于此初始化参数指定的值。

⊙读段头时单块读。

⊙物理读不能跨越多个区（extent）。

⊙部分数据块已经在高速缓存（buffer cache）中，除非是直接读，否则不会从I/O子系统重新读取。

为了阐述清楚，下图展示了数据库中一个段（segment）的结构。

跟其他段一样，它由若干个区（extent）组成（在本例中，是两个区），每个区由若干个块组成（在本例中，是16个块）。

第一个区的第一个块为段头块（segment header）。

其中一些块（第4、9、10、19和21块）被缓存在高速缓存中。

一个数据库进程对这个段执行一次串行的全扫描（并行全扫描有其特殊的行为，将在后面介绍），并不能通过一次单独的物理读就完成。

即使初始化参数db_file_multiblock_read_count的值被设置为32或者更大也不能。

如果初始化参数db_file_multiblock_read_count被设置为8，将会造成如下的物理读过程。

⊙一个单块（single-block）读获取段头（块1）。

⊙一个多块（multiblock）读得到两个块（块2和3）。

不能一次读更多的块因为块4被缓存。

⊙一个多块读读取4个块（从块5到块8）。

不能读更多的块因为块9被缓存。

⊙一个多块读读取6个块（从块11到块16）。

不能读更多的块因为块16是一个区的最后一个块，不能跨区读。

⊙一个多块读读取2个块（块17和块18）。

不能读更多的块因为块19被缓存。

⊙一个单块读块取块20。

不能读更多的块因为块21被缓存。

⊙一个多块读读取8个块（从块22到块29）。

不能读更多的块因为初始化参数db_file_multiblock_read_count被设置为8。

⊙一个多块读读取3个块（从块30到块32）。

总之，这个进程共进行了两个单块读和六个多块读。

平均每个多块读的块数约为4。

平均每次读块的数目小于参数设置值8的事实，解释了Oracle在计算系统统计信息时引入变量mbrc的原因。

在此，了解查询优化器如何计算多块读的开销也是很重要的（比如，在全表扫描或快速全索引扫描时）。

就像Wolfgang Breitling 在他的文章“A Look Under the Hood of CBO: The 10053 Event”中指出的那样，当系统统计信息不可用时，这个开销可用下列公式来近似地计算。

无系统统计时多块读操作的I/O 开销
当工作量系统统计信息可用时，I/O的开销不再只取决于初始化参数db_file_multiblock_read_count的值。

而是用下列公式计算。

注意到变量mreadtim被sreadtim除是因为查询优化器通常是根据单块读来换算开销的。

这点已在以前讨论过。

在公式中，如果是用非工作量统计信息，mbrc的值就用初始化参数db_file_multiblock_read_count的值来代替。

这意味着初始化参数db_file_multiblock_read_count仅在工作量统计信息不可用时，对多块读操作的开销计算有直接的影响。

这也暗示着此初始化参数太大可能导致过度的全扫描或者至少低估了多块读操作的开销。

进一步讲，这是工作量统计信息好于非工作量统计信息或者根本没有系统统计信息的又一个例子。

既然已经了解开销计算公式，关键在于如何找到公式中变量的合适值。

最重要的是认识到多块读是一种和性能相关的特性。

因此需要设置初始化参数db_file_multiblock_read_count以求获取最佳性能。

为了达到这个目的，必须认识到并不是所有情况下值越大性能就越好。

此外，将值设置为超过操作系统所能支持的物理I/O上限也是毫无意义的，这个上限是max I/O chunk size。

对一个简单的全表扫描分别设置不同的参数值测试性能，可以获得这个参数对性能产生影响的有效信息，有助于发现它的最佳值。

下面的PL/SQL代码块，参看脚本assess_dbfmbrc.sql，可用于此目的。

如你所见，做到这一点并不难，因为初始化参数db_file_multiblock_read_count是动态的并且可以在实例级和会话级修改。

无论如何，千万小心不要在数据库、操作系统和I/O子系统层级缓存测试表的数据，否则将导致测试结果无效。

避免它最容易的方法就是使用一张比系统中可用的最大缓存还要大的表来做测试。

有一点要注意的是不要使用并行处理，因为通常数据库引擎通过不同的系统调用来执行并行全表扫描和串行全表扫描。

通过上面PL/SQL块的测试，显示出下列几种不同系统特性。

◎测试环境1：
随着I/O尺寸的增加，性能不断提升，一直到每次8左右。

更大的值收效不大，甚至出现负增长。

所以该多块读不易设置大于8
◎测试环境2：
一直到每次16块性能增长都很缓慢（每次增幅不超过10%）。

当从16调到18时，性能骤然上升20%左右。

超过18以后又表现平平。

对这种系统，参数设置要避免小于18。

◎测试环境3：在参数达到8之前性能提升都很显著，在8到16之间，增长基本平缓。

当从16到17时，出现16%的负增长。

因此对这种系统来说，此参数值需要避免达到16以上。

◎测试环境4：系统的性能与此I/O的尺寸无关。

从Oracle 10g R2起，也可以指示数据库引擎自动配置初始化参数db_file_multiblock_read_count的值。

使用这个特性的方法很简单，就是不手动设置此参数的值。

在10GR2，理论上，最大db_file_multiblock_read_count和系统IO能力应该有如下关系: Max(db_file_multiblock_read_count) = max
I/O chunk size/db_block_size。

其中max I/O chunk size跟操作系统有关，但是Oracle文档中也指出大多数操作系统上该值为1M。

像在公式中展示的那样，有两个条件选其最小的那个作为db_file_multiblock_read_count。

数据库引擎将其值设置为能保证每次物理读获取1MB的数据。

同时，也将进行一种健全性检查，当在极端高会话数的系统中，高速缓存的大小相对于数据库支持的会话总数明显偏小时，调整减小此初始化参数的值，也就是每个会话的多块读总和不能大于高速缓存的大小。

如先前所讲，每次以最大I/O值进行物理读也不是总能带来好的性能，因此我们并不建议使用这个特性，更好的方式是通过一次次的测试找到最佳值，注意：同一个环境每次执行测试也会有区别。

注意如果在非工作量统计信息可用的情况下启用了自动配置，mbrc值不会被自动配置的db_file_multiblock_read_count值替代，而是被设置为8。

4. optimizer_dynamic_sampling
查询优化器过去一般都是把数据字典中的对象统计信息作为它评估的唯一依据。

而通过使用动态采样，这种情况已经有了改观。

事实上，有些统计信息可以在语句解析阶段动态地收集。

这意味着为了收集额外的信息，还要对涉及的对象执行一些查询。

遗憾的是，动态采样得到的统计信息既不存储在数据字典中也不存储在其他地方，真正重用它们的唯一方式是重用共享游标本身。

初始化参数optimizer_dynamic_sampling的值（或层级）指定动态采样的方式和时间。

下表汇总了得到公认的值和它们的含义。

动态采样等级以及含义
注意这个参数的默认值由另一个初始化参数optimizer_features_ enable决定。

⊙如果optimizer_features_enable设置为10.0.0或更高，默认值为层级2。

⊙如果optimizer_features_enable设置为9.2.0，默认值为层级1。

⊙如果optimizer_features_enable设置为9.0.1或更低，动态采样特性则被禁用。

这个参数是动态的，可以使用SQL语句ALTER SYSTEM在实例级或者SQL语句ALTER SESSION在会话级修改它的值。

也可以通过提示dynamic_sampling在SQL语句级应用动态采样。

这个提示有两种使用方式。

⊙为所有表设置采样层级：dynamic_sampling(level)。

⊙为一个指定表设置采样层级：dynamic_sampling(table_name level)。

* 这表示当动态采样特性通过初始化参数或通过未加表名（或别名）的提示而激活后，动态采样的数据块数。

当使用一个带有表名（或别名）的提示的时候，数据块的数目，除了层级10，将通过如下的公式计算：32*2^(层级 1)
这里用一些例子（摘录自运行在10.2.0.3版本的脚本dynamic_sampling_levels.sql）来解释在什么样的情况下层级1到层级4的动态采样会发生。

测试用的表由下面的SQL语句创建。

一开始，它们没有任何对象统计信息。

注意表t_noidx和表t_idx之间
的唯一区别是后者有主键。

第一组用来测试的查询如下，它们之间的细微区别在于第一个查询使用了表t_noidx，而第二个查询则使用了表t_idx。

如果参数的层级设置为1，只有第一个查询会进行动态采样，因为第二个查询所基于的表是有索引的。

下面给出的是在我的测试数据库上为表t_noidx收集统计信息所用的递归查询。

为了易于阅读，去掉了语句中的一些提示，且绑定变量也用实际值替换。

需要注意的是在进行这个查询之前需要激活SQL跟踪功能。

然后，我所需要做的便是检查产生的跟踪文件，找出执行了哪些递归SQL 语句。

这里有几个十分重要的地方要注意。

⊙查询优化器计算
以上采样语句的4个列分别是
◎总记录行数
◎符合WHERE条件（id<19）的记录数
◎列id的唯一值数目
◎NULL值的数目。

⊙查询中用到的变量必须是已知的。

如果使用了绑定变量，查询优化器必须要能得到绑定的值，从而实施动态采样。

⊙通过SAMPLE子句进行采样，在我的数据库中表t_noidx有155个块，级别1的采样块大小是32块，所以采样百分比为20%（32/155）。

如果层级设置为2，那么上面的两个用于测试的查询语句都将进行采样操作。

在这种情况下，只要没有对象统计信息都会进行采样。

用来为两张表收集统计信息的递归查询和刚才所示的相同。

而采样的百分比增加，因为在这个层级上，将抽取64块而非32块数据。

此外，对表t_idx来说，会多出下面的这个递归查询,将有同样的效果。

它的目的是在前一步的查询中用扫描索引来替代扫
描表。

这么做是因为对一张表进行快速采样有可能丢失WHERE子句中的条件所指定范围内的记录。

因为全表扫描采样是顺序的, 但是索引字段ID的值在全表扫描时不是顺序的比如ID=19, LEVEL设置为2,那么会采样64块，如果ID=19有10行在前64块，有40行在64块后，采样到10行,那么采样出来的数据有80%的误差。

与之相比，对这个索引进行快速扫描则肯定能够定位这些记录，只要记录存在即可。

下一个动态采样的层级是层级3。

从这一层级开始，即使在数据字典中有可用的对象统计信息，动态采样也依然会进行。

在进行进一步的测试之前，先用下面的PL/SQL块收集对象统计信息。

如果采样的层级设置为3或者更高，查询优化器通过测量样本中记录的选择性来估算语句中条件的选择性，而不是使用数据字典中的统计信息或者手工设置的值。

下面的两条查询语句证明了这一点：
对于第一条语句来说，查询优化器能够根据字段的统计信息和直方图来估计谓词条件id=19的选择性。

因此，对它来说动态采样并不是必要的。

但是对第二条语句（排除存在表达式round(id)的扩展统计的情况），查询优化器不能够推测谓词条件round(id)=19的选择性。

事实上，字段的统计和直方图仅提供字段id本身的信息，而不包含round函数作用后的信息。

在这里用来进行动态采样的查询语句如下面所示。

如你所见，它和上面出现过的一条查询有着相同的结构。

字段C2有所不同，这是由于SQL 语句中引起动态采样的WHERE子句不同所致。

既然表达式应用于一个索引字段（id），即使对于表t_idx，在这个特殊的例子中并没有对索引采样。

如果参数的层级设置为4或者更高，当同一张表的两个或者更多的字段在WHERE子句中被引用时也进行动态采样。

当字段间有关系的时候这将非常有助于提高估算的性能。

下面这个查询提供了这样的一个例子。

如果回顾一下创建测试用表的脚本，不难发现字段id和n1包含相同的数据。

同样在此例中，查询优化器用来实施动态采样的查询语句也和上面的所示的某些查询有着相同的结构。

而且，这些查询语句间的主要不同也是因为SQL语句中导致动态采样的WHERE子句不同造成的。

概括起来，可以发现层级1和2通常不是很实用。

事实上，表和索引应该有最新的对象统计信息。

有一个例外，就是使用临时表的时候，一般情况下它们没有可用的对象统计信息。

无论怎样，一定要意识到有些会话可能共享完全相同的游标，即使它们所用的临时表包含完全不同的数据集。

层级3或更高的层次有助于对“复杂”谓词条件的选择性评估。

因此，如果查询优化器因为“复杂”谓词而无法做出正确的估算，设置初始化参数optimizer_dynamic_sampling为4。

否者，也可以设置为默认值。

到了Oracle 11g，有可能对表达式和一组字段收集统计信息。

从那时起，也许在很多情况下就可以避免动态采样。

5. optimizer_index_cost_adj
初始化参数optimizer_index_cost_adj用于通过索引扫描改变访问表时的开销。

参数可用值的范围为1到10000。

默认值为100。

超过100后越大则会使索引扫描的开销越高，从而导致查询优化器更加倾向于使用全表扫描。

相反，值越小于100，索引扫描的开销就越低。

为了理解在开销公式中此初始化参数的作用，明白查询优化器如何计算通过索引扫描访问表时的开销，是一件非常有帮助的事情。

索引范围扫描的做法就是通过索引访问一组键。

我们来看下图理解索引访问的原理。

索引段表段
(1) 访问索引的根块。

(2) 通过分支块定位包含第一组键的叶子块。

(3) 对每一个满足查询条件的键，做如下操作。

a. 抽取指向数据块的rowid信息。

b. 根据rowid访问数据块。

通过索引范围扫描的方式访问表时需要进行的物理读数量，等于定位包含第一个键的叶子块所需访问的分支块数（就是统计值blevel），加上访问的叶子块数（用统计值leaf_blocks乘上操作的选择性得到），再加上根据rowid访问的数据块的数目（用clustering_factor乘上操作的选择性得到）。

，初始化参数optimizer_index_cost_adj的纠正作用也被考虑在内。

注意在公式中，计算索引访问和表访问的开销时使用了相同的选择性。

实际情况中，查询优化器可能会为这两个不同的开销评估使用两个不同的选择性。

当索引只用于部分过滤操作时，这样做是十分必要的。

举个例子，当一个索引由三个字段组成，而对其中第二个字段没有约束时就会如此。

总结起来，可知初始化参数optimizer_index_cost_adj对通过索引访问的I/O开销有着直接的影响。

当其设置低于默认值时，开销的总和也随之成比例下降。

在某些情况下因为查询优化器四舍五入其估算结果，有可能造成一些问题。

这意味着，即使几个索引的对象统计信息不同，只要查询优化器将此参数设置为较低的值也可能使它们拥有相同的访问开销。

如果几个开销在数值上相同，查询优化器的决定将基于索引的名称！它仅仅使用在字母序上排在前面的那个索引。

下面这个例子证明了这个问题。

注意当初始化参数optimizer_index_cost_adj和索引名改变时索引范围扫描（INDEX RANGE SCAN）操作使用索引的方式。

下面执行实验脚本optimizer_index_cost_adj.sql产生的输出：
为了避免这种不稳定性，我通常建议不要把初始化参数optimizer_index_cost_adj设置为较低的值。

还有一点很重要，系统统计提供了类似此参数提供的调节功能。

这意味着如果系统统计就绪，将此参数保留默认值也是很不错的。

同时需要注意系统统计没有此参数在上文中讲到的那个不足之处。

因为它是依靠增加而不是减少开销来实现的。

初始化参数optimizer_index_cost_adj是动态的，可在实例级和会话级修改。

6. optimizer_index_caching
初始化参数optimizer_index_caching用于在执行in-list遍历和嵌套循环连接时，指出预计已存在于高速缓存中的索引块的数量（以百分比的方式）。

有一点要明确，查询优化器用这个初始化参数来调整它的评估结果。

它不是用来指定数据库引擎实际被缓存的每个索引的大小。

参数的取值范围是0到100，默认值为0。

取值越大就越可以减小in-list遍历和嵌套循环连接的索引扫描。

因此，此参数可以用来提高这两类操作的利用率。

那么索引访问的IO成本计算公司在此前公式的基础上进行了修正，展示调整后的索引范围全扫描公式。

基于索引范围扫描对表进行访问的开销计算公式
这个初始化参数和前面讲到的optimizer_index_cost_adj有一些类似的缺点。

然而因为两个原因使得它的影响范围更小。

第一，它只用于嵌套循环和in-list遍历。

第二，对聚簇因子（clustering factor）没有影响。

既然聚簇因子往往是开销公式中权重最大的因素，那么由此初始化参数导致错误决定的可能性就很小。

概括地讲，这个初始化参数对查询优化器的影响不如optimizer_index_cost_adj那么大。

所以通常让其保持默认值就可以了。

数据库环境系统设置经验
⊙OLTP:OPTIMIZER_INDEX_CACHING = 90
OPTIMIZER_INDEX_COST_ADJ = 25
⊙OLAP:OPTIMIZER_INDEX_CACHING = 0
OPTIMIZER_INDEX_COST_ADJ = 100
该参数的计算细节将在后面章节介绍
7. optimizer_secure_view_merging
Oracle 10g R2引入了初始化参数optimizer_secure_view_merging，用于控制视图合并。

它可以被设置成FALSE或TRUE。

默认值是TRUE。

⊙FALSE允许查询优化器每次都执行视图合并。

⊙TRUE只在不会引起安全问题的情况下允许视图合并。

为理解这个初始化参数的影响，并更广泛地描述视图合并，来看下面这个实验脚本optimizer_secure_view_merging.sql。

假使有一张小表，有一个主键列和另外两个普通列：
出于安全考虑，不想让某用户执行访问表，假设你想通过下面视图提供对这张表的访问。

注意此处用函数进行过滤以展示部分符合条件的表内容。

而这个函数如何实现过滤及具体如何发挥作用在这里并不重要。

另一个用户安全原因不直接访问表T，通过下面方式访问这个视图。

注意这里加在主键上的约束使查询最多返回5条记录。

从性能角度来讲，查询优化器现在有两个选择。

第一个是先从视图当中返回所有记录，再应用过滤条件id BETWEEN 1 AND 5。

显然，如果视图返回大量数据，即使接下来的查询使用了用户提供的主键约束，性能还是很糟糕的。

第二个是合并视图查询和用户查询，就像下面这样：
使用这个查询，可以立即应用对主键约束的索引。

结果，不管表中有多少数据都能获得理想的性能。

只要可能，查询优化器都会利用视图合并。

然而，也不是总有这种机会。

比如，当视图在SELECT子句中使用分组、集合和分层操作时，查询优化器就无法使用视图合并。

这样的视图叫做不可合并视图。

了解视图合并后，接下来我们再说一说视图合并有可能带来哪些安全隐患。

比如，有个用户通过创建下面这个PL/SQL函数来访问这个视图。

如你所见，他将通过dbms_output包显示输入的参数：
当初始化参数optimizer_secure_view_merging被设置为FALSE时，可以运行两个测试查询。

两个查询都返回了用户应该看到的记录。

但是在第二个查询中，因为视图合并和加到查询里的函数的作用，可以看到本不应该被访问到的数据。

当初始化参数optimizer_secure_view_merging被设置为TRUE时，第二个查询返回如下的输出。

就如你看到的，这时函数和查询结果集返回相同的数据。

概括起来，把初始化参数optimizer_secure_view_merging设置为TRUE后，查询优化器会检查视图合并是否会导致安全隐患。

如果有危险，就不进行视图合并，当然也可能因此达不到最理想的性能。

所以，如果不是因为安全的目的使用视图，最好把这个参数设为FALSE。

初始化参数optimizer_secure_view_merging是动态的，可以在实例级修改，但不能在会话级修改。

不过，拥有对象权限MERGE VIEW或系统权限MERGE ANY VIEW的用户不受此初始化参数的约束。