Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!drn.maxwell.syr.edu!news.maxwell.syr.edu!postnews.google.com!g43g2000cwa.googlegroups.com!not-for-mail From: nagender.bandi@gmail.com Newsgroups: comp.databases.oracle.server Subject: Re: performance of joins Date: 5 Jun 2005 22:47:33 -0700 Organization: http://groups.google.com Lines: 94 Message-ID: <1118036853.653767.305170@g43g2000cwa.googlegroups.com> References: <1117685433.083007.38170@g49g2000cwa.googlegroups.com> <1117745694.464055.204730@g43g2000cwa.googlegroups.com> <1117835109.852799.120550@z14g2000cwz.googlegroups.com> NNTP-Posting-Host: 128.111.20.195 Mime-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" X-Trace: posting.google.com 1118036859 18530 127.0.0.1 (6 Jun 2005 05:47:39 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Mon, 6 Jun 2005 05:47:39 +0000 (UTC) In-Reply-To: User-Agent: G2/0.2 Complaints-To: groups-abuse@google.com Injection-Info: g43g2000cwa.googlegroups.com; posting-host=128.111.20.195; posting-account=o7hT7A0AAAC4aAUayDRv7OWjN1tcrE_B Xref: dp-news.maxwell.syr.edu comp.databases.oracle.server:244301 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" wrote in message > news:1117835109.852799.120550@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