Skip to Main Content

Database Software

custom context for cursor sharing

Franck PachotOct 20 2015 — edited Jan 11 2016

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

Post Details
Added on Oct 20 2015