Jack,
That is the use case for 'EXADATA' mode. 1 "pure" data warehouse, with lots of
offloading to storage cells. Worth pointing out a couple of things:
Depending up on the order of execution, you get 2 different results for your
commands.
Gathering EXADATA mode, then setting MBRC means the subsequent settting of the
MBRC number will be ignored by the optimizer as it will have been copied from
the parameters and explicitly set in the system statistics. Setting MBRC first,
then gathering EXADATA mode will copy "256" into the system statistics.
The other key setting for 'EXADATA' mode is the IOTRFSPEED, which will increase
from 4096 to a much bigger number (e.g. 200,000). The combination of a high
MBRC (e.g. 128) and IOTRFSPEED (but a reduced IOSEEKTM) mean that the number of
blocks you get for a multiblock read increases dramatically from the defaults.
[defaults] MBRC unset=8, IOSEEKTM=10ms,
IOTFRSPEED=4096 - for every single block read (index leaf block, and table
look up by rowid) you get 3.69 blocks in a table scan.
[exadata] MBRC=128 (default if 8k blocks), IOSEEKTM= 9ms,
IOTFRSPEED=200000 - for every single block read (index leaf block, and table
look up by rowid) you get 81.25 blocks in a table scan.
Your indexes need to be precise, preferably with good
clustering factors, to be used.
[exadata setting MBRC first] MBRC=256, IOSEEKTM= 9ms,
IOTFRSPEED=200000 - for every single block read (index leaf block, and table
look up by rowid) you get 117.78 blocks in a table scan.
I did a little blog post and spreadsheet (and some SQL) so you can see what
your numbers do to the single block/multi block ratio.
https://chandlerdba.com/2021/02/03/oracle-optimizer-system-statistics/
regards
Neil Chandler
Database guy
________________________________
1. execute dbms_stats.gather_system_stats('EXADATA')
2. execute dbms_stats.set_system_stats('MBRC',256);
I accept that this is Exadata and a very typical data warehouse (nightly load
and only reports being run throughout the day) so this may very well be one of
the few exceptions where tinkering is best.
Jack van Zanen
