COLUMN MONITORING
Table of Contents
1 Column Monitoring(欄位監控)
1.1 Foreword(前言)
Column Monitoring was introduced from Oracle 9i , aiming at deciding to gather histogram information. A base table COL_USAGE$ was created to records the information about column-accessing.It is called column tracking for column-access information gathering.
從Oracle 9i 開始，引入了欄位審計，以此決定是否對欄位收集直方圖（histogram)資訊。欄位被訪問的資訊被儲存於基表: COL_USAGE$中。
1.2 Col_Usage$
We get into the table COL_USAGE$,and see the structure .
我們先來看看這張表的結構。
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) /
- the column of COL_USAGE$(COL_USAGE$欄位)
| columen | related to |
|---|---|
| obj# | obj$.obj# |
| intcol# | col$.col# |
| obj# | 與基表obj$.obj# 相關聯 |
| intcol# | 與基表col$.col# 相關聯 |
| preds | 指的是predicate,也就是where條件語句中的條件。 |
| equlity_preds | 賦值條件 |
| equijoin_preds | 等值連線 |
| nonequijoin_preds | 非等值連線 |
| range_preds | 範圍查詢 |
| like_preds | 模糊查詢 |
| null_preds | 空值匹配查詢 |
| timestamp | COL_USAGE$ 表中該行資料更新時間 |
- COL_USAGE$ and HISTOGRAM(COL_USAGE$ 與直方圖)
From 10G and later,when a table statistics information was being gathered in the way of FOR ALL COLUMNS SIZE AUTO. On this condition, if a columns of certain table has an entry in COL_USAGE$. Histogram will be centenced to be neccesary.
從Oracle 10G 開始，收集表統計資訊時，若採用 ”FOR ALL COLUMNS SIZE AUTO" 的方式。如果某張表的欄位存在於COL_USAGE$中，資料庫就會認為有必要收集直方圖資訊。
1.3 refresh COL_USAGE$(重新整理基表)
1.3.1 refresh automatically(自動重新整理)
SMON and COL_USAGE$ (SMON與COL_USAGE$)
The column-access information are stored in SGA,Smon is on duty of flush them into COL_USAGE$ every 15 minutes. SMON process will do DML operations on it ,such as insert,update,delete. When a column was accessed for the first time, a new entry will be added into COL_USAGE$,and if a column or a table was dropped, the related column-access information will be deleted from COL_USAGE$.
欄位訪問記錄被儲存在SGA中，SMON 會每隔15分鐘將這些資訊從記憶體中重新整理至基表COL_USAGE$中。SMON程式會對該表進行插入、更新、刪除操作。當一個欄位被首次訪問後，SMON在重新整理記憶體資訊時，會在表中插入一行新的資料，如果欄位或者表被刪除，與其相關的欄位資訊會被SMON從COL_USAGE$中刪除。
1.3.2 refresh manually(手動重新整理)
REFRESH THE TABLE(重新整理COL_USAGE$)
It was mentioned above,SMON will flush date into COL_USAGE$ from SGA every 15 minutes.What to do if we want to get the newest data? There is a package which will help us,DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
上面已經提及，SMON 程式會每隔15分鐘將SGA 中的欄位訪問資料更新到COL_USAGE表中。那麼如果我們想得到最新的欄位訪問資料，該怎麼辦呢？Oracle提供了一個包：DBMSSTATS.FLUSHDATABASEMONITORINGINFO.Wejustneedtocallitasbelowanddataincolusagewill be freshed.
exec dbms_stats.flush_database_monitoring_info;
1.4 OPEN AND CLOSE(開啟和關閉欄位審計)
The hiden parameter "_column_tracking_leve" control the database whether to track columns. when value is 1 , gathering will be enabled while 0 disabled.
隱含引數 "_column_tracking_leve" 用於決定是否啟用欄位訪問記錄。 當引數值為1時為開啟，為0時表示關閉。
2 Take Advantage of COL_USAGE$ (使用COL_USAGE$)
The database take advantage of COL_USAGE$ to decide whether to gather histogram for a column. What we could do with it ? Did you met the scene? You are in charge of creating indexes in database.But You do not faliliar with the database nor the business logic. What will you do ? How to decide which column to build an indexes on ? This table will help you! Because it records all the access information of a column.
在收集表的統計資訊時，依據此表決定是否對某個欄位收集直方圖。我們是否可以利用它呢？ 不知道你是否遇到過這種場景： 你負責對一個資料庫上的欄位建立索引，但是你對業務邏輯和資料庫都不太瞭解，這時你會怎麼做？ 如何決定哪個欄位應該建立索引？ 這種時候，這張表可以給我們提供相應的資訊，因為它裡面儲存了所有欄位的訪問記錄。
col table_owner heading "Tab Owner" for a9
col table_name heading "Table Name" for a30
col column_name heading "Col Name" for a20
col col_acc_num for 9999999
col indexed for a8
select u.name as table_owner,
o.name as table_name,
c.name as column_name,
cu.equality_preds + cu.equijoin_preds + cu.nonequijoin_preds +
cu.range_preds + cu.like_preds + cu.null_preds as col_acc_num,
lpad(decode(ic.obj#, null, 'no', 'yes'),5,' ') as indexed,
to_char(round(ratio_to_report(cu.equality_preds + cu.equijoin_preds +
cu.nonequijoin_preds + cu.range_preds +
cu.like_preds + cu.null_preds) over() * 100,
2),'fm990.00') pct
from sys.col_usage$ cu,
sys.obj$ o,
(select distinct obj#, intcol#, name, property from sys.col$) c,
sys.user$ u,
sys.icol$ ic
where cu.obj# = o.obj#
and cu.intcol# = c.intcol#
and o.obj# = c.obj#
and o.owner# = u.user#
and c.intcol# = ic.intcol#(+)
and c.obj# = ic.bo#(+)
and u.name not in(
'NONYMOUS','CTXSYS','DIP','DBSNMP','DMSYS','EXFSYS','MDDATA','MDSYS',
'MGMT_VIEW','OLAPSYS','ORDPLUGINS','ORDSYS','OUTLN','SCOTT','SI_INFORMTN',
'_SCHEMA','SYS','SYSMAN','SYSTEM','WK_TEST','WKPROXY','WKSYS','WMSYS'
,'XDB','TSMSYS','ORACLE_OCM')
and o.name not like 'BIN$%'
order by 6
/
we could get the columns which was highly accessed by this sql, and decide which column should be create index with.