Bind Sensitivity and PL/SQL cursor caching

I have 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.
I put together a reproducible test case executing the problem SQL from SQLPlus, and found that addition of a /*+bind_aware*/ hint to inform CBO that this query was bind sensitive seemed to resolve the issue.

However retesting via a PL/SQL showed that the plan being used was still that of the first bind variables encountered.

I have simplified the testcase to more easily reproduce the behavior I was seeing (database version 12.1.0.2):

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;   
/

Checking v$sql we can see only one plan was generated:

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

I guessed that PL/SQL cursor caching was resulting in even the soft parse being skipped, so disabled this optimization in the calling code:

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;   
/

You can see now that the cursor is re-parsed, and that appropriate plans based on bind variables is now taking place correctly:

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

Update: while this was interesting to investigate, for the live issue I have simply locked in a plan that is acceptable (if not optimal) for all values.

Leave a Comment Cancel Reply