Re: Bind Sensitivity and PL/SQL cursor caching

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_at_gmail.com>:

> 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_at_soocs.de> 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 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.
>>
>> 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: _at_OracleSK
>> Upcoming online seminar: http://tinyurl.com/17-06-13-Sh
>> ared-Pool-Internals
>>
>> > Patrick Jolliffe <jolliffe_at_gmail.com> 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
>>
>
>

-- 

Houri Mohamed

Oracle DBA-Developer-Performance & Tuning

Member of Oraworld-team <http://www.oraworld-team.com/>

Visit My         - Blog <http://www.hourim.wordpress.com/>

Let's Connect -
<http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*Linkedin
Profile <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*

My Twitter <https://twitter.com/MohamedHouri>      - MohamedHouri
<https://twitter.com/MohamedHouri>

--
http://www.freelists.org/webpage/oracle-l