Hey Patrick,
yes, you are right - Oracle is aware of this issue since March 2009 - so please
don't expect any fix / enhancement in near future.
Depending on the kind of dynamic SQL implementation you choose - you may run
into other issues like no bind peeking in case of DBMS_SQL (#13386678).
All of these points are the current limitations that you need to be aware of
and implement your application accordingly.
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.
Patrick Jolliffe <jolliffe@xxxxxxxxx> hat am 30. März 2017 um 07:04--
geschrieben:
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
