Oracle ヒストグラム
ヒストグラムはオプティマイザが使用する統計情報の一種で、列のデータの分布の情報を持ちます。
列統計と異なり、必ず全ての列で収集されるわけではなく、Oracleが必要な列を選定し情報を収集します。
ヒストグラムについての詳しい話はたくさん情報があるのでここでは割愛。例えば、以下のページが分かりやすいかと思います。
ヒストグラムが全ての列で収集されるわけではないのに、その情報が有効なケースが限られるからです。必要なケースだけで収集することで負荷が高くならないようになっています。（といっても必要なケースを判断するのは難しいので、不要なケースでも収集されることがあります）
- データにばらつきがある場合（逆に均等なら不要）
- 対象の列がwhere句で使用されない場合
- 対象の列がの値が一意で、等価述語しか使用しない場合
ヒストグラムは上述のケースで有効なのですが、必ずしもそのケースだけしか収集されないということはありません。その場合はテーブル単位でヒストグラムを無効にします。そのテーブルの中で特定の列だけはヒストグラムを収集するという設定もできます。
スキーマ単位でヒストグラムを収集しない設定もできますが、明確な設計指針がないのであればテーブル単位で設定した方が良いと思います。
なお、ヒストグラムはAdaptive cursor sharing機能でも使用されており、バインド変数を使用していて実行計画を変えたくない場合はヒストグラムを生成しないようにします。
試してみる
準備としてテスト用のテーブルを作成し、テストデータを投入します。
create table tbl31 (id number not null, created timestamp(0), val char(100));
-- TBL31表へレコード100件×31日を挿入
begin
for i in 0..30 loop
insert into tbl31 select level + i, to_date('2021-01-01', 'YYYY-MM-DD') + i, 'test' || level from DUAL connect by level <= 100;
commit;
end loop ;
end ;
/
統計を取得し、ヒストグラムの有無を確認します。
全ての列でHISTOGRAMがNONEになっており、ヒストグラムが収集されていないことが確認できます。
デフォルトでは、統計取得のオプション（METHOD_OPT）が"FOR ALL COLUMNS SIZE AUTO"となっており、ワークロード（SQL）の実績によって収集対象であるかが決定されるため、SQLが実行されていない状況では収集されません。
exec DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'HR');
column table_name format a20
column column_name format a10
column high_value format a20
column low_value format a20
set linesize 150
select table_name, column_name, num_distinct, low_value, high_value, density, last_analyzed, histogram from user_tab_col_statistics where table_name = 'TBL31';
TABLE_NAME COLUMN_NAM NUM_DISTINCT LOW_VALUE HIGH_VALUE DENSITY LAST_ANAL HISTOGRAM
-------------------- ---------- ------------ -------------------- -------------------- ---------- --------- ---------------
TBL31 ID 130 C102 C2021F .007692308 13-JAN-21 NONE
TBL31 CREATED 31 78790101010101 7879011F010101 .032258065 13-JAN-21 NONE
TBL31 VAL 100 74657374312020202020 74657374393920202020 .01 13-JAN-21 NONE
20202020202020202020 20202020202020202020
20202020202020202020 20202020202020202020
20202020202020202020 20202020202020202020
20202020202020202020 20202020202020202020
20202020202020202020 20202020202020202020
20202020 20202020
ここで、CREATED列を検索条件でSQLを実行し統計を取得すると、ヒストグラムが収集されます。
HISTOGRAMが"FREQUENCY"になっていることで確認できます。
select count(*) from tbl31 where created = to_date('2021-01-05', 'YYYY-MM-DD');
exec DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'HR');
select table_name, column_name, num_distinct, low_value, high_value, density, last_analyzed, histogram from user_tab_col_statistics where table_name = 'TBL31';
TABLE_NAME COLUMN_NAM NUM_DISTINCT LOW_VALUE HIGH_VALUE DENSITY LAST_ANAL HISTOGRAM
-------------------- ---------- ------------ -------------------- -------------------- ---------- --------- ---------------
TBL31 ID 130 C102 C2021F .007692308 13-JAN-21 NONE
TBL31 CREATED 31 78790101010101 7879011F010101 .00016129 13-JAN-21 FREQUENCY
TBL31 VAL 100 74657374312020202020 74657374393920202020 .01 13-JAN-21 NONE
20202020202020202020 20202020202020202020
20202020202020202020 20202020202020202020
20202020202020202020 20202020202020202020
20202020202020202020 20202020202020202020
20202020202020202020 20202020202020202020
20202020 20202020
Oracleはヒストグラムを取得する列と判断する材料として、ワークロード（SQL）の実績を使用します。
具体的には、sys.col_usage$となるのですが、REPORT_COL_USAGEファンクションでも確認することができます。
実際の確認例が以下になります。
set linesize 100 long 1000000 longchunksize 1000000
select dbms_stats.report_col_usage(ownname=>'HR', tabname=>'TBL31') from dual;
DBMS_STATS.REPORT_COL_USAGE(OWNNAME=>'HR',TABNAME=>'TBL31')
----------------------------------------------------------------------------------------------------
LEGEND:
.......
EQ : Used in single table EQuality predicate
RANGE : Used in single table RANGE predicate
LIKE : Used in single table LIKE predicate
NULL : Used in single table is (not) NULL predicate
EQ_JOIN : Used in EQuality JOIN predicate
NONEQ_JOIN : Used in NON EQuality JOIN predicate
FILTER : Used in single table FILTER predicate
JOIN : Used in JOIN predicate
DBMS_STATS.REPORT_COL_USAGE(OWNNAME=>'HR',TABNAME=>'TBL31')
----------------------------------------------------------------------------------------------------
GROUP_BY : Used in GROUP BY expression
...............................................................................
###############################################################################
COLUMN USAGE REPORT FOR HR.TBL31
................................
1. CREATED : EQ
###############################################################################
DBMS_STATS.REPORT_COL_USAGE(OWNNAME=>'HR',TABNAME=>'TBL31')
----------------------------------------------------------------------------------------------------
長いので関係する箇所のみ抜粋しました。
以下が、where句で使用された列の情報となり、ここでは"CREATED"列がEQ（等価述語）で検索されたことを表しています。
COLUMN USAGE REPORT FOR HR.TBL31
................................
1. CREATED : EQ
ヒストグラムを取得しないようにする
前述のようにヒストグラムは自動で収集されますが、取得したくない場合は個別に設定してあげる必要があります。
現在のヒストグラムを削除するには以下の方法があります。
-- 既に取得されているヒストグラムを削除する
exec dbms_stats.delete_column_stats(ownname=>'HR',tabname=>'TBL31',colname=>'COL1',col_stat_type=>'HISTOGRAM');
-- ヒストグラムを取得しないように統計情報を収集する
-- "SIZE 1"とするのがポイント。
exec dbms_stats.gather_table_stats(ownname=>'HR',tabname=>'TBL31',METHOD_OPT => 'FOR ALL COLUMNS SIZE 1');
これだと自動オプティマイザ統計収集により、再度ヒストグラムが収集されるかもしれません。
そのため、統計取得時にヒストグラムを取得しないように設定します。
まず、テーブルの現在の設定を確認します。
"FOR ALL COLUMNS SIZE AUTO"となっています。これがデフォルトの設定で、必要に応じてヒストグラムが収集されます。
SQL> select dbms_stats.get_prefs (ownname=>'HR',tabname=>'TBL31',pname=>'METHOD_OPT') from dual;
DBMS_STATS.GET_PREFS(OWNNAME=>'HR',TABNAME=>'TBL31',PNAME=>'METHOD_OPT')
------------------------------------------------------------------------------------------------------------------------------------------------------
FOR ALL COLUMNS SIZE AUTO
以下のように"REPEAT"か"1"を設定します。
"REPEAT"の場合は収集済みのヒストグラムのみ更新するようになります。
"1"の場合はヒストグラムを収集しないようになります。
exec dbms_stats.set_table_prefs(ownname=>'HR', tabname=>'TBL31',pname=>'METHOD_OPT',pvalue=>'FOR ALL COLUMNS SIZE REPEAT');
exec dbms_stats.set_table_prefs(ownname=>'HR', tabname=>'TBL31',pname=>'METHOD_OPT',pvalue=>'FOR ALL COLUMNS SIZE 1');
ヒストグラムは特定の列だけ収集するようにしたい場合は以下のように実行します。
"FOR ALL COLUMNS SIZE 1"で全体としてヒストグラムを収集しないようにして、"FOR COLUMNS SIZE AUTO CREATED"で"CREATED"列だけヒストグラムを収集するようにしています。
exec dbms_stats.set_table_prefs(ownname=>'HR', tabname=>'TBL31',pname=>'METHOD_OPT',pvalue=>'FOR ALL COLUMNS SIZE 1, FOR COLUMNS SIZE AUTO CREATED');
[参考] FOR ALL COLUMNS SIZE [XXXX]
- integer: ヒストグラムのバケット数。 ヒストグラムを取得したくない場合は"1"に設定する。
- REPEAT: 既にヒストグラムが収集された列に対してのみを収集対象とする。
- AUTO: 列のデータ配分とワークロードに基づいて、ヒストグラムを収集する列が判断される。
- SKEWONLY: 列のデータ配分に基づいて、ヒストグラムを収集する列が判断される。