新博客http://www.cnblogs.com/zhjh256 欢迎访问
分类： Oracle
2008-04-18 14:59:41
优化器操作
对于任何SQL语句，优化器都执行以下操作：
评价表达式和条件：优化器首先尽可能多的评价包含常量的谓词；
语句转换；
选择优化器目标；
选择访问路径；
选择连接顺序；
选择优化器目标
默认情况下，查询优化器的目标是最佳吞吐量，Oracle也可以优化一个语句为最佳响应时间。
对于批处理应用程序，通常是优化最佳吞吐量，而对于交互式程序，通常是最佳响应时间。
影响优化器行为的因素包括：OPTIMIZER_MODE参数，改变查询优化器目标的优化器SQL提示，DD中的查询优化器统计。
OPTIMIZER_MODE初始化参数
OPTIMIZER_MODE参数决定优化器的默认行为，其取值包括：
·ALL_ROWS：默认；
·FIRST_ROWS_n：以最优返回Ｎ行为优化器目标；
·FIRST_ROWS：使用该参数可能会消耗大量的资源，主要是为了向后兼容，推荐使用FIRST_ROWS_n。
可以使用ALTER SESSION SET OPTIMIZER_MODE改变会话中全部SQL语句的目标。
优化器SQL提示
声明单个语句的优化器目标，使用提示FIRST_ROWS(n)或ALL_ROWS，这些提示会副该参数设置。
DD中的查询优化器统计
查询优化器统计通过DBMS_STATS包收集 。
启用/禁用查询优化器特征
这一部分对于调整Oracle应用程序来说特别重要。
启用查询优化器特征
可以通过设置初始化参数OPTIMIZER_FEATURES_ENABLE启用优化器特征。
OPTIMIZER_FEATURES_ENABLE参数
该参数控制使用的优化器特征的版本，比如说从Oracle8i升级到了Oracle9i，默认情况下参数为9.2.0，如果将它设置为8.1.6，那么将使用Oracle8i的优化器特征。Oracle推荐不要显示设置该参数，而是更改应用程序中的相关SQL，个人认为也是如此，既然升级就是为了更好的性能，因此应该使用新的优化器特征。
控制优化器的行为
CURSOR_SHARING
该参数主要是控制文本常量的替代行为，见CURSOR具体文档
DB_FILE_MULTIBLOCK_READ_COUNT
该参数控制全表扫描和索引快速全扫描的单次I/O读取的块数，较大的值可能会使优化器选择全表扫描代替索引扫描，该参数默认为8。
OPTIMIZER_INDEX_CACHING
该参数控制与嵌套循环一起使用时索引探测的代价，范围从0..100指示在缓存缓冲中的索引块的百分比，其更改优化器关于嵌套循环和IN列表索引缓存的假设。100指示全部的索引块都可能在缓存中找到并且调整索引探测的代价。
OPTIMIZER_INDEX_COST_ADJ
该参数用来调整索引探测的代价，范围从1..10000。默认为100，表示使用普通的代价模型估计索引作为访问路径。10表示使用索引作为访问路径的代价为正常的1/10。
OPTIMIZER_MODE
见上。
PGA_AGGREGATE_TARGET
该参数自动控制分配用于排序和哈希连接的内存总量。
STAR_TRANSFORMATION_ENABLED
如果为TRUE，优化器考虑使用星型转换，星型查询接合事实表的各个列上的位图。
理解查询优化器
查询优化器的执行步骤：
·基于可用的访问路径和提示产生一系列潜在计划；
·优化器根据DD中统计的数据分布和表，索引，分区的存储特征估计每一计划的代价；
·优化器比较各代价并选择代价最低的计划；
查询转换
查询转换器的输入是解析的查询，其以一系列的查询块表现，查询块可以内部相关或嵌套。查询的形式决定了查询块如何嵌套或内部相关。转换优化器的主要目标是确定改变查询的形式是否可以产生更好的查询计划。查询转换应用的技术如下：
视图接合 通常在不使用视图接合的情况下会产生次优的执行计划，因为视图是单独优化的。
谓词推进 对于那些不可接合的视图，查询优化器可以从包含查询的块推进谓词到视图。这种技术可以提高不可接合视图的子计划的性能，因为推进谓词可以作为索引或过滤器。
子查询解除嵌套 通常包含子查询的查询的性能可以通过解除子查询提高，大多数子查询被查询转换器解除
物化视图的查询重写 如果没有物化视图产生的计划的代价低于使用物化视图产生的代价，那么查询不会重写。
评价
评估器产生三种不同类型的测量，代价（包括CPU，I/O，内存），选择性，基数性。这些测量相互关联，相互驱动，评估器的目标是估计给定查询的全局代价。访问路径决定了从一个基表得到数据所需的工作单元。
阅读和理解执行计划
执行计划包含访问路径、连接顺序、连接方法。
理解查询优化器的访问路径
全表扫描
在访问大量数据时全表扫描更有效的原因是：全表扫描使用较大较少的I/O，这比较小较多的I/O代价更少。
优化器使用全表扫描的情况：缺少索引；处理大量数据；小表：如果表的大小小于DB_FILE_MULTIBLOCK_READ_COUNT，则只需要一次I/O调用；高度并行性：ALL_TABLES的DEGREE列确定了表的并行性。
全表扫描提示
使用FULL(table alias)指示优化器使用全表扫描，可以使用CACHE/NOCACHE提示指示提取的块是否放在缓存中。自动缓存小表可以通过CACHE属性更改。小表自动缓存的标准：
小：小于20块，总缓存块的2%。如果STATISTICS_LEVEL设置为大于TYPICAL，Oracle根据表的扫描历史决定是否缓存。如果为BASIC则不缓存。
中：大于小表，小于总缓存块的10%。Oracle根据表的扫描历史和负载统计来决定是否缓存。
大：超过缓存块的10%。不缓存表。
Index扫描
索引扫描类型：
·以块评估I/O，而非行
Oracle以块执行I/O，因此优化器决定使用全表扫描是受访问块的百分比影响，而非行。这称为索引聚簇因子，如果每个块仅包含一行，行访问和块访问是一样的。虽然聚簇因子是索引的属性，但是它也与表内索引列的相近的值在数据块内的分布有关。
·索引唯一扫描
在唯一键/主键上扫描时使用。
·索引范围扫描
如果需要排序，则声明ORDER BY子句，不要依赖于索引。
·索引范围扫描降序
当声明desc的排序满足索引时，优化器将使用降序索引范围扫描。也可以使用提示INDEX_DESC(table_alias index_name)。
·索引跳跃扫描
通常扫描索引块要比扫描表数据块块。逻辑子索引的数量由开头列的唯一值的数量决定。
·全扫描
如果谓词引用索引中的一个列，全扫描将成为一种方法。
·快速全索引扫描
可以使用初始化参数OPTIMIZER_FEATURES_ENABLE或INDEX_FFS提示（其参数与正常的HINT提示相同）声明快速全索引扫描。
·索引连接
索引连接是一种哈希连接各个索引在一起，这些索引中包含了查询引用的全部列。如果使用了索引连接就不再需要访问表，索引连接不能用来消除排序。使用INDEX_JOIN声明索引连接提示。
·位图索引。
哈希访问
哈希扫描用来在哈希聚簇中定位数据，基于哈希值。
样本表扫描
当FROM子句中包含了SAMPLE子句或SAMPLE BLOCK子句时将使用这种访问路径。
优化器如何选择访问路径
查询优化器基于以下因素选择访问路径：
·语句可用的访问路径；
·估计的执行语句的代价；
理解连接
优化器如何执行连接语句
为了选择连接语句的执行计划，优化器必须作出以下相关决定：
·访问路径；·连接方法；·连接顺序；
优化器如何为连接选择执行计划
·优化器首先确定连接表的定义中是否有导致最后行源唯一行的表；
·对于带外连接的连接语句，带外连接操作符的表必须在连接的其他表的后面。优化器不会考虑破坏这一规则的连接顺序。类似的当一个子查询转换为半连接或反连接时，子查询的表将在外部的表的后面。但是哈希反连接和半连接可能覆盖这种情况。
优化器估计代价的方法包括：
·嵌套循环的代价基于读取外表选择的行以及相应的内表匹配行到内存中的代价。
·排序接合连接的代价很大程度上依赖于读取所有行源到内存中并排序它们的代价。
·哈希连接的代价很大程度上依赖于建立哈希表的代价以及使用其他行源的行探测的代价。
优化器考虑的其他因素包括：
·一个小的排序区域大小通常会增加排序接合连接的代价，因为需要使用更多的CPU和I/O。
·相对于嵌套循环连接，使用较大的多块读可以降低排序接合连接的代价。
