Oracle中rownum對錶的掃描方式效能上的影響深入探究

PiscesCanon發表於2017-06-03
Oracle
前言：
偶然發現rownum上加限定條件後，執行計劃雖然是全表掃描，但是實際上卻是隻掃描了滿足條件的行所在的資料塊，也就是沒有做全表掃描。
問題可以假設性描述為一條select語句的全表掃描，結果是1000行，加上rownum<=500之後，oracle是掃描了前500行所在的塊，還是掃描了1000行後取前500行（前者效能要更好）。
構造資料進行探究。探究過程中影響的因素有
1.引數db_file_multiblock_read_count，關於該參詳情可以讀閱http://blog.itpub.net/30174570/viewspace-2140241/。
2.動態取樣。

作業系統資訊：
  1. [oracle@oracle ~]$ uname -a
  2. Linux oracle.example.com 2.6.32-431.el6.x86_64 #1 SMP Sun Nov 10 22:19:54 EST 2013 x86_64 x86_64 x86_64 GNU/Linux
  3. [oracle@oracle ~]$ lsb_release -a
  4. LSB Version:    :base-4.0-amd64:base-4.0-noarch:core-4.0-amd64:core-4.0-noarch:graphics-4.0-amd64:graphics-4.0-noarch:printing-4.0-amd64:printing-4.0-noarch
  5. Distributor ID: RedHatEnterpriseServer
  6. Description:    Red Hat Enterprise Linux Server release 6.5 (Santiago)
  7. Release:        6.5
  8. Codename:       Santiago

資料庫版本：
  1. SYS@proc> select * from v$version where rownum<=1;

  3. BANNER
  4. --------------------------------------------------------------------------------
  5. Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

構造測試資料：
  1. SYS@proc> drop table test purge;

  3. Table dropped.

  5. SYS@proc> create table test tablespace test as select * from dba_objects where rownum<=1500;

  7. Table created.

  9. SYS@proc> select dbms_rowid.rowid_row_number(rowid) row# from test where rownum<=10;

  11.       ROW#
  12. ----------
  13.          0
  14.          1
  15.          2
  16.          3
  17.          4
  18.          5
  19.          6
  20.          7
  21.          8
  22.          9

  24. 10 rows selected.


  27. SYS@proc> select dbms_rowid.rowid_block_number(rowid) block#,min(rownum),max(rownum) from test group by dbms_rowid.rowid_block_number(rowid) order by dbms_rowid.rowid_block_number(rowid);

  29.     BLOCK# MIN(ROWNUM) MAX(ROWNUM)
  30. ---------- ----------- -----------
  31.        139           1          88
  32.        140          89         171
  33.        141         172         251
  34.        142         252         329
  35.        143         330         407
  36.        144         408         487
  37.        145         488         567
  38.        146         568         646
  39.        147         647         724
  40.        148         725         798
  41.        149         799         873

  43.     BLOCK# MIN(ROWNUM) MAX(ROWNUM)
  44. ---------- ----------- -----------
  45.        150         874         946
  46.        151         947        1022
  47.        161        1023        1104
  48.        162        1105        1179
  49.        163        1180        1261
  50.        164        1262        1342
  51.        165        1343        1418
  52.        166        1419        1496
  53.        167        1497        1500

  55. 20 rows selected.

  57. SYS@proc> select EXTENT_ID,FILE_ID,BLOCK_ID,BLOCKS from dba_extents where owner='SYS' and SEGMENT_NAME='TEST';

  59.  EXTENT_ID    FILE_ID   BLOCK_ID     BLOCKS
  60. ---------- ---------- ---------- ----------
  61.          0          6        136          8    --136 137 138 139 140 141 142 143
  62.          1          6        144          8    --144 145 146 147 148 149 150 151
  63.          2          6        160          8    --160 161 162 163 164 165 166 167

實驗過程：
說明：由於該過程是做的全表掃描下對rownum加限定詞後，掃描表的塊是否發生減少，為了避免db_file_multiblock_read_count的影響，將其值設定為1。
  1. SYS@proc> show parameter multiblock

  3. NAME                                 TYPE        VALUE
  4. ------------------------------------ ----------- ------------------------------
  5. db_file_multiblock_read_count        integer     8

  7. SYS@proc> alter system set db_file_multiblock_read_count=1;

  9. System altered.

  1. SYS@proc> alter system flush buffer_cache;

  3. System altered.

  5. SYS@proc> alter system flush shared_pool;

  7. System altered.

  9. SYS@proc> select file#,dbablk,state from x$bh where obj=(select data_object_id from dba_objects where owner='SYS' and object_name='TEST') and state<>0 order by dbablk;

  11. no rows selected

  13. SYS@proc> select count(*) from test where rownum<=171;    --只讀前兩個塊

  15.   COUNT(*)
  16. ----------
  17.        171

  19. SYS@proc> set pagesize 9999
  20. SYS@proc> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));

  22. PLAN_TABLE_OUTPUT
  23. ------------------------------------------------------------------------------------
  24. SQL_ID  5h6qpq1adpkh8, child number 0
  25. -------------------------------------
  26. select count(*) from test where rownum<=171

  28. Plan hash value: 827909369

  30. --------------------------------------------------------------------
  31. | Id  | Operation           | Name | Rows | Cost (%CPU)| Time      |
  32. --------------------------------------------------------------------
  33. |   0 | SELECT STATEMENT    |      |      |    25 (100)|           |
  34. |   1 |  SORT AGGREGATE     |      |    1 |            |           |
  35. |*  2 |   COUNT STOPKEY     |      |      |            |           |
  36. |   3 |    TABLE ACCESS FULL| TEST | 1500 |    25 (0)  | 00:00:01  |
  37. --------------------------------------------------------------------
  38. ...省略部分內容...

  40. 49 rows selected.

  42. SYS@proc> select file#,dbablk,state from x$bh where obj=(select data_object_id from dba_objects where owner='SYS' and object_name='TEST') and state<>0 order by dbablk;

  44.      FILE#     DBABLK      STATE
  45. ---------- ---------- ----------
  46.          6        138          3
  47.          6        138          3
  48.          6        138          1
  49.          6        139          1
  50.          6        140          1
  51.          6        141          1
  52.          6        142          1
  53.          6        143          1
  54.          6        144          1
  55.          6        145          1
  56.          6        146          1

  58.      FILE#     DBABLK      STATE
  59. ---------- ---------- ----------
  60.          6        147          1
  61.          6        148          1
  62.          6        149          1
  63.          6        150          1
  64.          6        151          1
  65.          6        161          1
  66.          6        162          1
  67.          6        163          1
  68.          6        164          1
  69.          6        165          1
  70.          6        166          1

  72.      FILE#     DBABLK      STATE
  73. ---------- ---------- ----------
  74.          6        167          1

  76. 23 rows selected.
上述步驟分析：
select count(*) from test where rownum<=171只讀取了前兩個塊的內容，檢視該語句執行計劃，從TABLE ACCESS FULL可以看出是全表掃描，而最後檢視x$bh中sys.test表的情況可以看出，表的所有塊也被讀進去buffer cache中，符合全表掃描。
不過這裡有一個很奇怪的現象，重新清空buffer cache之後，繼續剛剛的動作，情況就不同了。
  1. SYS@proc> alter system flush buffer_cache;

  3. System altered.

  5. SYS@proc> select file#,dbablk,state from x$bh where obj=(select data_object_id from dba_objects where owner='SYS' and object_name='TEST') and state<>0 order by dbablk;

  7. no rows selected

  9. SYS@proc> select count(*) from test where rownum<=171;

  11.   COUNT(*)
  12. ----------
  13.        171

  15. SYS@proc> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));

  17. PLAN_TABLE_OUTPUT
  18. ----------------------------------------------------------------------------------
  19. SQL_ID  5h6qpq1adpkh8, child number 0
  20. -------------------------------------
  21. select count(*) from test where rownum<=171

  23. Plan hash value: 827909369

  25. --------------------------------------------------------------------
  26. | Id | Operation           | Name | Rows | Cost (%CPU)| Time       |
  27. --------------------------------------------------------------------
  28. |  0 | SELECT STATEMENT    |      |      |    25 (100)|            |
  29. |  1 |  SORT AGGREGATE     |      |    1 |            |            |
  30. |* 2 |   COUNT STOPKEY     |      |      |            |            |
  31. |  3 |    TABLE ACCESS FULL| TEST | 1500 |    25 (0)  | 00:00:01   |
  32. --------------------------------------------------------------------
  33. ...省略部分內容...

  35. SYS@proc> select file#,dbablk,state from x$bh where obj=(select data_object_id from dba_objects where owner='SYS' and object_name='TEST') and state<>0 order by dbablk;

  37.      FILE#     DBABLK      STATE
  38. ---------- ---------- ----------
  39.          6        138          1
  40.          6        139          1
  41.          6        140          1
上述步驟分析：
但從結果上看，執行計劃是全表掃描，但是讀進buffer cache的塊卻只有資訊塊138號和資料塊139、140。
說明rownum<=N（N為正整數）的時候，sql語句只要找出前N條滿足條件的結果後，便不再進行讀取，也就是“前言”中假設的那個問題的前者猜測。
只不過這裡執行計劃並不準確體現出來。

但是對比前邊實驗，為什麼一開始會全部對錶的塊進行讀取，第二次開始才讀取含有滿足條件的塊？？
實驗可以發現，若是對shared pool和buffer poll同時進行清空的情況，執行select count(*) from test where rownum<=171語句之後，又變成表的所有塊也被讀進去buffer cache中的情況。
看的出來，是否清空share pool是關鍵。

懷著疑問，繼續做實驗。接下去清空share pool，做10046，執行select count(*) from test where rownum<=171。
  1. SYS@proc> alter system flush shared_pool;

  3. System altered.

  5. SYS@proc> alter system flush buffer_cache;

  7. System altered.

  9. SYS@proc> select file#,dbablk,state from x$bh where obj=(select data_object_id from dba_objects where owner='SYS' and object_name='TEST') and state<>0 order by dbablk;

  11. no rows selected

  13. SYS@proc> alter session set events '10046 trace name context forever,level 12';

  15. Session altered.

  17. SYS@proc> select count(*) from test where rownum<=171;

  19.   COUNT(*)
  20. ----------
  21.        171

  23. SYS@proc> alter session set events '10046 trace name context off';

  25. Session altered.

  27. SYS@proc> select file#,dbablk,state from x$bh where obj=(select data_object_id from dba_objects where owner='SYS' and object_name='TEST') and state<>0 order by dbablk;

  29.      FILE#     DBABLK      STATE
  30. ---------- ---------- ----------
  31.          6        138          3
  32.          6        138          3
  33.          6        138          1
  34.          6        139          1
  35.          6        140          1
  36.          6        141          1
  37.          6        142          1
  38.          6        143          1
  39.          6        144          1
  40.          6        145          1
  41.          6        146          1

  43.      FILE#     DBABLK      STATE
  44. ---------- ---------- ----------
  45.          6        147          1
  46.          6        148          1
  47.          6        149          1
  48.          6        150          1
  49.          6        151          1
  50.          6        161          1
  51.          6        162          1
  52.          6        163          1
  53.          6        164          1
  54.          6        165          1
  55.          6        166          1

  57.      FILE#     DBABLK      STATE
  58. ---------- ---------- ----------
  59.          6        167          1

  61. 23 rows selected.

  63. SYS@proc> select value from v$diag_info where name like '%De%';

  65. VALUE
  66. --------------------------------------------------------------------
  67. /u01/app/oracle/diag/rdbms/proc/proc/trace/proc_ora_3950.trc
檢視trace檔案，部分內容如下：
  1. PARSING IN CURSOR #140571851573816 len=337 dep=1 uid=0 oct=3 lid=0 tim=1482094819882124 hv=3345277572 ad='7eceddb0' sqlid='baj7tjm3q9sn4'
  2. SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),0), NVL(SUM(C2),0) FROM (SELECT /*+ NO_PARALLEL("TEST") FULL("TEST") NO_PARALLEL_INDEX("TEST") */ 1 AS C1, 1 AS C2 FROM "SYS"."TEST" "TEST") SAMPLESUB
  3. END OF STMT
  4. PARSE #140571851573816:c=1000,e=946,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,plh=1950795681,tim=1482094819882123
  5. EXEC #140571851573816:c=0,e=13,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=1950795681,tim=1482094819882174
  6. WAIT #140571851573816: nam='db file sequential read' ela= 11 file#=6 block#=138 blocks=1 obj#=89299 tim=1482094819882200
  7. WAIT #140571851573816: nam='db file sequential read' ela= 11 file#=6 block#=139 blocks=1 obj#=89299 tim=1482094819882256
  8. WAIT #140571851573816: nam='db file sequential read' ela= 7 file#=6 block#=140 blocks=1 obj#=89299 tim=1482094819882284
  9. WAIT #140571851573816: nam='db file sequential read' ela= 6 file#=6 block#=141 blocks=1 obj#=89299 tim=1482094819882305
  10. WAIT #140571851573816: nam='db file sequential read' ela= 11 file#=6 block#=142 blocks=1 obj#=89299 tim=1482094819882360
  11. WAIT #140571851573816: nam='db file sequential read' ela= 7 file#=6 block#=143 blocks=1 obj#=89299 tim=1482094819882397
  12. WAIT #140571851573816: nam='db file sequential read' ela= 7 file#=6 block#=144 blocks=1 obj#=89299 tim=1482094819882420
  13. WAIT #140571851573816: nam='db file sequential read' ela= 5 file#=6 block#=145 blocks=1 obj#=89299 tim=1482094819882439
  14. WAIT #140571851573816: nam='db file sequential read' ela= 6 file#=6 block#=146 blocks=1 obj#=89299 tim=1482094819882462
  15. WAIT #140571851573816: nam='db file sequential read' ela= 10 file#=6 block#=147 blocks=1 obj#=89299 tim=1482094819882569
  16. WAIT #140571851573816: nam='db file sequential read' ela= 6 file#=6 block#=148 blocks=1 obj#=89299 tim=1482094819882602
  17. WAIT #140571851573816: nam='db file sequential read' ela= 9 file#=6 block#=149 blocks=1 obj#=89299 tim=1482094819882624
  18. WAIT #140571851573816: nam='db file sequential read' ela= 6 file#=6 block#=150 blocks=1 obj#=89299 tim=1482094819882647
  19. WAIT #140571851573816: nam='db file sequential read' ela= 5 file#=6 block#=151 blocks=1 obj#=89299 tim=1482094819882667
  20. WAIT #140571851573816: nam='db file sequential read' ela= 6 file#=6 block#=161 blocks=1 obj#=89299 tim=1482094819882687
  21. WAIT #140571851573816: nam='db file sequential read' ela= 6 file#=6 block#=162 blocks=1 obj#=89299 tim=1482094819882705
  22. WAIT #140571851573816: nam='db file sequential read' ela= 5 file#=6 block#=163 blocks=1 obj#=89299 tim=1482094819882723
  23. WAIT #140571851573816: nam='db file sequential read' ela= 5 file#=6 block#=164 blocks=1 obj#=89299 tim=1482094819882741
  24. WAIT #140571851573816: nam='db file sequential read' ela= 7 file#=6 block#=165 blocks=1 obj#=89299 tim=1482094819882761
  25. WAIT #140571851573816: nam='db file sequential read' ela= 56 file#=6 block#=166 blocks=1 obj#=89299 tim=1482094819882829
  26. WAIT #140571851573816: nam='db file sequential read' ela= 8 file#=6 block#=167 blocks=1 obj#=89299 tim=1482094819882868
  27. FETCH #140571851573816:c=1000,e=702,p=21,cr=23,cu=0,mis=0,r=1,dep=1,og=1,plh=1950795681,tim=1482094819882883
  28. STAT #140571851573816 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=23 pr=21 pw=0 time=701 us)'
  29. STAT #140571851573816 id=2 cnt=1500 pid=1 pos=1 obj=89299 op='TABLE ACCESS FULL TEST (cr=23 pr=21 pw=0 time=335 us cost=25 size=0 card=1961)'
  30. CLOSE #140571851573816:c=0,e=5,dep=1,type=1,tim=1482094819882937
  31. PARSE #140571851573816:c=0,e=12,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=1950795681,tim=1482094819883042
  32. EXEC #140571851573816:c=0,e=11,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=1950795681,tim=1482094819883066
  33. FETCH #140571851573816:c=0,e=129,p=0,cr=23,cu=0,mis=0,r=1,dep=1,og=1,plh=1950795681,tim=1482094819883204
  34. CLOSE #140571851573816:c=0,e=2,dep=1,type=3,tim=1482094819883233
  35. =====================
  36. PARSING IN CURSOR #140571852646608 len=44 dep=0 uid=0 oct=3 lid=0 tim=1482094819883659 hv=1297676880 ad='7ecc7130' sqlid='82jwkqt6pjykh'
  37.  select count(*) from test where rownum<=171
  38. END OF STMT
  39. PARSE #140571852646608:c=44993,e=46883,p=26,cr=68,cu=0,mis=1,r=0,dep=0,og=1,plh=827909369,tim=1482094819883659
  40. EXEC #140571852646608:c=0,e=38,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=827909369,tim=1482094819883756
  41. WAIT #140571852646608: nam='SQL*Net message to client' ela= 5 driver id=1650815232 #bytes=1 p3=0 obj#=89299 tim=1482094819883899
  42. FETCH #140571852646608:c=0,e=72,p=0,cr=5,cu=0,mis=0,r=1,dep=0,og=1,plh=827909369,tim=1482094819884012
  43. STAT #140571852646608 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=5 pr=0 pw=0 time=73 us)'
  44. STAT #140571852646608 id=2 cnt=171 pid=1 pos=1 obj=0 op='COUNT STOPKEY (cr=5 pr=0 pw=0 time=119 us)'
  45. STAT #140571852646608 id=3 cnt=171 pid=2 pos=1 obj=89299 op='TABLE ACCESS FULL TEST (cr=5 pr=0 pw=0 time=29 us cost=25 size=0 card=1500)'
  46. WAIT #140571852646608: nam='SQL*Net message from client' ela= 442 driver id=1650815232 #bytes=1 p3=0 obj#=89299 tim=1482094819884516
  47. FETCH #140571852646608:c=0,e=1,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=827909369,tim=1482094819884553
  48. WAIT #140571852646608: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=89299 tim=1482094819884570
上述步驟分析：
看上邊trace部分可以知道，select count(*) from test where rownum<=171語句已經沒有涉及到物理io的讀取了，原因是因為SQL語句“SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),0), NVL(SUM(C2),0) FROM (SELECT /*+ NO_PARALLEL("TEST") FULL("TEST") NO_PARALLEL_INDEX("TEST") */ 1 AS C1, 1 AS C2 FROM "SYS"."TEST" "TEST") SAMPLESUB”已經把表test所有的塊讀進buffer cache裡邊了。
這個SQL語句是oracle內部的遞迴sql，去掉相關hint簡化如下：
SELECT  NVL(SUM(C1),0), NVL(SUM(C2),0) FROM (SELECT  1 AS C1, 1 AS C2 FROM "SYS"."TEST" "TEST") SAMPLESUB，可以看到這裡已經對test表進行了訪問，而且從trace看是全表掃描。
因此我們知道了實際上所有的塊被讀進去buffer cache裡邊，並非select count(*) from test where rownum<=171語句引起的，也就是rownum<=N（N為正整數）的時候，sql語句只要找出前N條滿足條件的結果後，便不再進行讀取，這是oracle對rownum的優化。


但是由此引出另外的疑問，該遞迴sql的產生只有在第一次執行select count(*) from test where rownum<=171(share pool無此sql執行計劃)的時候才會產生，第二次執行做10046可以知道沒有該遞迴sql了。
問題出來總是要解決的，所幸還是可以找到相關資料的。
原因在於動態取樣，這裡引用網上對於動態取樣的一些描述:
動態取樣（Dynamic Sampling）技術的最初提出是在Oracle 9i R2，在段（表，索引，分割槽）沒有分析的情況下，為了使CBO 優化器得到足夠的資訊以保證做出正確的執行計劃而發明的一種技術，可以把它看做分析手段的一種補充。
當段物件沒有統計資訊時（即沒有做分析），動態取樣技術可以通過直接從需要分析的物件上收集資料塊（取樣）來獲得CBO需要的統計資訊。

顯然，遞迴sql是由於test表上無統計資訊而執行select count(*) from test where rownum<=171語句時後臺做動態取樣產生的。
下邊通過一些手段，在test表上沒有統計資訊的情況下，強制不做動態取樣。
  1. SYS@proc> alter system flush buffer_cache;

  3. System altered.

  5. SYS@proc> alter system flush shared_pool;

  7. System altered.

  9. SYS@proc> select file#,dbablk,state from x$bh where obj=(select data_object_id from dba_objects where owner='SYS' and object_name='TEST') and state<>0 order by dbablk;

  11. no rows selected

  13. SYS@proc> set autotrace on
  14. SYS@proc> select /*+ dynamic_sampling(test 0) */ count(*) from test where rownum<=171;

  16.   COUNT(*)
  17. ----------
  18.        171


  21. Execution Plan
  22. ----------------------------------------------------------
  23. Plan hash value: 827909369

  25. --------------------------------------------------------------------
  26. | Id | Operation           | Name | Rows  | Cost (%CPU)| Time      |
  27. --------------------------------------------------------------------
  28. |  0 | SELECT STATEMENT    |      |     1 |    25 (0)  | 00:00:01  |
  29. |  1 |  SORT AGGREGATE     |      |     1 |            |           |
  30. |* 2 |   COUNT STOPKEY     |      |       |            |           |
  31. |  3 |    TABLE ACCESS FULL| TEST | 1961  |    25 (0)  | 00:00:01  |
  32. --------------------------------------------------------------------

  34. Predicate Information (identified by operation id):
  35. ---------------------------------------------------

  37.    2 - filter(ROWNUM<=171)


  40. Statistics
  41. ----------------------------------------------------------
  42.      21 recursive calls
  43.      0 db block gets
  44.      23 consistent gets
  45.      8 physical reads
  46.      0 redo size
  47.     527 bytes sent via SQL*Net to client
  48.     523 bytes received via SQL*Net from client
  49.      2 SQL*Net roundtrips to/from client
  50.      3 sorts (memory)
  51.      0 sorts (disk)
  52.      1 rows processed

  54. SYS@proc> select file#,dbablk,state from x$bh where obj=(select data_object_id from dba_objects where owner='SYS' and object_name='TEST') and state<>0 order by dbablk;

  56.      FILE#     DBABLK      STATE
  57. ---------- ---------- ----------
  58.          6        138          1
  59.          6        138          3
  60.          6        139          1
  61.          6        140          1
從結果看，即使是清空共享池，沒有動態取樣下，是不會將所有的塊快取進資料緩衝區。

從動態取樣產生的原因上看，是因為test表沒有統計資訊，我們手動收集統計資訊，那麼即使不用hint去禁用動態取樣，也應該不會將所有的塊緩衝進去buffer cache裡邊。是否如此？
  1. SYS@proc> analyze table test compute statistics;

  3. Table analyzed.

  5. SYS@proc> alter system flush buffer_cache;

  7. System altered.

  9. SYS@proc> alter system flush shared_pool;

  11. System altered.

  13. SYS@proc> select file#,dbablk,state from x$bh where obj=(select data_object_id from dba_objects where owner='SYS' and object_name='TEST') and state<>0 order by dbablk;

  15. no rows selected

  17. SYS@proc> select count(*) from test where rownum<=171;

  19.   COUNT(*)
  20. ----------
  21.        171

  23. SYS@proc> select file#,dbablk,state from x$bh where obj=(select data_object_id from dba_objects where owner='SYS' and object_name='TEST') and state<>0 order by dbablk;

  25.      FILE# DBABLK     STATE
  26. ---------- ---------- ----------
  27.      6     138     1
  28.      6     139     1
  29.      6     140     1
結果顯而易見，到這裡大功告成。
關於動態取樣，這裡就不深入了。

對於rownum的結論，這裡附上一個實驗資料做效能對比吧！
  1. SYS@proc> create table t1 as select * from dba_objects;

  3. Table created.

  5. SYS@proc>
  6. SYS@proc>
  7. SYS@proc>
  8. SYS@proc> analyze table t1 compute statistics;

  10. Table analyzed.

  12. SYS@proc> set autotrace traceonly

  14. SYS@proc> select * from t1;

  16. 86997 rows selected.


  19. Execution Plan
  20. ----------------------------------------------------------
  21. Plan hash value: 3617692013

  23. --------------------------------------------------------------------------
  24. | Id | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time    |
  25. --------------------------------------------------------------------------
  26. |  0 | SELECT STATEMENT  |        | 86997 | 8580K | 1243 (1)   | 00:00:15|
  27. |  1 |  TABLE ACCESS FULL| T1     | 86997 | 8580K | 1243 (1)   | 00:00:15|
  28. --------------------------------------------------------------------------


  31. Statistics
  32. ----------------------------------------------------------
  33.        0 recursive calls
  34.        0 db block gets
  35.     6957 consistent gets
  36.        0 physical reads
  37.        0 redo size
  38. 10000967 bytes sent via SQL*Net to client
  39.    64312 bytes received via SQL*Net from client
  40.     5801 SQL*Net roundtrips to/from client
  41.        0 sorts (memory)
  42.        0 sorts (disk)
  43.    86997 rows processed


  46. SYS@proc> select * from t1 where rownum<=1;


  49. Execution Plan
  50. ----------------------------------------------------------
  51. Plan hash value: 3836375644

  53. -------------------------------------------------------------------------
  54. | Id | Operation          | Name | Rows | Bytes | Cost (%CPU)| Time     |
  55. -------------------------------------------------------------------------
  56. |  0 | SELECT STATEMENT   |      |    1 | 101   |    2 (0)   | 00:00:01 |
  57. |* 1 |  COUNT STOPKEY     |      |      |       |            |          |
  58. |  2 |   TABLE ACCESS FULL| T1   |    1 | 101   |    2 (0)   | 00:00:01 |
  59. -------------------------------------------------------------------------

  61. Predicate Information (identified by operation id):
  62. ---------------------------------------------------

  64.    1 - filter(ROWNUM<=1)


  67. Statistics
  68. ----------------------------------------------------------
  69.      0 recursive calls
  70.      0 db block gets
  71.      3 consistent gets
  72.      0 physical reads
  73.      0 redo size
  74.   1608 bytes sent via SQL*Net to client
  75.    523 bytes received via SQL*Net from client
  76.      2 SQL*Net roundtrips to/from client
  77.      0 sorts (memory)
  78.      0 sorts (disk)
  79.      1 rows processed



附上動態取樣相關資料：
動態取樣的作用
1、CBO 依賴的是充分的統計分析資訊，但是並不是每個使用者都會非常認真，及時地去對每個表做分析。為了保證執行計劃都儘可能地正確，Oracle需要使用動態取樣技術來幫助CBO 獲取儘可能多的資訊。
2、全域性臨時表。通常來講，臨時表的資料是不做分析的，因為它存放的資料是臨時性的，可能很快就釋放了，但是當一個查詢關聯到這樣的臨時表時，CBO要想獲得臨時表上的統計資訊分析資料，就只能依賴於動態取樣。
3、動態取樣除了可以在段物件沒有分析時，給CBO提供分析資料之外，還有一個獨特的能力，它可以對不同列之間的相關性做統計。這點通常發生在表設計不符合3NF的情況下，這個特性在表 符合3NF設計的 情況下少見。

儘管看到動態取樣的優點，但是它的缺點也是顯而易見，否則Oracle一定會一直使用動態取樣來取代資料分析：
1、 在OLAP或者資料倉儲環境下，SQL執行消耗的資源要遠遠大於SQL解析，那麼讓解析在消耗多一點資源做一些動態取樣分析，從而做出一個最優的執行計劃是非常值得的。 實際上在這樣的環境中，硬分析消耗的資源幾乎是可以忽略的。但是 取樣的資料塊有限，對於海量資料的表，結果難免有偏差。 所以一般在OLAP 或者資料倉儲環境中，將動態取樣的level 設定為3或者4比較好 。
2、 動態取樣需要額外的消耗資料庫資源，所以，如果 SQL被反覆執行，變數被繫結，硬分析很少，在這樣一個環境中，是不宜使用動態取樣的。 動態取樣發生在硬分析時，如果很少有硬分析發生，動態取樣的意義就不大。 當然如果沒有使用繫結變數，導致頻繁進行硬解析和動態取樣消耗過多資源也是不可行的，故OLTP系統非常不適宜使用動態取樣。

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

相關文章