It's generally a bad idea to adjust OICA - it has a global impact on the cost
of table access by index and has the effect of making it harder for Oracle to
tell the difference in quality between indexes. In recent versions of Oracle
the more appropriate strategy is to set the table preference
"table_cached_blocks" for specific tables if you think Oracle is costing
indexed access too high because of it's flawed strategy for estimating the
clustering_factor. (In extreme cases you might call
dbms_stats.set_index_stats() to adjust specific stats for a few indexes.)
Adjusting the OIC is also generally a bad idea; but Oracle has no other
mechanism (currently in production) for recognising how well an index might be
cached, and important, popular indexes are often very well cached. The impact
of adjusting this parameter is generally relatively small compared to fiddling
with OICA, so you may - if you have a couple of large, well-cached, indexes
that are most important to your system - give Oracle some idea of how well
cached those indexes are by setting the OIC and living with the consequences of
the optimizer thinking that ALL your indexes are well cached.
The step change in the optimizer's arithmetic in going from the old (8i)
"io-costing" to "cpu-costing" comes from the basic effect that cpu-costing
increases the cost of tablescans by assuming that a multiblock read takes
longer than a single block read.
If you leave the system stats at their default (and don't modify the
db_file_multiblock_read_count parameter) then Oracle will assume that an 8
block multiblock read will take 26 ms while a single block read will take 10
ms. (I'm assuming a standard 8KB block size).)
If you leave the system stats at their default and set the
db_file_multiblock_read_count parameter to 128 (as many people do) then Oracle
will assume that a 128 block multiblock read will take 266 ms while a single
block read will take 10 ms.
Historically the optimizer would assume it could scan 128 blocks as fast as it
could read one block - so you had to hack something to persuade it otherwise,
OICA was a very brutal hack that Oracle gave us, OIC nudged things a bit
further. Changes like the above two bits of arithmetic mean the optimizer will
be far less likely to choose tablescans when it shouldn't; and since they make
tablescans look more expensive (rather than make all indexes look cheaper and
(sometimes) indistiguishable) it's a safer strategy.
Regards
Jonathan Lewis
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Cee Pee <carlospena999@xxxxxxxxx>
Sent: 25 March 2019 23:01
To: Oracle-L Freelists
Subject: Re: System stats
Interesting. There have been lots of advices related to system stats and the
OICA/OIC parameters. The advices tend to ask us to leave the OICA/OIC
parameters at default and calculate system stats instead. For those advising to
leave system stats at default, is it ok to adjust OICA/OIC parameters? Does
leaving the system stats at default have an(y) effect on the OIC/OICA?
--
//www.freelists.org/webpage/oracle-l
