ORACLE性能优化之SQL优化-优化器

Oracle9i优化器介绍By DavisE-Mail:todavis@Blog:选择合适的优化器目标默认情况下，CBO 以最佳吞吐量为目标，这意味着Oracle 使用尽可能少的资源去处理被语句访问到的所有行；当然CBO 也可以用最快的响应速度来优化SQL，这意味着Oracle用尽可能少的资源去处理被语句访问到的第一行或前面少数行，当然这种情况对于整个语句来说可能消耗更多的资源。

优化器产生的执行计划会因―优化器目标‖的不同而不同。

如果以最佳吞吐量为目标，结果更倾向于使用全表扫描而不是索引扫描，或者使用排序合并连接而不是嵌套循环连接；如果以最快的响应速度为目标，其结果则通常倾向于使用索引扫描和嵌套循环连接。

例如，假使你有一个语句既能运行于嵌套循环连接又能运行于排序合并连接，排序合并连接能够较快的返回全部查询结果，而嵌套循环能快速的返回第一行或前面少数行结果。

如果你是以提高吞吐量为优化器目标，优化器就会倾向于选择排序合并连接；如果你的优化器目标是提高响应速度，则优化器倾向于选择嵌套循环连接。

选择优化器目标要以你的应用为基础，一般规则是：1、对于批处理应用，以最佳吞吐量为优化目标为好。

例如Oracle 报表应用程序。

2、对于交互式应用，以最快响应速度为优化目标为好。

例如SQLPLUS 的查询。

影响优化器优化目标的因素主要有：1、OPTIMIZER_MODE 初始化参数。

2、数据字典中的CBO 统计数据。

3、用来改变CBO 优化目标的Hints。

OPTIMIZER_MODE初始化参数这个初始化参数用来规定实例的默认优化方法。

其值列表及说明如下：Value CHOOSEALL_ROWSDescription此为缺省值。

优化器既可以使用基于成本的优化方法(CBO)，也可以使用基于规则的优化方法(RBO)，其决定于是否有可用的统计信息。

1、如果在被访问的表中，至少有一个表在数据字典中有可用的统计信息存在，则优化器使用基于成本的方法。

2、如果在被访问的表中，只有部分表在数据字典中有可用的统计信息，优化器仍然会使用基于成本的方法，但是优化器必须为无统计信息的表利用一些内部信息去尝试其他的统计，比如分配给这些表的数据块的数量等，这可能会导致产生不理想的执行计划。

3、如果在被访问的表中，没有一个表在数据字典中有统计信息，则优化器使用基于规则的方法。

不论是否有统计信息存在，优化器都使用基于成本的方法，并以最佳吞1你可以在SESSION 中改变CBO 优化目标：ALTER SESSION SET OPTIMIZER_MODE。

例如：1、在初始化参数文件中加入如下语句，可以在实例级改变CBO 优化目标：OPTIMIZER_MODE=FIRST_ROWS_12、下面的语句可以改变当前SESSION 的CBO 优化目标：ALTER SESSION SET OPTIMIZER_MODE=FIRST_ROWS_1可以改变CBO优化目标的Hints使用如下Hints 可以单独为具体的SQL 指定CBO 优化目标，SQL 语句中Hints 能够覆盖OPTIMIZER_MODE 初始化参数。

● FIRST_ROWS(n)，n 为任意正整数。

● FIRST_ROWS● ALL_ROWS● CHOOSE● RULE数据字典中的CBO统计信息CBO 使用的统计信息存放于数据字典中，你可以使用DBMS_STATS 包或ANALYZE语句以精确的方式或估算的方式来统计对象的物理存储特征和数据分布情况。

注意：Oracle 公司建议使用DBMS_STATS 包来代替ANALYZE 语句收集统计信息。

DBMS_STATS 包可以并行的收集统计信息，可以为分区对象收集全局统计信息，以及使用其他方式优化收集操作。

但是，收集和基于成本优化器无关的信息必须用ANALYZE 而不是DBMS_STATS，比如：● 使用VALIDATE 或LIST CHAINED ROWS 子句。

● 收集freelist 块的信息。

CBO如何对SQL做最快响应的优化OPTIMIZER_MODE 被设置成FIRST_ROWS_n、FIRST_ROWSS，或者SQL 语句中使用了FIRST_ROWS(n)、FIRST_ROWS 提示，CBO 都会对SQL 做最快响应的优化。

这非常适用于联机用户，像通过Oracle Forms 或Web 访问的用户。

联机用户的特点是只对前面少数行感兴趣，很少看整个查询的结果，特别是在查询结果巨大的情况下。

对于这样的用户，优化SQL 使前面少数行尽可能快速的返回是有意义的，即使产生整个查询结果的时间并不理想。

CBO 在做这种优化时，会产生一个处理第一行或前面少数行消耗成本最低的执行计划。

CBO 有两种用来产生最快响应速度的方法，一个是旧方法一个是新方法。

旧的方法就是用FIRST_ROWS 提示或初始化参数，这种方法CBO 会使用成本和规则混合的方式来产生一个计划。

Oracle 保留这种方法是为了向后兼容。

新方法FIRST_ROWS_n 或FIRST_ROWS(n)提示，是完全基于成本的。

如果n 值较小，CBO 倾向于产生一个包含嵌套循环连接和索引查询的执行计划；如果n 值较大，则CBO 倾向于产生一个包含散列连接和全表扫描的执行计划。

理解基于成本的优化器CBO 根据可用的访问路径和表、索引等对象的统计信息来确定当前SQL 的哪个执行计划是最高效的或成本最低的；同时CBO 也会考虑Hints 的建议。

CBO 执行下列步骤：1、优化器根据可用的访问路径和Hints 为SQL 语句产生一组潜在的执行计划。

2、优化器根据数据字典的统计信息评估每个计划的成本。

成本就是一个评估值，它与SQL 语句按照某个计划执行所消耗的计算机资源是成正比的。

优化器基于对计算机资源(I/O、CPU、内存)的评估，计算访问路径和连接顺序的成本。

3、优化器对比执行计划的成本，从而选择一个成本最低的执行计划。

CBO 包含下列组件：● 查询变换器（Query Transformer）● 评估器（Estimator）● 计划生成器（Plan Generator）如下图所示：（基于成本的优化器组件）查询变换器被解析器解析过的查询语句进入查询变换器，表现出来的是一组查询块(query block)，这些查询块之间是相互关联的或者是嵌套的，查询的形式决定这些查询块相互之间如何被关联。

查询变换器的主要目的就是决定改变查询的形式是否有利于产生一个好的执行计划。

查询变换器使用四种不同的查询变换技术：● 视图合并（View Merging）● 谓词推进（Predicate Pushing）● 非嵌套子查询（Subquery Unnesting）● 物化视图的查询重写（Query Rewrite with Materialized Views）最终应用于查询的也可以是以上四种变换技术的任意组合。

视图合并查询中的每个视图都会被解析器扩展到一个独立的查询块中，这个查询块本质上是用来描述视图定义的，是视图的结果。

优化器的一个任务就是去分析这个独立视图查询块(view query block)并产生一个视图子计划(subplan)，然后优化器在产生整个查询执行计划的同时使用视图子计划来处理剩余的查询部分。

由于视图是被独立在整个查询之外被优化的，因此这种技术常常会导致一个不良执行计划的产生。

查询变换器通过将视图查询块合并到查询块中从而消除这种不良执行计划。

绝大多数类型的视图是可以被合并的。

在一个视图被合并后，它原有的视图查询块被包含到查询块中，也就是说视图查询块不存在了，因此也不再需要产生一个子计划。

谓词推进对于那些不能合并的视图，查询变换器能够将相关的谓词从查询块中推进到视图查询块中。

由于被推进的谓词能够用来访问索引或者用于过滤，这个技术通常可以改进那些不能被合并的视图子计划。

非嵌套的子查询和视图一样，子查询也是用一个独立的查询块来代表的。

子查询是被嵌套在主查询或其他子查询之中的，计划产生器在找到一个成本最低的执行计划之前被迫要试验所有可能的计划。

由嵌套子查询产生的限制可以在转换为非嵌套的子查询和连接之后消除，经由查询转换器过滤之后绝大多数的子查询都会被转换为非嵌套的，然后这些非嵌套的子查询产生独立的子计划，这些子计划按照一种高效的方式进行排列，从而提高了整个查询计划的执行速度。

物化视图的查询重写物化视图就是把一个查询的结果事先固化存储在一个表里，当发现和物化视图一致的查询语句就将相应的项用物化视图来重写。

由于绝大多数的查询结果都事先计算好了，因此这种技术可以极大的提高查询速度。

查询转换器负责查找和用户查询相关的所有物化视图，用其中的一个或多个来重写查询。

利用物化视图来重写查询也是基于成本的，如果不使用物化视图的成本更低一些，则不会去使用物化视图。

评估器评估器会产生下列三个度量值：● 选择性（Selectivity）● 基数（Cardinality）● 成本（Cost）这些值是相互关联的，一个值由其他值导出，评估器的最终目标是评估计划的总体成本。

如果有统计信息可用，评估器使用统计信息来计算这些值，统计信息可以提高其精确度。

选择性这里的第一个度量值——选择性，表示所选择的行与行集的比值。

所谓行集可以是表、视图，或者是一个连接或GROUP BY 操作的中间结果。

选择性与查询中的谓词有关，比如last_name=‘Smith‘，或者一个联合谓词last_name=‘Smith‘ and job_type=‘Clerk‘。

一个谓词充当着一个过滤器的角色，在行集中过滤了一定量的行，谓词的选择性是一个比值，它表示一个行集经过谓词的过滤后剩下的行占原有行集的比例。

其值在0.0 和1.0 之间，0.0 表示在行集中没有行被选择；1.0 表示行集中的所有行都被选择了。

如果没有可用的统计信息，评估器为选择性赋予一个内部的缺省值，这个内部缺省值随着谓词的不同而不同。

例如：等式谓词（last_name=‘Smith‘）的内部缺省值低于范围谓词（last_name>‘Smith‘），评估器会假定等式谓词返回的行数小于范围谓词。

当存在可用的统计信息，评估器将使用统计信息来估算选择性。

例如：对于一个等式谓词（last_name=‘Smith‘ ），选择性的值是distinct last_name 的倒数即：（1/count(distinct（last_name）where last_name=‘Smith‘ / count（last_name）where last_name is not null。

可见在数据倾斜的字段上应用直方图能够帮助CBO 进行准确的选择性评估。

基数基数就是行集中行的数量。