Re: Peoplesoft: Prolific Use of EXISTS and HASH hints

"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.
>

Whether nested loop semi-join or hash semi-join is faster has a lot to do with your data distribution. Without that information, an application just has to choose one or the other. PeopleSoft could have used hash semi-join but then you'd have another set of SQLs which would've been a lot better off with nested loops (not trying to defend PeopleSoft. it's impossible for them to write perfect code but they should certainly do a better job by using both semi-joins). To me, it should be the optimizer's responsibility to figure out the best access path. I've never seen Oracle choosing hash semi-join without given a hint, and that seems to be an area they can improve on.

That said, PeopleSoft stores a lot of their code in the metadata area. If you have identified SQLs which can be drastically using a hint, you can update the SQLs.

> Thanks!
>
> - Ethan Post
Received on Sun May 11 2003 - 19:10:30 CDT