OS 환경 : Oracle Linux 8.1 (64bit)
DB 환경 : Oracle Database 19.3.0.0
방법 : 오라클 19c temp 사용시 tempfile 내용 확인
오라클에서 temp 테이블스페이스는 대량의 정렬 연산 시 pga에서 연산을 다 못하는 경우 일시적으로 사용됨
본문에서는 pga를 작게 설정해두고 temp를 의도적으로 사용시켜 각 연산별로 temp 파일에 어떤식으로 내용이 기록되는지 확인해봄
테스트
사전 확인
1. union 쿼리 수행
2. hash 조인 쿼리 수행
3. 집계 쿼리 수행
테스트
사전 확인
temp 확인
|
1
2
3
4
5
6
7
8
9
|
SQL>
set lines 200 pages 1000
col tablespace_name for a20
col file_name for a40
select tablespace_name, file_id, file_name, bytes/1024/1024 mb, maxbytes/1024/1024 max_mb, autoextensible from dba_temp_files;
TABLESPACE_NAME FILE_ID FILE_NAME MB MAX_MB AUT
-------------------- ---------- ---------------------------------------- ---------- ---------- ---
TEMP 1 /oradata1/ORACLE19/temp01.dbf 32 32767.9844 YES
|
현재 temp는 32mb이고 32gb까지 자동증가 가능한 상태임
현재 상태의 temp 파일 내용 확인
|
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
|
$ strings /oradata1/ORACLE19/temp01.dbf > /home/oracle/temp_before.txt
$ cat /home/oracle/temp_before.txt
}|{z
ORACLE19
TEMP
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:epsg="http://www.oracle.com/2004/spatial/epsg/gridfile/schema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<xsl:output method="text"/>
<xsl:param name="coordinatePosition">1</xsl:param>
<xsl:template match="epsg:GridFile">
<xsl:text><![CDATA[NUM_OREC]]></xsl:text>
<xsl:text><![CDATA[ 11]]></xsl:text>
<xsl:text><![CDATA[
]]></xsl:text>
<xsl:text><![CDATA[NUM_SREC]]></xsl:text>
<xsl:text><![CDATA[ 11]]></xsl:text>
<xsl:text><![CDATA[
]]></xsl:text>
<xsl:text><![CDATA[NUM_FILE]]></xsl:text>
<xsl:call-template name="right-align">
<xsl:with-param name="string" select="count(epsg:Grids/epsg:Grid)"/>
<xsl:with-param name="length" select="3"/>
</xsl:call-template>
<xsl:text><![CDATA[
]]></xsl:text>
<xsl:text><![CDATA[GS_TYPE ]]></xsl:text>
<xsl:text><![CDATA[SECONDS ]]></xsl:text>
<xsl:text><![CDATA[
]]></xsl:text>
<xsl:text><![CDATA[VERSION ]]></xsl:text>
<xsl:text><![CDATA[NTv2.0 ]]></xsl:text>
<xsl:text><![CDATA[
]]></xsl:text>
<xsl:text><![CDATA[DATUM_F ]]></xsl:text>
<xsl:text><![CDATA[NAD27 ]]></xsl:text>
<xsl:text><![CDATA[
]]></xsl:text>
<xsl:text><![CDATA[DATUM_T ]]></xsl:text>
<xsl:text><![CDATA[NAD83 ]]></xsl:text>
<xsl:text><![CDATA[
]]></xsl:text>
<xsl:text><![CDATA[MAJOR_F ]]></xsl:text>
<xsl:text><![CDATA[ 6378206.400]]></xsl:text>
<xsl:text><![CDATA[
]]></xsl:text>
<xsl:text><![CDATA[MINOR_F ]]></xsl:text>
<xsl:text><![CDATA[ 6356583.800]]></xsl:text>
<xsl:text><![CDATA[
]]></xsl:text>
<xsl:text><![CDATA[MAJOR_T ]]></xsl:text>
<xsl:text><![CDATA[ 6378137.000]]></xsl:text>
<xsl:text><![CDATA[
]]></xsl:text>
<xsl:text><![CDATA[MINOR_T ]]></xsl:text>
<xsl:text><![CDATA[ 6356752.314]]></xsl:text>
<xsl:text><![CDATA[
]]></xsl:text>
<xsl:apply-templates select="epsg:Grids/epsg:Grid"/>
<xsl:text><![CDATA[END 0.33E+33]]></xsl:text>
<xsl:text><![CDATA[
]]></xsl:text>
</xsl:template>
<xsl:template match="epsg:Grid">
<xsl:variable name="minLon" select="epsg:GridProperties/epsg:MinCoords/epsg:Coordinate[position()=1]"/>
<xsl:variable name="minLat" select="epsg:GridProperties/epsg:MinCoords/epsg:Coordinate[position()=2]"/>
<xsl:variable name="maxLon" select="epsg:GridProperties/epsg:MaxCoords/epsg:Coordinate[position()=1]"/>
<xsl:variable name="maxLat" select="epsg:GridProperties/epsg:MaxCoords/epsg:Coordinate[position()=2]"/>
<xsl:variable name="spacingLon" select="epsg:GridProperties/epsg:CoordSpacing/epsg:Coordinate[position()=1]"/>
<xsl:variable name="spacingLat" select="epsg:GridProperties/epsg:CoordSpacing/epsg:Coordinate[position()=2]"/>
<xsl:variable name="parentName" select="epsg:HierarchyParticipation/epsg:Parent[epsg:HierarchyType='http://www.oracle.com/2004/spatial/epsg/gridfile/hierarchical-organization/NTv2']/epsg:ParentNode"/>
<xsl:variable name="gridName" select="@gridName"/>
<xsl:variable name="creationDateTime" select="@creation"/>
<xsl:variable name="updateDateTime" select="@update"/>
<xsl:text><![CDATA[SUB_NAME]]></xsl:text>
<xsl:call-template name="left-align">
<xsl:with-param name="string" select="$gridName"/>
<xsl:with-param name="length" select="8"/>
</xsl:call-template>
<xsl:text><![CDATA[
]]></xsl:text>
<xsl:text><![CDATA[PARENT ]]></xsl:text>
<xsl:choose>
<xsl:when test="normalize-space($parentName) != ''">
<xsl:call-template name="left-align">
<xsl:with-param name="string" select="$parentName"/>
<xsl:with-param name="length" select="8"/>
</xsl:call-template>
</xsl:when>
<xsl:otherwise>
<xsl:text><![CDATA[NONE ]]></xsl:text>
</xsl:otherwise>
</xsl:choose>
<xsl:text><![CDATA[
]]></xsl:text>
<xsl:text><![CDATA[CREATED ]]></xsl:text>
<xsl:value-of select="substring($creationDateTime, 3, 8)"/>
<xsl:text><![CDATA[
]]></xsl:text>
<xsl:text><![CDATA[UPDATED ]]></xsl:text>
<xsl:value-of select="substring($updateDateTime, 3, 8)"/>
<xsl:text><![CDATA[
]]></xsl:text>
<xsl:text><![CDATA[S_LAT ]]></xsl:text>
<xsl:call-template name="right-align">
<xsl:with-param name="string" select="format-number(3600 * $minLat, '#.000000')"/>
<xsl:with-param name="length" select="15"/>
</xsl:call-template>
<xsl:text><![CDATA[
]]></xsl:text>
<xsl:text><![CDATA[N_LAT ]]></xsl:text>
<xsl:call-template name="right-align">
<xsl:with-param name="string" select="format-number(3600 * $maxLat, '#.000000')"/>
<xsl:with-param name="length" select="15"/>
</xsl:call-template>
<xsl:text><![CDATA[
]]></xsl:text>
<xsl:text><![CDATA[E_LONG ]]></xsl:text>
<xsl:call-template name="right-align">
<xsl:with-param name="string" select="format-number(-3600 * $maxLon, '#.000000')"/>
<xsl:with-param name="length" select="15"/>
</xsl:call-template>
<xsl:text><![CDATA[
]]></xsl:text>
<xsl:text><![CDATA[W_LONG ]]></xsl:text>
<xsl:call-template name="right-align">
<xsl:with-param name="string" select="format-number(-3600 * $minLon, '#.000000')"/>
<xsl:with-param name="length" select="15"/>
</xsl:call-template>
<xsl:text><![CDATA[
]]></xsl:text>
<xsl:text><![CDATA[LAT_INC ]]></xsl:text>
<xsl:call-template name="right-align">
<xsl:with-param name="string" select="format-number(3600 * $spacingLat, '#.000000')"/>
<xsl:with-param name="length" select="15"/>
</xsl:call-template>
<xsl:text><![CDATA[
]]></xsl:text>
<xsl:text><![CDATA[LONG_INC]]></xsl:text>
<xsl:call-template name="right-align">
<xsl:with-param name="string" select="format-number(3600 * $spacingLon, '#.000000')"/>
<xsl:with-param name="length" select="15"/>
</xsl:call-template>
<xsl:text><![CDATA[
]]></xsl:text>
<xsl:text><![CDATA[GS_COUNT]]></xsl:text>
<xsl:call-template name="right-align">
<xsl:with-param name="string" select="count(epsg:GridData//epsg:GridNode)"/>
<xsl:with-param name="length" select="6"/>
</xsl:call-template>
<xsl:text><![CDATA[
]]></xsl:text>
<xsl:apply-templates select="epsg:GridData/epsg:GridRow"/>
</xsl:template>
<xsl:template match="epsg:GridRow">
<xsl:for-each select="epsg:GridNode">
<xsl:apply-templates select="epsg:Offset/epsg:Coordinate[position() = 2]"/>
<xsl:apply-templates select="epsg:Offset/epsg:Coordinate[position() = 1]"/>
<xsl:apply-templates select="epsg:OffsetPrecision/epsg:Coordinate[position() = 2]"/>
<xsl:apply-templates select="epsg:OffsetPrecision/epsg:Coordinate[position() = 1]"/>
<xsl:text><![CDATA[
]]></xsl:text>
</xsl:for-each>
</xsl:template>
<xsl:template match="epsg:Coordinate">
<xsl:call-template name="right-align">
<xsl:with-param name="string" select="format-number((3600 * .), '#0.000000')"/>
<xsl:with-param name="length" select="10"/>
</xsl:call-template>
</xsl:template>
<xsl:template name="right-align">
<xsl:param name="string" select="'Not Available'" />
<xsl:param name="length" select="12" />
<xsl:variable name="valueLength" select="string-length($string)"/>
<xsl:variable name="paddingLength" select="($length - $valueLength)"/>
<xsl:value-of select="substring(' ', 1, $paddingLength)"/>
<xsl:value-of select="$string"/>
</xsl:template>
<xsl:template name="left-align">
<xsl:param name="string" select="'Not Available'" />
<xsl:param name="length" select="12" />
<xsl:variable name="valueLength" select="string-length($string)"/>
<xsl:variable name="paddingLength" select="($length - $valueLength)"/>
<xsl:value-of select="$string"/>
<xsl:value-of select="substring(' ', 1, $paddingLength)"/>
</xsl:template>
</xsl:stylesheet>
|
xslt 형식의 내용이 들어가 있음
pga 파라미터 최소화
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
SQL> show parameter pga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_limit big integer 2G
pga_aggregate_target big integer 400M
SQL> alter system set pga_aggregate_target = 10m;
System altered.
SQL> show parameter pga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_limit big integer 2G
pga_aggregate_target big integer 10M
|
최소화됨
*pga_aggregate_limit은 2gb가 최소값이어서 수정하지 않음
1. union 쿼리 수행
dba_objects 뷰의 count 확인
|
1
2
3
4
5
|
SQL> select count(*) from dba_objects;
COUNT(*)
----------
72367
|
7만2천개 정도 존재함
대량 union 쿼리 수행(300번 union)
|
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
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
|
SQL>
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects union
select owner, object_name, object_type from dba_objects;
|
쿼리 수행시 alert log 모니터링
|
1
2
3
4
5
6
7
8
9
10
|
$ tail -300f /ORA19/app/oracle/diag/rdbms/oracle19/oracle19/trace/alert_oracle19.log
2025-03-09T16:15:44.202148+09:00
Resize operation completed for file# 201, old size 32768K, new size 33792K
Resize operation completed for file# 201, old size 33792K, new size 34816K
Resize operation completed for file# 201, old size 34816K, new size 35840K
Resize operation completed for file# 201, old size 35840K, new size 36864K
Resize operation completed for file# 201, old size 36864K, new size 37888K
..
Resize operation completed for file# 201, old size 1230848K, new size 1231872K
Resize operation completed for file# 201, old size 1231872K, new size 1232896K
|
temp 파일이 1gb 이상까지 자동 증가됨
쿼리가 수행 완료된 직후 temp 파일 내용 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
$ strings /oradata1/ORACLE19/temp01.dbf > /home/oracle/temp_after1.txt
$ head -12 /home/oracle/temp_after1.txt
}|{z
ORACLE19
TEMP
SYSI
com/sun/tools/internal/xjc/generator/annotation/spec/XmlRootElementWriter
JAVA CLASS[
SYSH
com/sun/tools/internal/xjc/generator/annotation/spec/XmlSchemaTypeWriter
JAVA CLASSt\
SYSI
com/sun/tools/internal/xjc/generator/annotation/spec/XmlSchemaTypesWriter
JAVA CLASSW
|
union 쿼리에서 조회한 내용들이 temp 파일 안에서 확인됨
기존 xslt 형식은 사라짐
한 오브젝트당 SYSI(오너명+I), com/sun/~(오브젝트 명), JAVA CLASS(오브젝트 타입) 이렇게 3줄이 생김
전체 라인수 조회
|
1
2
|
$ wc -l /home/oracle/temp_after1.txt
58989787 /home/oracle/temp_after1.txt
|
5800만건 정도가 temp 파일에 쌓임
이 라인수를 3(오브젝트당 3줄)으로 나누고 dba_objects 뷰 count 값으로 나누어보면 아래와 같음
58989787 / 3 / 72367 = 271.71
dba_objects 가 300번 union 된 값이 대부분 temp 파일에 기록되어 있는것을 알수있음
이방식만 보면 temp는 상당히 비효율적인 방식으로 수행되는듯함
다음 테스트를 위해 temp 재생성
|
1
2
3
4
5
6
7
|
SQL>
create temporary tablespace temp2 tempfile '/oradata1/ORACLE19/temp02.dbf' size 32m autoextend on;
alter database default temporary tablespace temp2;
drop tablespace temp including contents and datafiles;
create temporary tablespace temp tempfile '/oradata1/ORACLE19/temp01.dbf' size 32m autoextend on;
alter database default temporary tablespace temp;
drop tablespace temp2 including contents and datafiles;
|
2. hash 조인 쿼리 수행
pga 많이 쓰는 hash 조인쿼리 사용
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
SQL>
select /*+ use_hash(ds1 ds2 ds3 ds4)
full(ds1) full(ds2) full(ds3) full(ds4) */
ds1.segment_name as seg1_name,
ds2.segment_name as seg2_name,
ds3.segment_name as seg3_name,
ds4.segment_name as seg4_name,
ds1.segment_type, ds2.segment_type, ds3.segment_type, ds4.segment_type,
ds1.bytes + ds2.bytes + ds3.bytes + ds4.bytes as total_size
from dba_segments ds1, dba_segments ds2, dba_segments ds3, dba_segments ds4
where ds1.segment_name = ds2.segment_name
and ds2.segment_name = ds3.segment_name
and ds3.segment_name = ds4.segment_name
and ds1.bytes > 1
and ds2.bytes > 1
and ds3.bytes > 1
and ds4.bytes > 1
order by total_size desc;
|
쿼리 수행시 alert log 모니터링
|
1
2
3
4
5
6
7
8
9
10
|
$ tail -300f /ORA19/app/oracle/diag/rdbms/oracle19/oracle19/trace/alert_oracle19.log
2025-03-09T16:52:43.542089+09:00
Resize operation completed for file# 201, old size 32768K, new size 33792K
Resize operation completed for file# 201, old size 33792K, new size 34816K
Resize operation completed for file# 201, old size 34816K, new size 35840K
Resize operation completed for file# 201, old size 35840K, new size 36864K
..
Resize operation completed for file# 201, old size 4222976K, new size 4224000K
Resize operation completed for file# 201, old size 4224000K, new size 4225024K
..
|
temp 파일이 4gb 이상까지 자동 증가되고 있음
수행중인 쿼리를 끊고 temp 파일 내용 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
$ strings /oradata1/ORACLE19/temp01.dbf > /home/oracle/temp_after2.txt
$ head -12 /home/oracle/temp_after2.txt
}|{z
ORACLE19
TEMP
IDL_UB1$
IDL_UB1$
IDL_UB1$
TABLE
TABLE
TABLE
3d2-
C_TOID_VERSION#
C_TOID_VERSION#
|
내부적인 뷰들이 파일에 쌓임
전체 라인수 조회
|
1
2
|
$ wc -l /home/oracle/temp_after2.txt
215072183 /home/oracle/temp_after2.txt
|
2억건 정도가 temp 파일에 쌓임
다음 테스트를 위해 temp 재생성
|
1
2
3
4
5
6
7
|
SQL>
create temporary tablespace temp2 tempfile '/oradata1/ORACLE19/temp02.dbf' size 32m autoextend on;
alter database default temporary tablespace temp2;
drop tablespace temp including contents and datafiles;
create temporary tablespace temp tempfile '/oradata1/ORACLE19/temp01.dbf' size 32m autoextend on;
alter database default temporary tablespace temp;
drop tablespace temp2 including contents and datafiles;
|
3. 집계 쿼리 수행
대량 집계쿼리 수행
|
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
|
SQL>
select /*+ use_nl(ds1 ds2 ds3 ds4 ds5 ds6 ds7 ds8 ds9 ds10) */
ds1.owner,
ds1.segment_type,
count(*) as segment_count,
sum(ds1.bytes + ds2.bytes + ds3.bytes + ds4.bytes + ds5.bytes +
ds6.bytes + ds7.bytes + ds8.bytes + ds9.bytes + ds10.bytes) as total_bytes,
avg(ds1.bytes + ds2.bytes + ds3.bytes + ds4.bytes + ds5.bytes +
ds6.bytes + ds7.bytes + ds8.bytes + ds9.bytes + ds10.bytes) as avg_bytes,
max(ds1.bytes + ds2.bytes + ds3.bytes + ds4.bytes + ds5.bytes +
ds6.bytes + ds7.bytes + ds8.bytes + ds9.bytes + ds10.bytes) as max_bytes,
min(ds1.bytes + ds2.bytes + ds3.bytes + ds4.bytes + ds5.bytes +
ds6.bytes + ds7.bytes + ds8.bytes + ds9.bytes + ds10.bytes) as min_bytes,
sum(ds1.bytes + ds2.bytes + ds3.bytes + ds4.bytes + ds5.bytes +
ds6.bytes + ds7.bytes + ds8.bytes + ds9.bytes + ds10.bytes) as total
from dba_segments ds1, dba_segments ds2, dba_segments ds3,
dba_segments ds4, dba_segments ds5, dba_segments ds6,
dba_segments ds7, dba_segments ds8, dba_segments ds9,
dba_segments ds10
where ds1.segment_name = ds2.segment_name
and ds2.segment_name = ds3.segment_name
and ds3.segment_name = ds4.segment_name
and ds4.segment_name = ds5.segment_name
and ds5.segment_name = ds6.segment_name
and ds6.segment_name = ds7.segment_name
and ds7.segment_name = ds8.segment_name
and ds8.segment_name = ds9.segment_name
and ds9.segment_name = ds10.segment_name
and ds1.bytes > 1
and ds2.bytes > 1
and ds3.bytes > 1
and ds4.bytes > 1
and ds5.bytes > 1
and ds6.bytes > 1
and ds7.bytes > 1
and ds8.bytes > 1
and ds9.bytes > 1
and ds10.bytes > 1
group by ds1.owner, ds1.segment_type
order by total_bytes desc;
|
쿼리 수행시 alert log 모니터링
|
1
2
|
$ tail -300f /ORA19/app/oracle/diag/rdbms/oracle19/oracle19/trace/alert_oracle19.log
(로그 발생안함)
|
앞선 테스트와는 다르게 temp 파일이 자동 증가되지 않음
이유 :
윈도우 함수가 없고 집계함수만 존재하기 때문에 집계 대상(group by절에 있는 컬럼 값 종류)만 많지 않다면
대상 row가 아무리 많아도 temp 테이블스페이스를 사용하지 않음, 메모리(pga)에서 모두 처리할수 있음
친절한 sql 튜닝 335p Sort Group By 파트 참고
Sort Group By에서 메모지를 찾기 위해 소트 알고리즘을 사용했다면, Hash Group By는 해싱 알고리즘을 사용한다.
읽는 레코드마다 Group By 컬럼의 해시 값으로 해시 버킷을 찾아 그룹별로 집계항목을 갱신하는 방식이다.
부서(그룹 개수)가 많지 않다면 집계할 대상 레코드가 아무리 많아도 temp 테이블스페이스 쓸 일이 전혀 없다.
결론 :
temp 사용량 비교
union 쿼리 : temp 파일이 1gb 이상 증가되고 union 대상 sql 실행 결과 내용이 기록됨
hash 조인 쿼리 : temp 파일이 4gb 이상 증가되고 내부적인 시스템 뷰와 데이터가 저장됨
집계 쿼리 : temp 파일이 자동증가 되지 않고(미사용), pga내에서만 집계 연산이 이루어짐
오라클에서 temp 테이블스페이스는 특정 sql 연산 시 매우 비효율적으로 사용되어 성능에 영향을 미칠 수 있음
temp 사용량을 줄이려면 불필요한 union, order by 등 정렬 작업이 있는 쿼리를 수정하고 필요시 pga 크기를 조정해야함
참조 :
bug 5723140
친절한 sql 튜닝 335p
'ORACLE > Admin' 카테고리의 다른 글
| 오라클 19c templates 경로의 seeddata 데이터베이스 (3) | 2025.04.20 |
|---|---|
| 오라클 23ai 신기능 Select AI 사용 테스트(non-adb) (0) | 2025.04.17 |
| 오라클 19c dbca silent 시 템플릿별 컴포넌트 비활성화 설정 (0) | 2025.04.14 |
| 오라클 23ai 신기능 dbms_cloud 설치(non-adb) (0) | 2025.04.13 |
| 오라클 19c 유저별 테이블스페이스 quota 확인 및 변경 (0) | 2025.04.07 |