Oracle的SQL执行有一个执行计划产生的过程,那么ORACLE是通过什么来产生执行计划的呢,在ORACLE性能调优的联机文档里有提到基于成本的优化方法,同时也有启发式的优化方法可供选择。
在优化器的参数方面,ORACLE使用了一个叫做OPTIMIZER_MODE的参数来控制实例级别的优化器模式。
OPTIMIZER_MODE包含了三个值:
1.ALL_ROWS:
The optimizer uses a cost-based approach for all SQL statements in the session regardless of the presence of statistics and optimizes with a goal of best throughput (minimum resource use to complete the entire statement). This is the default value.
2.FIRST_ROWS_
n
The optimizer uses a cost-based approach, regardless of the presence of statistics, and optimizes with a goal of best response time to return the first n number of rows; n can equal 1, 10, 100, or 1000.
3.FIRST_ROWS
The optimizer uses a mix of cost and heuristics to find a best plan for fast delivery of the first few rows.
从这里可以看出,前两个参数使用的是基于成本的优化器,而最后一个使用的是混合了基于成本和启发式优化器的模式。
而且,从目标的角度来讲的话,第一个是返回所有行的最小代价,第二个是返回前N行的最小代价,最后一个是返回前面的少量行的最好计划。
Note: Using heuristics sometimes leads the query optimizer to generate a plan with a cost that is significantly larger than the cost of a plan without applying the heuristic.
FIRST_ROWS
is available for backward compatibility and plan stability; use
FIRST_ROWS_
n
instead.
这里说,first_rows这个参数值是为了向后兼容考虑的,因为启发式优化方式可能带来很大的成本开销。所有建议使用第二个参数值(FIRST_ROWS_n)
在SESSION级别,可以使用
ALTER
SESSION
SET
OPTIMIZER_MODE来修改本SESSION的优化器方式。
在SQL级别,可以使用HINT来强制SQL语句使用你想要的优化器方式:
1.FIRST_ROWS(n)
This hint instructs Oracle to optimize an individual SQL statement with a goal of best response time to return the first n number of rows, where n equals any positive integer. The hint uses a cost-based approach for the SQL statement, regardless of the presence of statistic.
2.ALL_ROWS
This hint explicitly chooses the cost-based approach to optimize a SQL statement with a goal of best throughput.
最后要注意的是:
如果在SQL语句中涉及的表没有可用的统计信息,ORACLE会怎么做呢?(调用内部信息,如块等)
If the optimizer uses the cost-based approach for a SQL statement, and if some tables accessed by the statement have no statistics, then the optimizer uses internal information, such as the number of data blocks allocated to these tables, to estimate other statistics for these tables.