Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Peoplesoft: Prolific Use of EXISTS and HASH hints
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