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

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

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

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/，如需轉載，請註明出處，否則將追究法律責任。

oracle explain plan for獲取執行計劃並不可靠.

相關文章