Re: package invocation CALL vs EXEC
- From: Ls Cheng <exriscer@xxxxxxxxx>
- To: Tanel Poder <tanel@xxxxxxxxxxxxxx>
- Date: Wed, 4 Feb 2015 19:45:28 +0100
hmm
I didnt flush the shared pool.
What I did was
alter session set "_serial_direct_read" = ALWAYS;
call package.procedure('A', 'B', 'C')
call package.procedure('A', 'B', 'C')
call package.procedure('A', 'B', 'C')
exec package.procedure('A', 'B', 'C')
exec package.procedure('A', 'B', 'C')
exec package.procedure('A', 'B', 'C')
call package.procedure('A', 'B', 'C')
exec package.procedure('A', 'B', 'C')
in all cases direct reads kicks in when EXEC is used otherwise db file
scattered read is observed
I will check next week if flushing shared pool makes difference
Cheers
On Wed, Feb 4, 2015 at 7:33 PM, Tanel Poder <tanel@xxxxxxxxxxxxxx> wrote:
> 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:
