《Optimizer_mode First_Rows, First_Rows_n》

2012-10-11

摘要:《Optimizer_mode First_Rows, First_Rows_n》

Optimizer_mode First_Rows, First_Rows_n

Oracle 9i 升級 10g 遭遇問題如下:

原本是 Rule base 後來升級 10g 後改 First_Rows 造成某些 SQL 執行效能較以往差，

提供給大家參考。

First_Rows 以最快回傳第一筆資料的執行計劃為優先考量

First_Rows_n 以回傳 n 筆資料的執行計劃為優先考量

理論上 First_Rows 應該比 First_Rows_n cost 較少

但天總是不如人願。

原來 FIRST_ROWS_n 會參考統計資料，執行 cost 最少的執行計劃。

FIRST_ROWS 是混合cost base、heuristics(啟發式?) 所得到的執行計劃。

看來有時候啟發會過了頭. 造成取得 cost 較高的執行計劃。

例子如下:

Rule base:


Plan
SELECT STATEMENT CHOOSE Cost: 1,484 Bytes: 9,280 Cardinality: 160
 
4 FILTER 
 
3 HASH JOIN Cost: 1,484 Bytes: 9,280 Cardinality: 160 
 
1 TABLE ACCESS FULL A Cost: 1,476 Bytes: 3,840 Cardinality: 160
  
2 TABLE ACCESS FULL B Cost: 7 Bytes: 87,822 Cardinality: 2,583

First_Rows:


Plan
SELECT STATEMENT FIRST_ROWS Cost: 7,121 Bytes: 11,426 Cardinality: 197
 
6 FILTER
 
5 TABLE ACCESS BY INDEX ROWID TABLE B Cost: 3 Bytes: 24 Cardinality: 1
 
4 NESTED LOOPS Cost: 7,121 Bytes: 11,426 Cardinality: 197
  
2 TABLE ACCESS BY INDEX ROWID TABLE A Cost: 626 Bytes: 93,262 Cardinality: 2,743
 
1 INDEX RANGE SCAN INDEX (UNIQUE) IndexA Cost: 8 Cardinality: 2,743
 
3 INDEX RANGE SCAN INDEX (UNIQUE) IndexB Cost: 2 Cardinality: 1

First_Rows_n:


Plan
SELECT STATEMENT FIRST_ROWS Cost: 334 Bytes: 580 Cardinality: 10
 
5 FILTER 
 
4 TABLE ACCESS BY INDEX ROWID TABLE B Cost: 3 Bytes: 24 Cardinality: 1
 
 3 NESTED LOOPS Cost: 334 Bytes: 580 Cardinality: 10
 
 1 TABLE ACCESS FULL TABLE A Cost: 2 Bytes: 4,760 Cardinality: 140
 
 2 INDEX RANGE SCAN INDEX (UNIQUE) IndexB Cost: 2 Cardinality: 1

13.2.1 OPTIMIZER_MODE Initialization Parameter

The OPTIMIZER_MODE initialization parameter establishes the default behavior for choosing an optimization approach for the instance. The possible values and description are listed in Table 13-2.

Table 13-2 OPTIMIZER_MODE Initialization Parameter Values

Value

Description

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.

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.

FIRST_ROWS

The optimizer uses a mix of cost and heuristics to find a best plan for fast delivery of the first few rows.

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.

附上 oracle document 擷錄內容

http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/optimops.htm#PFGRF10102

參考其它查到的資訊

http://yangtingkun.itpub.net/post/468/228323

回首頁