Path: newssvr20.news.prodigy.com!newsmst01.news.prodigy.com!prodigy.com!prodigy.com!rip!c03.atl99!news.webusenet.com!fu-berlin.de!newsfeed.iinet.net.au!newsfeed.iinet.net.au!freenews.iinet.net.au!not-for-mail Message-ID: <3F86977D.433D@yahoo.com> Date: Fri, 10 Oct 2003 19:26:53 +0800 From: Connor McDonald X-Mailer: Mozilla 3.01 (WinNT; I) MIME-Version: 1.0 Newsgroups: comp.databases.oracle.server Subject: Re: why does CBO not use available indices References: <9042145d.0310060903.4ed4e354@posting.google.com> <3f829925$0$24515$afc38c87@news.optusnet.com.au> <3f8558d2$0$15970$afc38c87@news.optusnet.com.au> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Lines: 136 NNTP-Posting-Host: 203.59.112.27 X-Trace: 1065785213 freenews.iinet.net.au 23609 203.59.112.27 Xref: newssvr20.news.prodigy.com comp.databases.oracle.server:244988 Jonathan Lewis wrote: > > Notes in-line. > > -- > Regards > > Jonathan Lewis > http://www.jlcomp.demon.co.uk > > The educated person is not the person > who can answer the questions, but the > person who can question the answers -- T. Schick Jr > > One-day tutorials: > http://www.jlcomp.demon.co.uk/tutorial.html > ____Iceland__November (tbc) > ____Belgium__November (EOUG event) > ____UK_______December (UKOUG conference) > > Three-day seminar: > see http://www.jlcomp.demon.co.uk/seminar.html > ____USA__October > ____UK___November > > The Co-operative Oracle Users' FAQ > http://www.jlcomp.demon.co.uk/faq/ind_faq.html > > "Noons" wrote in message > news:3f8558d2$0$15970$afc38c87@news.optusnet.com.au... > > > > > > I really dislike your suggestion 4, > > > > That's why I put it in last order of desirability. > > > > > and I'm not > > > too keen on option 1 either. > > > > And yet it's one of the few reliable ways a 3rd party > > app writer has of ensuring that a given plan for > > a given table access in a given application will > > always be selected by Oracle. > > The alternative, of course, is to design the > application properly - but that's too much > like hard work ;) > > > Unfortunately, the > > DB2 "binding" is not (yet) available in Oracle. > > > > Apart from gambling on a particular combination of > > hints always doing the same across Oracle versions. > > Far from guaranteeed this last one. I'm actually > > surprised Oracle suggests it in their performance > > book. They are notorious for silently removing > > the effects of a particular hint on a point release... > > > > 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. > > > Of course, cloning stats is no insurance that plans > > will be stable across databases. But it's darn near > > close. Have yet to see one case where it didn't work. > > Apart from obvious things like widely different DBFMR. > > and sort_area_size, and hash_area_size, and > new features being silently enabled and costing > algorithms being tweaked etc. > > > > > 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). > > > But > > I doubt very much the OP was talking about that version. > > > > > > > > Fixing a problem > > > by kicking it until it slinks away bloody and > > > bruised is not a strategic solution - it's only > > > going to bounce back later, probably at the > > > most awkward possible moment. > > > > Isn't that what changing global optimizer performance > > parameters to fix ONE specific problem is all about? > > And yet it's been done ad-nauseum. > > > > I'd rather have a specific problem get a specific solution > > which will NOT easily create a bucket load of others. > > 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. 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. > > > That is not what one gets when fiddling with optimizer_* > > parameters. They are by their very nature global. > > > > By that I mean they affect *all* SQL, not just the > > problematic one. One setting might fix one problem > > and be a disaster for other apps running on the same DB. > > > > I'm reminded of the disastrous effects in 8.* of setting > > optimizer default to FIRST_ROWS: imp and exp performance > > goes out the window. So do some dictionary operations. > > > > I was of course assuming general purpose databases. > > Rather than systems that only run one application with > > a limited number of tables. Those behave differently. > > > > > > 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. I rather liked them too :-)