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

最新推荐文章于 2026-06-25 17:00:00 发布

转载最新推荐文章于 2026-06-25 17:00:00 发布 · 1.1k 阅读

标签

#数据库 #操作系统

本文通过实验详细探讨了Oracle数据库中rownum对表扫描方式的影响，展示了不同设置下对数据读取效率的差异，揭示了rownum在数据库操作中的性能表现，并分析了相关优化策略。

前言：

偶然发现rownum上加限定条件后，执行计划虽然是全表扫描，但是实际上却是只扫描了满足条件的行所在的数据块，也就是没有做全表扫描。

问题可以假设性描述为一条select语句的全表扫描，结果是1000行，加上rownum<=500之后，oracle是扫描了前500行所在的块，还是扫描了1000行后取前500行（前者性能要更好）。

构造数据进行探究。探究过程中影响的因素有

1.参数db_file_multiblock_read_count，关于该参详情可以读阅 http://blog.itpub.net/30174570/viewspace-2140241/ 。

2.动态采样。

操作系统信息：

[oracle@oracle ~]$ uname -a
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
[oracle@oracle ~]$ lsb_release -a
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
Distributor ID: RedHatEnterpriseServer
Description: Red Hat Enterprise Linux Server release 6.5 (Santiago)
Release: 6.5
Codename: Santiago

数据库版本：

SYS@proc> select * from v$version where rownum<=1;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

构造测试数据：

SYS@proc> drop table test purge;
Table dropped.
SYS@proc> create table test tablespace test as select * from dba_objects where rownum<=1500;
Table created.
SYS@proc> select dbms_rowid.rowid_row_number(rowid) row# from test where rownum<=10;
ROW#
----------
0
1
2
3
4
5
6
7
8
9
10 rows selected.
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);
BLOCK# MIN(ROWNUM) MAX(ROWNUM)
---------- ----------- -----------
139 1 88
140 89 171
141 172 251
142 252 329
143 330 407
144 408 487
145 488 567
146 568 646
147 647 724
148 725 798
149 799 873
BLOCK# MIN(ROWNUM) MAX(ROWNUM)
---------- ----------- -----------
150 874 946
151 947 1022
161 1023 1104
162 1105 1179
163 1180 1261
164 1262 1342
165 1343 1418
166 1419 1496
167 1497 1500
20 rows selected.
SYS@proc> select EXTENT_ID,FILE_ID,BLOCK_ID,BLOCKS from dba_extents where owner='SYS' and SEGMENT_NAME='TEST';
EXTENT_ID FILE_ID BLOCK_ID BLOCKS
---------- ---------- ---------- ----------
0 6 136 8 --136 137 138 139 140 141 142 143
1 6 144 8 --144 145 146 147 148 149 150 151
2 6 160 8 --160 161 162 163 164 165 166 167

实验 过程：
说明：由于该过程是做的全表扫描下对rownum加限定词后，扫描表的块是否发生减少，为了避免db_file_multiblock_read_count的影响，将其值设置为1。

SYS@proc> show parameter multiblock
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count integer 8
SYS@proc> alter system set db_file_multiblock_read_count=1;
System altered.

SYS@proc> alter system flush buffer_cache;
System altered.
SYS@proc> alter system flush shared_pool;
System altered.
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;
no rows selected
SYS@proc> select count(*) from test where rownum<=171; --只读前两个块
COUNT(*)
----------
171
SYS@proc> set pagesize 9999
SYS@proc> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
SQL_ID 5h6qpq1adpkh8, child number 0
-------------------------------------
select count(*) from test where rownum<=171
Plan hash value: 827909369
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 25 (100)| |
| 1 | SORT AGGREGATE | | 1 | | |
|* 2 | COUNT STOPKEY | | | | |
| 3 | TABLE ACCESS FULL| TEST | 1500 | 25 (0) | 00:00:01 |
--------------------------------------------------------------------
...省略部分内容...
49 rows selected.
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;
FILE# DBABLK STATE
---------- ---------- ----------
6 138 3
6 138 3
6 138 1
6 139 1
6 140 1
6 141 1
6 142 1
6 143 1
6 144 1
6 145 1
6 146 1
FILE# DBABLK STATE
---------- ---------- ----------
6 147 1
6 148 1
6 149 1
6 150 1
6 151 1
6 161 1
6 162 1
6 163 1
6 164 1
6 165 1
6 166 1
FILE# DBABLK STATE
---------- ---------- ----------
6 167 1
23 rows selected.

上述步骤分析：
select count ( * ) from test where rownum < = 171 只读取了前两个块的内容，查看该语句执行计划，从TABLE ACCESS FULL 可以看出是全表扫描，而最后查看x$bh中sys.test表的情况可以看出，表的所有块也被读进去buffer cache中，符合全表扫描。
不过这里有一个很奇怪的现象，重新清空buffer cache之后，继续刚刚的动作，情况就不同了。

SYS@proc> alter system flush buffer_cache;
System altered.
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;
no rows selected
SYS@proc> select count(*) from test where rownum<=171;
COUNT(*)
----------
171
SYS@proc> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
SQL_ID 5h6qpq1adpkh8, child number 0
-------------------------------------
select count(*) from test where rownum<=171
Plan hash value: 827909369
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 25 (100)| |
| 1 | SORT AGGREGATE | | 1 | | |
|* 2 | COUNT STOPKEY | | | | |
| 3 | TABLE ACCESS FULL| TEST | 1500 | 25 (0) | 00:00:01 |
--------------------------------------------------------------------
...省略部分内容...
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;
FILE# DBABLK STATE
---------- ---------- ----------
6 138 1
6 139 1
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 。

SYS@proc> alter system flush shared_pool;
System altered.
SYS@proc> alter system flush buffer_cache;
System altered.
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;
no rows selected
SYS@proc> alter session set events '10046 trace name context forever,level 12';
Session altered.
SYS@proc> select count(*) from test where rownum<=171;
COUNT(*)
----------
171
SYS@proc> alter session set events '10046 trace name context off';
Session altered.
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;
FILE# DBABLK STATE
---------- ---------- ----------
6 138 3
6 138 3
6 138 1
6 139 1
6 140 1
6 141 1
6 142 1
6 143 1
6 144 1
6 145 1
6 146 1
FILE# DBABLK STATE
---------- ---------- ----------
6 147 1
6 148 1
6 149 1
6 150 1
6 151 1
6 161 1
6 162 1
6 163 1
6 164 1
6 165 1
6 166 1
FILE# DBABLK STATE
---------- ---------- ----------
6 167 1
23 rows selected.
SYS@proc> select value from v$diag_info where name like '%De%';
VALUE
--------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/proc/proc/trace/proc_ora_3950.trc

查看trace文件，部分内容如下：

PARSING IN CURSOR #140571851573816 len=337 dep=1 uid=0 oct=3 lid=0 tim=1482094819882124 hv=3345277572 ad='7eceddb0' sqlid='baj7tjm3q9sn4'
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
END OF STMT
PARSE #140571851573816:c=1000,e=946,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,plh=1950795681,tim=1482094819882123
EXEC #140571851573816:c=0,e=13,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=1950795681,tim=1482094819882174
WAIT #140571851573816: nam='db file sequential read' ela= 11 file#=6 block#=138 blocks=1 obj#=89299 tim=1482094819882200
WAIT #140571851573816: nam='db file sequential read' ela= 11 file#=6 block#=139 blocks=1 obj#=89299 tim=1482094819882256
WAIT #140571851573816: nam='db file sequential read' ela= 7 file#=6 block#=140 blocks=1 obj#=89299 tim=1482094819882284
WAIT #140571851573816: nam='db file sequential read' ela= 6 file#=6 block#=141 blocks=1 obj#=89299 tim=1482094819882305
WAIT #140571851573816: nam='db file sequential read' ela= 11 file#=6 block#=142 blocks=1 obj#=89299 tim=1482094819882360
WAIT #140571851573816: nam='db file sequential read' ela= 7 file#=6 block#=143 blocks=1 obj#=89299 tim=1482094819882397
WAIT #140571851573816: nam='db file sequential read' ela= 7 file#=6 block#=144 blocks=1 obj#=89299 tim=1482094819882420
WAIT #140571851573816: nam='db file sequential read' ela= 5 file#=6 block#=145 blocks=1 obj#=89299 tim=1482094819882439
WAIT #140571851573816: nam='db file sequential read' ela= 6 file#=6 block#=146 blocks=1 obj#=89299 tim=1482094819882462
WAIT #140571851573816: nam='db file sequential read' ela= 10 file#=6 block#=147 blocks=1 obj#=89299 tim=1482094819882569
WAIT #140571851573816: nam='db file sequential read' ela= 6 file#=6 block#=148 blocks=1 obj#=89299 tim=1482094819882602
WAIT #140571851573816: nam='db file sequential read' ela= 9 file#=6 block#=149 blocks=1 obj#=89299 tim=1482094819882624
WAIT #140571851573816: nam='db file sequential read' ela= 6 file#=6 block#=150 blocks=1 obj#=89299 tim=1482094819882647
WAIT #140571851573816: nam='db file sequential read' ela= 5 file#=6 block#=151 blocks=1 obj#=89299 tim=1482094819882667
WAIT #140571851573816: nam='db file sequential read' ela= 6 file#=6 block#=161 blocks=1 obj#=89299 tim=1482094819882687
WAIT #140571851573816: nam='db file sequential read' ela= 6 file#=6 block#=162 blocks=1 obj#=89299 tim=1482094819882705
WAIT #140571851573816: nam='db file sequential read' ela= 5 file#=6 block#=163 blocks=1 obj#=89299 tim=1482094819882723
WAIT #140571851573816: nam='db file sequential read' ela= 5 file#=6 block#=164 blocks=1 obj#=89299 tim=1482094819882741
WAIT #140571851573816: nam='db file sequential read' ela= 7 file#=6 block#=165 blocks=1 obj#=89299 tim=1482094819882761
WAIT #140571851573816: nam='db file sequential read' ela= 56 file#=6 block#=166 blocks=1 obj#=89299 tim=1482094819882829
WAIT #140571851573816: nam='db file sequential read' ela= 8 file#=6 block#=167 blocks=1 obj#=89299 tim=1482094819882868
FETCH #140571851573816:c=1000,e=702,p=21,cr=23,cu=0,mis=0,r=1,dep=1,og=1,plh=1950795681,tim=1482094819882883
STAT #140571851573816 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=23 pr=21 pw=0 time=701 us)'
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)'
CLOSE #140571851573816:c=0,e=5,dep=1,type=1,tim=1482094819882937
PARSE #140571851573816:c=0,e=12,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=1950795681,tim=1482094819883042
EXEC #140571851573816:c=0,e=11,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=1950795681,tim=1482094819883066
FETCH #140571851573816:c=0,e=129,p=0,cr=23,cu=0,mis=0,r=1,dep=1,og=1,plh=1950795681,tim=1482094819883204
CLOSE #140571851573816:c=0,e=2,dep=1,type=3,tim=1482094819883233
=====================
PARSING IN CURSOR #140571852646608 len=44 dep=0 uid=0 oct=3 lid=0 tim=1482094819883659 hv=1297676880 ad='7ecc7130' sqlid='82jwkqt6pjykh'
select count(*) from test where rownum<=171
END OF STMT
PARSE #140571852646608:c=44993,e=46883,p=26,cr=68,cu=0,mis=1,r=0,dep=0,og=1,plh=827909369,tim=1482094819883659
EXEC #140571852646608:c=0,e=38,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=827909369,tim=1482094819883756
WAIT #140571852646608: nam='SQL*Net message to client' ela= 5 driver id=1650815232 #bytes=1 p3=0 obj#=89299 tim=1482094819883899
FETCH #140571852646608:c=0,e=72,p=0,cr=5,cu=0,mis=0,r=1,dep=0,og=1,plh=827909369,tim=1482094819884012
STAT #140571852646608 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=5 pr=0 pw=0 time=73 us)'
STAT #140571852646608 id=2 cnt=171 pid=1 pos=1 obj=0 op='COUNT STOPKEY (cr=5 pr=0 pw=0 time=119 us)'
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)'
WAIT #140571852646608: nam='SQL*Net message from client' ela= 442 driver id=1650815232 #bytes=1 p3=0 obj#=89299 tim=1482094819884516
FETCH #140571852646608:c=0,e=1,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=827909369,tim=1482094819884553
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表上没有统计信息的情况下，强制不做动态采样。

SYS@proc> alter system flush buffer_cache;
System altered.
SYS@proc> alter system flush shared_pool;
System altered.
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;
no rows selected
SYS@proc> set autotrace on
SYS@proc> select /*+ dynamic_sampling(test 0) */ count(*) from test where rownum<=171;
COUNT(*)
----------
171
Execution Plan
----------------------------------------------------------
Plan hash value: 827909369
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 (0) | 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
|* 2 | COUNT STOPKEY | | | | |
| 3 | TABLE ACCESS FULL| TEST | 1961 | 25 (0) | 00:00:01 |
--------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWNUM<=171)
Statistics
----------------------------------------------------------
21 recursive calls
0 db block gets
23 consistent gets
8 physical reads
0 redo size
527 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
1 rows processed
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;
FILE# DBABLK STATE
---------- ---------- ----------
6 138 1
6 138 3
6 139 1
6 140 1

从结果看，即使是清空共享池，没有动态采样下，是不会将所有的块缓存进数据缓冲区。

从动态采样产生的原因上看，是因为test表没有统计信息，我们手动收集统计信息，那么即使不用hint去禁用动态采样，也应该不会将所有的块缓冲进去buffer cache里边。是否如此？

SYS@proc> analyze table test compute statistics;
Table analyzed.
SYS@proc> alter system flush buffer_cache;
System altered.
SYS@proc> alter system flush shared_pool;
System altered.
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;
no rows selected
SYS@proc> select count(*) from test where rownum<=171;
COUNT(*)
----------
171
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;
FILE# DBABLK STATE
---------- ---------- ----------
6 138 1
6 139 1
6 140 1

结果显而易见，到这里大功告成。
关于动态采样，这里就不深入了。

对于rownum的结论，这里附上一个实验数据做性能对比吧！

SYS@proc> create table t1 as select * from dba_objects;
Table created.
SYS@proc>
SYS@proc>
SYS@proc>
SYS@proc> analyze table t1 compute statistics;
Table analyzed.
SYS@proc> set autotrace traceonly
SYS@proc> select * from t1;
86997 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 86997 | 8580K | 1243 (1) | 00:00:15|
| 1 | TABLE ACCESS FULL| T1 | 86997 | 8580K | 1243 (1) | 00:00:15|
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
6957 consistent gets
0 physical reads
0 redo size
10000967 bytes sent via SQL*Net to client
64312 bytes received via SQL*Net from client
5801 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
86997 rows processed
SYS@proc> select * from t1 where rownum<=1;
Execution Plan
----------------------------------------------------------
Plan hash value: 3836375644
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 101 | 2 (0) | 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | TABLE ACCESS FULL| T1 | 1 | 101 | 2 (0) | 00:00:01 |
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
1608 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30174570/viewspace-2140240/