Oracle - 統計情報を取得する事の有効性について - 闘うITエンジニアの覚え書き

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

• 検証
  • 検証１ - 統計情報がない状態で、索引列を条件に指定して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
    
    -- 結果１
    統計情報はないがルールベースとして動作している為、索引が使われている。
    コストは計算されていない。

• 検証２ - 統計情報がある状態で、索引列を条件に指定して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
  
  -- 結果２
  テーブルで使用しているブロック数が少ない為か（この時点で4ブロック使用）全表走査の方が低コストと判断されている様子。
  データを少しずつ増やして検証した結果、使用ブロック数が大体10ブロックを超えた辺りから検索コストが高いと判断し、索引を使用する模様。
  ※初期化パラメータ : db_file_multiblock_read_count の値も関係しているかもしれない。(この時は16)
  ※但し、逆に使用ブロックが1ブロックのみの場合は、索引を使用する様子。
  　（使用ブロック数は、 select table_name, blocks from user_tables で確認。）

• 検証３ - さらにデータを増やすが、統計情報は古いままで、索引列を条件に指定して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
  
  -- 結果３
  統計情報を更新していない為、相変わらず全表走査している。

• 検証４ - 統計情報を更新して、索引列を条件に指定して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
  
  -- 結果４
  索引が使用されるようになった。
  統計情報を更新した為、オプティマイザがデータの増加を認識できた様子。