sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 运维DBA >

【学习笔记】分析block size与db_file_multiblock_read_count的关系

时间:2016-12-12 21:39   来源:Oracle研究中心   作者:网络   点击:

天萃荷净 Oracle研究中心学习笔记:分享一篇关于Oracle数据库条带大小与block size以及db_file_multiblock_read_count 的关系。

本站文章除注明转载外,均为本站原创: 转载自love wife & love life —Roger 的Oracle技术博客
本文链接地址: stripe /block size/db_file_multiblock_read_count

+++++++ 条带大小与block size以及db_file_multiblock_read_count 的关系

首先我们来温习下db_file_multiblock_read_count参数,该参数我想都比较熟悉了,不多说。

db_file_multiblock_read_count:

关于这个参数,不用多说了,是指oracle多块读所能读取的最大block数(主要针对full scan,例如table full scan,index fast full scan),理论上有这么一个关系:

db_file_multiblock_read_count=(max io szie)/block_size ,

当然这里的max io size是受限于操作系统的,另外据我所知,目前db_file_multiblock_read_count参数,oracle所支持的最大值也就是128.

另外需要说明一点的是,从10gR2开始,该参数已经是自动调节了。
SQL> SELECT * FROM v$version WHERE rownum < 2;

BANNER
--------------------------------------------------------------------------------
Oracle DATABASE 11g Enterprise Edition Release 11.2.0.2.0 - Production

SQL> SHOW parameter multiblock

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_hash_multiblock_io_count            INTEGER     0
_sort_multiblock_read_count          INTEGER     2
db_file_multiblock_read_count        INTEGER     54

可以看到,我这里11gR2环境中,参数自动调整后为54,是一个比较大的值了。

block_size:

关于block size,是指定义数据库中数据块的大小,9i之前每个数据库只能有一个block size。从9i开始支持一个库中存在多种block_size的情况。当然,我们这里不是来讨论block_size定义的,我是来说明下,我们如何来选择适合自己系统的block_size?

在oracle 的performance tunning 手册里面,是这样描述的:

针对读操作:

1) 如果rows are small且数据库access方式主要是随机操作,那么推荐使用smaller block size;
2) 如果rows are small且数据库access方式主要是顺序读,那么推荐使用smaller block size;
3) 如果rows are small且数据库access方式主要是随机和顺序操作,那么推荐使用large block size;
4) 如果rows are large,比如包含log 数据,那么推荐使用large block size。


针对写:

对于高并发的OLTP系统,如果你使用一个比较大的block size,那么你可能需要考虑这个关键性的属性:
initrans,maxtrans,freelists。 对于assm的情况下,freelists不需要特别进行设置了。如果你不知道选择何种block size时,那么建议使用8k的block size。 oracle的建议说,8k的大小,对于绝大多数的oltp
系统来讲,设置都是比较合理的,除非哪些经常需要处理lob 对象的系统。

另外文档中还有一个关于block size的优劣描述:
Block Size        优势                                     劣势

小block           1)适合小数据的大量性质的随机读取        1)元数据开销比较大(例如header 占用的空间)
                  2)降低block争用                         2)不利于大量数据的操作,容易产生row chaining.

大block           1)开销小,用于存储数据的空间多          1)可能会浪费大量的buffer cache,例如你读取50 byts的数据到buffer中,
                  2)对于单块读,可以一次性读取更多的行数据  对于8k的block,你就会浪费7950 bytes的buffer。
                  3)适用于大量数据的随机读,比如lob。     2)OLTP环境中,这种情况下会导致index block争用的加剧。


另外其中一篇mos文档中也有些的相关描述:
Block Sizes, Buffer Pools, and Index Types                                                     
------------------------------------------                                                     
In Oracle databases 9i, 10g, and 11g, it is a best practice to use multiple block sizes;       
this allows you to tailor the block size to a specific type of access. 
Place tables and indexesos strip:
in tablespaces block sized according to access method.                                          
- For single block read type OLTP access, use 8k block sizes.                                  
- For full table scan access, such as data warehouse, use 16k-32k block sizes                  
- For index lookups use 8-16K block sizes.                                                     
- For indexes that are scanned or bitmap indexes use 16k-32k
stripe:

关于条带大小的设置,显然比较重要,performance tunning里面是这样描述的:

Stripe depth is the size of the stripe, sometimes called stripe unit.
Stripe width is the product of the stripe depth and the number of drives in the striped set.

Choose these values wisely so that the system is capable of sustaining the required throughput.
For an Oracle database, reasonable stripe depths range from 256 KB to 1 MB. Different types of
applications benefit from different stripe depths. The optimal stripe depth and stripe width
depend on the following:


On some Oracle ports, an Oracle block boundary may not align with the stripe. If your stripe depth is the same
size as the Oracle block, then a single I/O issued by Oracle might result in two physical I/O operations.   

这样就是说,由于oracle block边界的问题,如果设置比较小(等于block size的话),那么一个单块读都可能需要2次物理io.

文档Demantra Performance Basics to Best Practices - Sofware Hardware Complete [ID 1081936.1] 中有是这样描述的:

Set stripe width (amount of each stripe on each disk) to at least db_block_size*db_file_multiblock_read_count.                                                      
Set stripe depth (number of disks in the stripe set) to at least (expected maximum IO/sec)/90 for RAID1+0)
                 or (expected maximum IO/sec)/50 for RAID5).
我们可以看到,上面这里计算strip depth比较详细,主要是针对raid的情况:

raid 10 —- maximum IO/sec/90
raid 5 —- maximum IO/sec/50

所谓的条带深度,其实就是指条带大小。条带宽度是指一个条带集中的驱动数,这个我们通常不关注。我们关注的是条带大小。

11g的performance tunning官方文档是这样描述的:

Minimum Stripe Depth:                                                                                                                 
Random reads and writes     The minimum stripe depth is twice the Oracle block size.                                                               Oracleо                                                                                                                                                                                                                             
Sequential reads      The minimum stripe depth is twice the value of DB_FILE_MULTIBLOCK_READ_COUNT, multiplied by the Oracle block size.

我想,我们这里应该还是以performance tunning的文档说明为准,这里的推荐值是至少是2* DB_FILE_MULTIBLOCK_READ_COUNT。
那么,最后我们再来总结下这3者之间的关系:

1) 9i 开始,支持多个block size并存,通常来讲DB_FILE_MULTIBLOCK_READ_COUNT是block size的倍数。
2) DB_FILE_MULTIBLOCK_READ_COUNT参数本身也受限制于操作系统,该值最大的oracle取值是128,所以如果你的
   操作系统不支持,你设置128k也是没用的。
3) oracle推荐我们设置strip depth,只是保证为2*DB_FILE_MULTIBLOCK_READ_COUNT,而惨死DB_FILE_MULTIBLOCK_READ_COUNT
   默认为16,那你只是需要设置为32k。然而,10gR2以后该参数自动调节了,所以我想都推荐设置strip size为128k,甚至更高。
   官方文档说,通常来讲设置为256k~1m可能是比较好的。

4) 由于DB_FILE_MULTIBLOCK_READ_COUNT对oracle cbo有着重大的影响,通常来讲不推荐去手工修改,以免影响cbo的计算。
补充:

1) oracle asm里面也有条带的说法,其实跟这个差不多,10g和11gR2中都分别是1m和8,如下:
Name                                               VALUE
-------------------------------------------------- --------------------
_asm_acd_chunks                                    1
_asm_allow_only_raw_disks                          TRUE
_asm_allow_resilver_corruption                     FALSE
_asm_ausize                                        1048576
_asm_blksize                                       4096
_asm_direct_con_expire_time                        120
_asm_disk_repair_time                              14400
_asm_droptimeout                                   60
_asm_emulmax                                       10000
_asm_emultimeout                                   0
_asm_fob_tac_frequency                             3

Name                                               VALUE
-------------------------------------------------- --------------------
_asm_instlock_quota                                0
_asm_kfdpevent                                     0
_asm_libraries                                     ufs
_asm_maxio                                         1048576
_asm_skip_resize_check                             FALSE
_asm_stripesize                                    131072
_asm_stripewidth                                   8
_asm_wait_time                                     18
_asmlib_test                                       0
_asmsid                                            asm
2) iops的测试


以前一直是通过如下脚本来测试iops的:
----iops.sql
ALTER SESSION SET nls_date_format='yyyy-mm-dd hh24:mi:ss';
spool iops.log
SELECT 'Number of Small Reads :' ||
       SUM(decode(name, 'physical read total IO requests', VALUE, 0) -
           decode(name,
                  'physical read total multi block requests',
                  VALUE,
                  0)),
       'Number of Small Writes:' ||
       SUM(decode(name, 'physical write total IO requests', VALUE, 0) -
           decode(name,
                  'physical write total multi block requests',
                  VALUE,
                  0)),
       'Number of Large Reads :' ||
       SUM(decode(name,
                  'physical read total multi block requests',
                  VALUE,
                  0)),
       'Number of Large Writes:' ||
       SUM(decode(name,
                  'physical write total multi block requests',
                  VALUE,
                  0)),
       'Total Bytes Read :' ||
       SUM(decode(name, 'physical read total bytes', VALUE, 0)),
       'Total Bytes Written :' ||
       SUM(decode(name, 'physical write total bytes', VALUE, 0)),
       sysdate
  FROM gv$sysstat;
spool off

在从11g开始,oracle引入了一个包,DBMS_RESOURCE_MANAGER.CALIBRATE_IO(实际上10g就引入了,只是没有评估io这个存储过程).
我这里有3个disk,分别对应3个磁盘组,如下:
Disk GROUP Name      Path              File Name            Fail GROUP           File SIZE (MB) Used SIZE (MB) Pct. Used
-------------------- ----------------- -------------------- -------------------- -------------- -------------- ---------
DATA1                /dev/sdd          DATA1_0000           DATA1_0000                    4,096          4,073     99.44
********************                                                             -------------- --------------
                                                                                          4,096          4,073

DATA2                /dev/sdb          DATA2_0000           DATA2_0000                    2,048            136      6.64
********************                                                             -------------- --------------
                                                                                          2,048            136

DATA3                /dev/sdc          DATA3_0000           DATA3_0000                    2,048            192      9.38
********************                                                             -------------- --------------
                                                                                          2,048            192

                                                                                 -------------- --------------
Grand Total:                                                                              8,192          4,401
下面用该存储过程来测试下:

SQL> SHOW USER
USER IS "SYS"
SQL> SET SERVEROUTPUT ON
DECLARE
SQL>   2    lat  INTEGER;
  3    iops INTEGER;
  4    mbps INTEGER;
  5  BEGIN
  6  -- DBMS_RESOURCE_MANAGER.CALIBRATE_IO (, , iops, mbps, lat);
  7     DBMS_RESOURCE_MANAGER.CALIBRATE_IO (3, 10, iops, mbps, lat);
  8  
  9    DBMS_OUTPUT.PUT_LINE ('max_iops = ' || iops);
10    DBMS_OUTPUT.PUT_LINE ('latency  = ' || lat);
11    dbms_output.put_line('max_mbps = ' || mbps);
12  END;
13  /

max_iops = 62
latency  = 19
max_mbps = 34

PL/SQL PROCEDURE successfully completed.

oracle也提供了一个试图来查询上面iops计算的结果:
SQL> SELECT MAX_IOPS,MAX_MBPS,MAX_PMBPS,LATENCY
  2  FROM dba_rsrc_io_calibrate;

  MAX_IOPS   MAX_MBPS  MAX_PMBPS    LATENCY
---------- ---------- ---------- ----------
        62         34         29         19

SQL>
需要注意的是,在生成环境测试,最好是在系统空闲时做,否则可能引发性能问题。 当然最好是多次测试,取最大值。

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】分析block size与db_file_multiblock_read_count的关系

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1395.html

Oracle研究中心

关键词:

block size

db_file_multiblock_read_count