Re: package invocation CALL vs EXEC
Date: Wed, 4 Feb 2015 13:33:33 -0500
Message-ID: <CAMHX9JLX3z3uZtpFu__dFP5cVjaLC1MZhcWP4_WuuRpNCkxEpw_at_mail.gmail.com>
Did you flush the shared pool between these experiments? The _serial_direct_read parameter is not part of the optimizer environment, so if you already had a cursor with _serial_direct_read never/auto in library cache, it would have been reused (and with the never/auto setting) even if you changed the parameter with alter session later on. There's a chance that the CALL vs EXEC causes some difference in the compilation envioronment and that forced a different SQL child cursor to be parsed for CALL (and that happened to pick up the new _serial_direct_read value).
I think I discussed this in the Oracle parameters infrastructure hacking
session :
http://enkitec.tv/2012/06/24/oracle-hacking-session-with-tanel-poder-oracle-parameter-infrastructure/
Tanel.
On Wed, Feb 4, 2015 at 9:41 AM, Ls Cheng <exriscer_at_gmail.com> wrote:
> Hi Mauro
>
> 1. I have observed this behaviour in 11.2.0.4, AIX (also 11.2.0.3 exadata)
> 2. I didnt check the recursive queries so cant tell
> 3. Same parameters were used, cut & paste between executions changing only
> CALL to EXEC and cursor_sharing is set to EXACT
> 4. table size did not change, I ran the CALL and EXEC one after another
> several times and since it's a reporting system the tables didnt change in
> size or data distribution
> 5. _SERIAL_DIRECT_READ was set to ALWAYS s I am not sure about the
> percentage of table being cached matters, I understand that if
> _SERIAL_DIRECT_READ is used it shouldnt matter
>
> Quite of us were surprised when we saw this behaviour, it' such a basic
> stuff that we never imagined it could cause such difference :-)
>
> Thanks
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Feb 04 2015 - 19:33:33 CET