2017年5月 7日 (日)

dbms_stats.export_[schema|table]_stats/dbms_import_[schema|table]_stats : 索引名の変化に追随できるようになった?のか?

Oracle Database PL/SQLパッケージおよびタイプ・リファレンス 12c リリース2 (12.2) DBMS_STATSサブプログラムの要約
Oracle Databaseユーティリティ 12c リリース2 (12.2) 3 データ・ポンプ・インポート



dbms_stats.export_[schema|table]_stats/import_[schema|table]_statsを利用してオプティマイザ統計を移植する際、
一意制約や主キー制制約作成でシステム生成の索引名を持つ索引へオプティマイザ統計をインポートできない。(しかもエラーもワーニングも表示されない)
この問題、私が遭遇したのは11g R1のころだったと記憶している(詳しく調べたわけではないので、11g R1で初めて発覚した問題なのかそれ以前からあった問題なのか不明です)が、
オプティマイザ統計をdbms_stats.export_[schema|table]_stats/import_[schema|table]_statsを利用して、
以下のような内部的に付与される索引名を持つ索引へオプティマイザ統計を移植するのはたーーーいへん。というお話です。

主キー制約の例ですが、制約に名称を付与せず名称をOracle任せにする記述方法と、制約名と同じ名称で関連する索引を作成する記述方法があります。(事前に索引を作成しておく方法もありますが今回はあまり関係ないので省略)

例1

SCOTT> create table hoge
2 (
3 id number primary key
4 ,foo number
5 );

表が作成されました。


例2

SCOTT> create table hoge2
2 (
3 id number not null
4 ,foo number
5 ,constraint pk_hoge2 primary key(id) using index
6 );

表が作成されました。

例1の主キー制約で作成された制約及び索引は以下のようにSYS_Cnnnnnnという形式でOracleが名称を生成します!
data pumpによりインポートややDDLを再利用して他のスキーマやデータベースへ複製すると、都度、名称が異なることを意味します。それが問題の根っこ。

SCOTT> select table_name,index_name,num_rows,last_analyzed from user_ind_statistics where table_name like 'HOGE%';

TABLE_NAME INDEX_NAME NUM_ROWS LAST_ANALYZED
------------------------------ ------------------------------ ---------- -------------------
HOGE SYS_C009918
HOGE2 PK_HOGE2


Data Pumpやdbms_stats.export_[schema|table]_stats/dbms_import_[schema|table]_statsを利用したエクスポート/インポートで、元と先の名称の違いを解決できないとオプティマイザ統計がインポートできないという事態になります。
そこで、エクスポートした索引名とインポート先の索引名が異なっていてもオプティマイザ統計を正しくインポートする為には、元と先の名称をマッピングする機能の登場となるわけです。

マニュアルを読むと、Data Pumpにもdbms_stats.export_[schema|table]_stats/import_[schema|table]_statsにも、マッピングに関する記述があるので、その点は意識されている仕様になっている、ように見えます。
が、マッピングに関する部分を読んでも、Data Pumpとdbms_stats.export_[schema|table]_stats/import_[schema|table]_statsに同様のマッピング機能があるようには見えないんですよ。これがw。そうなれば試してガッテンw 差異を以下にまとめました!

知ってる方々にはFAQ(多分、ハマった経験からか?w)なのですが、意外に知られてないようなので忘れないうちに書いておきます:)
なお、12cになってから少々動きが変わったのでその点にも注意が必要でっす!


11.1.0.7 → 11.1.0.7 : NG (dbms_stats.upgrade_stat_tableプロシージャでのSTATTABのアップグレード不要)
11.1.0.7 → 11.2.0.1 : NG (同上)
11.1.0.7 → 12.1.0.2 : NG (dbms_stats.upgrade_stat_tableプロシージャでのSTATTABのアップグレード必要)
11.1.0.7 → 12.2.0.1 : NG (同上)

11.2.0.1 → 11.2.0.1 : NG (dbms_stats.upgrade_stat_tableプロシージャでのSTATTABのアップグレード不要)
11.2.0.1 → 12.1.0.2 : NG (dbms_stats.upgrade_stat_tableプロシージャでのSTATTABのアップグレード必要)
11.2.0.1 → 12.2.0.1 : NG (同上)

12.1.0.2 → 12.1.0.2 : OK (dbms_stats.upgrade_stat_tableプロシージャでのSTATTABのアップグレード不要)
12.1.0.2 → 12.2.0.1 : OK (dbms_stats.upgrade_stat_tableプロシージャでのSTATTABのアップグレード必要)

12.2.0.1 → 12.2.0.1 : OK (dbms_stats.upgrade_stat_tableプロシージャでのSTATTABのアップグレード不要)


ちなみに、Data Pumpによるexport/importでは索引名の変化には追随しているようなので、dbms_statsを利用した移植のみ注意が必要です。
Data Pumpを利用したオプティマイザ統計の移植ではdbms_stats利用時と同パターンであっても以下の通りの違いがあります。(とは言っても簡単な確認だけなので落とし穴がないとはいえませんけど)

索引名をOracleの自動付与にしないのが一番の対策だと思います。


11.1.0.7 → 11.1.0.7 : OK
11.1.0.7 → 11.2.0.1 : OK
11.1.0.7 → 12.1.0.2 : OK
11.1.0.7 → 12.2.0.1 : OK

11.2.0.1 → 11.2.0.1 : OK
11.2.0.1 → 12.1.0.2 : OK
11.2.0.1 → 12.2.0.1 : OK

12.1.0.2 → 12.1.0.2 : OK
12.1.0.2 → 12.2.0.1 : OK

12.2.0.1 → 12.2.0.1 : OK

結果だけ見ても、どういうことなのかピンと来ていない方向けに典型的な例を2つ載せておきます。




src:11.2 / dest:11.2 (STATTABのアップグレード不要)のケースで名称が変わった索引へ統計がインポートできない状況の例
SCOTT> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

STATTABの作成
以下、11.1~12.2共通の準備

SCOTT> exec dbms_stats.create_stat_table(ownname=>'SCOTT',stattab=>'STATTAB');

PL/SQLプロシージャが正常に完了しました。

SCOTT> create table hoge
2 (
3 id number primary key
4 ,foo number
5 );

表が作成されました。

SCOTT> create table hoge2
2 (
3 id number not null
4 ,foo number
5 ,constraint pk_hoge2 primary key(id) using index
6 );

表が作成されました。

SCOTT> select table_name,index_name from user_ind_statistics where table_name like 'HOGE%';

TABLE_NAME INDEX_NAME
------------------------------ ------------------------------
HOGE SYS_C0013274
HOGE2 PK_HOGE2

SCOTT> alter session set nls_date_format='yyyy/mm/dd hh24:mi:ss';

セッションが変更されました。

SCOTT> select table_name,index_name,num_rows,last_analyzed from user_ind_statistics where table_name like 'HOGE%';

TABLE_NAME INDEX_NAME NUM_ROWS LAST_ANALYZED
------------------------------ ------------------------------ ---------- -------------------
HOGE SYS_C0013274
HOGE2 PK_HOGE2

SCOTT> select table_name,num_rows,last_analyzed from user_tab_statistics where table_name like 'HOGE%';

TABLE_NAME NUM_ROWS LAST_ANALYZED
------------------------------ ---------- -------------------
HOGE
HOGE2

SCOTT> begin for i in 1..100 loop insert into hoge values(i,i); end loop; commit; end;
2 /

PL/SQLプロシージャが正常に完了しました。

SCOTT> insert into hoge2 select * from hoge;

100行が作成されました。

SCOTT> commit;

コミットが完了しました。


データを登録したらオプティマイザ統計を取得して、STATTABへエクスポートしておきます。これで準備完了

SCOTT> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'HOGE',cascade=>true,no_invalidate=>false);

PL/SQLプロシージャが正常に完了しました。

SCOTT> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'HOGE2',cascade=>true,no_invalidate=>false);

PL/SQLプロシージャが正常に完了しました。

SCOTT> exec dbms_stats.export_table_stats(ownname=>'SCOTT',tabname=>'HOGE',cascade=>true,stattab=>'STATTAB');

PL/SQLプロシージャが正常に完了しました。

SCOTT> exec dbms_stats.export_table_stats(ownname=>'SCOTT',tabname=>'HOGE2',cascade=>true,stattab=>'STATTAB');

PL/SQLプロシージャが正常に完了しました。


オプティマイザ統計は100行ですが、実データは10000行に増量しておきます。理由は、インポートされた統計なのか再取得された統計か区別するためです。オプティマイザ統計が再取得されていれば、NUM_ROWS列は10000行となります。LAST_ANALYZED列を確認するより楽なので!:)

SCOTT> begin for i in 101..10000 loop insert into hoge values(i,i); end loop; commit; end;
2 /

PL/SQLプロシージャが正常に完了しました。

SCOTT> insert into hoge2 select * from hoge where id between 101 and 10000;

9900行が作成されました。

SCOTT> commit;

コミットが完了しました。


NUM_ROWSは100ですよね!(実データは10000ですが)

SCOTT> select table_name,index_name,num_rows,last_analyzed from user_ind_statistics where table_name like 'HOGE%';

TABLE_NAME INDEX_NAME NUM_ROWS LAST_ANALYZED
------------------------------ ------------------------------ ---------- -------------------
HOGE SYS_C0013274 100 2017/05/06 15:25:11
HOGE2 PK_HOGE2 100 2017/05/06 15:25:23

SCOTT> select table_name,num_rows,last_analyzed from user_tab_statistics where table_name like 'HOGE%';

TABLE_NAME NUM_ROWS LAST_ANALYZED
------------------------------ ---------- -------------------
HOGE 100 2017/05/06 15:25:11
HOGE2 100 2017/05/06 15:25:23

SCOTT> select count(1) from hoge;

COUNT(1)
----------
10000

SCOTT> select count(1) from hoge2;

COUNT(1)
----------
10000


前述の状態でhoge表とhoge2表及び、stattab表をエクスポートします。データは10000行ですが、STATABへエクスポートしたオプティマイザ統計のNUM_ROWSは100行です。

接続先: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
"SYSTEM"."SYS_EXPORT_TABLE_01"を起動しています: system/******** directory=workdir dumpfile=11gr2stats_exp.dmp logfile=11gr2stats_exp.log tables=scott.hoge,scott.hoge2,scott.stattab
BLOCKSメソッドを使用して見積り中です...
オブジェクト型TABLE_EXPORT/TABLE/TABLE_DATAの処理中です
BLOCKSメソッドを使用した見積り合計: 512 KB
オブジェクト型TABLE_EXPORT/TABLE/TABLEの処理中です
オブジェクト型TABLE_EXPORT/TABLE/INDEX/INDEXの処理中です
オブジェクト型TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINTの処理中です
オブジェクト型TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSの処理中です
オブジェクト型TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSの処理中です
. . "SCOTT"."HOGE" 122.1 KB 10000行がエクスポートされました
. . "SCOTT"."HOGE2" 122.1 KB 10000行がエクスポートされました
. . "SCOTT"."STATTAB" 15.49 KB 8行がエクスポートされました
マスター表"SYSTEM"."SYS_EXPORT_TABLE_01"は正常にロード/アンロードされました
******************************************************************************
SYSTEM.SYS_EXPORT_TABLE_01に設定されたダンプ・ファイルは次のとおりです:
/home/oracle/11gr2stats_exp.dmp
ジョブ"SYSTEM"."SYS_EXPORT_TABLE_01"が15:32:05で正常に完了しました

ここからが本番
11.2でエクスポートしたSCOTT.HOGE表、SCOTT.HOGE2表、SCOTT.STATTAB表を同じ11.2のSCOTT2へインポート
オプティマイザ統計のインポートは抑止して試験上のノイズは排除しておきます。索引統計は索引作成時に自動取得され10000行になりますが、表統計は空の状態となります。

接続先: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
マスター表"SYSTEM"."SYS_IMPORT_FULL_01"は正常にロード/アンロードされました
"SYSTEM"."SYS_IMPORT_FULL_01"を起動しています: system/******** directory=workdir dumpfile=11gr2stats_exp.dmp logfile=11gr2stats_imp.log remap_schema=scott:scott2 exclude=statistics
オブジェクト型TABLE_EXPORT/TABLE/TABLEの処理中です
オブジェクト型TABLE_EXPORT/TABLE/TABLE_DATAの処理中です
. . "SCOTT2"."HOGE" 122.1 KB 10000行がインポートされました
. . "SCOTT2"."HOGE2" 122.1 KB 10000行がインポートされました
. . "SCOTT2"."STATTAB" 15.49 KB 8行がインポートされました
オブジェクト型TABLE_EXPORT/TABLE/INDEX/INDEXの処理中です
オブジェクト型TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINTの処理中です
ジョブ"SYSTEM"."SYS_IMPORT_FULL_01"が15:35:21で正常に完了しました


インポートされたSTATTABの主要列は以下の通り、C5列にはエクスポート元のSCOTTそして、C1列の赤字部分には、エクスポート元の索引名(SYS_C0013274)が確認できます。

SCOTT2> select c1,c2,c3,c4,c5 from stattab;

C1 C2 C3 C4 C5
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------
HOGE SCOTT
SYS_C0013274 SCOTT
HOGE ID SCOTT
HOGE FOO SCOTT
HOGE2 SCOTT
PK_HOGE2 SCOTT
HOGE2 ID SCOTT
HOGE2 FOO SCOTT

8行が選択されました。


C5列にスキーマ名を持っているのでそのままではインポートできず、”SCOTT”を”SCOTT2”にupdateする必要あり!。
なお、12c以降はremapするためのdbms_stats.remap_stat_tableプロシージャを利用すること!

SCOTT2> update stattab set c5='SCOTT2';

8行が更新されました。

SCOTT2> commit;

コミットが完了しました。


C5がSCOTTからSCOTT2になりました。(C1の赤字部分、索引名(SYS_C0013274)は以前のままです。この名称を元にインポートするとするとなるとうまく行く気がしませんよね!w
インポート先の自動名称付与索引の名称は異なるわけですから(たまたま同一になることもありますが)

SCOTT2> select c1,c2,c3,c4,c5 from stattab;

C1 C2 C3 C4 C5
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------
HOGE SCOTT2
SYS_C0013274 SCOTT2
HOGE ID SCOTT2
HOGE FOO SCOTT2
HOGE2 SCOTT2
PK_HOGE2 SCOTT2
HOGE2 ID SCOTT2
HOGE2 FOO SCOTT2

import_table_statsプロシージャでSTATTABからオプティマイザ統計をインポート

SCOTT2> exec dbms_stats.import_table_stats(ownname=>'SCOTT2',tabname=>'HOGE',cascade=>true,stattab=>'STATTAB',no_invalidate=>false);

PL/SQLプロシージャが正常に完了しました。

SCOTT2> exec dbms_stats.import_table_stats(ownname=>'SCOTT2',tabname=>'HOGE2',cascade=>true,stattab=>'STATTAB',no_invalidate=>false);

PL/SQLプロシージャが正常に完了しました。


元SYS_C0013274だった索引ですが、インポート先ではSYS_C0013288と異なっており、(11gまでの)import_stat_tableプロシージャでは変化に追随できていないことがわかります。また、インポート時(dbms_stats.import_table_statsプロシージャ)、エラーは発生しないのでインポート時の問題に気づきにくいの点も問題の一つ! 注意してね

SCOTT2> select table_name,index_name,num_rows,last_analyzed from user_ind_statistics where table_name like 'HOGE%';

TABLE_NAME INDEX_NAME NUM_ROWS LAST_ANALYZED
------------------------------ ------------------------------ ---------- -------------------
HOGE SYS_C0013288 10000 2017/05/06 15:35:21
HOGE2 PK_HOGE2 100 2017/05/06 15:25:23

SCOTT2> select table_name,num_rows,last_analyzed from user_tab_statistics where table_name like 'HOGE%';

TABLE_NAME NUM_ROWS LAST_ANALYZED
------------------------------ ---------- -------------------
HOGE 100 2017/05/06 15:25:11
HOGE2 100 2017/05/06 15:25:23


import_table_statsプロシージャ実行後のSTATTABの内容を今一度確認。12c版ではちょっとした変化が起きるので、この部分よーく覚えておいてください!

SCOTT2> select c1,c2,c3,c4,c5 from stattab;

C1 C2 C3 C4 C5
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------
HOGE SCOTT2
SYS_C0013274 SCOTT2
HOGE ID SCOTT2
HOGE FOO SCOTT2
HOGE2 SCOTT2
PK_HOGE2 SCOTT2
HOGE2 ID SCOTT2
HOGE2 FOO SCOTT2



12.1から12.2へ移植する例。こちらは成功します。
12.1でエクスポートしたSCOTT.HOGE表、SCOTT.HOGE2表、SCOTT.STATTAB表を12.2のSCOTT2へインポートする例 (STATTABのアップグレード必須)


...略...

HOGE表、HOGE2表、及び、STATTAB表をData Pumpでエクスポートするところまでは、11.2と同じ。
違いは、12.1で行なっているという点のみなので前半略。
12.1.0.2からエクスポートしたHOGE表、HOGE2表、及びSTATTAB表を12.2のSCOTT2へインポートするところから。

...略...

BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0
PL/SQL Release 12.2.0.1.0 - Production 0
CORE 12.2.0.1.0 Production 0
TNS for Linux: Version 12.2.0.1.0 - Production 0
NLSRTL Version 12.2.0.1.0 - Production 0


11.2と同様にimpdp時に統計情報は除外するようにしています。 インポート直後のオプティマイザ統計の状態は、表統計は空、索引統計は再取得され10000行となります

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/********@orcl directory=workdir dumpfile=12c1stats_exp.dmp logfile=12c1stats_imp.log remap_schema=scott:scott2 exclude=statistics
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT2"."HOGE" 122.2 KB 10000 rows
. . imported "SCOTT2"."HOGE2" 122.2 KB 10000 rows
. . imported "SCOTT2"."STATTAB" 17.89 KB 8 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Sat May 6 07:21:19 2017 elapsed 0 00:00:10


インポートされたSTATTABの内容は以下の通り。12.1.0.2のSTATTABですが、11gまでとは多少違いがあります。

SQL> select c1,c2,c3,c4,c5,c6,cl1 from stattab;

C1 C2 C3 C4 C5 C6 CL1
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------
HOGE SCOTT
SYS_C0011035 HOGE SCOTT SCOTT 2.ID
HOGE ID SCOTT
HOGE FOO SCOTT
HOGE2 SCOTT
PK_HOGE2 HOGE2 SCOTT SCOTT 2.ID
HOGE2 ID SCOTT
HOGE2 FOO SCOTT

8 rows selected.


オプティマイザ統計はインポートしていませんが、索引作成時に自動収集されたため索引だけは統計がセットされています。(STATTABからインポートするとどうなるか要確認)

SQL> select table_name,index_name,num_rows,last_analyzed from user_ind_statistics where table_name like 'HOGE%';

TABLE_NAME INDEX_NAME NUM_ROWS LAST_ANALYZED
------------------------------ ------------------------------ ---------- -------------------
HOGE2 PK_HOGE2 10000 2017/05/06 07:21:19
HOGE SYS_C0011800 10000 2017/05/06 07:21:19

SQL> select table_name,num_rows,last_analyzed from user_tab_statistics where table_name like 'HOGE%';

TABLE_NAME NUM_ROWS LAST_ANALYZED
------------------------------ ---------- -------------------
HOGE2
HOGE


12.1と12.2間でもSTATTABに変更があるようで、アップグレードが必要です! アップグレードを忘れると以下のエラーが返されます!!

SQL> exec dbms_stats.remap_stat_table(ownname=>'SCOTT2',stattab=>'STATTAB',src_own=>'SCOTT',src_tab=>null,tgt_own=>'SCOTT2',tgt_tab=>null); 
BEGIN dbms_stats.remap_stat_table(ownname=>'SCOTT2',stattab=>'STATTAB',src_own=>'SCOTT',src_tab=>null,tgt_own=>'SCOTT2',tgt_tab=>null); END;

*
ERROR at line 1:
ORA-20002: Version of statistics table "SCOTT2"."STATTAB" is too old. Please try upgrading it with dbms_stats.upgrade_stat_table
ORA-06512: at "SYS.DBMS_STATS", line 12442
ORA-06512: at "SYS.DBMS_STATS", line 12465
ORA-06512: at "SYS.DBMS_STATS", line 3619
ORA-06512: at "SYS.DBMS_STATS", line 12319
ORA-06512: at line 1

SQL> exec dbms_stats.upgrade_stat_table(ownname=>'SCOTT2',stattab=>'STATTAB');

PL/SQL procedure successfully completed.


アップグレードごのSTATTAB主要部分には違いは見えないので、他の列で変更があるようですね。

SQL> select c1,c2,c3,c4,c5,c6,cl1 from stattab;

C1 C2 C3 C4 C5 C6 CL1
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------
HOGE SCOTT
SYS_C0011035 HOGE SCOTT SCOTT 2.ID
HOGE ID SCOTT
HOGE FOO SCOTT
HOGE2 SCOTT
PK_HOGE2 HOGE2 SCOTT SCOTT 2.ID
HOGE2 ID SCOTT
HOGE2 FOO SCOTT

8 rows selected.


SCOTTの所有するオブジェクトをSCOTT2へ移植するのでスキーマのリマップを行います。Data Pumpのremap_schemaみたいなもんです。
11gまではC5列を直接更新していましたが、12c以降、それはだめですよ!。スキーマ名を保持している列も増えているので、dbms_stats.remap_stat_tableを使ってスキーマをリマップ!!!

SQL> exec dbms_stats.remap_stat_table(ownname=>'SCOTT2',stattab=>'STATTAB',src_own=>'SCOTT',src_tab=>null,tgt_own=>'SCOTT2',tgt_tab=>null); 

PL/SQL procedure successfully completed.

SQL> select c1,c2,c3,c4,c5,c6,cl1 from stattab;

C1 C2 C3 C4 C5 C6 CL1
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------
HOGE SCOTT2
SYS_C0011035 HOGE SCOTT2 SCOTT2 2.ID
HOGE ID SCOTT2
HOGE FOO SCOTT2
HOGE2 SCOTT2
PK_HOGE2 HOGE2 SCOTT2 SCOTT2 2.ID
HOGE2 ID SCOTT2
HOGE2 FOO SCOTT2

8 rows selected.


STATTABからオプティマイザ統計を表と索引へインポートします。

SQL> exec dbms_stats.import_table_stats(ownname=>'SCOTT2',tabname=>'HOGE',cascade=>true,stattab=>'STATTAB',no_invalidate=>false);

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.import_table_stats(ownname=>'SCOTT2',tabname=>'HOGE2',cascade=>true,stattab=>'STATTAB',no_invalidate=>false);

PL/SQL procedure successfully completed.


おおおおおお〜っ。 12c同士であれば、索引名の変化に追随して統計がインポート可能に!。ただし、11gからの移植には未対応なので、ほかの問題がなければ、Data Pumpを使って統計を移植するのが王道かな。

SQL> select table_name,index_name,num_rows,last_analyzed from user_ind_statistics where table_name like 'HOGE%';

TABLE_NAME INDEX_NAME NUM_ROWS LAST_ANALYZED
------------------------------ ------------------------------ ---------- -------------------
HOGE2 PK_HOGE2 100 2017/05/06 15:57:43
HOGE SYS_C0011800 100 2017/05/06 15:57:39

SQL> select table_name,num_rows,last_analyzed from user_tab_statistics where table_name like 'HOGE%';

TABLE_NAME NUM_ROWS LAST_ANALYZED
------------------------------ ---------- -------------------
HOGE2 100 2017/05/06 15:57:43
HOGE 100 2017/05/06 15:57:39


面白い点がもう一つ、import_table_stats後のSTATTABのC1列が、SYS_C0011035からSYS_C0011800へ更新されています。SYS_C0011800はインポート先の索引名なので、インポート先の索引名でSTATTABが更新されたことを意味します。

SQL> select c1,c2,c3,c4,c5,c6,cl1 from stattab;

C1 C2 C3 C4 C5 C6 CL1
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------
HOGE SCOTT2
SYS_C0011800 HOGE SCOTT2 SCOTT2 2.ID
HOGE ID SCOTT2
HOGE FOO SCOTT2
HOGE2 SCOTT2
PK_HOGE2 HOGE2 SCOTT2 SCOTT2 2.ID
HOGE2 ID SCOTT2
HOGE2 FOO SCOTT2

8 rows selected.



ところで、Data Pumpで可能なことが、dbms_stats.import_[schema|table]_statsで対応できないのはなんでだろう? とは思わなくもない。(どんな大人の事情なんだろう...w)
ということで今日はこのへんで。


==== おまけ ===
あ、そうそう、Data Pumpで統計情報表と索引のインポートと同時行う例も載せておきますね。 11.1から12.2への例。

エクポート元(11.1)

SCOTT> select table_name,index_name,num_rows,last_analyzed from user_ind_statistics where table_name like 'HOGE%';

TABLE_NAME INDEX_NAME NUM_ROWS LAST_ANALYZED
------------------------------ ------------------------------ ---------- -------------------
HOGE SYS_C009918 100 2017/05/06 14:42:25
HOGE2 PK_HOGE2 100 2017/05/06 14:42:32

SCOTT> select table_name,num_rows,last_analyzed from user_tab_statistics where table_name like 'HOGE%';

TABLE_NAME NUM_ROWS LAST_ANALYZED
------------------------------ ---------- -------------------
HOGE 100 2017/05/06 14:42:25
HOGE2 100 2017/05/06 14:42:32

接続先: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
"SYSTEM"."SYS_EXPORT_TABLE_01"を起動しています: system/******** directory=workdir dumpfile=11gr1stats_exp.dmp logfile=11gr1stats_exp.log tables=scott.hoge,scott.hoge2
BLOCKSメソッドを使用して見積り中です...
オブジェクト型TABLE_EXPORT/TABLE/TABLE_DATAの処理中です
BLOCKSメソッドを使用した見積り合計: 384 KB
オブジェクト型TABLE_EXPORT/TABLE/TABLEの処理中です
オブジェクト型TABLE_EXPORT/TABLE/INDEX/INDEXの処理中です
オブジェクト型TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINTの処理中です
オブジェクト型TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSの処理中です
オブジェクト型TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSの処理中です
. . "SCOTT"."HOGE" 122.1 KB 10000行がエクスポートされました
. . "SCOTT"."HOGE2" 122.1 KB 10000行がエクスポートされました
マスター表"SYSTEM"."SYS_EXPORT_TABLE_01"は正常にロード/アンロードされました
******************************************************************************
SYSTEM.SYS_EXPORT_TABLE_01に設定されたダンプ・ファイルは次のとおりです:
/home/oracle/11gr1stats_exp.dmp
ジョブ"SYSTEM"."SYS_EXPORT_TABLE_01"が22:38:22で正常に完了しました


インポート先 (12.2)

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/********@orcl directory=workdir dumpfile=11gr1stats_exp.dmp logfile=11gr1stats_imp.log remap_schema=scott:scott2
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT2"."HOGE" 122.1 KB 10000 rows
. . imported "SCOTT2"."HOGE2" 122.1 KB 10000 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Sat May 6 12:57:48 2017 elapsed 0 00:00:32


SCOTT2> select table_name,index_name,num_rows,last_analyzed from user_ind_statistics where table_name like 'HOGE%';

TABLE_NAME INDEX_NAME NUM_ROWS LAST_ANALYZED
------------------------------ ------------------------------ ---------- -------------------
HOGE2 PK_HOGE2 100 2017/05/06 14:42:32
HOGE SYS_C0011856 100 2017/05/06 14:42:25

SCOTT2> select table_name,num_rows,last_analyzed from user_tab_statistics where table_name like 'HOGE%';

TABLE_NAME NUM_ROWS LAST_ANALYZED
------------------------------ ---------- -------------------
HOGE2 100 2017/05/06 14:42:32
HOGE 100 2017/05/06 14:42:25

| | コメント (0) | トラックバック (0)