数据字典基表COL_USAGE$ 辅助创建索引相关应用

原创张鹏 2023-08-18

147

1719.数据字典基表COL_USAGE$ 辅助创建索引相关应用
简明：

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

查看相关表列信息统计

select r.name owner,
o.name table_name,
c.name column_name,
u.equality_preds, --等值过滤
u.equijoin_preds, --等值join
u.nonequijoin_preds, --不等join
u.range_preds, --范围过滤
u.like_preds,–过滤
u.null_preds,–null 过滤
u.timestamp --该列最后使用时间
from sys.col_usage$ u,sys.obj$ o,sys.col$ c,sys.user$ r
where o.obj#=u.obj#
and c.obj# = u.obj#
and c.col# =u.intcol#
and o.owner#=r.user#
and r.name=‘DHCC06’;

清空基表
truncate table sys.col_usage$;

刷新统计信息
exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

清空表统计信息
EXEC DBMS_STATS.PURGE_STATS( DBMS_STATS.PURGE_ALL);
EXEC DBMS_STATS.PURGE_STATS( SYSDATE-1 );

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

首先来看一看col_usage$的表结构：

SQL> select * from v$version;

BANNER

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> desc 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

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

在oracle10g后，默认使用FOR ALL COLUMNS SIZE AUTO来收集列的直方图。size auto模式下，oracle会查询col_usage基表，如果某张表的列存在于col_usage中，oracle就认为该列存在收集直方图的必要。SMON进程会每隔15分钟，将SGA中的内容刷新到COL_USAGE基表，当然我们也可以手工调用DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO来刷新col_usage的内容。在实例shutdown时，smon会清除部分无效的col_usage$内容（例如某张表已被删除），如果需要清理的内容过多，则shutdown的时间会较长。

设置隐藏参数_column_tracking_level(column usage tracking)，该参数默认为1即启用column使用情况跟踪。设置该参数为0，将禁用column tracking，该参数可以在session和system级别动态修改:
下面测试col_usage$在生成直方图方面所起的作用：

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

OBJECT_ID OBJECT_NAM

 98581 T1

SQL> EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

PL/SQL 过程已成功完成。

SQL> select * from col_usage$ where obj#=98581;

未选定行

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

PL/SQL 过程已成功完成。

SQL> SELECT table_name, column_name,histogram,num_distinct,num_buckets,sample_size,last_analyzed FROM DBA_TAB_COL_STATISTICS WHERE OWNER=‘SCOTT’;

TABLE_NAME COLUMN_NAME HISTOGRAM NUM_DISTINCT NUM_BUCKETS SAMPLE_SIZE LAST_ANALYZED

T1 EDITION_NAME NONE 0 0 2013-12-18 14:28:51
T1 NAMESPACE NONE 18 1 32048 2013-12-18 14:28:51
T1 SECONDARY NONE 1 1 32049 2013-12-18 14:28:51
T1 GENERATED NONE 2 1 32049 2013-12-18 14:28:51
T1 TEMPORARY NONE 2 1 32049 2013-12-18 14:28:51
T1 STATUS NONE 1 1 32049 2013-12-18 14:28:51
T1 TIMESTAMP NONE 721 1 32048 2013-12-18 14:28:51
T1 LAST_DDL_TIME NONE 724 1 32048 2013-12-18 14:28:51
T1 CREATED NONE 653 1 32049 2013-12-18 14:28:51
T1 OBJECT_TYPE NONE 42 1 32049 2013-12-18 14:28:51
T1 DATA_OBJECT_ID NONE 2822 1 2859 2013-12-18 14:28:51

TABLE_NAME COLUMN_NAME HISTOGRAM NUM_DISTINCT NUM_BUCKETS SAMPLE_SIZE LAST_ANALYZED

T1 OBJECT_ID NONE 32049 1 32049 2013-12-18 14:28:51
T1 SUBOBJECT_NAME NONE 280 1 569 2013-12-18 14:28:51
T1 OBJECT_NAME NONE 30536 1 32049 2013-12-18 14:28:51

已选择14行。

SQL> SELECT COUNT(*) FROM SCOTT.T1 WHERE OBJECT_ID < 200;

COUNT(*)

SQL> EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

PL/SQL 过程已成功完成。

SQL> select * from col_usage$ where obj#=98581;

  OBJ#    INTCOL# EQUALITY_PREDS EQUIJOIN_PREDS NONEQUIJOIN_PREDS RANGE_PREDS LIKE_PREDS NULL_PREDS TIMESTAMP

 98581	    3		   0		  0		    0		1	   0	      0 2013-12-18 14:29:45

SQL> SELECT table_name, column_name,histogram,num_distinct,num_buckets,sample_size,last_analyzed FROM DBA_TAB_COL_STATISTICS WHERE OWNER=‘SCOTT’;

TABLE_NAME COLUMN_NAME HISTOGRAM NUM_DISTINCT NUM_BUCKETS SAMPLE_SIZE LAST_ANALYZED

T1 OBJECT_ID NONE 32049 1 32049 2013-12-18 14:28:51
T1 SUBOBJECT_NAME NONE 280 1 569 2013-12-18 14:28:51
T1 OBJECT_NAME NONE 30536 1 32049 2013-12-18 14:28:51

已选择14行。

SQL> UPDATE SCOTT.T1 SET OBJECT_ID = MOD(OBJECT_ID,25);

已更新32049行。

SQL> EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

PL/SQL 过程已成功完成。

SQL> select * from col_usage$ where obj#=98581;

  OBJ#    INTCOL# EQUALITY_PREDS EQUIJOIN_PREDS NONEQUIJOIN_PREDS RANGE_PREDS LIKE_PREDS NULL_PREDS TIMESTAMP

 98581	    3		   0		  0		    0		1	   0	      0 2013-12-18 14:29:45

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

PL/SQL 过程已成功完成。

SQL> SELECT table_name, column_name,histogram,num_distinct,num_buckets,sample_size,last_analyzed FROM DBA_TAB_COL_STATISTICS WHERE OWNER=‘SCOTT’;

TABLE_NAME COLUMN_NAME HISTOGRAM NUM_DISTINCT NUM_BUCKETS SAMPLE_SIZE LAST_ANALYZED

T1 EDITION_NAME NONE 0 0 2013-12-18 14:31:32
T1 NAMESPACE NONE 18 1 32048 2013-12-18 14:31:32
T1 SECONDARY NONE 1 1 32049 2013-12-18 14:31:32
T1 GENERATED NONE 2 1 32049 2013-12-18 14:31:32
T1 TEMPORARY NONE 2 1 32049 2013-12-18 14:31:32
T1 STATUS NONE 1 1 32049 2013-12-18 14:31:32
T1 TIMESTAMP NONE 721 1 32048 2013-12-18 14:31:32
T1 LAST_DDL_TIME NONE 724 1 32048 2013-12-18 14:31:32
T1 CREATED NONE 653 1 32049 2013-12-18 14:31:32
T1 OBJECT_TYPE NONE 42 1 32049 2013-12-18 14:31:32
T1 DATA_OBJECT_ID NONE 2822 1 2859 2013-12-18 14:31:32

TABLE_NAME COLUMN_NAME HISTOGRAM NUM_DISTINCT NUM_BUCKETS SAMPLE_SIZE LAST_ANALYZED

T1 OBJECT_ID FREQUENCY 25 25 5482 2013-12-18 14:31:32–是否生成直方图与数据的分布特征有关
T1 SUBOBJECT_NAME NONE 280 1 569 2013-12-18 14:31:32
T1 OBJECT_NAME NONE 30536 1 32049 2013-12-18 14:31:32

已选择14行。

SQL> update scott.t1 set object_name=object_id;

已更新32049行。

SQL> select count(*) from scott.t1 where object_name like ‘%4%’;

COUNT(*)

SQL> EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

PL/SQL 过程已成功完成。

SQL> select * from col_usage$ where obj#=98581;

  OBJ#    INTCOL# EQUALITY_PREDS EQUIJOIN_PREDS NONEQUIJOIN_PREDS RANGE_PREDS LIKE_PREDS NULL_PREDS TIMESTAMP

 98581	    1		   0		  0		    0		0	   1	      1 2013-12-18 14:32:42
 98581	    3		   0		  0		    0		1	   0	      0 2013-12-18 14:29:45

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

PL/SQL 过程已成功完成。

SQL> SELECT table_name, column_name,histogram,num_distinct,num_buckets,sample_size,last_analyzed FROM DBA_TAB_COL_STATISTICS WHERE OWNER=‘SCOTT’;

TABLE_NAME COLUMN_NAME HISTOGRAM NUM_DISTINCT NUM_BUCKETS SAMPLE_SIZE LAST_ANALYZED

T1 EDITION_NAME NONE 0 0 2013-12-18 14:32:55
T1 NAMESPACE NONE 18 1 32048 2013-12-18 14:32:55
T1 SECONDARY NONE 1 1 32049 2013-12-18 14:32:55
T1 GENERATED NONE 2 1 32049 2013-12-18 14:32:55
T1 TEMPORARY NONE 2 1 32049 2013-12-18 14:32:55
T1 STATUS NONE 1 1 32049 2013-12-18 14:32:55
T1 TIMESTAMP NONE 721 1 32048 2013-12-18 14:32:55
T1 LAST_DDL_TIME NONE 724 1 32048 2013-12-18 14:32:55
T1 CREATED NONE 653 1 32049 2013-12-18 14:32:55
T1 OBJECT_TYPE NONE 42 1 32049 2013-12-18 14:32:55
T1 DATA_OBJECT_ID NONE 2822 1 2859 2013-12-18 14:32:55

TABLE_NAME COLUMN_NAME HISTOGRAM NUM_DISTINCT NUM_BUCKETS SAMPLE_SIZE LAST_ANALYZED

T1 OBJECT_ID FREQUENCY 25 25 5423 2013-12-18 14:32:55
T1 SUBOBJECT_NAME NONE 280 1 569 2013-12-18 14:32:55
T1 OBJECT_NAME FREQUENCY 25 25 5423 2013-12-18 14:32:55

已选择14行。

SQL> exec dbms_stats.delete_table_stats(ownname=>‘SCOTT’,TABNAME=>‘T1’);

PL/SQL 过程已成功完成。

SQL> SELECT table_name, column_name,histogram,num_distinct,num_buckets,sample_size,last_analyzed FROM DBA_TAB_COL_STATISTICS WHERE OWNER=‘SCOTT’;

未选定行

SQL> select * from col_usage$ where obj#=98581;

  OBJ#    INTCOL# EQUALITY_PREDS EQUIJOIN_PREDS NONEQUIJOIN_PREDS RANGE_PREDS LIKE_PREDS NULL_PREDS TIMESTAMP

 98581	    1		   0		  0		    0		0	   1	      1 2013-12-18 14:32:42
 98581	    3		   0		  0		    0		1	   0	      0 2013-12-18 14:29:45

SQL> delete from col_usage$ where obj#=98581 and intcol#=1;

已删除 1 行。

SQL> commit;

提交完成。

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

PL/SQL 过程已成功完成。

SQL> SELECT table_name, column_name,histogram,num_distinct,num_buckets,sample_size,last_analyzed FROM DBA_TAB_COL_STATISTICS WHERE OWNER=‘SCOTT’;

TABLE_NAME COLUMN_NAME HISTOGRAM NUM_DISTINCT NUM_BUCKETS SAMPLE_SIZE LAST_ANALYZED

T1 EDITION_NAME NONE 0 0 2013-12-18 14:34:18
T1 NAMESPACE NONE 18 1 32048 2013-12-18 14:34:18
T1 SECONDARY NONE 1 1 32049 2013-12-18 14:34:18
T1 GENERATED NONE 2 1 32049 2013-12-18 14:34:18
T1 TEMPORARY NONE 2 1 32049 2013-12-18 14:34:18
T1 STATUS NONE 1 1 32049 2013-12-18 14:34:18
T1 TIMESTAMP NONE 721 1 32048 2013-12-18 14:34:18
T1 LAST_DDL_TIME NONE 724 1 32048 2013-12-18 14:34:18
T1 CREATED NONE 653 1 32049 2013-12-18 14:34:18
T1 OBJECT_TYPE NONE 42 1 32049 2013-12-18 14:34:18
T1 DATA_OBJECT_ID NONE 2822 1 2859 2013-12-18 14:34:18

TABLE_NAME COLUMN_NAME HISTOGRAM NUM_DISTINCT NUM_BUCKETS SAMPLE_SIZE LAST_ANALYZED

T1 OBJECT_ID FREQUENCY 25 25 5515 2013-12-18 14:34:18
T1 SUBOBJECT_NAME NONE 280 1 569 2013-12-18 14:34:18
T1 OBJECT_NAME NONE 25 1 32049 2013-12-18 14:34:18 --如果在col_usgae$不存在对应记录，在size auto模式下是不会生成直方图的

已选择14行。
————————————————
版权声明：本文为CSDN博主「ezbit」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/yidian815/article/details/17393473

墨力计划

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

数据字典基表COL_USAGE$ 辅助创建索引相关应用

BANNER

COUNT(*)

COUNT(*)

评论