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

Being 10g, I ran slq profile on it. SQL PROFILE also shows hash join in its plan, so hash join is perhaps the best way.

By the way, I have used stored outlines in the past. It is not straightforward to add hints, becuase if you put hints in SQL statement, its "text" is no longer same as original SQL's, so optimizer will not pick the stored outline. One has to edit the outline itself without chaging text, not an easy process. Am I missing some thing? Received on Fri Dec 26 2008 - 10:06:59 CST