sys.col_usage$とは
sys.col_usage$を理解するためにはまずヒストグラムを理解する必要がある。
ヒストグラムとはCBOが使用する列分布情報を保持するものであり、列データの分布が不均一な場合はヒストグラムの情報を使用してより良い実行計画を選択する。
ヒストグラムは列データの偏りが高い場合に有用なので、次のような状況では有用ではなく、つまりヒストグラムを作成する意味がない。
- WHERE句内で指定しない列
- 絞り込み条件として使用しない列にヒストグラムを作っても無駄
- 均一な分布
- データの偏りがない場合
- 一意な列を含む等価述語
OracleはDBMS_STATSパッケージよって統計情報を取得する際、ヒストグラムを取得すべき列を特定する情報を収集している。
この情報はSMONによって取得されsys.col_usage$表に保持される。
「CBOに関する統計情報は、バックグラウンドプロセスのシステムモニタ(SMON)によってテーブルに記録される。そのようなテーブルの1つ、COL_USAGE$テーブルは、SELECTクエリで使用される述語、つまり、WHERE節で使用される列、および、等号、LIKE、範囲など、述語の種類に関する情報を記録するのに使われる。10g Release 2では、SMONプロセスが20分ごとにこのテーブルを更新する。」
Oracleフォレンジック 第５部 無監査時のデータ窃盗の証拠調査 から
sys.col_usage$表のカラム
sys.col_usage$表各カラムには当該WHERE条件が実行された回数が格納される
- EQUALITY_PREDS ：等価条件
- EQUIJOIN_PREDS：等価結合条件
- NONEQUIJOIN_PREDS：不等価結合条件
- RANGE_PREDS：範囲検索条件
- LIKE_PREDS：LIKE（またはNOT LIKE）検索条件
- NULL_PREDS：NULL（またはNOT NULL）検索条件
sys.col_usage$表でWHERE句の分析をする。
sys.col_usage$表は前述のとおり、本来はOracleがヒストグラム作成のために情報を格納する内部表であるが、この表を使えば、（スキーマ）、テーブル、カラム単位でWHERE句にどのような条件が指定されているかを一覧表示させることができる。
19〜33行目で表示させたくないスキーマを指定している。
（右スクロールしてCOLUMN_NAME列よりも右側を表示させる。）
SQL> select 2 u.NAME OWNER 3 ,o.NAME TABLE_NAME 4 ,c.NAME COLUMN_NAME 5 ,us.EQUALITY_PREDS 6 ,us.EQUIJOIN_PREDS 7 ,us.NONEQUIJOIN_PREDS 8 ,us.RANGE_PREDS 9 ,us.LIKE_PREDS 10 ,us.NULL_PREDS 11 --,to_char(us.TIMESTAMP,'yyyy/mm/dd hh24:mi:ss') TIMESTAMP 12 from 13 sys.col_usage$ us 14 join sys.obj$ o on us.OBJ# = o.OBJ# 15 join sys.col$ c on us.OBJ# = c.OBJ# 16 and us.INTCOL# = c.INTCOL# 17 join sys.user$ u on o.OWNER# = u.USER# 18 where u.NAME not in ( 19 'APEX_030200' 20 ,'CTXSYS' 21 ,'DBSNMP' 22 ,'EXFSYS' 23 ,'FLOWS_FILES' 24 ,'IX' 25 ,'MDSYS' 26 ,'OLAPSYS' 27 ,'ORDDATA' 28 ,'ORDSYS' 29 ,'SYS' 30 ,'SYSMAN' 31 ,'SYSTEM' 32 ,'WMSYS' 33 ,'XDB' 34 ) 35 order by 36 u.NAME 37 ,o.NAME 38 ,c.INTCOL# 39 ; OWNER TABLE_NAME COLUMN_NAME EQUALITY_PREDS EQUIJOIN_PREDS NONEQUIJOIN_PREDS RANGE_PREDS LIKE_PREDS NULL_PREDS ------ ------------------------------ ------------------------------ -------------- -------------- ----------------- ----------- ---------- ---------- OE CATEGORIES_TAB CATEGORY_ID 1 0 0 0 0 0 OE INVENTORIES PRODUCT_ID 1 0 0 0 0 0 OE INVENTORIES WAREHOUSE_ID 0 1 0 0 0 0 OE PRODUCT_INFORMATION CATEGORY_ID 1 0 0 0 0 0 OE WAREHOUSES WAREHOUSE_ID 0 1 0 0 0 0 SCOTT EMP EMPNO 2 0 0 1 0 0 SH CHANNELS CHANNEL_ID 0 1 0 0 0 0 SH CHANNELS CHANNEL_CLASS_ID 0 1 0 0 0 0 SH CHANNELS CHANNEL_TOTAL_ID 0 1 0 0 0 0 SH COUNTRIES COUNTRY_ID 0 1 0 0 0 0 SH COUNTRIES COUNTRY_SUBREGION_ID 0 1 0 0 0 0 SH COUNTRIES COUNTRY_REGION_ID 0 1 0 0 0 0 SH COUNTRIES COUNTRY_TOTAL_ID 0 1 0 0 0 0 SH CUSTOMERS CUST_ID 0 1 0 0 0 0 SH CUSTOMERS CUST_CITY_ID 0 1 0 0 0 0 SH CUSTOMERS CUST_STATE_PROVINCE_ID 0 1 0 0 0 0 SH CUSTOMERS COUNTRY_ID 0 1 0 0 0 0 SH CUSTOMERS CUST_TOTAL_ID 0 1 0 0 0 0 SH PRODUCTS PROD_ID 0 1 0 0 0 0 SH PRODUCTS PROD_SUBCATEGORY_ID 0 1 0 0 0 0 SH PRODUCTS PROD_CATEGORY_ID 0 1 0 0 0 0 SH PRODUCTS PROD_TOTAL_ID 0 1 0 0 0 0 SH PROMOTIONS PROMO_ID 0 1 0 0 0 0 SH PROMOTIONS PROMO_SUBCATEGORY_ID 0 1 0 0 0 0 SH PROMOTIONS PROMO_CATEGORY_ID 0 1 0 0 0 0 SH PROMOTIONS PROMO_TOTAL_ID 0 1 0 0 0 0 SH SALES PROD_ID 1 1 0 0 0 0 SH SALES TIME_ID 1 1 0 0 0 0 SH TIMES TIME_ID 0 1 0 0 0 0 SH TIMES WEEK_ENDING_DAY_ID 0 1 0 0 0 0 SH TIMES CALENDAR_MONTH_ID 0 1 0 0 0 0 SH TIMES FISCAL_MONTH_ID 0 1 0 0 0 0 SH TIMES CALENDAR_QUARTER_ID 0 1 0 0 0 0 SH TIMES FISCAL_QUARTER_ID 0 1 0 0 0 0 SH TIMES CALENDAR_YEAR_ID 0 1 0 0 0 0 SH TIMES FISCAL_YEAR_ID 0 1 0 0 0 0 36 rows selected.