Path: text.usenetserver.com!out03b.usenetserver.com!news.usenetserver.com!in04.usenetserver.com!news.usenetserver.com!nx01.iad01.newshosting.com!newshosting.com!216.196.98.140.MISMATCH!border1.nntp.dca.giganews.com!nntp.giganews.com!local01.nntp.dca.giganews.com!nntp.bt.com!news.bt.com.POSTED!not-for-mail NNTP-Posting-Date: Wed, 03 Oct 2007 11:47:04 -0500 From: "Jonathan Lewis" Newsgroups: comp.databases.oracle.server References: <1191398017.565868.270760@o80g2000hse.googlegroups.com> <1191418084.729161.256830@50g2000hsm.googlegroups.com> <1191424330.534720.242750@k79g2000hse.googlegroups.com> <1191426971.871390.111260@22g2000hsm.googlegroups.com> Subject: Re: PX Deq Credit: send blkd Date: Wed, 3 Oct 2007 17:47:43 +0100 X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2900.3138 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3138 X-RFC2646: Format=Flowed; Original Message-ID: Lines: 51 X-Usenet-Provider: http://www.giganews.com NNTP-Posting-Host: 86.130.255.131 X-AuthenticatedUsername: NoAuthUser X-Trace: sv3-lvtxjK/QKFBKz9XEupuUefqZXob3Ka4jCnEK07oaXaf4CVUN9wt4cxjGGLshMjam3zKhaQNBIlYngex!GKoafqrZJKABB8YEVOZRC5Ncxe1aqaX4SjfgTBbBAXo7IWlRPj1Wz/cb9AJolpXpdWCaGHAyuH0g X-Complaints-To: abuse@btinternet.com X-DMCA-Complaints-To: abuse@btinternet.com X-Abuse-and-DMCA-Info: Please be sure to forward a copy of ALL headers X-Abuse-and-DMCA-Info: Otherwise we will be unable to process your complaint properly X-Postfilter: 1.3.36 Bytes: 3214 Xref: usenetserver.com comp.databases.oracle.server:435881 X-Received-Date: Wed, 03 Oct 2007 12:46:56 EDT (text.usenetserver.com) "Carlos" wrote in message news:1191426971.871390.111260@22g2000hsm.googlegroups.com... > On 3 oct, 17:48, "Jonathan Lewis" wrote: >> "Carlos" wrote in message >> >> news:1191424330.534720.242750@k79g2000hse.googlegroups.com... >> >> If the second plan is unsuitable, it is because Oracle's estimate of the >> volume of the two data sets was wrong. Possibly, however, a fairly >> small change in the predicates could have resulted in a sufficiently >> large >> change in the data volumes that the plan is the best possible plan, and >> the time is a side effect of the limit on the available memory for >> building >> the hash table. >> > > Thanks Jonathan. > > So you think the parallel hint is not the one to blame? > > What do you think about sort_merge join here? > > Before blaming anything, or trying to fiddle with the query until it works, I would examine the data patterns and work out the volume of data in the old and new data sets to see if the optimizer's estimates were close or far out. Then I'd try to work out why the estimates were bad (if they were) and see if I could improve the estimates - possibly by correcting the statistics on the tables. Then I'd try to work out if a merge or NL would work more efficiently; and I'd consider (a) allowing more memory to the session by switching to manual workarea execution and (b) forcing the hash join order. -- Regards Jonathan Lewis http://jonathanlewis.wordpress.com Author: Cost Based Oracle: Fundamentals http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html