RE: Bind Sensitivity and PL/SQL cursor caching

Patrick,

>> 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.

So, are you going to accept and store multiple plans using SPM?

-BA

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Patrick Jolliffe Sent: Wednesday, March 29, 2017 11:54 PM To: Stefan Koehler
Cc: oracle-l
Subject: Re: Bind Sensitivity and PL/SQL cursor caching

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<mailto: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-Shared-Pool-Internals

> Patrick Jolliffe <jolliffe_at_gmail.com<mailto: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

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