LoginSignup
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

@ora_gonsuke777in日本オラクル株式会社

Oracle Database・統計プリファレンス の expdp/impdp時の挙動を確認する。

Posted at

表題の通り、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 に包含されるのは盲点やった……彡(-)(-)

1
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?