Re: Bind Sensitivity and PL/SQL cursor caching

What opt_param hint?
All your comments seem to be referring to dynamic SQL which we are not using in this case.
This is not my code, but basically the reason that we are using bind variables is that this is all coded in PL/SQL. I understand that we can 'force' PL/SQL to use literals instead of bind variables by using Dynamic SQL, I think it's pretty inelegant. At the moment we have a implicit cursor in the outer loop driving implicit cursor in inner loop, nice and simple.
I think the developers will struggle if having to re-code using Dynamic SQL, there will be more opportunity for bugs, and I have a concern that if I push them down this route, they will (ab)use this when it's not appropriate
(I realize these are not all technical reasons) Regards
Patrick

On 29 March 2017 at 18:02, Lothar Flatz <l.flatz_at_bluewin.ch> wrote:

>
> The opt_param hint could be of some help. Did not try though.
> Any unique comment would trigger a reparse. You have to phrase it as a
> hint, otherwise PL/SQL would strip it from the sql text.
> Placed at the end of the hint some arbitrary text normally works fine.
> Should be easy enough with a dynamic sql.
> I sometimes thought about putting some hint text that would sort of
> categorize the search criteria quality. (e.g. "A" means good search
> criteria, "B" middle and "C" bad.)
> However looks like you have not answered the question why you were not
> using literals in the first place.
>
> Regards
>
> Lothar
>
>
> On 29.03.2017 11:31, Patrick Jolliffe wrote:
>
> 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-Sh
>> ared-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