In this Document
| Purpose |
| Scope |
| Details |
| References |
APPLIES TO:
Oracle Database - Enterprise Edition - Version 9.2.0.1 to 11.2.0.2 [Release 9.2 to 11.2]Information in this document applies to any platform.
ALL PRODUCT ACCESS
PURPOSE
This document explains regarding safe and unsafe literals and binds and their effect on query optimization.
SCOPE
This document is intended for Support Analysts and customers
DETAILS
Introduction
When bind variables are peeked, the parse engine makes a decision as to the 'safety' of these peeked values for creating plans based upon whether it is felt that different values could produce different plans.
The usual (but not the only) reason for such different plans is the use of CURSOR_SHARING=SIMILAR and the presence of histogram column statistics on the column that the bind is being compared with when using the Cost Based Optimizer (CBO). If there are histograms on the column, then the bind value may be deemed to be 'unsafe' because there is the potential chance that the different values could produce a different explain plan and the selection of a single plan for all values may not be 'safe' in terms of performance. If the bind is deemed 'unsafe' then multiple children are created for each set of different bound values so that different plans can be associated with them. This occurs in a few scenarios but the most common is with histogram stats on an equality predicate.
CURSOR_SHARING=SIMILAR
With CURSOR_SHARING=SIMILAR whenever the optimizer examines any system-generated bind variable (ie where a literal has been replaced with a bind) to make a decision, then that bind is checked to see if it should be considered unsafe. The check made is :
Is the operator NEITHER of '=' or '!='
OR
Are there Column Histograms present on the column.
If either of these is true then the bind is deemed to be unsafe and a new cursor will be created (So binds used in non equality predicates (eg >, <, >=, <=, LIKE) are unsafe). To check for whether a bind is considered unsafe see:
With histogram stats on an equality predicate, this can cause severe problems (in terms of sharing) if there is, for example, a histogram on a main key column.eg:
select ... from orders where orderid='literal';
If there is a histogram on "orderid" then the bind will likely get marked unsafe and a new child will be produced for every single value of 'literal'. The SQL would only be shared if the value of 'literal' matches exactly to a value used already.
Remember that if literals are converted to binds due to CURSOR_SHARING then they are subject to this checking, though unsafe binds are really only an issue if CURSOR_SHARING is SIMILAR.
In an OLTP type environment it would be sensible to only use histogram statistics on columns that need it (i.e. where there are only a few distinct values with heavy skew and where different plans are needed for different predicate values). This way most SQL is shared and the absence of histograms keeps predicates safe except where needed.
CURSOR_SHARING = FORCE
If CURSOR_SHARING = FORCE binds can still be "unsafe" (in terms of performance) if used by the optimizer in decisions but this should not then affect share-ability of the SQL since CURSOR_SHARING=FORCE does not care about unsafe literals, but the cursor should still be shared. e.g.: In the above example with orderid='literal', without histograms, the CBO does not need to look at 'literal' to determine the selectivity of the predicate and so the bind does not get marked unsafe.
If there are histograms, the predicate is marked as unsafe, but since FORCE uses the same plan whatever the circumstance, this does not matter. Only where non data literals for which different values alter the actual meaning of the SQL (e.g. order by 1 versus order by 2) will an unsafe predicate have an affect on plans.
Note that, prior to 11g, unsafe literals are NOT covered by 'bind mismatch' in V$SQL_SHARED_CURSOR as this is for user bind metadata mismatches. ie: different max bind lengths or bind type mismatches.
In 11g R2 (and 11.1.0.7 Patchset) a new column has been added to V$SQL_SHARED_CURSOR to check if literal replacement is used with CURSOR_SHARING=SIMILAR. The new column HASH_MATCH_FAILED is set to "Y" if sharing fails due to a hash mismatch, such as the case with mismatched histogram data or a range predicate marked as unsafe by literal replacement. The unshared child cursors may have histogram data
on key columns used in equality predicates, or range predicates with literals which the optimizer has marked
as unsafe.
From the optimizer point of view, these additional plans for 'unsafe' bind variables explain why multiple good plans may occur for peeked bind variables, even though the standard behavior for binds is to use peeked binds for the initial parse and then use the resultant plan for all other iterations. With unsafe binds, different plans for different peeked values can occur.
Background Information
This issue has been coming more in to focus with dynamic sampling in 10.2 since the default was changed from 1 to 2. When optimizer_dynamic_sampling is greater than 1 then Dynamic sampling emulates statistics + histograms. If histograms are created, then binds may be marked as unsafe and produce different plans for different values. With optimizer_dynamic_sampling > 1 a predicate can create a new version of a sql statement for each different value, even if there are no histograms (or even statistics) on a table (since dynamic sampling may create these in the background).
REFERENCES
BUG:2992537 - 9I CBO USES MERGE JOIN CARTESIAN INAPPROPRIATELY.
BUG:3132098 - BIND PEEKING DOES NOT WORK FOR DUPLICATE BINDS .
BUG:5929112 - CURSOR_SHARING = SIMILAR|FORCE DO NOT SHARE SQL - DOCUMENTATION
NOTE:261020.1 - High Version Count with CURSOR_SHARING = SIMILAR or FORCE
NOTE:270056.1 - Parameter "OPTIMIZER_DYNAMIC_SAMPLING" behaviour has changed since Oracle 9.2.0.3.
NOTE:296377.1 - Troubleshooting: High Version Count Issues
NOTE:336267.1 - Optimizer Dynamic Sampling (OPTIMIZER_DYNAMIC_SAMPLING)
NOTE:70075.1 - Use of bind variables in queries (Pre 9i)
NOTE:94036.1 - Init.ora Parameter "CURSOR_SHARING" Reference Note
587
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
