数据字典基表---COL_USAGE$ - 数据库编程

从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 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	   CREA

数据字典基表---COL_USAGE$(一)