??? 在CBO的優化模式下，我們可以使用optimizer_mode參數控制優化模式。主要有兩種模式，一種是ALL_ROWS模式，另外一種是FIRST_ROWS模式。

??? ALL_ROWS模式適用場景：希望優化程序給出一種盡快得到全部記錄的執行計劃，目標是增加系統的吞吐量。

??? FIRST_ROWS模式適用場景：希望優化程序給出一種可以迅速的得到第一行的執行計劃，目標是減少系統的響應時間。

??? 兩種模式需要具體場景具體分析，比如常見的Web應用，很少有一次性得到全部記錄的情況，都是分多頁交互的響應操作者，因此默認的ALL_ROWS模式就不太合適了，應該考慮使用FIRST_ROWS模式進行優化。又如，我們想要生成全部數據的報表，那么默認的ALL_ROWS模式就比較的合適。

??? 下面通過實驗來比較all_rows和first_rows對執行計劃的影響：

? 1.實驗環境：

? 操作系統：rhel 5.4 x32

? 數據庫：oracle 11.2.0.1.0

? 2.首先我們創建一個具有dba權限的用戶jack_lin，default_tablespace使用默認的users。

1 SQL> conn /as sysdba 2 Connected. 3 SQL> create user jack_lin identified by jack; 4 User created. 5 SQL> grant dba to jack_lin; 6 Grant succeeded.

? 3.創建該實驗需要用到的一張表。

1 SQL> conn jack_lin/jack; 2 Connected. 3 SQL> create table test(id number,name varchar2(10)); 4 Table created. 5 SQL> insert into test values(100,'aaaa'); 6 1 row created. 7 SQL> insert into test values(200,'bbbb'); 8 1 row created. 9 SQL> insert into test values(300,'cccc'); 10 1 row created. 11 SQL> insert into test values(400,'dddd'); 12 1 row created. 13 SQL> commit; 14 Commit complete.

? 4.在沒有索引的情況比較：

? 首先來看FIRST_ROWS的效果，為了保證CBO執行計劃的準確，我們需要analyze一下表。

1 SQL> alter session set optimizer_mode=first_rows; 2 3 Session altered. 4 5 SQL> analyze table test compute statistics; 6 7 Table analyzed. 8 9 SQL> set autotrace trace exp; 10 SQL> select * from test where name='aaaa'; 11 12 Execution Plan 13 ---------------------------------------------------------- 14 Plan hash value: 1357081020 15 16 -------------------------------------------------------------------------- 17 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 18 -------------------------------------------------------------------------- 19 | 0 | SELECT STATEMENT | | 1 | 6 | 3 (0)| 00:00:01 | 20 |* 1 | TABLE ACCESS FULL| TEST | 1 | 6 | 3 (0)| 00:00:01 | 21 -------------------------------------------------------------------------- 22 23 Predicate Information (identified by operation id): 24 --------------------------------------------------- 25 26 1 - filter("NAME"='aaaa')

? 由于表上沒有索引，所以只有一種選擇，全表掃描。

? 現在再看一下ALL_ROWS的情況：

1 SQL> alter session set optimizer_mode=all_rows; 2 3 Session altered. 4 5 SQL> select * from test where name='aaaa'; 6 7 Execution Plan 8 ---------------------------------------------------------- 9 Plan hash value: 1357081020 10 11 -------------------------------------------------------------------------- 12 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 13 -------------------------------------------------------------------------- 14 | 0 | SELECT STATEMENT | | 1 | 6 | 3 (0)| 00:00:01 | 15 |* 1 | TABLE ACCESS FULL| TEST | 1 | 6 | 3 (0)| 00:00:01 | 16 -------------------------------------------------------------------------- 17 18 Predicate Information (identified by operation id): 19 --------------------------------------------------- 20 21 1 - filter("NAME"='aaaa')

? 通過上面的簡單舉例比較，可以看到在表上沒有索引，當數據量很少，并且值唯一的情況下，兩種模式的效果是一樣的。

? 5.在有索引的情況下比較：

? 創建索引，并執行在FIRST_ROWS的操作

1 SQL> create index ind_test on test(name); 2 3 Index created. 4 5 SQL> analyze index ind_test compute statistics; 6 7 Index analyzed. 8 9 SQL> analyze table test compute statistics; 10 11 Table analyzed. 12 13 SQL> select /*+ first_rows */* from test where name='aaaa'; 14 15 Execution Plan 16 ---------------------------------------------------------- 17 Plan hash value: 3856466897 18 19 ---------------------------------------------------------------------------------------- 20 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 21 ---------------------------------------------------------------------------------------- 22 | 0 | SELECT STATEMENT | | 1 | 6 | 2 (0)| 00:00:01 | 23 | 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 6 | 2 (0)| 00:00:01 | 24 |* 2 | INDEX RANGE SCAN | IND_TEST | 1 | | 1 (0)| 00:00:01 | 25 ---------------------------------------------------------------------------------------- 26 27 Predicate Information (identified by operation id): 28 --------------------------------------------------- 29 30 2 - access("NAME"='aaaa')

?

? 設置成ALL_ROWS的情況：

1 SQL> select /*+ all_rows */ * from test where name='aaaa'; 2 3 Execution Plan 4 ---------------------------------------------------------- 5 Plan hash value: 3856466897 6 7 ---------------------------------------------------------------------------------------- 8 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 9 ---------------------------------------------------------------------------------------- 10 | 0 | SELECT STATEMENT | | 1 | 6 | 2 (0)| 00:00:01 | 11 | 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 6 | 2 (0)| 00:00:01 | 12 |* 2 | INDEX RANGE SCAN | IND_TEST | 1 | | 1 (0)| 00:00:01 | 13 ---------------------------------------------------------------------------------------- 14 15 Predicate Information (identified by operation id): 16 --------------------------------------------------- 17 18 2 - access("NAME"='aaaa')

?? 通過上面的演示可以看到兩種模式都走了索引，目前來看一切正常。

? 6.現在通過insert into test select * from test;往test表中反復插入記錄，注意記錄大部分是重復的，其實只有四條，各占1/4。

1 set autotrace off; 2 SQL> insert into test select * from test; 3 4 16384 rows created. 5 SQL> analyze table test compute statistics; 6 7 Table analyzed. 8 SQL> analyze index ind_test compute statistics; 9 10 Index analyzed. 11 12 SQL> alter session set optimizer_mode=first_rows; 13 14 Session altered. 15 16 SQL> set autotrace trace exp; 17 SQL> select * from test where name='aaaa'; 18 19 Execution Plan 20 ---------------------------------------------------------- 21 Plan hash value: 3856466897 22 23 ---------------------------------------------------------------------------------------- 24 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 25 ---------------------------------------------------------------------------------------- 26 | 0 | SELECT STATEMENT | | 8192 | 49152 | 87 (0)| 00:00:02 | 27 | 1 | TABLE ACCESS BY INDEX ROWID| TEST | 8192 | 49152 | 87 (0)| 00:00:02 | 28 |* 2 | INDEX RANGE SCAN | IND_TEST | 8192 | | 28 (0)| 00:00:01 | 29 ---------------------------------------------------------------------------------------- 30 31 Predicate Information (identified by operation id): 32 --------------------------------------------------- 33 34 2 - access("NAME"='aaaa') 35 36 SQL> alter session set optimizer_mode=all_rows; 37 38 Session altered. 39 40 SQL> select * from test where name='aaaa'; 41 42 Execution Plan 43 ---------------------------------------------------------- 44 Plan hash value: 1357081020 45 46 -------------------------------------------------------------------------- 47 | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 48 -------------------------------------------------------------------------- 49 | 0 | SELECT STATEMENT | | 8192 | 49152 | 19 (0)| 00:00:01 | 50 |* 1 | TABLE ACCESS FULL| TEST | 8192 | 49152 | 19 (0)| 00:00:01 | 51 -------------------------------------------------------------------------- 52 53 Predicate Information (identified by operation id): 54 --------------------------------------------------- 55 56 1 - filter("NAME"='aaaa')

? 這時我們看到FIRST_ROWS走了索引，就本例而言，這顯然不是一種理想的結果，而ALL_ROWS走了全表掃描，我們可以看到成本明顯更低。

? 參考一下Oracle 10g官方文檔關于optimizer_mode參數的描述

OPTIMIZER_MODE

PropertyDescription

Parameter type	String
Syntax	OPTIMIZER_MODE = { first_rows_[1 | 10 | 100 | 1000] | first_rows | all_rows }
Default value	all_rows
Modifiable	ALTER SESSION,?ALTER SYSTEM

OPTIMIZER_MODE?establishes the default behavior for choosing an optimization approach for the instance.

Values:

• first_rows_n

  The optimizer uses a cost-based approach and optimizes with a goal of best response time to return the first?n?rows (where?n?= 1, 10, 100, 1000).

• first_rows

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

• all_rows

  The optimizer uses a cost-based approach for all SQL statements in the session and optimizes with a goal of best throughput (minimum resource use to complete the entire statement).

??總結：

? Oracle默認的優化模式并不一定是我們想要的，必須根據自己的系統特定細心的定制。

?

?

?

轉載于:https://www.cnblogs.com/Richardzhu/articles/2814599.html

總結

以上是生活随笔為你收集整理的讨论ALL_ROWS模式和FIRST_ROWS模式的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。