【原創】論Optimizer的工作模式ALL_ROWS&FIRST_ROWS

優化器是oracle用於分析SQL語句和執行物件的一個核心工具，8i之前oracle使用的是RBO（基於規則優化器），9i 10g 11g 已經升級為CBO（基於成本優化器），例如要根據作業系統cpu資源；記憶體資源；磁碟I/O資源；例項引數；表物件；索引物件；列物件等內容綜合計算出不同的成本，對比哪種成本最優從而選擇出最適合的方案。

CBO Optimizer 有2種工作模式：

1. all_rows：這種工作模式要求一次性處理完全部的資料返回給使用者，場合：報表系統，金融系統

2. first_rows(n)：這種工作模式要求把前n條記錄馬上處理完優先返回給使用者，場合：搜尋，論壇，電商推薦，網上購物

下面就用例項來對比一下all_rows和first_rows(n)效能差異

LEO1@LEO1>create table leo1 as select * from dba_objects; 建立leo1表

Table created.

143916 rowscreated.

LEO1@LEO1>insert /*+ parellel */ into leo1 select * from leo1;

287832 rowscreated.

LEO1@LEO1>insert /*+ parellel */ into leo1 select * from leo1;

575664 rowscreated.

LEO1@LEO1>insert /*+ parellel */ into leo1 select * from leo1;

1151328 rowscreated.

LEO1@LEO1>commit;

Commit complete.

LEO1@LEO1>select count(*) from leo1; 插入了230w條記錄

COUNT(*)

----------------

2302656

LEO1@LEO1>create index leo1_type_name_idx on leo1(object_type,object_name);

在leo1表的object_type,object_name欄位上建立複合索引，必須要建立索引，如果沒有索引在進行檢索時就會忽略FIRST_ROWS(n)，而使用ALL_ROWS。

Index created.

LEO1@LEO1>execute dbms_stats.gather_table_stats('LEO1','LEO1',cascade=>true); 收集統計資訊

第一個LEO1指的是使用者名稱

第二個LEO1指的是表名字

第三個cascade指的是表和表中的索引進行級聯分析，如果不加只是單單對錶分析

PL/SQL proceduresuccessfully completed.

LEO1@LEO1> setautotrace on; 啟動執行計劃

LEO1@LEO1>alter session set tracefile_identifier=optimizer; 指定trace檔案識別符號為optimizer

Session altered.

LEO1@LEO1>alter session set sql_trace=true; 追蹤下面sql語句

Session altered.

#######################################################################################

ALL_ROWS工作模式

LEO1@LEO1>select /*+ all_rows */ * from

(select /*+ all_rows*/ l.*,rownum from

(select /*+ all_rows */ object_id,object_name,object_type,ownerfrom leo1 where object_type='TABLE' order by object_name) l

where rownum<=10)

whererownum>=1; 2 3 4 5

條件查詢10條記錄，使用ALL_ROWS模式

OBJECT_ID OBJECT_NAME OBJECT_TYPE OWNER ROWNUM

-------------------------------------------------- --------------- ------ ----------

73465 A TABLE LEO1 1

73465 A TABLE LEO1 2

73465 A TABLE LEO1 3

73465 A TABLE LEO1 4

73465 A TABLE LEO1 5

73465 A TABLE LEO1 6

73465 A TABLE LEO1 7

73465 A TABLE LEO1 8

73465 A TABLE LEO1 9

73465 A TABLE LEO1 10

10 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value:1112449198

-------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 10 | 1200 | | 10106 (1)| 00:02:02 |

| 1| COUNT | | | | | | |

|* 2 | FILTER | | | | | | |

| 3 | VIEW | | 10 | 1200 | | 10106 (1)| 00:02:02 |

|* 4 | COUNT STOPKEY | | | | | | |

| 5| VIEW | | 85990 | 8985K| | 10106 (1)| 00:02:02 |

|* 6 | SORT ORDER BY STOPKEY| |85990 | 3778K| 5072K| 10106 (1)| 00:02:02 |

|* 7 | TABLE ACCESS FULL | LEO1 | 85990 | 3778K| | 9126 (1)| 00:01:50 |

-------------------------------------------------------------------------------------------

採用了全表掃描方式訪問資料

PredicateInformation (identified by operation id):

---------------------------------------------------

2 - filter(ROWNUM>=1)

4 - filter(ROWNUM<=10)

6 - filter(ROWNUM<=10)

7 - filter("OBJECT_TYPE"='TABLE')

Statistics

----------------------------------------------------------

106 recursive calls

0 db block gets

32818 consistent gets 32818個塊一致性讀，因為需要處理完所有的資料才返回給使用者

32793 physical reads

0 redo size

962 bytes sent via SQL*Net to client

524 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

4 sorts (memory)

0 sorts (disk)

10 rows processed

TRACE檔案的內容

********************************************************************************

select /*+all_rows */ * from

(select /*+ all_rows */ l.*,rownum from

(select /*+ all_rows */object_id,object_name,object_type,owner from leo1 where object_type='TABLE'order by object_name) l

where rownum<=10)

where rownum>=1

call count cpu elapsed disk query current rows

------------- -------- ---------- -------------------- ---------- ----------------------------------------------------------

Parse 1 0.00 0.00 0 0 0 0

Execute 1 0.00 0.00 0 0 0 0

Fetch 2 0.33 0.51 32793 32804 0 10

------------- -------- ---------- -------------------- ---------- ----------------------------------------------------------

total 4 0.33 0.51 32793 32804 0 10

Misses in librarycache during parse: 0

Optimizer mode: ALL_ROWS 模式正確，一致性讀與上面一樣

Parsing user id:85

Rows Row Source Operation

------- ---------------------------------------------------

10 COUNT (cr=32804 pr=32793 pw=0time=0 us)

10 FILTER (cr=32804 pr=32793 pw=0time=0 us)

10 VIEW (cr=32804 pr=32793 pw=0time=198 us cost=10106 size=1200 card=10)

10 COUNT STOPKEY (cr=32804 pr=32793 pw=0 time=72 us)

10 VIEW (cr=32804 pr=32793 pw=0time=0 us cost=10106 size=9200930 card=85990)

10 SORT ORDER BY STOPKEY (cr=32804 pr=32793 pw=0 time=0 us cost=10106size=3869550 card=85990)

89792 TABLE ACCESS FULL LEO1 (cr=32804 pr=32793 pw=0 time=2061611 us cost=9126size=3869550 card=85990)

********************************************************************************

#######################################################################################

FIRST_ROWS工作模式

LEO1@LEO1>select /*+ first_rows(10) */ * from

(select /*+ first_rows(10)*/ l.*,rownum from

(select /*+ first_rows(10) */object_id,object_name,object_type,owner from leo1 where object_type='TABLE'order by object_name) l

where rownum<=10)

whererownum>=1; 2 3 4 5

條件查詢10條記錄，使用FIRST_ROWS(n)模式

OBJECT_ID OBJECT_NAME OBJECT_TYPE OWNER ROWNUM

-------------------------------------------------- --------------- ------ ----------

73465 A TABLE LEO1 1

73465 A TABLE LEO1 2

73465 A TABLE LEO1 3

73465 A TABLE LEO1 4

73465 A TABLE LEO1 5

73465 A TABLE LEO1 6

73465 A TABLE LEO1 7

73465 A TABLE LEO1 8

73465 A TABLE LEO1 9

73465 A TABLE LEO1 10

10 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value:1323255736

-------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 10 | 1200 | 14 (0)| 00:00:01 |

| 1 | COUNT | | | | | |

|* 2 | FILTER | | | | | |

| 3 | VIEW | | 10 | 1200 | 14 (0)| 00:00:01 |

|* 4 | COUNT STOPKEY | | | | | |

| 5 | VIEW | | 10| 1070 | 14 (0)| 00:00:01 |

| 6 | TABLE ACCESS BY INDEX ROWID| LEO1 | 10 | 450 | 14 (0)| 00:00:01 |

|* 7 | INDEX RANGE SCAN |LEO1_TYPE_NAME_IDX | | | 3 (0)| 00:00:01 |

-------------------------------------------------------------------------------------------------------

採用了索引掃描方式訪問資料，索引比全表掃描要快很多

PredicateInformation (identified by operation id):

---------------------------------------------------

2 - filter(ROWNUM>=1)

4 - filter(ROWNUM<=10)

7 - access("OBJECT_TYPE"='TABLE')

Statistics

----------------------------------------------------------

1 recursive calls

0 db block gets

14 consistent gets 14個塊一致性讀，因為只需要優先返回前10條記錄即可

0 physical reads

0 redo size

962 bytes sent via SQL*Net to client

524 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

10 rows processed

TRACE檔案的內容

********************************************************************************

select /*+first_rows(10) */ * from

(select /*+ first_rows(10) */ l.*,rownumfrom

(select /*+ first_rows(10) */object_id,object_name,object_type,owner from leo1 where object_type='TABLE'order by object_name) l

where rownum<=10)

where rownum>=1

call count cpu elapsed disk query current rows

------------- -------- ---------- ---------- -------------------- -------------------------------------------------------

Parse 1 0.00 0.00 0 0 0 0

Execute 1 0.00 0.00 0 0 0 0

Fetch 2 0.00 0.00 0 14 0 10

------------- -------- ---------- -------------------- ---------- -------------------------------------------------------

total 4 0.00 0.00 0 14 0 10

Misses in librarycache during parse: 1

Optimizer mode: FIRST_ROWS

Parsing user id:85

Rows Row Source Operation

------- ---------------------------------------------------

10 COUNT (cr=14 pr=0 pw=0 time=0 us)

10 FILTER (cr=14 pr=0 pw=0 time=0us)

10 VIEW (cr=14 pr=0 pw=0 time=360 uscost=14 size=1200 card=10)

10 COUNT STOPKEY (cr=14 pr=0 pw=0 time=261 us)

10 VIEW (cr=14 pr=0 pw=0 time=0 uscost=14 size=1070 card=10)

10 TABLE ACCESS BY INDEX ROWID LEO1 (cr=14 pr=0 pw=0 time=0 us cost=14size=450 card=10)

10 INDEX RANGE SCAN LEO1_TYPE_NAME_IDX (cr=4 pr=0 pw=0 time=0 us cost=3size=0 card=0)(object id 73578)

********************************************************************************

LEO1@LEO1>alter session set sql_trace=false; 關閉sql的trace功能

Session altered.

LEO1@LEO1> setautotrace off; 關閉執行計劃

[oracle@leonarding1trace]$ pwd

/u01/app/oracle/diag/rdbms/leo1/LEO1/trace

[oracle@leonarding1trace]$ tkprof LEO1_ora_12962_OPTIMIZER.trc optimizer1.log sys=no

對trace檔案格式化，不輸出sys使用者trace資訊（例如遞迴語句的資訊）

TKPROF: Release11.2.0.1.0 - Development on Thu Dec 13 20:40:38 2012

-rw-r--r-- 1oracle oinstall 7671 Dec 13 20:19optimizer1.log

[oracle@leonarding1trace]$ ll -lrt

-rw-r--r-- 1oracle oinstall 27793 Dec 13 20:40optimizer1.log

[oracle@leonarding1trace]$ vim optimizer1.log 檢視trace檔案內容（放在上面了）

小結：2條SQL返回相同的記錄，但FIRST_ROWS要比ALL_ROWS效率高很多，因為為了最快速度返回需要的資料，只進行了14個一致性讀，後面的資料還沒有處理完，前面的資料就返回給使用者了。而ALL_ROWS需要全表掃描所有資料塊才返回結果。ALL_ROWS在OLAP系統中使用比較多。

論Optimizer的工作模式ALL_ROWS&FIRST_ROWS.pdf (151.22 KB, 下載次數: 0, 售價: 1 金子)

Leonarding
2012.12.12
天津&winter
分享技術~成就夢想
Blog：www.leonarding.com

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26686207/viewspace-751067/，如需轉載，請註明出處，否則將追究法律責任。

【原創】論Optimizer的工作模式ALL_ROWS&FIRST_ROWS

相關文章