从oracle9i开始引入了col_usage$表用来跟踪表列的使用情况,该功能通过隐含参数
_column_tracking_level(动态参数)控制,如
_column_tracking_level=0取消该功能,_column_tracking_level=1使该功能生效。缺省情况下,该功能是生效的,并且CBO负责将SQL语句中WHERE条件的查询谓词信息保存在该表中(when and how frequently? ),数据库在执行shutdown normal或者shutdown immediate会自动将该表中的数据清空(清空机制?)
DDL定义如下:
DBMS_METADATA.GET_DDL('TABLE','COL_USAGE$','SYS')
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
CREATE TABLE "SYS"."COL_USAGE$"
( "OBJ#" NUMBER,
"INTCOL#" NUMBER,
"EQUALITY_PREDS" NUMBER,
"EQUIJOIN_PREDS" NUMBER,
"NONEQUIJOIN_PREDS" NUMBER,
"RANGE_PREDS" NUMBER,
"LIKE_PREDS" NUMBER,
"NULL_PREDS" NUMBER,
"TIMESTAMP" DATE
( "OBJ#" NUMBER,
"INTCOL#" NUMBER,
"EQUALITY_PREDS" NUMBER,
"EQUIJOIN_PREDS" NUMBER,
"NONEQUIJOIN_PREDS" NUMBER,
"RANGE_PREDS" NUMBER,
"LIKE_PREDS" NUMBER,
"NULL_PREDS" NUMBER,
"TIMESTAMP" DATE
DBMS_METADATA.GET_DDL('TABLE','COL_USAGE$','SYS')
--------------------------------------------------------------------------------
) TABLESPACE "SYSTEM"
--------------------------------------------------------------------------------
) TABLESPACE "SYSTEM"
可以根据col_usage$知道那些索引列访问次数高,具体可以考虑创建以下试图
create view dba_column_usage
as
select oo.name owner,
o.name,
c.name column_name,
u.equality_preds,
u.equijoin_preds,
u.nonequijoin_preds,
u.range_preds,
u.like_preds,
u.null_preds,
u.timestamp
from sys.col_usage$ u,
sys.obj$ o,
sys.user$ oo,
sys.col$ c
where o.obj# = u.obj#
and oo.user# = o.owner#
and c.obj# = u.obj#
and c.col# = u.intcol#;
程序包dbms_stats在收集统计信息时,如果采用了for all indexes size auo方式,那么当col_usage$为空时或者没有对应表列相关信息,size auto是不会收集直方图的
as
select oo.name owner,
o.name,
c.name column_name,
u.equality_preds,
u.equijoin_preds,
u.nonequijoin_preds,
u.range_preds,
u.like_preds,
u.null_preds,
u.timestamp
from sys.col_usage$ u,
sys.obj$ o,
sys.user$ oo,
sys.col$ c
where o.obj# = u.obj#
and oo.user# = o.owner#
and c.obj# = u.obj#
and c.col# = u.intcol#;
程序包dbms_stats在收集统计信息时,如果采用了for all indexes size auo方式,那么当col_usage$为空时或者没有对应表列相关信息,size auto是不会收集直方图的
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28698474/viewspace-755482/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/28698474/viewspace-755482/