Originally posted by Ethan Post
> 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
Have you tried setting always_semi_join=hash ? The default is
nested_loop - at least in Oracle 8 and 8i; in 9i the dafault changes
to choose.
--
Posted via http://dbforums.com
Received on Mon May 12 2003 - 00:27:15 CDT
