213
Oracle優化器的optimizer_mode參數
optimizer_mode參數
optimizer_mode是oracle 11g的一個優化器參數，在某些時候可以影響優化器的行為，是個不可忽視的細節參數。
SQL> show parameter optimizer;
optimizer_capture_sql_plan_baselines boolean FALSE
optimizer_dynamic_sampling integer 2
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
optimizer_secure_view_merging boolean TRUE
optimizer_use_invisible_indexes boolean FALSE
optimizer_use_pending_statistics boolean FALSE
optimizer_use_sql_plan_baselines boolean TRUE
此參數的簡單介紹如下，
oracle優化器在解析sql前先查看這個參數的值。參數optimizer_mode 決定取出多少行後開始向用戶返回第一批數據，當值為all_rows時，獲取完所有的數據後再開始向用戶返回數據。
這麼看來，貌似感覺和執行計劃沒多大關係，但是oracle優化器在選擇執行計劃的時候卻是會考慮這個參數的，而這裏主要體現在btree索引的特性。
Let's make a test
我們來做一個測試。執行一個sql語句，從t1表中取出object_id列為1-3000的數據，結果以object_id列的值排序。測試情況如下。
SQL> show parameter optimizer_mode;
optimizer_mode string ALL_ROWS
SQL> select * from t1 where object_id between 1 and 3000 order by object_id;
2998 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2148421099
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2959 | 283K| | 404 (1)| 00:00:05 |
| 1 | SORT ORDER BY | | 2959 | 283K| 400K| 404 (1)| 00:00:05 |
|* 2 | TABLE ACCESS FULL| T1 | 2959 | 283K| | 336 (1)| 00:00:05 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_ID"<=3000 AND "OBJECT_ID">=1)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1235 consistent gets
0 physical reads
0 redo size
120248 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
2998 rows processed
我們可以看到，當前optimizer_mode的值為all_rows，優化器選擇了全表掃描TABLE ACCESS FULL。那麼我們一般的認識是，數據量總量較小或者查詢量的總量占比較大的情況下，優化器認為全表掃描的性能較索引檢索性能更優。
接下來修改當前會話的optimizer_mode的值為first_rows_10
SQL> alter session set optimizer_mode='first_rows_10';
Session altered.
然後再次測試剛才的SQL語句
SQL> select * from t1 where object_id between 1 and 3000 order by object_id;
2998 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1057374866
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 1176 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 12 | 1176 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I_T1_1 | 2959 | | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID">=1 AND "OBJECT_ID"<=3000)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
53 consistent gets
7 physical reads
0 redo size
276790 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2998 rows processed
呃，，我們發現，調整完這個參數後，優化器立馬就選擇了索引。
那麼，怎麼去解釋呢？
這裏就需要考慮到btree索引的特性了，因為索引的有序性，也就是獲取到的數據是根據索引列排好序的。
因此，當進行全表掃描時，由於列值雜亂，所以必須等到所有的數據全部獲取完畢後才能進行排序，再開始返回第一批數據；而通過索引返回的數據都是有序的，取到第十行時就可以直接返回給用戶。
由於索引的這個小特性，加上optimizer_mode參數對數據操作的影響，間接地影響了優化器的選擇行為。
最後更新：2017-09-30 08:03:59