Re: Significance of specific database parameter difference

Well, I did express myself a bit clumsily. SREAD and MREAD are not the only statistics and yes, I started gathering system statistics when I noticed that it increases chances of getting an index scan. So yes, strictly speaking, the effect is not exactly the same. However, the effect is similar enough to make sense to compare the two. In addition to that, I did mention that optimizer_index_cost_adj  (OICA) can cause the optimizer to choose wrong index.

However, there is a philosophical issue in the background: cost based optimizer was introduced in order to make full table scans cheaper and data warehouse loads more sustainable.In other words, the purpose of CBO was to make full table scan more likely to occur.  If the database load is almost exclusively OLTP, then it makes sense to force the optimizer to  strongly favor the index scans because it's the right thing to do. I am guilty of setting optimizer_index_cost_adj to 1 --- on the session level, by using a log-in trigger. Of course, that applies only, when I know exactly what the application is doing. Even then, I had an issue with the wrong index being selected, for 2 out of several thousand SQL statements. I was able to treat that by creating SQL patches. So yes, generally speaking, OICA  is not advised on the instance level. However, since there is rather decisive advice not to gather system statistics, that is the only thing left to do in the situation when it is needed.  And despite the general crusade against that parameter, you will still find many applications recommending it or even requiring it. The reason for all this ruckus about OICA  is the fact that RBO was very good for OLTP applications. If you are maintaining an OLTP application, you do want to emulate RBO.  And that is precisely what OICA does: it helps you emulate RBO, and does it rather well. Oracle is keeping this parameter around since 8i or 9g, I'm not sure. Do you think they would keep it around for so long if it is wasn't needed? Puritanism around OICA is just a fad. That parameter exists in order to be used when appropriate.

Despite the very large portion of the databases being mixed mode, reporting, decision support, data marts or data warehouses, databases used for OLTP only applications are still extremely important. Most of the banking, payment, shopping and e-commerce applications are OLTP only. And bind variable peeking sometimes produces incorrect plans. Without optimizer geared toward selecting the index range scan you may end up with a mess and a VP asking questions about the DB performance. So, purity and examples of bad usage aside, those two parameters still have their use.

And yes, I am aware that I have just opened a can of worms and started a very long thread. That is, after all, something I am known for.

On 4/21/21 5:34 AM, Jonathan Lewis wrote:
> Mladen Gogala commented that setting system stats had the same effect
> setting the optimizer_index_xxx parameters. This is not strictly true
> - setting system stats generatlly increases the cost of tablescans,
> changing the optimizer_index_xxx parameters generally decreases the
> cost of indexed access paths, often to the level where all indexes are
> equally cheap.  (Effectively your combination of parameters is almost
> saying to Oracle "pick an index, any index" - and that's not a good
> idea unless the indexing strategy has been very well defined.)

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

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