Oracle OPTIMIZER_MODE 小敘

http://dba.sky-mobi.com/?p=316

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.