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$;
Kerry Osborne has a good discussion about this here.
Thanks to my good friend Olcay Sarioglu, the note MOS 368252.1 was corrected.