Re: Bind Sensitivity and PL/SQL cursor caching

Hey Patrick,
what you see here is an effect of the (old) soft-parse avoidance scheme implementation. We already had discussions with Bryn about these issues as it also affects statistics / cardinality feedback.

There are some open bugs / enhancement requests (e.g. #8357294 or #25158799) for these issues but Oracle has not adjusted the implementation until yet and if you look at the dates of these bugs - i guess we can't expect some enhancements in near future.  

Best Regards
Stefan Koehler

Independent Oracle performance consultant and researcher Website: http://www.soocs.de
Twitter: _at_OracleSK
Upcoming online seminar: http://tinyurl.com/17-06-13-Shared-Pool-Internals  

> Patrick Jolliffe <jolliffe_at_gmail.com> hat am 29. März 2017 um 10:34 geschrieben:
>
> 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