Just checking stats, for recent executions.
The outer query executes the inner 'problem' query about 1000 times, and
total execution time is around 5 minutes except when bind variable peeking
issue kicks in, query spills to temp and eventually fails.
Inner query is moderately complex (50 lines, with 5 binds from outer
block).
The cardinality of values on problem table is fairly evenly distributed
amongst 60,000 different combinations of values.
Most frequent combination has 50,000 records, there are 10 combinations
with over 10,000 records, 500 combinations with over 1000 records, and
about 10,000 with just one record.
I also suspect Bind Variable Peeking is happening against other tables in
the join. I don't think special handling of one particular bad combination
is going to help.
I would be concerned about the additional parsing required caused by using
literals, plus the additional complexity of the code.
This is likely not the only area of our code where this problem is
affecting us, and I really don't want to start pushing our developers down
the "EXECUTE IMMEDIATE"/No Bind Variables as I don't trust them to know
when to use this appropriately.
Maybe I am really wanting to have my cake and eat it too, but I want to be
able to perform a SOFT parse on the query every time, and correct plan
automatically generated or used appropriate to bind variables.
(I am assuming this is reasonably easy to achieve from say Java, but I
admit I haven't actually tested).
I can get this by using BIND_AWARE hint, and setting SESSION_CACHED_CURSORS
to zero while executes, and resetting it afterwards, but it seems clunky.
I was hoping for a better solutions, but haven't yet heard any compelling
arguments otherwise (maybe I am being stubborn).
From Stefan's comments, seems that people within Oracle corporation are at
least aware of the issue.
Regards
Patrick
On 30 March 2017 at 06:48, William Robertson <william@xxxxxxxxxxxxxxxxxxxx>
wrote:
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@xxxxxxxxxx> 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@xxxxxxxxxx> 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@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-Sh
ared-Pool-Internals
Patrick Jolliffe <jolliffe@xxxxxxxxx> hat am 29. März 2017 um 10:34geschrieben:
non-optimal plan was being used due to a combination of Bind Variable
Hi List,
Been investigating a PL/SQL process which was failing because a
Peeking and
data skew.was executed from SQLPlus, however retesting via PL/SQL showed that the plan
A /*+bind_aware*/ hint resolved my simple test when the statement it
being used was still that of the first bind variables encountered.have been proved by disabling this (session_cached_cursors=0).
I guessed problem was due to PL/SQL cursor caching, and this seems to
I have vastly simplified testcase and workaround and provide it below.wanted to throw this out there to see if any better solutions.
I understand what is going on, and have this workaround, but just
(Yes I know I could lock in an acceptable plan using SPM ormanipulating stats, but I think that is not really optimal, we really do
need different
plans for different bind variables).stuff working with PL/SQL, but without having set/reset
What I really want is somehow to get this bind_aware/sensitivity
session_cached_cursors.
A /*+NO_SESSION_CACHE*/ hint would be ideal I think (any downsides orcomplexities for implementation?)
Any other ideas? (btw Oracle 12.1.0.2)METHOD_OPT=>'FOR ALL COLUMNS SIZE SKEWONLY');
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',
skew_table where c10000 = p;
create or replace procedure get_skew(p in number)
is
dummy number;
begin
select /*+ bind_aware sktest */ count(*) INTO dummy FROM
end;is_bind_aware from v$sql where sql_id = '1rg2w46daksr4';
/
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,
CHILD_NUMBER EXECUTIONS PARSE_CALLS I I
