oracle 优化器相关知识-CSDN博客

oracle 优化器相关知识

Oracle的优化器共有3种:

　　a. RULE (基于规则)

　　b. COST (基于成本)

　　c. CHOOSE (选择性)。

　　设置缺省的优化器,可以通过对init.ora文件中OPTIMIZER_MODE参数的各种声明,如RULE，COST，CHOOSE，ALL_ROWS，FIRST_ROWS 。你当然也在SQL句级或是会话(session)级对其进行覆盖。

　　为了使用基于成本的优化器(CBO, Cost-Based Optimizer) , 你必须经常运行analyze 命令,以增加数据库中的对象统计信息(object statistics)的准确性。

　　如果数据库的优化器模式设置为选择性(CHOOSE),那么实际的优化器模式将和是否运行过analyze命令有关。如果table已经被analyze过, 优化器模式将自动成为CBO , 反之,数据库将采用RULE形式的优化器。

　　在缺省情况下,Oracle采用CHOOSE优化器, 为了避免那些不必要的全表扫描（full table scan）, 你必须尽量避免使用CHOOSE优化器,而直接采用基于规则或者基于成本的优化器

、如何设置优化器

设置缺省的优化器,可以通过对init.ora文件中OPTIMIZER_MODE参数的各种声明,如RULE,COST,CHOOSE,ALL_ROWS,FIRST_ROWS。

你当然也在SQL句级或是会话(session)级对其进行覆盖. 你可以在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

3、可以改变CBO优化目标的Hints

使用如下Hints可以单独为具体的SQL指定CBO优化目标，SQL语句中Hints能够覆盖OPTIMIZER_MODE初始化参数。
OPTIMIZER_MODE

Value	Description
CHOOSE	此为缺省值。优化器既可以使用基于成本的优化方法(CBO)，也可以使用基于规则的优化方法(RBO)，其决定于是否有可用的统计信息。 1、如果在被访问的表中，至少有一个表在数据字典中有可用的统计信息存在，则优化器使用基于成本的方法。 2、如果在被访问的表中，只有部分表在数据字典中有可用的统计信息，优化器仍然会使用基于成本的方法，但是优化器必须为无统计信息的表利用一些内部信息去尝试其他的统计，比如分配给这些表的数据块的数量等，这可能会导致产生不理想的执行计划。 3、如果在被访问的表中，没有一个表在数据字典中有统计信息，则优化器使用基于规则的方法。
ALL_ROWS	不论是否有统计信息存在，优化器都使用基于成本的方法，并以最佳吞吐量为优化目标。
FIRST_ROWS_n	不论是否有统计信息存在，优化器都使用基于成本的方法，并以最快的速度返回前n行数据集，n可以是1,10,100,1000。
FIRST_ROWS	优化器使用成本与试探法混合的方式，去寻找一个可以最快返回前面少数行的执行计划。
RULE	不论是否有统计信息存在，优化器都会使用基于规则的方法。

三、执行计划

Oracle 用来运行一个语句的步骤就叫做执行计划（execution plan ），执行计划包含了语句所涉及的每个表的访问路径和连接顺序。

使用EXPLAIN PLAN 语句可以查看优化器所选择的执行计划，首先要创建PLAN_TABLE ，用来存放执行计划的描述信息：

@$ORACLE_HOME/RDBMS/ADMIN/UTLXPLAN.SQL

EXPLAINPLAN 是一个很好的分析SQL 语句的工具, 它甚至可以在不执行SQL 的情况下分析语句. 通过分析, 我们就可以知道ORACLE 是怎么样连接表, 使用什么方式扫描表( 索引扫描或全表扫描) 以及使用到的索引名称。

你需要按照从里到外, 从上到下的次序解读分析的结果. EXPLAIN PLAN 分析的结果是用缩进的格式排列的, 最内部的操作将被最先解读, 如果两个操作处于同一层中, 带有最小操作号的将被首先执行。