Re: Improving Performance of Hash Joins

On Dec 26, 5:57 am, Robert Klemme <shortcut..._at_googlemail.com> wrote:
> On 25.12.2008 23:06, Mark D Powell wrote:
>
> > On Dec 25, 3:04 pm, zigzag..._at_yahoo.com wrote:
> >> I am on Oracle 10.2.0.3 on Windows 2003 32 bit. I have /3GB switch set
> >> on and have a value of sga_target set to 2.x GB,
>
> >> I am using a third party package and many queries are doing hash
> >> joins. I cannot modify any queries because of no source code available
> >> to me. I know in Oracle9i, there was some init.ora parameter for
> >> specifying memory used by hash joins which has been taken away in 10G.
> >> What are my options (if any) to improve performance of this hash joins
> >> which for some queries take 2-3 minutes.
>
> Are schema and system statistics up to date?  Is it a bad plan or is the
> plan good and it just takes too long?
>
> > By the way on a 9.2 system you can potentially use the OUTLINE feature
> > to provide hints to the vendor SQL without ever having to change the
> > source.
>
> If I understood him correct he is on 10g now - which should still have
> stored outlines AFAIK.
>
> Kind regards
>
>         robert
>
> --
> remember.guy do |as, often| as.you_can - without end

Yes, the OP is on 10g but had mentioned a 9i parameter no longer available in 10g which is where my incorrect version statement came from. Outlines as pointed out are also available in 10g. OP has mentioned using 10g profile feature which I usually do not mention since I believe it requires a Performance Pack license to legally use.

HTH -- Mark D Powell -- Received on Fri Dec 26 2008 - 10:40:10 CST