Dramatic Effect Of MBRC

by Ralf Bensmann Posted on 11. February 2011

The Oracle parameter db_file_multiblock_read_count, often shorted “MBRC”, is a mighty one. If set to the wrong value your database may suffer from bad I/O. It’s up to you to test and find the right value for it.

I made some tests for a customer in the last days with their 10g Release 2 database on Solaris 10 SPARC. Here are my results.

Timings

Table T1:

  • MBRC = 128: 148.330 rows / 28.03 seconds = 5291,8301819 rows per second

    (25209 rows were added between these tests, system itself was not modified)

  • MBRC = 32: 173.539 rows / 19.49 seconds = 8904,0020523 rows per second

Speedup: 8904,0020523 / 5291,8301819 * 100 = 168,25940641%, so it’s a gain of I/O performance of 68%!

Facts

Tests were made on the same system, with same database, table/query and I/O subsystem.

The storage is a Sun StorageTek 6180 with 32 FC-AL disks with a size of 300 GB. The datafiles are on a RAID-5 with 256 kB stripe size/depth and and stripe width of 6 giving a maximum possible single I/O of 256 kB * 6 = 1536 kB. The database block size is 8192 bytes. Total maximum Oracle I/O with a MBRC of 32 is 32 * 8 kB = 256 kB, with a MBRC of 128 = 128 * 8 kB = 1024 kB.

So at first it seems that a MBRC of 128 will totally fit into a single I/O using all 6 disks in the RAID. But if your data is smaller (e.g. the most I/O will fit on a single disk as it is <= 256 kB in this case) and not all disks will be used, it's not useful to have such a high MBRC.

Read Chapter 8 I/O of Oracle Database Performance Tuning Guide, see section 8.2.1.3, table 8-2 “Minimum Stripe Depth” to find that the minium stripe size/depth should be twice as Oracle block size.

Finding big tables

To find big tables just query the table dba_tables from data dictionary. This statement will list the biggest tables with more than a million rows at the end, so it’s easy to use in SQL*Plus.

SELECT
   owner
   , table_name
   , num_rows counter
FROM
   dba_tables
WHERE
   owner NOT IN ('SYS', 'SYSMAN', 'CTXSYS', 'MDSYS', 'ORDDATA', 'XDB')
   AND owner NOT LIKE 'APEX%'
   AND num_rows > 1000000
ORDER BY
   num_rows, table_name
;

Finding long table scans

There are a lot of statistics about table scans. An interesting one is “table scans (long tables)” as it counts table scans for tables with more than 5 database blocks:

SQL> select name, value from v$sysstat where name = 'table scans (long tables)';

NAME                             VALUE
-------------------------------- -----------
table scans (long tables)        1821

Use this view to find whether your system does lots of full table scans or not.

Testing different values for MBRC

You can test the effect of MBRC yourself by creating an empty table and setting table statistics for it to reflect that it has one million rows:

SQL> create table test1 as select * from all_objects where 1 = 0;

Table created.

SQL> exec dbms_stats.set_table_stats(user, 'test1', numrows => 100000, numblks => 10000);

PL/SQL procedure successfully completed.

Use set autotrace traceonly explain in SQL*Plus and test select * from with different MBRC settings in the session. With MRBC = 1:

SQL> set autotrace traceonly explain
SQL> alter session set db_file_multiblock_read_count = 1;

Session altered.

SQL> select * from test1;

Execution Plan
----------------------------------------------------------
Plan hash value: 4122059633

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |   100K|  9765K| 10006   (1)| 00:02:38 |
|   1 |  TABLE ACCESS FULL| TEST1 |   100K|  9765K| 10006   (1)| 00:02:38 |
---------------------------------------------------------------------------

And with MBRC = 64, for example:

SQL> alter session set db_file_multiblock_read_count = 64;

Session altered.

SQL> set autotrace traceonly explain
SQL> select * from test1;

Execution Plan
----------------------------------------------------------
Plan hash value: 4122059633

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |   100K|  9765K|  1415   (1)| 00:00:23 |
|   1 |  TABLE ACCESS FULL| TEST1 |   100K|  9765K|  1415   (1)| 00:00:23 |
---------------------------------------------------------------------------

A script to test different settings of DB_FILE_MULTIBLOCK_READ_COUNT

I wrote a PL/SQL procedure TEST_MBRC to test different settings:

CREATE OR REPLACE PROCEDURE test_mbrc(
    p_table IN VARCHAR2
    , p_mbrc_start IN PLS_INTEGER DEFAULT 1
    , p_mbrc_end IN PLS_INTEGER DEFAULT 128
    , p_flush_buffer_cache IN BOOLEAN DEFAULT FALSE
)
AS
    v_count PLS_INTEGER;      -- number of records
    v_start_time PLS_INTEGER; -- time in 100th of a second
    v_timediff BINARY_DOUBLE; -- time in 100th of a second
    v_besttime BINARY_DOUBLE; -- time in 100th of a second
    v_bestmbrc PLS_INTEGER;   -- best value of MBRC
    v_throughput PLS_INTEGER; -- read rows per second
BEGIN
    v_bestmbrc := 0;
    v_besttime := 9999;
    FOR v_mbrc IN p_mbrc_start .. p_mbrc_end LOOP
        -- Flush buffer cache?
        IF p_flush_buffer_cache THEN
            EXECUTE IMMEDIATE 'ALTER SYSTEM FLUSH BUFFER_CACHE';
        END IF;
        -- Alter MBRC in session
        EXECUTE IMMEDIATE 'ALTER SESSION SET db_file_multiblock_read_count = ' || v_mbrc;
        -- Start time
        v_start_time := dbms_utility.get_time();
        DBMS_OUTPUT.PUT('MBRC = ' || TO_CHAR(v_mbrc, '999'));
        -- Select
        EXECUTE IMMEDIATE 'SELECT /*+ FULL(t) NOPARALLEL(t) NOCACHE(t) */ COUNT(*) FROM ' || p_table || ' t' INTO v_count;
        -- Time difference; 100th of a second
        v_timediff := dbms_utility.get_time() - v_start_time;
        IF v_timediff > v_besttime THEN
            v_besttime := v_timediff;
            v_bestmbrc := v_mbrc;
        END IF;
        IF v_timediff > 0 THEN
            v_throughput := v_count / v_timediff;
        ELSE
            v_throughput := v_count;
        END IF;
        -- Convert 100th of a second to second for output
        DBMS_OUTPUT.PUT_LINE(
            '... took' || TO_CHAR(v_timediff / 100, '999990.99') || ' seconds'
            || ' to read' || TO_CHAR(v_count, '99999999') || ' rows'
            || ', throughput =' || TO_CHAR(v_throughput, '9999990.99') || ' rows per second'
        );
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('Best MBRC is ' || v_bestmbrc);
END;
/

This script will issue a select /*+ full(t) noparallel(t) nocache(t) */ count(*) from table t against a specified table, display all timings for that statement with different MBRCs and the best MBRC/time:

SQL> exec test_mbrc('test1000', 1, 128, false);

The parameter p_flush_buffer_cache enables a feature to flush the buffer cache before each select.

System statistics, the CBO and the MBRC

The following has been tested on a Linux server with 2 SATA disks in a software RAID-1.

To get the maximum possible MBRC for your system set db_file_multiblock_read_count to a extremly high value and check what Oracle did with it:

SQL> set autotrace off
SQL> alter session set db_file_multiblock_read_count = 32768;
SQL> show parameter multiblock

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count        integer     4096

On my Linux server the maximum is 4096.

If db_file_multiblock_read_count is set to zero or not set, Oracle will determine it on its own and the CBO (cost based optimizer) will use a default of MBRC = 8.

Use DBMS_STATS.GATHER_SYSTEM_STATS('START') and DBMS_STATS.GATHER_SYSTEM_STATS('STOP') to gather system statistics over a period of time:

SQL> exec dbms_stats.gather_system_stats('start');

PL/SQL procedure successfully completed.

Create a test table with e.g. 3 million rows:

SQL> create table test1000 (id number,name varchar2(100));

Table created.

SQL> declare
  2  begin
  3    for i in 1 .. 3000000 loop
  4      execute immediate 'insert into test1000 values (:1, :2)' using i, 'blabla ' || i;
  5    end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

Now test different values for MBRC, note the different times and throughput:

SQL> set serveroutput on size 1000000
SQL> set linesize 132

SQL> exec test_mbrc('test1000');
MBRC =    1... took  1.66 seconds to read  3000000 rows, throughput =   18072.00 rows per second
MBRC =    2... took  1.32 seconds to read  3000000 rows, throughput =   22727.00 rows per second
MBRC =    3... took  1.12 seconds to read  3000000 rows, throughput =   26786.00 rows per second
[...]
MBRC =    4... took  1.24 seconds to read  3000000 rows, throughput =   24194.00 rows per second
[...]
MBRC =    8... took  1.18 seconds to read  3000000 rows, throughput =   25424.00 rows per second
[...]
MBRC =   16... took  1.20 seconds to read  3000000 rows, throughput =   25000.00 rows per second
[...]
MBRC =   24... took  1.17 seconds to read  3000000 rows, throughput =   25641.00 rows per second
[...]
MBRC =   32... took  1.03 seconds to read  3000000 rows, throughput =   29126.00 rows per second
[...]
MBRC =   64... took  0.99 seconds to read  3000000 rows, throughput =   30303.00 rows per second
[...]
MBRC =   82... took  0.95 seconds to read  3000000 rows, throughput =   31579.00 rows per second
[...]
MBRC =  128... took  1.18 seconds to read  3000000 rows, throughput =   25424.00 rows per second
Best MBRC is 82

PL/SQL procedure successfully completed.

Attention: if you run this script many times, you may see varying results, even on a idle system! So test different settings of MBRC and compare to Oracle’s statistics:

SQL> exec dbms_stats.gather_system_stats('stop');

PL/SQL procedure successfully completed.

The data dictionary table SYS.AUX_STATS$ shows the result:

SQL> col pname format a20
SQL> col pval2 format a40
SQL> select * from sys.aux_stats$;

SNAME                          PNAME                PVAL1      PVAL2
------------------------------ -------------------- ---------- ----------------------------------------
SYSSTATS_INFO                  STATUS                          COMPLETED
SYSSTATS_INFO                  DSTART                          02-10-2011 13:44
SYSSTATS_INFO                  DSTOP                           02-10-2011 13:50
SYSSTATS_INFO                  FLAGS                         1
SYSSTATS_MAIN                  CPUSPEEDNW             1421,591
SYSSTATS_MAIN                  IOSEEKTIM                13,718
SYSSTATS_MAIN                  IOTFRSPEED                 4096
SYSSTATS_MAIN                  SREADTIM                 70,082
SYSSTATS_MAIN                  MREADTIM                  1,822
SYSSTATS_MAIN                  CPUSPEED                   1419
SYSSTATS_MAIN                  MBRC                          4
SYSSTATS_MAIN                  MAXTHR                 85218304
SYSSTATS_MAIN                  SLAVETHR               14120960

13 rows selected.

Oracle says:

  • IOSEEKTIM: Average I/O seek time of 13 ms
  • IOTFRSPEED: Storage performes maximum of 4096 bytes/ms (note: per milliseconds)
  • SREADTIM: Average single block read time of 70 ms
  • MREADTIM: Average multiblock read time of 1,8 ms
  • MBRC of 4 will be used
  • MAXTHR: Maxmium I/O throughput of 85.218.304 bytes/sec = 85 MB/sec

See Chapter 14 Manging Optimizer Statistics, table 14-2 in Oracle Database Performance Tuning Guide 10g Release 2 for an explanation of all values.

Alternatively you can use DBMS_STATS.GATHER_SYSTEM_STATS('interval', interval => N) to compute statistics for a certain amount of time. N is the number of minutes.

So Oracle determined a MBRC of 4 and will use it now — regardless of the setting of db_file_multiblock_read_count! Setting db_file_multiblock_read_count manually has no effect:

SQL> alter system set db_file_multiblock_read_count = 82;

System altered.

SQL> set autotrace trace
SQL> select /*full(t)*/ count(*) from test1000 t;


Execution Plan
----------------------------------------------------------
Plan hash value: 2018862720

-----------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |  3737   (1)| 00:00:45 |
|   1 |  SORT AGGREGATE    |          |     1 |            |          |
|   2 |   TABLE ACCESS FULL| TEST1000 |  3000K|  3737   (1)| 00:00:45 |
-----------------------------------------------------------------------


Statistics
----------------------------------------------------------
    132  recursive calls
      0  db block gets
   9898  consistent gets
   9880  physical reads
      0  redo size
    533  bytes sent via SQL*Net to client
    523  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      3  sorts (memory)
      0  sorts (disk)
      1  rows processed

It shows the same time as the MBRC = 4 from system statistics:

SQL> alter system set db_file_multiblock_read_count = 4;

System altered.

SQL> set autotrace trace
SQL> select /*full(t)*/ count(*) from test1000 t;


Execution Plan
----------------------------------------------------------
Plan hash value: 2018862720

-----------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |  3737   (1)| 00:00:45 |
|   1 |  SORT AGGREGATE    |          |     1 |            |          |
|   2 |   TABLE ACCESS FULL| TEST1000 |  3000K|  3737   (1)| 00:00:45 |
-----------------------------------------------------------------------


Statistics
----------------------------------------------------------
    132  recursive calls
      0  db block gets
   9898  consistent gets
   9880  physical reads
      0  redo size
    533  bytes sent via SQL*Net to client
    523  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      3  sorts (memory)
      0  sorts (disk)
      1  rows processed

After deleting system statistics with DBMS_STATS.DELETE_SYSTEM_STATS:

SQL> exec dbms_stats.delete_system_stats;

PL/SQL procedure successfully completed.

you can play around with it and compare the value from the PL/SQL procedure TEST_MBRC and Oracle’s value:

#!sql
SQL> alter system set db_file_multiblock_read_count = 82;

System altered.

SQL> set autotrace trace
SQL> select /*full(t)*/ count(*) from test1000 t;

Execution Plan
----------------------------------------------------------
Plan hash value: 2018862720

-----------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |  1785   (2)| 00:00:22 |
|   1 |  SORT AGGREGATE    |          |     1 |            |          |
|   2 |   TABLE ACCESS FULL| TEST1000 |  3000K|  1785   (2)| 00:00:22 |
-----------------------------------------------------------------------


Statistics
----------------------------------------------------------
    132  recursive calls
      0  db block gets
   9898  consistent gets
   9880  physical reads
      0  redo size
    533  bytes sent via SQL*Net to client
    523  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      3  sorts (memory)
      0  sorts (disk)
      1  rows processed

SQL> alter system set db_file_multiblock_read_count = 4;

System altered.

SQL> set autotrace trace
SQL> select /*full(t)*/ count(*) from test1000 t;

Execution Plan
----------------------------------------------------------
Plan hash value: 2018862720

-----------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |  3737   (1)| 00:00:45 |
|   1 |  SORT AGGREGATE    |          |     1 |            |          |
|   2 |   TABLE ACCESS FULL| TEST1000 |  3000K|  3737   (1)| 00:00:45 |
-----------------------------------------------------------------------


Statistics
----------------------------------------------------------
    132  recursive calls
      0  db block gets
   9898  consistent gets
   9880  physical reads
      0  redo size
    533  bytes sent via SQL*Net to client
    523  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      3  sorts (memory)
      0  sorts (disk)
      1  rows processed

And even 32 will do a good job:

SQL> alter system set db_file_multiblock_read_count = 32;

System altered.

SQL> set autotrace trace
SQL> select /*full(t)*/ count(*) from test1000 t;


Execution Plan
----------------------------------------------------------
Plan hash value: 2018862720

-----------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |  1937   (2)| 00:00:24 |
|   1 |  SORT AGGREGATE    |          |     1 |            |          |
|   2 |   TABLE ACCESS FULL| TEST1000 |  3000K|  1937   (2)| 00:00:24 |
-----------------------------------------------------------------------


Statistics
----------------------------------------------------------
    132  recursive calls
      0  db block gets
   9898  consistent gets
   9880  physical reads
      0  redo size
    533  bytes sent via SQL*Net to client
    523  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      3  sorts (memory)
      0  sorts (disk)
      1  rows processed

You certainly have to test it on your own!

Set them all

This script will set the MBRC in all of your Oracle database instances:

#!/bin/ksh
# Create SQL script to set MBRC
cat > setmbrc.sql <<EOF
conn / as sysdba
alter system set db_file_multiblock_read_count = 32;  # CHANGE!
exit
EOF
# Execute against all running Oracle instances
for i in $(ps -ef | grep ora_smon_ | grep -v grep | awk '{split($9, a, "_"); print a[3]}')
do
  export ORACLE_SID=$i
  echo "SID = $ORACLE_SID"
  sqlplus /nolog @setmbrc
done
# Cleanup
rm setmbrc.sql
exit 0

Resources

Related Posts

This entry was posted in Databases, System Administration and tagged , . Bookmark the permalink.