Re: Question on gathering System Statistics

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

_____________ ___________________

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.

-- 
Mladen Gogala
Database Consultant
https://dbwhisperer.wordpress.com


--
http://www.freelists.org/webpage/oracle-l