Re: package invocation CALL vs EXEC

From: Mauro Pagano <mauro.pagano_at_gmail.com>
Date: Sun, 8 Feb 2015 16:43:22 -0500
Message-ID: <CAAnDMS1UuUDQ3-qjSMQvutZ+bGqECJNuUAoqh_2iZFkOx0Miew_at_mail.gmail.com>

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 asselect *from dba_source;insert into t5select * from
> t5;insert into t5select * from t5;insert into t5select * from
> t5;commit;create or replace procedure p1is 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 EXECselect EVENT,TOTAL_WAITSfrom
> v$session_eventwhere 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 readcall p1();call
> p1();call
> p1();EVENT
> TOTAL_WAITS----------------------------------------------------------------
> -----------db file scattered
> read 214-- exec does use
> direct path readsexec p1exec p1exec p1select EVENT,TOTAL_WAITSfrom
> v$session_eventwhere sid = sys_context('USERENV', 'SID')and event in
> ('direct path read', 'db file scattered
> read');EVENT
> TOTAL_WAITS----------------------------------------------------------------
> -----------db file scattered
> read 214direct path
> read 273select
> 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_WAITSfrom
> v$session_eventwhere 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 readsexec p1exec p1exec
> p1EVENT
> TOTAL_WAITS----------------------------------------------------------------
> -----------db file scattered
> read 32direct path
> read 436-- call
> does use now direct path reads, inherits EXEC behaviourcall p1();call
> p1();call p1();select EVENT,TOTAL_WAITSfrom v$session_eventwhere sid =
> sys_context('USERENV', 'SID')and event in ('direct path read', 'db file
> scattered
> read');EVENT
> TOTAL_WAITS----------------------------------------------------------------
> -----------db file scattered
> read 32direct path
> read 872select
> 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 - 22:43:22 CET