Re: package invocation CALL vs EXEC
- From: Ls Cheng <exriscer@xxxxxxxxx>
- To: Tanel Poder <tanel@xxxxxxxxxxxxxx>
- Date: Sun, 8 Feb 2015 20:51:42 +0100
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@xxxxxxxxxxxxxx> 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@xxxxxxxxx> wrote:
>
>> hmm
>>
>> I didnt flush the shared pool.
>>
>> What I did was
>>
>>
>
Other related posts: