Re: Peoplesoft: Prolific Use of EXISTS and HASH hints

From: Paul Brewer <paul_at_paul.brewers.org.uk>
Date: Thu, 8 May 2003 21:41:21 +0100
Message-ID: <3ebac562_3@mk-nntp-1.news.uk.worldonline.com>

"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 Received on Thu May 08 2003 - 15:41:21 CDT