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

＜Oracle10g Cost Base Optimizerにまつわる検証　その９＞
～DBMS_STATSの編～
ペンネーム：りん

先日、誕生日を迎えたりんです。こんにちは
年齢よりも体力年齢が年上ですが気にせず

さて、今週からまた検証生活をお送りしたいと思います。今年も当メルマガを
何卒よろしくお願い致します。

■おさらい
DBMS_STATSの編と題して、昨年から配信していますがお正月休みもあったので
おさらいしておきます。
・Monitoring属性
・Oracle Scheduler
・動的サンプリング
・DBMS_STATS
というCBO包囲網が形成されているということを書いてきました。
Oracle10gでは、RBOがサポートされなくなった為コストを算出する材料が自動
的に取得される…というわけです。
さらに、CBOの精度を高める為にDBMS_STATS.GATHER_SYSTEM_STATSについてを
Oracle 9i Release 2と比較して記載してきました。

■今回のお題
DBMS_STATS.GATHER_SYSTEM_STATSは、情報を取得して統計情報を取るわけです
がほとんどの場合、システムは時間帯によって負荷が変わってきます。
昼はスイスイ動いたのに、夜になると全然ダメ…
なんてことになったら「余計なことするなよー」と言われちゃいそうです。
なんとかならないものでしょうか？

というわけで、今回着目するのはオンライン時間帯とバッチ処理時間帯での運
用も視野に入れた、GATHER_SYSTEM_STATSについてを検証したいと思います。

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

■検証
では、CPUに高負荷をかけた場合に、どう変化するのかを確認したいと思いま
す。今までは、簡単なSELECT文でしたが、今回はSubqueryを使用して実行計画
が変化するかどうかを確認したいと思います。

100万件データをインサートしたテーブルを使ってSubQueryしちゃいましょう

SQL> create table dodekai_table ( id number, value number, data varchar2(200));

表が作成されました。

SQL> begin
  2    for cnt in 1..1000000 loop
  3      insert into dodekai_table (id, value, data)
  4      values ( cnt, cnt,
  5      'データデータデータデータデータデータデータデータデータデータ
         データデータデータデータデータデータデータデータデータデータ');
  6    end loop;
  7    commit;
  8  end;
  9  /

では、前回取得した「なにも負荷をかけていない状態」での統計情報を保持し
た状態でCPUを使う処理として大量のソート処理をSubQueryを使用して実行し
てみます。

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

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

SQL> select out.id, out.value from ( select avg(VALUE) avg_value from dodekai_table ) sbq,
  2 stats_test out where out.value <= sbq.avg_value order by id;

実行計画
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4981 Card=500 Bytes=10000)
   1    0   SORT (ORDER BY) (Cost=4981 Card=500 Bytes=10000)
   2    1     NESTED LOOPS (Cost=4980 Card=500 Bytes=10000)
   3    2       VIEW (Cost=4962 Card=1 Bytes=13)
   4    3         SORT (AGGREGATE)
   5    4           TABLE ACCESS (FULL) OF 'DODEKAI_TABLE' (TABLE) (Cost=4962 Card=974847 Bytes=12673011)
   6    2       TABLE ACCESS (FULL) OF 'STATS_TEST' (TABLE) (Cost=18 Card=500 Bytes=3500)

見てみると、100万件入っているDODEKAI_TABLEがフルスキャンされ、Sortされ
たViewが作成されています。
作成されたViewと、STATS_TESTテーブルとNested Loopでジョインされて、Sort
されたようですね…
では、このSQL文のトレースを見てみたいと思います。

なにやら、結合条件のコストが出力されていましたがSort処理のCPUのコスト
に着目したいと思います。
Sort Merge Joinの場合Total CPU sort cost: 10331355
Order byの場合Total CPU sort cost: 3980211
この結果から、Sort Merge Joinを採用せず、Order Byが採用されているようです。

では、CPUの負荷をあげてDBMS_STATS.GATHER_SYSTEM_STATSを実行してみます。
1時間CPUをガリガリ使って、統計情報を取得させてみた結果と負荷をかけるま
えと比較してみましょう

SYSSTATS_MAIN	CPUSPEEDNW	566.641→566.641
SYSSTATS_MAIN	IOSEEKTIM	10→10
SYSSTATS_MAIN	IOTFRSPEED	4096→4096
SYSSTATS_MAIN	SREADTIM	6.699→14.603
SYSSTATS_MAIN	MREADTIM	0.385→92.778
SYSSTATS_MAIN	CPUSPEED	564→230
SYSSTATS_MAIN	MBRC		9→6
SYSSTATS_MAIN	MAXTHR		27648→131072

となりました。
CPUSPEEDNW、IOSEEKTIM、IOTFRSPEEDに変更はありませんがその他の値は変更
されています。
IOもCPUも軒並み悪化しているようです。
これが、高負荷時の統計情報となります。

ではこの状態で同じSQL文を実行してみましょう

実行計画
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=20127 Card=500 Bytes=10000)
   1    0   SORT (ORDER BY) (Cost=20127 Card=500 Bytes=10000)
   2    1     NESTED LOOPS (Cost=20126 Card=500 Bytes=10000)
   3    2       VIEW (Cost=20060 Card=1 Bytes=13)
   4    3         SORT (AGGREGATE)
   5    4           TABLE ACCESS (FULL) OF 'DODEKAI_TABLE' (TABLE) (Cost=20060 Card=974847 Bytes=12673011)
   6    2       TABLE ACCESS (FULL) OF 'STATS_TEST' (TABLE) (Cost=67 Card=500 Bytes=3500)

実行計画が変わるかな？！と思ったのですが実行計画に変化はありませんでした。
ただ、Costに注目してみると…
負荷をかける前後で、Costが変更されています。
CBOはとにかくCostの低いもの実行計画を選択するのであれば負荷状況によっ
ては、もしかしたら実行計画が変わる可能性もあるわけですよね。

■まとめ
・DBMS_STATS.GATHER_SYSTEM_STATSは取得時の負荷によって値が変わる
・実行計画を作成する際のコストも変更される。
→ということは、実行計画が変わる可能性もある…

というわけで、次回は統計情報の運用についてを検証したいと思います。

2005年も頑張るぞ！茅ヶ崎にて…

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

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