通過案例學調優之--Oracle參數(db_file_multiblock_read_count)

應用環境：

操作系統： RedHat EL55

Oracle： ? Oracle 10gR2

Oracle DB_FILE_MULTIBLOCK_READ_COUNT是Oracle比較重要的一個全局性參數，可以影響系統級別及sessioin級別。主要是用于設置最小化表掃描時Oracle一次按順序能夠讀取的數據塊數。通常情況下，我們看到top events中的等待事件db file scattered read時會考慮到增加該參數的值。

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

設置DB_FILE_MULTIBLOCK_READ_COUNT以充分利用操作系統I/O緩沖區的大小。應考慮DB_FILE_MULTIBLOCK_READ_COUNT <= 操作系統I/O緩沖區 / Oracle Block的大小，如果DB_FILE_MULTIBLOCK_READ_COUNT設置的太大，會使優化器認為全表掃描更有效而改變執行計劃，然后實際情況并非如此。

如果必須創建小的區間，創建其大小是操作系統I/O緩沖區大小的整數倍

設置區間尺寸大小的考慮思路應該是合理的利用Oracle的能力以便在全表掃描時執行多塊存取，同時讀操作又是不能跨區間的。舉個例子，假設操作系統I/O緩沖區大小是64KB，考察讀取一個640KB大小的表，如果設置為每個區間64KB，一共10個區間，如果執行全表掃描，則Oracle需要10次讀操作(相當于一次讀一個區間)；如果設置為一個640KB的區間，則Oracle還是需要10次讀操作(因為操作系統I/O緩沖區大小是64KB)，可見壓縮區間并不能提高性能；如果設置為每個區間80KB，一共8個區間，則每個區間Oracle需要讀兩次，第一次讀64KB，第二次讀這個區間剩余的16KB(讀操作不能跨區間)，所以總共需要16次讀操作(相當于一次讀一個區間)。區間尺寸的設置對性能的影響是顯而易見的。

案例分析：

案例1

15:21:10 SYS@ test1 >show parameter mult

NAME ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? TYPE ? ? ? ?VALUE

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

db_file_multiblock_read_count ? ? ? ?integer ? ? 128

1)查看表中extent分配

14:46:03 SCOTT@ test1 >col segment_name for a20

14:46:15 SCOTT@ test1 >select segment_name,extent_id,bytes,blocks from user_extents

14:46:36 ? 2 ? where segment_name=‘T1‘;SEGMENT_NAME??????????EXTENT_ID??????BYTES?????BLOCKS

--------------------?----------?----------?----------

T1????????????????????????????0??????65536??????????8

T1????????????????????????????1??????65536??????????8

T1????????????????????????????2??????65536??????????8

T1????????????????????????????3??????65536??????????8

T1????????????????????????????4??????65536??????????8

T1????????????????????????????5??????65536??????????8

T1????????????????????????????6??????65536??????????8

T1????????????????????????????7??????65536??????????8

T1????????????????????????????8??????65536??????????8

T1????????????????????????????9??????65536??????????8

T1???????????????????????????10??????65536??????????8

T1???????????????????????????11??????65536??????????8

T1???????????????????????????12??????65536??????????8

T1???????????????????????????13??????65536??????????8

T1???????????????????????????14??????65536??????????8

T1???????????????????????????15??????65536??????????8

T1???????????????????????????16????1048576????????128

SEGMENT_NAME??????????EXTENT_ID??????BYTES?????BLOCKS

--------------------?----------?----------?----------

T1???????????????????????????17????1048576????????128

T1???????????????????????????18????1048576????????128

T1???????????????????????????19????1048576????????128

T1???????????????????????????20????1048576????????128

T1???????????????????????????21????1048576????????128

22?rows?selected.

2)配置10046進行分析14:48:00?SCOTT@?test1?>alter?session?set?events?‘10046?trace?name?context?forever,level?8‘;

Session?altered.

14:48:57?SCOTT@?test1?>select?count(*)?from?t1;

COUNT(*)

----------

5002

14:49:09?SCOTT@?test1?>alter?session?set?events?‘10046?trace?name?context?off‘;

Session?altered.

3)查看trace文件

[[email?protected] ~]$ more ?/u01/app/oracle/diag/rdbms/test1/test1/trace/test1_ora_4160.trcWAIT?#2:?nam=‘db?file?sequential?read‘?ela=?9?file#=11?block#=691?blocks=1?obj#=16394?tim=1408604404294713

WAIT?#2:?nam=‘Disk?file?operations?I/O‘?ela=?26?FileOperation=2?fileno=4?filetype=2?obj#=16394?tim=1408604404294846

WAIT?#2:?nam=‘db?file?sequential?read‘?ela=?9?file#=4?block#=143?blocks=1?obj#=16394?tim=1408604404294872

WAIT?#2:?nam=‘db?file?scattered?read‘?ela=?23?file#=4?block#=157?blocks=3?obj#=16394?tim=1408604404294998

WAIT?#2:?nam=‘db?file?sequential?read‘?ela=?8?file#=4?block#=164?blocks=1?obj#=16394?tim=1408604404295041

WAIT?#2:?nam=‘db?file?sequential?read‘?ela=?8?file#=11?block#=727?blocks=1?obj#=16394?tim=1408604404295069

WAIT?#2:?nam=‘db?file?sequential?read‘?ela=?8?file#=4?block#=183?blocks=1?obj#=16394?tim=1408604404295097

WAIT?#2:?nam=‘db?file?scattered?read‘?ela=?33?file#=4?block#=187?blocks=5?obj#=16394?tim=1408604404295156

WAIT?#2:?nam=‘db?file?sequential?read‘?ela=?8?file#=4?block#=199?blocks=1?obj#=16394?tim=1408604404295191

WAIT?#2:?nam=‘db?file?scattered?read‘?ela=?51?file#=11?block#=153?blocks=8?obj#=16394?tim=1408604404295272

WAIT?#2:?nam=‘db?file?scattered?read‘?ela=?50?file#=11?block#=203?blocks=8?obj#=16394?tim=1408604404295355

WAIT?#2:?nam=‘db?file?scattered?read‘?ela=?52?file#=11?block#=213?blocks=8?obj#=16394?tim=1408604404295442

......

從以上文件可以看出，在“db file scattered read”中每次讀取的數據塊個數(blocks=8)不超過extent中blocks的大小(8個)；在“db file sequential read”中每次讀取的數據塊個數(blocks)為1個。

案例2：

15:21:10 SYS@ test1 >show parameter mult

NAME ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? TYPE ? ? ? ?VALUE

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

db_file_multiblock_read_count ? ? ? ?integer ? ? 128

1)創建新的segment

15:08:28 SCOTT@ test1 >create table t2 (id int)

15:08:35 ? 2? storage (initial 2048k next 2048k pctincrease 0);

Table created.

15:08:41 SCOTT@ test1 >col segment_name for a20

15:08:53 SCOTT@ test1 >select segment_name,tablespace_name,extent_id,bytes,blocks from user_extents

15:09:19 ? 2 ? where segment_name=‘T2‘;SEGMENT_NAME?????????TABLESPACE_NAME?????????????????EXTENT_ID??????BYTES?????BLOCKS

--------------------?------------------------------?----------?----------?----------

T2???????????????????USERS???????????????????????????????????0????1048576????????128

T2???????????????????USERS???????????????????????????????????1????1048576????????128

2)配置10046進行分析15:11:00?SCOTT@?test1?>alter?session?set?events?‘10046?trace?name?context?forever,level?8‘;

Session?altered.

15:14:57?SCOTT@?test1?>select?count(*)?from?t2;

COUNT(*)

----------

32002

15:15:09?SCOTT@?test1?>alter?session?set?events?‘10046?trace?name?context?off‘;

Session?altered.

3)查看trace文件

[[email?protected] ~]$ more ?/u01/app/oracle/diag/rdbms/test1/test1/trace/test1_ora_4260.trcPARSE?#5:c=0,e=302,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,plh=0,tim=1408605696796384

EXEC?#5:c=1000,e=1131,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,plh=3321871023,tim=1408605696797594

WAIT?#5:?nam=‘db?file?sequential?read‘?ela=?75?file#=11?block#=512?blocks=1?obj#=16404?tim=1408605696797838

WAIT?#5:?nam=‘db?file?sequential?read‘?ela=?15?file#=11?block#=514?blocks=1?obj#=16404?tim=1408605696797913

WAIT?#5:?nam=‘db?file?scattered?read‘?ela=?1123?file#=11?block#=528?blocks=48?obj#=16404?tim=1408605696799202

從以上文件可以看出，在“db file scattered read”中每次讀取的數據塊個數(blocks=48)不超過extent中blocks的大小(128個)；在“db file sequential read”中每次讀取的數據塊個數(blocks)為1個。

附注：

原文：http://tiany.blog.51cto.com/513694/1543199

創作挑戰賽新人創作獎勵來咯，堅持創作打卡瓜分現金大獎

總結

以上是生活随笔為你收集整理的oracle dbfile数,通过案例学调优之--Oracle参数（db_file_multiblock_read_count）的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。