Shulou(Shulou.com)06/01 Report--

Oracle parameter (db_file_multiblock_read_count) is optimized by case study.

Application environment:

Operating system: RedHat EL55

Oracle: Oracle 10gR2

Oracle DB_FILE_MULTIBLOCK_READ_COUNT is an important global parameter of Oracle, which can affect system level and sessioin level. It is mainly used to set the number of data blocks that Oracle can read sequentially at a time when a table scan is performed. In general, we consider increasing the value of this parameter when we see the wait event db file scattered read in top events.

1. Parameter DB_FILE_MULTIBLOCK_READ_COUNT (MBRC)

The parameter DB_FILE_MULTIBLOCK_READ_COUNT is abbreviated to (MBRC).

This parameter is an important parameter for minimizing table scans and is used to specify the number of blocks that Oracle can read sequentially at a time. In theory, the higher the value, the more blocks can be read.

The total number of Icano required to achieve full table scan, full index scan and fast index scan depends on this parameter, the size of the table itself, the use of parallelism, and so on.

Oracle 10gR2 will automatically set the value of this parameter in an optimal way according to the corresponding operating system and buffer cache. Typically, this value is 1MB/db_block_size.

If the maximum block O is 1MB, and the size of the parameter is 8KB, the value of the parameter is 128. If the maximum Ibind O is 64KB and the field block is 8KB, the value of the parameter is 8.

For OLTP and batch environments, the value of this parameter is 4 to 16. DSS environments should be set to values greater than 16 or greater.

The change of this parameter has an overall impact on database performance, too large setting will lead to a large number of SQL access path changes, such as the original index scan tends to use full table scan.

Use the value automatically set by oracle as much as possible after 10g R2, as recommended by Oracle.

2. Parameters DB_FILE_MULTIBLOCK_READ_COUNT and 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.

As you can see from the above description, after Oracle 9.2, there is something called SSTIOMAX that limits the setting of MBRC.

Since the maximum one-time MBRC O for most SSTIOMAX platforms is 1MB, the MBRC parameter usually has a maximum value of 128MB. 128*8kb=1mb .

For cases where the setting is greater than 1MB, that is, MBRC*db_block_size > SSTIOMAX, the set value does not take effect, but uses the maximum MBRC value that conforms to SSTIOMAX.

3. How to calculate 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))

Set the DB_FILE_MULTIBLOCK_READ_COUNT to take full advantage of the size of the operating system Istroke O buffer. DB_FILE_MULTIBLOCK_READ_COUNT show parameter mult should be considered

NAME TYPE VALUE

-

Db_file_multiblock_read_count integer 128

1) View the extent allocation in the table

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 8T1 1 65536 8T1 2 65536 8T1 3 65536 8T1 4 65536 8T1 5 65536 8T1 6 65536 8T1 7 65536 8T1 8 65536 8T1 9 65536 8T1 10 65536 8T1 11 65536 8T1 12 65536 8T1 13 65536 8T1 14 65536 8T1 15 65536 8T1 16 1048576 128SEGMENT_NAME EXTENT_ID BYTES BLOCKS -T1 17 1048576 128T1 18 1048576 128T1 19 1048576 128T1 20 1048576 128T1 21 1048576 12822 rows selected.2) configure 10046 for analysis

14:48:00 SCOTT@ test1 > alter session set events' 10046 trace name context forever,level 8 accountant session altered.14:48:57 SCOTT@ test1 > select count (*) from T1; COUNT (*)-500214 49 trace name context forever,level 09 SCOTT@ test1 > alter session set events' 10046 trace name context off';Session altered.3) View trace file

[oracle@rh7 ~] $more / u01/app/oracle/diag/rdbms/test1/test1/trace/test1_ora_4160.trc

WAIT # 2: nam='db file sequential read' ela= 9 file#=11 block#=691 blocks=1 obj#=16394 tim=1408604404294713WAIT # 2: nam='Disk file operations I ela= O'ela= 26 FileOperation=2 fileno=4 filetype=2 obj#=16394 tim=1408604404294846WAIT # 2: nam='db file sequential read' ela= 9 file#=4 block#=143 blocks=1 obj#=16394 tim=1408604404294872WAIT # 2: nam='db file scattered read' ela= 23 file#=4 block#=157 blocks=3 obj#=16394 tim=1408604404294998WAIT # 2: nam='db file sequential read' ela= 8 file#=4 block#=164 blocks=1 obj#=16394 tim=1408604404295041WAIT # 2: nam='db File sequential read' ela= 8 file#=11 block#=727 blocks=1 obj#=16394 tim=1408604404295069WAIT # 2: nam='db file sequential read' ela= 8 file#=4 block#=183 blocks=1 obj#=16394 tim=1408604404295097WAIT # 2: nam='db file scattered read' ela= 33 file#=4 block#=187 blocks=5 obj#=16394 tim=1408604404295156WAIT # 2: nam='db file sequential read' ela= 8 file#=4 block#=199 blocks=1 obj#=16394 tim=1408604404295191WAIT # 2: nam='db file scattered read' ela= 51 file#=11 block#=153 blocks=8 obj#=16394 tim=1408604404295272WAIT # 2: nam='db file scattered read' ela= 50 file#=11 block#=203 blocks=8 obj#=16394 tim=1408604404295355WAIT # 2: nam='db file scattered read' ela= 52 file#=11 block#=213 blocks=8 obj#=16394 tim=1408604404295442. As can be seen from the above files, the number of blocks read each time in "db file scattered read" (blocks=8) does not exceed the size of blocks in extent (8), and the number of blocks read each time in "db file sequential read" (blocks) is 1.

Case 2:

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

NAME TYPE VALUE

-

Db_file_multiblock_read_count integer 128

1) create a new 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 128T2 USERS 1 1048576 128

2) configure 10046 for analysis

15:11:00 SCOTT@ test1 > alter session set events' 10046 trace name context forever,level 8 accountant session altered.15:14:57 SCOTT@ test1 > select count (*) from T2; COUNT (*)-3200215 15 trace name context forever,level 15 trace 09 SCOTT@ test1 > alter session set events' 10046 trace name context off';Session altered.3) View trace file

[oracle@rh7 ~] $more / u01/app/oracle/diag/rdbms/test1/test1/trace/test1_ora_4260.trc

PARSE # 5 file#=11 block#=512 blocks=1 obj#=16404 tim=1408605696797838WAIT file#=11 block#=512 blocks=1 obj#=16404 tim=1408605696797838WAIT # 5: nam='db file sequential read' ela= 15 file#=11 block#=514 blocks=1 obj#=16404 tim=1408605696797913WAIT # 5: nam='db file scattered read' ela= 1123 file#=11 block#=528 blocks=48 obj#=16404 tim=1408605696799202

As can be seen from the above files, the number of blocks read each time in "db file scattered read" (blocks=48) does not exceed the size of blocks in extent (128s), and the number of blocks read each time (blocks) in "db file sequential read" is 1.

Note:

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.