System Statistics – “Exadata” Mode

Here’s a very quick note on a relatively recent addition to System Statistics. It’s the so called “Exadata mode”. This mode is intended to give the optimizer a little more info about the storage system on the Exadata platform. Here’s some info from an MOS note.

——————————————————————————————
Oracle Sun Database Machine Setup/Configuration Best Practices [ID 1274318.1]

Verify Exadata specific optimizer statistics have been gathered

 
Benefit / Impact

Gathering Exadata specific system statistics ensure the optimizer is aware of Exadata scan speed. Accurately accounting for the speed of scan operations will ensure the Optimizer chooses an optimal execution plan in a Exadata environment. The following command gathers Exadata specific system statistics

exec dbms_stats.gather_system_stats(‘EXADATA’);

Risk:

Lack of Exadata specific stats can lead to less performant optimizer plans.

Action / Repair:

To see if Exadata specific optimizer stats have been gathered, run the following query on a system with at least 11.2.0.2 BP18 or 11.2.0.2 BP8 Oracle software. If PVAL1 returns null or is not set, Exadata specific stats have not been gathered.

select pname, PVAL1 from aux_stats$ where pname='MBRC';

——————————————————————————————-

And here’s an example of gathering Exadata mode system stats on a real Exadata as performed by a real Enkitec employee.

 
SYS@dbm1> exec dbms_stats.delete_system_stats;
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:00:00.00
SYS@dbm1> @system_stats
 
PNAME                               PVAL1
------------------------------ ----------
STATUS
DSTART
DSTOP
FLAGS                                   0
CPUSPEEDNW                           2797
IOSEEKTIM                              10
IOTFRSPEED                           4096
SREADTIM
MREADTIM
CPUSPEED
MBRC
MAXTHR
SLAVETHR
 
13 rows selected.
 
Elapsed: 00:00:00.00
SYS@dbm1> exec dbms_stats.gather_system_stats('EXADATA');
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:01:45.51
SYS@dbm1> select  pname, pval1 from sys.aux_stats$;
 
PNAME                               PVAL1
------------------------------ ----------
STATUS
DSTART
DSTOP
FLAGS                                   1
CPUSPEEDNW                           2832
IOSEEKTIM                               9
IOTFRSPEED                          88419
SREADTIM
MREADTIM
CPUSPEED
MBRC                                  128
MAXTHR
SLAVETHR
 
13 rows selected.
Elapsed: 00:00:00.00

So just a couple of quick notes. First, gathering “Exadata” mode system stats does spend some time gathering NOWORKLOAD stats (1:45 in this case) but appears to manually set MBRC to 128.
Note that this is not hardcoded, but actually uses the value of the db parameter db_file_multiblock_read_count which in our case defaults to 128. (see comment from Chris Antognini below) Increasing MBRC makes sense because it would tend to push the optimizer towards full table scans which can obviously be a lot faster on Exadata due to Smart Scan offloading. The default for costing is 8 and pushing it to 128 does make full scans more attractive to the optimizer. Note also that the IOTFRSPEED was dramatically increased (although this doesn’t always happen).

Here’s the section from a 10053 (Wolfgang) trace just for verification.

-----------------------------
SYSTEM STATISTICS INFORMATION
-----------------------------
  Using NOWORKLOAD Stats
  CPUSPEEDNW: 2832 millions instructions/sec (default is 100)
  IOTFRSPEED: 88419 bytes per millisecond (default is 4096)
  IOSEEKTIM: 9 milliseconds (default is 10)
  MBRC: 128 blocks (default is 8)

I do think that Exadata mode system stats should be set (collected) on new Exadata implementations.

8 Comments

  1. Hi Kerry

    I observed that the value is not necessarily set to 128. Instead, it is set to db_file_multiblock_read_count. So, if you don’t set db_file_multiblock_read_count, it’s generally (but, not always) set to 128. If the parameter is set to a lower or higher value, mbrc will be set accordingly.

    Could you please try to reproduce that behavior?

    Thank you,
    Chris

  2. osborne says:

    Hi Chris,

    You are correct. It does set it to whatever db_file_multiblock_read_count is set to. So on systems that have not changed the setting, it will set MBRC to 128 (at least on the current 11.2.0.3 version on Exadata X2,X3). As you are aware, db_file_multiblock_read_count controls two underscore parameters (_db_file_optimizer_read_count for costing – default 8 and _db_file_exec_read_count for execution – default 128), so when left to the default, these two split the costing from execution making multi-block reads large without encouraging the optimizer to use full scans. So I was interested if the calculation used one of these under the covers. But apparently it uses the actual setting of db_file_multiblock_read_count. Anyway, here’s an example:

    SYS@dbm1> @system_stats
    
    PNAME                               PVAL1
    ------------------------------ ----------
    STATUS
    DSTART
    DSTOP
    FLAGS                                   1
    CPUSPEEDNW                           2797
    IOSEEKTIM                               9
    IOTFRSPEED                           4096
    SREADTIM
    MREADTIM
    CPUSPEED
    MBRC                                  128
    MAXTHR
    SLAVETHR
    
    13 rows selected.
    SYS@dbm1> @parms
    Enter value for parameter: db_file_multiblock_read_count
    Enter value for isset: 
    Enter value for show_hidden: 
    
    NAME                                               VALUE                                                                  ISDEFAUL ISMODIFIED ISSET
    -------------------------------------------------- ---------------------------------------------------------------------- -------- ---------- ----------
    db_file_multiblock_read_count                      128                                                                    TRUE     FALSE      FALSE
    
    SYS@dbm1> alter system set db_file_multiblock_read_count=256;
    
    System altered.
    
    SYS@dbm1> @parms
    Enter value for parameter: db_file_multiblock_read_count
    Enter value for isset: 
    Enter value for show_hidden: 
    
    NAME                                               VALUE                                                                  ISDEFAUL ISMODIFIED ISSET
    -------------------------------------------------- ---------------------------------------------------------------------- -------- ---------- ----------
    db_file_multiblock_read_count                      256                                                                    TRUE     TRUE       TRUE
    
    SYS@dbm1> exec dbms_stats.gather_system_stats('EXADATA');
    
    PL/SQL procedure successfully completed.
    
    SYS@dbm1> @system_stats;
    
    PNAME                               PVAL1
    ------------------------------ ----------
    STATUS
    DSTART
    DSTOP
    FLAGS                                   1
    CPUSPEEDNW                           2795
    IOSEEKTIM                               9
    IOTFRSPEED                          85346
    SREADTIM
    MREADTIM
    CPUSPEED
    MBRC                                  256
    MAXTHR
    SLAVETHR
    
    13 rows selected.
    
  3. Hi Kerry,

    It will set it to MBRC to 128 depending on the db_cache_size, one of my customers is running database with a small sga on there X2/X3′s and the MBRC did not go to 128 we did some tests with ASMM disabled:

    SQL> show parameter db_cache
    
    NAME                                 TYPE       VALUE
    ------------------------------------ ----------- ------------------------------
    db_cache_advice                      string      ON
    db_cache_size                        big integer 560M
                                         
    SQL> exec dbms_stats.delete_system_stats();
    
    PL/SQL procedure successfully completed.
    
    SQL> exec dbms_stats.gather_system_stats('EXADATA');
    
    PL/SQL procedure successfully completed.
    
    SQL> select pname, pval1 from aux_stats$ where pname='MBRC';
    
    PNAME                               PVAL1
    ------------------------------ ----------
    MBRC                                   42
    
    SQL> alter system set db_cache_size=2G scope=spfile;
    
    System altered.
    
    SQL> exit
    
    Bounce the database
    
    SQL> exec dbms_stats.delete_system_stats();
    
    PL/SQL procedure successfully completed.
    
    SQL> exec dbms_stats.gather_system_stats('EXADATA');
    
    PL/SQL procedure successfully completed.
    
    SQL> select pname, pval1 from aux_stats$ where pname='MBRC';
    
    PNAME                               PVAL1
    ------------------------------ ----------
    MBRC                                  128
    
  4. osborne says:

    Hi Klaas-jan,

    Thanks for the info. I’m surprised by that actually. As Tanel is fond of saying, “the rabbit hole always goes deeper”. I don’t have time for further testing at the moment but hope to get back to it soon. I’m also wondering why the IOTFRSPEED seems to sometimes get set and other times reverts back to the default 4096.

    Kerry

  5. [...] aware and thus let the optimizer make choices that might result in offloading more often. See this blog post from Kerry Osborne about it, i made a small comment about the MBRC in it and i would like [...]

  6. [...] mode” is. Hence, if you don’t know what it is, before continuing reading have a look to this post published on Kerry Osborne’s blog. The only think I would like to add is that the [...]

  7. Frits hoogland says:

    Have you tried setting MBRC to a value larger than 128?
    128 used to be the max value for MBRC, and still is for buffered reads (given an 8kb blocksize), but the maximal used value observed when doing direct path reads with MBRC set to 1024 or higher is 1024 (tested on 11.2.0.3).

  8. osborne says:

    Hi Frits,

    Yes – you can definitely set it higher than 128 now as discussed in a previous comment.

    Kerry

Leave a Reply