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 22.214.171.124):
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.