autotrace在绑定变量情况下不准确的问题

Comments

1. admin says

   2010/08/03 at 16:00

   Applies to:
   Oracle Server – Enterprise Edition – Version: RDBMS to 9.2
   This problem can occur on any platform.
   Symptoms
   10046 raw trace file does not have the same execution plan as autotrace and explain plan for

   Cause

   As per bug 4026101 this is a normal behaviour when bind peeking behaviour is enabled. Bind
   peeking is the default behaviour in 9.2

   It says in internal comments that explain plan does not peek values from bind variables.

   Bind peeking is the feature allowing the CBO to take the value of a bind variable in count when
   evaluating an execution plan at parse time.

   When bind peeking is active CBO will choose the most optimal execution plan for the query
   according to the bind variable value given when a hard parse occurrs.

   Solution

   To tune the query use v$sql_plan or tkprof output using different value for bind variables and
   compare the execution plans in both cases.

   If you wish to deactivate bind peeking you can set
   alter system set “_OPTIM_PEEK_USER_BINDS”=FALSE.

   When runing tkprof “explain=username/password” argument must NOT be used. That will cause
   tkprof to issue an explain plan whose output could differ from the execution plan info inside the raw 10046/sql_trace file.

   回复
2. admin says

   2010/08/03 at 16:05

   Query using Bind Variables is suddenly slow
   Applies to:
   Oracle Server – Enterprise Edition – Version: 9.0.1 to 10.2
   This problem can occur on any platform.
   Symptoms

   You are running on a database at 9.x or above, and have observed that sometimes, for no apparent
   reason, some SQL which has been running fine suddenly runs very poorly. You have made no changes
   to the data, the SQL, or the statistics for the objects involved.

   On further examination of the SQL it can be seen that it is using bind variables.
   Cause

   One reason for this behaviour may be explained by the use of a feature introduced in 9.x called bind
   peeking.

   With this feature the query optimizer peeks at the values of user-defined bind variables on the first
   invocation of a cursor. This feature lets the optimizer determine the selectivity of any WHERE
   clause condition, based on this value just as if a literal had been used instead of a bind variable.
   On subsequent invocations of the cursor, no peeking takes place, and the cursor is shared, based on
   the standard cursor-sharing criteria, even if subsequent invocations use different bind values.
   Thus if the first set of bind values that happen to be used when the cursor is first hard-parsed are not
   representative then the plan may be inappropriate for subsequent executions.

   The Oracle 10.2 Database Performance Tuning Guide manual Chapter 13 “The Query Optimizer” says the
   following about peeking bind variables :-

   “When bind variables are used in a statement, it is assumed that cursor sharing is intended and that
   different invocations are supposed to use the same execution plan. If different invocations of the
   cursor would significantly benefit from different execution plans, then bind variables may have been
   used inappropriately in the SQL statement.”

   Bind peeking has been known to cause a different execution plan to be used on different nodes of a RAC
   cluster, because each node has its own Shared Pool, and despite the same SQL, data, and statistics the
   first time a cursor was hard parsed on each node a different set of bind values was presented to the
   optimizer, and it thus chose a different plan on each node.

   There is a hidden parameter which controls this feature’s behaviour, whose default value is TRUE.
   Although a stable plan can be achieved by setting the parameter off it must be realized that this stable
   plan is not necessarily the optimum plan for all bind values. Consider the following simple example where
   tablex has 10,000 rows and col1 has an index.

   SELECT
   FROM tablex
   WHERE col1 BETWEEN :bind1 AND :bind2;

   If this SQL is used, for example, with values 123 and 124 in order to pick out 2 rows from the
   10,000 rows in the table then using an index would be the obvious choice.
   However, if the same SQL is used with bind values 123 and 9999 then we would be getting the vast majority
   of the rows and a full table scan would be more appropriate. But the optimizer cannot now know this,
   and does not change the plan accordingly.
   Solution

   In this sort of situation it would perhaps be a good idea to modify the application and have two separate
   modules/sections each with the above SQL, but with a variation (perhaps modified with a hint) that will result
   in the desired plan. The appropriate module could then be invoked programmatically. An example might be
   a situation where you use essentially the same SQL to query the pay for one employee or all 10,000 employees.
   The query for one employee should use indexes, and the query for all employees should do a full table scan.

   N.B. The cursor will be moved out of the Shared Pool and therefore require a hard parse on subsequent
   invocation in a number of circumstances, such as :-

   1) database shutdown/restart

   2) cursor not in use by any session, and aged out by LRU algorithm

   3) change to the stats associated with any referenced object (eg following a gather stats job)

   4) change to the structure of any referenced object (eg alter table)

   5) Granting/revoking privileges on a referenced object

   It will NOT get moved out by flushing the Shared Pool if it is pinned (ie in use)

   CONCLUSION
   ==========

   It is desirable to share cursors, particularly in an OLTP environment, for all the good reasons outlined in

   Note 62143.1 Understanding/Tuning the Shared Pool in Oracle7, 8, 8i

   Thus coding bind variables, or perhaps using CURSOR_SHARING values of SIMILAR or FORCE, is an appropriate
   path to follow, but it must be realized that having bind peeking may result in unpredictable execution
   plans dependent on the first set of bind values presented to the optimizer on hard parse.
   Tuning SQL with hints and coding your application to allow the use of the appropriate “version” of the
   SQL, or using literal values, is the preferred method of dealing with SQL having changing
   execution plans due to bind peeking, but if necessary this feature can also be disabled.

   To set this feature off for the whole database :-

   a) set _OPTIM_PEEK_USER_BINDS=FALSE in the spfile/init.ora

   or just for the session :-

   b) use alter session set “_OPTIM_PEEK_USER_BINDS”=FALSE;

   For a good case study where this was a factor please see Note 369427.1 “Case Study: The Mysterious Performance Drop”

   N.B. Please also be aware of the following

   i) (unpublished) Bug:5082178 (fixed in 10.2.0.4 and 11.x).

   Details:
   In some situations bind peeking can occur when it should not eg: Bind peeking can occur for user binds even if “_optim_peek_user_binds” is set to FALSE.
   This can cause binds to be marked “unsafe” leading to cursors not being shared when they should be.
   This fix is notable as plan changes could occur if statements suffering this problem execute in a release with this fix as the CBO will no longer have peeked data to use when determining an execution plan.
   ii) (unpublished) Bug: 4567767 Abstract: UNEXPLAINED PLAN CHANGES CAN OCCUR WITHOUT STATS REGATHER (Fixed in 10.2.0.4 and 11.x)

   Details:
   It is possible for queries’ execution plans to change without any modification in statistics or optimizer environment. Usually it is interpreted as the plans changed “out of the blue”. The reason for the change is that density is being reevaluated as 1/ndv instead of taking the statistic stored in the data dictionary when the table is reloaded to the row cache for whatever reason, like a shared pool flush.
   It is not easy to catch in the act but can be seen on a 10053 trace file when the query is hardparsed before and after the table is reloaded to the row cache.
   Before:
   Column: ISOCODE Col#: 7 Table: PL_X_NP Alias: X
   NDV: 1344 NULLS: 0 DENS: 1.5152e-02 <------ From Dict. NO HISTOGRAM: #BKT: 1 #VAL: 2 After: Column: ISOCODE Col#: 7 Table: PL_X_NP Alias: X NDV: 1344 NULLS: 0 DENS: 7.4405e-04 <------ 1 / 1344 NO HISTOGRAM: #BKT: 1 #VAL: 2 To turn this fix off (in 11g and 10gR2): Set "_fix_control"='4567767:off' Workaround Set event 10139 :- alter session set events '10139 trace name context forever'; or event="10139 trace name context forever" This bug is described in Note:338113.1 "Plans can change despite no stats being regathered" iii) Bug: 5364143 Abstract: UNPREDICTABLE CHANGE IN QUERY OPTIMIZER PLAN (Fixed in 10.2.0.4 and 11.x) Details: It is possible for queries' execution plans to change without any modification in statistics or optimizer environment. Usually its interpreted like the plans changed "out of the blue". The reason for the change is that the cursor was taken out of the library cache for whatever reason (flush, Memory Pressure, DDLs,etc) and upon reload sometimes bind peeking is skipped for the cursor. Note: Disabling Bind Peeking DOES NOT workaround the issue. SUMMARY ======= In summary, the bind peeking feature can give the optimizer better information and allow a more appropriate execution plan if the bind values presented on hard parsing the cursor are representative. However, if there is a possibility they are NOT representative then a plan which is sub-optimal for subsequent invocations may result. Under these circumstances one of the above strategies should be considered. Ultimately, in order to make the most appropriate decision, a good knowledge of both the application and the data is required. FOOTNOTE ======== Once a good plan is in operation for a key SQL statement it always good practice to do the following :- a) for a 9.2 database capture the statistics for the objects involved using DBMS_STATS.EXPORT_TABLE_STATS. (See Metalink Note 117203.1 "How to Use DBMS_STATS to Move Statistics to a Different Database" for more information on how to do this). These statistics could then be imported in an "emergency" to restore use of a good plan while a rogue plan is investigated. (NB take a copy of the "bad" stats before importing, of course). In 10g whenever optimizer statistics are modified using the DBMS_STATS package, old versions of the statistics are saved automatically for future restoration, so the above is not necessary. See Note 281793.1 "Oracle Database 10g stats history using dbms_stats.restore_table_stats". b) capture the good execution plan so it can be used as a baseline reference in the event that an undesired change occurs. For 9.2 use Note 260942.1: "Display Execution plans from Statements in V$SQL_PLAN". In 10g the view DBA_HIST_SQL_PLAN has historical information with respect to execution plans. Use select * from table(dbms_xplan.display_awr('&sql_id')) ...as documented in Note 362887.1 "A 10g Equivalant Process To The 9i Statspack Level 6 Execution Plan Output"

   回复
3. admin says

   2010/08/03 at 16:07

   Use of bind variables in queries (Pre 9i)
   Purpose
   ~~~~~~~
   This article is intended to provide more information on the usage of bind
   variables in queries.

   Scope & Application
   ~~~~~~~~~~~~~~~~~~~
   This article is aimed at application designers/users who may not understand
   some of the implications for them and the database in the usage of bind
   variables.

   Bind variables
   ~~~~~~~~~~~~~~

   Bind variables are place holders for query input values. They are a pointer to
   a memory location where data value(s) will be placed.

   Note that the presence of bind variables has no effect on queries that are
   optimised using the RBO. They only affect CBO query optimization because the
   CBO attempts to use column value information to determine the optimal access
   path for the query.
   When no values are supplied, the CBO may make a sub-optimal plan choice.

   Advantages of bind variables:
   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   o When a bind variable as opposed to a hard coded value is placed in a query,
   the query code does not have to change each time the query is run. This means
   that the code does not need to be reparsed and can be shared between sessions
   and you do not need to maintain a copy of the statement for each value used
   in the query. The effect of this is to reduce the amount of space used in the
   shared pool to store almost identical copies of sql statements.

   NB sharing also depends on other factors e.g.
   o identical objects and object owners must be referenced
   o bind variables must have the same datatype
   o etc.

   Disadvantages of bind variables:
   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   o When a SQL statement is optimized, the optimizer is unable to use the current
   bind value. If it did then the plan chosen for that value may be excessively
   poor for other values. Also the plan chosen would depend on which value was
   supplied first. Because of this the optimizer must either choose the average
   selectivity fo that column (the density) or use defaults. This may result in
   the generation of a sub-optimal plan.

   The CBO is unable to determine accurate selectivities for range predicate
   containing bind variables. The CBO uses column value data to adjust
   selectivities. If it does not have any data values to do this with
   (such as if bind variables are used) then this is not possible and assumptions
   have to be made.
   For queries with range predicates using bind variables, we have no way of
   calculating the selectivity, so we use a hardcoded default value of 5%
   This is true irrespective of histograms as CBO does not know the value of
   the bind variable.

   Selectivity for bind variables with ‘like’ predicates defaults to 25%

   Range Predicate Example:
   ~~~~~~~~~~~~~~~~~~~~~~~~
   SELECT ename FROM emp WHERE empno > 9999;
   SELECT ename FROM emp WHERE empno > :bind1;

   Assuming the table has been analyzed, CBO knows the HIGH and LOW values for
   empno and that the values are evenly distributed between these points.
   For the first statement, CBO can determine the selectivity for the
   where clause ‘where empno >9999’ – it uses the assumption that values
   are evenly distributed to enable it to estimate the number of values between
   the supplied value and the HIGH value.

   For the second statement, it does not know what the value of :bind1 is,
   so it is unable to use the same assumption and uses the default selectivity
   of 5%.

   It is possible to test the affect of using bind variables as opposed to literals
   by setting up a variable in sqlplus. You can also assign a value to the variable.
   Setup details for a numeric bind variable called bindvar:

   variable bindvar number;
   begin
   :bindvar:=10;
   end;
   /
   SELECT * FROM emp WHERE deptno = :bindvar;

   Bind variable selectivities:
   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   See Note:68992.1

   Advice
   ~~~~~~

   o Use bind variables for queries which are executed many times
   e.g. OLTP environments
   o Use literals where queries are not repeatedly executed and where the query
   is sensitive to column sectivities.
   o If bind variables are being used then use hints to force the desired access
   path.
   o Balance the use of bind variables and shareable code with the need to
   produce accurate plans.

   回复