col_usage$填充过程(避免造成锁等待和死锁)

oracle存储过程

SQL> select * from v$version;

 

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

PL/SQL Release 10.2.0.4.0 - Production

CORE    10.2.0.4.0      Production

TNS for Linux: Version 10.2.0.4.0 - Production

NLSRTL Version 10.2.0.4.0 - Production

 

SQL> select * from global_name;

 

GLOBAL_NAME

--------------------------------------------------------------------------------

 

SQL> create table maclean (t1 int);

Table created.

 

SQL> select object_id from dba_objects where object_name='MACLEAN';

 

 OBJECT_ID

----------

   1323013

 

SQL> select * from maclean where t1=1;

 

no rows selected

 

SQL> set linesize 200 pagesize 2000;

 

注意col_usage$的数据同*_tab_modifications类似，

从查询到数据刷新到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$上的数据，避免造成锁等待和死锁。