Home -> Mailing Lists -> Oracle-L -> Re: package invocation CALL vs EXEC

Re: package invocation CALL vs EXEC

From: Mauro Pagano <mauro.pagano_at_gmail.com>
Date: Sun, 8 Feb 2015 17:16:04 -0500
Message-Id: <607D097E-D482-44D6-B5E1-2416F7F9AA6A_at_gmail.com> 





Sorry I wasn't clear, I meant ASM as a quick way to refer to the tbs in the storage cells aka Exadata specific (ASM shouldn't play any part in this behavior)


> On Feb 8, 2015, at 5:09 PM, Ls Cheng <exriscer_at_gmail.com> wrote:
> 
> in the AIX environment where I hit the problem the 11.2.0.4 database runs on ASM
> 
> I will test tmorrow with a few more environments
> 
> thanks
> 
> 
>> On Sun, Feb 8, 2015 at 10:43 PM, Mauro Pagano <mauro.pagano_at_gmail.com> wrote:
>> I think the fix for 15882436 is incomplete, reason is this behavior doesn't reproduce anymore in Exadata starting 11.2.0.4 (indeed fix for 15882436) but it still does all the way up to 12.1.0.2 in non-Exa database and even in Exadata if the object is in a tbs on filesystem.
>> Following some test runs
>> 
>> Exadata 11.2.0.3, tbs inside ASM  -> cell multiblock physical read
>> Exadata 11.2.0.4, tbs inside ASM  -> cell smart table scan
>> Exadata 11.2.0.4, tbs on filesystem -> db file scattered read
>> Exadata 12.1.0.2, tbs inside ASM -> cell smart table scan
>> Exadata 12.1.0.2, tbs on filesystem -> db file scattered read
>> 
>> Maybe the fix was made Exadata specific instead of generic, I suggest you file a SR with Oracle Support to get it sorted out
>> 




>>> On Sun, Feb 8, 2015 at 2:51 PM, Ls Cheng <exriscer_at_gmail.com> wrote:

>>> Hi

>>> 

>>> I reproduced the issue in a test database, Linux x64_86, 11.2.0.4.

>>> 

>>> Strange behaviour, if I run exec first then call, the call statement uses direct path reads, if I run call first the exec then call does not use direct path reads, in both cases there is only one cursor. 

>>> 

>>> Test Case with 400MB buffer cache:

>>> 

>>> 

>>> create table t5 

>>> as

>>> select *

>>> from dba_source;

>>> 

>>> insert into t5

>>> select * from t5;

>>> 

>>> insert into t5

>>> select * from t5;

>>> 

>>> insert into t5

>>> select * from t5;

>>> 

>>> commit;

>>> 

>>> create or replace procedure p1

>>> is

>>>     l_cnt number;

>>> begin

>>>     select /*+ TEST01 */ count(*)

>>>       into l_cnt

>>>       from t5;

>>> end;

>>> /

>>> 

>>> alter system flush shared_pool;

>>> 

>>> alter system flush buffer_cache;

>>> 

>>> alter session set "_serial_direct_read" = ALWAYS;

>>> 

>>> 

>>> -- now use CALL first then EXEC

>>> 

>>> select EVENT,TOTAL_WAITS

>>> from v$session_event

>>> where sid = sys_context('USERENV', 'SID')

>>> and event in ('direct path read', 'db file scattered read');

>>> 

>>> no rows selected

>>> 

>>> -- call does not use direct path reads, only db file scattered read

>>> call p1();

>>> 

>>> call p1();

>>> 

>>> call p1();

>>> 

>>> EVENT                                                            TOTAL_WAITS


>>> ---------------------------------------------------------------- -----------

>>> db file scattered read                                                   214

>>> 

>>> -- exec does use direct path reads

>>> exec p1

>>> 

>>> exec p1

>>> 

>>> exec p1

>>> 

>>> select EVENT,TOTAL_WAITS

>>> from v$session_event

>>> where sid = sys_context('USERENV', 'SID')

>>> and event in ('direct path read', 'db file scattered read');

>>> 

>>> EVENT                                                            TOTAL_WAITS


>>> ---------------------------------------------------------------- -----------

>>> db file scattered read                                                   214

>>> direct path read                                                         273

>>> 

>>> select sql_id, child_number, disk_reads, buffer_gets, executions from v$sql where sql_text like '%TEST01%' and sql_text not like '%v$sql%';

>>> 

>>> SQL_ID        CHILD_NUMBER DISK_READS BUFFER_GETS EXECUTIONS


>>> ------------- ------------ ---------- ----------- ----------

>>> 6c53gs1u556fm            0      52530      105216          6

>>> 

>>> exit

>>> 

>>> -- now use EXEC first then CALL

>>> 

>>> alter system flush shared_pool;

>>> 

>>> alter system flush buffer_cache;

>>> 

>>> alter session set "_serial_direct_read" = ALWAYS;

>>> 

>>> select EVENT,TOTAL_WAITS

>>> from v$session_event

>>> where sid = sys_context('USERENV', 'SID')

>>> and event in ('direct path read', 'db file scattered read');

>>> 

>>> EVENT                                                            TOTAL_WAITS


>>> ---------------------------------------------------------------- -----------

>>> db file scattered read                                                     3

>>> 

>>> -- exec does use direct path reads

>>> exec p1

>>> 

>>> exec p1

>>> 

>>> exec p1

>>> 

>>> EVENT                                                            TOTAL_WAITS


>>> ---------------------------------------------------------------- -----------

>>> db file scattered read                                                    32

>>> direct path read                                                         436

>>> 

>>> -- call does use now direct path reads, inherits EXEC behaviour

>>> call p1();

>>> 

>>> call p1();

>>> 

>>> call p1();

>>> 

>>> select EVENT,TOTAL_WAITS

>>> from v$session_event

>>> where sid = sys_context('USERENV', 'SID')

>>> and event in ('direct path read', 'db file scattered read');

>>> 

>>> EVENT                                                            TOTAL_WAITS


>>> ---------------------------------------------------------------- -----------

>>> db file scattered read                                                    32

>>> direct path read                                                         872

>>> 

>>> select sql_id, child_number, disk_reads, buffer_gets, executions from v$sql where sql_text like '%TEST01%' and sql_text not like '%v$sql%';

>>> 

>>> SQL_ID        CHILD_NUMBER DISK_READS BUFFER_GETS EXECUTIONS


>>> ------------- ------------ ---------- ----------- ----------

>>> 6c53gs1u556fm            0     105292      105188          6

>>> 

>>> 

>>> 

>>> 

>>> 

>>> 

>>> 

>>> 


>>>> On Wed, Feb 4, 2015 at 7:49 PM, Tanel Poder <tanel_at_tanelpoder.com> wrote:
>>>> You can check if you get two (or more) child cursors for your SQL query (and use v$sql_shared_cursor to see why). 
>>>> 
>>>>> On Wed, Feb 4, 2015 at 1:45 PM, Ls Cheng <exriscer_at_gmail.com> wrote:
>>>>> hmm
>>>>> 
>>>>> I didnt flush the shared pool.
>>>>> 
>>>>> What I did was
> 




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


Received on Sun Feb 08 2015 - 23:16:04 CET












Original text of this message