Oracle 字段监控 ( column monitor)

1. 开启与关闭
2. 字段说明
3. 数据来源与清除
4. 对直方图的影响
5. 对建立索引的影响
6. Col_Usage$

从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 */
  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)
/

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$(刷新基表)

refresh automatically(自动刷新)

字段访问记录被保存在SGA中，SMON 会每隔15分钟将这些信息从内存中刷新至基表COL_USAGE$中。SMON进程会对该表进行插入、更新、删除操作。当一个字段被首次访问后，SMON在刷新内存信息时，会在表中插入一行新的数据，如果字段或者表被删除，与其相关的字段信息会被SMON从COL_USAGE$中删除。
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 "Table Name" for a30
col column_name heading "Col Name" for a20
col col_acc_num for 9999999
col indexed for a8

select u.name as table_owner,
       o.name as table_name,
       c.name as column_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,
       (select distinct 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.name not in(
   '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.name not like 'BIN$%'
order by 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');

 SELECT table_name, column_name,histogram,num_distinct,num_buckets,sample_size,last_analyzed FROM DBA_TAB_COL_STATISTICS WHERE OWNER='SCOTT';

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

 EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

 select * from col_usage$ where obj#=98581;

 SELECT table_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');

 SELECT table_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;

 select count(*) 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');

 SELECT table_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');

 SELECT table_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;

 delete from 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');

 SELECT table_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