前回のおさらい
前回は「オプティマイザ統計の保留」をシナリオに沿って動作を検証しました。
テーブルの統計情報公開属性を「FALSE」（=保留）に設定すれば、統計情報を取得したとしても現在の実行計画に影響を与えず、保留状態に置かれます。
そして、保留状態の統計情報を手動で公開すれば、次回実行計画を算出されるタイミングでその公開された統計情報が使用されて新しい実行計画が作成されることを確認しました。
この機能の概要は前々回の記事「オプティマイザ統計の保留」の検証（その１）で紹介しましたが、別のマニュアルにある記事も紹介しておきます。
Oracle® Database PL/SQLパッケージおよびタイプ・リファレンス
11g リリース2(11.2)
B56262-06
保留中の統計情報
「保留中の統計情報」のポイント
- デフォルトでは収集された統計情報はディクショナリに格納される。
- テーブルのPUBLISHオプション（統計情報公開属性）を「FALSE」に設定すると、統計情報はディクショナリではなくシステムのプライベート・エリアに格納される。
- プライベート・エリアに格納された統計情報は原則的にはコストベース・オプティマイザ（CBO）で使用されない。
- パラメータOPTIMIZER_USE_PENDING_STATISTICSをセッション（またはシステム）レベルで「TRUE」に設定変更すると、保留中の統計情報が新しい実行計画に影響を与えるか否かの確認を行うことができる。
プライベート・エリアはどこにあるのか？
上のポイントに出てきた保留中の統計情報が格納されるプライベート・エリアとは一体どこなのでしょうか？
前回の検証で、保留中の統計情報はデータ・ディクショナリ・ビュー「XXX_TAB_PENDING_STATS」で参照することを紹介しました。
まずは、このビュー定義からどのような実表に情報が格納されるのかを見ていきましょう。
ビュー定義を確認する
ビュー定義はDBA_VIEWSのTEXT列を確認することで得られます。以下はDBA_TAB_PENDING_STATSのビュー定義です。
SQL> set long 2000000000
SQL> set pages 200
SQL> select TEXT from dba_views where VIEW_NAME = 'DBA_TAB_PENDING_STATS';
TEXT
--------------------------------------------------------------------------------
select u.name, o.name, null, null, h.rowcnt, h.blkcnt, h.avgrln,
h.samplesize, h.analyzetime
from sys.user$ u, sys.obj$ o, sys.wri$_optstat_tab_history h
where h.obj# = o.obj# and o.type# = 2 and o.owner# = u.user#
and h.savtime > systimestamp
union all
-- partitions
select u.name, o.name, o.subname, null, h.rowcnt, h.blkcnt,
h.avgrln, h.samplesize, h.analyzetime
from sys.user$ u, sys.obj$ o, sys.wri$_optstat_tab_history h
where h.obj# = o.obj# and o.type# = 19 and o.owner# = u.user#
and h.savtime > systimestamp
union all
-- sub partitions
select u.name, osp.name, ocp.subname, osp.subname, h.rowcnt,
h.blkcnt, h.avgrln, h.samplesize, h.analyzetime
from sys.user$ u, sys.obj$ osp, obj$ ocp, sys.tabsubpart$ tsp,
sys.wri$_optstat_tab_history h
where h.obj# = osp.obj# and osp.type# = 34 and osp.obj# = tsp.obj# and
tsp.pobj# = ocp.obj# and osp.owner# = u.user#
and h.savtime > systimestamp
このビューは３つの部分（テーブル、パーティション、サブパーティション）の問い合わせから成っていることがわかります。
今回の検証ではテーブルのみを対象とするので、１番最初のパートに着目します。
それから、ハイライト表示した11行目の「h.savtime > systimestamp」というWHERE条件はこの先重要になりますので覚えておいてください。
さて、保留中の統計情報はWRI$_OPTSTAT_TAB_HISTORY（所有者はSYSユーザ）という実表に格納されるようです。
このテーブルの構成を確認しておきましょう。
SQL> desc sys.wri$_optstat_tab_history 名前 NULL? 型 ----------------- -------- ---------------------------- OBJ# NOT NULL NUMBER SAVTIME TIMESTAMP(6) WITH TIME ZONE FLAGS NUMBER ROWCNT NUMBER BLKCNT NUMBER AVGRLN NUMBER SAMPLESIZE NUMBER ANALYZETIME DATE CACHEDBLK NUMBER CACHEHIT NUMBER LOGICALREAD NUMBER SPARE1 NUMBER SPARE2 NUMBER SPARE3 NUMBER SPARE4 VARCHAR2(1000) SPARE5 VARCHAR2(1000) SPARE6 TIMESTAMP(6) WITH TIME ZONE
またこのテーブルが格納されている表領域を確認しておきます。
SQL> select OWNER,TABLE_NAME,TABLESPACE_NAME 2 from dba_tables 3 where OWNER = 'SYS' and TABLE_NAME = 'WRI$_OPTSTAT_TAB_HISTORY'; OWNER TABLE_NAME TABLESPACE_NAME ------ ------------------------------ ---------------- SYS WRI$_OPTSTAT_TAB_HISTORY SYSAUX
SYSAUX表領域であることがわかります。つまり「プライベート・エリア」というのはSYSAUX表領域にあるSYS.WRI$_OPTSTAT_TAB_HISTORY表のようです。
実は、このSYS.WRI$_OPTSTAT_TAB_HISTORYを元表とするビューは他にも存在します。
以下は、DBA_TAB_STATS_HISTORYビューの定義を確認したところです。
SQL> select TEXT from dba_views where VIEW_NAME = 'DBA_TAB_STATS_HISTORY';
TEXT
--------------------------------------------------------------------------------
select u.name, o.name, null, null, h.savtime
from sys.user$ u, sys.obj$ o, sys.wri$_optstat_tab_history h
where h.obj# = o.obj# and o.type# = 2 and o.owner# = u.user#
and h.savtime <= systimestamp -- exclude pending statistics
union all
-- partitions
select u.name, o.name, o.subname, null, h.savtime
from sys.user$ u, sys.obj$ o, sys.wri$_optstat_tab_history h
where h.obj# = o.obj# and o.type# = 19 and o.owner# = u.user#
and h.savtime <= systimestamp -- exclude pending statistics
union all
-- sub partitions
select u.name, osp.name, ocp.subname, osp.subname, h.savtime
from sys.user$ u, sys.obj$ osp, obj$ ocp, sys.tabsubpart$ tsp,
sys.wri$_optstat_tab_history h
where h.obj# = osp.obj# and osp.type# = 34 and osp.obj# = tsp.obj#
and tsp.pobj# = ocp.obj# and osp.owner# = u.user#
and h.savtime <= systimestamp -- exclude pending statistics
union all
-- fixed tables
select 'SYS', t.kqftanam, null, null, h.savtime
from sys.x$kqfta t, sys.wri$_optstat_tab_history h
where t.kqftaobj = h.obj#
and h.savtime <= systimestamp -- exclude pending statistics
驚いたことに、DBA_TAB_PENDING_STATSビューと定義がほぼ同じで、8行目のWHERE条件が正反対になっています。
実は
13.5.3 前のバージョンの統計のリストア
Oracle® Databaseパフォーマンス・チューニング・ガイド
11gリリース2 (11.2)
B56312-06
に出てくる統計情報の履歴管理と、統計情報の保留はほぼ同じ仕組みで実装されているようです。
そこで、以下の「保留中の統計情報」と「統計情報の履歴」を同時に確認できるSQL文を使って、統計情報取得に伴う情報の管理のされ方を追いかけて行きたいと思います。
showstat.sql
COLUMN TABLE_NAME FORMAT a11
COLUMN OWNER FORMAT a6
select
case when h.savtime > systimestamp
then 'PENDING'
when h.savtime <= systimestamp
then 'PUBLISH'
end as STAT
, u.name OWNER
, o.name TABLE_NAME
, h.rowcnt NUM_ROWS
, h.blkcnt BLOCKS
, h.avgrln AVG_ROW_LEN
, h.samplesize SAMPLE_SIZE
, to_char(h.analyzetime,'yyyy-mm-dd hh24:mi:ss') LAST_ANALYZED
, to_char(h.savtime,'yyyy-mm-dd hh24:mi:ss') SAVE_TIME
From
sys.user$ u
, sys.obj$ o
, sys.wri$_optstat_tab_history h
where h.obj# = o.obj#
and o.type# = 2
and o.owner# = u.user#
and u.name = 'TEST'
and o.name like 'TEST%'
;
select
'CURRENT' STAT
, u.name OWNER
, o.name TABLE_NAME
, t.rowcnt NUM_ROWS
, t.blkcnt BLOCKS
, t.avgrln AVG_ROW_LEN
, t.samplesize SAMPLE_SIZE
, to_char(t.analyzetime,'yyyy-mm-dd hh24:mi:ss') LAST_ANALYZED
from
sys.user$ u
, sys.tab$ t
, sys.obj$ o
where o.owner# = u.user#
and o.obj# = t.obj#
and u.name = 'TEST'
and o.name like 'TEST%'
/
解説：
4-8行目のCASE式により、h.savtime列の値が現在日時に対して大小関係がどうなっているかで表示を変えています。
また27行目以降は、現在オプティマイザで使用されているディクショナリ（SYS.TAB$）に格納された統計情報の状況を確認するための２番目のクエリーです。
ちなみにSYS.TAB$は、以下のとおりSYSTEM表領域に格納されるディクショナリ表です。
SQL> select OWNER,TABLE_NAME,TABLESPACE_NAME 2 from dba_tables 3 where OWNER = 'SYS' and TABLE_NAME = 'TAB$'; OWNER TABLE_NAME TABLESPACE_NAME ------ ----------- ---------------- SYS TAB$ SYSTEM
長くなったので、検証結果は次回に紹介します。
続く