oracle explain plan for获取执行计划并不可靠.

要想调优sql首先需要获得一个准确的执行计划
那么当使用explain plan for 获得含有bind变量的sql语句的执行计划时，是不可靠的
原因：explain plan for不会peeking bind值

具体测试
SQL> select * from v$version where rownum<2;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod

SQL> conn xh/a831115
Connected.
SQL> select distinct sid from v$mystat;

       SID
----------
       159

SQL> create table t1  (a int,b int);

Table created.

SQL> SQL>  declare
  2      begin
  3      for i in 1..10000 loop
  4      insert into t1 values(i,i+1);
  5      end loop;
  6      commit;
  7      end;
  8      /

PL/SQL procedure successfully completed.

SQL> SQL> create index t1_ind on t1(a);

Index created.

SQL> execute dbms_stats.gather_table_stats('XH','T1');

PL/SQL procedure successfully completed.

SQL> variable a number
SQL> execute :a:=1

PL/SQL procedure successfully completed.
select * from t1 where a>:a

 

 col sql_text for a40

SQL> select b.sql_id ,b.sql_text,b.hash_value from v$session a ,v$sqlarea b where a.sid=159 and b.hash_value in (a.sql_hash_value,a.prev_hash_value);

SQL_ID        SQL_TEXT                                 HASH_VALUE
------------- ---------------------------------------- ----------
5mu9n4f3fqxtt select * from t1 where a>:a              2263578425

SQL> select * from table(dbms_xplan.display_cursor('5mu9n4f3fqxtt'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  5mu9n4f3fqxtt, child number 0
-------------------------------------
select * from t1 where a>:a

Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     6 (100)|          |
|*  1 |  TABLE ACCESS FULL| T1   |  9999 | 69993 |     6   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------

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

   1 - filter("A">:A)

18 rows selected.

可以看到这个sql真正 的plan是走fts

那么当我们调整sql时候 会用到explain获得执行计划

SQL> explain plan for select * from t1 where a>:a  ;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2059591622

--------------------------------------------------------------------------------
------

| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time
     |

--------------------------------------------------------------------------------
------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |   500 |  3500 |     3   (0)| 00:0
0:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T1     |   500 |  3500 |     3   (0)| 00:0
0:01 |

|*  2 |   INDEX RANGE SCAN          | T1_IND |    90 |       |     2   (0)| 00:0
0:01 |

--------------------------------------------------------------------------------
------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

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

   2 - access("A">TO_NUMBER(:A))

14 rows selected.

可以看到explain并没peeking，而是通过 计算fts与index访问的cost计算出了 走index的fts，那么走index访问的选择率
（表选择率，索引选择率） 因为是bind所以使用5%，最后走index cost为3 fts为6 选择了走index，但走index并没有实际peeking bind值
所以选择率是不对的，所以执行计划不正确 如果调整这样的sql那结果必定是错误的.

下面是具体走fts cost和走index cost计算（走index 选择率不准确，不能peeking用的是5%）
index
cost =
blevel +
ceiling(leaf_blocks * effective index selectivity) +
ceiling(clustering_factor * effective table selectivity)
可以写成cost=(blevel+(leaf_blocks+clustering_factor )*effective table selectivity)*(optimizer_index_cost_adj /100)

SQL> show parameter index

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_index_caching              integer     0
optimizer_index_cost_adj             integer     100

 

SQL> select num_rows from user_tables where table_name='T1';

  NUM_ROWS
----------
     10000

 

SQL>  select column_name,NUM_NULLS,NUM_DISTINCT,density from user_tab_col_statistics where table_name='T1';

COLUMN_NAME                     NUM_NULLS NUM_DISTINCT    DENSITY
------------------------------ ---------- ------------ ----------
A                                       0        10000      .0001
B                                       0        10000      .0001

SQL> select LEAF_BLOCKS,BLEVEL ,clustering_factor from user_indexes where index_name='T1_IND';

LEAF_BLOCKS     BLEVEL CLUSTERING_FACTOR
----------- ---------- -----------------
         21          1                18

io_cost =
select 1+(21+18)*0.05 from dual;

1+(21+18)*0.05
--------------
          2.95
取整不太准确，未用10053 跟踪

走fts时cost计算
SQL> select pname,pval1 from aux_stats$ where sname='SYSSTATS_MAIN';

PNAME                               PVAL1
------------------------------ ----------
CPUSPEED
CPUSPEEDNW                     484.974958  speed in millions of operations per second
IOSEEKTIM                              10  disk seek time in milliseconds
IOTFRSPEED                           4096   disk transfer time in bytes per millisecond
MAXTHR
MBRC
MREADTIM
SLAVETHR
SREADTIM

已选择9行。以上没有信息收集 ORACLE 会用DEFAULT 计算************

计算*使用 默认信息**************************
MBRC=db_file_multiblock_read_count=16
sreadtim=IOSEEKTIM+db+block_size/IOTFRSPEED=12
mreadtime=IOSEEKTIM+db_file_multiblock_read_count*db+block_size/IOTFRSPEED=42

MDRS=BLOCKS/MBRC
#SRds =0(single-block reads),因为是 FTS 都是多块读取
最早的公式变换一下(除开)
Cost = (
#SRds +
#MRds * mreadtim / sreadtim +**************io cost
#CPUCycles / (cpuspeed * sreadtim)********cpu cost

SQL> select 20/16*42/12 from dual;

20/16*42/12
-----------
      4.375

4.375=5 由于
_optimizer_ceil_cost           TRUE                      TRUE      FALSE      FALSE

CEIL cost in CBO~~~~~~~~~~取整

SQL> select ceil(20/16*42/12) from dual;

CEIL(20/16*42/12)
-----------------
                5~~~~~~~~~~~~~~~~~~~~~~~~~~~IO 部分IO COST

又由于 _table_scan_cost_plus_one           = true   加1 所以IO COST=6
true时候COST+1 表示开始需要访问表的SEGMENT HEADER BLOCK,这是优化器的内置技巧之一(避免小表中用INDEX)
********oracle cost-based oracle原文描述 _table_scan_cost_plus_one
This is the type of minor tweak that appears surprisingly frequently in Oracle, and makes
it very difficult to produce any sort of documentation about the optimizer that is both concise
and complete. In this case, the change may represent the requirement for the necessary access
to the table’s segment header block at the start of a tablescan; alternatively it may simply be
one of several tricks built into the optimizer to bias it towards using indexed access paths on
very small tables.
***********
CPU 部分 转换成IO COST 单位

#CPUCycles / (cpuspeed * sreadtim)
#CPUCycles:1642429
cpuspeed default 使用 CPUSPEEDNW
sreadtime:12(milliseconds)=12000(microseconds)
 
SQL> select 1642429/(484*12000) from dual;

1642429/(484*12000)
-------------------
         .282787362

 

那么如何才能获取准确的执行计划10g 可以从library cache中直接获取 使用dbms_xplan.display_cursor （AWR）
9i可以用sql_trace ,10046,或直接从v$sql_plan中获取准确的执行计划

 

SQL>   create table PLAN_TABLE2 (
  2          statement_id       varchar2(30),
  3          plan_id            number,
  4          timestamp          date,
  5          remarks            varchar2(4000),
  6          operation          varchar2(30),
  7          options            varchar2(255),
  8          object_node        varchar2(128),
  9          object_owner       varchar2(30),
 10          object_name        varchar2(30),
 11          object_alias       varchar2(65),
 12          object_instance    numeric,
 13          object_type        varchar2(30),
 14          optimizer          varchar2(255),
 15          search_columns     number,
 16          id                 numeric,
 17          parent_id          numeric,
 18          depth              numeric,
 19          position           numeric,
 20          cost               numeric,
 21          cardinality        numeric,
 22          bytes              numeric,
 23          other_tag          varchar2(255),
 24          partition_start    varchar2(255),
 25          partition_stop     varchar2(255),
 26          partition_id       numeric,
 27          other              long,
 28          distribution       varchar2(30),
 29          cpu_cost           numeric,
 30          io_cost            numeric,
 31          temp_space         numeric,
 32          access_predicates  varchar2(4000),
        filter_predicates  varchar2(4000),
 33   34          projection         varchar2(4000),
 35          time               numeric,
 36          qblock_name        varchar2(30),
 37          other_xml          clob
 38  );

Table created
SQL> select b.sql_id ,b.sql_text,b.hash_value,b.child_number,b.address from v$session a ,v$sql b where a.sid=159 and b.hash_value in (a.sql_hash_value,a.prev_hash_value);

SQL_ID        SQL_TEXT                                 HASH_VALUE CHILD_NUMBER
------------- ---------------------------------------- ---------- ------------
ADDRESS
--------
5mu9n4f3fqxtt select * from t1 where a>:a              2263578425            0
2FA4CF14

 

 INSERT INTO plan_table2 ( operation, options,
                         object_node, object_owner, object_name, optimizer,
                        search_columns, id, parent_id, position, cost,
                           cardinality, bytes, other_tag, partition_start,
                            partition_stop, partition_id, other, distribution,
                          cpu_cost, io_cost, temp_space, access_predicates,
                          filter_predicates)
 SELECT
         operation, options, object_node, object_owner, object_name,
         optimizer, search_columns, id, parent_id, position, cost,
        cardinality, bytes, other_tag, partition_start, partition_stop,
         partition_id, other, distribution, cpu_cost, io_cost, temp_space,
        access_predicates, filter_predicates
  FROM v$sql_plan
   WHERE address = '2FA4CF14'
  AND hash_value = 2263578425
  AND child_number = 0;

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12020513/viewspace-626478/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12020513/viewspace-626478/