Re: performance of joins

I greatly appreciate your help in enlightening me on my doubts!! Thanks a lot for the detailed and helpful replies!!

best regards,
Derek.

Jonathan Lewis wrote:
> "derek" <dereksmi_at_gmail.com> wrote in message
> news:1117835109.852799.120550_at_z14g2000cwz.googlegroups.com...
> >
> > 3) I am pasting the results with the autotrace on at the end of this
> > posting. I hope that you would be able to help me out with
> > understanding the trace. I will be more than glad to send you the
> > actual trace file if you require it to analyse more deeply. One
> > surprising thing I noticed in the output of the auto-trace is "sorts"
> > being used in the nested indexed join. What does that mean? Also in
> > sort merge join number of sorts is 4 -- does it mean that the sorted
> > tables are trashed due to lack of memory?
> >
> > 4) For the comment 4, yeah I think Oracle probably doesnot care abt
> > optimising some unusual queries and ths might explain the results. I am
> > attaching th autotrace summaries first followed by summaries from the
> > trace file of tkprof.
> >
> >
> > best regards,
> > Derek Smith.
> >
> >
>
>
> You seem to have missed the reply I sent
> to you under the "Non equi-join" thread.
>
>
> Item 3: - with autotrace, you are going to
> see some statistics relating to recursive
> activity for parsing the statements. If you
> ran the statements twice and checked the
> stats on the second pass, the "sorts" value
> from autotrace would make more sense,
> i.e. 0 for the NL, 2 for the SM.
>
>
> Item 4: - Oracle is unable to 'not care',
> the code has to run. It has tried "very hard"
> to optimize the query. Think carefully about
> what your query actually has to do in the
> absence of an index:
>
> for each row in first table,
> compare with every row in second table.
>
> Of course, with your example, the SM allows
> the average number of comparisons to be about
> half because Oracle can find the right starting
> point in the (sorted) second data set.
>
> The query takes a lot of CPU.
>
> In fact, the NL and SM joins are doing pretty
> much the same thing - but EITHER option could
> have been the more CPU intensive for a particularly
> odd data set. Oracle has a few generic routines -
> they are bound to be sub-optimal at times.
>
> Equating logical I/O to CPU is often a fair indicator,
> but not when Oracle doesn't use logical I/O to
> acquire the data. In the SM join, Oracle has
> copied the data into local memory - but it's pretty
> much the same data (in the right order) probably
> using about the same amount of memory.
>
> The amount of work is about the same, regardless
> of where the data comes from - in the SM it comes
> from local memory anmd chasing pointers, in the NL
> it comes from visits to buffered blocks and walking
> row lists.
>
>
> --
> Regards
>
> Jonathan Lewis
>
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
> The Co-operative Oracle Users' FAQ
>
> http://www.jlcomp.demon.co.uk/seminar.html
> Public Appearances - schedule updated April 5th 2005
Received on Mon Jun 06 2005 - 00:47:33 CDT