為什麼進行統計分析執行效率反而更差呢？

  有一個經典案例，在8i到10g版本中，我們執行統計分析後，感覺執行效率反而更差了，造成該原因是我們建立了一個function-based index，將會產生一個隱藏列，該隱藏列會參與到成本計算。

 具體的實驗如下：

1、建立測試表
SQL> create table t1
            as select 'a' col1, rpad('x', 200) col2 from all_objects where rownum<=100
  
SQL>insert into t1  select  'b' col1, rpad('x', 200) col2 from all_objects where  rownum<=10000;
  
SQL>create index t1_idx on t1 (upper(col1));

2、統計分析後進行檢測：

  SQL>exec dbms_stats.gather_table_stats(user, 't1', cascade=>true);
  
  SQL>explain plan for select * from t1 where upper(col1)='A';
  
  SQL>select * from table(dbms_xplan.display);

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  5050 |  1001K|    70   (2)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |  5050 |  1001K|    70   (2)| 00:00:01 |
--------------------------------------------------------------------------

10053跟蹤檔案如下：

SINGLE TABLE ACCESS PATH
Column (#3): SYS_NC00003$(CHARACTER)
    AvgLen: 2.00 NDV: 2 Nulls: 0 Density: 0.5
  Table: T1  Alias: T1
    Card: Original: 10100  Rounded: 5050  Computed: 5050.00  Non Adjusted: 5050.00
  Access Path: TableScan
    Cost:  69.77  Resp: 69.77  Degree: 0
      Cost_io: 69.00  Cost_cpu: 7351525
      Resp_io: 69.00  Resp_cpu: 7351525
  Access Path: index (AllEqRange)
    Index: T1_IDX
    resc_io: 159.00  resc_cpu: 3001659
    ix_sel: 0.5  ix_sel_with_filters: 0.5
    Cost: 159.31  Resp: 159.31  Degree: 1
  Best:: AccessPath: TableScan
         Cost: 69.77  Degree: 1  Resp: 69.77  Card: 5050.00  Bytes: 0

以上是隱藏列SYS_NC00003$被統計分析。
該隱藏列參與的CBO的成本計算，在計算CBO的時候全表掃描cost=69.77，而走索引的cost=159.31，所以選擇全表掃描

3、刪除索引和統計分析


SQL>exec dbms_stats.delete_table_stats(user, 't1');
SQL>explain plan for select * from t1 where upper(col1)='A';
SQL>select * from table(dbms_xplan.display);
  --------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |   101 | 20503 |    11   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1     |   101 | 20503 |    11   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_IDX |    40 |       |     9   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

感覺是刪除了統計統計分析造成效率更高，其實不是，以下實驗是遮蔽掉隱藏列的統計分析。

SQL>drop index t1_idx;

SQL>exec dbms_stats.gather_table_stats(user, 't1', cascade=>true);

SQL>create index t1_idx on t1 (upper(col1));

SQL>exec dbms_stats.gather_index_stats(user, 't1_idx');

SQL>explain plan for select * from t1 where upper(col1)='A';

SQL>select * from table(dbms_xplan.display);
--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |   101 | 20503 |    11   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1     |   101 | 20503 |    11   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_IDX |    40 |       |     9   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

10053 trace 檔案：
SINGLE TABLE ACCESS PATH
  Column (#3): SYS_NC00003$(CHARACTER)  NO STATISTICS (using defaults)
    AvgLen: 1.00 NDV: 316 Nulls: 0 Density: 0.0031683
  Table: T1  Alias: T1
    Card: Original: 10100  Rounded: 101  Computed: 101.00  Non Adjusted: 101.00
  Access Path: TableScan
    Cost:  69.76  Resp: 69.76  Degree: 0
      Cost_io: 69.00  Cost_cpu: 7252545
      Resp_io: 69.00  Resp_cpu: 7252545
  Access Path: index (AllEqGuess)
    Index: T1_IDX
    resc_io: 11.00  resc_cpu: 94356
    ix_sel: 0.004  ix_sel_with_filters: 0.004
    Cost: 11.01  Resp: 11.01  Degree: 1
  Best:: AccessPath: IndexRange  Index: T1_IDX
         Cost: 11.01  Degree: 1  Resp: 11.01  Card: 101.00  Bytes: 0

注意一點隱藏列沒有被統計分析，他採用預設值，Density=0.0031683，造成參與成本計算的時候，全表掃描成本是69.76，索引的成本掃描只有11.01。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/354732/viewspace-613784/，如需轉載，請註明出處，否則將追究法律責任。