表題の通り、Oracle Database の統計プリファレンスの挙動を確認してみるやで彡(ﾟ)(ﾟ)

1. Oracle Database の統計プリファレンスとは？

Oracle Database の SQL性能を良くするには、オプティマイザ統計を適切に管理する必要が有ります。
統計プリファレンスは Oracle Database でオプティマイザ統計を取得する
DBMS_STATSパッケージの動作を制御するパラメータとなります。

Oracle Database SQLチューニング・ガイド 19c
12.2 オプティマイザ統計プリファレンスの設定
https://docs.oracle.com/cd/F19136_01/tgsql/options-for-optimizer-statistics-gathering.html#GUID-4FDDBCB2-1DA1-4697-9F07-5AB7DE01D682

2. 統計プリファレンスの設定値を確認

統計プリファレンスの設定値は DBMS_STATSパッケージのGET_PREFSファンクション
および (DBA|ALL|USER)_TAB_STAT_PREFSディクショナリから確認します。

166 DBMS_STATS
166.7.61 GET_PREFSファンクション
https://docs.oracle.com/cd/F19136_01/arpls/DBMS_STATS.html#GUID-2C00FE80-1553-404C-85B6-220895561FE8

3.116 ALL_TAB_STAT_PREFS
https://docs.oracle.com/cd/F19136_01/refrn/ALL_TAB_STAT_PREFS.html#GUID-32D6CE93-E336-4BA5-A55C-1CACFDEF423E

下記はサンプルです。テーブル作成直後は DBA_TAB_STAT_PREFSディクショナリにレコードは無し。

COLUMN OWNER FORMAT A30;
COLUMN TABLE_NAME FORMAT A30;
COLUMN PREFERENCE_NAME FORMAT A30;
COLUMN PREFERENCE_VALUE FORMAT A30;
CREATE TABLE TBL_A (C1 NUMBER);
SELECT DBMS_STATS.GET_PREFS ('INCREMENTAL', 'AYSHIBAT', 'TBL_A') FROM DUAL;
SELECT * FROM DBA_TAB_STAT_PREFS WHERE TABLE_NAME = 'TBL_A';

SQL> CREATE TABLE TBL_A (C1 NUMBER);

Table created.

SQL> SELECT DBMS_STATS.GET_PREFS ('INCREMENTAL', 'AYSHIBAT', 'TBL_A') FROM DUAL;

DBMS_STATS.GET_PREFS('INCREMENTAL','AYSHIBAT','TBL_A')
--------------------------------------------------------------------------------
FALSE

SQL> SELECT * FROM DBA_TAB_STAT_PREFS WHERE TABLE_NAME = 'TBL_A';

no rows selected

3. 統計プリファレンスを設定してみる。

統計プリファレンスの設定は DBMS_STATSパッケージの SET_TABLE_PREFSプロシージャで実施します。

166 DBMS_STATS
166.7.121 SET_TABLE_PREFSプロシージャ
https://docs.oracle.com/cd/F19136_01/arpls/DBMS_STATS.html#GUID-CDAA4742-5398-4A64-B871-E39F38D0021F

下記はサンプルです。統計プリファレンスをセットすると、DBA_TAB_STAT_PREFSディクショナリにレコードが格納されます。

COLUMN OWNER FORMAT A30;
COLUMN TABLE_NAME FORMAT A30;
COLUMN PREFERENCE_NAME FORMAT A30;
COLUMN PREFERENCE_VALUE FORMAT A30;
EXEC DBMS_STATS.SET_TABLE_PREFS('AYSHIBAT', 'TBL_A', 'INCREMENTAL', 'TRUE');
SELECT DBMS_STATS.GET_PREFS ('INCREMENTAL', 'AYSHIBAT', 'TBL_A') FROM DUAL;
SELECT * FROM DBA_TAB_STAT_PREFS WHERE TABLE_NAME = 'TBL_A';

SQL> EXEC DBMS_STATS.SET_TABLE_PREFS('AYSHIBAT', 'TBL_A', 'INCREMENTAL', 'TRUE');

PL/SQL procedure successfully completed.

SQL> SELECT DBMS_STATS.GET_PREFS ('INCREMENTAL', 'AYSHIBAT', 'TBL_A') FROM DUAL;

DBMS_STATS.GET_PREFS('INCREMENTAL','AYSHIBAT','TBL_A')
--------------------------------------------------------------------------------
TRUE

SQL> SELECT * FROM DBA_TAB_STAT_PREFS WHERE TABLE_NAME = 'TBL_A';

OWNER                          TABLE_NAME                     PREFERENCE_NAME                PREFERENCE_VALUE
------------------------------ ------------------------------ ------------------------------ ------------------------------
AYSHIBAT                       TBL_A                          INCREMENTAL                    TRUE

SQL>

4. 統計プリファレンスはエクスポート/インポート(expdp/impdp)で移行されるか？

エクスポート/インポート(expdp/impdp) で統計プリファレンスが移行されるか試してみます。
シンプルな下記コマンドを実行彡(ﾟ)(ﾟ)

expdp AYSHIBAT/xxxxxxxx@ORCL JOB_NAME=exp_test1 DUMPFILE=DIR_EXTERNAL:exp_test1.dmp LOGFILE=DIR_EXTERNAL:exp_test1.log TABLES=TBL_A

sqlplus /nolog
CONNECT AYSHIBAT/xxxxxxxx@ORCL
DROP TABLE TBL_A;
EXIT;

impdp AYSHIBAT/xxxxxxxx@ORCL JOB_NAME=imp_test1 DUMPFILE=DIR_EXTERNAL:exp_test1.dmp LOGFILE=DIR_EXTERNAL:imp_test1.log TABLES=TBL_A

sqlplus /nolog
CONNECT AYSHIBAT/xxxxxxxx@ORCL
SET LINESIZE 170;
COLUMN OWNER FORMAT A30;
COLUMN TABLE_NAME FORMAT A30;
COLUMN PREFERENCE_NAME FORMAT A30;
COLUMN PREFERENCE_VALUE FORMAT A30;
SELECT DBMS_STATS.GET_PREFS ('INCREMENTAL', 'AYSHIBAT', 'TBL_A') FROM DUAL;
SELECT * FROM DBA_TAB_STAT_PREFS WHERE TABLE_NAME = 'TBL_A';

結果は以下のようになりました。

[oracle@localhost ~]$ expdp AYSHIBAT/xxxxxxxx@ORCL JOB_NAME=exp_test1 DUMPFILE=DIR_EXTERNAL:exp_test1.dmp LOGFILE=DIR_EXTERNAL:exp_test1.log TABLES=TBL_A

Export: Release 19.0.0.0.0 - Production on Thu May 20 22:51:58 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "AYSHIBAT"."EXP_TEST1":  AYSHIBAT/xxxxxxxx@ORCL JOB_NAME=exp_test1 DUMPFILE=DIR_EXTERNAL:exp_test1.dmp LOGFILE=DIR_EXTERNAL:exp_test1.log TABLES=TBL_A
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "AYSHIBAT"."TBL_A"                              0 KB       0 rows
Master table "AYSHIBAT"."EXP_TEST1" successfully loaded/unloaded
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Dump file set for AYSHIBAT.EXP_TEST1 is:
  /home/oracle/work/external/exp_test1.dmp
Job "AYSHIBAT"."EXP_TEST1" successfully completed at Thu May 20 22:53:20 2021 elapsed 0 00:01:21

[oracle@localhost ~]$ sqlplus /nolog

SQL*Plus: Release 19.0.0.0.0 - Production on Thu May 20 23:01:09 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

SQL> CONNECT AYSHIBAT/xxxxxxxx@ORCL
Connected.
SQL> DROP TABLE TBL_A;

Table dropped.

SQL> EXIT;
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@localhost ~]$ impdp AYSHIBAT/xxxxxxxx@ORCL JOB_NAME=imp_test1 DUMPFILE=DIR_EXTERNAL:exp_test1.dmp LOGFILE=DIR_EXTERNAL:imp_test1.log TABLES=TBL_A

Import: Release 19.0.0.0.0 - Production on Thu May 20 23:01:22 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "AYSHIBAT"."IMP_TEST1" successfully loaded/unloaded
Starting "AYSHIBAT"."IMP_TEST1":  AYSHIBAT/xxxxxxxx@ORCL JOB_NAME=imp_test1 DUMPFILE=DIR_EXTERNAL:exp_test1.dmp LOGFILE=DIR_EXTERNAL:imp_test1.log TABLES=TBL_A
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "AYSHIBAT"."TBL_A"                              0 KB       0 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "AYSHIBAT"."IMP_TEST1" successfully completed at Thu May 20 23:01:45 2021 elapsed 0 00:00:22

[oracle@localhost ~]$ sqlplus /nolog

SQL*Plus: Release 19.0.0.0.0 - Production on Thu May 20 23:03:40 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

SQL> CONNECT AYSHIBAT/xxxxxxxx@ORCL
Connected.
SQL> SET LINESIZE 170;
SQL> COLUMN OWNER FORMAT A30;
SQL> COLUMN TABLE_NAME FORMAT A30;
SQL> COLUMN PREFERENCE_NAME FORMAT A30;
SQL> COLUMN PREFERENCE_VALUE FORMAT A30;
SQL> SELECT DBMS_STATS.GET_PREFS ('INCREMENTAL', 'AYSHIBAT', 'TBL_A') FROM DUAL;

DBMS_STATS.GET_PREFS('INCREMENTAL','AYSHIBAT','TBL_A')
--------------------------------------------------------------------------------
TRUE

SQL> SELECT * FROM DBA_TAB_STAT_PREFS WHERE TABLE_NAME = 'TBL_A';

OWNER                          TABLE_NAME                     PREFERENCE_NAME                PREFERENCE_VALUE
------------------------------ ------------------------------ ------------------------------ ------------------------------
AYSHIBAT                       TBL_A                          INCREMENTAL                    TRUE

SQL>

統計プリファレンスは移行されているもよう彡(ﾟ)(ﾟ)

5. エクスポート(expdp)のオプションに exclude=STATISTICS を付与すると、統計プリファレンスは移行されるか？

エクスポート(expdp)コマンドのオプションに exclude=STATISTICS を付与して、同じことをやってみます。

expdp AYSHIBAT/xxxxxxxx@ORCL JOB_NAME=exp_test1 DUMPFILE=DIR_EXTERNAL:exp_test1.dmp LOGFILE=DIR_EXTERNAL:exp_test1.log TABLES=TBL_A exclude=STATISTICS

sqlplus /nolog
CONNECT AYSHIBAT/xxxxxxxx@ORCL
DROP TABLE TBL_A;
EXIT;

impdp AYSHIBAT/xxxxxxxx@ORCL JOB_NAME=imp_test1 DUMPFILE=DIR_EXTERNAL:exp_test1.dmp LOGFILE=DIR_EXTERNAL:imp_test1.log TABLES=TBL_A

sqlplus /nolog
CONNECT AYSHIBAT/xxxxxxxx@ORCL
SET LINESIZE 170;
COLUMN OWNER FORMAT A30;
COLUMN TABLE_NAME FORMAT A30;
COLUMN PREFERENCE_NAME FORMAT A30;
COLUMN PREFERENCE_VALUE FORMAT A30;
SELECT DBMS_STATS.GET_PREFS ('INCREMENTAL', 'AYSHIBAT', 'TBL_A') FROM DUAL;
SELECT * FROM DBA_TAB_STAT_PREFS WHERE TABLE_NAME = 'TBL_A';

結果は以下のようになりました。

[oracle@localhost work]$ expdp AYSHIBAT/xxxxxxxx@ORCL JOB_NAME=exp_test1 DUMPFILE=DIR_EXTERNAL:exp_test1.dmp LOGFILE=DIR_EXTERNAL:exp_test1.log TABLES=TBL_A exclude=STATISTICS

Export: Release 19.0.0.0.0 - Production on Thu May 20 23:07:56 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "AYSHIBAT"."EXP_TEST1":  AYSHIBAT/xxxxxxxx@ORCL JOB_NAME=exp_test1 DUMPFILE=DIR_EXTERNAL:exp_test1.dmp LOGFILE=DIR_EXTERNAL:exp_test1.log TABLES=TBL_A exclude=STATISTICS
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "AYSHIBAT"."TBL_A"                              0 KB       0 rows
Master table "AYSHIBAT"."EXP_TEST1" successfully loaded/unloaded
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Dump file set for AYSHIBAT.EXP_TEST1 is:
  /home/oracle/work/external/exp_test1.dmp
Job "AYSHIBAT"."EXP_TEST1" successfully completed at Thu May 20 23:08:53 2021 elapsed 0 00:00:56

[oracle@localhost work]$ sqlplus /nolog

SQL*Plus: Release 19.0.0.0.0 - Production on Thu May 20 23:09:30 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

SQL> CONNECT AYSHIBAT/xxxxxxxx@ORCL
Connected.
SQL> DROP TABLE TBL_A;

Table dropped.

SQL> EXIT;
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@localhost work]$ impdp AYSHIBAT/xxxxxxxx@ORCL JOB_NAME=imp_test1 DUMPFILE=DIR_EXTERNAL:exp_test1.dmp LOGFILE=DIR_EXTERNAL:imp_test1.log TABLES=TBL_A

Import: Release 19.0.0.0.0 - Production on Thu May 20 23:09:41 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "AYSHIBAT"."IMP_TEST1" successfully loaded/unloaded
Starting "AYSHIBAT"."IMP_TEST1":  AYSHIBAT/xxxxxxxx@ORCL JOB_NAME=imp_test1 DUMPFILE=DIR_EXTERNAL:exp_test1.dmp LOGFILE=DIR_EXTERNAL:imp_test1.log TABLES=TBL_A
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "AYSHIBAT"."TBL_A"                              0 KB       0 rows
Job "AYSHIBAT"."IMP_TEST1" successfully completed at Thu May 20 23:09:49 2021 elapsed 0 00:00:06

[oracle@localhost work]$ sqlplus /nolog

SQL*Plus: Release 19.0.0.0.0 - Production on Thu May 20 23:09:55 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

SQL> CONNECT AYSHIBAT/xxxxxxxx@ORCL
Connected.
SQL> SET LINESIZE 170;
SQL> COLUMN OWNER FORMAT A30;
SQL> COLUMN TABLE_NAME FORMAT A30;
SQL> COLUMN PREFERENCE_NAME FORMAT A30;
SQL> COLUMN PREFERENCE_VALUE FORMAT A30;
SQL> SELECT DBMS_STATS.GET_PREFS ('INCREMENTAL', 'AYSHIBAT', 'TBL_A') FROM DUAL;

DBMS_STATS.GET_PREFS('INCREMENTAL','AYSHIBAT','TBL_A')
---------------------------------------------------------------
FALSE

SQL> SELECT * FROM DBA_TAB_STAT_PREFS WHERE TABLE_NAME = 'TBL_A';

no rows selected

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@localhost work]$

exclude=STATISTICS を付与すると、統計プリファレンスは移行されませんでした。
統計プリファレンスは expdp/impdp の STATISTICS に包含されるという事ですやね。彡(ﾟ)(ﾟ)

6. まとめ

いま進行中のプロジェクトで必要に迫られて、統計プリファレンスについて検証しました。
統計プリファレンスが expdp/impdp の STATISTICS に包含されるのは盲点やった……彡(-)(-)