Re: why does CBO not use available indices

Hi Guys,

Comments on 10g below.

Noons wrote:
>
> "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.

The primary benefit to the DBA is having the capability to affect the SQL execution plan when access to the underlying source code is not available. The CBO has a "tuning mode" in addition to its "normal mode" in 10g version. The main difference in modes is the time constraint to find a plan is relaxed to allow the optimizer additional time to try and find a better SQL plan. Hey, it looked good in the Oracle World lab last month. The DBMS_SQLTUNE package was the implementation underneath the OEM GUI tool. Of course, using the GUI is recommended ;)

>
> 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
Received on Tue Oct 28 2003 - 12:24:29 CST