Oracle10g Cost Base Optimizerにまつわる検証 その１１
＜Oracle10g Cost Base Optimizerにまつわる検証 その１１＞
～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の場合はどうなるのでしょうか？
例えば、１万件のテーブルから１件検索する場合でも、この方程式で算出でき
るはずなのですが、確認してみましょう。
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は実行計画を選定する。
次週は持ち越しになっていた統計情報の運用についてを予定しています。
英語を身に付けたい…茅ヶ崎にて