Re: Bind Sensitivity and PL/SQL cursor caching

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_at_hotmail.com> 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_at_gmail.com>
> Sent: ‎29/‎03/‎2017 09:36
> To: oracle-l <oracle-l_at_freelists.org>
> 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
>
>
>
>
>
>
>

--
http://www.freelists.org/webpage/oracle-l