《Pro Oracle SQL》--Chapter 6--6.1 Explain Plans--之二

Understanding How EXPLAIN PLAN can Miss the Mark
理解解释计划怎么会错误标记
    One of the most frustrating things about EXPLAIN PLAN output is that it may not always match the plan that is used when the statement is actually executed.  There are three things to keep in mind about using EXPLAIN PLAN that make it susceptible to producing plan output that won’t match the actual execution plan:   
• EXPLAIN PLAN produces plans based on the environment at the moment you use it. 
• EXPLAIN PLAN doesn’t consider the datatype of bind variables (all binds are VARCHAR2).
• EXPLAIN PLAN doesn’t “peek” at bind variable values.
     For these reasons, it is very possible that EXPLAIN PLAN will produce a plan that won’t match the
plan that is produced when the statement is actually executed.  Listing 6-4 demonstrates the second
point about bind variable datatypes.
     (解释计划)EXPLAIN PLAN输出最让人郁闷的事情就是它可能不总是能匹配(SQL)语句实际执行计划。关于使用EXPLAIN PLAN有3件事值得怀疑，可能生成的计划输出不能匹配实际执行计划：
• EXPLAIN PLAN 生成的计划是基于你当时使用的环境。
• EXPLAIN PLAN 不考虑绑定变量的数据类型(所有的绑定变量都是VARCHAR2)。
• EXPLAIN PLAN 不会“窥视”绑定变量的值。
     就以上这些原因，EXPLAIN PLAN非常有可能生成的计划不匹配SQL语句实际执行的计划。列表6-4演示了第二点，关于绑定变量类型。
Listing 6-4. EXPLAIN PLAN and Bind Variable Datatypes
SQL>-- Create a test table where primary key column
SQL>-- is string datatype
SQL>create table regions2
  2  (region_id  varchar2(10) primary key,
  3   region_name varchar2(25));
Table created.
SQL>
SQL>-- Insert rows into the test table
SQL>insert into regions2
  2  select * from regions;
4 rows created.
SQL>
SQL>-- Create a variable and set its value
SQL>variable regid number
SQL>exec :regid := 1
PL/SQL procedure successfully completed.
SQL>
SQL>-- Turn on autotrace explain plan
SQL>set autotrace traceonly explain


SQL>
SQL>-- Execute query and get explain plan
SQL>select /* DataTypeTest */ *
  2  from regions2
  3  where region_id = :regid;
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3821806520
 
--------------------------------------------------------------------------------
| Id  | Operation                                        | Name                 | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                           |     1     |    21   |     1   (0)        |
|   1 |   TABLE ACCESS BY INDEX ROWID   | REGIONS2          |     1     |    21   |     1   (0)       |
|*  2 |     INDEX UNIQUE SCAN                  | SYS_C0011282  |     1     |           |     1   (0)       |
--------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("REGION_ID"=:REGID)

SQL>
SQL>set autotrace off
SQL>
SQL>-- Execute query again
SQL>select /* DataTypeTest */ *
  2  from regions2
  3  where region_id = :regid;
 
REGION_ID  REGION_NAME
---------- -------------------------
1          Europe
 
SQL>
SQL>-- Review the actual execution plan
SQL>-- This script uses dbms_xplan.display_cursor
SQL>@pln DataTypeTest
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
SQL_ID  2va424cgs3sfb, child number 0
-------------------------------------
select /* DataTypeTest */ * from regions2 where region_id = :regid
 
Plan hash value: 670750275
 
--------------------------------------------------------------------------
| Id  | Operation                              | Name          | Starts   | E-Rows | A-Rows | Buffers |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                     |      1     |               |      1      |       8    |
|*  1 |   TABLE ACCESS FULL           | REGIONS2    |      1     |      1       |      1      |       8    |
--------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(TO_NUMBER("REGION_ID")=:REGID)
 
Note
-----
   - dynamic sampling used for this statement (level=2)

    Did you notice how the EXPLAIN PLAN output indicated that the primary key index would be used but the actual plan really used a full table scan?  The reason why is clearly shown in the Predicate
Information section.  In the explained plan output, the predicate is “REGION_ID"=:REGID, but in the actual plan, the predicate shows TO_NUMBER("REGION_ID")=:REGID.  This demonstrates how EXPLAIN PLAN doesn’t consider the datatype of a bind variable and assumes all bind variables are string types.  For the EXPLAIN PLAN, the datatypes were considered to be the same (both strings).  However, the datatypes were considered when the plan was prepared for the actual execution of the statement and Oracle implicitly converted the string datatype for the REGION_ID column to a number to match the bind variable datatype (NUMBER).  This is expected behavior in that when datatypes being compared don’t match, Oracle will always attempt to convert the string datatype to match the non-string datatype.  By doing so in this example, the TO_NUMBER function caused the use of the index to be disallowed.  This is another expected behavior to keep in mind: the predicate must match the index definition exactly or else the index will not be used.
    你注意到了么？EXPLAIN PLAN输出指出主键索引将被使用，但是实际计划用的却是全表扫描？原因在谓词信息那节中讲过。在解释计划输出中，谓词是 “REGION_ID”=:REGID,但是在实际的计划中谓词显示是TO_NUMBER("REGION_ID")=:REGID。这演示了 EXPLAIN PLAN怎么会不考虑绑定变量的数据类型且假设所有的绑定变量是字符串类型的。对于EXPLAIN PLAN而言，数据类型都被认为是一样的(都是string)。然而，为实际执行所准备的计划是要考虑数据类型的，且Oracle隐式的转换字符串类 型，REGION_ID列，成数字(NUMBER)型。当数据类型不匹配时这是期望的行为。Oracle总是企图转换字符串数据类型成非字符串数据类型。 在本例中也是这样做的，TO_NUMBER函数使得索引不允许使用。这是另外一个必须记住的期望的行为：谓词必须准确的匹配索引的定义否则索引将不能被使用。
    If you were testing this statement in your development environment and used the explain plan
output to confirm that the index was being used, you’d be wrong.  From the explain plan output, it would appear that the plan was using the index as you would expect, but when the statement was actually executed, performance would likely be unsatisfactory due to the full table scan that really would occur. Another issue with using explain plan output as your sole source for testing is that you never get a true picture of how the statement uses resources.  Estimates are just that—estimates.  To really confirm the behavior of the SQL and to make intelligent choices about whether or not the statement will provide optimal performance, you need to look at actual execution statistics.  I’ll cover the details of how to capture and interpret actual execution statistics shortly.
     如果你在开发环境测试这个语句，且用解释计划确认使用了索引，你就错了。从解释计划的输出，它显示如你所愿的用了索引，但是当语句实际执行，性能将不能满 足，由于实际发生的是全表扫描。另一个问题，因为使用解释计划输出作为你单独的测试来源，你不可能获得语句怎么使用资源的真实图画。评估只是评估。为了真 正的确认SQL的行为，做出智能的选择，是否语句提供优化的性能，你需要查看实际执行的统计。等会我将详细讨论如何捕捉和解释实际执行的统计。