Re: Query tuning experts: Any hope for tuning query with 5+ joins and a sort?

Comments embedded.

"Nuno Souto" <nsouto_at_optushome.com.au.nospam> wrote in message news:3dc66b59$0$20540$afc38c87_at_news.optusnet.com.au...
> Mon, 4 Nov 2002 23:18:38 +1000, Richard Foote said (and I quote):
> >
> > I think you've missed the point. It's not that the sort is particularly
> > efficient, it's because using an index to read a large portion of a
table is
> > so terribly inefficient.
>
> Unique indexes are not that inefficient, non-unique can be.

Hi Nuno,

Unique/Non Unique, it makes no difference in that an index range scan reading heaps of rows is an index range scan reading heaps of rows. To be avoided *if* it costs more than other options.

>
> The thing to remember here is that it's OK in the pristine environment of
> a benchmark to grab as much CPU/IO as possible to get max speed off a
> given operation. In isolation, fully dedicated. As such, it's quite
> kosher for a full scan with a sort to be faster, given sufficient
> hardware.
>
> Problem is that in normal operation of a production system, the hardware
> is being shared with a gazzilion other things. We fire off an operation
> that grabs as much as possible to go fast and we got an immediate problem
> in our hands. Particularly if we're forcing a sort into TEMP, while the
> monthly processing is doing its big sorts. You get the picture.

I kinda do but if an index is performing more work and costs more in terms of resources (ie. performing a colossal amount of logical and/or physical reads) than other options, then it's the index scan that is putting stress on the overall system.

The nice thing with 9i is that the CBO is also aware of system resources (via the system stats) and if for example it determines the system is maxing CPU takes that into consideration. All things being equal though, the option that performs the less work and requires the less resources is generally going to win out. Hence the CBO's objection to expensive index scans.

>
> My approach to these things is always: does it still deliver all those
> advantages in a real life situation? If there is the slightest doubt,
> then it's time for caution.

I quite agree. Suck it, experiment and see is the only way to determine which option is more appropriate.

>
> Heck, I just had a very well known Oracle consultancy firm walk into a
> client of mine and recommend we go for RULE-based optimizer, given all
> the crap the CBO has been causing ( and to them also, when they tried to
> "fix" it...).

Been there ;)

>
> It's one of those things: not all is glitter in the world of performance
> in Oracle.
>
> > big snip
> > I'll let the above example speak for itself.
>
> Impressive. What I don't get is why the stats for the 10% full scan are
> not returning the same number of blocks visited. 10% or not, it should
> have been the same full scan.

Because the second query performs a much smaller disk sort and hence accesses a reduced number of temp blocks.

> Also, the index is obviously not unique ("id between 1 and 1000" cannot
> return 128000 rows otherwise). I was talking about unique indexes. There
> is a difference for this purpose.

Correct, my id is non unique (I guess it's not much of an id :) But it being unique/non unique makes no difference. If the index were unique and it accessed the same proportion of data, performance would have been identical with both options.

>
> >
> > Give it a go and see for yourself ;)
>
> Will do. In 8i, I don't have 9r2 yet loaded anywhere...
> I'll do a few tries with/without unique idx and with large/mid/small
> volumes. Stay tuned.

Go for it. I look forward to hearing what conclusions you come to.

Cheers

Richard

>
>
>
> --
> Cheers
> Nuno Souto
> nsouto_at_optusnet.com.au.nospam
Received on Mon Nov 04 2002 - 21:03:23 CST