分类: Oracle
2012-12-25 18:02:15
linux视频教程下载 |
从查询到数据刷新到col_usage$存在一段时间的延迟,
所以我们立即查询col_usage$将得不到任何记录,
可以手动执行DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO将缓存中的信息刷新到字典上
SQL> select * from col_usage$ where obj#=1323013;
no rows selected
SQL> oradebug setmypid;
Statement processed.
针对FLUSH_DATABASE_MONITORING_INFO填充操作做10046 level 12 trace
SQL> oradebug event 10046 trace name context forever,level 12;
Statement processed.
SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
PL/SQL procedure successfully completed.
SQL> select * from col_usage$ where obj#=1323013;
OBJ# INTCOL# EQUALITY_PREDS EQUIJOIN_PREDS NONEQUIJOIN_PREDS RANGE_PREDS LIKE_PREDS NULL_PREDS TIMESTAMP
---------- ---------- -------------- -------------- ----------------- ----------- ---------- ---------- ---------
1323013 1 1 0 0 0 0 0 19-AUG-11
=============10046 trace content====================
lock table sys.col_usage$ in exclusive mode nowait
在测试中可以发现10.2.0.4上DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO存储过程会优先使用
lock in exclusive mode nowait来锁住col_usage$基表,
如果lock失败则会反复尝试1100次,
若仍不能锁住col_usage$表则放弃更新col_usage$上的数据,避免造成锁等待和死锁。
Cksxm.c
Monitor Modification Hash Table Base
modification hash table entry
modification hash table chunk
monitoring column usage element
ksxmlock_1
lock table sys.col_usage$ in exclusive mode
lock table sys.col_usage$ in exclusive mode nowait
update sys.col_usage$
set equality_preds = equality_preds +
decode(bitand(:flag, 1), 0, 0, 1),
equijoin_preds = equijoin_preds +
decode(bitand(:flag, 2), 0, 0, 1),
nonequijoin_preds = nonequijoin_preds +
decode(bitand(:flag, 4), 0, 0, 1),
range_preds = range_preds + decode(bitand(:flag, 8), 0, 0, 1),
like_preds = like_preds + decode(bitand(:flag, 16), 0, 0, 1),
null_preds = null_preds + decode(bitand(:flag, 32), 0, 0, 1),
timestamp = :time
where obj# = :ob jn
and intcol# = :coln
insert into sys.col_usage$
(obj#,
intcol#,
equality_preds,
equijoin_preds,
nonequijoin_preds,
range_preds,
like_preds,
null_preds,
timestamp)
values
(:objn,
:coln,
decode(bitand(:flag, 1), 0, 0, 1),
decode(bitand(:flag, 2), 0, 0, 1),
decode(bitand(:flag, 4), 0, 0, 1),
decode(bitand(:flag, 8), 0, 0, 1),
decode(bitand(:flag, 16), 0, 0, 1),
decode(bitand(:flag, 32), 0, 0, 1),
:time)