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

Oracleパフォーマンス障害の克服(4):オプティマイザの判断ミスを疑ってみよう (2/3)

[高橋潤,@IT]

問題解決! オプティマイザの挙動を正確に理解する

サンプル表の作成

 それでは統計情報がどのように実行計画に影響を与えるか、確認していきます。まずリスト5のサンプル表と索引を作成します。以降、オプティマイザのモードは「CHOOSE」であるとします。

表:
CREATE TABLE test_table(no number, data VARCHAR2(20));

データ: テストデータを1000件挿入
BEGIN
  FOR i IN 1..1000 LOOP
    INSERT INTO test_table(no,data)
    VALUES((i * 100), TO_CHAR('data_' || (i * 100)));
  END LOOP;
END;
/

索引:
CREATE INDEX idx_test_table ON test_table(no);
リスト5 サンプル表と索引の作成

統計情報の有無による実行計画の違い

 まずは統計情報を取得せずに、AUTOTRACEを実行し実行計画を確認してみます。

SQL> SET AUTOTRACE ON;
SQL> SELECT * FROM test_table WHERE no >= 1 AND no <= 1000;
リスト6 AUTOTRACEを実行し実行計画を確認

図1 統計情報を取得する前の実行計画 図1 統計情報を取得する前の実行計画(クリックで拡大します)

 「INDEX (RANGE SCAN) OF 'IDX_TEST_TABLE' (NON-UNIQUE)」と表示され、インデックス・スキャンが行われたことが分かります。オプティマイザ・モードが「CHOOSE」であるため、統計情報の存在しないテーブルはルールベースを採用した検索となります。

 次に、test_tableとidx_test_tableにANALYZEコマンドで統計情報を作成し、同じSQLを発行します。

SQL> ANALYZE TABLE test_table COMPUTE STATISTICS;
SQL> ANALYZE INDEX idx_test_table COMPUTE STATISTICS;
SQL> SELECT * FROM test_table WHERE no >= 10 AND no <=10000;
リスト7 ANALYZEコマンドを実行し、AUTOTRACEで実行計画を確認

図2 統計情報を取得した後の実行計画 図2 統計情報を取得した後の実行計画(クリックで拡大します)

 「TABLE ACCESS (FULL) OF 'TEST_TABLE' (Cost=2 Card=100 Bytes=1300)」と表示されました。オプティマイザ・モードが「CHOOSE」で統計情報が存在するため、コストベースを採用した検索となり、ここではフル・スキャンが行われました。

実行計画の検証とオプティマイザの挙動

 索引が存在するのに、なぜインデックス・スキャンが採用されないのでしょうか。コストベースでは、テーブル全体の構成を統計情報から取得し、NO列の10から10000までのテーブルデータを取得する際に、索引を使用して検索するよりも、表データを1行目から順に見ていき、10より大きいデータを採用する方がコスト的に有効であると判断したからです。

 以下のSQL文では1000行のデータから1行を取得するので索引を使用した方が有効と判断され、「INDEX (RANGE SCAN) OF 'IDX_TEST_TABLE'(NON-UNIQUE) (Cost=1 Card=1)」の表示からインデックス・スキャンが採用されたことが分かります。

SQL> SELECT * FROM test_table WHERE no =10000;
リスト8 1件のデータを検索

図3 インデックス・スキャンが採用された 図3 インデックス・スキャンが採用された(クリックで拡大します)

次ページに続く)

Copyright © ITmedia, Inc. All Rights Reserved.

RSSについて

アイティメディアIDについて

メールマガジン登録

@ITのメールマガジンは、 もちろん、すべて無料です。ぜひメールマガジンをご購読ください。