How To Gather Exadata Statistics ?

Very quick note on gathering of Exadata statistics. 

Why?

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. 

Following command gathers Exadata specific system statistics

SQL>exec dbms_stats.gather_system_stats(‘EXADATA’);

Risk:

Lack of Exadata specific stats can lead to less performance 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.

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

Let's check the output without system stats.

SQL> select  pname, pval1 from sys.aux_stats$;

PNAME                               PVAL1
------------------------------ ----------
STATUS
DSTART
DSTOP
FLAGS                                   0
CPUSPEEDNW                         2560
IOSEEKTIM                             10
IOTFRSPEED                           4096
SREADTIM
MREADTIM
CPUSPEED
MBRC
MAXTHR
SLAVETHR

13 rows selected.

Without stats values of MBRC is showing blank as shown above.

Gather Stats:

Now let's execute exadata system stats.

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

PL/SQL procedure successfully completed.

Elapsed: 00:01:26.53


Now let's check the value of MBRC

SQL> select  pname, pval1 from sys.aux_stats$;

PNAME                               PVAL1
------------------------------ ----------
STATUS
DSTART
DSTOP
FLAGS                                   1
CPUSPEEDNW                           2560
IOSEEKTIM                               7
IOTFRSPEED                         128695
SREADTIM
MREADTIM
CPUSPEED
MBRC                                  128
MAXTHR
SLAVETHR

13 rows selected.

This will use the value of db_file_multiblock_read_count database parameter which is 128 in our case. 

If you observe in above output, the value of IOTFRSPEED is also increased 

In our case db_file_muliblock_read_count parameter value is

SQL> show parameter db_file_multiblock_read_count

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count        integer     128

If you increase it to 256 and gather Exadata stats again than you will see the MBRC value to 256.

Benefits:

Increasing MBRC tend to push the optimizer towards full table scans which can obviously be a lot faster on Exadata due to Smart Scan offloading.

Increasing MBRC to 128 does make full scans more attractive to the optimizer, ultimately it will boost the performance.

Remove stats:

You can delete stats using below command.

SQL> exec dbms_stats.delete_system_stats;

PL/SQL procedure successfully completed.


You would also like our post on Exadata Interview Questions and Answers.