Patrick,
Instead of trying to force your query bind peeking and, eventually, hard
parsing an “optimal plan” at each execution, I would have questioned my
self whether this will not ultimately damage its performance rather then
making it running faster as you are expecting. Particularly for your case
where you have a bunch of bind variable combinations (5 bind variables) and
a relatively high number of executions. I still have not met a real life
situation where the Extended Cursor Sharing have been of a certain help. To
make my proper opinion, I’d have implemented your suggestion (in a TEST
environment where the number of executions of the query approaches that of
the PRODUCTION instance) and checked the number of generated child cursors
together with the number of rows in the gv$sql_cs_selectivity for the
corresponding parent cursor.
It might be possible as well that you can find an execution plan satisfying
the majority of the set of bind variable values; why then not fixing it and
watching out the new performance situation (as you have suggested). You may
probably not notice the penalty caused by the remaining 20% of the bind
variable values
Best regards
Mohamed Houri
2017-03-30 7:53 GMT+01:00 Patrick Jolliffe <jolliffe@xxxxxxxxx>:
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 knowwhen to 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-Sh
ared-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 evenlydistributed amongst 60,000 different combinations of values.
Most frequent combination has 50,000 records, there are 10combinations with 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 knowwhen to 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 corporationare at least aware of the issue.
Regards
Patrick