Re: package invocation CALL vs EXEC
- From: Tanel Poder <tanel@xxxxxxxxxxxxxx>
- To: Ls Cheng <exriscer@xxxxxxxxx>
- Date: Wed, 4 Feb 2015 13:33:33 -0500
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@xxxxxxxxx> 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
>
>
Other related posts:
