DBA手记:DBA诊断利器 - Event 10046和 10053
????? 0???INDEX UNIQUE SCAN I_USER# (object id 11)
以上执行计划中，最可疑的部分是对于OBJ$的全表扫描，这个环节的行数返回有1，935，557行，这个量级一直向上传递，所以我们首先怀疑这里的执行计划选择错误，如果选择索引，执行性能肯定会有极大的不同。
可是10046的跟踪事件显示的信息比较有限不能够准确定位错误的原因，我请朋友通过10053事件来生成一个执行计划的跟踪，10053使用极为简便，通过如下方式就可以捕获SQL的解析过程：
alter session set events '10053 trace namecontext forever,level 1';
explain plan for you_select_query;
例如：
SQL> alter session set events '10053 trace name context forever,level 1';
Session altered.
SQL> explain plan for select count(*) from obj$;
Explained.
然后在udump目录下就可以找到10053生成的跟踪文件，在该文件中，找到了查询相关表的统计信息，其中OBJ$的信息如下所示，其中CDN（CarDiNality）指表中包含的记录数量，此处显示OBJ$表中有24万左右的记录，使用了2941个数据块：
***********************
Table stats??? Table: OBJ$?? Alias: SYS_ALIAS_1
??TOTAL ::?CDN: 245313? NBLKS:? 2941?AVG_ROW_LEN:? 79
Column:????OWNER#? Col#: 3????? Table: OBJ$?? Alias: SYS_ALIAS_1
???NDV: 221?????? NULLS: 0???????? DENS: 4.5249e-03 LO:? 0? HI:259
???NO HISTOGRAM: #BKT: 1 #VAL: 2
-- Index stats
?INDEX NAME: I_OBJ1? COL#: 1
???TOTAL ::? LVLS: 1?? #LB: 632?#DK: 245313? LB/K: 1? DB/K: 1?CLUF: 4184
?INDEX NAME: I_OBJ2? COL#: 3 4 5 1213 6
???TOTAL ::? LVLS: 2?? #LB: 1904?#DK: 245313? LB/K: 1? DB/K: 1?CLUF: 180286
?INDEX NAME: I_OBJ3? COL#: 15
???TOTAL ::? LVLS: 1?? #LB: 19?#DK: 2007? LB/K: 1? DB/K: 1?CLUF: 340
_OPTIMIZER_PERCENT_PARALLEL = 0
***************************************
而在前面的10046跟踪信息中，显示OBJ$包含大约200万条记录，这是非常巨大的不同，对于USER$表，显示具有2863条记录，而统计信息中显示仅有253条记录：
***********************
Table stats??? Table: USER$?? Alias:?U
?TOTAL ::? CDN: 253? NBLKS:? 16? AVG_ROW_LEN:? 82
Column:?????USER#? Col#: 1????? Table: USER$?? Alias:?U
???NDV: 253?????? NULLS: 0???????? DENS: 3.9526e-03 LO:? 0? HI:261
???NO HISTOGRAM: #BKT: 1 #VAL: 2
-- Index stats
?INDEX NAME: I_USER#? COL#: 1
???TOTAL ::? LVLS: 0?? #LB: 1?#DK: 258? LB/K: 1? DB/K: 1?CLUF: 13
?INDEX NAME: I_USER1? COL#: 2
???TOTAL ::? LVLS: 0?? #LB: 1?#DK: 253? LB/K: 1? DB/K: 1?CLUF: 87
***********************
这说明数据字典中记录的统计信息与真实情况不符合，导致了SQL选择了错误的执行计划，在使用CBO的Oracle9i数据库中，这种情况极为普遍，通过删除表的统计信息，或者重新收集正确的统计信息，可以使SQL执行恢复到正常合理的范畴内。在Oracle10g开始的自动统计信息收集，就是为了防止出现统计信息陈旧的现象。
以下是通过dbms_stats包清除和重新收集表的统计信息的简单参考：
SQL> exec dbms_stats.delete_table_stats(user,'OBJ$');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats(user,'OBJ$');
PL/SQL procedure successfully completed.
基于这样的判断我们建议客户做出修正，最后的客户反馈结果是：按照你的建议，在更新OBJ$的统计信息后，该语句的执行时间由10分钟减少到了2分钟。接下来，按照类似的思路，又更新了USER$，CON$,CDEF$表的统计信息，这时，语句的执行时间减少到了零点几秒。至此，问题解决。
参考至：http://www.eygle.com/archives/2011/02/dba_event_10046_10053.html
如有错误，欢迎指正
邮箱:czmcj@163.com
