SQLplus 下行预取特性 - 军军小站|张军博客

通常情况下数据库引擎每访问一个数据块将产生至少一个逻辑读。而行预取与逻辑读息息相关。行预取是指当客户端从数据库获取数据时
可以采用单行也可以采用多行方式返回数据。当采用多行方式时，则会预取多条记录存储在客户端内存中以避免后续多次该数据的请求所致的
各种开销(LIO，PIO，NET IO)。一般预取行数越大，则所产生的开销越小，当达到临界值时其变化不大。
一、演示

1、创建演示表

	scott@CNMMBO> select * from v$version where rownum<2;

	

	BANNER

	----------------------------------------------------------------

	Oracle Database 10g Release 10.2.0.3.0 - 64bit Production

	scott@CNMMBO> create table t as select * from dba_objects;   -->创建演示表

	

	scott@CNMMBO> analyze table t compute statistics;            -->更新统计信息

	

	scott@CNMMBO> exec show_space('T');

	Unformatted Blocks .....................               0

	FS1 Blocks (0-25) ......................               0

	FS2 Blocks (25-50) .....................               0

	FS3 Blocks (50-75) .....................               0

	FS4 Blocks (75-100).....................               0

	Full Blocks ............................             690

	Total Blocks............................             768    -->表段上的总块数768

	Total Bytes.............................       6，291，456

	Total MBytes............................               6

	Unused Blocks...........................              58    -->未使用的块数58

	Unused Bytes............................         475，136

	Last Used Ext FileId....................              11

	Last Used Ext BlockId...................          39，561

	Last Used Block.........................              70

	

	PL/SQL procedure successfully completed.



2、arraysize与consistent gets的关系

	scott@CNMMBO> show arraysize                 -->arraysize的大小此时为默认值15

	arraysize 15

	scott@CNMMBO> set arraysize 2                -->修改arraysize的值为2

	scott@CNMMBO> set autotrace traceonly stat;  -->启用autotrace   

	scott@CNMMBO> select * from t;

	

	Statistics

	----------------------------------------------------------

	          1  recursive calls

	          0  db block gets

	      25390  consistent gets                -->此时的consistent gets为25390

	          0  physical reads

	          0  redo size

	    6596152  bytes sent via SQL*Net to client

	     275844  bytes received via SQL*Net from client

	      25034  SQL*Net roundtrips to/from client

	          0  sorts (memory)

	          0  sorts (disk)

	      50066  rows processed

	

	scott@CNMMBO> set arraysize 15              -->修改arraysize的值为15

	scott@CNMMBO> select * from t;

	

	Statistics

	----------------------------------------------------------

	          0  recursive calls

	          0  db block gets

	       3992  consistent gets                -->此时的consistent gets为3992

	          0  physical reads

	          0  redo size

	    2625967  bytes sent via SQL*Net to client

	      37199  bytes received via SQL*Net from client

	       3339  SQL*Net roundtrips to/from client

	          0  sorts (memory)

	          0  sorts (disk)

	      50066  rows processed



--从上面的演示可以看出由于设置了不同的array size值，导致在全表扫描(表上不存在索引)是产生了不同的consistent gets

--当arraysize值变大时，consistent gets的值会减少

--consistent gets 为3992从何而来?



	scott@CNMMBO> set autotrace off;

	scott@CNMMBO> select 50066/15+768-58 from dual;  -->number_of_rows/arraysize + total_blocks_read 

	

	50066/15+768-58

	---------------

	     4047.73333

	     

--从上面的计算可以看出4047若高于3992，因此该consistent gets接近于计算得到的值



	scott@CNMMBO> set autotrace traceonly;

	scott@CNMMBO> select count(*) from t;           -->聚合计算

	

	Execution Plan

	----------------------------------------------------------

	Plan hash value: 2966233522

	

	-------------------------------------------------------------------

	| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |

	-------------------------------------------------------------------

	|   0 | SELECT STATEMENT   |      |     1 |   158   (1)| 00:00:02 |

	|   1 |  SORT AGGREGATE    |      |     1 |            |          |

	|   2 |   TABLE ACCESS FULL| T    | 50066 |   158   (1)| 00:00:02 |

	-------------------------------------------------------------------

	

	Statistics

	----------------------------------------------------------

	          0  recursive calls

	          0  db block gets

	        695  consistent gets                -->此时的consistent gets为695

	          0  physical reads

	          0  redo size

	        517  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)

	          1  rows processed

	

	--此时走的是全表扫描，为什么一致读仅仅只有695?接近于表上的块数.

	--考虑表段上的块数768-58=710

	--注意此时的聚合仅仅是一个SINGLE CALL,SQL引擎内部使用行预取,每个块上将产生一次逻辑读，抽取其中的所有行	

	

	scott@CNMMBO> set autotrace off;

	scott@CNMMBO> SELECT num_rows，

	  2    blocks blks，

	  3    empty_blocks em_blks，

	  4    avg_space，

	  5    chain_cnt，

	  6    avg_row_len，

	  7    round(num_rows / blocks) AS avg_rows_per_block，

	  8    last_analyzed lst_anly，

	  9    stale_stats

	 10  FROM   dba_tab_statistics

	 11  WHERE  table_name = 'T'

	 12  AND owner = 'SCOTT';

	

	  NUM_ROWS       BLKS    EM_BLKS  AVG_SPACE  CHAIN_CNT AVG_ROW_LEN AVG_ROWS_PER_BLOCK LST_ANLY  STA

	---------- ---------- ---------- ---------- ---------- ----------- ------------------ --------- ---

	     50066        710         58        860          0          97                 71 12-JAN-12 NO

	

	scott@CNMMBO> select 71*710 from dual;

	

	    71*710

	----------            -->值50410与50066相接近

	     50410



3、arraysize与全表扫描	

	scott@CNMMBO> set autotrace traceonly stat;

	scott@CNMMBO> set arraysize 100       -->调整arraysize值为100

	scott@CNMMBO> select * from t;

	

	Statistics

	----------------------------------------------------------

	          0  recursive calls

	          0  db block gets

	       1185  consistent gets

	          0  physical reads

	          0  redo size

	    2106796  bytes sent via SQL*Net to client

	       5992  bytes received via SQL*Net from client

	        502  SQL*Net roundtrips to/from client

	          0  sorts (memory)

	          0  sorts (disk)

	      50066  rows processed

	

	scott@CNMMBO> set arraysize 200       -->调整arraysize值为200  

	scott@CNMMBO> select * from t;

	

	Statistics

	----------------------------------------------------------

	          0  recursive calls

	          0  db block gets

	        940  consistent gets

	          0  physical reads

	          0  redo size

	    2061046  bytes sent via SQL*Net to client

	       3242  bytes received via SQL*Net from client

	        252  SQL*Net roundtrips to/from client

	          0  sorts (memory)

	          0  sorts (disk)

	      50066  rows processed

	

	scott@CNMMBO> set arraysize 2000      -->调整arraysize值为2000,此时访问方式为全表扫描

	scott@CNMMBO> select * from t;

	

	Statistics

	----------------------------------------------------------

	          0  recursive calls

	          0  db block gets

	        721  consistent gets

	          0  physical reads

	          0  redo size

	    2019871  bytes sent via SQL*Net to client

	        767  bytes received via SQL*Net from client

	         27  SQL*Net roundtrips to/from client

	          0  sorts (memory)

	          0  sorts (disk)

	      50066  rows processed

	

	scott@CNMMBO> set arraysize 4000      -->调整arraysize值为4000,此时访问方式为全表扫描

	scott@CNMMBO> select * from t;

	

	Statistics

	----------------------------------------------------------

	          0  recursive calls

	          0  db block gets

	        708  consistent gets

	          0  physical reads

	          0  redo size

	    2017492  bytes sent via SQL*Net to client

	        624  bytes received via SQL*Net from client

	         14  SQL*Net roundtrips to/from client

	          0  sorts (memory)

	          0  sorts (disk)

	      50066  rows processed 

	

	scott@CNMMBO> set arraysize 20000     -->调整arraysize超出范围

	SP2-0267: arraysize option 20000 out of range (1 through 5000)

	scott@CNMMBO> set arraysize 5000      -->调整arraysize到最大值5000,此时访问方式为全表扫描

	scott@CNMMBO> select * from t;

	

	Statistics

	----------------------------------------------------------

	          0  recursive calls

	          0  db block gets

	        706  consistent gets

	          0  physical reads

	          0  redo size

	    2017126  bytes sent via SQL*Net to client

	        602  bytes received via SQL*Net from client

	         12  SQL*Net roundtrips to/from client

	          0  sorts (memory)

	          0  sorts (disk)

	      50066  rows processed 

                

--从上面观察可以看出当arraysize不但增大的情形下，其逻辑读相应会减少，当arraysize达到一定值之后逻辑读减少的幅度变化不大

      

	scott@CNMMBO> set autotrace traceonly; 

	scott@CNMMBO> show arraysize 

	arraysize 1

	scott@CNMMBO> select * from emp;

	

	Execution Plan

	----------------------------------------------------------

	Plan hash value: 3956160932

	

	--------------------------------------------------------------------------

	| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

	--------------------------------------------------------------------------

	|   0 | SELECT STATEMENT  |      |    14 |   518 |     3   (0)| 00:00:01 |

	|   1 |  TABLE ACCESS FULL| EMP  |    14 |   518 |     3   (0)| 00:00:01 |

	--------------------------------------------------------------------------

	

	Statistics

	----------------------------------------------------------

	          0  recursive calls

	          0  db block gets

	         14  consistent gets

	          0  physical reads

	          0  redo size

	       2633  bytes sent via SQL*Net to client

	        558  bytes received via SQL*Net from client

	          8  SQL*Net roundtrips to/from client

	          0  sorts (memory)

	          0  sorts (disk)

	         14  rows processed	    

--从上面的测试可以看出在全表扫描时，当arraysize为1时，表上的一条记录将产生一次consistent gets  

	         

3、arraysize与索引扫描

	scott@CNMMBO> alter table t add constraint t_pk primary key(object_id);  -->为表添加索引

	

	scott@CNMMBO> analyze table t compute statistics;                        -->更新统计信息

	

	scott@CNMMBO> set autotrace traceonly; 

	scott@CNMMBO> select count(*) from t;

	

	Execution Plan

	----------------------------------------------------------

	Plan hash value: 454320086

	

	----------------------------------------------------------------------

	| Id  | Operation             | Name | Rows  | Cost (%CPU)| Time     |

	----------------------------------------------------------------------

	|   0 | SELECT STATEMENT      |      |     1 |    24   (0)| 00:00:01 |

	|   1 |  SORT AGGREGATE       |      |     1 |            |          |

	|   2 |   INDEX FAST FULL SCAN| T_PK | 50066 |    24   (0)| 00:00:01 |

	----------------------------------------------------------------------

	

	Statistics

	----------------------------------------------------------

	          1  recursive calls

	          0  db block gets

	        111  consistent gets

	          0  physical reads

	          0  redo size

	        517  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)

	          1  rows processed

	          

	--从上面的执行计划和buffers可以看出此时走了索引全扫描,且consistent gets的大小为111.即优化器基于索引统计得到总行数,而不是基于表.

	

	scott@CNMMBO> exec show_space('T_PK'，'SCOTT'，'INDEX');         -->查看索引段的使用情况

	Unformatted Blocks .....................               0

	FS1 Blocks (0-25) ......................               0

	FS2 Blocks (25-50) .....................               1

	FS3 Blocks (50-75) .....................               0

	FS4 Blocks (75-100).....................               0

	Full Blocks ............................             104

	Total Blocks............................             120       -->索引段总块数120      

	Total Bytes.............................         983，040

	Total MBytes............................               0

	Unused Blocks...........................               5       -->空闲块数为5

	Unused Bytes............................          40，960

	Last Used Ext FileId....................              11

	Last Used Ext BlockId...................          39，745

	Last Used Block.........................               3

	

	PL/SQL procedure successfully completed.



	scott@CNMMBO> show arraysize 

	arraysize 5000	

	scott@CNMMBO> select count(*) from t;

	

	Statistics

	----------------------------------------------------------

	          0  recursive calls

	          0  db block gets

	        111  consistent gets

	          0  physical reads

	          0  redo size

	        517  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)

	          1  rows processed

	

	scott@CNMMBO> set arraysize 2000

	scott@CNMMBO> select count(*) from t;

	

	Statistics

	----------------------------------------------------------

	          0  recursive calls

	          0  db block gets

	        111  consistent gets

	          0  physical reads

	          0  redo size

	        517  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)

	          1  rows processed

	

	scott@CNMMBO> set arraysize 15

	scott@CNMMBO> select count(*) from t;

	

	Statistics

	----------------------------------------------------------

	          0  recursive calls

	          0  db block gets

	        111  consistent gets

	          0  physical reads

	          0  redo size

	        517  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)

	          1  rows processed

	

	scott@CNMMBO> set arraysize 1

	scott@CNMMBO> select count(*) from t;

	

	Statistics

	----------------------------------------------------------

	          0  recursive calls

	          0  db block gets

	        111  consistent gets

	          0  physical reads

	          0  redo size

	        517  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)

	          1  rows processed

	          

--从上面的测试可知，走索引扫描之后的聚合与arraysize无关

二、分析
假定使用select * from t发布查询，此时表上每个数据块为16行，且arraysize 的设置为15(缺省值)，
则
    第一次fetch，读第一块15行，此时产生第1次consistent gets
    第二次fetch，读第一块1行，此时产生第2次consistent gets，读第二块14行，此时产生第3次consistent gets
    第三次fetch，读第二块2行，此时产生第4次consistent gets，读第三块13行，此时产生第5次consistent gets
     依此内推
假定此时表上每个数据块为10行，即数据块上的行数小于arraysize的情形(10<15)
     第一次fetch，读第一块10行，此时产生第1次consistent gets
           读第二块5行，此时产生第2次consistent gets
     第二次fetch，读第二块5行，此时产生第3次consistent gets
           读第三块10行，此时产生第4次consistent gets
     第三次fetch，读第四块10行，此时产生第5次consistent gets
           读第五块5行，此时产生第6次consistent gets
    依此内推

当arraysize设置为n(15)时，则Oracle从buffer一次fetch n(15)行，然后将数据返回给客户端.接下来Oracle会再次从buffer中fetch第二次
，返回下一个n(15)行
由上可知，在fetch期间可能会产生多次consistent gets。

当设置了较大的arraysize，从演示中可以看出SQL*Net的sent与received也随之减少。即请求时往返的次数明显降低。
三、总结
1、arraysize参数用于控制返回给客户端预取的行数，缺省值为15
2、逻辑读为consistent gets + db block gets，为简化描述，下面直接使用逻辑读
3、当行预取值设定为1时，对于全表扫描，每返回一个行需要产生一个逻辑读，如果设定的行预取值大于单块存储的行数，则逻辑读接近于块数
4、逻辑读的值随arraysize的增大而减小，当达到临界值(即略大于表上单块存储的行束)之后其变化趋势不大
5、SQL*Net的sent与received的值会随着arraysize的增大而减小
6、逻辑读通常会多于一次，见第二点的分析
7、对于聚合运算的情形(无索引)，SQL引擎内部使用行预取。即每次SQL引擎访问一个块的时候，抽取其中的所有行。其逻辑读接近于块数
8、在使用索引的情形下，consistent gets与arraysize无关
四、更多参考

父游标、子游标及共享游标

绑定变量及其优缺点

dbms_xplan之display_cursor函数的使用

dbms_xplan之display函数的使用

执行计划中各字段各模块描述

使用 EXPLAIN PLAN 获取SQL语句执行计划

更多文章、技术交流、商务合作、联系博主

微信扫码或搜索：z360901061

微信扫一扫加我为好友

QQ号联系： 360901061

您的支持是博主写作最大的动力，如果您喜欢我的文章，感觉我的文章对您有帮助，请用微信扫描下面二维码支持博主2元、5元、10元、20元等您想捐的金额吧，狠狠点击下面给点支持吧，站长非常感激您！手机微信长按不能支付解决办法：请将微信支付二维码保存到相册，切换到微信，然后点击微信右上角扫一扫功能，选择支付二维码完成支付。

【本文对您有帮助就好】元

2元

5元

10元

20元

自定义