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

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

■おさらい
先週は、コストの計算をしていましたが、I/Oしかフォーカスしていませんで
した。つまり、I/Oのコストが算出されただけでした。

GATHER_SYSTEM_STATSを取得して、CPUのコストも…
なんて書いていたわりに、CPUが入っていないのはおかしな話です。

では、GATHER_SYSTEM_STATSで取得したCPUSPEEDはどう使われるのでしょうか

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

■検証
先週書いた計算式をまとめると
((Single Block Read x SREADTIM) + (Multi Block Read x MREADTIM))/SREADTIM
でした。
CPUの見積りを入れてみましょう
((Single Block Read x SREADTIM) + (Multi Block Read x MREADTIM) + (CPU Cycles / CPUSPEED))/SREADTIM
となります。

CPUSPEEDは1秒間に何回実行できるかをMhzで表した統計値です。
CPU Cycleは使用するCPUの回数です。
CPU Cycle / CPUSPEEDで、全てのCPUを使う時間が算出されることになります。
・Single Block Readの時間
・Multi Block Readの時間
・CPUを使用する時間
という形で算出されるはずです。

では、早速検証しましょう。

今回は、負荷をかけていない時にGATHER_SYSTEM_STATSを実行しオフピーク時
で検証してみます。
AUX_STATS$の値はこのように変化しています。

SQL>; select * from aux_stats$ where sname = 'SYSSTATS_MAIN';

SNAME           PNAME           PVAL1 PVAL2
--------------- ---------- ---------- -----------------
SYSSTATS_MAIN   CPUSPEED          569
SYSSTATS_MAIN   CPUSPEEDNW     566.04
SYSSTATS_MAIN   IOSEEKTIM          10
SYSSTATS_MAIN   IOTFRSPEED       4096
SYSSTATS_MAIN   MAXTHR         131072
SYSSTATS_MAIN   MBRC                6
SYSSTATS_MAIN   MREADTIM       92.778
SYSSTATS_MAIN   SLAVETHR
SYSSTATS_MAIN   SREADTIM        7.404

実行計画
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=129 Card=10000 Bytes=70000)
   1    0   TABLE ACCESS (FULL) OF 'STATS_TEST' (TABLE) (Cost=129 Card=10000 Bytes=70000)

コストは129とレポートされました。
この場合テーブルスキャンコストが発生するはずです。

では、方程式に値を代入していきましょう

(1)Single Block Read
   (HWM-(INT(HWM/MBRC)*MBRC))
   →64-(INT(64/6)*6)=4

(2)Multi Block Read
   INT(HWM/MBRC)
   →INT(64/6)=10

(3)CPU Cycle
   CPU CycleトレースファイルのCPU-RSCに出力されていますので、この値を使います。
   1727286/1000=1727.286 ms

(( (1) * 7.404 ) + ( (2) * 92.778 ) + ( (3) / 569 )) / 7.404 = 129.72

となり、合致します。

では、IndexScanの場合はどうなるのでしょうか?
例えば、1万件のテーブルから1件検索する場合でも、この方程式で算出でき
るはずなのですが、確認してみましょう。

STATS_TESTテーブルにPKを作成し、検索してみたいと思います。

SQL> alter table stats_test add constraint PK_STATS_TEST primary key ( id ) using index;

表が変更されました。

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

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

SQL> select * from stats_test where id = '438';

        ID      VALUE
---------- ----------
       438        438


実行計画
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=7)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'STATS_TEST' (TABLE) (Cost=2 Card=1 Bytes=7)
   2    1     INDEX (UNIQUE SCAN) OF 'PK_STATS_TEST' (INDEX (UNIQUE))(Cost=1 Card=1)

INDEX SCANになりました

トレースファイルを見て見ると、Block Accessは2Blockとなっています。
MBRCが6なので、Single Block Readで見積もってみます。

ということは、

(( 2 * 7.404 ) + ( 0 * 92.778 ) + ( 21734 * 569 )) = 2

となりますね

結局のところ物理ディスクからデータを読み出す際のコストの計算方法は変わ
らないようです。

というわけで、コストの計算については以上です。

■まとめ
読み出すブロック数からAUX_STATS$のシステム統計値を使用してかかる時間を
コストとして算出する。その値を基に、Optimizerは実行計画を選定する。

次週は持ち越しになっていた統計情報の運用についてを予定しています。

英語を身に付けたい…茅ヶ崎にて