ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > Oracle中rownum对表的扫描方式性能上的影响深入探究

Oracle中rownum对表的扫描方式性能上的影响深入探究

原创 Oracle 作者：PiscesCanon 时间：2017-06-03 01:02:43 0 删除 编辑
前言：
偶然发现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博客 ” ，链接：https://blog.itpub.net/30174570/viewspace-2140240/ ，如需转载，请注明出处，否则将追究法律责任。

1
0
上一篇： Oracle中flush buffer cache和x$bh
下一篇： Oracle中db_file_multiblock_read_count参数探究
请登录后发表评论 登录
全部评论
PiscesCanon

注册时间：2015-03-23

  • 博文量
    29
  • 访问量
    57487

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593