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语句执行计划

启用 AUTOTRACE 功能

函数使得索引列失效

Oracle 绑定变量窥探

Oracle 自适应共享游标