sql tuning—分析10与11g在处理rownum及rowid的性能区别—part1

  • wisdomone1

    2015-06-23 16:43:24
  • Oracle
  • 原创

测试结论

1,11g中rownum分页，不同分页情况，消耗的逻辑读相同
2,11g中rowid，不同分页情况下，消耗的逻辑读相同
3,附上，rowid同比rownum消耗的逻辑读要高
4,基于rowid访问表记录时，11g比10g算法作了优化，即如果不同记录的rowid同属同一个数据块，仅算1次逻辑读，
   而10G却无法区分这点
5,一定要强化自己知识点全想联系的能力，方可真正深入理解ORACLE数据库版本变化的规律及设计思想
6,本测试仍不能模拟全部的可能，仅适用于测试场景，欢迎大家交流

测试明细


oracle 11.2.0.1


SQL> select * from v$version where rownum=1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

SQL> create table t_rownum(a int,b int);

Table created.

SQL> insert into t_rownum select level,level from dual connect by level<=10000;

10000 rows created.

SQL> commit;

Commit complete.

SQL> create index idx_t_rownum on t_rownum(a);

Index created.

SQL> exec dbms_stats.gather_table_stats(user,'t_rownum',cascade=>true);

PL/SQL procedure successfully completed.

SQL> select a from (select rn,a from (select rownum rn,a from t_rownum order by a) where rn<=1000) where rn>=900;

101 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 917663930

---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 253K| 8 (13)| 00:00:01 |
|* 1 | VIEW | | 10000 | 253K| 8 (13)| 00:00:01 |
| 2 | SORT ORDER BY | | 10000 | 40000 | 8 (13)| 00:00:01 |
| 3 | COUNT | | | | | |
| 4 | TABLE ACCESS FULL| T_ROWNUM | 10000 | 40000 | 7 (0)| 00:00:01 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("RN"<=1000 AND "RN">=900)


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
23 consistent gets
0 physical reads
0 redo size
2240 bytes sent via SQL*Net to client
585 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
101 rows processed


SQL> select a from (select rn,a from (select rownum rn,a from t_rownum order by a) where rn<=2000) where rn>=1900;

101 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 917663930

---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 253K| 8 (13)| 00:00:01 |
|* 1 | VIEW | | 10000 | 253K| 8 (13)| 00:00:01 |
| 2 | SORT ORDER BY | | 10000 | 40000 | 8 (13)| 00:00:01 |
| 3 | COUNT | | | | | |
| 4 | TABLE ACCESS FULL| T_ROWNUM | 10000 | 40000 | 7 (0)| 00:00:01 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("RN"<=2000 AND "RN">=1900)


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
23 consistent gets
0 physical reads
0 redo size
2240 bytes sent via SQL*Net to client
585 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
101 rows processed

SQL> select a from (select rn,a from (select rownum rn,a from t_rownum order by a) where rn<=10000) where rn>=9900;

101 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 917663930

---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 253K| 8 (13)| 00:00:01 |
|* 1 | VIEW | | 10000 | 253K| 8 (13)| 00:00:01 |
| 2 | SORT ORDER BY | | 10000 | 40000 | 8 (13)| 00:00:01 |
| 3 | COUNT | | | | | |
| 4 | TABLE ACCESS FULL| T_ROWNUM | 10000 | 40000 | 7 (0)| 00:00:01 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("RN"<=10000 AND "RN">=9900)


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
23 consistent gets
0 physical reads
0 redo size
2240 bytes sent via SQL*Net to client
585 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
101 rows processed


SQL> r
1 select /*+ ordered use_nl(sub,t_rownum) */ t_rownum.a from
2 (select rn,rd
3 from
4 (select rownum rn,
5 rowid rd,a
6 from t_rownum order by a)
7 where rn<=1000) sub,
8 t_rownum
9 where sub.rd=t_rownum.rowid and
10* sub.rn>=900

101 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3443890559

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 322K| 10011 (1)| 00:02:01 |
| 1 | NESTED LOOPS | | 10000 | 322K| 10011 (1)| 00:02:01 |
|* 2 | VIEW | | 10000 | 244K| 8 (13)| 00:00:01 |
| 3 | SORT ORDER BY | | 10000 | 80000 | 8 (13)| 00:00:01 |
| 4 | COUNT | | | | | |
| 5 | TABLE ACCESS FULL | T_ROWNUM | 10000 | 80000 | 7 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY USER ROWID| T_ROWNUM | 1 | 8 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("RN"<=1000 AND "RN">=900)


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
31 consistent gets
0 physical reads
0 redo size
2240 bytes sent via SQL*Net to client
585 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
101 rows processed

SQL> r
1 select /*+ ordered use_nl(sub,t_rownum) */ t_rownum.a from
2 (select rn,rd
3 from
4 (select rownum rn,
5 rowid rd,a
6 from t_rownum order by a)
7 where rn<=2000) sub,
8 t_rownum
9 where sub.rd=t_rownum.rowid and
10* sub.rn>=1900

101 rows selected.
SQL> /

101 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3443890559

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 322K| 10011 (1)| 00:02:01 |
| 1 | NESTED LOOPS | | 10000 | 322K| 10011 (1)| 00:02:01 |
|* 2 | VIEW | | 10000 | 244K| 8 (13)| 00:00:01 |
| 3 | SORT ORDER BY | | 10000 | 80000 | 8 (13)| 00:00:01 |
| 4 | COUNT | | | | | |
| 5 | TABLE ACCESS FULL | T_ROWNUM | 10000 | 80000 | 7 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY USER ROWID| T_ROWNUM | 1 | 8 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("RN"<=2000 AND "RN">=1900)


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
31 consistent gets
0 physical reads
0 redo size
2240 bytes sent via SQL*Net to client
585 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
101 rows processed

SQL>

oracle 10.2.0.5

SQL> select * from v$version where rownum=1;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi

SQL> select a from (select rn,a from (select rownum rn,a from t_rownum order by a) where rn<=1000) where rn>=900;

101 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 917663930

---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10017 | 254K| 8 (25)| 00:00:01 |
|* 1 | VIEW | | 10017 | 254K| 8 (25)| 00:00:01 |
| 2 | SORT ORDER BY | | 10017 | 40068 | 8 (25)| 00:00:01 |
| 3 | COUNT | | | | | |
| 4 | TABLE ACCESS FULL| T_ROWNUM | 10017 | 40068 | 6 (0)| 00:00:01 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("RN"<=1000 AND "RN">=900)


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
23 consistent gets
0 physical reads
0 redo size
2229 bytes sent via SQL*Net to client
558 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
101 rows processed



oracle 11.2.0.1

SQL> insert into t_rowid_diff select level,level from dual connect by level<=2000000;

SQL> commit;

SQL> select rowid,a,dbms_rowid.rowid_block_number(rowid) from t_rowid_diff where DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)=142743 and rownum<=2;

ROWID A DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------ ---------- ------------------------------------
AAAUD+AABAAAi2XAAA 1990787 142743
AAAUD+AABAAAi2XAAB 1990788 142743

SQL> select rowid,a,dbms_rowid.rowid_block_number(rowid) from t_rowid_diff where DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)=142595 and rownum<=2;

ROWID A DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------ ---------- ------------------------------------
AAAUD+AABAAAi0DAAA 1927443 142595
AAAUD+AABAAAi0DAAB 1927444 142595

SQL> select a from t_rowid_diff where rowid in ('AAAUD+AABAAAi2XAAA','AAAUD+AABAAAi2XAAB','AAAUD+AABAAAi0DAAA','AAAUD+AABAAAi0DAAB');


Execution Plan
----------------------------------------------------------
Plan hash value: 1051546041

--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 1 (0)| 00:00:01 |
| 1 | INLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY USER ROWID| T_ROWID_DIFF | 1 | 25 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
601 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed


SQL> set autot off
SQL> select rowid,a,dbms_rowid.rowid_block_number(rowid) from t_rowid_diff where DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)=142595 and rownum<=4;

ROWID A DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------ ---------- ------------------------------------
AAAUD+AABAAAi0DAAA 1927443 142595
AAAUD+AABAAAi0DAAB 1927444 142595
AAAUD+AABAAAi0DAAC 1927445 142595
AAAUD+AABAAAi0DAAD 1927446 142595

SQL> select rowid,a,dbms_rowid.rowid_block_number(rowid) from t_rowid_diff where DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)=142743 and rownum<=4;

ROWID A DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------ ---------- ------------------------------------
AAAUD+AABAAAi2XAAA 1990787 142743
AAAUD+AABAAAi2XAAB 1990788 142743
AAAUD+AABAAAi2XAAC 1990789 142743
AAAUD+AABAAAi2XAAD 1990790 142743


SQL> select a from t_rowid_diff where rowid in ('AAAUD+AABAAAi2XAAA','AAAUD+AABAAAi2XAAB','AAAUD+AABAAAi0DAAA','AAAUD+AABAAAi0DAAB','AAAUD+AABAAAi0DAAC','AAAUD+AABAAAi0DAAD','AAAUD+AABAAAi2XAAC','AAAUD+AABAAAi2XAAD');

8 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1051546041

--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 1 (0)| 00:00:01 |
| 1 | INLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY USER ROWID| T_ROWID_DIFF | 1 | 25 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
645 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
8 rows processed

oracle 10.2.0.5
SQL> insert into t_rowid_diff select level,level from dual connect by level<=2000000;

1000000 rows created.

SQL> commit;

Commit complete.

SQL> select rowid,a,dbms_rowid.rowid_block_number(rowid) from t_rowid_diff where DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) in (69618) and rownum<=2;

ROWID A DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------ ---------- ------------------------------------
AAANN8AABAAAQ/yAAA 1 69618
AAANN8AABAAAQ/yAAB 2 69618

SQL> select rowid,a,dbms_rowid.rowid_block_number(rowid) from t_rowid_diff where DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) in (69619) and rownum<=2;

ROWID A DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------ ---------- ------------------------------------
AAANN8AABAAAQ/zAAA 576 69619
AAANN8AABAAAQ/zAAB 577 69619

SQL> select a from t_rowid_diff where rowid in ('AAANN8AABAAAQ/yAAA','AAANN8AABAAAQ/yAAB','AAANN8AABAAAQ/zAAA','AAANN8AABAAAQ/zAAB');


Execution Plan
----------------------------------------------------------
Plan hash value: 1051546041

--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 1 (0)| 00:00:01 |
| 1 | INLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY USER ROWID| T_ROWID_DIFF | 1 | 25 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
580 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed

SQL> select a from t_rowid_diff where rowid in ('AAANN8AABAAAQ/yAAA','AAANN8AABAAAQ/yAAB','AAANN8AABAAAQ/zAAA','AAANN8AABAAAQ/zAAB','AAANN8AABAAAQ/zAAC','AAANN8AABAAAQ/zAAD','AAANN8AABAAAQ/yAAC','AAANN8AABAAAQ/yAAD');

8 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1051546041

--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 1 (0)| 00:00:01 |
| 1 | INLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY USER ROWID| T_ROWID_DIFF | 1 | 25 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
614 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
8 rows processed

个人简介

8年oracle从业经验，具备丰富的oracle技能，目前在国内北京某专业oracle服务公司从事高级技术顾问。
服务过的客户：
中国电信
中国移动
中国联通
中国电通
国家电网
四川达州商业银行
湖南老百姓大药房
山西省公安厅
中国邮政
北京302医院     
河北廊坊新奥集团公司

 项目经验:
中国电信3G项目AAA系统数据库部署及优化
中国联通CRM数据库性能优化
中国移动10086电商平台数据库部署及优化
湖南老百姓大药房ERR数据库sql优化项目
四川达州商业银行TCBS核心业务系统数据库模型设计和RAC部署及优化
四川达州商业银行TCBS核心业务系统后端批处理存储过程功能模块编写及优化
北京高铁信号监控系统RAC数据库部署及优化
河南宇通客车数据库性能优化
中国电信电商平台核心采购模块表模型设计及优化
中国邮政储蓄系统数据库性能优化及sql优化
北京302医院数据库迁移实施
河北廊坊新奥data guard部署及优化
山西公安厅身份证审计数据库系统故障评估
国家电网上海灾备项目4 node rac+adg 
          贵州移动crm及客服数据库性能优化项目
          贵州移动crm及客服务数据库sql审核项目
          深圳穆迪软件有限公司数据库性能优化项目
联系方式：
手机：18201115468
qq   :   305076427
qq微博: wisdomone1
新浪微博：wisdomone9
qq群：275813900    
itpub博客名称：wisdomone1    http://blog.itpub.net/9240380/
请使用浏览器的分享功能分享到微信等