Re: Improving Performance of Hash Joins

On Dec 26, 12:04 am, DA Morgan <damor..._at_psoug.org> wrote:
> 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.
>
> Your assumption that you can not modify the queries is incorrect. You
> can easily do so with the DBMS_ADVANCED_REWRITE built-in package.
>
> Go to Morgan's Library atwww.psoug.organd look up the package. The
> demo will show you how to do it.
> --
> Daniel A. Morgan
> Oracle Ace Director & Instructor
> University of Washington
> damor..._at_x.washington.edu (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org

Below is information about PGA memory usage. I see my PGA_AGGREATE_TARGET is set to 614M, yet only 28M is used, why??: So increaseing it won't help imporive performance because HASH_JOIN does not seem to use all of the memory.

aggregate PGA target parameter
 643825664 bytes

aggregate PGA auto target
 554185728 bytes

global memory bound
 104857600 bytes

total PGA inuse
  28063744 bytes

total PGA allocated
  57332736 bytes

maximum PGA allocated
  74617856 bytes Received on Fri Dec 26 2008 - 13:46:53 CST