Oracle - 統計情報を取得する事の有効性について

オプティマイザモード:CHOOSEで運用中のOracle9iにおいて、統計情報を取得する事の有効性を検証する。

  • 検証の準備
    • テーブル及び索引の作成
      CREATE TABLE test_table(no number, data VARCHAR2(20));
      CREATE INDEX idx_test_table ON test_table(no);
  • テストデータの作成(1000件)
    BEGIN
      FOR i IN 1..1000 LOOP
        INSERT INTO test_table(no,data) VALUES((i*10), TO_CHAR('data_' || (i*10)));
      END LOOP;
      COMMIT;
    END;
    /
  • SQL*PlusのAutoTrace機能をONにする
    SET AUTOTRACE TRACEONLY
  • 検証
    • 検証1 - 統計情報がない状態で、索引列を条件に指定して1000件中の10件を取得する
      SELECT * FROM test_table WHERE no >= 900 AND no < 999;
      
      10行が選択されました。
      
      実行計画
      ----------------------------------------------------------
         0      SELECT STATEMENT Optimizer=CHOOSE
         1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST_TABLE'
         2    1     INDEX (RANGE SCAN) OF 'IDX_TEST_TABLE' (NON-UNIQUE)
      
      統計
      ----------------------------------------------------------
                0  recursive calls
                0  db block gets
                5  consistent gets
                0  physical reads
                0  redo size
              634  bytes sent via SQL*Net to client
              503  bytes received via SQL*Net from client
                2  SQL*Net roundtrips to/from client
                0  sorts (memory)
                0  sorts (disk)
               10  rows processed
      
      -- 結果1
      統計情報はないがルールベースとして動作している為、索引が使われている。
      コストは計算されていない。
  • 検証2 - 統計情報がある状態で、索引列を条件に指定して1000件中の10件を取得する
    -- 統計情報の取得
    BEGIN
      DBMS_STATS.GATHER_TABLE_STATS(
        OWNNAME       => 'DAISUKE'
        ,TABNAME      => 'TEST_TABLE'
        ,METHOD_OPT   => 'FOR ALL INDEXED'
        ,CASCADE      => TRUE
      );
    END;
    /
    
    -- 索引列を条件に指定して1000件中の10件を取得
    SELECT * FROM test_table WHERE no >= 900 AND no < 999;
    
    10行が選択されました。
    
    実行計画
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=9 Bytes=144)
       1    0   TABLE ACCESS (FULL) OF 'TEST_TABLE' (Cost=2 Card=9 Bytes=144)
    
    統計
    ----------------------------------------------------------
             22  recursive calls
              0  db block gets
              9  consistent gets
              0  physical reads
              0  redo size
            634  bytes sent via SQL*Net to client
            503  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
             10  rows processed
    
    -- 結果2
    テーブルで使用しているブロック数が少ない為か(この時点で4ブロック使用)全表走査の方が低コストと判断されている様子。
    データを少しずつ増やして検証した結果、使用ブロック数が大体10ブロックを超えた辺りから検索コストが高いと判断し、索引を使用する模様。
    ※初期化パラメータ : db_file_multiblock_read_count の値も関係しているかもしれない。(この時は16)
    ※但し、逆に使用ブロックが1ブロックのみの場合は、索引を使用する様子。
     (使用ブロック数は、 select table_name, blocks from user_tables で確認。)
  • 検証3 - さらにデータを増やすが、統計情報は古いままで、索引列を条件に指定して10000件中の10件を取得する
    -- データを10000件に増やす
    BEGIN
      FOR i IN 1001..10000 LOOP
        INSERT INTO test_table(no,data) VALUES((i*10), TO_CHAR('data_' || (i*10)));
      END LOOP;
      COMMIT;
    END;
    /
    
    -- 古い統計情報のままで、索引列を条件に指定して10000件中の10件を取得する
    SELECT * FROM test_table WHERE no >= 900 AND no < 999;
    
    10行が選択されました。
    
    実行計画
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=9 Bytes=144)
       1    0   TABLE ACCESS (FULL) OF 'TEST_TABLE' (Cost=2 Card=9 Bytes=144)
    
    統計
    ----------------------------------------------------------
              2  recursive calls
              0  db block gets
             46  consistent gets
              0  physical reads
              0  redo size
            634  bytes sent via SQL*Net to client
            503  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
             10  rows processed
    
    -- 結果3
    統計情報を更新していない為、相変わらず全表走査している。
  • 検証4 - 統計情報を更新して、索引列を条件に指定して10000件中の10件を取得する
    -- 統計情報の取得
    BEGIN
      DBMS_STATS.GATHER_TABLE_STATS(
        OWNNAME       => 'DAISUKE'
        ,TABNAME      => 'TEST_TABLE'
        ,METHOD_OPT   => 'FOR ALL INDEXED'
        ,CASCADE      => TRUE
      );
    END;
    /
    
    -- 最新の統計情報で、索引列を条件に指定して10000件中の10件を取得する
    SELECT * FROM test_table WHERE no >= 900 AND no < 999;
    
    10行が選択されました。
    
    実行計画
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=10 Bytes=170)
       1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST_TABLE' (Cost=3 Card=10 Bytes=170)
       2    1     INDEX (RANGE SCAN) OF 'IDX_TEST_TABLE' (NON-UNIQUE) (Cost=2 Card=10)
    
    統計
    ----------------------------------------------------------
              0  recursive calls
              0  db block gets
              5  consistent gets
              0  physical reads
              0  redo size
            634  bytes sent via SQL*Net to client
            503  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
              0  sorts (memory)
              0  sorts (disk)
             10  rows processed
    
    -- 結果4
    索引が使用されるようになった。
    統計情報を更新した為、オプティマイザがデータの増加を認識できた様子。

トップ   差分 バックアップ リロード   一覧 単語検索 最終更新   ヘルプ   最終更新のRSS
Last-modified: 2009-06-11 (木) 00:48:35 (4351d)