StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PORelationship between CURSOR_SHARING, Bind Variable Peeking and Histograms
text
Body
copied!<p>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.</p> <p>Ok so here's what I've gathered so far, feel free too correct me if I got anything wrong:</p> <p><b>CURSOR_SHARING</b></p> <p><b>1. = EXACT (default)</b></p> <p><li>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. <li>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). </p> <p><b>2. = FORCE</b> <li>2.1. optimizer will replace all literals with binds - and will basically use the same algorithm as scenario 1.2</p> <p><b>3. = SIMILAR</b> <li>3.1. no histogram: optimizer replaces all literals with binds -> same final effect as with 1.2 and 2.1 <li>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.</p> <p><b>Questions:</b></p> <ol> <li><p>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.</p></li> <li><p>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.</p></li> <li><p>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?</p></li> <li><p>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?</p></li> </ol> <p>Hopefully, I'm not too far off base right now but if I made any mistakes please do correct me</p> <p>Thanks!</p> <p>Edited by: BYS2 on Dec 20, 2011 12:11 PM</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload