/*********************************************************

*

* 标题：对于两种分页方法的性能分析详细步骤

* 作者：***

* 时间：2013-06-01 14:10

* 环境：linux Redhat 5.4（64位） &&& Oracle 10.2.0.5（64位）

* 描述：在测试服务器上建立大表，通过查看两条语句所消耗的时间和IO，来分析其性能及各自的利弊。从而达到在具体的环境下，使用相应的语法。

*

*********************************************************/

*****************************准备工作*****************************

1.创建测试表

SQL> create table test_big as select * from dba_objects;

2.准备测试数据

SQL> insert into test_big select * from test_big;

SQL> commit;

SQL> select count(*) from test_big;

COUNT(*)

----------

3226560

3.测试，提取从1条到100条数据。

*****************************第一种情况*****************************

SQL> select * from(

2 select rownum rn,t.OWNER,t.OBJECT_NAME,t.OBJECT_ID from test_big t

3 )where rn >= 1 and rn <= 100;

100 rows selected.

Elapsed: 00:00:18.12

Execution Plan

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

Plan hash value: 757132106

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

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

| 0 | SELECT STATEMENT | | 2563K| 266M| 9894 (2)| 00:01:59 |

|* 1 | VIEW | | 2563K| 266M| 9894 (2)| 00:01:59 |

| 2 | COUNT | | | | | |

| 3 | TABLE ACCESS FULL| TEST_BIG | 2563K| 234M| 9894 (2)| 00:01:59 |

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

Predicate Information (identified by operation id):

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

1 - filter("RN"<=100 AND "RN">=1)

Note

-----

- dynamic sampling used for this statement

Statistics

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

8 recursive calls

0 db block gets

44636 consistent gets

33850 physical reads

0 redo size

3829 bytes sent via SQL*Net to client

558 bytes received via SQL*Net from client

8 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

100 rows processed

SQL>

*****************************第二种情况*****************************

SQL> alter system flush shared_pool;--清空共享池

System altered.

SQL> alter system flush buffer_cache;--清空数据库缓冲区，都是为了实验需要

System altered.

SQL>

SQL> select * from(

2 select rownum rn,t.OWNER,t.OBJECT_NAME,t.OBJECT_ID from test_big t

3 where rownum <= 100)

4 where rn >= 1;

100 rows selected.

Elapsed: 00:00:02.95

Execution Plan

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

Plan hash value: 3779742634

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

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

| 0 | SELECT STATEMENT | | 100 | 10900 | 26 (93)| 00:00:01 |

|* 1 | VIEW | | 100 | 10900 | 26 (93)| 00:00:01 |

|* 2 | COUNT STOPKEY | | | | | |

| 3 | TABLE ACCESS FULL| TEST_BIG | 2563K| 234M| 26 (93)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

1 - filter("RN">=1)

2 - filter(ROWNUM<=100)

Note

-----

- dynamic sampling used for this statement

Statistics

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

348 recursive calls

0 db block gets

175 consistent gets

165 physical reads

0 redo size

3829 bytes sent via SQL*Net to client

558 bytes received via SQL*Net from client

8 SQL*Net roundtrips to/from client

5 sorts (memory)

0 sorts (disk)

100 rows processed

SQL>

分析:

对比这两种写法，显然第二种情况比第一种一致性读（数据请求总数在回滚段buffer中的数据一致性读所需要的数据块）大大减小，主要是把rownum引入到了第二层，注意执行计划中的stopkey，它是专门为了提取top n 的需求优化的。所以绝大多数的情况下，第二个查询的效率比第一个高得多。

这是由于CBO优化模式下，Oracle可以将外层的查询条件推到内层查询中，以提高内层查询的执行效率。对于第一个查询语句，第二层的查询条件WHERE ROWNUM <= 40就可以被Oracle推入到内层查询中，这样Oracle查询的结果一旦超过了ROWNUM限制条件，就终止查询将结果返回了。

而第二个查询语句，由于查询条件BETWEEN 21 AND 40是存在于查询的第三层，而Oracle无法将第三层的查询条件推到最内层（即使推到最内层也没有意义，因为最内层查询不知道RN代表什么）。因此，对于第二个查询语句，Oracle最内层返回给中间层的是所有满足条件的数据，而中间层返回给最外层的也是所有数据。数据的过滤在最外层完成，显然这个效率要比第一个查询低得多。

上面分析的查询不仅仅是针对单表的简单查询，对于最内层查询是复杂的多表联合查询或最内层查询包含排序的情况一样有效。

*****************************第3、看句简单的*****************************

SQL> select t.OWNER,t.OBJECT_NAME,t.OBJECT_ID from test_big t where rownum < 5;

Elapsed: 00:00:01.98

Execution Plan

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

Plan hash value: 3665350523

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

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

| 0 | SELECT STATEMENT | | 4 | 384 | 26 (93)| 00:00:01 |

|* 1 | COUNT STOPKEY | | | | | |

| 2 | TABLE ACCESS FULL| TEST_BIG | 2563K| 234M| 26 (93)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

1 - filter(ROWNUM<5)

Note

-----

- dynamic sampling used for this statement

Statistics

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

347 recursive calls

0 db block gets

169 consistent gets

166 physical reads

0 redo size

751 bytes sent via SQL*Net to client

492 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

5 sorts (memory)

0 sorts (disk)

4 rows processed

SQL>

*****************************第4、其他*****************************

可见条件中有rownum存在，就会有stopkey优化，那么越往后分页速度会怎么样呢？

SQL> select * from(

2 select rownum rn,t.OWNER,t.OBJECT_NAME,t.OBJECT_ID from test_big t

3 where rownum <= 3226560)

4 where rn > 3226460;

100 rows selected.

Elapsed: 00:01:11.29

Execution Plan

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

Plan hash value: 3779742634

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

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

| 0 | SELECT STATEMENT | | 2563K| 266M| 9894 (2)| 00:01:59 |

|* 1 | VIEW | | 2563K| 266M| 9894 (2)| 00:01:59 |

|* 2 | COUNT STOPKEY | | | | | |

| 3 | TABLE ACCESS FULL| TEST_BIG | 2563K| 234M| 9894 (2)| 00:01:59 |

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

Predicate Information (identified by operation id):

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

1 - filter("RN">3226460)

2 - filter(ROWNUM<=3226560)

Note

-----

- dynamic sampling used for this statement

Statistics

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

347 recursive calls

0 db block gets

44673 consistent gets

44500 physical reads

0 redo size

5348 bytes sent via SQL*Net to client

558 bytes received via SQL*Net from client

8 SQL*Net roundtrips to/from client

5 sorts (memory)

0 sorts (disk)

100 rows processed

SQL>

总结：

对比第2种情况和第4种情况，二者执行效率相差很大，第4种情况需要40000多个逻辑读，而第2种情况只需要175个逻辑读。观察二者的执行计划可以发现，两个执行计划唯一的区别就是第二个查询在COUNT这步使用了STOPKEY，也就是说，Oracle将ROWNUM <= 20推入到查询内层，当符合查询的条件的记录达到STOPKEY的值，则Oracle结束查询。

因此，可以预见，采用第2种方式，在翻页的开始部分查询速度很快，越到后面，效率越低，当翻到最后一页，效率应该和第一种方式接近。

ITPub博客

Oracle两种分页方法的性能分析

最新文章