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

Oracle9i 优化器介绍 By Davis E-Mai l: todavis@ Blog: davis. 选择合适的优化器目标默认情况下, CBO 以最佳吞吐量为目标,这意味着 Oracle 使用尽可能少的资源去处理被语句访问到的所有行;当然 CBO 也可以用最快的响应速度来优化 SQL ,这意味着 Oracle 用尽可能少的资源去处理被语句访问到的第一行或前面少数行,当然这种情况对于整个语句来说可能消耗更多的资源。优化器产生的执行计划会因“优化器目标”的不同而不同。如果以最佳吞吐量为目标, 结果更倾向于使用全表扫描而不是索引扫描,或者使用排序合并连接而不是嵌套循环连接; 如果以最快的响应速度为目标,其结果则通常倾向于使用索引扫描和嵌套循环连接。例如,假使你有一个语句既能运行于嵌套循环连接又能运行于排序合并连接,排序合并连接能够较快的返回全部查询结果,而嵌套循环能快速的返回第一行或前面少数行结果。如果你是以提高吞吐量为优化器目标,优化器就会倾向于选择排序合并连接;如果你的优化器目标是提高响应速度,则优化器倾向于选择嵌套循环连接。选择优化器目标要以你的应用为基础,一般规则是: 1、对于批处理应用,以最佳吞吐量为优化目标为好。例如 Oracle 报表应用程序。 2、对于交互式应用,以最快响应速度为优化目标为好。例如 SQLPLUS 的查询。影响优化器优化目标的因素主要有: 1、 OPTIMIZER_MODE 初始化参数。 2、数据字典中的 CBO 统计数据。 3、用来改变 CBO 优化目标的 Hints 。 OPTIMIZER_MODE 初始化参数这个初始化参数用来规定实例的默认优化方法。其值列表及说明如下: Value CHOOSE ALL_ROWS Description 此为缺省值。优化器既可以使用基于成本的优化方法(CBO) ,也可以使用基于规则的优化方法(RBO) ,其决定于是否有可用的统计信息。 1、如果在被访问的表中,至少有一个表在数据字典中有可用的统计信息存在,则优化器使用基于成本的方法。 2、如果在被访问的表中,只有部分表在数据字典中有可用的统计信息,优化器仍然会使用基于成本的方法,但是优化器必须为无统计信息的表利用一些内部信息去尝试其他的统计,比如分配给这些表的数据块的数量等,这可能会导致产生不理想的执行计划。 3、如果在被访问的表中,没有一个表在数据字典中有统计信息,则优化器使用基于规则的方法。不论是否有统计信息存在,优化器都使用基于成本的方法,并以最佳吞 1 吐量为优化目标。 FIRST_ROWS_n 不论是否有统计信息存在,优化器都使用基于成本的方法,并以最快的速度返回前 n 行数据集, n 可以是 1,10,100,1000 。 FIRST_ROWS RULE 优化器使用成本与试探法混合的方式,去寻找一个可以最快返回前面少数行的执行计划。注: CBO 使用试探法产生的执行计划,其成本可能会比不使用试探法要大。 FIRST_ROWS 可用于向后兼容和计划稳定性。不论是否有统计信息存在,优化器都会使用基于规则的方法。你可以在 SESSION 中改变 CBO 优化目标: ALTER SESSION SET OPTIMIZER_MODE 。例如: 1 、在初始化参数文件中加入如下语句,可以在实例级改变 CBO 优化目标: OPTIMIZER_MODE=FIRST_ROWS_1 2 、下面的语句可以改变当前 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 做最快响应的优化