Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Peoplesoft: Prolific Use of EXISTS and HASH hints

Re: Peoplesoft: Prolific Use of EXISTS and HASH hints

From: Daniel Morgan <damorgan_at_exxesolutions.com>
Date: Thu, 08 May 2003 14:36:31 -0700
Message-ID: <3EBACDDF.8C6FCD1E@exxesolutions.com>


Paul Brewer wrote:

> "Ethan Post" <nospam_at_nowhere.com> wrote in message
> news:R2jua.249049$Si4.198070_at_rwcrnsc51.ops.asp.att.net...
> > Peoplesoft seems to use EXISTS all over the place in the app generated
> SQL.
> > I have found that in most cases the SQL can be tuned with dramatic effects
> > simply by use a HASH_SJ hint or in other cases USE_HASH. The system has
> > hash joins enabled, stats are up to date but they are not using
> DBMS_STATS,
> > they are using analyze with estimate. Since most of this SQL is generated
> > by the app, what tips/tricks have you found to get Oracle to use hash
> joins
> > and why isn't Oracle figuring out that this is much faster? It seems this
> > would be one of the plans Oracle would consider. I have not modified
> > optimizer parameters but tests as the session level on a couple statements
> > don't show any difference in the plan.
> >
> > Thanks!
> >
> > - Ethan Post
> >
>
> When you have a Third Party Application which is as badly designed as
> Peoplesoft, please don't blame the underlying database management system;
> blame the app.
>
> Peoplesoft design is dreadful, whatever database it is running on. But it
> has a flashy front end; the one the bosses always go for.
>
> A lesson for us all here, I think.
>
> Regards,
> Paul

There should be a rule in IT/IS against purchasing projectorware.

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)
Received on Thu May 08 2003 - 16:36:31 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US