当统计信息不准确时，CBO可能产生错误的执行计划，并在10053 trace中找到CBO出错的位置示例

一、本文说明：
操作系统：rhel 5.4 x32
数据库：oracle 11g r2 x32
二、实验内容：
  ----创建一张jack表，并创建索引jack_ind----
  1 SQL> create table jack as select * from dba_objects;
  2 
  3 Table created.
  4 
  5 SQL> create index jack_ind on jack(object_id);
  6 
  7 Index created.
  8 
  ----先做一下统计信息，并连带索引----
  9 SQL> exec dbms_stats.gather_table_stats(user,'jack',cascade=>true);
 10 
 11 PL/SQL procedure successfully completed.
 12 
 13 SQL> set autotrace traceonly;
 14 SQL> set linesize 120;
  ----当object_id=100时的值是唯一的，所以查询会走索引----
 15 SQL> select * from jack where object_id=100;
 16 
 17 
 18 Execution Plan
 19 ----------------------------------------------------------
 20 Plan hash value: 2860868395
 21 
 22 ----------------------------------------------------------------------------------------
 23 | Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
 24 ----------------------------------------------------------------------------------------
 25 |   0 | SELECT STATEMENT        |           |     1 |    97 |     2     (0)| 00:00:01 |
 26 |   1 |  TABLE ACCESS BY INDEX ROWID| JACK     |     1 |    97 |     2     (0)| 00:00:01 |
 27 |*  2 |   INDEX RANGE SCAN        | JACK_IND |     1 |       |     1     (0)| 00:00:01 |
 28 ----------------------------------------------------------------------------------------
 29 
 30 Predicate Information (identified by operation id):
 31 ---------------------------------------------------
 32 
 33    2 - access("OBJECT_ID"=100)
 34 
 35 
 36 Statistics
 37 ----------------------------------------------------------
 38       1  recursive calls
 39       0  db block gets
 40       4  consistent gets
 41 
 42 SQL> set autotrace off;
  ----再把所有object_id的值修改成100---
 43 SQL> update jack set object_id=100;
 44 
 45 72489 rows updated.
 46 
 47 SQL> set autotrace traceonly;
 48 SQL> alter session set events '10053 trace name context forever,level 1';
 49 
 50 Session altered.
 51 
  ----因为现在object_id的值全部都是100了，所以不应该走索引而走全表----
 52 SQL> select * from jack where object_id=100;
 53 
 54 72489 rows selected.
 55 
 56 
 57 Execution Plan
 58 ----------------------------------------------------------
 59 Plan hash value: 2860868395
 60 
 61 ----------------------------------------------------------------------------------------
 62 | Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
 63 ----------------------------------------------------------------------------------------
 64 |   0 | SELECT STATEMENT        |           |     1 |    97 |     2     (0)| 00:00:01 |
 65 |   1 |  TABLE ACCESS BY INDEX ROWID| JACK     |     1 |    97 |     2     (0)| 00:00:01 |
 66 |*  2 |   INDEX RANGE SCAN        | JACK_IND |     1 |       |     1     (0)| 00:00:01 |
 67 ----------------------------------------------------------------------------------------
 68                                                               -----------在这里可以看到执行计划依然走的是索引，而Rows=1，显然执行计划是错误的。 
 69 Predicate Information (identified by operation id):
 70 ---------------------------------------------------
 71 
 72    2 - access("OBJECT_ID"=100)
 73 
 74 
 75 Statistics
 76 ----------------------------------------------------------
 77       0  recursive calls
 78       0  db block gets
 79       10860  consistent gets
 80       0  physical reads
 81 SQL> alter session set events '10053 trace name context off';
 82 
 83 Session altered.
 84 
 85 SQL> set autotrace off;
 86 SQL> @/u01/scripts/showtrace
 87 
 88 trace_file_name
 89 ------------------------------------------------------------------------------------------------------------------------
 90 /u01/app/oracle/diag/rdbms/yft/yft/trace/yft_ora_4005.trc
 91 
 92 
  ----退出再进，并对表jack进行一下统计信息----
 93 SQL> exec dbms_stats.gather_table_stats(user,'jack',cascade=>true);
 94 
 95 PL/SQL procedure successfully completed.
 96 
 97 SQL> set autotrace trace exp;
 98 SQL> alter session set events '10053 trace name context forever,level 1';
 99 
100 Session altered.
101 
 ----查询object_id=100时，执行计划走了全表，显然这次是正确的----
102 SQL> select * from jack where object_id=100;
103 
104 Execution Plan
105 ----------------------------------------------------------
106 Plan hash value: 949574992
107 
108 --------------------------------------------------------------------------
109 | Id  | Operation      | Name | Rows  | Bytes | Cost (%CPU)| Time     |
110 --------------------------------------------------------------------------
111 |   0 | SELECT STATEMENT  |     | 72482 |  6724K|   290   (1)| 00:00:04 |
112 |*  1 |  TABLE ACCESS FULL| JACK | 72482 |  6724K|   290   (1)| 00:00:04 |
113 --------------------------------------------------------------------------
114                                                         -----在这里Rows=72482已经很接近72489了 
115 Predicate Information (identified by operation id):
116 ---------------------------------------------------
117 
118    1 - filter("OBJECT_ID"=100)
119 
120 SQL> alter session set events '10053 trace name context off';
121 
122 Session altered.
123 
124 SQL> set autotrace off;
125 SQL> @/u01/scripts/showtrace
126 
127 trace_file_name
128 --------------------------------------------------------------------------------
129 /u01/app/oracle/diag/rdbms/yft/yft/trace/yft_ora_4032.trc
130 
 ----先看一下yft_ora_4005.trc中的内容，这个是错误的执行计划的内容-----
131 ***************************************
132 BASE STATISTICAL INFORMATION
133 ***********************
134 Table Stats::
135   Table: JACK  Alias: JACK
136     #Rows: 72489  #Blks:  1058  AvgRowLen:  97.00
137 Index Stats::
138   Index: JACK_IND  Col#: 4
139     LVLS: 1  #LB: 161  #DK: 72489  LB/K: 1.00  DB/K: 1.00  CLUF: 1656.00
140 Access path analysis for JACK                                              ----在10053事件trace文件中我们可以发现#DK的值依然是72489，说明不同值有72489个，才导致执行计划错误。
141 ***************************************
142 SINGLE TABLE ACCESS PATH
143   Single Table Cardinality Estimation for JACK[JACK]
144   Table: JACK  Alias: JACK
145     Card: Original: 72489.000000  Rounded: 1  Computed: 1.00  Non Adjusted: 1.00
146   Access Path: TableScan
147     Cost:  289.48  Resp: 289.48  Degree: 0
148       Cost_io: 288.00  Cost_cpu: 26381844
149       Resp_io: 288.00  Resp_cpu: 26381844
150   Access Path: index (AllEqRange)
151     Index: JACK_IND
152     resc_io: 2.00  resc_cpu: 15723
153     ix_sel: 0.000014  ix_sel_with_filters: 0.000014
154     Cost: 2.00  Resp: 2.00  Degree: 1
155   Best:: AccessPath: IndexRange
156   Index: JACK_IND
157          Cost: 2.00  Degree: 1  Resp: 2.00  Card: 1.00  Bytes: 0
158 
159 ***************************************
160 
 ----先看一下yft_ora_4032.trc中的内容，这个是错误的执行计划的内容-----
161 
162 ***************************************
163 BASE STATISTICAL INFORMATION
164 ***********************
165 Table Stats::
166   Table: JACK  Alias: JACK
167     #Rows: 72489  #Blks:  1058  AvgRowLen:  95.00
168 Index Stats::
169   Index: JACK_IND  Col#: 4
170     LVLS: 1  #LB: 250  #DK: 1  LB/K: 250.00  DB/K: 1032.00  CLUF: 1032.00
171 Access path analysis for JACK                                              ----当进行统计信息以后#DK的值已经变成1了，而表中object_id的值就只有1个了--100。所以CBO选择了全表
172 ***************************************
173 SINGLE TABLE ACCESS PATH
174   Single Table Cardinality Estimation for JACK[JACK]
175   Table: JACK  Alias: JACK
176     Card: Original: 72489.000000  Rounded: 72482  Computed: 72482.35  Non Adjusted: 72482.35
177   Access Path: TableScan
178     Cost:  290.38  Resp: 290.38  Degree: 0
179       Cost_io: 288.00  Cost_cpu: 42327664
180       Resp_io: 288.00  Resp_cpu: 42327664
181   Access Path: index (AllEqRange)
182     Index: JACK_IND
183     resc_io: 1282.00  resc_cpu: 54794826
184     ix_sel: 0.999908  ix_sel_with_filters: 0.999908
185     Cost: 1285.08  Resp: 1285.08  Degree: 1
186   Best:: AccessPath: TableScan
187          Cost: 290.38  Degree: 1  Resp: 290.38  Card: 72482.35  Bytes: 0
188 
189 ***************************************
相关阅读:
WPF中图形表示语法详解（Path之Data属性语法）ZZ
C#/.NET Little Wonders: Use Cast() and OfType() to Change Sequence Type(zz)
WPF在DLL中读取Resource的方法
 CENTOS6.5安装CDH5.12.1(一) https://mp.weixin.qq.com/s/AP_m0QqKgzEUfjf0PQCX-w
Buildroot stress-ng Linux系统压力测试
 Jenkins+git
MySql5.7配置文件my.cnf设置
 mongoDB 删除集合后，空间不释放
 有赞MySQL自动化运维之路—ZanDB
TIDB介绍新数据库趋势
原文地址：https://www.cnblogs.com/Richardzhu/p/2916252.html