Re: package invocation CALL vs EXEC
Date: Sun, 8 Feb 2015 23:09:46 +0100
Message-ID: <CAJ2-Qb8bY9CRtF0kPyVdSxeq74AJFNB9MySgpgk99ZLfV6YmAg_at_mail.gmail.com>
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 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-lReceived on Sun Feb 08 2015 - 23:09:46 CET