Oracle10g Cost Base Optimizerにまつわる検証 その１０
＜Oracle10g Cost Base Optimizerにまつわる検証 その１０＞
～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として算出されているようです。
次週も、コストの計算についてもう少し検証を重ねたいと思います。
富士山が綺麗に見える茅ヶ崎にて