Re: package invocation CALL vs EXEC

From: Ls Cheng <exriscer_at_gmail.com>
Date: Sun, 8 Feb 2015 20:51:42 +0100
Message-ID: <CAJ2-Qb8nvBArienrUFXR5NC1pc33jPqUkVTfgXkKHJKwZwvpjg_at_mail.gmail.com>

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 - 20:51:42 CET