Forum Stats

  • 3,872,008 Users
  • 2,266,363 Discussions
  • 7,911,026 Comments

Discussions

custom context for cursor sharing

Franck Pachot
Franck Pachot Member Posts: 912 Bronze Trophy
edited Jan 11, 2016 6:16PM in Database Ideas - Ideas

A good practice in SQL development is to share cursors (thus the 'use bind variable' must important recommendation) because optimizing a cursor to find the right execution plan is resource consuming: CPU, latches, I/O (when dynamic sampling occurs), etc.

A good practice in every development is to keep code maintainable. Code re-usability is needed. If you have a complex query that includes some business rules, you don't want to write a different one for another context. Think of that query that processes all customers when called from the nightly batch job, and the same is called for a small range of customers, or even one, from the online application.

If you have that, you know the nightmare's name: bind variable peeking. When the first execution (since startup, or since cursor aged out from shared pool, or invalidated by statistics gathering) is called for few rows, the execution plan is doing index access, nested loop, or even merge join cartesian. And then the job that calls it for a large range of rows will reuse the same execution plan and takes hours and fills the tempfiles.

How to avoid that?

solution 1: Keep only one query and force a plan that is good for all cases (hints, SQL baselines, etc).

solution 2: Write two different queries for the two contexts

solution 3: change any optimizer parameter depending on the context in order to prevent cursor sharing

solution 3 is an ugly workaround, except if the parameter you change makes sense for the use cases (for example 'first rows' for the online and 'all rows' for the batch job)

The idea here is a simple feature to implement solution 3: add an 'optimizer_application_context' parameter that prevents cursor sharing when it has a different value.

Then my code can just set it to a value depending on the context, and the same code will be optimized for each context.

It can be even nicer if instead of a custom value, we can put a reference to a session value such as a sys_context parameter

I know Adaptive Cursor Sharing is there to do that for you, but a third good practice for SQL development is not to parse for each execution, and ACS occurs only at parse call.

Anyway, ACS is reactive (need to see bad execution plan first). When developer knows what has to be shared or not, better to do manual cursor sharing...

Franck PachotberxSayan MalakshinovjgarryRainer StenzelChristian Erlingerctriebtop.gunBPeaslandDBAPravin Takpire
10 votes

Active · Last Updated

Comments

  • berx
    berx Member Posts: 219 Silver Badge

    I support this idea,

    to make it even more flexible I suggest to enable such a parameter to check SERVICE, ACTION, MODULE (or any combination) as a sharing rule.

    This can enable this feature even without any change of application code (if it's instrumented already).

    Sayan MalakshinovFranck PachotPravin Takpire
  • Franck Pachot
    Franck Pachot Member Posts: 912 Bronze Trophy

    I support this idea,

    to make it even more flexible I suggest to enable such a parameter to check SERVICE, ACTION, MODULE (or any combination) as a sharing rule.

    This can enable this feature even without any change of application code (if it's instrumented already).

    Right. SERVICE should even be the default. ACTION and MODULE, I've seen people putting timestamp in it, but good is properly used.

    Pravin Takpire
  • Sayan Malakshinov
    Sayan Malakshinov Member Posts: 32 Blue Ribbon
    edited Oct 20, 2015 2:54PM

    Right. SERVICE should even be the default. ACTION and MODULE, I've seen people putting timestamp in it, but good is properly used.

    I think it would be great to have parameter where we can specify it., something like "_optimizer_additional_env_params"="SERVICE,ACTION,MODULE".

    Franck Pachot
  • jgarry
    jgarry Member Posts: 13,844 Gold Crown

    Stepping back a bit to look at an even broader picture, this arises from a desire in a non-procedural language to have the computer figure out the best method to access the data.  All the workarounds mentioned are basically dealing with shortcomings automating that method search.  It seems the more sophisticated a CBO, the easier it is to find more shortcomings.  As developers, we want to control access in a fine-grained manner, so we'll always be unhappy with these shortcomings - and of course, some will go procedural.  Only so many access paths to try, only so many buckets of histogram data, only so many hints, and that strange ACS melange...

    So I'll upvote, but I think a better long-term solution is to have a more complete and consistent language for telling Oracle what we want to do when we don't think it is finding the optimal solution.  That might be a whole advanced degree thesis for someone.  Though it will probably never happen, as people throw work into virtual tables in flash, or what-all.

    Franck Pachotberx