OS 환경 : Oracle Linux 8.4 (64bit)
DB 환경 : Oracle Database 19.3.0.0
방법 : 오라클 19c 통계정보 이관 방법
본문에서는 오라클 19c 환경애서 db전체의 테이블, 인덱스 통계정보를 export 하여 타 db(clone)에 넣는 방법을 설명함
본문의 통계정보 이관작업은 아래와 같은 방식으로 수행함
1. create_stat_table 프로시저로 통계정보가 저장될 테이블을 만들고
2. export_database_stats 프로시저로 전체 db 통계정보를 통계정보 저장 테이블에 넣고
3. 이후 datapump expdp를 이용해 해당 테이블을 export 받아서
4. tobe db에서 datapump impdp를 이용해 해당 테이블을 import 한 뒤
5. import_database_stats 프로시저로 통계정보를 db의 오브젝트에 넣어줌
6. drop_stat_table 프로시저로 통계정보가 저장될 테이블을 제거함
본문에서 사용하는 프로시저들 설명
DBMS_STATS.CREATE_STAT_TABLE
|
1
2
3
4
5
|
DBMS_STATS.CREATE_STAT_TABLE (
ownname VARCHAR2,
stattab VARCHAR2,
tblspace VARCHAR2 DEFAULT NULL,
global_temporary BOOLEAN DEFAULT FALSE);
|
변수 설명
ownname : 통계 테이블을 생성할 스키마 이름
stattab : 생성할 통계 테이블 이름임, 이 값은 나중에 EXPORT_*_STATS나 IMPORT_*_STATS 프로시저에서 stattab 파라미터로 사용됨
데이터딕셔너리 통계를 직접 수정하지 않고 내보내거나 가져오기 위해 사용하는 테이블 이름
tblspace : 통계 테이블을 생ㅋ성할 테이블스페이스 이름, 지정하지 않으면 유저의 기본 테이블스페이스에 생성됨
global_temporary : 글로벌 임시 테이블로 생성할지 여부를 지정함
테이블을 글로벌 임시 테이블로 생성해야 하는지 여부
DBMS_STATS.EXPORT_DATABASE_STATS
|
1
2
3
4
5
|
DBMS_STATS.EXPORT_DATABASE_STATS (
stattab VARCHAR2,
statid VARCHAR2 DEFAULT NULL,
statown VARCHAR2 DEFAULT NULL,
stat_category VARCHAR2 DEFAULT DEFAULT_STAT_CATEGORY);
|
변수 설명
stattab : 통계를 저장할 사용자 정의 통계 테이블 이름
statid : 통계 그룹 식별자(선택 사항), 이 값으로 stattab 내에서 통계를 구분할 수 있음
statown : stattab이 속한 스키마(현재 스키마와 다를 경우 지정함)
stat_category : 가져올 통계 종류를 지정함, 여러 값을 쉼표로 구분해서 입력 가능
- OBJECT_STATS : 테이블, 컬럼, 인덱스 통계(기본값)
- SYNOPSES : 증분 통계를 위한 synopsis 정보
- OBJECT_STATS, SYNOPSES 지정 시 기존 테이블, 컬럼, 인덱스 통계와 synopsis 모두 삭제됨
DBMS_STATS.IMPORT_DATABASE_STATS
|
1
2
3
4
5
6
7
|
DBMS_STATS.IMPORT_DATABASE_STATS (
stattab VARCHAR2,
statid VARCHAR2 DEFAULT NULL,
statown VARCHAR2 DEFAULT NULL,
no_invalidate BOOLEAN DEFAULT to_no_invalidate_type(get_param('NO_INVALIDATE')),
force BOOLEAN DEFAULT FALSE,
stat_category VARCHAR2 DEFAULT DEFAULT_STAT_CATEGORY);
|
변수 설명
stattab : 가져올 통계가 저장되어 있는 통계 테이블 이름
statid : stattab 내에서 이 통계를 구분할 수 있는 식별자 (선택 사항)
statown : stattab이 속한 스키마 (현재 스키마와 다를 경우 지정함)
no_invalidate : 통계를 가져올 때 관련 커서의 무효화 방식을 제어함
- TRUE : 관련 커서를 무효화하지 않음
- FALSE : 관련 커서를 즉시 무효화함
- AUTO : 기본값이며, 점진적 무효화를 통해 성능 영향 최소화함(많은 커서가 무효화되는 경우 특히 효과적임)
이 기본값은 SET_DATABASE_PREFS, SET_GLOBAL_PREFS, SET_SCHEMA_PREFS, SET_TABLE_PREFS 프로시저로 변경할 수 있음
force : 통계가 잠겨 있을 경우에도 강제로 가져올지 여부를 지정함
- TRUE : 통계 잠금을 무시하고 통계를 가져옴
- FALSE : 통계가 잠겨 있지 않은 경우에만 가져옴
stat_category : 가져올 통계 종류를 지정함, 여러 값을 쉼표로 구분해서 입력 가능
- OBJECT_STATS : 테이블, 컬럼, 인덱스 통계 (기본값)
- SYNOPSES : 증분 통계를 위한 synopsis 정보
DBMS_STATS.DROP_STAT_TABLE
|
1
2
3
|
DBMS_STATS.DROP_STAT_TABLE (
ownname VARCHAR2,
stattab VARCHAR2);
|
변수 설명
ownname : 스키마 이름
stattab : 사용자 통계 테이블 식별자 (통계를 저장하거나 가져오는 데 사용하는 테이블 이름)
테스트
현재 통계정보 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
|
SQL>
set lines 200 pages 1000
col owner for a20
col table_name for a30
select * from (
select owner, table_name, to_char(last_analyzed, 'yyyy/mm/dd hh24:mi:ss') last_analyzed from dba_tables
where 1=1
and last_analyzed is not null
order by 3 desc
)
where rownum <= 10;
OWNER TABLE_NAME LAST_ANALYZED
-------------------- ------------------------------ -------------------
SYS UTL_RECOMP_SORTED 2025/01/22 15:17:11
SYS UTL_RECOMP_COMPILED 2025/01/22 15:17:11
DVSYS RULE_SET$ 2019/04/17 02:03:10
DVSYS RULE_SET_T$ 2019/04/17 02:03:10
DVSYS REALM_T$ 2019/04/17 02:03:10
DVSYS REALM_AUTH$ 2019/04/17 02:03:10
DVSYS REALM_OBJECT$ 2019/04/17 02:03:10
DVSYS REALM$ 2019/04/17 02:03:10
DVSYS RULE_SET_RULE$ 2019/04/17 02:03:10
DVSYS RULE$ 2019/04/17 02:03:10
10 rows selected.
|
25년 1월에 수집된 통계정보가 제일 최신임
이 db를 현재 시점기준으로 clone 또는 cold 백업으로 신규 db에 넣는 상황 가정함
db 종료 후 cold 백업 후 추후 통계 import 시점에 restore
참고 : Oracle 12c R2 Clone DB 생성 및 복구 시나리오 ( https://positivemh.tistory.com/268 )
통계정보 수집(db 레벨 수집)
|
1
2
3
4
5
6
7
8
9
10
11
12
|
SQL>
BEGIN
DBMS_STATS.GATHER_DATABASE_STATS(
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE SKEWONLY',
degree => DBMS_STATS.AUTO_DEGREE,
cascade => TRUE
);
END;
/
PL/SQL procedure successfully completed.
|
각 파라미터 의미
DBMS_STATS.GATHER_DATABASE_STATS : db 레벨에서 통계정보 수집
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE : 자동 샘플링, Oracle이 데이터 양과 통계 품질에 따라 적절한 샘플링 비율을 자동 결정
method_opt => 'FOR ALL COLUMNS SIZE SKEWONLY' : Oracle이 컬럼의 데이터 분포를 기반으로 히스토그램을 수집할 컬럼을 결정
degree => DBMS_STATS.AUTO_DEGREE : 병렬 자동 시스템 상황(코어 수, 부하 등)에 따라 Oracle이 병렬도를 자동 설정
cascade => TRUE 인덱스 포함 테이블뿐 아니라 해당 테이블에 속한 인덱스 통계도 함께 수집
통계 저장용 테이블 생성
|
1
2
3
|
SQL> exec dbms_stats.create_stat_table(ownname => 'SYSTEM', stattab => 'ALL_TABLE_STATS');
PL/SQL procedure successfully completed.
|
all_table_stats 구조 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
|
SQL> desc system.all_table_stats
Name Null? Type
--------------------- --------------------------------
STATID VARCHAR2(128)
TYPE CHAR(1)
VERSION NUMBER
FLAGS NUMBER
C1 VARCHAR2(128)
C2 VARCHAR2(128)
C3 VARCHAR2(128)
C4 VARCHAR2(128)
C5 VARCHAR2(128)
C6 VARCHAR2(128)
N1 NUMBER
N2 NUMBER
N3 NUMBER
N4 NUMBER
N5 NUMBER
N6 NUMBER
N7 NUMBER
N8 NUMBER
N9 NUMBER
N10 NUMBER
N11 NUMBER
N12 NUMBER
N13 NUMBER
D1 DATE
T1 TIMESTAMP(6) WITH TIME ZONE
R1 RAW(1000)
R2 RAW(1000)
R3 RAW(1000)
CH1 VARCHAR2(1000)
CL1 CLOB
BL1 BLOB
|
통계정보 export(ALL_TABLE_STATS 테이블로 넣는작업)
|
1
2
3
|
SQL> exec dbms_stats.export_database_stats(statown => 'SYSTEM', stattab => 'ALL_TABLE_STATS');
PL/SQL procedure successfully completed.
|
all_table_stats 테이블 count 확인
|
1
2
3
4
5
|
SQL> select count(*) from system.all_table_stats;
COUNT(*)
----------
188400
|
약 18만건이 저장됨
통계테이블 datapump export
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
|
$ expdp system/oracle \
dumpfile=202507nn_all_table_stats.dmp \
logfile=202507nn_all_table_stats.log \
directory=DATA_PUMP_DIR \
tables='SYSTEM.ALL_TABLE_STATS'
Export: Release 19.0.0.0.0 - Production on Mon Jun 23 21:45:47 2025
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 "SYSTEM"."SYS_EXPORT_TABLE_01": system/******** dumpfile=202507nn_all_table_stats.dmp logfile=202507nn_all_table_stats.log directory=DATA_PUMP_DIR tables=SYSTEM.ALL_TABLE_STATS
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
. . exported "SYSTEM"."ALL_TABLE_STATS" 23.95 MB 188400 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
/ORA19/app/oracle/admin/oracle19/dpdump/202507nn_all_table_stats.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at Mon Jun 23 21:46:07 2025 elapsed 0 00:00:19
|
datapump dump 파일 clone db로 복제 또는 콜드백업본 사용중이라면 백업된 경로만 확인
|
1
2
|
$ ls /ORA19/app/oracle/admin/oracle19/dpdump/202507nn_all_table_stats.dmp
/ORA19/app/oracle/admin/oracle19/dpdump/202507nn_all_table_stats.dmp
|
콜드백업본 restore 또는 clone db 기동
|
1
2
3
|
$ cp 콜드백업본_위치/* 데이터파일위치/
SQL> startup
..
|
통계테이블 datapump import
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
$ impdp system/"Dhfkzmf1!" \
dumpfile=202507nn_all_table_stats.dmp \
logfile=202507nn_all_table_stats_impdp1.log \
directory=DATA_PUMP_DIR \
tables='SYSTEM.ALL_TABLE_STATS'
Import: Release 19.0.0.0.0 - Production on Mon Jun 23 22:05:27 2025
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 "SYSTEM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TABLE_01": system/******** dumpfile=202507nn_all_table_stats.dmp logfile=202507nn_all_table_stats_impdp1.log directory=DATA_PUMP_DIR tables=SYSTEM.ALL_TABLE_STATS
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SYSTEM"."ALL_TABLE_STATS" 23.95 MB 188400 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "SYSTEM"."SYS_IMPORT_TABLE_01" successfully completed at Mon Jun 23 22:05:39 2025 elapsed 0 00:00:11
|
통계정보 import(ALL_TABLE_STATS 테이블에서 db 테이블로 통계정보를 넣는작업)
|
1
2
3
|
SQL> exec dbms_stats.import_database_stats(statown => 'SYSTEM', stattab => 'ALL_TABLE_STATS');
PL/SQL procedure successfully completed.
|
통계정보 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
|
SQL>
set lines 200 pages 1000
col owner for a20
col table_name for a30
select * from (
select owner, table_name, to_char(last_analyzed, 'yyyy/mm/dd hh24:mi:ss') last_analyzed from dba_tables
where 1=1
and last_analyzed is not null
order by 3 desc
)
where rownum <= 10;
OWNER TABLE_NAME LAST_ANALYZED
-------------------- ------------------------------ -------------------
SYS _default_auditing_options_ 2025/06/23 21:22:22
SYS XSTREAM$_MAP 2025/06/23 21:22:22
SYS XSTREAM$_SERVER_CONNECTION 2025/06/23 21:22:22
SYS XSTREAM$_HANDLE_COLLISIONS 2025/06/23 21:22:22
SYS XSTREAM$_REPERROR_HANDLER 2025/06/23 21:22:22
SYS XSTREAM$_DML_CONFLICT_HANDLER 2025/06/23 21:22:22
SYS XSTREAM$_PARAMETERS 2025/06/23 21:22:22
SYS XSTREAM$_SYSGEN_OBJS 2025/06/23 21:22:22
SYS XSTREAM$_SUBSET_RULES 2025/06/23 21:22:22
SYS XSTREAM$_SERVER 2025/06/23 21:22:22
10 rows selected.
|
last_analyzed 값이 null이 아닌값은 모두 잘 들어감
last_analyzed가 null인 테이블이 있는지 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
|
SQL>
set lines 200 pages 1000
col owner for a20
col table_name for a30
select owner, table_name, to_char(last_analyzed, 'yyyy/mm/dd hh24:mi:ss') last_analyzed from dba_tables
where 1=1
and last_analyzed is null
order by 1, 2;
OWNER TABLE_NAME LAST_ANALYZED
-------------------- ------------------------------ -------------------
DBSNMP MGMT_DB_FILE_GTT
DBSNMP MGMT_DB_SIZE_GTT
DBSNMP MGMT_TEMPT_SQL
DVSYS DV$CMDCONTEXT
GSMADMIN_INTERNAL CHANGE_LOG_QUEUE_TABLE
GSMADMIN_INTERNAL CHUNKDATA_TMP
MDSYS SDO_CS_CONTEXT_INFORMATION
MDSYS SDO_GEOR_DDL__TABLE$$
MDSYS SDO_GR_MOSAIC_0
MDSYS SDO_GR_MOSAIC_1
MDSYS SDO_GR_MOSAIC_2
MDSYS SDO_GR_MOSAIC_3
MDSYS SDO_GR_MOSAIC_CB
MDSYS SDO_GR_PARALLEL
MDSYS SDO_GR_RDT_1
MDSYS SDO_ST_TOLERANCE
MDSYS SDO_TOPO_DATA$
MDSYS SDO_TOPO_MAPS
MDSYS SDO_TOPO_RELATION_DATA
MDSYS SDO_TOPO_TRANSACT_DATA
MDSYS SDO_TXN_IDX_EXP_UPD_RGN
MDSYS SDO_TXN_JOURNAL_GTT
MDSYS SDO_WFS_LOCAL_TXNS
ORDDATA ORDDCM_ANON_ATTRS_TMP
ORDDATA ORDDCM_ANON_RULES_TMP
ORDDATA ORDDCM_CT_ACTION_TMP
ORDDATA ORDDCM_CT_DAREFS_TMP
ORDDATA ORDDCM_CT_LOCATORPATHS_TMP
ORDDATA ORDDCM_CT_PRED_OPRD_TMP
ORDDATA ORDDCM_CT_PRED_PAR_TMP
ORDDATA ORDDCM_CT_PRED_SET_TMP
ORDDATA ORDDCM_CT_PRED_TMP
ORDDATA ORDDCM_DICT_ATTRS_TMP
ORDDATA ORDDCM_DOCS_TMP
ORDDATA ORDDCM_DOC_REFS_TMP
ORDDATA ORDDCM_MAPPED_PATHS_TMP
ORDDATA ORDDCM_MAPPING_DOCS_TMP
ORDDATA ORDDCM_PRV_ATTRS_TMP
ORDDATA ORDDCM_RT_PREF_PARAMS_TMP
ORDDATA ORDDCM_STD_ATTRS_TMP
ORDDATA ORDDCM_STORED_TAGS_TMP
ORDDATA ORDDCM_UID_DEFS_TMP
SYS ALERT_QT
SYS AQ$_KUPC$DATAPUMP_QUETAB_1_D
SYS AQ$_KUPC$DATAPUMP_QUETAB_1_G
SYS AQ$_KUPC$DATAPUMP_QUETAB_1_H
SYS AQ$_KUPC$DATAPUMP_QUETAB_1_I
SYS AQ$_KUPC$DATAPUMP_QUETAB_1_L
SYS AQ$_KUPC$DATAPUMP_QUETAB_1_P
SYS AQ$_KUPC$DATAPUMP_QUETAB_1_S
SYS AQ$_KUPC$DATAPUMP_QUETAB_1_T
SYS AQ$_MEM_MC
SYS AQ_EVENT_TABLE
SYS AQ_PROP_TABLE
SYS ATEMPTAB$
SYS CHNF$_QUERY_DELTAS
SYS CLUSTER_DATABASES
SYS CLUSTER_INSTANCES
SYS CLUSTER_NODES
SYS CQN_EVENT_TABLE
SYS DATA_PUMP_XPL_TABLE$
SYS DBMS_STATS_ID_MAP_TAB
SYS DBMS_STATS_OBJ_LIST_TAB
SYS EDITIONING_TYPES$
SYS FINALHIST$
SYS IMPDP_STATS
SYS KU$NOEXP_TAB
SYS KU$XKTFBUE
SYS KU$_DATAPUMP_MASTER_10_1
SYS KU$_DATAPUMP_MASTER_11_1
SYS KU$_DATAPUMP_MASTER_11_1_0_7
SYS KU$_DATAPUMP_MASTER_11_2
SYS KU$_DATAPUMP_MASTER_12_0
SYS KU$_DATAPUMP_MASTER_12_2
SYS KU$_LIST_FILTER_TEMP
SYS KU$_LIST_FILTER_TEMP_2
SYS KU$_SHARD_DOMIDX_NAMEMAP
SYS KUPC$DATAPUMP_QUETAB_1
SYS MAP_OBJECT
SYS MODELGTTRAW$
SYS ODCI_PMO_ROWIDS$
SYS ODCI_SECOBJ$
SYS ODCI_WARNINGS$
SYS ORA$PREPLUGIN_BACKUP_QTB
SYS PDB_MON_EVENT_QTABLE$
SYS PLAN_TABLE$
SYS PSTUBTBL
SYS SAM_SPARSITY_ADVICE
SYS SCHEDULER$_EVENT_QTAB
SYS SCHEDULER$_REMDB_JOBQTAB
SYS SCHEDULER_FILEWATCHER_QT
SYS SNAP_XCMT$
SYS SPD_SCRATCH_TAB
SYS STATS_ADVISOR_FILTER_OBJ$
SYS STATS_ADVISOR_FILTER_OPR$
SYS STATS_ADVISOR_FILTER_RULE$
SYS STREAMS$_COMPONENT_EVENT_IN
SYS STREAMS$_COMPONENT_IN
SYS STREAMS$_COMPONENT_LINK_IN
SYS STREAMS$_COMPONENT_PROP_IN
SYS STREAMS$_COMPONENT_STAT_IN
SYS STREAMS$_COMPONENT_STAT_OUT
SYS STREAMS$_LOCAL_ACTIONS_IN
SYS STREAMS$_LOCAL_FINDINGS_IN
SYS STREAMS$_LOCAL_RECS_IN
SYS STREAMS$_PATH_BOTTLENECK_OUT
SYS STREAMS$_PATH_STAT_OUT
SYS SUMDELTA$
SYS SYS$SERVICE_METRICS_TAB
SYS SYS_IOT_OVER_73248
SYS SYS_IOT_OVER_73293
SYS SYS_MFBA_NCHANGE
SYS SYS_MFBA_NROW
SYS SYS_MFBA_NTCRV
SYS SYS_MFBA_STAGE_RID
SYS SYS_MFBA_TRACKED_TXN
SYS TTS_ERROR$
SYS WRI$_ADV_ADDM_PDBS
SYS WRI$_ADV_ASA_RECO_DATA
SYS WRI$_HEATMAP_TOPN_DEP1
SYS WRI$_HEATMAP_TOPN_DEP2
SYS WRI$_SQLSET_PLANS_TOCAP
SYS WRI$_SQLSET_STS_TOPACK
SYS XS$VALIDATION_TABLE
SYSTEM ALL_TABLE_STATS
SYSTEM LOGMNRT_MDDL$
SYSTEM LOGMNR_GT_TAB_INCLUDE$
SYSTEM LOGMNR_GT_USER_INCLUDE$
SYSTEM LOGMNR_GT_XID_INCLUDE$
SYSTEM OL$
SYSTEM OL$HINTS
SYSTEM OL$NODES
WMSYS WM$EVENT_QUEUE_TABLE
WMSYS WM$MW_TABLE$
XDB XDB$XIDX_IMP_T
135 rows selected.
|
135개정도 있지만 모두 오라클 기본 유저들이라 무시해도 될듯함
작업 완료 후 통계 저장용 테이블 삭제
|
1
2
3
|
SQL> exec dbms_stats.drop_stat_table(ownname => 'SYSTEM',stattab => 'ALL_TABLE_STATS');
PL/SQL procedure successfully completed.
|
참고용
통계정보 수집 이력 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
|
SQL>
set lines 200 pages 1000
col operation for a20
col target for a40
col status for a10
col job_name for a20
select operation, target
, to_char(start_time, 'yyyy/mm/dd hh24:mi:ss') st_time
, to_char(end_time, 'yyyy/mm/dd hh24:mi:ss') end_time
, status, job_name, session_id
from dba_optstat_operations;
OPERATION TARGET ST_TIME END_TIME STATUS JOB_NAME SESSION_ID
-------------------- ---------------------------------------- ------------------- ------------------- ---------- -------------------- ----------
gather_index_stats "SYS"."WRI$_ADV_REC_PK" 2025/06/24 22:00:25 2025/06/24 22:00:25 COMPLETED ORA$AT_OS_OPT_SY_21 149
gather_index_stats "SYS"."WRI$_ADV_SQLT_RTN_PLAN_PK" 2025/06/24 22:00:25 2025/06/24 22:00:25 COMPLETED ORA$AT_OS_OPT_SY_21 149
gather_index_stats "SYS"."WRI$_ADV_ACTIONS_PK" 2025/06/24 22:00:25 2025/06/24 22:00:25 COMPLETED ORA$AT_OS_OPT_SY_21 149
gather_index_stats "SYS"."WRI$_ADV_REC_ACTIONS_PK" 2025/06/24 22:00:25 2025/06/24 22:00:25 COMPLETED ORA$AT_OS_OPT_SY_21 149
lock_table_stats "IMSI"."TBL_LOCK" 2025/06/26 12:47:47 2025/06/26 12:47:47 COMPLETED 393
unlock_table_stats "IMSI"."TBL_LOCK" 2025/06/26 13:17:06 2025/06/26 13:17:06 COMPLETED 393
lock_schema_stats IMSI 2025/06/26 13:19:58 2025/06/26 13:19:58 COMPLETED 393
unlock_table_stats "IMSI"."TBL_LOCK" 2025/06/26 13:21:34 2025/06/26 13:21:34 COMPLETED 393
gather_table_stats "IMSI"."TEST1" 2025/06/26 13:23:31 2025/06/26 13:23:31 COMPLETED 393
lock_schema_stats IMSI 2025/06/26 13:23:50 2025/06/26 13:23:50 COMPLETED 393
gather_table_stats "IMSI"."TEST1" 2025/06/26 13:23:53 2025/06/26 13:23:53 FAILED 393
lock_schema_stats IMSI 2025/06/26 13:24:34 2025/06/26 13:24:34 COMPLETED 393
unlock_table_stats "IMSI"."TBL_LOCK" 2025/06/26 13:25:14 2025/06/26 13:25:14 COMPLETED 393
unlock_schema_stats IMSI 2025/06/26 13:25:24 2025/06/26 13:25:24 COMPLETED 393
lock_schema_stats IMSI 2025/06/26 13:25:39 2025/06/26 13:25:39 COMPLETED 393
gather_table_stats "IMSI"."TEST1" 2025/06/26 13:43:10 2025/06/26 13:43:10 COMPLETED 393
gather_table_stats "IMSI"."TBL_LOCK" 2025/06/26 13:43:24 2025/06/26 13:43:24 FAILED 393
1185 rows selected.
|
failed 인 대상은 notes 컬럼을 보면 실패 원인을 확인할 수 있음
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
SQL>
set lines 200 pages 1000
col status for a10
select id, status, notes
from dba_optstat_operations
where id = 1527;
ID STATUS
---------- ----------
NOTES
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1527 FAILED
<params><param name="block_sample" val="FALSE"/><param name="cascade" val="NULL"/><param name="concurrent" val="FALSE"/><param name="degree" val="NULL"/><param name="estimate_percent" val="DBMS_STATS.
AUTO_SAMPLE_SIZE"/><param name="force" val="FALSE"/><param name="granularity" val="AUTO"/><param name="method_opt" val="FOR ALL COLUMNS SIZE AUTO"/><param name="no_invalidate" val="NULL"/><param name=
"ownname" val="IMSI"/><param name="partname" val=""/><param name="reporting_mode" val="FALSE"/><param name="statid" val=""/><param name="statown" val=""/><param name="stattab" val=""/><param name="sta
ttype" val="DATA"/><param name="tabname" val="TBL_LOCK"/></params><error>ORA-20005: object statistics are locked (stattype = ALL)</error>
|
note 재정렬
|
1
2
3
4
5
6
7
8
9
10
|
<params><param name="block_sample" val="FALSE"/><param name="cascade" val="NULL"/>
<param name="concurrent" val="FALSE"/><param name="degree" val="NULL"/>
<param name="estimate_percent" val="DBMS_STATS.AUTO_SAMPLE_SIZE"/>
<param name="force" val="FALSE"/><param name="granularity" val="AUTO"/>
<param name="method_opt" val="FOR ALL COLUMNS SIZE AUTO"/><param name="no_invalidate" val="NULL"/>
<param name="ownname" val="IMSI"/><param name="partname" val=""/>
<param name="reporting_mode" val="FALSE"/>
<param name="statid" val=""/><param name="statown" val=""/>
<param name="stattab" val=""/><param name="stattype" val="DATA"/><param name="tabname" val="TBL_LOCK"/>
</params><error>ORA-20005: object statistics are locked (stattype = ALL)</error>
|
error 부분를 보면 ORA-20005: object statistics are locked (stattype = ALL) 이라고 나와있음
이는 테이블에 통계정보 lock이 걸려 있어 수집을 못한것임
에러 이외에도 notes 컬럼을 보면 통계정보 수집시 어떤 옵션을 사용했는지 확인도 가능함
결론 :
dbms_stats 패키지를 이용해 db 전체 통계정보를 백업 한뒤 다시 복구할수 있음
이 방식은 마이그레이션 프로젝트시 1차로 이관해놓은 데이터에 대해서 통계정보를 전체 수집한 뒤
실 이관 및 오픈을 하기 직전에 통계정보를 재수집하면 너무 느리기때문에 백업해둔 통계정보를 임포트하는 용도로 사용할수 있음
참조 :
How To Transfer Statistics To Different Schemas and Tables and Indexes and Partition and Columns (Doc ID 2701368.1)
How to Use DBMS_STATS to Move Statistics to a Different Database (Doc ID 117203.1)
Transferring Optimizer Statistics to Support (Doc ID 242489.1)
ORA-20001 While Importing Database Statistics From 12.1.0.2 NON-CDB To 19c PDB (Doc ID 2832259.1)
https://dohdatabase.com/2023/12/18/how-to-export-and-import-statistics-faster-using-dbms_stats-in-parallel/
https://bae9086.tistory.com/515#google_vignette
https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_STATS.html#GUID-A323DD4F-1A05-4247-BEC6-1A7103660DAF
https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_STATS.html#GUID-CE0A8C67-2EC6-422F-A311-16083A07110D
https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_STATS.html#GUID-0244C71F-40E4-4CC5-A1D3-08C96B8B9E16
https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/ALL_TAB_STATISTICS.html
https://positivemh.tistory.com/332
'ORACLE > Migration' 카테고리의 다른 글
| 오라클 19c 통계정보 대량 이관 분석 및 속도 개선 (0) | 2025.08.10 |
|---|---|
| 오라클 19c 통계정보 대량 이관시 병렬 처리 (0) | 2025.08.10 |
| 오라클 11gR2 Raw Device RAC to 19c ASM RAC Rman 백업셋 이용 업그레이드 방법 (0) | 2025.06.22 |
| 오라클 11gR2 ASM RAC to 19c ASM RAC Rman 백업셋 이용 업그레이드 방법 (0) | 2025.06.22 |
| 오라클 19c to 19c datapump impdp network_link 옵션 사용 (0) | 2025.05.18 |