Re: Peoplesoft: Prolific Use of EXISTS and HASH hints

From: W.Breitling <member28455_at_dbforums.com>
Date: Tue, 13 May 2003 12:04:51 +0000
Message-ID: <2872661.1052827491@dbforums.com>

1. It is session modifiable, not system modifiable: SQL> alter session set always_semi_join=hash;

Session altered.

SQL> alter system set always_semi_join=hash; alter system set always_semi_join=hash

                                      *

ERROR at line 1:
ORA-02096: specified initialization parameter is not modifiable with this option

You could put it in an after logon trigger if you can't bounce the database. It may be better nayways because then you can modify it just for certain sessions - nVision or AppEngine for example. They seem to be most riddled with exists.

b) If Oracle finds a plan for a matching sql in the shared pool it will

   use it. Changing a parameter like this does not invalidate plans. But    as you said, that's unlikely to be an issue.

Originally posted by Ethan Post
> Thanks. I will check that out. This is a heavily used system so
> hopefully
> this is ALTER SYSTEM capable incase I have to back out. That makes me
> wonder (not that this really matters since PSOFT hard parses
> 90% of it's
> SQL), if a plan is in the cache and you change a parameter like
> always_semi_join or some other parameter that would effect the
> plan using
> ALTER SYSTEM, would it reparse and come up with a new plan next time
> or
> would it use the existing plan (assuming it only soft parses).
>
> - Ethan
>
>
> "W.Breitling" wrote in message
> news:2867235.1052717235_at_dbforums.com"]news:2867235.1052717235_at_d-
> bforums.com[/url]...
>
> > > - 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/http://dbforums.com

--
Posted via http://dbforums.com

Received on Tue May 13 2003 - 07:04:51 CDT