過去の統計情報を分析しましょう。

Oracle 10g以降からは統計情報が更新されるたびに以前の統計情報をディクショナリーに貯蔵します。統計情報更新によって性能問題が発生した時、以前の統計情報で復元するための目的です。

でも過去の統計情報を問い合わせる簡単な方法はなさそうです。たぶんディクショナリーを直接クエリするのが雄一な方法みたいです。

• SYS.WRI$_OPTSTAT_TAB_HISTORY: テーブル統計情報
  
• SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY:列統計情報
  
• SYS.WRI$_OPTSTAT_HISTGRM_HISTORY:ヒストグラム統計情報
  
• SYS.WRI$_OPTSTAT_IND_HISTORY:索引統計情報
  

このテーブルたちを問い合わせて過去の特定の時点のテーブルの統計情報を確認する簡単なスクリプトを作ってみれば次のようです。


(簡単なサンプルに過ぎませんので、運営システムで使用しようとすれば補完が必要です)

--------------------------------------------------------------------------------
-- @name: tab_stat_hist
-- @author: dion cho
-- @note: show stats gathering history 
-- @usage: @tab_stat_hist   
--          @tab_stat_hist ukja t1 "2010/12/13 13:00:00"
--------------------------------------------------------------------------------

define __OWNER = &1;
define __TABLE_NAME = &2;
define __TIME = "&3";

col obj# new_value obj_id
col savtime new_value savtime

select * from 
  (select obj#, to_char(savtime, 'yyyy/mm/dd hh24:mi:ss') as savtime
    from sys.wri$_optstat_tab_history 
    where obj# = (select object_id from all_objects 
              where owner = upper('&__OWNER') 
                    and object_name = upper('&__TABLE_NAME')
                    and subobject_name is null)
          and savtime <= to_date('&__TIME', 'yyyy/mm/dd hh24:mi:ss')
    order by savtime desc)
where rownum = 1 
;

set serveroutput on

prompt 01. table stats
exec print_table('-
    select obj#, upper(''&__TABLE_NAME'') as table_name, -
      rowcnt, blkcnt, avgrln, samplesize, analyzetime, -
      to_char(savtime, ''yyyy/mm/dd hh24:mi:ss'') as savtime -
    from sys.wri$_optstat_tab_history -
    where obj# = &obj_id -
          and to_char(savtime, ''yyyy/mm/dd hh24:mi:ss'') = ''&savtime'' -
');

prompt 02. column stats
exec print_table(' -
select c.intcol#, c.name as column_name, null_cnt, minimum, maximum, -
      distcnt, density, avgcln -
from sys.wri$_optstat_histhead_history h, sys.col$ c -
where h.obj# = c.obj# -
      and h.obj# = &obj_id -
      and h.intcol# = c.intcol# -
      and to_char(h.savtime,''yyyy/mm/dd hh24:mi:ss'') = ''&savtime'' -
order by 1 asc -
');

col col_name format a10
col epvalue format a30
prompt 03. histogram
select
  c.name as col_name,
  h.bucket,
  h.endpoint,
  h.epvalue
from 
  sys.wri$_optstat_histgrm_history h, sys.col$ c
where
  h.obj# = &obj_id
  and h.obj# = c.obj#
  and h.intcol# = c.intcol#
  and to_char(h.savtime,'yyyy/mm/dd hh24:mi:ss') = '&savtime'
order by c.intcol#, h.bucket
;

過去の索引統計情報やパーティション統計情報なども同じ方法で得られます。

この方法を利用すれば統計情報更新の後、実行計画の変わった場合、統計情報の変化を追跡できます。以前のバージョンではできなっかた重要な改善だと言えます。とくにAWRのようなデータと連動してよく使えば有効な道具になるはずです。