OS 환경 : Oracle Linux 8.4 (64bit)
DB 환경 : Oracle Database 19.27.0.0
방법 : 오라클 19c 통계정보 대량 이관 분석 및 속도 개선2
이전 게시글에서 통계정보 대량 이관시 statid를 넣지 않는 경우 inlist 방식으로 풀리지 않고 일반 index range scan으로 풀려 느리고
그렇기 때문에 statid를 넣어줘야 inlist 방식으로 수행되 빨라진다고 얘기했었음
참고 : 오라클 19c 통계정보 대량 이관시 병렬 처리 ( https://positivemh.tistory.com/1267 )
참고 : 오라클 19c 통계정보 대량 이관 분석 및 속도 개선 ( https://positivemh.tistory.com/1268 )
이 부분과 관련해 조금더 찾아보고 오라클측과 얘기해본 결과, 이 속도 문제는 버그로 인한것이라고함
참고 : Bug 32614157 - INLIST OPTIMIZATION NOT USED WHEN PRIOR COLUMN HAS AN "IS NULL" PREDICATE (Doc ID 32614157.8)
원래는 값이 is null로 들어와도 inlist 방식으로 잘 풀려야 한다고함
이 버그는 19.16ru에 패치로 포함되어 있지만 옵티마이저 관련 버그라 기본적으로 비활성화 되어있다고함(12.2 부터 옵티마이저 수정 패치는 비활성화 상태로 적용된다고함)
이 패치를 활성화 하고 싶은 경우 아래처럼 세션 레벨로 명령어를 수행해줘야함(system 레벨로 적용하는건 다른 쿼리에 문제가 생길수 있으니 비권고함)
|
1
|
"_fix_control" = "bug번호":ON 또는 "_fix_control" = "bug번호":1
|
통계정보 저장 테이블 생성 및 export는 이전 게시물을 참고하길 바람
참고 : 오라클 19c 통계정보 이관 방법 ( https://positivemh.tistory.com/1265 )
세션 레벨로 버그 fix 활성화 후 통계정보 import 작업 재시도
*플랜이 제대로 풀리는지 확인하기 위해 statistics_level을 세션 레벨로 all로 설정하고 수행함
|
1
2
3
4
5
|
SQL>
alter session set "_fix_control"='32614157:on';
alter session set statistics_level =all;
exec dbms_stats.import_database_stats(statown => 'SYSTEM', stattab => 'ALL_TABLE_STATS');
(실행중)
|
쿼리 및 sql_id 확인
|
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>
set lines 200 pages 1000 long 9999999
select sql_id, sql_fulltext
from v$sql
where sql_text like '%into sys.dbms_stats_id_map_ta%from user$ u, obj$ op, tabsubpartv$ tsp, obj$ os%';
SQL_ID SQL_FULLTEXT
------------- --------------------------------------------------------------------------------
282619p7ktfqd select sql_id, sql_fulltext
from v$sql
where sql_text like '%into sys.dbms_stats_id_map_ta%from user$ u, obj$ op, tabsu
bpartv$ tsp, obj$ os%'
c13dgxma46u7s
insert /*+ OPT_PARAM('_parallel_syspls_obey_force' 'false') */
into sys.dbms_stats_id_map_tab (c5, c1, c2, c3, cn)
select /*+ leading(s) index(s) */
distinct s.c5, s.c1, s.c2, s.c3, d.subpartition_name cn
from "SYSTEM"."ALL_TABLE_STATS" s,
(select u.name table_owner, op.name table_name,
op.subname partition_name, os.subname subpartition_name,
tsp.subpart# subpartition_position
from user$ u, obj$ op, tabsubpartv$ tsp, obj$ os
where u.user# = op.owner# and op.type# = 19
and op.obj# = tsp.pobj#
and tsp.obj# = os.obj# and os.type# = 34) d
where s.c5 = :1 and s.c1 = :2
and s.type in ('T','C','E','P','H','B','t','c','M','U','G','L') and s.n1
3 is not null
and s.c3 like 'SYS_%'
and s.c5 = d.table_owner and s.c1 = d.table_name
and s.c2 = d.partition_name
and s.n13 = d.subpartition_position
and s.c3 != d.subpartition_name and s.statid is null
|
c13dgxma46u7s 쿼리 플랜 확인
|
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
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
|
SQL> select * from dbms_xplan.display_cursor('c13dgxma46u7s', null, 'ADVANCED ALLSTATS LAST');
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID c13dgxma46u7s, child number 0
-------------------------------------
insert /*+ OPT_PARAM('_parallel_syspls_obey_force' 'false') */
into sys.dbms_stats_id_map_tab (c5, c1, c2, c3, cn) select
/*+ leading(s) index(s) */ distinct s.c5, s.c1, s.c2, s.c3,
d.subpartition_name cn from "SYSTEM"."ALL_TABLE_STATS" s,
(select u.name table_owner, op.name table_name,
op.subname partition_name, os.subname subpartition_name,
tsp.subpart# subpartition_position
from user$ u, obj$ op, tabsubpartv$ tsp, obj$ os
where u.user# = op.owner# and op.type# = 19 and
op.obj# = tsp.pobj# and tsp.obj# = os.obj# and
os.type# = 34) d where s.c5 = :1 and s.c1 = :2 and s.type
in ('T','C','E','P','H','B','t','c','M','U','G','L') and s.n13 is not
null and s.c3 like 'SYS_%' and s.c5 = d.table_owner
and s.c1 = d.table_name and s.c2 = d.partition_name and
s.n13 = d.su
Plan hash value: 3858324225
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | | | 7 (100)| | 0 |00:00:00.01 | 916 |
| 1 | LOAD TABLE CONVENTIONAL | DBMS_STATS_ID_MAP_TAB | 1 | | | | | 0 |00:00:00.01 | 916 |
| 2 | HASH UNIQUE | | 1 | 1 | 196 | 7 (15)| 00:00:01 | 0 |00:00:00.01 | 915 |
| 3 | NESTED LOOPS | | 1 | 1 | 196 | 6 (0)| 00:00:01 | 0 |00:00:00.01 | 915 |
| 4 | NESTED LOOPS | | 1 | 1 | 196 | 6 (0)| 00:00:01 | 0 |00:00:00.01 | 915 |
| 5 | NESTED LOOPS | | 1 | 1 | 184 | 4 (0)| 00:00:01 | 0 |00:00:00.01 | 915 |
| 6 | NESTED LOOPS | | 1 | 1 | 156 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 915 |
| 7 | MERGE JOIN CARTESIAN | | 1 | 1 | 121 | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 915 |
| 8 | INLIST ITERATOR | | 1 | | | | | 0 |00:00:00.01 | 915 |
|* 9 | TABLE ACCESS BY INDEX ROWID BATCHED| ALL_TABLE_STATS | 12 | 1 | 106 | 0 (0)| | 0 |00:00:00.01 | 915 |
|* 10 | INDEX RANGE SCAN | ALL_TABLE_STATS | 12 | 1 | | 0 (0)| | 0 |00:00:00.01 | 915 |
| 11 | BUFFER SORT | | 0 | 1 | 15 | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 |
| 12 | TABLE ACCESS BY INDEX ROWID | USER$ | 0 | 1 | 15 | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 |
|* 13 | INDEX UNIQUE SCAN | I_USER1 | 0 | 1 | | 0 (0)| | 0 |00:00:00.01 | 0 |
|* 14 | INDEX RANGE SCAN | I_OBJ2 | 0 | 1 | 35 | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 |
|* 15 | VIEW PUSHED PREDICATE | TABSUBPARTV$ | 0 | 1 | 28 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 |
| 16 | WINDOW BUFFER | | 0 | 32 | 480 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 |
|* 17 | TABLE ACCESS BY INDEX ROWID | TABSUBPART$ | 0 | 32 | 480 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 |
|* 18 | INDEX RANGE SCAN | I_TABSUBPART_POBJSUBPART$ | 0 | 32 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 |
|* 19 | INDEX RANGE SCAN | I_OBJ1 | 0 | 1 | | 1 (0)| 00:00:01 | 0 |00:00:00.01 | 0 |
|* 20 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 0 | 1 | 12 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 0 |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$F5BB74E1
9 - SEL$F5BB74E1 / S@SEL$1
10 - SEL$F5BB74E1 / S@SEL$1
12 - SEL$F5BB74E1 / U@SEL$2
13 - SEL$F5BB74E1 / U@SEL$2
14 - SEL$F5BB74E1 / OP@SEL$2
15 - SEL$B01C6807 / TSP@SEL$2
16 - SEL$B01C6807
17 - SEL$B01C6807 / TABSUBPART$@SEL$3
18 - SEL$B01C6807 / TABSUBPART$@SEL$3
19 - SEL$F5BB74E1 / OS@SEL$2
20 - SEL$F5BB74E1 / OS@SEL$2
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
OPT_PARAM('_fix_control' '32614157:1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$B01C6807")
PUSH_PRED(@"SEL$F5BB74E1" "TSP"@"SEL$2" 14)
OUTLINE_LEAF(@"SEL$F5BB74E1")
MERGE(@"SEL$2" >"SEL$1")
OUTLINE_LEAF(@"INS$1")
OUTLINE(@"SEL$3")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$2")
FULL(@"INS$1" "DBMS_STATS_ID_MAP_TAB"@"INS$1")
INDEX_RS_ASC(@"SEL$F5BB74E1" "S"@"SEL$1" ("ALL_TABLE_STATS"."STATID" "ALL_TABLE_STATS"."TYPE" "ALL_TABLE_STATS"."C5" "ALL_TABLE_STATS"."C1"
"ALL_TABLE_STATS"."C2" "ALL_TABLE_STATS"."C3" "ALL_TABLE_STATS"."C4" "ALL_TABLE_STATS"."VERSION"))
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$F5BB74E1" "S"@"SEL$1")
INDEX_RS_ASC(@"SEL$F5BB74E1" "U"@"SEL$2" ("USER$"."NAME"))
INDEX(@"SEL$F5BB74E1" "OP"@"SEL$2" ("OBJ$"."OWNER#" "OBJ$"."NAME" "OBJ$"."NAMESPACE" "OBJ$"."REMOTEOWNER" "OBJ$"."LINKNAME" "OBJ$"."SUBNAME"
"OBJ$"."TYPE#" "OBJ$"."SPARE3" "OBJ$"."OBJ#"))
NO_ACCESS(@"SEL$F5BB74E1" "TSP"@"SEL$2")
INDEX(@"SEL$F5BB74E1" "OS"@"SEL$2" ("OBJ$"."OBJ#" "OBJ$"."OWNER#" "OBJ$"."TYPE#"))
LEADING(@"SEL$F5BB74E1" "S"@"SEL$1" "U"@"SEL$2" "OP"@"SEL$2" "TSP"@"SEL$2" "OS"@"SEL$2")
USE_MERGE_CARTESIAN(@"SEL$F5BB74E1" "U"@"SEL$2")
USE_NL(@"SEL$F5BB74E1" "OP"@"SEL$2")
USE_NL(@"SEL$F5BB74E1" "TSP"@"SEL$2")
USE_NL(@"SEL$F5BB74E1" "OS"@"SEL$2")
NLJ_BATCHING(@"SEL$F5BB74E1" "OS"@"SEL$2")
USE_HASH_AGGREGATION(@"SEL$F5BB74E1")
INDEX_RS_ASC(@"SEL$B01C6807" "TABSUBPART$"@"SEL$3" ("TABSUBPART$"."POBJ#" "TABSUBPART$"."SUBPART#"))
END_OUTLINE_DATA
*/
Peeked Binds (identified by position):
--------------------------------------
1 - :1 (VARCHAR2(30), CSID=846): 'SYS'
2 - :2 (VARCHAR2(30), CSID=846): 'ACCESS$'
Predicate Information (identified by operation id):
---------------------------------------------------
9 - filter("S"."N13" IS NOT NULL)
10 - access("S"."STATID" IS NULL AND (("S"."TYPE"='B' OR "S"."TYPE"='C' OR "S"."TYPE"='E' OR "S"."TYPE"='G' OR "S"."TYPE"='H' OR "S"."TYPE"='L' OR
"S"."TYPE"='M' OR "S"."TYPE"='P' OR "S"."TYPE"='T' OR "S"."TYPE"='U' OR "S"."TYPE"='c' OR "S"."TYPE"='t')) AND "S"."C5"=:1 AND "S"."C1"=:2 AND "S"."C3"
LIKE 'SYS_%')
filter(("S"."C2" IS NOT NULL AND "S"."C1"=:2 AND "S"."C5"=:1 AND "S"."C3" LIKE 'SYS_%' AND INTERNAL_FUNCTION("S"."TYPE")))
13 - access("U"."NAME"=:1)
14 - access("U"."USER#"="OP"."OWNER#" AND "OP"."NAME"=:2 AND "S"."C2"="OP"."SUBNAME" AND "OP"."TYPE#"=19)
filter(("OP"."SUBNAME" IS NOT NULL AND "S"."C2"="OP"."SUBNAME" AND "OP"."TYPE#"=19))
15 - filter("S"."N13"="TSP"."SUBPART#")
17 - filter(BITAND("FLAGS",8388608)=0)
18 - access("POBJ#"="OP"."OBJ#")
19 - access("TSP"."OBJ#"="OS"."OBJ#" AND "OS"."TYPE#"=34)
filter("OS"."TYPE#"=34)
20 - filter("S"."C3"<>"OS"."SUBNAME")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
2 - "S"."C5"[VARCHAR2,128], "S"."C1"[VARCHAR2,128], "S"."C2"[VARCHAR2,128], "S"."C3"[VARCHAR2,128], "OS"."SUBNAME"[VARCHAR2,128]
3 - "S".ROWID[ROWID,10], "S"."STATID"[VARCHAR2,128], "S"."TYPE"[CHARACTER,1], "S"."C1"[VARCHAR2,128], "S"."C2"[VARCHAR2,128], "S"."C3"[VARCHAR2,128],
"S"."C5"[VARCHAR2,128], "S"."N13"[NUMBER,22], "U".ROWID[ROWID,10], "U"."USER#"[NUMBER,22], "U"."NAME"[VARCHAR2,128], "OP".ROWID[ROWID,10],
"OP"."OBJ#"[NUMBER,22], "OP"."OWNER#"[NUMBER,22], "OP"."NAME"[VARCHAR2,128], "OP"."SUBNAME"[VARCHAR2,128], "OP"."TYPE#"[NUMBER,22],
"TSP"."OBJ#"[NUMBER,22], "TSP"."SUBPART#"[NUMBER,22], "OS".ROWID[ROWID,10], "OS"."OBJ#"[NUMBER,22], "OS"."SUBNAME"[VARCHAR2,128], "OS"."TYPE#"[NUMBER,22]
4 - "S".ROWID[ROWID,10], "S"."STATID"[VARCHAR2,128], "S"."TYPE"[CHARACTER,1], "S"."C1"[VARCHAR2,128], "S"."C2"[VARCHAR2,128], "S"."C3"[VARCHAR2,128],
"S"."C5"[VARCHAR2,128], "S"."N13"[NUMBER,22], "U".ROWID[ROWID,10], "U"."USER#"[NUMBER,22], "U"."NAME"[VARCHAR2,128], "OP".ROWID[ROWID,10],
"OP"."OBJ#"[NUMBER,22], "OP"."OWNER#"[NUMBER,22], "OP"."NAME"[VARCHAR2,128], "OP"."SUBNAME"[VARCHAR2,128], "OP"."TYPE#"[NUMBER,22],
"TSP"."OBJ#"[NUMBER,22], "TSP"."SUBPART#"[NUMBER,22], "OS".ROWID[ROWID,10], "OS"."OBJ#"[NUMBER,22], "OS"."TYPE#"[NUMBER,22]
5 - "S".ROWID[ROWID,10], "S"."STATID"[VARCHAR2,128], "S"."TYPE"[CHARACTER,1], "S"."C1"[VARCHAR2,128], "S"."C2"[VARCHAR2,128], "S"."C3"[VARCHAR2,128],
"S"."C5"[VARCHAR2,128], "S"."N13"[NUMBER,22], "U".ROWID[ROWID,10], "U"."USER#"[NUMBER,22], "U"."NAME"[VARCHAR2,128], "OP".ROWID[ROWID,10],
"OP"."OBJ#"[NUMBER,22], "OP"."OWNER#"[NUMBER,22], "OP"."NAME"[VARCHAR2,128], "OP"."SUBNAME"[VARCHAR2,128], "OP"."TYPE#"[NUMBER,22],
"TSP"."OBJ#"[NUMBER,22], "TSP"."SUBPART#"[NUMBER,22]
6 - "S".ROWID[ROWID,10], "S"."STATID"[VARCHAR2,128], "S"."TYPE"[CHARACTER,1], "S"."C1"[VARCHAR2,128], "S"."C2"[VARCHAR2,128], "S"."C3"[VARCHAR2,128],
"S"."C5"[VARCHAR2,128], "S"."N13"[NUMBER,22], "U".ROWID[ROWID,10], "U"."USER#"[NUMBER,22], "U"."NAME"[VARCHAR2,128], "OP".ROWID[ROWID,10],
"OP"."OBJ#"[NUMBER,22], "OP"."OWNER#"[NUMBER,22], "OP"."NAME"[VARCHAR2,128], "OP"."SUBNAME"[VARCHAR2,128], "OP"."TYPE#"[NUMBER,22]
7 - "S".ROWID[ROWID,10], "S"."STATID"[VARCHAR2,128], "S"."TYPE"[CHARACTER,1], "S"."C1"[VARCHAR2,128], "S"."C2"[VARCHAR2,128], "S"."C3"[VARCHAR2,128],
"S"."C5"[VARCHAR2,128], "S"."N13"[NUMBER,22], "U".ROWID[ROWID,10], "U"."USER#"[NUMBER,22], "U"."NAME"[VARCHAR2,128]
8 - "S".ROWID[ROWID,10], "S"."STATID"[VARCHAR2,128], "S"."TYPE"[CHARACTER,1], "S"."C1"[VARCHAR2,128], "S"."C2"[VARCHAR2,128], "S"."C3"[VARCHAR2,128],
"S"."C5"[VARCHAR2,128], "S"."N13"[NUMBER,22]
9 - "S".ROWID[ROWID,10], "S"."STATID"[VARCHAR2,128], "S"."TYPE"[CHARACTER,1], "S"."C1"[VARCHAR2,128], "S"."C2"[VARCHAR2,128], "S"."C3"[VARCHAR2,128],
"S"."C5"[VARCHAR2,128], "S"."N13"[NUMBER,22]
10 - "S".ROWID[ROWID,10], "S"."STATID"[VARCHAR2,128], "S"."TYPE"[CHARACTER,1], "S"."C5"[VARCHAR2,128], "S"."C1"[VARCHAR2,128], "S"."C2"[VARCHAR2,128],
"S"."C3"[VARCHAR2,128]
11 - (#keys=0) "U".ROWID[ROWID,10], "U"."USER#"[NUMBER,22], "U"."NAME"[VARCHAR2,128]
12 - "U".ROWID[ROWID,10], "U"."USER#"[NUMBER,22], "U"."NAME"[VARCHAR2,128]
13 - "U".ROWID[ROWID,10], "U"."NAME"[VARCHAR2,128]
14 - "OP".ROWID[ROWID,10], "OP"."OBJ#"[NUMBER,22], "OP"."OWNER#"[NUMBER,22], "OP"."NAME"[VARCHAR2,128], "OP"."SUBNAME"[VARCHAR2,128],
"OP"."TYPE#"[NUMBER,22]
15 - "TSP"."OBJ#"[NUMBER,22], "TSP"."SUBPART#"[NUMBER,22]
16 - (#keys=2) "POBJ#"[NUMBER,22], "SUBPART#"[NUMBER,22], "TABSUBPART$".ROWID[ROWID,10], "OBJ#"[NUMBER,22], "FLAGS"[NUMBER,22], ROW_NUMBER() OVER (
PARTITION BY "POBJ#" ORDER BY "SUBPART#")[22]
17 - "TABSUBPART$".ROWID[ROWID,10], "OBJ#"[NUMBER,22], "POBJ#"[NUMBER,22], "SUBPART#"[NUMBER,22], "FLAGS"[NUMBER,22]
18 - "TABSUBPART$".ROWID[ROWID,10], "POBJ#"[NUMBER,22], "SUBPART#"[NUMBER,22]
19 - "OS".ROWID[ROWID,10], "OS"."OBJ#"[NUMBER,22], "OS"."TYPE#"[NUMBER,22]
20 - "OS".ROWID[ROWID,10], "OS"."SUBNAME"[VARCHAR2,128]
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 3
---------------------------------------------------------------------------
0 - STATEMENT
- OPT_PARAM('_parallel_syspls_obey_force' 'false')
1 - SEL$F5BB74E1
- leading(s)
9 - SEL$F5BB74E1 / S@SEL$1
- index(s)
Query Block Registry:
---------------------
<q o="18" f="y" h="y"><n><![CDATA[SEL$F5BB74E1]]></n><p><![CDATA[SEL$1]]></p><i><o><t>VW</t><v><![CDATA[SEL$2]]></v></o></i><f><h><t><![CDATA[S]]></t><s><!
[CDATA[SEL$1]]></s></h><h><t><![CDATA[OP]]></t><s><![CDATA[SEL$2]]></s></h><h><t><![CDATA[OS]]></t><s><![CDATA[SEL$2]]></s></h><h><t><![CDATA[TSP]]></t><s>
<![CDATA[SEL$2]]></s></h><h><t><![CDATA[U]]></t><s><![CDATA[SEL$2]]></s></h></f></q>
<q o="2" f="y"><n><![CDATA[INS$1]]></n><f><h><t><![CDATA[DBMS_STATS_ID_MAP_TAB]]></t><s><![CDATA[INS$1]]></s></h></f></q>
<q o="2"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[D]]></t><s><![CDATA[SEL$1]]></s></h><h><t><![CDATA[S]]></t><s><![CDATA[SEL$1]]></s></h></f></q>
<q o="7" f="y" h="y"><n><![CDATA[SEL$B01C6807]]></n><p><![CDATA[SEL$3]]></p><f><h><t><![CDATA[TABSUBPART$]]></t><s><![CDATA[SEL$3]]></s></h></f></q>
<q o="2"><n><![CDATA[SEL$2]]></n><f><h><t><![CDATA[OP]]></t><s><![CDATA[SEL$2]]></s></h><h><t><![CDATA[OS]]></t><s><![CDATA[SEL$2]]></s></h><h><t><![CDATA[
TSP]]></t><s><![CDATA[SEL$2]]></s></h><h><t><![CDATA[U]]></t><s><![CDATA[SEL$2]]></s></h></f></q>
<q o="2"><n><![CDATA[SEL$3]]></n><f><h><t><![CDATA[TABSUBPART$]]></t><s><![CDATA[SEL$3]]></s></h></f></q>
191 rows selected.
|
Predicate Information을 보면 statid가 is null로 들어오는데 plan을 보면 정상적으로 inlist 방식으로 풀림
v$session_longops 확인
|
1
2
3
4
5
6
7
8
9
10
|
SQL>
set lines 200 pages 1000
col opname for a30
select sid, serial#, opname, sofar, totalwork
from v$session_longops
where sofar != totalwork;
SID SERIAL# OPNAME SOFAR TOTALWORK
---------- ---------- ------------------------------ ---------- ----------
19 59620 Import Database Statistics 1569 11857
|
막 시작했는데 빠르게 sofar 값이 증가하고 있음
통계정보 import 작업이 정상적으로 빠르게 수행되고 있음
결론 :
대량의 통계정보를 이관하는 경우 statid를 넣어주거나 statid를 넣지 않은 경우 세션 레벨로 32614157 버그 fix 구문을 수행해주면 정상적으로 빠르게 import 할 수 있음
참고로 이 버그는 23ai 버전에서는 개선되었다고 함
참조 :
오라클 19c 통계정보 이관 방법 ( https://positivemh.tistory.com/1265 )
오라클 19c 유저 100개, 테이블 100개, 인덱스 100개, 데이터 1000건 생성 ( https://positivemh.tistory.com/1266 )
오라클 19c 통계정보 대량 이관시 병렬 처리 ( https://positivemh.tistory.com/1267 )
오라클 19c 통계정보 대량 이관 분석 및 속도 개선 ( https://positivemh.tistory.com/1268 )
'ORACLE > Performance Tuning ' 카테고리의 다른 글
| 오라클 19c 인덱스 선두컬럼 값이 null인 경우 성능 테스트 (0) | 2025.08.14 |
|---|---|
| 오라클 19c 오브젝트 100만개 생성 및 shared pool 확인 테스트 (0) | 2025.05.26 |
| 오라클 19c Insert 시 Undo TS Autoextend 옵션에 따른 성능 차이 분석 (2) | 2025.05.05 |
| 오라클 19c 인덱스 nologging 사용시 append insert 속도 차이 확인 (0) | 2025.04.27 |
| 오라클 19c Insert 시 Autoextend 옵션에 따른 성능 차이 분석 (2) | 2025.02.25 |