Re: Bind Sensitivity and PL/SQL cursor caching

  • From: Patrick Jolliffe <jolliffe@xxxxxxxxx>
  • To: Dominic Brooks <dombrooks@xxxxxxxxxxx>
  • Date: Wed, 29 Mar 2017 17:17:27 +0800

Actual problem case is PL/SQL implicit outer loop which is used to drive
implicit inner loop.
I am really hesitant to start asking developers to start using literals in
such cases.


On 29 March 2017 at 16:50, Dominic Brooks <dombrooks@xxxxxxxxxxx> wrote:

To me, ACS is an indication that you're using binds instead of literals.

Binds exist partly so you can have SQL and execution plans shared for all
values.

With a massive sweeping generalisation, a whole bunch of additional
complicated features exist to combat the misuse of with binds or literals.

In plsql you have the flexibility to rewrite the code to either always use
literals or to code different sql statements for different sets of values.

Cheers
Dominic

Sent from my Windows Phone
------------------------------
From: Patrick Jolliffe <jolliffe@xxxxxxxxx>
Sent: ‎29/‎03/‎2017 09:36
To: oracle-l <oracle-l@xxxxxxxxxxxxx>
Subject: Bind Sensitivity and PL/SQL cursor caching

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: