Re: package invocation CALL vs EXEC
- From: Ls Cheng <exriscer@xxxxxxxxx>
- To: Mauro Pagano <mauro.pagano@xxxxxxxxx>
- Date: Wed, 4 Feb 2015 15:41:29 +0100
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
On Wed, Feb 4, 2015 at 3:13 PM, Mauro Pagano <mauro.pagano@xxxxxxxxx> wrote:
> Hi Ls,
>
> Unfortunately there is not much detail here to comment about so we'll have
> to make some assumptions.
> Assuming that 1. you are on a version before 11.2.0.4 2. after changing
> the "CALL" with "EXEC" the plan for the recursive SQLs were the same as
> before 3. same binds/parameters were used 4. data in the underlying tables
> didn't change much (table size is a factor in the decision to go buffered
> vs direct path) 5. aprox the same percentage of the table was cached
> (another factor considered in buffered vs direct path) then this might be a
> match of bug 15882436, details in MOS ID 15882436.8
>
> Hope it helps,
> Mauro
>
> On Sun, Feb 1, 2015 at 5:22 PM, Ls Cheng <exriscer@xxxxxxxxx> wrote:
>
>> Hi Mohamed
>>
>> The customer ran packages using call package.procedure(), when I changed
>> it to exec package.procedure() direct path reads kicked in, otherwise it
>> used db file scattered read.
>>
>> On Sun, Feb 1, 2015 at 8:09 PM, Mohamed Houri <mohamed.houri@xxxxxxxxx>
>> wrote:
>>
>>> I know one difference in calling a package using
>>>
>>> *call package.procedure()*
>>>
>>> versus
>>>
>>> *begin*
>>> * package.procedure()*
>>> *end;*
>>>
>>> In case you use the cursor sharing FORCE, bind variable substitution
>>> will occur in the first manner of calling the package while it will not
>>> occur in the pl/sql begin end call
>>>
>>> https://hourim.wordpress.com/?s=bind+variable
>>>
>>> As a call represents a SQL function, exception like no_data_found and
>>> when to many rows will not be reported as error while in th begin end call
>>> those two errors will be reported
>>>
>>> I didn't tested the comparison between call and exec.
>>>
>>> Best regards
>>> Mohamed Houri
>>> www.hourim.wordpress.com
>>>
>>> 2015-02-01 19:17 GMT+01:00 Ls Cheng <exriscer@xxxxxxxxx>:
>>>
>>>> Hi all
>>>>
>>>> Does anyone know the difference between invoking a package using CALL
>>>> or EXEC?
>>>>
>>>> I hit a bug recently, 15882436, and wondering if anyone know the
>>>> difference between using call and exec.
>>>>
>>>> Even the bug is for Exadata I have been bitten in both Exadata 11.2.0.3
>>>> and AIX 11.2.0.4 environments
>>>>
>>>> Thanks
>>>>
>>>>
>>>>
>>>
>>>
>>> --
>>>
>>> Houri Mohamed
>>>
>>> Oracle DBA-Developer-Performance & Tuning
>>>
>>> Member of Oraworld-team <http://www.oraworld-team.com/>
>>>
>>> Visit My - Blog <http://www.hourim.wordpress.com/>
>>>
>>> Let's Connect -
>>> <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*Linkedin
>>> Profile <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*
>>>
>>> My Twitter <https://twitter.com/MohamedHouri> - MohamedHouri
>>> <https://twitter.com/MohamedHouri>
>>>
>>>
>>
>
Other related posts:
