![[PukiWiki]](image/pukiwiki.png "[PukiWiki]")
** Oracle - 統計情報を取得する事の有効性について [#m2086f50] #setlinebreak(on); オプティマイザモード:CHOOSEで運用中の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 -- 結果４ 索引が使用されるようになった。 統計情報を更新した為、オプティマイザがデータの増加を認識できた様子。
