论Optimizer的工作模式ALL_ROWS&FIRST

优化器是用于分析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系统中使用比较多。

本文转自 leonarding151CTO博客，原文链接：http://blog.51cto.com/leonarding/1088761，如需转载请自行联系原作者