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
