古くなった統計を特定し、表ごとの統計設定(prefs)を設定

コマンド

-- 1) Flush DML counters to the dictionary and list stale objects:
EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

SELECT owner, table_name, partition_name, num_rows,
       TO_CHAR(last_analyzed,'YYYY-MM-DD HH24:MI') AS last_analyzed,
       stale_stats, stattype_locked
FROM   dba_tab_statistics
WHERE  owner = 'CUSTOMER_APP'
  AND  (stale_stats = 'YES' OR last_analyzed IS NULL)
ORDER  BY table_name, partition_name;

SELECT table_name, partition_name, inserts, updates, deletes, truncated
FROM   dba_tab_modifications
WHERE  table_owner = 'CUSTOMER_APP';

-- 2) Per-table preferences (used by the auto stats job and manual GATHER calls):
EXEC DBMS_STATS.SET_TABLE_PREFS('CUSTOMER_APP', 'SALES', 'STALE_PERCENT', '5');
EXEC DBMS_STATS.SET_TABLE_PREFS('CUSTOMER_APP', 'SALES', 'METHOD_OPT', 'FOR ALL COLUMNS SIZE AUTO FOR COLUMNS SIZE 254 CUSTOMER_ID');
EXEC DBMS_STATS.SET_TABLE_PREFS('CUSTOMER_APP', 'SALES', 'DEGREE', '4');

SELECT DBMS_STATS.GET_PREFS('STALE_PERCENT', 'CUSTOMER_APP', 'SALES') AS stale_pct FROM dual;
SELECT table_name, preference_name, preference_value
FROM   dba_tab_stat_prefs WHERE owner = 'CUSTOMER_APP';

-- 3) Gather only stale objects:
EXEC DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'CUSTOMER_APP', options => 'GATHER STALE');
-- Revert a preference: EXEC DBMS_STATS.DELETE_TABLE_PREFS('CUSTOMER_APP', 'SALES', 'STALE_PERCENT');

概要

変更率がSTALE_PERCENT(デフォルト10%)を超えると統計は古い(stale)と見なされる。設定(preferences)は表ごとのサンプリング、ヒストグラム、並列度、stale判定しきい値を永続化し、夜間の自動統計タスクが同じルールを適用するようにする。

使用する場面

大規模な表で10%のしきい値の発動が遅すぎるとき、特定の列に常にヒストグラムが必要なとき、収集戦略をスクリプトではなくディクショナリで定義するため。

注意

ライセンス不要。手動GATHER呼出しの明示的なパラメータは設定より優先される。METHOD_OPTを変更するとヒストグラムが追加/削除され、計画が変わる可能性がある。DBA_TAB_MODIFICATIONSはフラッシュ後にのみ最新になる(自動フラッシュは約15分ごと)。