Re: Bind Sensitivity and PL/SQL cursor caching

  • From: Patrick Jolliffe <jolliffe@xxxxxxxxx>
  • To: Stefan Koehler <contact@xxxxxxxx>
  • Date: Wed, 29 Mar 2017 17:31:13 +0800

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@xxxxxxxx> 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: @OracleSK
Upcoming online seminar: http://tinyurl.com/17-06-13-Shared-Pool-Internals

Patrick Jolliffe <jolliffe@xxxxxxxxx> 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


Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 03-2017