Oracle 字段监控 ( column monitor)
Table of Contents
从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$
字段 | 含义 |
---|---|
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模式下是不会生成直方图的
Created: 2019-06-22 Sat 13:58