Re: Bind Sensitivity and PL/SQL cursor caching

Thanks foe the details Stefan.
Your mention of Bryn makes me realize my idea of a NO_SESSION_CACHE hint wouldn't really be workable, as this change would have to come from the PL/SQL layer, whereas SQL hints are only handled (I think) during SQL optimization.

On 29 March 2017 at 16:58, Stefan Koehler <contact_at_soocs.de> wrote:

> 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