数据字典基表---COL_USAGE$

最新推荐文章于 2021-04-02 19:12:21 发布

ezbit

最新推荐文章于 2021-04-02 19:12:21 发布

阅读量2.2k

点赞数 1

分类专栏： Oracle Database 文章标签： oracle histogram col_usage

本文链接：https://blog.csdn.net/yidian815/article/details/17393473

版权

Oracle Database 专栏收录该内容

50 篇文章 3 订阅

订阅专栏

从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	   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(*)
----------
       199

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	   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> 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(*)
----------
      3846

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行。

ezbit

关注

1
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
数据字典基表---COL_USAGE$

从oracle9i开始，oracle为了监控column的使用情况，引入了col_usage$基表。col_usage$会记录数据库运行期间column作为谓词被使用的情况，这些记录信息会指导oracle如何生成column的直方图。首先来看一看col_usage$的表结构：SQL> select * from v$version;BANNER--------------------
复制链接

扫一扫

专栏目录