Oracle：統計情報の取得と移行

よく使う統計情報操作に関するコマンドやSQLのまとめ。
大抵はこれで事足りるはず。細かい設定がしたければ、マニュアルをみる。

よく使うOracle SQL/コマンドまとめ

Oracle DBの統計情報（オプティマイズ統計）とは
　Oracle DBは、SQLを実行する際に、実行計画を複数作成し、最もコストの低いデータアクセス方法でSQLを実行する。この実行計画を作成するためには、各テーブル、カラムのデータ量、データの特性、偏りなどの情報が必要である。これをDBの統計情報という。
　DBの統計情報は、ANALYZE文（非推奨）、DBMS_STATSパッケージ（Oracle推奨）、その他アプリケーション特有の方法で取得する。
　Oracle 10g以降は、DB作成時にデフォルトで自動的に収集する設定となっている（22時～）。自動収集の停止、時間の変更も可能（別記事で後ほどUpdate予定）。

実行計画
　実行計画は、前述の統計情報を基に自動的に作成される。実行計画はSQL文毎に作成され、共有プールというメモリ領域に格納される。これは、同じSQL文が再度実行された場合、毎回実行計画を作るのではなく、共有プールにある実行計画を再利用するためである。
（実行計画を作るという処理はかなりコストがかかる。）
　同じSQL文とは、大文字、小文字の区別、空白（全角、半角）、改行など、ありとあらゆるものが一致しているSQL文のことである。WHERE句の値のみが異なるSQLを大量に実行するOLTPシステムで、バインド変数を使用することが推奨されるのはこのためである。

統計情報取得（テーブル単位）

EXECUTE DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'オーナー名', TABNAME=>'テーブル名');

※デフォルトでは、自動的に関連するINDEXの統計情報についても取得する。サンプルサイズはOracleが自動的に最適な値を計算し、短時間で収集が完了するようになっている（10g以降）。

統計情報取得（スキーマ単位）

EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME=>'オーナー名',
OPTIONS=>'GATHER AUTO');

※OPTIONSのデフォルトである「GATHER」の場合、指定したオーナーが持つ全オブジェクト（テーブル、インデックス）が統計情報収集の対象となるため、時間がかかる。GATHER AUTOにすることにより、Oracleが自動的に統計情報の更新が必要と判断したもののみの情報を収集するため、短時間で完了する上、その精度もバージョンが上がるにつれて良くなっているため、GATHER AUTOでの実行で特に問題はない。

統計情報取得（スキーマ単位）

EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME=>'オーナー名',
OPTIONS=>'GATHER AUTO');

EXECUTE DBMS_STATS.GATHER_SYSTEM_STATS;

※本番稼働前やサーバーのI/O性能に変更があった場合に取得するとよい。

統計情報取得（データ・ディクショナリ統計情報）

EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

※本番稼働前や大量のデータ・ディクショナリ情報の更新があった場合に取得するとよい。例えば、大量のDBユーザー作成や権限の変更などがこれに当たる。

統計情報の移行概要
　統計情報は以下の条件を満たす場合、他のDBへ移行することが可能である。
   ・移行元のテーブルオーナーと移行先のテーブルオーナーが同じであること
   ・移行元のテーブル構造と移行先のテーブル構造が同じであること
   統計情報をexportコマンド(empまたはexpdp）でDumpファイルとして取得し、このファイルを他のDBが存在するサーバに伝送後、importコマンド (impまたはimpdp）でインポートする。ただし、統計情報を直接対象テーブルから取得したり、直接移行することはできず、別テーブル（以下、ワークテーブルと記述）を経由する必要がある。

統計情報の移行手順
①　＜移行元＞ワークテーブルを作成する

EXECUTE DBMS_STATS.CREATE_STAT_TABLE(OWNNAME=>'オーナー名', STATTAB=>'STAT_TABLE', TBLSPACE=>'表領域名');

②　＜移行元＞実テーブルからワークテーブルへ、統計情報をExport

（スキーマ単位で移行する場合）
EXECUTE DBMS_STATS.EXPORT_SCHEMA_STATS(OWNNAME=>'オーナー名', STATTAB=>'STAT_TABLE', STATID=>NULL, STATOWN=>'オーナー名');

（テーブル単位で移行する場合）
EXECUTE DBMS_STATS.EXPORT_TABLE_STATS(OWNNAME=>'オーナー名', TABNAME=>'テーブル名', STATTAB=>'STAT_TABLE', CASCADE=>TRUE);

※テーブル単位で複数テーブルを対象にする場合、その数分だけコマンドを実行する必要がある。

③　＜移行元＞ワークテーブルをExport（expまたはexpdpを使用する）

④　③で取得したDumpファイルを移行先サーバへ伝送（FTP等で転送する）

⑤　＜移行先＞④で取得したDumpファイルをImport(impまたはimpdpを使用する）

⑥　＜移行先＞ワークテーブルから実テーブルへ、統計情報をImport

（スキーマ単位で移行する場合）
EXECUTE DBMS_STATS.IMPORT_SCHEMA_STATS(OWNNAME=>'オーナー名＞', STATTAB=>'STAT_TABLE', STATID=>NULL, STATOWN=>'オーナー名');

（テーブル単位で移行する場合）
EXECUTE DBMS_STATS.IMPORT_TABLE_STATS(OWNNAME=>'オーナー名', TABNAME=>'テーブル名', STATTAB=>'STAT_TABLE', CASCADE=>TRUE);

※テーブル単位で複数テーブルを対象にする場合、その数分だけコマンドを実行する必要がある。

⑦　＜移行元、移行先＞移行が完了したら、ワークテーブルを削除

EXECUTE DBMS_STATS.DROP_STAT_TABLE(OWNNAME=>'オーナー名', STATTAB=>'STAT_TABLE');

よく使うOracle SQL/コマンドまとめ

けしくんのWebLog

自分が考えたこと、調べたことを忘れずに残しておくため、Web上にLogを残していきます。

Oracle：統計情報の取得と移行