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

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

■おさらい
DBMS_STATS.GATHER_SYSTEM_STATSで、システム統計が取得されるのですが
統計情報が変更されて、実行計画が変わってしまうかもしれない…
では、どうやって運用すればいいのかを検証により考えてみましょう
というお話の予定でしたが…
下記のご質問を頂いたので運用の検証に入る前にご質問頂いた内容について…

■読者様からのご質問
——————————————————————-
 Sort Merge Joinの場合Total CPU sort cost: 10331355
 Order byの場合Total CPU sort cost: 3980211

 このそれぞれの場合の「Total CPU sort cost」は、
 どのようにして算出されたのでしょうか。
——————————————————————-
算出したわけではなくて、トレースファイルに出力された数字を
そのまま掲載しましたのですが、その旨を記載していませんでした。
申し訳ありません。

ただ、せっかくのご質問ですので、なにか発展できないものか?と
思っていたところ、コストの計算方法についてをまだ検証していないことに
気づいたので、さっそく検証してみたいと思います。

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

■検証
Oracle9iからは、Timeベースでコストを算出するようになりましたよね
それは、どういうことかというと、I/Oの見積りではなくて、
I/Oに掛かる時間をコストとして見るというところでしょうか
Oracle10gからは、デフォルトでシステム統計が取得されるようになり
より、精度をあげようとしています。
統計を取得したくない場合はSTATISTICS_LEVEL=BASICで運用すれば
これらの値は取得されません。

では、DBMS_STATS.GATHER_SYSTEM_STATSで取得したシステム統計はどのように
利用されるのでしょうか

とりあえず、AUX_STATS$を参照してみましょう

SQL> select * from aux_stats$;

SNAME           PNAME           PVAL1 PVAL2
--------------- ---------- ---------- ----------------
SYSSTATS_INFO   STATUS                COMPLETED
SYSSTATS_INFO   DSTART                12-27-2004 14:38
SYSSTATS_INFO   DSTOP                 12-27-2004 14:38
SYSSTATS_INFO   FLAGS               0
SYSSTATS_MAIN   CPUSPEEDNW     566.04
SYSSTATS_MAIN   IOSEEKTIM          10
SYSSTATS_MAIN   IOTFRSPEED       4096
SYSSTATS_MAIN   SREADTIM       14.603
SYSSTATS_MAIN   MREADTIM       92.778
SYSSTATS_MAIN   CPUSPEED          230
SYSSTATS_MAIN   MBRC                6
SYSSTATS_MAIN   MAXTHR         131072
SYSSTATS_MAIN   SLAVETHR

コストを計算するにあたって、整理するために
単純にテーブルをFullScanさせてみたいと思います。

まず、今までテストしてきた、STATS_TESTテーブル(1万件)のFull Scanで
試してみたいと思います

まず、dbms_space.unused_spaceを使用して
STATS_TESTテーブルのHigh Water Markを調べてみると
Uniform Size 1M byte分を取得していて、64 Block使用しているようです。

これに、Multi Block Read時の実績平均取得ブロック(MBRC)を使います。
1度のReadで平均どれだけのBlockを取得したのか?という値です。
AUX_STATS$を見ると、6となっています。

ということは…

ReadするBlock数 / MBRC=必要なMulti Block ReadのI/O回数(見積り値)
という式ができるはずです。
つまり、64 Block / 6 MBRC=10回のMulti Block ReadのI/Oが
発生する見積りです。
ということは、内訳として
Single Block Read…4回
Multi Block Read…10回
ということになるはずです。

残りの4 Blockについては、Single Block Readとすると

Single Block Read x SREADTIM=58.412 ms
Multi Block Read x MREADTIM=927.78 ms
986.192 ms / SREADTIM = 67.53がCostとして算出されているようです。

次週も、コストの計算についてもう少し検証を重ねたいと思います。

富士山が綺麗に見える茅ヶ崎にて