Oracle10g Cost Base Optimizerにまつわる検証 その8

<Oracle10g Cost Base Optimizerにまつわる検証 その8>
~DBMS_STATSの変~
ペンネーム:りん

先週に引き続き、DBMS_STATSのCBOに与える影響についてを検証していきたい
と思います。

■環境
Redhat Linux Advanced Server 2.1
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0

■検証
先週、Oracle9iでの検証を行いましたが、今週からOracle 10gについての検証
を行いたいと思います。

Oracle 10g では、DBMS_STATS.GATHER_SYSTEM_STATSでどう変わるのでしょうか

Oracle 9i と同じ検証方法で比較したいと思います。
フェーズとしては、

1.DBMS_STATSを実行していない時のAUX_STATS$ビューの内容
2.負荷をかけていない状態でのDBMS_STATSを実行した時のAUX_STATS$ビュー
    の内容
3.負荷をかけた状態でのDBMS_STATSを実行した時のAUX_STATS$ビューの内容
の3つです。

今回だけでは書ききれないので、年明けになってしまいますが…

SQL> alter session set events '10053 trace name context forever, level 1';

セッションが変更されました。

SELECT文を実行し、作成されたトレースファイルを確認してみると…

  IO-RSC	: 15
  IO-RSP	: 15
  CPU-RSC	: 2427306
  CPU-RSP	: 2427306

あれ?DBMS_STATS動かした記憶もないのに既に値が入っています。

AUX_STATS$ビューを確認してみましょう

SQL> select * from aux_stats$;

SNAME                PNAME           PVAL1 PVAL2
-------------------- ---------- ---------- ---------------------------
SYSSTATS_INFO        STATUS                COMPLETED
SYSSTATS_INFO        DSTART                02-05-2004 13:38
SYSSTATS_INFO        DSTOP                 02-05-2004 13:38
SYSSTATS_INFO        FLAGS               1
SYSSTATS_MAIN        CPUSPEEDNW 400.897016
SYSSTATS_MAIN        IOSEEKTIM          10
SYSSTATS_MAIN        IOTFRSPEED       4096
SYSSTATS_MAIN        SREADTIM
SYSSTATS_MAIN        MREADTIM
SYSSTATS_MAIN        CPUSPEED
SYSSTATS_MAIN        MBRC
SYSSTATS_MAIN        MAXTHR
SYSSTATS_MAIN        SLAVETHR

13行が選択されました。

デフォルトでも、13件のレコードが入っています。
どうやら、Oracle 10gからは、デフォルトでGATHER_SYSTEM_STATSで取得され
るデータが入っているようです。
もちろん、SYSTEM_STATSの値だけではありませんがこれらの値を使用して、CBO
はより詳細なコストを求めようとします。

では、DBMS_STATS.GATHER_SYSTEM_STATSを実行した後ではどのように変化する
のでしょうか。

SQL> EXECUTE DBMS_STATS.GATHER_SYSTEM_STATS(gathering_mode => 
'INTERVAL',interval => 120, statown => 'SYSTEM');

PL/SQLプロシージャが正常に完了しました。

ちなみに、まだDBMS_STATSが終了していない場合は下記のように表示されます。

SQL> select * from aux_stats$;

SNAME           PNAME           PVAL1 PVAL2
--------------- ---------- ---------- --------------------
SYSSTATS_INFO   STATUS                AUTOGATHERING
SYSSTATS_INFO   DSTART                12-14-2004 19:10
SYSSTATS_INFO   DSTOP                 12-14-2004 21:10
SYSSTATS_INFO   FLAGS               0
SYSSTATS_MAIN   CPUSPEEDNW    569.718
SYSSTATS_MAIN   IOSEEKTIM          10
SYSSTATS_MAIN   IOTFRSPEED       4096
SYSSTATS_MAIN   SREADTIM
SYSSTATS_MAIN   MREADTIM
SYSSTATS_MAIN   CPUSPEED
SYSSTATS_MAIN   MBRC
SYSSTATS_MAIN   MAXTHR
SYSSTATS_MAIN   SLAVETHR
SYSSTATS_TEMP   SBLKRDS          4374
SYSSTATS_TEMP   SBLKRDTIM       45970
SYSSTATS_TEMP   MBLKRDS           449
SYSSTATS_TEMP   MBLKRDTIM        7600
SYSSTATS_TEMP   CPUCYCLES      146412
SYSSTATS_TEMP   CPUTIM         257011
SYSSTATS_TEMP   JOB                41
SYSSTATS_TEMP   CACHE_JOB          42
SYSSTATS_TEMP   MBRTOTAL         7694

22行が選択されました。

突然、SYSSTATS_TEMPというのが出てきたのですがOracle 9iでも、INTERVALで
指定した時間がくるまではSYSSTATS_TEMPとして管理されています。
SYSSTATS_MAINが突然ガラリと変わってしまって統計的な情報が取得されない
のを防ぐためでしょう。

DSTOPに表示された時間(今回の場合は、21:10)以降にSELECTすると

SNAME           PNAME                     PVAL1 PVAL2
--------------- -------------------- ---------- --------------------
SYSSTATS_INFO   STATUS                          COMPLETED
SYSSTATS_INFO   DSTART                          12-14-2004 19:10
SYSSTATS_INFO   DSTOP                           12-14-2004 21:10
SYSSTATS_INFO   FLAGS                         0
SYSSTATS_MAIN   CPUSPEEDNW              569.718
SYSSTATS_MAIN   IOSEEKTIM                    10
SYSSTATS_MAIN   IOTFRSPEED                 4096
SYSSTATS_MAIN   SREADTIM                  6.699
SYSSTATS_MAIN   MREADTIM                   .385
SYSSTATS_MAIN   CPUSPEED                    564
SYSSTATS_MAIN   MBRC                          9
SYSSTATS_MAIN   MAXTHR                    27648
SYSSTATS_MAIN   SLAVETHR

上記のような内容になりました。
STATUSもAUTOGATHERINGからCOMPLETEDに更新されています。

では、同じSQL文のコストを確認したいと思います。

  IO-RSC: 17
  IO-RSP: 17
  CPU-RSC: 2427306
  CPU-RSP: 2427306

I/Oのコストは変更されていますが、CPUのコストは変更されていません。
ということは、デフォルトで入っていた値と取得した統計とがあまり乖離して
いなかったということですね

I/OについてはDBMS_STATS.GATHER_SYSTEM_STATSを使用することでSREADTIMや
MREADTIMなどのI/Oコストについての詳細が考慮されるようになり15→17へ見
積りを変更したようです。
つまり、Oracle 10gにおいてもDBMS_STATS.GATHER_SYSTEM_STATSでより正確な
CBOの動作が期待できるということですね

■まとめ

・Oracle9iとは異なり、Oracle 10gではデフォルトの値が使用されるのでCPU
  のコストが0ということはない。
  つまり、コストを計算する材料は必ずある→CBO包囲網
・INTERVALで指定した時間が終わるまで、SYSSTATS_TEMPで管理され統計を収
  集する
・Oracle 10gでも、DBMS_STATS.GATHER_SYSTEM_STATSを使用することによりコ
  スト計算の精度が上がる

今年の検証はここまでです。
次回から、DBMS_STATSで取得した統計情報の運用についてと、Oracle 10gで実
装されたDBMS_STATSの新機能についても検証したいと思います。

本年もご愛読頂きありがとうございました。
来年も何卒よろしくお願い致します。

イカ釣り部発足?!の茅ヶ崎にて