Relationship between CURSOR_SHARING, Bind Variable Peeking and Histograms

Question

I'm trying to make sure I have a good understanding regarding the relationship between CURSOR_SHARING, bind variables, bind variable peeking and histograms as most sources cover these topics is different sections.

Ok so here's what I've gathered so far, feel free too correct me if I got anything wrong:

CURSOR_SHARING

1. = EXACT (default)

1.1. if SQL statement uses literals: the optimizer will generate a new execution plan for every combination of literals - optimizer will not replace literals with binds. A new parent cursor is generated for every literal combination.

1.2. if SQL statement uses bind variables: first time the statment is run, the optimizer will peek at the value of the bind variables and use those specific values to generate an execution plan - all future statements with those bind variables will use that same plan (even if the plan is suboptimal for other values of the bind variable).

2. = FORCE

2.1. optimizer will replace all literals with binds - and will basically use the same algorithm as scenario 1.2

3. = SIMILAR

3.1. no histogram: optimizer replaces all literals with binds -> same final effect as with 1.2 and 2.1

3.2. with histogram: optmizer replaces all literals with binds, but peeks at the bind variable EVERY time the statement is run (as opposed to just on the first run through) to see if there is a more optimal execution plan for that specific value of the bind variable (based on histogram statistics). Therefore, a new child cursor is effectively created for every distinct value of the bind variable that the optimizer encounters.

Questions:

1. From my understanding, isn't using CUSOR_SHARING = EXACT + writing SQL statments with bind-variables (1.2) lead to the exact same outcome as setting CURSOR_SHARING = FORCE (2.1)? In both cases, the optimizer will only peek at the bind variable on the first run to generate the execution plan and then reuse that plan no matter what the values of the bind variables on subsequent runs? If so, then why do most sources recommend using bind variables? this seems like it could have a significant impact on performance.

2. Is the histogram used in the initial bind variable peek for 1.2 and 2.1? As in, the first time that SQL statment is run and the optimizer peeks at the bind variable, does it use the histogram (if there is one) to determine if full-table scan or index scan is used? "Oracle Database 11g, Performance Tuning Recipes" seems to indicate that histograms are relevant only when CURSOR_SHARING = SIMILAR but some other sources indicate that the histogram is used in all the other CURSOR_SHARING settings as well.

3. In case 1.1, would the optimizer make use the histogram to determine the best execution plan? Basically I just want to know when the histogram is used. Is it only when CURSOR_SHARING = SIMILAR or for other CURSOR_SHARING settings are well?

4. Adpative Cursor Sharing - this feature will only take place if there are bind variables (either from user query or system-generated (by literal replacements)). Therefore it only takes place in 1.2, 2.1, 3.1 and 3.2? but since SIMILAR has been deprecated, does this mean that ACS only occurs in 1.2 and 2.1?

Hopefully, I'm not too far off base right now but if I made any mistakes please do correct me

Thanks!

Edited by: BYS2 on Dec 20, 2011 12:11 PM

Answer 1

1. The difference between (a) using FORCE and (b) using EXACT and coding bind variable yourself, is that in the latter case you control when bind variables are used. So if you can see that in a particular case bind variables are hurting performance, or aren't necessary, you can change that query. With FORCE, you're stuck. The reason that bind variables are recommended for OLTP type queries is that parsing is a highly serialized process that can become a big bottleneck. In OLTP systems you tend to see lots of queries that should always be using the same execution plan run with different values, so re-parsing them all the time is a waste. Any good source will also recommend that you consider when to not use bind variables -- for instance, if you only have a few possible values that can appear at a particular position in a query, and one or more of those values might benefit from a different execution plan, it may be better overall to use literals since you can parse each variant once and then reuse the cached plan.

(Another benefit to using bind variables is that it leaves you less open to SQL injection.)

2 & 3. Histograms are used in general when creating execution plans for queries, and in more ways than are obvious. Yes, in the case of a standard bind variable peek with EXACT setting, the histogram is (or at least, may be) used by the optimizer in determining the execution plan. This can be a good thing or a bad thing, depending on the skew and what particular value you have for the bind. I think the point your source is making about histograms and the SIMILAR setting is that in that case, the presence of the histogram is one of the triggers that will cause a new execution plan to be created.

(I would highly recommend Jonathan Lewis's "Cost-Based Oracle Fundamentals" for all the information you could want about when and how histograms are used.)

4.. I believe that Adaptive Cursor Sharing is essentially an enhanced version of the logic that was previously implemented for CURSOR_SHARING=SIMILAR. The optimizer will consider creating new plans based on bind variable peeking, in all circumstances. SIMILAR appears to still exist as an option. This post may provide some further helpful info.