Re: Bind Sensitivity and PL/SQL cursor caching
From: William Robertson <william_at_williamrobertson.net>
Date: Wed, 29 Mar 2017 23:48:55 +0100
Message-Id: <718CCF5F-A534-4EAD-A430-45FCD565FDAA_at_williamrobertson.net>
>>>> 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
>>>>
Date: Wed, 29 Mar 2017 23:48:55 +0100
Message-Id: <718CCF5F-A534-4EAD-A430-45FCD565FDAA_at_williamrobertson.net>
There are good reasons for avoiding both dynamic SQL and duplicated code, so even though they may provide a performance fix I think a certain reluctance to go down that path is understandable.
William
On 29 Mar 2017, at 11:47, Lothar Flatz <l.flatz_at_bluewin.ch> wrote:
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> 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-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
>>>>
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Mar 30 2017 - 00:48:55 CEST