Peoplesoft: Prolific Use of EXISTS and HASH hints

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