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

通常我们在检验SQL执行计划时采用autotrace的方法,但autotrace本身存在许多不准确的情况。
以下为一个例子:
SQL> create table test(t1 int, t2 char(200));

表已创建。

SQL> create index ind_t2 on test(t2);

索引已创建。

SQL> insert into test values (0,’A’);

已创建 1 行。

SQL> commit;

提交完成。

SQL> begin
2  for i in 1..100000 loop
3  insert into test values(i,’ZZZZ’);
4  end loop;
5  commit;
6  end;
7  /
SQL> analyze table test compute statistics ;

表已分析。

SQL> analyze index ind_t2 compute statistics;

索引已分析

SQL> analyze table test compute statistics for all indexed columns;

表已分析。

以上代码 在test表中 产生一条t2为A的记录以及10万条t2为ZZZZ的语句,即列上值出现严重的倾斜。
SQL> set autotrace on;
SQL> variable a char;
SQL> exec :a:=’A’;
SQL> alter system flush shared_pool;

系统已更改。
PL/SQL 过程已成功完成。

SQL> oradebug setmypid;
已处理的语句
SQL> oradebug event 10046 trace name context forever,level 10;
已处理的语句

SQL> select * from test where t2=:a;

T1
———-
T2
————————————————————————–
0
A

执行计划
———————————————————-
Plan hash value: 1357081020

————————————————————————–
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
————————————————————————–
|   0 | SELECT STATEMENT  |      | 50001 |  9961K|   652   (2)| 00:00:08 |
|*  1 |  TABLE ACCESS FULL| TEST | 50001 |  9961K|   652   (2)| 00:00:08 |
————————————————————————–

Predicate Information (identified by operation id):
—————————————————

1 – filter(“T2″=:A)

统计信息
———————————————————-
231  recursive calls
0  db block gets
38  consistent gets
0  physical reads
0  redo size
654  bytes sent via SQL*Net to client
385  bytes received via SQL*Net from client
2  SQL*Net roundtrips to/from client
4  sorts (memory)
0  sorts (disk)
1  rows processed

SQL> oradebug tracefile_name;
e:\oracle\product\10.2.0\admin\orclv\udump\orclv_ora_4956.trc

使用tkprof 工具对 trace文件整理
tkprof  e:\oracle\product\10.2.0\admin\orclv\udump\orclv_ora_4956.trc C:\ora_4956.trc

可以找到以上查询的实际执行计划。
select *
from
test where t2=:a

call     count       cpu    elapsed       disk      query    current        rows
——- ——  ——– ———- ———- ———- ———-  ———-
Parse        1      0.01       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          6          0           1
——- ——  ——– ———- ———- ———- ———-  ———-
total        4      0.01       0.01          0          6          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS

Rows     Row Source Operation
——-  —————————————————
1  TABLE ACCESS BY INDEX ROWID TEST (cr=6 pr=0 pw=0 time=43 us)
1   INDEX RANGE SCAN IND_T2 (cr=5 pr=0 pw=0 time=32 us)(object id 51539)

可以看到这里实际的执行计划时 INDEX RAGNE SCAN 而非TABLE ACCESS FULL,这是由于优化器(optimizer)实际使用了绑定变量窥视的手段,而autotrace工具似乎不具备这种特性,故其展现的执行计划出现严重偏差。

一般情况下autotrace的结果仍是准确的,但也仅是一般情况,这需要我们凭借直觉去分辨。

Comments

  1. admin says

    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

    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

    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.

Comment

*

沪ICP备14014813号-2

沪公网安备 31010802001379号