查看收集统计数据后的执行计划:
SQL> set autotrace trace exp;
SQL> select * from tabtemp where object_id=3;
Execution Plan
----------------------------------------------------------
Plan hash value: 3955501171
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 72757 | 6749K| 293 (2)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| TABTEMP | 72757 | 6749K| 293 (2)| 00:00:04 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=3)
由输出可知,本次查询使用了正确的执行计划。所以,要注意在实际生产环境中对表、索引等进行及时有效的统计数据收集工作,避免因此带来性能问题。
SQL> alter session set events '10053 trace name context off';
SQL> select value from v$diag_info where;
VALUE
------------------------------------------------------------------------------
c:\app\administrator\diag\rdbms\orcl11g\orcl11g\trace\orcl11g_ora_5952_plan.trc
4、分析10053事件trace文件中CBO出错的位置
#more orcl11g_ora_5952_plan.trc
在前面模拟中有如下操作:
SQL> update tabtemp set object_id=3 where object_id!=3;
72763 rows updated.
SQL> commit;
SQL> select count(distinct object_id) from tabtemp;
COUNT(DISTINCTOBJECT_ID)
------------------------
1
SQL> select * from tabtemp where object_id=3; 此处没有重新进行统计信息收集,直接发起查询。
查看10053trace文件中相对应的内容:
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: TABTEMP Alias: TABTEMP
#Rows: 72764 #Blks: 1062 AvgRowLen: 97.00
Index Stats::
Index: IDX_TABTEMP_ID Col#: 4
LVLS: 1 #LB: 161 #DK: 72764 LB/K: 1.00 DB/K: 1.00 CLUF: 1102.00
Access path analysis for TABTEMP
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for TABTEMP[TABTEMP]
Table: TABTEMP Alias: TABTEMP
Card: Original: 72764.000000 Rounded: 1 Computed: 1.00 Non Adjusted: 1.00
Access Path: TableScan
Cost: 291.18 Resp: 291.18 Degree: 0
Cost_io: 289.00 Cost_cpu: 26481829
Resp_io: 289.00 Resp_cpu: 26481829
Access Path: index (AllEqRange)
Index: IDX_TABTEMP_ID
resc_io: 2.00 resc_cpu: 15723
ix_sel: 0.000014 ix_sel_with_filters: 0.000014
Cost: 2.00 Resp: 2.00 Degree: 1
Best:: AccessPath: IndexRange
Index: IDX_TABTEMP_ID
Cost: 2.00 Degree: 1 Resp: 2.00 Card: 1.00 Bytes: 0
***************************************
如上述输出trace文件中加粗所示:
#DK: 表示索引中不同的键值数量。此处数值72764错误,在对表进行更新后,索引中只有1个key。
LB/K:表示每个键值对应多少个leaf blocks。此处数值为1错误,应为leaf blocks即#LB的数值。
DB/K:表示每个key对应多少个数据块。此处数值为1错误,应为#Blks的数值。
Rounded:表示关联后将产生多少条数据。此处数值为1错误,应该是测试表的总行数72764。
ix_sel_with_filters 是带有过滤条件的索引选择率,即过滤因子FF,ix_sel_with_filters =1/DK ,本例中DK数值为1,所以ix_sel_with_filters数值近似为1。
Card:即Cardinality,10gr2以后cardinality用rows表示,是oracle自己估算的数值。本例中应为测试表的行数。
本例中Index range scan访问方式cost计算公式为:
cost=blevel + FF*leaf_blocks + FF*clustering_factor,由于FF(ix_sel_with_filters)数值出现的巨大差异(错误的数值为0.000014,正确数值近似等于1),导致Index range scan访问方式cost数值出现严重偏差,最终生成了错误的执行计划。