Re: System stats
Date: Tue, 26 Mar 2019 12:32:54 +0000
Message-ID: <LO2P265MB041546EB54D1300127AA43E0A55F0_at_LO2P265MB0415.GBRP265.PROD.OUTLOOK.COM>
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_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Cee Pee <carlospena999_at_gmail.com> 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?
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Mar 26 2019 - 13:32:54 CET