オプティマイザの判断ミスを疑ってみよう：Oracleパフォーマンス障害の克服（4）（3/3 ページ）

» 2004年11月09日 00時00分公開

データの分布・量に偏りのある表でのオプティマイザの挙動

　では、リスト9のデータをtest_tableに挿入して、データ分布・量を変化させると、オプティマイザはどのような判断を下すでしょうか。

データ：

BEGIN

  FOR i IN 1..1000 LOOP

    INSERT INTO test_table(no,data)

    VALUES((i * 10)+1, TO_CHAR('data_' || i));



    INSERT INTO test_table(no,data)

    VALUES((i * 10)+2, TO_CHAR('data_' || i));



    INSERT INTO test_table(no,data)

    VALUES((i * 10)+3, TO_CHAR('data_' || i));



    INSERT INTO test_table(no,data)

    VALUES((i * 10)+4, TO_CHAR('data_' || i));



    INSERT INTO test_table(no,data)

    VALUES((i * 10)+5, TO_CHAR('data_' || i));



    INSERT INTO test_table(no,data)

    VALUES((i * 10)+6, TO_CHAR('data_' || i));



    INSERT INTO test_table(no,data)

    VALUES((i * 10)+7, TO_CHAR('data_' || i));



    INSERT INTO test_table(no,data)

    VALUES((i * 10)+8, TO_CHAR('data_' || i));



    INSERT INTO test_table(no,data)

    VALUES((i * 10)+9, TO_CHAR('data_' || i));

  END LOOP;

END;

/

リスト9　サンプル表にデータを挿入

図4　データ分布・量に偏りがある表

　この表は、11から10009までは1つずつデータが増分で入っているためデータの密度は高く、以降から100000までは100ずつの増分でデータの密度が低いと考えられます。この表にANALYZEコマンドを実行し、データ10件を取得する検索を行います。

SQL> ANALYZE TABLE test_table COMPUTE STATISTICS;

SQL> SELECT * FROM test_table WHERE no >=11 AND no <=21;

リスト10　ANALYZEコマンドを実行後、密度の高い範囲のデータを検索

図5　インデックス・スキャンが採用された（クリックで拡大します）

　「INDEX (RANGE SCAN) OF 'IDX_TEST_TABLE' (NON-UNIQUE) (Cost=2 Card=2)」と表示され、データ密度の高い範囲の検索では、索引が使用されたことが分かります。次は、データ密度の低い範囲を検索してみます。

SQL> SELECT * FROM test_table 

⇒WHERE no >=99001 AND no <=100000;

リスト11　密度の低い範囲のデータを検索
（注：⇒記号は表示の都合で折り返していることを表します）

図6　フル・スキャンが採用された（クリックで拡大します）

　同じ10件のデータを取得しているにもかかわらず、フル・スキャンを採用していることが「TABLE ACCESS (FULL) OF 'TEST_TABLE'(Cost=3 Card=100 Bytes=1200)」から分かります。索引を使用するよりも、対象範囲の表データを直接取得した方が検索にかかるコストが低いと判断されたということです。このようにコストベースを採用したオプティマイザは、統計情報によりデータベース内のデータの傾向も判断し、どちらの検索コストが低いかを判断していることが分かります。

オプティマイザの判断ミスを発見するオプティマイザの判断ミスを発見する

　ただし、ここでのコストは単純に取得データ全体にたどり着くまでのアクセスパスを判断しているだけで、パフォーマンスやサーバに対する負荷まで考慮していません。リスト11のSQL文にヒント文^注1 を使って、強制的に索引を使用させる検索を行ってみましょう。

注1：ヒント文

SQL文に直接使用したいインデックスを記述し、オプティマイザに指示を送るコメント。コメントアウト文で記述する。オプティマイザのルールよりも優先されるため、インデックスを使った検索が明らかに有効なときに使用すると、パフォーマンスやサーバの処理負荷軽減につながる。ただしANALYZEを正しく行い、更新が頻繁でデータの偏りが大きいデータベースに通常業務で使用すると、ANALYZEの効果が薄れるため十分注意する。

　リスト12のSQL文では「/*+ INDEX(テーブル名インデックス名) */」がヒント文です。

SQL> SELECT　/*+ INDEX(test_table idx_test_table) */ * 

⇒FROM test_table WHERE no >=99001 AND no <=100000;

リスト12　ヒント文を追加した検索
（注：⇒記号は表示の都合で折り返していることを表します）

図7　ヒント文を使用してインデックス・スキャンを強制した（クリックで拡大します）

　ヒント文を使用したことで、索引を使用したテーブルのスキャン「TABLE ACCESS (BY INDEX ROWID) OF 'TEST_TABLE'(Cost=5 Card=101 Bytes=1212)」と索引の範囲検索「INDEX (RANGE SCAN) OF 'IDX_TEST_TABLE'(NON-UNIQUE) (Cost=2 Card=101)」が行われています。図6のフル・スキャンでは「Cost」パラメータが「3」だったのに対し、図7のインデックス・スキャンでは「Cost=5」になっています。つまり、オプティマイザはフル・スキャンの方が有効だと判断していた、ということになります。

　では、実際このオプティマイザの判断は正しかったのでしょうか。ユーザーが期待するようにレスポンスが速く、なおかつサーバに対する負荷は本当に軽く済んだのでしょうか。実はこの検索はリスト11のフル・スキャンよりも、リスト12のインデックス・スキャンの方がサーバに対する負荷は低いと考えられます。

　図6と図7の画面下部に表示されている「統計」の「consistent gets」というパラメータは、「SELECT文でアクセスしたバッファキャッシュのブロック数」です。つまりメモリ上に展開されたテーブルデータを見つけるまでにアクセスしたメモリブロック数です。リスト11のSQL文ではこの「consistentgets」は「32」であり、ヒント文を使用したリスト12では「5」でした。つまり、コストをベースにした判断では有効であると判断されたリスト11は、実際にはより多くの処理（メモリブロックのアクセス）を行っていたのです。

　もちろんオプティマイザが正しい判断をする場合もありますが、このような逆効果になることも十分あり得るわけです。これもパフォーマンス障害時の問題切り分けに有効な判断材料の1つとなります。

　今回は、サーバがANALYZEコマンドで得られる統計情報をどのように判断して実行計画を作成しているかを確認しました。障害発生時は、SQL文だけでなくOracle側の設定に起因する問題もあることを知ることが重要です。次回はメモリ（データベース・バッファキャッシュ、共有プールなど）に関連した問題の解決方法を解説します。（次回に続く）

「次回」へ

「Oracleパフォーマンス障害の克服」バックナンバー