日別アーカイブ: 2015/02/15

「オプティマイザ統計の保留」の検証(その3)

前回のおさらい

前回は「オプティマイザ統計の保留」をシナリオに沿って動作を検証しました。

テーブルの統計情報公開属性を「FALSE」(=保留)に設定すれば、統計情報を取得したとしても現在の実行計画に影響を与えず、保留状態に置かれます。

そして、保留状態の統計情報を手動で公開すれば、次回実行計画を算出されるタイミングでその公開された統計情報が使用されて新しい実行計画が作成されることを確認しました。

この機能の概要は前々回の記事「オプティマイザ統計の保留」の検証(その1)で紹介しましたが、別のマニュアルにある記事も紹介しておきます。

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

このビューは3つの部分(テーブル、パーティション、サブパーティション)の問い合わせから成っていることがわかります。
今回の検証ではテーブルのみを対象とするので、1番最初のパートに着目します。
それから、ハイライト表示した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$)に格納された統計情報の状況を確認するための2番目のクエリーです。

ちなみに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

長くなったので、検証結果は次回に紹介します。

続く