Kerry Osborne's Oracle Blog

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.