資料字典基表---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	   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行。

資料字典基表---COL_USAGE$

繼續閱讀

快速删除ORACLE重複記錄

oracle海量資料表删除重複記錄

[引]在Oracle中如何利用Rowid查找和删除表中的重複記錄

oralce與mysql中，如何删除重複記錄

查詢表中重複記錄

ORACLE 表壓縮

oracle中的start with connect by用法

使用JDeveloper Remote debug PLSQL程式

LoadRunner 的多種Vuser類型

性能測試（并發負載壓力）測試分析－簡要篇

賦予使用者dba權限--解釋以上：

Oracle的基本操作

為什麼要選擇UniDAC

windows不能在本地計算機上運作oracleDbConsoleorcl

SQL語言基礎：常用的資料查詢語句

Oracle 批量查詢傳入List 傳回List