Bind Sensitivity and PL/SQL cursor caching

From: Patrick Jolliffe <jolliffe_at_gmail.com>
Date: Wed, 29 Mar 2017 16:34:57 +0800
Message-ID: <CABx0cSXBeoL+5QZWh7R3cw-nL5PG2qhc0ARr8rXqYvoJFUJPWw_at_mail.gmail.com>

Hi List,
Been investigating a PL/SQL process which was failing because a non-optimal plan was being used due to a combination of Bind Variable Peeking and data skew.
A /*+bind_aware*/ hint resolved my simple test when the statement it was executed from SQLPlus, however retesting via PL/SQL showed that the plan being used was still that of the first bind variables encountered. I guessed problem was due to PL/SQL cursor caching, and this seems to have been proved by disabling this (session_cached_cursors=0). I have vastly simplified testcase and workaround and provide it below. I understand what is going on, and have this workaround, but just wanted to throw this out there to see if any better solutions. (Yes I know I could lock in an acceptable plan using SPM or manipulating stats, but I think that is not really optimal, we really do need different plans for different bind variables).
What I really want is somehow to get this bind_aware/sensitivity stuff working with PL/SQL, but without having set/reset session_cached_cursors. A /*+NO_SESSION_CACHE*/ hint would be ideal I think (any downsides or complexities for implementation?)
Any other ideas? (btw Oracle 12.1.0.2)
Thanks in advance
Patrick

drop table skew_table;

create table skew_table nologging as

   select rownum id,

        case mod(rownum,  10000) when 0 then 0 else 1 end c10000,
        rpad('X', 255, 'X') padding
        from dual
        connect by level <= 1e6;

create index skew_index on skew_table(c10000); exec dbms_stats.gather_table_stats(NULL, 'SKEW_TABLE', METHOD_OPT=>'FOR ALL COLUMNS SIZE SKEWONLY'); create or replace procedure get_skew(p in number) is

   dummy number;
begin

   select /*+ bind_aware sktest */ count(*) INTO dummy FROM skew_table where c10000 = p;
end;
/

declare

   dummy number;
begin

   get_skew(0);
   get_skew(1);
end;
/

select child_number, executions, parse_calls, is_bind_sensitive, is_bind_aware from v$sql where sql_id = '1rg2w46daksr4';

CHILD_NUMBER EXECUTIONS PARSE_CALLS I
I

• ---------- ----------- - -

           0 2 1 Y Y

declare

   dummy number;
begin

   execute immediate 'ALTER SESSION SET session_cached_cursors = 0';    get_skew(0);
   get_skew(1);
   execute immediate 'ALTER SESSION RESET session_cached_cursors'; end;
/

SQL> select child_number, executions, parse_calls, is_bind_sensitive, is_bind_aware from v$sql where sql_id = '1rg2w46daksr4';

CHILD_NUMBER EXECUTIONS PARSE_CALLS I
I

• ---------- ----------- - -

           0 3 3 Y
Y

           1 1 0 Y
Y

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

Received on Wed Mar 29 2017 - 10:34:57 CEST