Oracle db_file_mulitblock_read_count Parameter

The Oracle db_file_mulitblock_read_count parameter Oracle DB_FILE_MULTIBLOCK_READ_COUNT is an important global parameter in Oracle, which can affect the system level and sessioin level. It is mainly used to minimize the number of data blocks that Oracle can read at a time in order during table scan. Generally, we can see that the value of this parameter is added when waiting for the event db file scattered read in top events. But does the increase in the value of DB_FILE_MULTIBLOCK_READ_COUNT certainly reduce db file scattered read? This document describes the settings of DB_FILE_MULTIBLOCK_READ_COUNT and provides an example. 1. The DB_FILE_MULTIBLOCK_READ_COUNT (MBRC) parameter DB_FILE_MULTIBLOCK_READ_COUNT is abbreviated as (MBRC ). This parameter is an important parameter for minimizing table scanning. It is used to specify the number of data blocks that Oracle can read in sequence at a time. Theoretically, the larger the value, the more data blocks can be read. The total number of I/O required for full table scan, full index scan, and quick index scan depends on this parameter, the size of the table, and whether parallel queries are used. Oracle 10gR2 will automatically set this parameter value based on the corresponding operating system and buffer cache in an optimal way. Generally, this value is 1 MB/db_block_size. If the maximum I/O is 1 MB and the block size is 8 KB, the parameter value is 128. If the maximum I/O is 64 KB and the block is 8 KB, the parameter value is 8. For the OLTP and batch environments, the value of this parameter is 4 to 16, and the DSS environment should be set to a value greater than 16 or greater. Changes in this parameter have a holistic impact on the database performance. Excessive settings will lead to a large number of changes in the SQL access path. For example, the original index scan tends to use full table scanning. Follow Oracle's advice to use oracle's automatically set value after 10g R2 as much as possible. 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 1 Mb and is referred to as SSTIOMA X. 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. from the above description, we can see that after Oracle 9.2, there is a stuff named SSTIOMAX, which limits the MBRC settings. Most SSTIOMAX platforms have a maximum I/O of 1 MB and db_block_size of 8 KB. Therefore, the maximum value of the MBRC parameter is usually 128. 128*8 KB = 1 mb. If the value is greater than 1 MB, 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: Memory = min (1048576/db_block_size, memory/(sessions * db_block_size) Without WORKLOAD stats, CBO computes multiblock reads: io_cost = blocks/(1.6765 * power (db_file_multiblock_read_count, 0.6581) With WORKLOAD stats, then: io_cost = blocks/mbrc * mreadtim/sreadtim the maximum I/o db_blocks_size tablespace caused by different settings Block size db_file_multiblock_read_count bytes --------------- ------------------ bytes Bytes 8 k 8 k 32 8*32 = 256kb (I/o) 8 k 4 k 32 8*32/4 = 64kb (I/o) 8 k 8 k not explicitly set determined by OS and db_cache_size 8 k 8 k> 128 8*128 = 1 MB (I/o) 4. What causes multiple read segments to be less than pre-set a at a time? When reading a segment header, it is obvious that an extent usually contains a segment header) b. Physical reads cannot span multiple zones (extent) c. Some data blocks If it is already in the cache, it will not be read again from the I/O subsystem, unless it is directly read (direct path read ). 5. Demonstrate the block (system level) for a single read of MBRC with different values) [SQL] case where a and MBRC are 16 -- demo environment sys @ SYBO2SZ> ho cat/etc/issue Welcome to SUSE Linux Enterprise Server 10 SP3 (x86_64) -Kernel \ r (\ l ). sys @ SYBO2SZ> select * from v $ version where rownum = 1; BANNER orders Oracle Database 10g Release 10.2.0.3.0-64bit Production -- View MBRC value sys @ SYBO2SZ> show parameter read_count; NAME Type value values ----------- ---------------------------- db_file_multiblock_read_count integer 16 -- view the hidden parameter sys @ SYBO2SZ> @ hidden_para Enter value for para: read_count ksppinm ksppstvl describ ------------------------------------- ------------------ success _ db_file_exec_read_count 48 multiblock read count for reg Ular clients _ route 8 multiblock read count for regular clients _ limit 11 number of noncontiguous db blocks to be prefetched _ sort_multiblock_read_count 2 multi-block read count for sort sys @ SYBO2SZ> get limit 1 * alter session set events '10046 trace name context forever, level 8'; sys @ ORA11G> @ trace_enable_cur Session altered. sys @ SYBO2SZ> select count (*) from scott. big_table where owner = 'sys'; COUNT (*) ---------- 468066 SYS @ SYBO2SZ> get trace_disable_cur. SQL 1 * alter session set events '10046 trace name context off '; sys @ SYBO2SZ> @ trace_disable_cur. SQL Session altered. sys @ SYBO2SZ> @ my_env spid sid serial # username program ----------------------------------------------------------------------------------------------- 24472 1094 30 robin oracle @ SZDB (TNS V1-V3) sys @ SYBO2SZ> @ get_spec_sess_trace_file Enter value for input_sid: 1094 Enter value for input_serial: 30 sid serial # SPID TRACE_FILE ---------- -------------- limit 1094 30 24472/u02/database/SYBO2SZ/udump/blocks -- The following blocks is the number of chunks for a single read. sys @ SYBO2SZ> ho grep scatter/u02/database/S YBO2SZ/udump/sybo2sz_ora_24472.trc | tail WAIT #7: nam = 'db file scattered read' ela = 1491 file # = 5 block # = 18363 blocks = 16 obj # = 52884 tim = 1337624821997106 WAIT #7: nam = 'db file scattered read' ela = 5148 file # = 5 block # = 18443 blocks = 2 obj # = 52884 tim = 1337624822005413 WAIT #7: nam = 'db file scattered read' ela = 32363 file # = 5 block # = 18571 blocks = 16 obj # = 52884 tim = 1337624822041788 WAIT #7: nam = 'db file scat Tered read 'ela = 1930 file # = 5 block # = 18587 blocks = 16 obj # = 52884 tim = 1337624822044227 WAIT #7: nam = 'db file scattered read' ela = 345 file # = 5 block # = 18603 blocks = 16 obj # = 52884 tim = 1337624822045165 WAIT #7: nam = 'db file scattered read' ela = 1712 file # = 5 block # = 18619 blocks = 16 obj # = 52884 tim = 1337624822047555 WAIT #7: nam = 'db file scattered read' ela = 58 file # = 5 block # = 18635 blocks = 2 obj # = 52884 tim = 133 7624822048219 B. When MBRC is greater than 128, sys @ SYBO2SZ> alter system set db_file_multiblock_read_count = 256; System altered. sys @ SYBO2SZ> show parameter read_count; ---> from this query, name type value ------------------------------------------------ -------------------------------------------- db_file_multiblock_read_count integer 128 is not effective in cases where MBRC is 128, only the final results are listed below -- note that buffer cache should be cleared before different MBRC tests are performed (alter system f Lush buffer_cache) sys @ SYBO2SZ> ho grep scatter/u02/database/SYBO2SZ/udump/sybo2sz_ora_24750.trc | tail WAIT #7: nam = 'db file scattered read' ela = 2070 file # = 5 block # = 20875 blocks = 126 obj # = 53208 tim = 1337625626875769 WAIT #7: nam = 'db file scattered read' ela = 1991 file # = 5 block # = 21003 blocks = 126 obj # = 53208 tim = 1337625626880199 WAIT #7: nam = 'db file scattered read' ela = 1970 file # = 5 block # = 21131 blo Cks = 126 obj # = 53208 tim = 1337625626884412 WAIT #7: nam = 'db file scattered read' ela = 2078 file # = 5 block # = 21259 blocks = 126 obj # = 53208 tim = 1337625626888846 WAIT #7: nam = 'db file scattered read' ela = 2035 file # = 5 block # = 21387 blocks = 126 obj # = 53208 tim = 1337625626893039 WAIT #7: nam = 'db file scattered read' ela = 2040 file # = 5 block # = 21515 blocks = 126 obj # = 53208 tim = 1337625626897021 WAIT #7: nam = 'db file SC Attered read 'ela = 1048 file # = 5 block # = 21643 blocks = 66 obj # = 53208 tim = 1337625626900379 c. If MBRC is the default value, sys @ SYBO2SZ> alter system reset db_file_multiblock_read_count scope = spfile sid = '*'; system altered. sys @ SYBO2SZ> show parameter read_count; name type value =------------- ------------------------------ db_file_multiblock_read_count integer 128 sys @ SYBO2SZ> startup Force; ---> after reset mbrc, restart db sys @ SYBO2SZ> show parameter read_count name type value =----------- restart when integer 44 sys @ SYBO2SZ> ho grep scatter/u02/database/SYBO2SZ/udump /sybo2sz_ora_24890.trc | tail WAIT #14: nam = 'db file scattered read' ela = 682 file # = 5 block # = 21431 blocks = 44 obj # = 53208 tim = 1337626216154 292 WAIT #14: nam = 'db file scattered read 'ela = 4497 file # = 5 block # = 21475 blocks = 38 obj # = 53208 tim = 1337626216163586 WAIT #14: nam = 'db file scattered read' ela = 586 file # = 5 block # = 21515 blocks = 44 obj # = 53208 tim = 1337626216166611 WAIT #14: nam = 'db file scattered read' ela = 691 file # = 5 block # = 21559 blocks = 44 obj # = 53208 tim = 1337626216170137 WAIT #14: nam = 'db file scattered read' ela = 4860 file # = 5 block # = 21603 blocks = 38 obj # = 53208 tim = 1337626216177698 WAIT #14: nam = 'db file scattered read' ela = 592 file # = 5 block # = 21643 blocks = 44 obj # = 53208 tim = 1337626216180540 WAIT #14: nam = 'db file scattered read' ela = 292 file # = 5 block # = 21687 blocks = 22 obj # = 53208 tim = 1337626216183493 6. Demonstrate the time consumed by different MBRC (session level) [SQL] -- The following PL/SQL code is used to test the number of blocks read and the consumed time of different MBRC. scott @ SYBO2SZ> get assess_mbrc. SQL 1 DECLARE 2 l_count PL S_INTEGER; 3 l_time NUMBER (); 4 l_starting_time PLS_INTEGER; 5 l_ending_time PLS_INTEGER; 6 l_blocks PLS_INTEGER; 7 l_starting_blocks PLS_INTEGER; 8 l_ending_blocks PLS_INTEGER; 9 l_dbfmbrc PLS_INTEGER; 10 BEGIN 11 dbms_output.put_line ('dbfmbrc blocks seconds '); 12 dbms_output.put_line (' ------- ------ ------- '); 13 FOR I IN 1 .. 32 14 LOOP 15 l_dbfmbrc: = I * 4; 16 EXECUTE IMMEDIATE 'alter SESSI On set db_file_multiblock_read_count = '| l_dbfmbrc; 17 execute immediate 'alter system set events' 'immediate trace name FLUSH_CACHE '''; 18 SELECT value INTO l_starting_blocks 19 FROM v $ mystat ms join v $ statname USING (statistic #) 20 WHERE name = 'physical reads'; 21 l_starting_time: = dbms_utility.get_time (); 22 SELECT count (*) INTO l_count FROM big_table; 23 l_ending_time: = dbms_utility.get_ti Me (); 24 SELECT value INTO l_ending_blocks 25 FROM v $ mystat ms join v $ statname USING (statistic #) 26 WHERE name = 'physical reads'; 27 l_time: = l_ending_time-l_starting_time; 28 l_blocks: = l_ending_blocks-l_starting_blocks; 29 dbms_output.put_line (l_dbfmbrc | ''| l_blocks |'' | to_char (l_time) | 'hsec '); 30 end loop; 31 * END; 32/dbfmbrc blocks seconds ------- ------ ------- 4 2119 18 hsec 8 2093 15 hsec 12 2093 7 hsec 16 2093 8 hsec 20 2093 9 hsec 24 2093 8 hsec 28 2093 9 hsec 32 2093 8 hsec 36 2093 8 hsec 40 2093 8 hsec 44 2093 9 hsec 48 2093 9 hsec 52 2093 9 hsec 56 2093 8 hsec 60 2093 9 hsec 64 2093 8 hsec 68 2093 9 hsec 72 2093 8 hsec 76 2093 9 hsec 80 2093 9 hsec 84 2093 9 hsec 88 2093 8 hsec 92 2093 9 hsec 96 2093 9 hsec 100 2093 9 hsec 104 2093 8 hsec 108 2093 9 hsec 112 2093 116 8 hsec 2093 120 2093 9 hsec 8 hsec 124 2093 9 hsec 128 2093 9 hsec PL/SQL procedure successfully completed. -- from the above results, we can see that when MBRC is set to 16, the number of read blocks and the consumed time are basically not affected by MBRC. -- Therefore, increasing the MBRC value alone cannot completely solve db file scattered read. -- How to set the number or retain the default value as recommended by Oracle. Then adjust the value based on the default value. -- Reference: [841444.1] [473740.1] [1398860.1] [291239.1]