eygle.com   eygle.com
eygle.com eygle
eygle.com  
 

« SAP系统中的Bug 5376783 dbms_space高物理读 | Blog首页 | 网站回来了 - 离开网络的日子 »

CBO中 SMON 进程与 col_usage$ 的维护

从Oracle9i开始,为了监控column的使用信息,引入了一个对象col_usage$,用于记录运行时的COLUMN使用信息。

这部分信息由SMON维护,所以当看到SMON报出相关的死锁、错误时不要惊讶,SMON做的工作是越来越杂了。

在Oracle10g中,这个表的结构如下:
create table col_usage$
(
  obj#              number,                                 /* object number */
  intcol#           number,                        /* internal column number */
  equality_preds    number,                           /* equality predicates */
  equijoin_preds    number,                           /* equijoin predicates */
  nonequijoin_preds number,                        /* nonequijoin predicates */
  range_preds       number,                              /* range predicates */
  like_preds        number,                         /* (not) like predicates */
  null_preds        number,                         /* (not) null predicates */
  timestamp         date      /* timestamp of last time this row was changed */
)
  storage (initial 200K next 100k maxextents unlimited pctincrease 0)
/
create unique index i_col_usage$ on col_usage$(obj#,intcol#)
  storage (maxextents unlimited)
/

注意,这里的每个选项都是有意义的,比如maxextents unlimited就是因为col_usage$表可能过度扩展空间设计的。

今天,在客户一个繁忙的数据库中,看到了关于这个表的操作SQL,执行次数非常频繁,以下是3个SQL:

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# = :objn AND intcol# = :coln;

INSERT INTO SYS.col_usage$
     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);
在以下1小时采样的报告中,3条SQL执行了数千次:

Parse CallsExecutions % Total Parses SQL IdSQL ModuleSQL Text
986 4,075 0.71 3c1kubcdjnppq   update sys.col_usage$ set eq...
986 69 0.71 53btfq0dt9bs9   insert into sys.col_usage$ val...
986 986 0.71 b2gnxm5z6r51n   lock table sys.col_usage$ in e...

相关的维护SQL还有:
delete from sys.col_usage$ c where not exists (select 1 from sys.obj$ o where o.obj# = c.obj# )

如果想关闭这个特性,可以通过设置_column_tracking_level = 0来实现。

以下这段SQL在进行CBO统计信息收集时,会被调用用于获取列的使用信息,以确定是否要进行基于COLUMN的柱状图信息收集等(Oracle9i版本):
SELECT /*+  RULE  */
       c.NAME col_name, c.type# col_type, c.CHARSETFORM col_csf,
       c.default$ col_def, c.null$ col_null, c.property col_prop,
       c.col# col_unum, c.intcol# col_inum, c.obj# col_obj, c.scale col_scale,
       h.bucket_cnt h_bcnt, h.distcnt h_pndv, c.LENGTH col_len,
       cu.TIMESTAMP cu_time, cu.equality_preds cu_ep,
       cu.equijoin_preds cu_ejp, cu.range_preds cu_rp, cu.like_preds cu_lp
  FROM SYS.user$ u,
       SYS.obj$ o,
       SYS.col$ c,
       SYS.col_usage$ cu,
       SYS.hist_head$ h
 WHERE u.NAME = :b1
   AND o.owner# = u.user#
   AND o.type# = 2
   AND o.NAME = :b2
   AND o.obj# = c.obj#
   AND c.obj# = cu.obj#(+)
   AND c.intcol# = cu.intcol#(+)
   AND c.obj# = h.obj#(+)
   AND c.intcol# = h.intcol#(+);



历史上的今天...
    >> 2011-12-15文章:
    >> 2005-12-15文章:

无觅

By eygle on 2009-12-15 08:08 | Comments (2) | FAQ | SQL.PLSQL | 2473 |

2 Comments

这都被抓到了?google了下,
hard parse时,传进一个:flag参数,用于标识哪种类型的列被使用,此列相应类型的谓词统计加1

不知道影响有多大呢?从上述情况看应该不是很夸张的说。


CopyRight © 2004~2020 云和恩墨,成就未来!, All rights reserved.
数据恢复·紧急救援·性能优化 云和恩墨 24x7 热线电话:400-600-8755 业务咨询:010-59007017-7040 or 7037 业务合作: marketing@enmotech.com