Oracle 字段监控 ( column monitor)
Oracle 字段监控 ( column monitor)
*/-->
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模式下是不会生成直方图的
最新文章
- luac++
- Java经典兔子问题
- git 常用的简单命令
- 段落的展开收起(substring的应用)
- ASP.NET MVC+EF5 开发常用代码
- Goldbach&#39;s Conjecture
- c++ 字符串函数用法举例
- python开发环境安装
- NancyFx 2.0的开源框架的使用-Forms
- UITableView 的使用小点
- @Transactional 事务说明
- 将tiff文件转化为jpg文件并保存
- ibatis.net:第三天,Insert
- 重学Java
- centos6.5 命令行配置无线上网
- C#学习笔记9
- Resharper F12下载dll源码
- c语言描述的链队列的基本操作
- HDU 多校1.5
- windows下基于bat的每1分钟执行一次一个程序
热门文章
- MySQL增删改查语句
- centos 7 安装 Git-2.23.0
- 开源Android 恶意软件Radio Balouch
- Windows Server 2016分层式存储,使用PowerShell修改底层介质类型
- 论文笔记:Integrated Object Detection and Tracking with Tracklet-Conditioned Detection
- 四、指定Nginx启动用户
- Linux系统组成和获取命令帮助2
- Java语言基础(12)
- Spring——AOP
- php类知识点滴---魔术方法,系统在特定时机触发的方法