Re: Improving Performance of Hash Joins
Date: Fri, 26 Dec 2008 11:46:53 -0800 (PST)
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
aggregate PGA auto target
global memory bound
total PGA inuse
total PGA allocated
maximum PGA allocated
74617856 bytes Received on Fri Dec 26 2008 - 13:46:53 CST