数据字典基表---COL_USAGE$

浏览： ℃

字体：大中小

发布时间：2013-12-20 16:18:40

来源：

从oracle9i开始，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 ProductionPL/SQL Release 11.2.0.3.0 - ProductionCORE	11.2.0.3.0	ProductionTNS for Linux: Version 11.2.0.3.0 - ProductionNLSRTL Version 11.2.0.3.0 - ProductionSQL> 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 T1SQL> 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:51T1	   NAMESPACE		NONE			  18	       1       32048 2013-12-18 14:28:51T1	   SECONDARY		NONE			   1	       1       32049 2013-12-18 14:28:51T1	   GENERATED		NONE			   2	       1       32049 2013-12-18 14:28:51T1	   TEMPORARY		NONE			   2	       1       32049 2013-12-18 14:28:51T1	   STATUS		NONE			   1	       1       32049 2013-12-18 14:28:51T1	   TIMESTAMP		NONE			 721	       1       32048 2013-12-18 14:28:51T1	   LAST_DDL_TIME	NONE			 724	       1       32048 2013-12-18 14:28:51T1	   CREATED		NONE			 653	       1       32049 2013-12-18 14:28:51T1	   OBJECT_TYPE		NONE			  42	       1       32049 2013-12-18 14:28:51T1	   DATA_OBJECT_ID	NONE			2822	       1	2859 2013-12-18 14:28:51TABLE_NAME COLUMN_NAME		HISTOGRAM	NUM_DISTINCT NUM_BUCKETS SAMPLE_SIZE LAST_ANALYZED---------- -------------------- --------------- ------------ ----------- ----------- -------------------T1	   OBJECT_ID		NONE		       32049	       1       32049 2013-12-18 14:28:51T1	   SUBOBJECT_NAME	NONE			 280	       1	 569 2013-12-18 14:28:51T1	   OBJECT_NAME		NONE		       30536	       1       32049 2013-12-18 14:28:51已选择14行。SQL> SELECT COUNT(*) FROM SCOTT.T1 WHERE OBJECT_ID < 200;  COUNT(*)----------       199SQL> 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:45SQL> 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:51T1	   NAMESPACE		NONE			  18	       1       32048 2013-12-18 14:28:51T1	   SECONDARY		NONE			   1	       1       32049 2013-12-18 14:28:51T1	   GENERATED		NONE			   2	       1       32049 2013-12-18 14:28:51T1	   TEMPORARY		NONE			   2	       1       32049 2013-12-18 14:28:51T1	   STATUS		NONE			   1	       1       32049 2013-12-18 14:28:51T1	   TIMESTAMP		NONE			 721	       1       32048 2013-12-18 14:28:51T1	   LAST_DDL_TIME	NONE			 724	       1       32048 2013-12-18 14:28:51T1	   CREATED		NONE			 653	       1       32049 2013-12-18 14:28:51T1	   OBJECT_TYPE		NONE			  42	       1       32049 2013-12-18 14:28:51T1	   DATA_OBJECT_ID	NONE			2822	       1	2859 2013-12-18 14:28:51TABLE_NAME COLUMN_NAME		HISTOGRAM	NUM_DISTINCT NUM_BUCKETS SAMPLE_SIZE LAST_ANALYZED---------- -------------------- --------------- ------------ ----------- ----------- -------------------T1	   OBJECT_ID		NONE		       32049	       1       32049 2013-12-18 14:28:51T1	   SUBOBJECT_NAME	NONE			 280	       1	 569 2013-12-18 14:28:51T1	   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:45SQL> 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:32T1	   NAMESPACE		NONE			  18	       1       32048 2013-12-18 14:31:32T1	   SECONDARY		NONE			   1	       1       32049 2013-12-18 14:31:32T1	   GENERATED		NONE			   2	       1       32049 2013-12-18 14:31:32T1	   TEMPORARY		NONE			   2	       1       32049 2013-12-18 14:31:32T1	   STATUS		NONE			   1	       1       32049 2013-12-18 14:31:32T1	   TIMESTAMP		NONE			 721	       1       32048 2013-12-18 14:31:32T1	   LAST_DDL_TIME	NONE			 724	       1       32048 2013-12-18 14:31:32T1	   CREATED		NONE			 653	       1       32049 2013-12-18 14:31:32T1	   OBJECT_TYPE		NONE			  42	       1       32049 2013-12-18 14:31:32T1	   DATA_OBJECT_ID	NONE			2822	       1	2859 2013-12-18 14:31:32TABLE_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:32T1	   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(*)----------      3846SQL> 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:45SQL> 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:55T1	   NAMESPACE		NONE			  18	       1       32048 2013-12-18 14:32:55T1	   SECONDARY		NONE			   1	       1       32049 2013-12-18 14:32:55T1	   GENERATED		NONE			   2	       1       32049 2013-12-18 14:32:55T1	   TEMPORARY		NONE			   2	       1       32049 2013-12-18 14:32:55T1	   STATUS		NONE			   1	       1       32049 2013-12-18 14:32:55T1	   TIMESTAMP		NONE			 721	       1       32048 2013-12-18 14:32:55T1	   LAST_DDL_TIME	NONE			 724	       1       32048 2013-12-18 14:32:55T1	   CREATED		NONE			 653	       1       32049 2013-12-18 14:32:55T1	   OBJECT_TYPE		NONE			  42	       1       32049 2013-12-18 14:32:55T1	   DATA_OBJECT_ID	NONE			2822	       1	2859 2013-12-18 14:32:55TABLE_NAME COLUMN_NAME		HISTOGRAM	NUM_DISTINCT NUM_BUCKETS SAMPLE_SIZE LAST_ANALYZED---------- -------------------- --------------- ------------ ----------- ----------- -------------------T1	   OBJECT_ID		FREQUENCY		  25	      25	5423 2013-12-18 14:32:55T1	   SUBOBJECT_NAME	NONE			 280	       1	 569 2013-12-18 14:32:55T1	   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:45SQL> 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:18T1	   NAMESPACE		NONE			  18	       1       32048 2013-12-18 14:34:18T1	   SECONDARY		NONE			   1	       1       32049 2013-12-18 14:34:18T1	   GENERATED		NONE			   2	       1       32049 2013-12-18 14:34:18T1	   TEMPORARY		NONE			   2	       1       32049 2013-12-18 14:34:18T1	   STATUS		NONE			   1	       1       32049 2013-12-18 14:34:18T1	   TIMESTAMP		NONE			 721	       1       32048 2013-12-18 14:34:18T1	   LAST_DDL_TIME	NONE			 724	       1       32048 2013-12-18 14:34:18T1	   CREATED		NONE			 653	       1       32049 2013-12-18 14:34:18T1	   OBJECT_TYPE		NONE			  42	       1       32049 2013-12-18 14:34:18T1	   DATA_OBJECT_ID	NONE			2822	       1	2859 2013-12-18 14:34:18TABLE_NAME COLUMN_NAME		HISTOGRAM	NUM_DISTINCT NUM_BUCKETS SAMPLE_SIZE LAST_ANALYZED---------- -------------------- --------------- ------------ ----------- ----------- -------------------T1	   OBJECT_ID		FREQUENCY		  25	      25	5515 2013-12-18 14:34:18T1	   SUBOBJECT_NAME	NONE			 280	       1	 569 2013-12-18 14:34:18T1	   OBJECT_NAME		NONE			  25	       1       32049 2013-12-18 14:34:18  --如果在col_usgae$不存在对应记录，在size auto模式下是不会生成直方图的已选择14行。

>更多相关文章