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