System statistics for EBS

Until very recently, the note with DocId 368252.1 ‘FAQ – Collecting Statistics in Oracle EBS 11i and R12’ in MOS (My Oracle Support) was recommending to use the following command to ‘gather system statistics’ for EBS instances running 11i/R12.X:

execute DBMS_STATS.GATHER_SYSTEM_STATS (gathering_mode => ‘NOWORKLOAD’);

And that is correct, if you are not using Exadata. On Exadata, to ‘gather system statistics’  you should use:

execute DBMS_STATS.GATHER_SYSTEM_STATS (‘EXADATA’);

The main reason for that is that the last command will set MBRC =128 (actually, it sets this value based on the parameter db_file_multiblock_read_count, which defaults to 128). The idea is that a higher value make full table scans more attractive to the optimizer, which can be a lot faster on Exadata due to Smart Scan offloading.

You can see how it currently set running:

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

PNAME                                      PVAL1
——————— ———-
STATUS
DSTART
DSTOP
FLAGS                                          1
CPUSPEEDNW                          2768
IOSEEKTIM                                10
IOTFRSPEED                             4096
SREADTIM
MREADTIM
CPUSPEED
MBRC                                          128
MAXTHR
SLAVETHR

Kerry Osborne has a good discussion about this here.

Thanks to my good friend Olcay Sarioglu, the note MOS 368252.1 was corrected.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s