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

《ORACLE性能优化之SQL优化-优化器》由会员分享，可在线阅读，更多相关《ORACLE性能优化之SQL优化-优化器（22页珍藏版）》请在金锄头文库上搜索。

1、Oracle9i 优化器介绍By DavisE-Mail:Blog:选择合适的优化器目标默认情况下，CBO 以最佳吞吐量为目标，这意味着 Oracle 使用尽可能少的资源去处理被语句访问到的所有行；当然 CBO 也可以用最快的响应速度来优化 SQL，这意味着 Oracle用尽可能少的资源去处理被语句访问到的第一行或前面少数行，当然这种情况对于整个语句来说可能消耗更多的资源。优化器产生的执行计划会因“优化器目标”的不同而不同。如果以最佳吞吐量为目标，结果更倾向于使用全表扫描而不是索引扫描，或者使用排序合并连接而不是嵌套循环连接；如果以最快的响应速度为目标，其结果则通常倾向于使用索引扫描和嵌套循环

2、连接。例如，假使你有一个语句既能运行于嵌套循环连接又能运行于排序合并连接，排序合并连接能够较快的返回全部查询结果，而嵌套循环能快速的返回第一行或前面少数行结果。如果你是以提高吞吐量为优化器目标，优化器就会倾向于选择排序合并连接；如果你的优化器目标是提高响应速度，则优化器倾向于选择嵌套循环连接。选择优化器目标要以你的应用为基础，一般规则是：1、 对于批处理应用，以最佳吞吐量为优化目标为好。例如 Oracle 报表应用程序。2、 对于交互式应用，以最快响应速度为优化目标为好。例如 SQLPLUS 的查询。影响优化器优化目标的因素主要有：1、 OPTIMIZER_MODE 初始化参数。2、 数据字典

3、中的 CBO 统计数据。3、 用来改变 CBO 优化目标的 Hints。OPTIMIZER_MODE 初始化参数这个初始化参数用来规定实例的默认优化方法。其值列表及说明如下：ValueCHOOSEALL_ROWSDescription此为缺省值。优化器既可以使用基于成本的优化方法(CBO)，也可以使用基于规则的优化方法(RBO)，其决定于是否有可用的统计信息。1、 如果在被访问的表中，至少有一个表在数据字典中有可用的统计信息存在，则优化器使用基于成本的方法。2、 如果在被访问的表中，只有部分表在数据字典中有可用的统计信息，优化器仍然会使用基于成本的方法，但是优化器必须为无统计信息的表利用一些内

4、部信息去尝试其他的统计，比如分配给这些表的数据块的数量等，这可能会导致产生不理想的执行计划。3、 如果在被访问的表中，没有一个表在数据字典中有统计信息，则优化器使用基于规则的方法。不论是否有统计信息存在，优化器都使用基于成本的方法，并以最佳吞1吐量为优化目标。FIRST_ROWS_n 不论是否有统计信息存在，优化器都使用基于成本的方法，并以最快的速度返回前 n 行数据集，n 可以是 1,10,100,1000。FIRST_ROWSRULE优化器使用成本与试探法混合的方式，去寻找一个可以最快返回前面少数行的执行计划。注：CBO 使用试探法产生的执行计划，其成本可能会比不使用试探法要大。FIRST

5、_ROWS 可用于向后兼容和计划稳定性。不论是否有统计信息存在，优化器都会使用基于规则的方法。你可以在 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

6、 优化目标，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 包可以并行的收集统计信息，可以为分区对象收集全局统

7、计信息，以及使用其他方式优化收集操作。但是，收集和基于成本优化器无关的信息必须用 ANALYZE 而不是 DBMS_STATS，比如： 使用 VALIDATE 或 LIST CHAINED ROWS子句。 收集 freelist 块的信息。CBO 如何对 SQL 做最快响应的优化2OPTIMIZER_MODE 被设置成 FIRST_ROWS_n、FIRST_ROWSS ，或者 SQL 语句中使用了 FIRST_ROWS(n)、FIRST_ROWS 提示，CBO 都会对 SQL 做最快响应的优化。这非常适用于联机用户，像通过 Oracle Forms 或 Web 访问的用户。联机用户的特点是只对

8、前面少数行感兴趣，很少看整个查询的结果，特别是在查询结果巨大的情况下。对于这样的用户，优化 SQL 使前面少数行尽可能快速的返回是有意义的，即使产生整个查询结果的时间并不理想。CBO 在做这种优化时，会产生一个处理第一行或前面少数行消耗成本最低的执行计划。CBO 有两种用来产生最快响应速度的方法，一个是旧方法一个是新方法。旧的方法就是用FIRST_ROWS 提示或初始化参数，这种方法 CBO 会使用成本和规则混合的方式来产生一个计划。Oracle 保留这种方法是为了向后兼容。新方法 FIRST_ROWS_n 或 FIRST_ROWS(n)提示，是完全基于成本的。如果 n 值较小，CBO 倾向于

9、产生一个包含嵌套循环连接和索引查询的执行计划；如果 n 值较大，则 CBO 倾向于产生一个包含散列连接和全表扫描的执行计划。理解基于成本的优化器CBO 根据可用的访问路径和表、索引等对象的统计信息来确定当前 SQL 的哪个执行计划是最高效的或成本最低的；同时 CBO 也会考虑 Hints 的建议。CBO 执行下列步骤：1、 优化器根据可用的访问路径和 Hints 为 SQL 语句产生一组潜在的执行计划。2、 优化器根据数据字典的统计信息评估每个计划的成本。成本就是一个评估值，它与 SQL语句按照某个计划执行所消耗的计算机资源是成正比的。优化器基于对计算机资源(I/O、CPU、内存)的评估，计算

10、访问路径和连接顺序的成本。3、 优化器对比执行计划的成本，从而选择一个成本最低的执行计划。CBO 包含下列组件： 查询变换器（Query Transformer） 评估器（Estimator） 计划生成器（Plan Generator）如下图所示：3查询变换器（基于成本的优化器组件）被解析器解析过的查询语句进入查询变换器，表现出来的是一组查询块(query block)，这些查询块之间是相互关联的或者是嵌套的，查询的形式决定这些查询块相互之间如何被关联。查询变换器的主要目的就是决定改变查询的形式是否有利于产生一个好的执行计划。查询变换器使用四种不同的查询变换技术： 视图合并（View Merg

11、ing） 谓词推进（Predicate Pushing） 非嵌套子查询（Subquery Unnesting ） 物化视图的查询重写（Query Rewrite with Materialized Views）最终应用于查询的也可以是以上四种变换技术的任意组合。视图合并查询中的每个视图都会被解析器扩展到一个独立的查询块中，这个查询块本质上是用来描述视图定义的，是视图的结果。优化器的一个任务就是去分析这个独立视图查询块(viewquery block)并产生一个视图子计划 (subplan)，然后优化器在产生整个查询执行计划的同时使用视图子计划来处理剩余的查询部分。由于视图是被独立在整个查询之外

12、被优化的，因此这种技术常常会导致一个不良执行计划的产生。查询变换器通过将视图查询块合并到查询块中从而消除这种不良执行计划。绝大多数类型的视图是可以被合并的。在一个视图被合并后，它原有的视图查询块被包含到查询块中，也就是说视图查询块不存在了，因此也不再需要产生一个子计划。谓词推进4对于那些不能合并的视图，查询变换器能够将相关的谓词从查询块中推进到视图查询块中。由于被推进的谓词能够用来访问索引或者用于过滤，这个技术通常可以改进那些不能被合并的视图子计划。非嵌套的子查询和视图一样，子查询也是用一个独立的查询块来代表的。子查询是被嵌套在主查询或其他子查询之中的，计划产生器在找到一个成本最低的执行计划之

13、前被迫要试验所有可能的计划。由嵌套子查询产生的限制可以在转换为非嵌套的子查询和连接之后消除，经由查询转换器过滤之后绝大多数的子查询都会被转换为非嵌套的，然后这些非嵌套的子查询产生独立的子计划，这些子计划按照一种高效的方式进行排列，从而提高了整个查询计划的执行速度。物化视图的查询重写物化视图就是把一个查询的结果事先固化存储在一个表里，当发现和物化视图一致的查询语句就将相应的项用物化视图来重写。由于绝大多数的查询结果都事先计算好了，因此这种技术可以极大的提高查询速度。查询转换器负责查找和用户查询相关的所有物化视图，用其中的一个或多个来重写查询。利用物化视图来重写查询也是基于成本的，如果不使用物化视

14、图的成本更低一些，则不会去使用物化视图。评估器评估器会产生下列三个度量值： 选择性（Selectivity） 基数（Cardinality） 成本（Cost ）这些值是相互关联的，一个值由其他值导出，评估器的最终目标是评估计划的总体成本。如果有统计信息可用，评估器使用统计信息来计算这些值，统计信息可以提高其精确度。选择性这里的第一个度量值选择性，表示所选择的行与行集的比值。所谓行集可以是表、视图，或者是一个连接或 GROUP BY 操作的中间结果。选择性与查询中的谓词有关，比如last_name=Smith，或者一个联合谓词 last_name=Smith and job_type=Clerk

15、。一个谓词充当着一个过滤器的角色，在行集中过滤了一定量的行，谓词的选择性是一个比值，它表示一个行集经过谓词的过滤后剩下的行占原有行集的比例。其值在 0.0 和 1.0 之间，0.0 表示在行集中没有行被选择；1.0 表示行集中的所有行都被选择了。如果没有可用的统计信息，评估器为选择性赋予一个内部的缺省值，这个内部缺省值随着谓词的不同而不同。例如：等式谓词（last_name=Smith）的内部缺省值低于范围谓词（last_nameSmith），评估器会假定等式谓词返回的行数小于范围谓词。当存在可用的统计信息，评估器将使用统计信息来估算选择性。例如：对于一个等式谓词 （ last_name=Sm

16、ith ）， 选 择 性的 值是 distinct last_name 的 倒 数 即 ：（ 1/count(distinctlast_name)）。但是如果在 last_name 字段上存在直方图（ histogram），则选择性值为：count5（last_name）where last_name=Smith / count （last_name）where last_name is not null。可见在数据倾斜的字段上应用直方图能够帮助 CBO 进行准确的选择性评估。基数基数就是行集中行的数量。基数分为： 基础基数（Base cardinality）：就是基表中的行数。基础基数在表分析期间获得。如果表没有可用的统计信息，则评估器利用表中区（extents）的数量来估算基础基数。 有效基数（Effective cardinality）：就是从基表中选择的行数。有效基数与