首页 / ORACLE / Oracle 字段监控 ( column monitor)

Oracle 字段监控 ( column monitor)

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了Oracle 字段监控 ( column monitor)，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含12869字，纯文字阅读大概需要19分钟。

内容图文

Oracle 字段监控 ( column monitor)

Oracle 字段监控 ( column monitor)

从oracle9i开始，oracle为了监控column的使用情况，引入了col_usage$ 基表,该表会记录 数据库运行期间column作为谓词被使用的情况，这些记录信息会指导oracle如何生成column的 直方图。

这是Oracle 本身原本的意图,从另外一个角度上来讲，我们可以以此为依据,判断哪个字段上 应该建立索引。

1 开启与关闭

Oracle通过隐藏参数"_column_tracking_level"控制此功能的开关:

0
禁用column tracking
1
启用column tracking

此参数可以在session 或者system级别动态调整，不需要重新启动数据库。

2 字段说明

表字段可以通过desc col_usage$来查看。如: 

sql> desc col_usage$
Table 1: col_usage$ 字段说明
字段 含义
OBJ# DBA_OBJECTS.OBJECT_ID
INTCOL#  
EQUALITY_PREDS 等值查询
EQUIJOIN_PREDS 等值连接
NONEQUIJOIN_PREDS 不等值连接
RANGE_PREDS 范围查询
LIKE_PREDS 使用LIKE关键字查询
NULL_PREDS 空值查询
TIMESTAMP 时间戳

3 数据来源与清除

  • 插入
    • 自动 SMON里程每15分钟将SGA中的内容刷新到col_usage$表中
    • 手动 调用DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO
  • 清除 在数据库实例关闭的时候，smon会清除无效的行，比如某张表被删除，与此表相关的信息 即为无效。

4 对直方图的影响

从Oracle 10G 开始，收集表统计信息时，若采用 ”FOR ALL COLUMNS SIZE AUTO" 的方式。如果某张表的字段存在于COL_USAGE$中，数据库就会认为有必要收集直方图信息。

5 对建立索引的影响

开启此功能后，我们可以对字段被用于查询条件的情况进行区分统计。哪个字段被使用到， 被使用的频率有多高，是用于连接还是范围查询等等。这些信息，对于我们创建索引，创 建什么类型的索引，是一个重要的依据。

6 Col_Usage$

6.0.1 表结构

我们先来看看这张表的结构。 

                                    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 */timestampdate/* timestamp of last time this row was changed */
)
  storage (initial 200K next 100k maxextents unlimited pctincrease 0)
/
createunique index i_col_usage$ on col_usage$(obj#,intcol#)
  storage (maxextents unlimited)
/

6.0.2 COL_USAGE$字段说明

columen related to
obj# obj$.obj#
intcol# col$.col#
obj# 与基表obj$.obj# 相关联
intcol# 与基表col$.col# 相关联
preds 指的是predicate,也就是where条件语句中的条件。
equlity_preds 赋值条件
equijoin_preds 等值连接
nonequijoin_preds 非等值连接
range_preds 范围查询
like_preds 模糊查询
null_preds 空值匹配查询
timestamp COL_USAGE$ 表中该行数据更新时间

6.0.3 refresh COL_USAGE$(刷新基表)

  1. refresh automatically(自动刷新)

    字段访问记录被保存在SGA中，SMON 会每隔15分钟将这些信息从内存中刷新至基表COL_USAGE$中。SMON进程会对该表进行插入、更新、删除操作。当一个字段被首次访问后，SMON在刷新内存信息时，会在表中插入一行新的数据，如果字段或者表被删除，与其相关的字段信息会被SMON从COL_USAGE$中删除。

  2. refresh manually(手动刷新)

    REFRESH THE TABLE(刷新COL_USAGE$)

    上面已经提及，SMON 进程会每隔15分钟将SGA 中的字段访问数据更新到COL_USAGE$表中。那么如果我们想得到最新的字段访问数据，该怎么办呢？ Oracle 提供了一个包：DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO. 

                                                exec dbms_stats.flush_database_monitoring_info;

6.0.4 利用COL_USAGE$表

在收集表的统计信息时，依据此表决定是否对某个字段收集直方图。我们是否可以利用它呢？ 不知道你是否遇到过这种场景：

你负责对优化数据库,其中一个很重要的工作就是创建索引,但是你对业务逻辑和数据库都不 太了解，这时你会怎么做？ 如何决定哪个字段应该创建索引？

这种时候，这张表可以给我们提供相应的信息，因为它里面存储了所有字段的访问记录。 

col table_owner heading "Tab Owner" for a9
col table_name  heading "TableName" for a30
col column_name heading "Col Name" for a20
col col_acc_num for 9999999
col indexed for a8

select u.nameas table_owner,
       o.nameastable_name,
       c.nameascolumn_name,
       cu.equality_preds + cu.equijoin_preds + cu.nonequijoin_preds +
       cu.range_preds + cu.like_preds + cu.null_preds as col_acc_num,
       lpad(decode(ic.obj#, null, ‘no‘, ‘yes‘),5,‘ ‘) as indexed,
       to_char(round(ratio_to_report(cu.equality_preds + cu.equijoin_preds +
                             cu.nonequijoin_preds + cu.range_preds +
                             cu.like_preds + cu.null_preds) over() * 100,
             2),‘fm990.00‘) pct
  from sys.col_usage$ cu,
       sys.obj$ o,
       (selectdistinct obj#, intcol#, name, property from sys.col$) c,
       sys.user$ u,
       sys.icol$ ic
 where cu.obj# = o.obj#
   and cu.intcol# = c.intcol#
   and o.obj# = c.obj#
   and o.owner# = u.user#
   and c.intcol# = ic.intcol#(+)
   and c.obj# = ic.bo#(+)
   and u.namenotin(
   ‘NONYMOUS‘,‘CTXSYS‘,‘DIP‘,‘DBSNMP‘,‘DMSYS‘,‘EXFSYS‘,‘MDDATA‘,‘MDSYS‘,
   ‘MGMT_VIEW‘,‘OLAPSYS‘,‘ORDPLUGINS‘,‘ORDSYS‘,‘OUTLN‘,‘SCOTT‘,‘SI_INFORMTN‘,
   ‘_SCHEMA‘,‘SYS‘,‘SYSMAN‘,‘SYSTEM‘,‘WK_TEST‘,‘WKPROXY‘,‘WKSYS‘,‘WMSYS‘
   ,‘XDB‘,‘TSMSYS‘,‘ORACLE_OCM‘)
   and o.namenotlike‘BIN$%‘orderby 6
/

6.0.5 TEST(测试示例) 

                                    create
                                    table
                                    scott.t_halberd_colmon
                                    as
                                    select * from dba_objects;

 SELECT OBJECT_ID,OBJECT_NAME FROM DBA_OBJECTS WHERE OWNER=‘SCOTT‘and object_type=‘TABLE‘;

 EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

 select * from col_usage$ where obj#=98581;

 exec dbms_stats.gather_table_stats(ownname=>‘SCOTT‘,TABNAME=>‘T_HALBERD_COLMON‘,METHOD_OPT=>‘FOR ALL COLUMNS SIZE AUTO‘);

 SELECTtable_name, column_name,histogram,num_distinct,num_buckets,sample_size,last_analyzed FROM DBA_TAB_COL_STATISTICS WHERE OWNER=‘SCOTT‘;

 SELECTCOUNT(*) FROM SCOTT.T_HALBERD_COLMON WHERE OBJECT_ID < 200;

 EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

 select * from col_usage$ where obj#=98581;

 SELECTtable_name, column_name,histogram,num_distinct,num_buckets,sample_size,last_analyzed FROM DBA_TAB_COL_STATISTICS WHERE OWNER=‘SCOTT‘;

 UPDATE SCOTT.T_HALBERD_COLMON SET OBJECT_ID = MOD(OBJECT_ID,25);

 EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

 select * from col_usage$ where obj#=98581;

 exec dbms_stats.gather_table_stats(ownname=>‘SCOTT‘,TABNAME=>‘T_HALBERD_COLMON‘,METHOD_OPT=>‘FOR ALL COLUMNS SIZE AUTO‘);

 SELECTtable_name, column_name,histogram,num_distinct,num_buckets,sample_size,last_analyzed FROM DBA_TAB_COL_STATISTICS WHERE OWNER=‘SCOTT‘;

 update scott.t1 set object_name=object_id;

 selectcount(*) from scott.t1 where object_name like‘%4%‘;

 EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

 select * from col_usage$ where obj#=98581;

 exec dbms_stats.gather_table_stats(ownname=>‘SCOTT‘,TABNAME=>‘T_HALBERD_COLMON‘,METHOD_OPT=>‘FOR ALL COLUMNS SIZE AUTO‘);

 SELECTtable_name, column_name,histogram,num_distinct,num_buckets,sample_size,last_analyzed FROM DBA_TAB_COL_STATISTICS WHERE OWNER=‘SCOTT‘;

 exec dbms_stats.delete_table_stats(ownname=>‘SCOTT‘,TABNAME=>‘T_HALBERD_COLMON‘);

 SELECTtable_name, column_name,histogram,num_distinct,num_buckets,sample_size,last_analyzed FROM DBA_TAB_COL_STATISTICS WHERE OWNER=‘SCOTT‘;

 select * from col_usage$ where obj#=98581;

 deletefrom col_usage$ where obj#=98581 and intcol#=1;

 exec dbms_stats.gather_table_stats(ownname=>‘SCOTT‘,TABNAME=>‘T_HALBERD_COLMON‘,METHOD_OPT=>‘FOR ALL COLUMNS SIZE AUTO‘);

 SELECTtable_name, column_name,histogram,num_distinct,num_buckets,sample_size,last_analyzed FROM DBA_TAB_COL_STATISTICS WHERE OWNER=‘SCOTT‘;

–如果在col_usgae$不存在对应记录，在size auto模式下是不会生成直方图的

Author: halberd

Created: 2019-06-22 Sat 13:58

Validate

原文：https://www.cnblogs.com/halberd-lee/p/11068656.html

内容总结

以上是互联网集市为您收集整理的Oracle 字段监控 ( column monitor)全部内容，希望文章能够帮你解决Oracle 字段监控 ( column monitor)所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

扫描二维码推送至手机访问。

本文链接：https://qyyshop.com/info/1151940.html

来源：【匿名】