しばちょう先生の試して納得！DBAへの道　indexページ▶▶

 

みなさん、こんにちは。“しばちょう”こと柴田長（しばた つかさ）です。少しずつですが、夜の風が気持ち良くなってきましたが、私は締め切り間近に迫ったこの原稿を目の前に焦りまくっています。皆さんはいかがお過ごしでしょうか？

まだ先ですが、Oracle DBA & Developer Day 2012が11月20日に開催されることが決定いたしました。私は最大可用性構成（Maximum Availability Architecture）のトラック内のセッションを担当する予定です。また、昨年ご好評頂いた津島博士やシバタツのセッション枠も用意されておりますので、是非とも、ご参加頂ければありがたいです。

さて、今回もオプティマイザ統計収集の管理についてご紹介していきます。前回までの演習ではオプティマイザ統計情報の収集時間を短縮削減する機能として、「DBMS_STATS.AUTO_SAMPLE_SIZE」による高速化と、「STALE_PERCENT」オプションを使用して統計情報が失効しているオブジェクトのみの統計収集を行う効率化を体験して頂きましたが、今回はSQLの実行計画の安定化につながる「収集した統計情報を保留する」機能をご紹介したいと思います。

これまでも、統計情報の精度が良い（=実際に格納されているデータの特徴に近い）状態を保つことが、オプティマイザに最良な実行計画を選択させる為のポイントになるとお伝えしてきましたが、これは計画的に統計情報を収集して下さいということを意味しています。そして、統計情報を収集するということは統計情報が書き変わることになり、オプティマイザが選択する実行計画が収集前後で変化する可能性があるということです。基本的には実データに即したより良い実行計画に変化しますが、多くのトラブルの現場を経験されている方は・・・。まさにそのような不安をお持ちの方に、今回ご紹介する「統計情報を保留する」機能を積極的に活用して頂きたいと考えています。この機能は新しい統計情報で既存の統計情報を書き換える前に、それらの統計情報の差や影響（SQLの実行計画が変化するか否か）を確認することができます。

以下の演習をこれまでの演習で使用したOracle Database 11g Release 2 Enterprise Editionのデータベースで試してみてください。

■ 1. TRYスキーマに、第一カラムがNUMBER型で主キーを構成し、第二カラムがCHAR(100)型の1000レコードが格納されている表「TBL10」を作成して下さい。また、表TBL10の統計情報を収集して下さい。

sqlplus TRY/TRY 

SQL> -- TBL10表の作成
create table TBL10 (COL1 number NOT NULL, COL2 char(100)) ;
insert into TBL10 select LEVEL, 'hoge'||to_char(LEVEL) from DUAL connect by LEVEL <= 1000 ;
commit ;
create unique index IDX_TBL10_COL1 on TBL10(COL1) ;
alter table TBL10 add primary key (COL1) using index ;

SQL> -- 表の統計情報の収集
exec DBMS_STATS.GATHER_TABLE_STATS(ownname => 'TRY', tabname => 'TBL10', cascade => FALSE);

はい、復習ですね。前回の演習とほとんど同じSQL文が使用できると思います。

一点補足させて頂くと、表の統計情報の収集をDBMS_STATS.GATHER_TABLE_STATSプロシージャで実行していますが、見慣れない引数「cascade => FALSE」が設定されています。マニュアル「PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス 11gリリース2(11.2)」の「GATHER_TABLE_STATSプロシージャ」に記述があるように、「CASCADE」パラメータは「TABNAME」パラメータで設定した表に作成されている索引の統計情報を同時に収集するか否かを指定することが可能です。デフォルトではOracle Databaseが自動的に判断する定数DBMS_STATS.AUTO_CASCADEが設定されています。

今回は「CASCADE」パラメータを「FALSE」に設定している為、表TAB10の統計情報の収集のタイミングで索引IDX_TBL10_COL1の統計情報は収集しないことになります。何故、このような設定を施したのでしょうか。それを理解するには上記の回答例の各SQLの間毎に、索引IDX_TBL10_COL1の統計情報がどのような変化をするのかを確認してみると答えが導き出せますね。

答えは、索引作成のタイミングで索引の統計情報は収集されているということです。よって、表の統計情報の収集時に再度、索引の統計情報を収集する必要はないと明確に理解できていたので、このような設定を施したのです。その他のタイミングとしては、索引の再作成（alter index <INDEX_NAME> rebuild;）です。このような細かいノウハウはいつ必要になるか分かりませんが、貴重な財産になるので是非とも覚えておいてください。

■ 2. 表TBL10において、COL1の値が1000以下であるレコードのCOL2の値の種類を検索するSELECT文を実行して下さい。

sqlplus / as sysdba
SQL> alter system flush buffer_cache ;

SQL> connect TRY/TRY
set autotrace on
select count(*) from (select distinct(COL2) from TBL10 where COL1 <= 1000) ;

  COUNT(*)
----------
      1000

実行計画
----------------------------------------------------------
Plan hash value: 2388482128

------------------------------------------------------------------------------
| Id  | Operation            | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |       |     1 |       |    15   (7)| 00:00:01 |
|   1 |  SORT AGGREGATE      |       |     1 |       |            |          |
|   2 |   VIEW               |       |  1000 |       |    15   (7)| 00:00:01 |
|   3 |    HASH UNIQUE       |       |  1000 |   102K|    15   (7)| 00:00:01 |
|*  4 |     TABLE ACCESS FULL| TBL10 |  1000 |   102K|    14   (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter("COL1"<=1000)


統計
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         47  consistent gets
         47  physical reads
          0  redo size
        549  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

この演習で確認して頂きたいのは、現在の統計情報を使用してオプティマイザがどのような実行計画を選択するのかという部分です。今回は「set autotrace on」を使用して確認しています。これ以外の方法も以前ご紹介していますが、覚えていますかね？

上記の出力結果からは、表TBL10に対して「TABLE ACCESS FULL」というオペレーションでアクセスが行われていることが確認できます。演習0において、皆さんは表TBL10を作成して1000レコードをINSERTしました。つまり、表TBL10にどのようなレコードが格納されているのかを把握していますよね。その状態でこのSELECT文を見れば、WHERE句があるけれど、この条件では結局全てのレコードにヒットすることになるだろうと予想出来ますよね。しかし、もしも表TBL10にレコードをINSERTしたのが自分ではなく他人だった場合は予想できますか？ということです。このように考えると、オプティマイザが最良な実行計画を選択する為には、精度の高い統計情報が必要であることが少しは理解して頂けると思います。

■ 3. 表TBL10に10000レコード追加してください。

sqlplus TRY/TRY

SQL> -- 表TBL10に10000レコード追加
insert into TBL10 select LEVEL+1000, 'hoge'||to_char(LEVEL+1000) from DUAL connect by LEVEL <= 10000 ;
commit ;

この演習により、表TBL10に格納されているレコード数は、当初の1000レコードから11000レコード（10倍以上）に増加したことになりますね。ここで認識しておいて頂きたいポイントは、表TBL10の実際のデータ保持の状態と統計情報に大きな乖離が生まれたという部分です。

■ 4. 表TBL10に対する統計情報収集時に、収集した新しい統計情報を既存の統計情報に上書きしないよう設定し、GATHER_TABLE_STATSを実行して下さい。を収集するように、OPTIONSパラメータを適切に設定してDBMS_STATS.GATHER_SCHEMA_STATSプロシージャを実行してください。

sqlplus TRY/TRY
SQL> -- 保留の設定
exec DBMS_STATS.SET_TABLE_PREFS(ownname => 'TRY', tabname => 'TBL10', -
pname => 'PUBLISH', pvalue => 'FALSE');

SQL> -- 設定が反映されているか確認
select DBMS_STATS.GET_PREFS(pname => 'PUBLISH', ownname => 'TRY', tabname => 'TBL10') from DUAL ;

DBMS_STATS.GET_PREFS(PNAME=>'PUBLISH',OWNNAME=>'TRY',TABNAME=>'TBL10')
--------------------------------------------------------------------------------
FALSE

SQL> -- 表TBL10の統計情報を収集
exec DBMS_STATS.GATHER_TABLE_STATS(ownname => 'TRY', tabname => 'TBL10');

さあ、いよいよ統計情報を保留する機能を活用していきましょう。

上記の回答例では、DBMS_STATS.SET_TABLE_PREFSプロシージャを使用して、表単位でPUBLISHパラメータ（デフォルト：TRUE）を「FALSE」に設定することで、統計情報を収集しても現在の統計情報を更新しない（保留する）動作になります。また、特定スキーマ内の全表に設定する場合にはDBMS_STATS.SET_SCHEMA_PREFSプロシージャを、データベース内の全表に設定する場合にはDBMS_STATS.SET_DATABASE_PREFSプロシージャを使用します。

ここで意外と知られていないこれらのプロシージャの注意点を述べておきます。

SET_SCHEMA_PREFSプロシージャとSET_DATABASE_PREFSプロシージャは、スキーマ単位、データベース単位でパラメータ設定が施されるのは正しいのですが、内部的には、SET_TABLE_PREFSが表単位で呼び出されていることを理解しておく必要があります。結局は個別の表毎に設定されるものであり、スキーマとしての設定、データベースとしての設定として保持されるものではないです。具体的には、各プロシージャを実行した時点で存在していた表のみがパラメータ設定の対象となるだけで、それ以降に新規に作成した表はデフォルト値、もしくは、DBMS_STATS.SET_GLOBAL_PREFSで設定されたパラメータが設定される動作となります。

次に、保留の設定を施した後、いつも通りGATHER_TABLE_STATSで統計情報を収集していますが、ここで作業を終わらせてはいけないですよね。本当に、既存の統計情報が上書き（更新）されていないのかを確認してみましょう。

sqlplus TRY/TRY

SQL> -- 統計情報の確認
set serveroutput on
exec print_table('select * from USER_TAB_STATISTICS where TABLE_NAME = ''TBL10''') ;

TABLE_NAME                    : TBL10
PARTITION_NAME                :
PARTITION_POSITION            :
SUBPARTITION_NAME             :
SUBPARTITION_POSITION         :
OBJECT_TYPE                   : TABLE
NUM_ROWS                      : 1000
BLOCKS                        : 46
EMPTY_BLOCKS                  : 0
AVG_SPACE                     : 0
CHAIN_CNT                     : 0
AVG_ROW_LEN                   : 105
AVG_SPACE_FREELIST_BLOCKS     : 0
NUM_FREELIST_BLOCKS           : 0
AVG_CACHED_BLOCKS             :
AVG_CACHE_HIT_RATIO           :
SAMPLE_SIZE                   : 1000
LAST_ANALYZED                 : 13-9月 -2012 16:52:09
GLOBAL_STATS                  : YES
USER_STATS                    : NO
STATTYPE_LOCKED               :
STALE_STATS                   : NO
-----------------

[参考]　print_table：http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1035431863958

如何でしょうか。演習0を終えた直後に統計情報を確認されていた方は、それと比較してみるのが一番良い方法ですね。

上記の出力結果だけでも、NUM_ROWS列の値が1000と表示されていることから更新されていないことが理解できます。なぜならば、もし統計情報が更新されているとしたら11000と表示されているべきです。他ではLAST_ANALYZED列がこの統計情報を収集した日時を表しているので、その情報を基に推測することも可能だと思います。

では、保留を設定した後に収集した統計情報はどこに格納されているのか。どのように確認できるのか。と疑問を持って下さいね。その答えは、データ・ディクショナリ・ビュー「USER_[TAB | COL | IND | TAB_HISTGRAM]_PENDING_STATS」となりますので試しにアクセスしてみてください。

sqlplus TRY/TRY

SQL> -- 保留中の統計情報の確認
set serveroutput on
exec print_table('select * from USER_TAB_PENDING_STATS  where TABLE_NAME = ''TBL10''') ;

TABLE_NAME                    : TBL10
PARTITION_NAME                :
SUBPARTITION_NAME             :
NUM_ROWS                      : 11000
BLOCKS                        : 174
AVG_ROW_LEN                   : 105
SAMPLE_SIZE                   : 11000
LAST_ANALYZED                 : 13-9月 -2012 17:19:48
-----------------

[参考]　print_table：http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1035431863958

■ 5. 既存の統計情報と保留中の（新しく取得した）統計情報を比較して下さい。

sqlplus TRY/TRY
SQL> -- 既存の統計情報と保留中の統計情報の差分比較
set head off trim on linesize 1000 pagesize 0 long 1000000 longchunksize 1000000
select REPORT, MAXDIFFPCT from table(DBMS_STATS.DIFF_TABLE_STATS_IN_PENDING('TRY', 'TBL10'));

###############################################################################

STATISTICS DIFFERENCE REPORT FOR:
.................................

TABLE         : TBL10
OWNER         : TRY
SOURCE A      : Current Statistics in dictionary
SOURCE B      : Pending Statistics
PCTTHRESHOLD  : 10
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

TABLE / (SUB)PARTITION STATISTICS DIFFERENCE:
.............................................

OBJECTNAME                  TYP SRC ROWS       BLOCKS     ROWLEN     SAMPSIZE
...............................................................................

TBL10                       T   A   1000       46         105        1000
                                B   11000      174        105        11000
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

COLUMN STATISTICS DIFFERENCE:
.............................

COLUMN_NAME     SRC NDV     DENSITY    HIST NULLS   LEN  MIN   MAX   SAMPSIZ
...............................................................................

COL1            A   1000    .001       NO   0       4    C102  C20B  1000
                B   11000   .000090909 NO   0       5    C102  C3020 11000
COL2            A   1000    .001       NO   0       101  686F6 686F6 1000
                B   11000   .000090909 NO   0       101  686F6 686F6 11000
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

INDEX / (SUB)PARTITION STATISTICS DIFFERENCE:
.............................................

OBJECTNAME      TYP SRC ROWS    LEAFBLK DISTKEY LF/KY DB/KY CLF     LVL SAMPSIZ
...............................................................................


                              INDEX: IDX_TBL10_COL1
                              .....................

IDX_TBL10_COL1  I   A   1000    2       1000    1     1     16      1   1000
                    B   11000   20      11000   1     1     167     NUL 11000
###############################################################################

演習3の最後に、現在の統計情報と保留中の統計情報をそれぞれ参照するビューをご説明したので、力技で比較することもできますが、今回は、非常にスマートはファンクションをご紹介させて頂きたいと思います。それが、「DBMS_STATS.DIFF_TABLE_STATS_IN_PENDING」となります。上記はこのファンクションを使用して、表TBL10の現在の統計情報と保留中の統計情報を比較した結果が出力されています。

最上部のセクション「STATISTICS DIFFERENCE REPORT FOR」において、SOURCE Aが現在ディクショナリ上に展開されている（オプティマイザが参照する）統計情報で、SOURCE Bが保留中の統計情報であることが読み取れます。これを基に次のセクション「TABLE / (SUB) PARTITION STATISTICS DIFFERENCE」を確認すると、ROWS（行数）が1000から11000へ増加しており、BLOCKS（TBL10のセグメントを構成するブロック数）が46から176に増加していることが理解できます。次のセクションでは列統計、最後のセクションでは索引統計までも確認できます。レコード数が10倍も変化すると統計情報も大きく変わってくることを理解して頂けたのではないでしょうか。

他にも2つの統計情報を比較するファンクションが用意してありますので参考にしてみてください。これらの詳細は、マニュアル「PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス 11gリリース2(11.2)」をご参照ください。

  • DIFF_TABLE_STATS_IN_STATTAB
    • 2つの異なるユーザー統計表
    • statidで識別可能な2つの統計セットが含まれている単一のユーザー統計表
    • ユーザー統計表とディクショナリ履歴
  • DIFF_TABLE_STATS_IN_HISTORY
    • 過去の2つのタイムスタンプにおける統計情報

■ 6. 現在の統計情報と保留中の統計情報のそれぞれにおいて演習1のSELECT文を実行し、実行計画を確認して下さい。

sqlplus / as sysdba
SQL> alter system flush buffer_cache ;

SQL> -- 現在の統計情報を使用した実行
connect TRY/TRY
set autotrace on
select count(*) from (select distinct(COL2) from TBL10 where COL1 <= 1000) ;

  COUNT(*)
----------
      1000

実行計画
----------------------------------------------------------
Plan hash value: 2388482128

------------------------------------------------------------------------------
| Id  | Operation            | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |       |     1 |       |    15   (7)| 00:00:01 |
|   1 |  SORT AGGREGATE      |       |     1 |       |            |          |
|   2 |   VIEW               |       |  1000 |       |    15   (7)| 00:00:01 |
|   3 |    HASH UNIQUE       |       |  1000 |   102K|    15   (7)| 00:00:01 |
|*  4 |     TABLE ACCESS FULL| TBL10 |  1000 |   102K|    14   (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter("COL1"<=1000)


統計
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        175  consistent gets
        175  physical reads
          0  redo size
        549  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processeTd

まずは、現在の統計情報を使用してSELECT文を実行します。これは通常通りにオペレーションするだけなので演習1の繰り返しになります。実行計画は変化しましたか？していませんよね。表TBL10のレコード数が11000レコードに増加しているにも関わらず、統計情報が更新されていないので演習1を実施した1000レコードが格納されている時点と同じ実行計画となっていますね。

オプティマイザはあくまで1000レコードが格納されている時点の統計情報を基に実行計画を選択しているだけで、実際に11000レコードが格納されていることは知りません。つまり、1000レコードが格納されていたら最良な実行計画かもしれませんが、今回の11000レコードが格納されている表にもそれが当てはまるのかはわかりませんよね。また、現在の統計情報においてはBLOCKS=46となっていて、実際に全表検索してみたら175ブロック（=physical readsより）だったという部分も興味深いポイントだと思います。

次に、保留中の統計を使用してSELECT文の実行し、その実行計画を確認してみます。

ここで登場するのが、OPTIMIZER_USE_PENDING_STATISTICS初期化パラメータ（デフォルト：FALSE）です。この初期化パラメータを「TRUE」に設定することで、オプティマイザは保留中の統計情報を使用して実行計画を選択する動きに変わります。

今回の演習では、ALTER SESSION文を使用して、OPTIMIZER_USE_PENDING_STATISTICS初期化パラメータをセッション単位でTRUEに設定しています。これにより、TRUEに設定したセッションのみが保留中の統計情報を使用し、その他のセッションでは現在の統計情報を使用する動きのままで影響はないです。

sqlplus / as sysdba
SQL> alter system flush buffer_cache ;

SQL> -- 保留中の統計情報を使用した実行
connect TRY/TRY
alter session set OPTIMIZER_USE_PENDING_STATISTICS = TRUE ;
set autotrace on
select count(*) from (select distinct(COL2) from TBL10 where COL1 <= 1000) ;

  COUNT(*)
----------
      1000


実行計画
----------------------------------------------------------
Plan hash value: 1370601824

-------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                |     1 |       |    20   (5)| 00:00:01 |
|   1 |  SORT AGGREGATE                |                |     1 |       |            |          |
|   2 |   VIEW                         |                |  1000 |       |    20   (5)| 00:00:01 |
|   3 |    HASH UNIQUE                 |                |  1000 |   102K|    20   (5)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID| TBL10          |  1000 |   102K|    19   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | IDX_TBL10_COL1 |  1000 |       |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("COL1"<=1000)


統計
----------------------------------------------------------
          0  recursive calls
          0  db block gets

         19  consistent gets
        110  physical reads
          0  redo size
        549  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

さあ、いかがでしょうか。保留中の統計情報を使用した場合は索引IDX_TBL10_COL1を使用して表TBL10へアクセスする（INDEX RANGE SCAN + TABLE ACCESS BY INDEX ROWID）実行計画が選択されることが確認できましたね。本来であれば実行時間の比較を行うのが良いと思いますが、今回はディスクから読み込んだブロック数（physical reads）を比較することで、どちらの実行計画が効率的かを判断してみましょう。

現在の統計情報を使用したTABLE ACCESS FULLの実行計画では、physical readsが175でしたが、保留中の統計を使用したINDEX_RANGE_SCANの実行計画では110と低減していることが理解できます。つまり、保留中の統計を使用した方が、より良い実行計画が選択されていると判断することができますね。

■ 7. 保留中の統計情報を公開して下さい。

sqlplus / as sysdba
SQL> -- Publish or DELETE Pending Statistics
BEGIN
  DBMS_STATS.PUBLISH_PENDING_STATS(ownname => 'TRY', tabname => 'TBL10', no_invalidate => FALSE);
END;
/

と言うことで、保留中の統計情報でディクショナリ上に展開されている現在の統計情報を上書き（公開）してみましょう。

公開する為には、DBMS_STATS.PUBLISH_PENDING_STATSプロシージャを実行すれば良いだけです。簡単ですね。ただし、上記の実行例ではNO_IINVALIDATEパラメータを追加設定していますが、このパラメータの意味については、また機会があればご紹介したいと思います。かなり重要なパラメータですので、ご興味のある方は、マニュアル「PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス 11gリリース2(11.2)」でご確認下さいね。

ちなみに、保留中の統計情報を公開せずに削除したい場合には、DBMS_STATS.DELETE_PENDING_STATSプロシージャを実行することになります。

さて、Oracle Database 11gでのオプティマイザ統計情報の保留機能を体験して頂きましたが、いかがでしたでしょうか？

正直、あまり知られていない機能ですが、統計情報の比較や公開前に新しい統計情報がアプリケーションにどの程度影響を与えるのかを事前に検証することができますので、使い方によってはデータベース・システムの安定稼働に大きく貢献できる機能ではないかという可能性が皆さんの中に芽生えたのであれば、非常に嬉しい限りです。私は最近、この機能とその他のオプティマイザ関連機能とを組み合わせることで、より効果的なソリューションが得られないのかを検討し続けていますので、何かアイディアがあれば呟いて頂けるとありがたいです。今回もありがとうございました。次回も頑張りますので、よろしくお願いします。

 

ページトップへ戻る▲

 

しばちょう先生の試して納得！DBAへの道　indexページ▶▶