はじめに
本番（プロダクション）環境と開発環境では、実行計画が同じになるようにDBのデータ量とデータの分布を揃えておきたいです。実行結果が同じにならないと開発環境では性能の問題がでなかったが、本番環境に持っていくとSQLが遅いという問題が発生してしまうことがあります。
とは言え、本番環境と開発環境で同じデータを揃えるのはストレージが足らないなどの理由で難しいことが多いです。そのため、本番環境の統計を開発環境へ移行することがよく行われます。
今回はその場合の移行手順を確認してみます。
なお、Oracleは12cR2(12.2)を利用しています。12c(12.1)でも同様です。（11gではやったことがないから知らないがたぶん同じ）
準備
まずは準備作業としてテーブルを作成し、テストデータを投入します。
create table stats_test (id number, cdate timestamp, contents varchar2(30));
insert into stats_test select rownum, to_date('2019-01-01', 'yyyy-mm-dd'), 'TEST DATA1234567890123456789' from (select level from DUAL connect by level <= 100);
commit;
次にgather_schema_statsプロシージャで統計情報を取得します。以下はスキーマに対して実行していますが、テーブルを直接指定することもできます。
exec dbms_stats.gather_schema_stats(ownname=>'TEST01');
統計が取得されたことを確認するために、dba_tab_statisticsを検索してみます。
作成したSTATS_TESTテーブルのLAST_ANALYZEDを見ると、gather_schema_statsプロシージャで統計情報を取得した日時になっています。
set linesize 300;
column table_name format a20;
column object_type format a20;
column last_analyzed format a20;
select table_name, object_type, num_rows, to_char(last_analyzed, 'yyyy-mm-dd hh24:mi:ss') as LAST_ANALYZED, stale_stats from dba_tab_statistics where owner = 'TEST01' and table_name = 'STATS_TEST';
TABLE_NAME OBJECT_TYPE NUM_ROWS LAST_ANALYZED STA
-------------------- -------------------- ---------- -------------------- ---
STATS_TEST TABLE 100 2019-11-19 11:59:39 NO
オプティマイザ統計の移行
準備が終わったので、オプティマイザ統計の移行を実施してみます。
移行の作業は以下の順番で実行します。
- （移行元）ステージング表を作成する
- （移行元）統計情報をステージング表にエクスポートする
- （移行元）expdpコマンドでステージング表をダンプする
- （移行元/移行先）ダンプファイルを移行先へコピーします（FTPなど）
- （移行先）impdpコマンドでステージング表をリストアする
- （移行先）統計情報をインポートする
（移行元）ステージング表を作成する
ステージング表は統計情報をエクスポートする先のテーブルです。
まずはCREATE_STAT_TABLEプロシージャを使用してステージング表を作成します。
CREATE_STAT_TABLEプロシージャはテーブルが対象ですが、データベース、スキーマ単位でもエクスポートできます。
プロシージャのオプションは以下のとおりです。
- ownname: スキーマ名
- stattab: 作成するステージング表の名前
- tblspace: ステージング表を作成する表領域
以下は実行例です。
exec dbms_stats.create_stat_table('test01', 'EXP_STATS_TEST', 'USERS');
（移行元）統計情報をステージング表にエクスポートする
EXPORT_SCHEMA_STATSプロシージャを使用して統計情報をステージング表にエクスポートします。
プロシージャのオプションは以下のとおりです。
- ownname: スキーマ名
- stattab: 統計のエクスポート先のステージング表の名前
以下は実行例です。
exec dbms_stats.export_schema_stats('test01', stattab =>'EXP_STATS_TEST');
（移行元）expdpコマンドでステージング表をダンプする
（書くまでもないので）省略
（移行元/移行先）ダンプファイルを移行先へコピーします（FTPなど）
（書くまでもないので）省略
（移行先）impdpコマンドでステージング表をリストアする
（書くまでもないので）省略
（移行先）統計情報をインポートする
移行先のステージング表の統計情報をインポートします。
プロシージャのオプションは以下のとおりです。
- ownname: スキーマ名
- stattab: 統計のインポート先のステージング表の名前
- no_invalidate: TRUEに設定すると即時にカーソルが無効化されます。無効化されるとSQLの次回実行時に実行計画が作成されます。開発環境に持っていくならTRUEで良いでしょう。
- force: TRUEに設定するとロックされていても統計情報を上書きします。これもTRUEで良い。
- stat_category: デフォルト（'OBJECT_STATS'）では表、列、インデックスの統計情報をインポートします。増分統計も含める場合は'OBJECT_STATS,SYNOPSES'を指定します。開発環境にインポートして統計情報を固定するなら、デフォルトで良いと思います。SYNOPSESは増分統計のためなので、統計を取得しないなら不要ですので。
以下は実行例です。
exec dbms_stats.import_schema_stats(ownname=>'test01', stattab=>'EXP_STATS_TEST', statid=>NULL, statown=>'test01');
※no_invalidate, forceを指定し忘れていますが。
統計情報を検索すると以下のように取り込まれていることが分かります。
select table_name, object_type, num_rows, to_char(last_analyzed, 'yyyy-mm-dd hh24:mi:ss') as LAST_ANALYZED, stale_stats from dba_tab_statistics where owner = 'TEST01' and table_name = 'STATS_TEST';
TABLE_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE NUM_ROWS LAST_ANALYZED STA
------------ ---------- ------------------- ---
STATS_TEST
TABLE 100 2019-11-19 11:59:39 NO
統計のロックとステージング表の削除
せっかくインポートした統計情報なので、勝手に更新されないようにロックしておきます。もしくは自動統計取得を停止させます。
exec dbms_stats.lock_table_stats(ownname=>'test01',tabname=>'STATS_TEST');
最後にインポート後はステージング表は不要なので削除します。
exec dbms_stats.drop_stat_table('test01', 'EXP_STATS_TEST');