Re: package invocation CALL vs EXEC

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_at_gmail.com> 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_at_gmail.com>
> 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_at_gmail.com>:
>>
>>> 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>
>>
>>
>

--
http://www.freelists.org/webpage/oracle-l