oracle

Oracle ヒストグラムの取得状況を確認する

DBA_TAB_COL_STATISTICS

ヒストグラムの取得状況はDBA_TAB_COL_STATISTICSにて取得されているカラムのみ確認できます。取得できていないカラムも確認する場合、DBA_TAB_COLS(注1)と結合する必要があります。
(注1)DBA_TAB_COLUMNSでもよいのですが、このディクショナリビューは非表示列が含まれないのでDBA_TAB_COLSと結合しましょう。

ヒストグラム取得状況確認

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 ='TBL26'
order by c.INTERNAL_COLUMN_ID
;

表統計(DBA_TAB_STATISTICS)の確認も必要

あわせてDBA_TAB_STATISTICSSTALE_STATSカラムも確認してください。場合によっては以下のように「表統計情報が失効」しているケースがあるためです。失効している場合 STALE_STATS=YESとなります。かつてヒストグラムの取得は行えていたのですが、ある一定量の更新が発生(デフォルトは10%)したため失効しているのです。基本的には定期的に統計情報の取得が計画されているはずなので、その内に取得され直すと思いますが、必要であれば手動で取得します。

DBA_TAB_STATISTICS 表統計取得状況確認SQL
select OWNER,TABLE_NAME,OBJECT_TYPE,NUM_ROWS,BLOCKS,LAST_ANALYZED,STALE_STATS
from DBA_TAB_STATISTICS where TABLE_NAME ='TBL26'
;
表統計取得状況確認例
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 c.OWNER='SOONI' and c.TABLE_NAME ='TBL26'
  6  order by c.INTERNAL_COLUMN_ID
  7  ;

OWNER    TABLE_NAME   INTERNAL_COLUMN_ID COLUMN_NAME          HISTOGRAM  LAST_ANALYZED
-------- ------------ ------------------ -------------------- ---------- -------------------
SOONI    TBL26                         1 COL1                 HYBRID     2019/06/29 14:00:23
SOONI    TBL26                         2 COL2                 NONE       2019/06/29 14:00:23
SOONI    TBL26                         3 COL3                 NONE       2019/06/29 14:00:23
SOONI    TBL26                         4 COL4                 NONE       2019/06/29 14:00:23
SOONI    TBL26                         5 COL5                 FREQUENCY  2019/06/29 14:00:23
SOONI    TBL26                         6 AUTO_ID


6行が選択されました。

SQL> select OWNER,TABLE_NAME,OBJECT_TYPE,NUM_ROWS,BLOCKS,LAST_ANALYZED,STALE_STATS
  2  from DBA_TAB_STATISTICS where TABLE_NAME ='TBL26'
  3  ;

OWNER    TABLE_NAME   OBJECT_TYPE                NUM_ROWS     BLOCKS LAST_ANALYZED       STALE_
-------- ------------ ------------------------ ---------- ---------- ------------------- ------
SOONI    TBL26        TABLE                        1739995     41717 2019/06/29 14:00:29    YES

特定カラムに対するヒストグラムの削除と取得

特定のカラムに絞ってヒストグラムを削除する事ができます。ヒストグラムは無駄に多くのカラムに対して取得しても意味はありません。検索条件に指定されたり、結合項目となるような項目でかつ格納されているデータに偏りが見られる場合にのみ有用となります。余計な統計情報を取得してもオプティマイザにとって不要なオーバヘッドになるだけなので気を付けたいところです。

特定カラムのヒストグラム削除SQL
EXEC dbms_stats.delete_column_stats('SOONI','TBL26','COL5',col_stat_type=>'HISTOGRAM')
;
削除後の確認例

削除後の確認では COL5 がNONEとなっています。この時LAST_ANALYZEDに変化はありません。

SQL> select t.OWNER,t.TABLE_NAME,t.COLUMN_NAME,t.HISTOGRAM,t.LAST_ANALYZED
  2  from DBA_TAB_COL_STATISTICS  t where t.OWNER='SOONI' and t.TABLE_NAME ='TBL26';

   OWNER    TABLE_NAME    COLUMN_NAME    HISTOGRAM          LAST_ANALYZED
________ _____________ ______________ ____________ ______________________
SOONI    TBL26         AUTO_ID        NONE         2019/06/29 14:00:23
SOONI    TBL26         COL5           NONE         2019/06/29 14:00:23
SOONI    TBL26         COL4           NONE         2019/06/29 14:00:23
SOONI    TBL26         COL3           NONE         2019/06/29 14:00:23
SOONI    TBL26         COL2           NONE         2019/06/29 14:00:23
SOONI    TBL26         COL1           NONE         2019/06/29 14:00:23

6行が選択されました。

SQL>
特定カラムのヒストグラム取得SQL

以下例では「AUTO_ID」,「COL5」2つのカラム限定でヒストグラム取得を行います。

begin
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SOONI',
tabname => 'TBL26',
cascade => FALSE,
method_opt => 'FOR COLUMNS AUTO_ID,COL5');
end;
取得後の確認例

取得後の確認では、COL5、AUTO_IDのLAST_ANALYZEDも更新されています。

SQL> select t.OWNER,t.TABLE_NAME,t.COLUMN_NAME,t.HISTOGRAM,t.LAST_ANALYZED
  2  from DBA_TAB_COL_STATISTICS  t
  3  where t.OWNER='SOONI' and t.TABLE_NAME ='TBL26'
  4  ;

   OWNER    TABLE_NAME    COLUMN_NAME    HISTOGRAM    LAST_ANALYZED
________ _____________ ______________ ____________ ________________
SOONI    TBL26         COL1           NONE         2019/06/29 14:00:23
SOONI    TBL26         COL2           NONE         2019/06/29 14:00:23
SOONI    TBL26         COL3           NONE         2019/06/29 14:00:23
SOONI    TBL26         COL4           NONE         2019/06/29 14:00:23
SOONI    TBL26         COL5           FREQUENCY    2019/07/01 00:55:02
SOONI    TBL26         AUTO_ID        HYBRID       2019/07/01 00:55:02

6行が選択されました。

SQL>
SQL*Plus表示用
set lin 100
set pages 100
col column_id for '999'
col column_name for a20
col num_buckets for '99'
col histogram for a10

SQLのチューニングをする際たまに使うのがこちらです

スポンサーリンク
コピペで使う