Re: Bind Sensitivity and PL/SQL cursor caching

In your case searching with literals will result in precise plans. If that is what you need you should do it. Plain and simple.  From my point /*+NO_SESSION_CACHE*/ is an award way to avoid the straight solution.
As you can see, elegance is in the eye of the beholder. ;-) "Inelegant" sounds in your words a bit like "I am uncomfortable with it because I don't know it well enough."

On 29.03.2017 12:26, Patrick Jolliffe wrote:
> 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
> <mailto: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
>> <mailto: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 <tel: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
>> <http://tinyurl.com/17-06-13-Shared-Pool-Internals>
>>
>> > Patrick Jolliffe <jolliffe_at_gmail.com
>> <mailto: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