In complete agreement. Actually had a chat with developer and agreed that
refactoring the logic into single SQL should be long term solution.
Just need a shorter-term solution until then, and I think that just locking
in a 'good-enough' plan using SPM is much simpler than hacking around with
session parameters and hints.
Regards
Patrick
On 30 March 2017 at 14:21, Stefan Koehler <contact@xxxxxxxx> wrote:
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 isaffecting 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 whento use this appropriately.
Why not educating your developers and let them do it right? If they
understand when they have to do it - everything should be fine.
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-Shared-Pool-Internals
Patrick Jolliffe <jolliffe@xxxxxxxxx> hat am 30. März 2017 um 07:04geschrieben:
and total execution time is around 5 minutes except when bind variable
Just checking stats, for recent executions.
The outer query executes the inner 'problem' query about 1000 times,
peeking
issue kicks in, query spills to temp and eventually fails.block).
Inner query is moderately complex (50 lines, with 5 binds from outer
The cardinality of values on problem table is fairly evenly distributedamongst 60,000 different combinations of values.
Most frequent combination has 50,000 records, there are 10 combinationswith over 10,000 records, 500 combinations with over 1000 records, and
about 10,000 with just one record.in the join. I don't think special handling of one particular bad
I also suspect Bind Variable Peeking is happening against other tables
combination is going to help.using literals, plus the additional complexity of the code.
I would be concerned about the additional parsing required caused by
This is likely not the only area of our code where this problem isaffecting 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 whento use this appropriately.
Maybe I am really wanting to have my cake and eat it too, but I wantto be able to perform a SOFT parse on the query every time, and correct plan
automatically generated or used appropriate to bind variables.admit I haven't actually tested).
(I am assuming this is reasonably easy to achieve from say Java, but I
I can get this by using BIND_AWARE hint, and settingSESSION_CACHED_CURSORS to zero while executes, and resetting it afterwards,
but it seems
clunky.compelling arguments otherwise (maybe I am being stubborn).
I was hoping for a better solutions, but haven't yet heard any
From Stefan's comments, seems that people within Oracle corporation areat least aware of the issue.
Regards
Patrick
