Re: Improving Performance of Hash Joins

On Dec 26, 6:05 pm, zigzag..._at_yahoo.com wrote:
> 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.organdlook 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.- Hide quoted text -
>
> - Show quoted text -

Oh well, I got prematurely excited about it. In sql statements it does not support bind varaiables in where clause which makes is quite limiting. Received on Fri Dec 26 2008 - 17:49:22 CST