oracle性能优化之sql优化-优化器

《oracle性能优化之sql优化-优化器》由会员上传分享，免费在线阅读，更多相关内容在应用文档-天天文库。

1、Oracle9i优化器介绍ByDavisE-Mail:todavis@gmail.comBlog:davis.blogchina.com选择合适的优化器目标默认情况下，CBO以最佳吞吐量为目标，这意味着Oracle使用尽可能少的资源去处理被语句访问到的所有行；当然CBO也可以用最快的响应速度来优化SQL，这意味着Oracle用尽可能少的资源去处理被语句访问到的第一行或前面少数行，当然这种情况对于整个语句来说可能消耗更多的资源。优化器产生的执行计划会因“优化器目标”的不同而不同。如果以最佳吞吐量为目标，

2、结果更倾向于使用全表扫描而不是索引扫描，或者使用排序合并连接而不是嵌套循环连接；如果以最快的响应速度为目标，其结果则通常倾向于使用索引扫描和嵌套循环连接。例如，假使你有一个语句既能运行于嵌套循环连接又能运行于排序合并连接，排序合并连接能够较快的返回全部查询结果，而嵌套循环能快速的返回第一行或前面少数行结果。如果你是以提高吞吐量为优化器目标，优化器就会倾向于选择排序合并连接；如果你的优化器目标是提高响应速度，则优化器倾向于选择嵌套循环连接。选择优化器目标要以你的应用为基础，一般规则是：1、对于批处理应用

3、，以最佳吞吐量为优化目标为好。例如Oracle报表应用程序。2、对于交互式应用，以最快响应速度为优化目标为好。例如SQLPLUS的查询。影响优化器优化目标的因素主要有：1、OPTIMIZER_MODE初始化参数。2、数据字典中的CBO统计数据。3、用来改变CBO优化目标的Hints。OPTIMIZER_MODE初始化参数这个初始化参数用来规定实例的默认优化方法。其值列表及说明如下：ValueCHOOSEALL_ROWSDescription此为缺省值。优化器既可以使用基于成本的优化方法(CBO)，也

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

5、化器都使用基于成本的方法，并以最佳吞1吐量为优化目标。FIRST_ROWS_n不论是否有统计信息存在，优化器都使用基于成本的方法，并以最快的速度返回前n行数据集，n可以是1,10,100,1000。FIRST_ROWSRULE优化器使用成本与试探法混合的方式，去寻找一个可以最快返回前面少数行的执行计划。注：CBO使用试探法产生的执行计划，其成本可能会比不使用试探法要大。FIRST_ROWS可用于向后兼容和计划稳定性。不论是否有统计信息存在，优化器都会使用基于规则的方法。你可以在SESSION中改变C

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

7、。lFIRST_ROWS(n)，n为任意正整数。lFIRST_ROWSlALL_ROWSlCHOOSElRULE数据字典中的CBO统计信息CBO使用的统计信息存放于数据字典中，你可以使用DBMS_STATS包或ANALYZE语句以精确的方式或估算的方式来统计对象的物理存储特征和数据分布情况。注意：Oracle公司建议使用DBMS_STATS包来代替ANALYZE语句收集统计信息。DBMS_STATS包可以并行的收集统计信息，可以为分区对象收集全局统计信息，以及使用其他方式优化收集操作。但是，收集和基于

8、成本优化器无关的信息必须用ANALYZE而不是DBMS_STATS，比如：l使用VALIDATE或LISTCHAINEDROWS子句。l收集freelist块的信息。CBO如何对SQL做最快响应的优化2OPTIMIZER_MODE被设置成FIRST_ROWS_n、FIRST_ROWSS，或者SQL语句中使用了FIRST_ROWS(n)、FIRST_ROWS提示，CBO都会对SQL做最快响应的优化。这非常适用于联机用户，像通过OracleForms或Web