Re: Improving Performance of Hash Joins
Date: Fri, 26 Dec 2008 15:05:23 -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
dbms_advanced_rewrite is something I always wanted since I started supporting many third party applications. Even though, I have gone through Oracle 10g manuals several times, I had missed it. Hope to make use of it.Thanks a lot. Received on Fri Dec 26 2008 - 17:05:23 CST