Re: PX Deq Credit: send blkd

"Carlos" <miotromailcarlos_at_netscape.net> wrote in message news:1191426971.871390.111260_at_22g2000hsm.googlegroups.com...
> On 3 oct, 17:48, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> wrote:
>> "Carlos" <miotromailcar..._at_netscape.net> wrote in message
>>
>> news:1191424330.534720.242750_at_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