Oracle optimizer uses SREADTIM, MREADTIM and MBRC parameter to calculate
the cost of index reads and full table scans. Newer version of the
optimizer use CPUSPEED to calculate the duration of the CPU portion but
I've not noticed any impact on my plans. Without collected system
statistics, oracle uses some default values which may or may not be what
you want. Oracle stores the system statistics in SYS.AUX_STATS$ table.
Here is what the contents look like:
SQL> select pname,pval1 from sys.aux_stats$;
PNAME PVAL1
_____________ ___________________
STATUS
DSTART
DSTOP
FLAGS 1
CPUSPEEDNW 2467.23680330286
IOSEEKTIM 10
IOTFRSPEED 4096
SREADTIM 0.41
MREADTIM 1.396
CPUSPEED 3188
MBRC 8
MAXTHR 17428480
SLAVETHR 37888
13 rows selected.
If you collect system stats on your new disk array, the IO will look
cheaper. However, if the ratio of SREADTIM/MREADTIM doesn't change, the
plans will not change because the relative price of multi-block read vs.
single block read will not change. If, on the other hand, the ratio does
change, your plans will change. This table is the source of some black
magic. One of the tricks is to lower MBRC (using
DBMS_STATS.SET_SYSTEM_STATS) and make the optimizer think it will have
to perform more multi-block reads, thereby making full table/index scans
more expensive and less likely to be chosen by the optimizer. Optimizer
uses the value from the AUX_STATS$ to calculate the price of the full
table scan, not the value of db_file_multiblock_read_count. By
manipulating the ratio of SREADTIM/MREADTIM you are effectively doing
the same thing as by setting optimizer_index_cost_adj parameter, which a
rather crude and ugly tool that some companies which sell OLTP
applications love to use in order to mimic the rule based optimizer. Be
aware, though, that if SREADTIM is larger than MREADTIM, the optimizer
will not use the values, or at least didn't do so in 11.2.0.4 when was
the last time I tested it.
Now, if you already do have system statistics and if your plans are
mostly OK, keeping the same system statistics will keep the same plans
which will execute faster. Very long time ago Jonathan simulated
"_make_apps_go_faster" hidden parameter in one presentation. I found the
presentation hilarious but getting a newer and faster SAN may really be
"make apps go faster" silver bullet. Knowing EMC, that silver bullet is
a bit on the expensive side.
On 1/28/21 9:47 PM, Hameed, Amir wrote:
Thanks Neil. Per MOS note *E-Business Suite Applications Performance - Collecting Statistics in Oracle EBS 11i and R12 (Doc ID 368252.1)*, Oracle does suggest gathering System Statistics.