Re: why does CBO not use available indices

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:bm4a94$ijj$1$8300dec7_at_news.demon.co.uk...
>
> The alternative, of course, is to design the
> application properly - but that's too much
> like hard work ;)

True, of course. But we must give some lag to the app makers. In most cases they have to make the darn things work well with minimal changes across a number of databases. It's not that easy at all. Particularly with the enormous applications we see nowadays, where 5000+ tables are not uncommon.

Clearly, something more definite than just "good design" needs to be done to make life easier for these folks. It's like the "bind variables" mantra: we all know what not using them does to Oracle. However, not all databases have such a major problem without them as Oracle does.

While I wouldn't even DREAM of designing an Oracle system without BVs, I can see that the new optimizer parameters to force cursor sharing without bind variables went a LONG way in ensuring third parties don't get a raw deal from Oracle.

It's all about adjusting. We can complain as much as we want about bad this or bad that but if nobody is doing the right thing, then it's better to make it happen optimally using the engine itself, rather than expecting the world to change: clearly, it won't.

> I think 99% of the problem there has usually
> been the fact that very few people understand
> how hints work in the first case, so of course
> their effects are not stable when they are used
> incorrectly.

Well, to me the problem is they "need" hints! I've used them for a long time, but nowadays I'm tending to the side of only doing so if nothing else works. Just not reliable enough in the long run. And getting more obscure by the day.

To me, one of the main things to avoid in designing or tuning a system is complexity. If using hints is so complex that only a "hint expert" can handle them, then I know exactly what I won't be using. Mostly, because I can't afford to require an expert for everything that is done. There are a number of ways of achieving that. One is of course setting stats.

> and sort_area_size, and hash_area_size, and
> new features being silently enabled and costing
> algorithms being tweaked etc.

The same applies to hints. The thing is: we do NOT usually run a new version (with hidden changes) in production until it's been thoroughly checked. Development and production better be the same version, unless someone wants a nasty surprise. So, the hidden changes and tweaks are not that much of a factor. As for the other parameters, they are easy enough to set the same between development and production. Wich BTW is what I see in most sites.

> > Then we have 10g where the actual executing statement
> > can be "re-written" with new hints. That is fine, although
> > far from an optimal way of fixing this problem.
>
> Not sure quite what you are describing there - but it is
> true that Oracle 10 has the facility for collecting better
> information about the data, and therefore creating
> descriptions of better execution plans - and that's not
> really something I would call a 'fix', it's more a case
> of a perfectly rational trade: you get better plans when
> you have better information to start with (GIGO).

Strange you haven't heard about it, I thought it would have been public domain now? In 10g according too Ken Jacobs' presentation here last month, it is possible with OEM to pick a badly behaving statement and tune it (with hints), *on the fly*. Ie, without stopping anything, directly in production.

Great idea, but IMHO open to "tweak city". And God only knows what happens when the database is shut down: do we have to re-tune them all again next time? Nope, not the right way to approach this problem... Looks "kewl" in a presentation or a demo, but doesn't warm me up one bit!

Oracle has to bite the bullet and put the whole thing into policies. Which can be applied to a group of users, or schemas, or apps, or SQL statements of a specific class. Then, it will be useful. Until then, it's nothing more than a vehicle for hacking.

> Hacking the statistics on one table to solve one query
> is is just as likely to create a bucket load of new
> problem as fiddling with a significant optimizer
> parameter.

Oh no it isn't! Changing an optimizer parameter affects ALL tables and ALL SQL. And it usually requires a bounce. Changing the stats in a table affects at most only the SQL dealing with that table and requires no bounce whatsoever. Two completely different dimensions of impact!

> A key difference is that sometimes,
> setting the parameter is telling the truth about
> your system - unless you know what you are doing,
> deliberately lying about table stats is unlikely to
> be telling Oracle anything useful.

The same applies to changing optimizer parameters: unless we know the impact of it on the TOTALITY of the system(and in most cases that is not possible), we may cause problems. I'd rather limit the problems, potential or real.

>
>
> I rather liked Connor's comments - take advantage of
> hints to get ideas about WHY the optimizer isn't doing
> what you expect it to do, and then fix the root cause in
> an appropriate manner.

That is true, but not always possible with third party applications. Which are the vast majority of code out there. Of course we all would like to design systems from top to bottom and trap the problems before they become so. However, that is not the reality of most Oracle sites right now. And I see no change in the near future. If anything, the tendency to use off-the-shelf apps seems to be increasing...

Clearly, re-design is not an option, in these circumstances. I'm very interested in what you see out there regarding this, BTW. And thanks a lot for the comments.

-- 
Cheers
Nuno Souto
wizofoz2k_at_yahoo.com.au.nospam