oracle 预取,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无关

四、更多参考

原文链接:

http://blog.csdn.net/robinson_0612/article/details/7199520

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
回答: RabbitMQ的机制是指消费者在从队列中获消息之前,可以先获一定数量的消息。这个数量可以根据业务情况进行设置。如果设置为1,可以最大程度地利用客户端的性能,但是会导致频繁通知RabbitMQ并出新的消息,对RabbitMQ的性能不利。而如果设置过大,虽然性能会提高,但可靠性会降低。一般来说,数量在1到2500之间选择,中间值为500。通常会一定数量的消息,然后批量确认。\[2\] 在使用机制时,需要注意的是,如果同时设置了channel和消费者,RabbitMQ会独立地强制执行两个限制。消费者只有在未达到未确认消息限制时才会收到新消息。\[1\] 此外,如果已经配置了手动确认(acknowledge-mode: manual),并且每次只处理一条消息(prefetch: 1),但通道关闭后确认消息失败,可能是由于其他原因导致的,比如网络问题或者消息处理过程中发生了异常。需要进一步排查具体的错误原因。\[3\] #### 引用[.reference_title] - *1* [消息中间件 -RabbitMQ QoS模式 (二十五)](https://blog.csdn.net/a1173537204/article/details/103447094)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v91^control_2,239^v3^insert_chatgpt"}} ] [.reference_item] - *2* [RabbitMQ消费端消费机制](https://blog.csdn.net/li1987by/article/details/90449921)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v91^control_2,239^v3^insert_chatgpt"}} ] [.reference_item] - *3* [Rabbitmq机制配置,配置手动确认后仍然java.lang.IllegalStateException: Channel closed;...](https://blog.csdn.net/ChengQinHong/article/details/127223224)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v91^control_2,239^v3^insert_chatgpt"}} ] [.reference_item] [ .reference_list ]

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值