Oracle db_file_mulitblock_read_count参数

Oracle db_file_mulitblock_read_count参数


     Oracle DB_FILE_MULTIBLOCK_READ_COUNT是Oracle比较重要的一个全局性参数,可以影响系统级别及sessioin级别。主要是用于设置最小化表扫描时Oracle一次按顺序能够读取的数据块数。通常情况下,我们看到top events中的等待事件db file scattered read时会考虑到增加该参数的值。但是否增加了DB_FILE_MULTIBLOCK_READ_COUNT的值就一定可以减少db file scattered read? 本文描述了DB_FILE_MULTIBLOCK_READ_COUNT的设置并给出演示。

 

1、参数DB_FILE_MULTIBLOCK_READ_COUNT(MBRC)
       参数DB_FILE_MULTIBLOCK_READ_COUNT简写为(MBRC)。
       该参数是最小化表扫描的重要参数,用于指定Oracle一次按顺序能够读取的数据块数。理论上该值越大则能够读取的数据块越多。
       实现全表扫描,索引全扫描及索引快速扫描所需的I/O总数取决于该参数,以及表自身的大小,是否使用并行等等。
       Oracle 10gR2以后会根据相应的操作系统及buffer cache以最优化的方式来自动设定该参数的值。通常情况下该值为1MB/db_block_size。
       在最大I/O为1MB的情况下,block的大小为8KB,则参数的值为128。如果在最大I/O为64KB,block为8KB,则参数的值为8。
       对于OLTP和batch环境该参数的值为4到16,DSS环境应设置大于16以上或大的值。
       该参数的变化对数据库性能产生整体性的影响,过大的设置会导致大量SQL访问路径发生变化,如原先的索引扫描倾向于使用全表扫描。
       按照Oracle的建议在10g R2之后尽可能使用oracle自动设置的值。

 

2、参数DB_FILE_MULTIBLOCK_READ_COUNT与SSTIOMAX
     In Release 9.2 and above; follow the explanation below:
  
     Each version of Oracle on each port, is shipped with a preset maximum of how much data can be transferred in a single read (which of course is equivalent to the db_file_multiblock_read_count since the block size is fixed).
     For 8i and above (on most platforms) this is 1Mb and is referred to as SSTIOMAX.
     To determine it for your port and Oracle version, simply set db_file_multiblock_read_count to a nonsensical value and Oracle will size it down for you.
  
     从上面的描述可知,Oracle 9.2之后,有一个名叫SSTIOMAX的东东,限制了MBRC的设置。
     由于SSTIOMAX大多数平台最大单次I/O为1MB,db_block_size为8kb,因此MBRC参数的最大值通常为128。128*8kb=1mb。
     对于设置大于1MB的情形,即MBRC*db_block_size>SSTIOMAX的情形,则设置的值并不生效,而是使用符合SSTIOMAX的最大MBRC值。

 

3、如何计算MBRC
     The formula as internally used is as below:
         db_file_multiblock_read_count = min(1048576/db_block_size , db_cache_size/(sessions * db_block_size))
  
     Without WORKLOAD stats, CBO computes multiblock reads as:
          io_cost = blocks/(1.6765 * power(db_file_multiblock_read_count,0.6581))
  
     With WORKLOAD stats, then:
          io_cost = blocks/mbrc * mreadtim/sreadtim
  
     下面是不同情形设置所致的最大i/o
       
db_blocks_size      tablespace block size      db_file_multiblock_read_count   max_fetch_blocks_in_single_read
     ---------------     ----------------------     -----------------------------   -------------------------------
     8k                  8k                         32                              8*32=256kb(i/o)
     8k                  4k                         32                              8*32/4=64kb(i/o)
     8k                  8k                         not explicitly set              determined by OS and db_cache_size
     8k                  8k                         >128                            8*128=1MB(i/o)

 

4、哪些情形导致单次多块读少于预设置
     a、读段头时单块读(此情形显而易见,通常一个extent包含一个段头header)
     b、物理读不能跨越多个区(extent)
     c、部分数据块已经位于高速缓存则不会从I/O子系统再次读取,除非是直接读(direct path read)。

 

5、演示不同值的MBRC单次读的block(system级别)

  1. a、MBRC为16的情形  
  2. --演示环境  
  3. sys@SYBO2SZ> ho cat /etc/issue  
  4.   
  5. Welcome to SUSE Linux Enterprise Server 10 SP3 (x86_64) - Kernel \r (\l).  
  6.   
  7. sys@SYBO2SZ> select * from v$version where rownum=1;  
  8.   
  9. BANNER  
  10. ----------------------------------------------------------------  
  11. Oracle Database 10g Release 10.2.0.3.0 - 64bit Production  
  12.   
  13. --Author: Robinson  
  14. --Blog  : http://blog.csdn.net/robinson_0612  
  15.   
  16. --查看MBRC的值  
  17. sys@SYBO2SZ> show parameter read_count;  
  18.   
  19. NAME                                 TYPE        VALUE  
  20. ------------------------------------ ----------- ------------------------------  
  21. db_file_multiblock_read_count        integer     16  
  22.   
  23. --查看和read_count相关的隐藏参数       
  24. sys@SYBO2SZ> @hidden_para  
  25. Enter value for para: read_count  
  26.   
  27. KSPPINM                               KSPPSTVL             DESCRIB  
  28. -----------------------------------   -------------------- ---------------------------------------------  
  29. _db_file_exec_read_count              48                   multiblock read count for regular clients  
  30. _db_file_optimizer_read_count         8                    multiblock read count for regular clients  
  31. _db_file_noncontig_mblock_read_count  11                   number of noncontiguous db blocks to be prefetched  
  32. _sort_multiblock_read_count           2                    multi-block read count for sort  
  33.   
  34. sys@SYBO2SZ> get trace_enable_cur.sql  
  35.   1* alter session set events '10046 trace name context forever,level 8';  
  36. sys@ORA11G> @trace_enable_cur  
  37.   
  38. Session altered.  
  39.   
  40. sys@SYBO2SZ> select count(*) from scott.big_table where owner='SYS';  
  41.   
  42.   COUNT(*)  
  43. ----------  
  44.     468066  
  45.   
  46. sys@SYBO2SZ> get trace_disable_cur.sql  
  47.   1* alter session set events '10046 trace name context off';  
  48. sys@SYBO2SZ> @trace_disable_cur.sql  
  49.   
  50. Session altered.  
  51.   
  52. sys@SYBO2SZ> @my_env  
  53.   
  54. SPID                SID    SERIAL# USERNAME        PROGRAM  
  55. ------------ ---------- ---------- --------------- ------------------------------------------------  
  56. 24472              1094         30 robin           oracle@SZDB (TNS V1-V3)  
  57.   
  58. sys@SYBO2SZ> @get_spec_sess_trace_file  
  59. Enter value for input_sid: 1094  
  60. Enter value for input_serial: 30  
  61.   
  62.        SID    SERIAL# SPID         TRACE_FILE  
  63. ---------- ---------- ------------ ----------------------------------------------------------  
  64.       1094         30 24472        /u02/database/SYBO2SZ/udump/sybo2sz_ora_24472.trc  
  65. --下面的blocks即为单次读取时的块数  
  66. sys@SYBO2SZ> ho grep scatter /u02/database/SYBO2SZ/udump/sybo2sz_ora_24472.trc |tail   
  67. WAIT #7: nam='db file scattered read' ela= 1491 file#=5 block#=18363 blocks=16 obj#=52884 tim=1337624821997106  
  68. WAIT #7: nam='db file scattered read' ela= 5148 file#=5 block#=18443 blocks=2 obj#=52884 tim=1337624822005413  
  69. WAIT #7: nam='db file scattered read' ela= 32363 file#=5 block#=18571 blocks=16 obj#=52884 tim=1337624822041788  
  70. WAIT #7: nam='db file scattered read' ela= 1930 file#=5 block#=18587 blocks=16 obj#=52884 tim=1337624822044227  
  71. WAIT #7: nam='db file scattered read' ela= 345 file#=5 block#=18603 blocks=16 obj#=52884 tim=1337624822045165  
  72. WAIT #7: nam='db file scattered read' ela= 1712 file#=5 block#=18619 blocks=16 obj#=52884 tim=1337624822047555  
  73. WAIT #7: nam='db file scattered read' ela= 58 file#=5 block#=18635 blocks=2 obj#=52884 tim=1337624822048219  
  74.   
  75.   
  76. b、MBRC大于128的情形  
  77. sys@SYBO2SZ> alter system set db_file_multiblock_read_count=256;  
  78.   
  79. System altered.  
  80.   
  81. sys@SYBO2SZ> show parameter read_count;   --->从这个查询可知,大于128的情形并没有生效  
  82.   
  83. NAME                                 TYPE        VALUE  
  84. ------------------------------------ ----------- ------------------------------  
  85. db_file_multiblock_read_count        integer     128  
  86.   
  87. --对于MBRC为128的情形演示步骤同上,下面仅仅列出最终结果  
  88. --注意在使用不同的MBRC在系统级别测试前应将buffer cache清空(alter system flush buffer_cache)  
  89. sys@SYBO2SZ> ho grep scatter /u02/database/SYBO2SZ/udump/sybo2sz_ora_24750.trc |tail  
  90. WAIT #7: nam='db file scattered read' ela= 2070 file#=5 block#=20875 blocks=126 obj#=53208 tim=1337625626875769  
  91. WAIT #7: nam='db file scattered read' ela= 1991 file#=5 block#=21003 blocks=126 obj#=53208 tim=1337625626880199  
  92. WAIT #7: nam='db file scattered read' ela= 1970 file#=5 block#=21131 blocks=126 obj#=53208 tim=1337625626884412  
  93. WAIT #7: nam='db file scattered read' ela= 2078 file#=5 block#=21259 blocks=126 obj#=53208 tim=1337625626888846  
  94. WAIT #7: nam='db file scattered read' ela= 2035 file#=5 block#=21387 blocks=126 obj#=53208 tim=1337625626893039  
  95. WAIT #7: nam='db file scattered read' ela= 2040 file#=5 block#=21515 blocks=126 obj#=53208 tim=1337625626897021  
  96. WAIT #7: nam='db file scattered read' ela= 1048 file#=5 block#=21643 blocks=66 obj#=53208 tim=1337625626900379  
  97.   
  98.   
  99. c、MBRC为缺省值的情形  
  100. sys@SYBO2SZ> alter system reset db_file_multiblock_read_count scope=spfile sid='*';  
  101.   
  102. System altered.  
  103.   
  104. sys@SYBO2SZ> show parameter read_count;  
  105.   
  106. NAME                                 TYPE        VALUE  
  107. ------------------------------------ ----------- ------------------------------  
  108. db_file_multiblock_read_count        integer     128  
  109.   
  110. sys@SYBO2SZ> startup force;   --->reset mbrc后需要重新启动db  
  111.   
  112. sys@SYBO2SZ> show parameter read_count  
  113.   
  114. NAME                                 TYPE        VALUE  
  115. ------------------------------------ ----------- ------------------------------  
  116. db_file_multiblock_read_count        integer     44  
  117.     
  118. sys@SYBO2SZ> ho grep scatter /u02/database/SYBO2SZ/udump/sybo2sz_ora_24890.trc | tail  
  119. WAIT #14: nam='db file scattered read' ela= 682 file#=5 block#=21431 blocks=44 obj#=53208 tim=1337626216154292  
  120. WAIT #14: nam='db file scattered read' ela= 4497 file#=5 block#=21475 blocks=38 obj#=53208 tim=1337626216163586  
  121. WAIT #14: nam='db file scattered read' ela= 586 file#=5 block#=21515 blocks=44 obj#=53208 tim=1337626216166611  
  122. WAIT #14: nam='db file scattered read' ela= 691 file#=5 block#=21559 blocks=44 obj#=53208 tim=1337626216170137  
  123. WAIT #14: nam='db file scattered read' ela= 4860 file#=5 block#=21603 blocks=38 obj#=53208 tim=1337626216177698  
  124. WAIT #14: nam='db file scattered read' ela= 592 file#=5 block#=21643 blocks=44 obj#=53208 tim=1337626216180540  
  125. WAIT #14: nam='db file scattered read' ela= 292 file#=5 block#=21687 blocks=22 obj#=53208 tim=1337626216183493  

6、演示不同MBRC所耗用的时间(session级别)

  1. --下面的PL/SQL代码用于测试不同的MBRC时所读的块数以及耗用的时间  
  2. scott@SYBO2SZ> get assess_mbrc.sql  
  3.   1  DECLARE  
  4.   2    l_count PLS_INTEGER;  
  5.   3    l_time NUMBER(10,1);  
  6.   4    l_starting_time PLS_INTEGER;  
  7.   5    l_ending_time PLS_INTEGER;  
  8.   6    l_blocks PLS_INTEGER;  
  9.   7    l_starting_blocks PLS_INTEGER;  
  10.   8    l_ending_blocks PLS_INTEGER;  
  11.   9    l_dbfmbrc PLS_INTEGER;  
  12.  10  BEGIN  
  13.  11    dbms_output.put_line('dbfmbrc    blocks    seconds');  
  14.  12    dbms_output.put_line('-------    ------    -------');  
  15.  13    FOR i IN 1..32  
  16.  14    LOOP  
  17.  15      l_dbfmbrc := i * 4;  
  18.  16      EXECUTE IMMEDIATE 'ALTER SESSION SET db_file_multiblock_read_count = '||l_dbfmbrc;  
  19.  17      EXECUTE IMMEDIATE 'ALTER SYSTEM SET EVENTS ''IMMEDIATE TRACE NAME FLUSH_CACHE''';  
  20.  18      SELECT value INTO l_starting_blocks  
  21.  19      FROM v$mystat ms JOIN v$statname USING (statistic#)  
  22.  20      WHERE name = 'physical reads';  
  23.  21      l_starting_time := dbms_utility.get_time();  
  24.  22      SELECT count(*) INTO l_count FROM big_table;  
  25.  23      l_ending_time := dbms_utility.get_time();  
  26.  24      SELECT value INTO l_ending_blocks  
  27.  25      FROM v$mystat ms JOIN v$statname USING (statistic#)  
  28.  26      WHERE name = 'physical reads';  
  29.  27      l_time := l_ending_time-l_starting_time;  
  30.  28      l_blocks := l_ending_blocks-l_starting_blocks;  
  31.  29      dbms_output.put_line(l_dbfmbrc||'         '||l_blocks||'         '||to_char(l_time)||'hsec');  
  32.  30    END LOOP;  
  33.  31* END;  
  34.  32  /  
  35. dbfmbrc    blocks    seconds  
  36. -------    ------    -------  
  37. 4         2119         18hsec  
  38. 8         2093         15hsec  
  39. 12         2093         7hsec  
  40. 16         2093         8hsec  
  41. 20         2093         9hsec  
  42. 24         2093         8hsec  
  43. 28         2093         9hsec  
  44. 32         2093         8hsec  
  45. 36         2093         8hsec  
  46. 40         2093         8hsec  
  47. 44         2093         9hsec  
  48. 48         2093         9hsec  
  49. 52         2093         9hsec  
  50. 56         2093         8hsec  
  51. 60         2093         9hsec  
  52. 64         2093         8hsec  
  53. 68         2093         9hsec  
  54. 72         2093         8hsec  
  55. 76         2093         9hsec  
  56. 80         2093         9hsec  
  57. 84         2093         9hsec  
  58. 88         2093         8hsec  
  59. 92         2093         9hsec  
  60. 96         2093         9hsec  
  61. 100         2093         9hsec  
  62. 104         2093         8hsec  
  63. 108         2093         9hsec  
  64. 112         2093         8hsec  
  65. 116         2093         9hsec  
  66. 120         2093         8hsec  
  67. 124         2093         9hsec  
  68. 128         2093         9hsec  
  69.   
  70. PL/SQL procedure successfully completed.  
  71. --从上面的结果可以看出,当设置MBRC为16时,所读取的块数以及所耗用的时间基本上不再受到MBRC的影响。  
  72. --由此可知,单纯增加MBRC的值也不能够彻底地解决db file scattered read。  
  73. --到底如何设置多少,还是按照Oracle的建议保留缺省值。再在缺省值的基础之上作相应调整。  
  74. --Reference:[841444.1] [473740.1] [1398860.1] [291239.1]  

 

更多参考

DML Error Logging 特性 

PL/SQL --> 游标

PL/SQL --> 隐式游标(SQL%FOUND)

批量SQL之 FORALL 语句

批量SQL之 BULK COLLECT 子句

PL/SQL 集合的初始化与赋值

PL/SQL 联合数组与嵌套表
PL/SQL 变长数组
PL/SQL --> PL/SQL记录

SQL tuning 步骤

高效SQL语句必杀技

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

绑定变量及其优缺点

dbms_xplan之display_cursor函数的使用

dbms_xplan之display函数的使用

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

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

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

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

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

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值