sql tuning—分析10與11g在處理rownum及rowid的效能區別—part1
測試結論
1,11g中rownum分頁，不同分頁情況，消耗的邏輯讀相同
2,11g中rowid，不同分頁情況下，消耗的邏輯讀相同
3,附上，rowid同比rownum消耗的邏輯讀要高
4,基於rowid訪問表記錄時，11g比10g演算法作了最佳化，即如果不同記錄的rowid同屬同一個資料塊，僅算1次邏輯讀，
而10G卻無法區分這點
5,一定要強化自己知識點全想聯絡的能力，方可真正深入理解ORACLE資料庫版本變化的規律及設計思想
6,本測試仍不能模擬全部的可能，僅適用於測試場景，歡迎大家交流
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稽核專案
深圳穆迪軟體有限公司資料庫效能最佳化專案
貴州移動crm及客服資料庫效能最佳化專案
貴州移動crm及客服務資料庫sql稽核專案
深圳穆迪軟體有限公司資料庫效能最佳化專案
聯絡方式：
手機：18201115468
qq : 305076427
qq微博: wisdomone1
新浪微博：wisdomone9
qq群：275813900
itpub部落格名稱：wisdomone1 http://blog.itpub.net/9240380/
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9240380/viewspace-1709354/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【oracle rowid與rownum的使用與區別 】Oracle
- rowid和rownum的區別
- oracle中rownum和rowid的區別Oracle
- [精選] SQL中的IN與NOT IN、EXISTS與NOT EXISTS的區別及效能分析SQL
- 對線面試官：SQL中的IN與NOT IN、EXISTS與NOT EXISTS的區別及效能分析面試SQL
- 【實驗】【SQL】排序與rownum的關係暨利用rownum處理一實際需求SQL排序
- ROWID與ROWNUM的簡介與對比
- oracle sql tuning_in與exists的區別_轉摘OracleSQL
- Oracle中rownum與rowid使用上的問題Oracle
- STS（SQL Tuning Set）匯入匯出過程及錯誤處理SQL
- 10g與11g在處理 >= nvl(:b1,0) and
- http://www.hzoes.com/ rowid與rownum不一樣HTTP
- 10g,11g sql auto tuning 測試SQL
- job處理緩慢的效能問題排查與分析
- 11g alert log中的automatic SQL Tuning及Resource Manager planSQL
- sql tuning advisor和sql access advisor區別SQL
- order by在SQL Server與Oracle中的一個區別SQLServerOracle
- [轉]使用 SAX 處理 XML 文件 和與DOM的區別XML
- C與C++中的異常處理2(part1) (轉)C++
- Automatic SQL Tuning in Oracle Database 11gSQLOracleDatabase
- Python 編碼處理之 str與Unicode的區別與使用PythonUnicode
- 異常處理機制(一)之throw與throws的區別
- MySQL效能分析和優化-part1MySql優化
- oracle 10g 與11g統計資訊區別Oracle 10g
- rownum和rowid，row_number一點應用
- 【SQL】UNION ALL 與UNION 的區別SQL
- disruptor佇列SleepingWaitStrategy與YieldingWaitStrategy處理的區別佇列AI
- 10 文字分析處理命令
- 淺析Oracle（rownum）和Mysql（limit）分頁的區別OracleMySqlMIT
- Redis的配置及與memcached區別Redis
- Oracle 11g 中SQL效能最佳化新特性之SQL效能分析器（SQLPA）OracleSQL
- JavaScript與DropDownList 區別分析JavaScript
- SQL中Having與Where的區別SQL
- SQL join中on與where區別SQL
- SQL truncate 、delete與drop區別SQLdelete
- 單引號在動態SQL中的處理SQL
- PL/SQL中command window與SQL window的區別SQL
- 10G遷移升級到11G使用SPA 分析SQL效能例項SQL