表統計と列統計（ヒストグラム）の取得と削除確認

DBMS_STATS.GATHER_TABLE_STATSで表統計と列統計を取得する

手動で表統計情報の取得を行うにはDBMS_STATS.GATHER_TABLE_STATS（プロシージャ）を使います。こちら勘違いされがちなのですが列統計（ヒストグラムの収集）の収集も同プロシージャにて行います。どのカラムを対象に列統計の取得を行うのかを METHOD_OPT パラメータで制御します。（ヒストグラムだけの取得はできません。ヒストグラムの収集には表統計の取得が伴います）

通常お勧めするOracle任せでの取得方法

Oracleは、かつて実行されてきたSQLをもとに取得すべき対象カラムを選別します。以下例では「METHOD_OPT」「CASCADE」パラメータを冗長的に指定していますが、それぞれのデフォルト値がFOR ALL COLUMNS SIZE AUTO、DBMS_STATS.AUTO_CASCADEとなっているので、 「METHOD_OPT」 「CASCADE」 自体を省略しても同様の動作となります。通常はこの設定がおすすめです。これで表統計、列統計、索引統計の取得をOracle任せにします。

テーブル単位で表統計、列統計、索引統計をOracle任せで取得するSQL

begin
DBMS_STATS.GATHER_TABLE_STATS(
 OWNNAME    => 'SOONI'
,TABNAME    => 'OY_PREFECTURE'
,METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO'  -- 列統計取得Oracle任せ
,CASCADE    => DBMS_STATS.AUTO_CASCADE      -- 索引統計取得Oracle任せ
);
end;

スキーマ単位で表統計、列統計、索引統計をOracle任せで取得するSQL

スキーマ単位で取得する場合も基本的にはOracle任せにしておけばよいと思っています。上司から「統計情報は取得しているのか」と問われた場合も、『Oracleが適切なものを自分で判断して取得しています』と返答すればほぼ納得してもらえます。もし、『具体的には何をとっているのか』と用心深い上司の場合、こちらの方法で具体的な対象一覧を出してあげましょう。

begin
DBMS_STATS.GATHER_SCHEMA_STATS (
 OWNNAME     => 'SOONI'
,options     => 'GATHER AUTO'  --必要な統計情報をすべて自動的に収集
);
end;

インデックスを作成しているカラムの列統計は取得したい

Oracleがヒストグラム収集対象カラムの判断ができないと思われる場合は（重そうなSQLを初めて流すようなとき。特にバッチ処理）、とりあえず「インデックスが作成されているカラムについて取得すれば大外しはしないだろう！」という考えで以下使う事もあります。METHOD_OPTにFOR ALL INDEXEDと指定します。 私は索引統計も同時にとる事が多いので以下例ではCASCADE=TRUEを入れています。上記の通りCASCADEのデフォルトはDBMS_STATS.AUTO_CASCADEでOracle任せになっていますが、TRUEとする事で全ての索引統計を取得します。CASCADE=FALSEとする事もできます。（INDEXを作成したばかりの時は索引統計も取得されているので処理時間短縮を目的にFALSEでよいと思います。）

インデックスに使われているカラムを対象に取得するSQL

表統計と、列統計（インデックスに使われているカラムを対象）を取得します。

begin
DBMS_STATS.GATHER_TABLE_STATS(
 OWNNAME    => 'SOONI'
,TABNAME    => 'OY_PREFECTURE'
,METHOD_OPT => 'FOR ALL INDEXED' -- インデックス作成カラムを対象
,CASCADE    => TRUE              -- インデックスの統計情報も合わせて取得
);
end;

表統計、列統計の削除

以下SQLでは表統計、列統計、索引統計を一度に全て削除しますが、特定の列統計のみの削除や、特定の列に絞っての取得も可能です。

begin
DBMS_STATS.DELETE_TABLE_STATS(
 OWNNAME    => 'SOONI'
,TABNAME    => 'OY_PREFECTURE'
);
end;

表統計情報の取得状況確認

一度取得した統計情報も、対象のテーブルに対しある一定数の更新（デフォルトでは10%）が行われると失効（無効化）してしまいます。last_analyzedに日付が入っているからと言って安心してはいけません。STALE_STATS=’YES’になっている場合は失効しています。以前こちらで統計情報の失効を検証していますのでご興味のある方はどうぞ。

DBA_TAB_STATISTICS　表統計取得状況を確認するSQL

-- 表統計が取得されている事を確認する
select OWNER,TABLE_NAME,OBJECT_TYPE,NUM_ROWS
,BLOCKS,LAST_ANALYZED,STALE_STATS
from DBA_TAB_STATISTICS
where OWNER ='SOONI' and TABLE_NAME  ='OY_PREFECTURE'
;

列統計（ヒストグラム）の取得状況確認

DBA_TAB_COL_STATISTICSで取得されているカラムは確認できるのですが、取得できていないカラムも確認する場合DBA_TAB_COLSとの結合が必要です。（参考までにですがDBA_TAB_COLUMNSは非表示列が含まれていないのでお勧めできません）

DBA_TAB_COL_STATISTICS　列統計（ヒストグラム）取得状況を確認するSQL

-- ヒストグラムが取得されている事を確認する
select c.OWNER,c.TABLE_NAME,c.INTERNAL_COLUMN_ID,c.COLUMN_NAME,t.HISTOGRAM,t.LAST_ANALYZED
from DBA_TAB_COLS c left outer join 
DBA_TAB_COL_STATISTICS  t 
on c.OWNER = t.OWNER and c.TABLE_NAME = t.TABLE_NAME and c.COLUMN_NAME = t.COLUMN_NAME
where c.OWNER='SOONI' and c.TABLE_NAME ='OY_PREFECTURE'
order by c.INTERNAL_COLUMN_ID
;

SQL*Plus表示用

set lin 200
COLUMN OWNER       FORMAT A10
COLUMN TABLE_NAME  FORMAT A20
COLUMN OBJECT_TYPE FORMAT A10
COLUMN STALE_STATS FORMAT A11
COLUMN COLUMN_NAME FORMAT A20
COLUMN HISTOGRAM   FORMAT A18

以下表統計と列統計情報の取得状況確認例

SQL> select t.OWNER,t.TABLE_NAME,t.OBJECT_TYPE,t.NUM_ROWS,t.BLOCKS,t.LAST_ANALYZED,t.STALE_STATS
  2   from DBA_TAB_STATISTICS t where t.OWNER ='SOONI' and t.TABLE_NAME  ='OY_PREFECTURE'
  3  ;

OWNER      TABLE_NAME           OBJECT_TYP   NUM_ROWS     BLOCKS LAST_ANALYZED       STALE_STATS
---------- -------------------- ---------- ---------- ---------- ------------------- -----------
SOONI      OY_PREFECTURE        TABLE              47          4 2021/12/31 13:59:50 NO

SQL> -- ヒストグラムが取得されている事を確認する
SQL> select c.OWNER,c.TABLE_NAME,c.INTERNAL_COLUMN_ID,c.COLUMN_NAME,t.HISTOGRAM,t.LAST_ANALYZED
  2  from dba_tab_cols c left outer join
  3  DBA_TAB_COL_STATISTICS  t
  4  on c.OWNER = t.OWNER and c.TABLE_NAME = t.TABLE_NAME and c.COLUMN_NAME = t.COLUMN_NAME
  5  where t.OWNER='SOONI' and t.TABLE_NAME ='OY_PREFECTURE'
  6  order by c.INTERNAL_COLUMN_ID
  7  ;

OWNER      TABLE_NAME           INTERNAL_COLUMN_ID COLUMN_NAME          HISTOGRAM          LAST_ANALYZED
---------- -------------------- ------------------ -------------------- ------------------ -------------------
SOONI      OY_PREFECTURE                         1 PREFID               NONE               2021/12/31 13:59:50
SOONI      OY_PREFECTURE                         2 NAME                 NONE               2021/12/31 13:59:50
SOONI      OY_PREFECTURE                         3 NAME_R               NONE               2021/12/31 13:59:50
SOONI      OY_PREFECTURE                         4 REGIN_ID             FREQUENCY          2021/12/31 13:59:50

SQL>

各パラメータのデフォルト値を確認する方法

DBMS_STATS.GET_PREFS()ファンクションで確認できます。

各パラメータのデフォルト値を確認するSQL

select DBMS_STATS.GET_PREFS ('CASCADE') from dual
;

確認例

SQL> select DBMS_STATS.GET_PREFS ('CASCADE') from dual;

   DBMS_STATS.GET_PREFS('CASCADE')
__________________________________
DBMS_STATS.AUTO_CASCADE

SQL> select DBMS_STATS.GET_PREFS ('ESTIMATE_PERCENT') from dual;

   DBMS_STATS.GET_PREFS('ESTIMATE_PERCENT')
___________________________________________
DBMS_STATS.AUTO_SAMPLE_SIZE

SQL> select DBMS_STATS.GET_PREFS ('METHOD_OPT') from dual;

   DBMS_STATS.GET_PREFS('METHOD_OPT')
_____________________________________
FOR ALL COLUMNS SIZE AUTO

SQL>