OS 환경 : Oracle Linux 7.6 (64bit)
DB 환경 : Oracle Database 19.3.0.0
방법 : 오라클 19c ordered_predicates 힌트 사용 확인(XPLAN, 10053 비교)
오라클에는 ordered_predicates 라는 힌트가 존재함
이 힌트는 쿼리의 where 절에 포함된 조건들이 지정된 순서대로 평가되도록 강제함
일반적으로 오라클 옵티마이저는 조건들을 최적의 순서로 평가하여 쿼리 성능을 최적화하려고 하는데 특정 상황에서는 사용자가 명시한 순서대로 조건을 평가하는 것이 더 효율적일 수 있음
이 경우 ordered_predicates 힌트를 사용하면 옵티마이저에게 조건 평가 순서를 강제하도록 명령할 수 있음
본문에서는 이 힌트가 19c에서도 제대로 동작하는지 여부를 xplan 과 10053 트레이스로 확인해봄
테스트
1. xplan 확인
2. 10053 트레이스 확인
테스트
샘플 테이블 생성
|
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
|
SQL>
-- 고객 테이블 생성
create table customers (
customer_id number primary key,
name varchar2(100),
city varchar2(100),
age number
);
-- 주문 테이블 생성
create table orders (
order_id number primary key,
customer_id number,
order_date date,
amount number,
foreign key (customer_id) references customers(customer_id)
);
-- 데이터 삽입
insert into customers values (1, 'alice', 'new york', 30);
insert into customers values (2, 'bob', 'los angeles', 40);
insert into customers values (3, 'charlie', 'chicago', 25);
-- 더 많은 데이터 삽입 가능...
insert into orders values (101, 1, to_date('2023-01-01', 'yyyy-mm-dd'), 200);
insert into orders values (102, 2, to_date('2023-02-01', 'yyyy-mm-dd'), 150);
insert into orders values (103, 3, to_date('2023-03-01', 'yyyy-mm-dd'), 300);
-- 더 많은 데이터 삽입 가능...
commit;
|
1. xplan 확인
힌트 미사용 쿼리 수행 후 xplan 결과 확인
|
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
|
SQL>
alter session set statistics_level = all;
SELECT *
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE c.city = 'New York'
AND c.age > 25
AND o.amount > 100;
SQL> SELECT * FROM DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ADVANCED ALLSTATS LAST');
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID aa9vhbzahx2pv, child number 0
-------------------------------------
SELECT * FROM customers c JOIN orders o ON c.customer_id =
o.customer_id WHERE c.city = 'New York' AND c.age > 25 AND o.amount
> 100
Plan hash value: 2054468610
--------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 6 (100)| | 1 |00:00:00.01 | 12 |
| 1 | NESTED LOOPS | | 1 | 1 | 178 | 6 (0)| 00:00:01 | 1 |00:00:00.01 | 12 |
| 2 | NESTED LOOPS | | 1 | 3 | 178 | 6 (0)| 00:00:01 | 3 |00:00:00.01 | 9 |
|* 3 | TABLE ACCESS FULL | ORDERS | 1 | 3 | 144 | 3 (0)| 00:00:01 | 3 |00:00:00.01 | 7 |
|* 4 | INDEX UNIQUE SCAN | SYS_C006957 | 3 | 1 | | 0 (0)| | 3 |00:00:00.01 | 2 |
|* 5 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS | 3 | 1 | 130 | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 3 |
--------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
3 - SEL$58A6D7F6 / O@SEL$1
4 - SEL$58A6D7F6 / C@SEL$1
5 - SEL$58A6D7F6 / C@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$58A6D7F6")
MERGE(@"SEL$1" >"SEL$2")
OUTLINE(@"SEL$2")
OUTLINE(@"SEL$1")
FULL(@"SEL$58A6D7F6" "O"@"SEL$1")
INDEX(@"SEL$58A6D7F6" "C"@"SEL$1" ("CUSTOMERS"."CUSTOMER_ID"))
LEADING(@"SEL$58A6D7F6" "O"@"SEL$1" "C"@"SEL$1")
USE_NL(@"SEL$58A6D7F6" "C"@"SEL$1")
NLJ_BATCHING(@"SEL$58A6D7F6" "C"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("O"."AMOUNT">100)
4 - access("C"."CUSTOMER_ID"="O"."CUSTOMER_ID")
5 - filter(("C"."CITY"='New York' AND "C"."AGE">25))
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "O"."ORDER_ID"[NUMBER,22], "O"."CUSTOMER_ID"[NUMBER,22], "O"."ORDER_DATE"[DATE,7], "O"."AMOUNT"[NUMBER,22],
"C"."CUSTOMER_ID"[NUMBER,22], "C"."NAME"[VARCHAR2,100], "C"."CITY"[VARCHAR2,100], "C"."AGE"[NUMBER,22]
2 - "O"."ORDER_ID"[NUMBER,22], "O"."CUSTOMER_ID"[NUMBER,22], "O"."ORDER_DATE"[DATE,7], "O"."AMOUNT"[NUMBER,22],
"C".ROWID[ROWID,10], "C"."CUSTOMER_ID"[NUMBER,22]
3 - "O"."ORDER_ID"[NUMBER,22], "O"."CUSTOMER_ID"[NUMBER,22], "O"."ORDER_DATE"[DATE,7], "O"."AMOUNT"[NUMBER,22]
4 - "C".ROWID[ROWID,10], "C"."CUSTOMER_ID"[NUMBER,22]
5 - "C"."NAME"[VARCHAR2,100], "C"."CITY"[VARCHAR2,100], "C"."AGE"[NUMBER,22]
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- this is an adaptive plan
Query Block Registry:
---------------------
<q o="2"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[C]]></t><s><![CDATA[SEL$1]]></s></h><h><t><![CDATA[O]]></t><s><![CDATA[SEL$
1]]></s></h></f></q> <q o="18" f="y" h="y"><n><![CDATA[SEL$58A6D7F6]]></n><p><![CDATA[SEL$2]]></p><i><o><t>VW</t><v><![CDATA[SEL$1]]></v></o></i><f ><h><t><![CDATA[C]]></t><s><![CDATA[SEL$1]]></s></h><h><t><![CDATA[O]]></t><s><![CDATA[SEL$1]]></s></h></f></q> <q o="2"><n><![CDATA[SEL$2]]></n><f><h><t><![CDATA[from$_subquery$_003]]></t><s><![CDATA[SEL$2]]></s></h></f></q> 80 rows selected.
|
힌트 사용 쿼리 수행 후 xplan 결과 확인
|
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
|
SQL>
alter session set statistics_level = all;
SELECT /*+ ORDERED_PREDICATES */
*
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE c.city = 'New York'
AND c.age > 25
AND o.amount > 100;
SQL> SELECT * FROM DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ADVANCED ALLSTATS LAST');
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID b52a0275tgs3x, child number 0
-------------------------------------
SELECT /*+ ORDERED_PREDICATES */ * FROM customers c JOIN orders
o ON c.customer_id = o.customer_id WHERE c.city = 'New York' AND
c.age > 25 AND o.amount > 100
Plan hash value: 2054468610
--------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 6 (100)| | 1 |00:00:00.01 | 12 |
| 1 | NESTED LOOPS | | 1 | 1 | 178 | 6 (0)| 00:00:01 | 1 |00:00:00.01 | 12 |
| 2 | NESTED LOOPS | | 1 | 3 | 178 | 6 (0)| 00:00:01 | 3 |00:00:00.01 | 9 |
|* 3 | TABLE ACCESS FULL | ORDERS | 1 | 3 | 144 | 3 (0)| 00:00:01 | 3 |00:00:00.01 | 7 |
|* 4 | INDEX UNIQUE SCAN | SYS_C006957 | 3 | 1 | | 0 (0)| | 3 |00:00:00.01 | 2 |
|* 5 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS | 3 | 1 | 130 | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 3 |
--------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
3 - SEL$58A6D7F6 / O@SEL$1
4 - SEL$58A6D7F6 / C@SEL$1
5 - SEL$58A6D7F6 / C@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
ALL_ROWS
OUTLINE_LEAF(@"SEL$58A6D7F6")
MERGE(@"SEL$1" >"SEL$2")
OUTLINE(@"SEL$2")
OUTLINE(@"SEL$1")
FULL(@"SEL$58A6D7F6" "O"@"SEL$1")
INDEX(@"SEL$58A6D7F6" "C"@"SEL$1" ("CUSTOMERS"."CUSTOMER_ID"))
LEADING(@"SEL$58A6D7F6" "O"@"SEL$1" "C"@"SEL$1")
USE_NL(@"SEL$58A6D7F6" "C"@"SEL$1")
NLJ_BATCHING(@"SEL$58A6D7F6" "C"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("O"."AMOUNT">100)
4 - access("C"."CUSTOMER_ID"="O"."CUSTOMER_ID")
5 - filter(("C"."CITY"='New York' AND "C"."AGE">25))
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "O"."ORDER_ID"[NUMBER,22], "O"."CUSTOMER_ID"[NUMBER,22], "O"."ORDER_DATE"[DATE,7], "O"."AMOUNT"[NUMBER,22],
"C"."CUSTOMER_ID"[NUMBER,22], "C"."NAME"[VARCHAR2,100], "C"."CITY"[VARCHAR2,100], "C"."AGE"[NUMBER,22]
2 - "O"."ORDER_ID"[NUMBER,22], "O"."CUSTOMER_ID"[NUMBER,22], "O"."ORDER_DATE"[DATE,7], "O"."AMOUNT"[NUMBER,22],
"C".ROWID[ROWID,10], "C"."CUSTOMER_ID"[NUMBER,22]
3 - "O"."ORDER_ID"[NUMBER,22], "O"."CUSTOMER_ID"[NUMBER,22], "O"."ORDER_DATE"[DATE,7], "O"."AMOUNT"[NUMBER,22]
4 - "C".ROWID[ROWID,10], "C"."CUSTOMER_ID"[NUMBER,22]
5 - "C"."NAME"[VARCHAR2,100], "C"."CITY"[VARCHAR2,100], "C"."AGE"[NUMBER,22]
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------
1 - SEL$58A6D7F6
- ORDERED_PREDICATES
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- this is an adaptive plan
Query Block Registry:
---------------------
<q o="2"><n><![CDATA[SEL$1]]></n><f><h><t><![CDATA[C]]></t><s><![CDATA[SEL$1]]></s></h><h><t><![CDATA[O]]></t><s><![CDATA[SEL$
1]]></s></h></f></q> <q o="18" f="y" h="y"><n><![CDATA[SEL$58A6D7F6]]></n><p><![CDATA[SEL$2]]></p><i><o><t>VW</t><v><![CDATA[SEL$1]]></v></o></i><f ><h><t><![CDATA[C]]></t><s><![CDATA[SEL$1]]></s></h><h><t><![CDATA[O]]></t><s><![CDATA[SEL$1]]></s></h></f></q> <q o="2"><n><![CDATA[SEL$2]]></n><f><h><t><![CDATA[from$_subquery$_003]]></t><s><![CDATA[SEL$2]]></s></h></f></q> 87 rows selected.
|
Hint Report에 ORDERED_PREDICATES 힌트가 정상적으로 사용되었나고 나옴
하지만 xplan 결과에서 별다른 차이점은 보이지 않음
2. 10053 트레이스 확인
10053 트레이스 설정 후 힌트 미사용 쿼리 수행
|
1
2
3
4
5
6
7
8
9
|
SQL>
alter session set tracefile_identifier='10053_order1';
SELECT *
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE c.city = 'New York'
AND c.age > 25
AND o.amount > 100;
alter session set events '10053 trace name context off';
|
세션 재접속
10053 트레이스 설정 후 힌트 사용 쿼리 수행
|
1
2
3
4
5
6
7
8
9
10
|
SQL>
alter session set tracefile_identifier='10053_order2';
SELECT /*+ ORDERED_PREDICATES */
*
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE c.city = 'New York'
AND c.age > 25
AND o.amount > 100;
alter session set events '10053 trace name context off';
|
힌트 미사용시 트레이스 확인
|
1
2
3
4
5
|
$ vi /ORA19/app/oracle/diag/rdbms/oracle19/oracle19/trace/oracle19_ora_15642_10053_order1.trc
FPD: Considering simple filter push in query block SEL$58A6D7F6 (#0)
"C"."CITY"='New York' AND "C"."AGE">25 AND "O"."AMOUNT">100 AND "C"."CUSTOMER_ID"="O"."CUSTOMER_ID"
try to generate transitive predicate from check constraints for query block SEL$58A6D7F6 (#0)
finally: "C"."CITY"='New York' AND "C"."AGE">25 AND "O"."AMOUNT">100 AND "C"."CUSTOMER_ID"="O"."CUSTOMER_ID"
|
힌트 미사용시 : CITY, AGE, AMOUNT, CUSTOMER_ID 순서로 평가됨
힌트 사용시 트레이스 확인
|
1
2
3
4
5
|
$ vi /ORA19/app/oracle/diag/rdbms/oracle19/oracle19/trace/oracle19_ora_36772_10053_order2.trc
FPD: Considering simple filter push in query block SEL$58A6D7F6 (#0)
"C"."CUSTOMER_ID"="O"."CUSTOMER_ID" AND "C"."CITY"='New York' AND "C"."AGE">25 AND "O"."AMOUNT">100
try to generate transitive predicate from check constraints for query block SEL$58A6D7F6 (#0)
finally: "C"."CUSTOMER_ID"="O"."CUSTOMER_ID" AND "C"."CITY"='New York' AND "C"."AGE">25 AND "O"."AMOUNT">100
|
힌트 사용시 : CUSTOMER_ID, CITY, AGE, AMOUNT 순서로 평가됨
결론 :
ORDERED_PREDICATES 힌트가 19c에서도 잘 동작함, 하지만 xplan 에서는 Hint Report에서 사용여부만 확인 가능하고 세부적인 확인은 불가능했음
하지만 10053 트레이스에서는 조건절 평가 순서를 변경하는데 기여하는것을 확인할 수 있었음
추가로 10053은 트레이스가 길어서 모두 보기는 힘들지만 최소 아래 포인트 정도만이라도 확인할수 있으면 좋음
- 쿼리 블록 등록 정보 : 옵티마이저가 각 쿼리 블록을 어떻게 인식하고 처리하는지 확인함
예시 : Registered qb: SEL$1 0x... (PARSER)
- 변환 후 쿼리: 옵티마이저가 쿼리를 변환한 후의 상태를 보여줌
예시 : Final query after transformations:
- Predicate Information : 실행 계획의 각 단계에서 조건절이 어떻게 사용되는지를 설명함(필터 및 엑세스, 조인 조건으로 사용되는지 등)
예시 : Predicate Information (identified by operation id):
- 조건 평가 순서 (finally): 최종적으로 조건이 어떤 순서로 평가될지를 보여줌
예시: finally: "C"."CUSTOMER_ID"="O"."CUSTOMER_ID" AND "C"."CITY"='New York' AND "C"."AGE">25 AND "O"."AMOUNT">100
전체 트레이스
(10053 트레이스 전체는 너무 길어서 diff 명령을 사용해 다른 부분만 첨부함)
더보기
|
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
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
|
$ diff oracle19_ora_36685_10053_order1.trc oracle19_ora_36772_10053_order2.trc
1c1
< Trace file /ORA19/app/oracle/diag/rdbms/oracle19/oracle19/trace/oracle19_ora_36685_10053_order1.trc
---
> Trace file /ORA19/app/oracle/diag/rdbms/oracle19/oracle19/trace/oracle19_ora_36772_10053_order2.trc
14c14
< Unix process pid: 36685, image: oracle@ora19 (TNS V1-V3)
---
> Unix process pid: 36772, image: oracle@ora19 (TNS V1-V3)
17,23c17,23
< *** 2024-05-19T16:00:53.437694+09:00
< *** SESSION ID:(16.28701) 2024-05-19T16:00:53.437709+09:00
< *** CLIENT ID:() 2024-05-19T16:00:53.437712+09:00
< *** SERVICE NAME:(SYS$USERS) 2024-05-19T16:00:53.437714+09:00
< *** MODULE NAME:(SQL*Plus) 2024-05-19T16:00:53.437717+09:00
< *** ACTION NAME:() 2024-05-19T16:00:53.437720+09:00
< *** CLIENT DRIVER:(SQL*PLUS) 2024-05-19T16:00:53.437722+09:00
---
> *** 2024-05-19T16:01:12.517620+09:00
> *** SESSION ID:(16.49267) 2024-05-19T16:01:12.517634+09:00
> *** CLIENT ID:() 2024-05-19T16:01:12.517637+09:00
> *** SERVICE NAME:(SYS$USERS) 2024-05-19T16:01:12.517640+09:00
> *** MODULE NAME:(SQL*Plus) 2024-05-19T16:01:12.517642+09:00
> *** ACTION NAME:() 2024-05-19T16:01:12.517645+09:00
> *** CLIENT DRIVER:(SQL*PLUS) 2024-05-19T16:01:12.517647+09:00
25c25
< Registered qb: SEL$1 0x985c998 (PARSER)
---
> Registered qb: SEL$1 0x157d0498 (PARSER)
33c33
< Registered qb: SEL$2 0xf21cb80 (PARSER)
---
> Registered qb: SEL$2 0x1b18cb80 (PARSER)
58,59c58,60
< ----- Current SQL Statement for this session (sql_id=aa9vhbzahx2pv) -----
< SELECT *
---
> ----- Current SQL Statement for this session (sql_id=b52a0275tgs3x) -----
> SELECT /*+ ORDERED_PREDICATES */
> *
793c794
< CBQT: Validity checks failed for aa9vhbzahx2pv.
---
> CBQT: Validity checks failed for b52a0275tgs3x.
815c816
< JE:[V2] Query block (0x7fda0985c998) before join elimination:
---
> JE:[V2] Query block (0x7f3c157d0498) before join elimination:
824c825
< JE:[V2] Query block (0x7fda0985c998) after join elimination:
---
> JE:[V2] Query block (0x7f3c157d0498) after join elimination:
836c837
< Registered qb: SEL$58A6D7F6 0xf21cb80 (VIEW MERGE SEL$2; SEL$1; SEL$2)
---
> Registered qb: SEL$58A6D7F6 0x1b18cb80 (VIEW MERGE SEL$2; SEL$1; SEL$2)
851,852c852,853
< SELECT "C"."CUSTOMER_ID" "CUSTOMER_ID","C"."NAME" "NAME","C"."CITY" "CITY","C"."AGE" "AGE","O"."ORDER_ID" "ORDER_ID","O"."CUSTOMER_ID" "CUSTOMER_ID","O"."ORDER_DATE" "ORDER_DATE","O"."AMOUNT" "AMOUNT" FROM "IMSI"."CUSTOMERS" "C","IMSI"."ORDERS" "O" WHERE "C"."CITY"='New York' AND "C"."AGE">25 AND "O"."AMOUNT">100 AND "C"."CUSTOMER_ID"="O"."CUSTOMER_ID"
< JE:[V2] Query block (0x7fda0f21cb80) before join elimination:
---
> SELECT "C"."CUSTOMER_ID" "CUSTOMER_ID","C"."NAME" "NAME","C"."CITY" "CITY","C"."AGE" "AGE","O"."ORDER_ID" "ORDER_ID","O"."CUSTOMER_ID" "CUSTOMER_ID","O"."ORDER_DATE" "ORDER_DATE","O"."AMOUNT" "AMOUNT" FROM "IMSI"."CUSTOMERS" "C","IMSI"."ORDERS" "O" WHERE "C"."CUSTOMER_ID"="O"."CUSTOMER_ID" AND "C"."CITY"='New York' AND "C"."AGE">25 AND "O"."AMOUNT">100
> JE:[V2] Query block (0x7f3c1b18cb80) before join elimination:
854c855
< SELECT "C"."CUSTOMER_ID" "CUSTOMER_ID","C"."NAME" "NAME","C"."CITY" "CITY","C"."AGE" "AGE","O"."ORDER_ID" "ORDER_ID","O"."CUSTOMER_ID" "CUSTOMER_ID","O"."ORDER_DATE" "ORDER_DATE","O"."AMOUNT" "AMOUNT" FROM "IMSI"."CUSTOMERS" "C","IMSI"."ORDERS" "O" WHERE "C"."CITY"='New York' AND "C"."AGE">25 AND "O"."AMOUNT">100 AND "C"."CUSTOMER_ID"="O"."CUSTOMER_ID"
---
> SELECT "C"."CUSTOMER_ID" "CUSTOMER_ID","C"."NAME" "NAME","C"."CITY" "CITY","C"."AGE" "AGE","O"."ORDER_ID" "ORDER_ID","O"."CUSTOMER_ID" "CUSTOMER_ID","O"."ORDER_DATE" "ORDER_DATE","O"."AMOUNT" "AMOUNT" FROM "IMSI"."CUSTOMERS" "C","IMSI"."ORDERS" "O" WHERE "C"."CUSTOMER_ID"="O"."CUSTOMER_ID" AND "C"."CITY"='New York' AND "C"."AGE">25 AND "O"."AMOUNT">100
861c862
< JE:[V2] Query block (0x7fda0f21cb80) after join elimination:
---
> JE:[V2] Query block (0x7f3c1b18cb80) after join elimination:
863c864
< SELECT "C"."CUSTOMER_ID" "CUSTOMER_ID","C"."NAME" "NAME","C"."CITY" "CITY","C"."AGE" "AGE","O"."ORDER_ID" "ORDER_ID","O"."CUSTOMER_ID" "CUSTOMER_ID","O"."ORDER_DATE" "ORDER_DATE","O"."AMOUNT" "AMOUNT" FROM "IMSI"."CUSTOMERS" "C","IMSI"."ORDERS" "O" WHERE "C"."CITY"='New York' AND "C"."AGE">25 AND "O"."AMOUNT">100 AND "C"."CUSTOMER_ID"="O"."CUSTOMER_ID"
---
> SELECT "C"."CUSTOMER_ID" "CUSTOMER_ID","C"."NAME" "NAME","C"."CITY" "CITY","C"."AGE" "AGE","O"."ORDER_ID" "ORDER_ID","O"."CUSTOMER_ID" "CUSTOMER_ID","O"."ORDER_DATE" "ORDER_DATE","O"."AMOUNT" "AMOUNT" FROM "IMSI"."CUSTOMERS" "C","IMSI"."ORDERS" "O" WHERE "C"."CUSTOMER_ID"="O"."CUSTOMER_ID" AND "C"."CITY"='New York' AND "C"."AGE">25 AND "O"."AMOUNT">100
877c878
< "C"."CITY"='New York' AND "C"."AGE">25 AND "O"."AMOUNT">100 AND "C"."CUSTOMER_ID"="O"."CUSTOMER_ID"
---
> "C"."CUSTOMER_ID"="O"."CUSTOMER_ID" AND "C"."CITY"='New York' AND "C"."AGE">25 AND "O"."AMOUNT">100
879c880
< "C"."CITY"='New York' AND "C"."AGE">25 AND "O"."AMOUNT">100 AND "C"."CUSTOMER_ID"="O"."CUSTOMER_ID"
---
> "C"."CUSTOMER_ID"="O"."CUSTOMER_ID" AND "C"."CITY"='New York' AND "C"."AGE">25 AND "O"."AMOUNT">100
887c888
< CBQT: Validity checks failed for aa9vhbzahx2pv.
---
> CBQT: Validity checks failed for b52a0275tgs3x.
901c902
< SELECT "C"."CUSTOMER_ID" "CUSTOMER_ID","C"."NAME" "NAME","C"."CITY" "CITY","C"."AGE" "AGE","O"."ORDER_ID" "ORDER_ID","O"."CUSTOMER_ID" "CUSTOMER_ID","O"."ORDER_DATE" "ORDER_DATE","O"."AMOUNT" "AMOUNT" FROM "IMSI"."CUSTOMERS" "C","IMSI"."ORDERS" "O" WHERE "C"."CITY"='New York' AND "C"."AGE">25 AND "O"."AMOUNT">100 AND "C"."CUSTOMER_ID"="O"."CUSTOMER_ID"
---
> SELECT "C"."CUSTOMER_ID" "CUSTOMER_ID","C"."NAME" "NAME","C"."CITY" "CITY","C"."AGE" "AGE","O"."ORDER_ID" "ORDER_ID","O"."CUSTOMER_ID" "CUSTOMER_ID","O"."ORDER_DATE" "ORDER_DATE","O"."AMOUNT" "AMOUNT" FROM "IMSI"."CUSTOMERS" "C","IMSI"."ORDERS" "O" WHERE "C"."CUSTOMER_ID"="O"."CUSTOMER_ID" AND "C"."CITY"='New York' AND "C"."AGE">25 AND "O"."AMOUNT">100
918,919c919,920
< SELECT "C"."CUSTOMER_ID" "CUSTOMER_ID","C"."NAME" "NAME","C"."CITY" "CITY","C"."AGE" "AGE","O"."ORDER_ID" "ORDER_ID","O"."CUSTOMER_ID" "CUSTOMER_ID","O"."ORDER_DATE" "ORDER_DATE","O"."AMOUNT" "AMOUNT" FROM "IMSI"."CUSTOMERS" "C","IMSI"."ORDERS" "O" WHERE "C"."CITY"='New York' AND "C"."AGE">25 AND "O"."AMOUNT">100 AND "C"."CUSTOMER_ID"="O"."CUSTOMER_ID"
< JE:[V2] Query block (0x7fda0f21cb80) before join elimination:
---
> SELECT "C"."CUSTOMER_ID" "CUSTOMER_ID","C"."NAME" "NAME","C"."CITY" "CITY","C"."AGE" "AGE","O"."ORDER_ID" "ORDER_ID","O"."CUSTOMER_ID" "CUSTOMER_ID","O"."ORDER_DATE" "ORDER_DATE","O"."AMOUNT" "AMOUNT" FROM "IMSI"."CUSTOMERS" "C","IMSI"."ORDERS" "O" WHERE "C"."CUSTOMER_ID"="O"."CUSTOMER_ID" AND "C"."CITY"='New York' AND "C"."AGE">25 AND "O"."AMOUNT">100
> JE:[V2] Query block (0x7f3c1b18cb80) before join elimination:
921c922
< SELECT "C"."CUSTOMER_ID" "CUSTOMER_ID","C"."NAME" "NAME","C"."CITY" "CITY","C"."AGE" "AGE","O"."ORDER_ID" "ORDER_ID","O"."CUSTOMER_ID" "CUSTOMER_ID","O"."ORDER_DATE" "ORDER_DATE","O"."AMOUNT" "AMOUNT" FROM "IMSI"."CUSTOMERS" "C","IMSI"."ORDERS" "O" WHERE "C"."CITY"='New York' AND "C"."AGE">25 AND "O"."AMOUNT">100 AND "C"."CUSTOMER_ID"="O"."CUSTOMER_ID"
---
> SELECT "C"."CUSTOMER_ID" "CUSTOMER_ID","C"."NAME" "NAME","C"."CITY" "CITY","C"."AGE" "AGE","O"."ORDER_ID" "ORDER_ID","O"."CUSTOMER_ID" "CUSTOMER_ID","O"."ORDER_DATE" "ORDER_DATE","O"."AMOUNT" "AMOUNT" FROM "IMSI"."CUSTOMERS" "C","IMSI"."ORDERS" "O" WHERE "C"."CUSTOMER_ID"="O"."CUSTOMER_ID" AND "C"."CITY"='New York' AND "C"."AGE">25 AND "O"."AMOUNT">100
928c929
< JE:[V2] Query block (0x7fda0f21cb80) after join elimination:
---
> JE:[V2] Query block (0x7f3c1b18cb80) after join elimination:
930c931
< SELECT "C"."CUSTOMER_ID" "CUSTOMER_ID","C"."NAME" "NAME","C"."CITY" "CITY","C"."AGE" "AGE","O"."ORDER_ID" "ORDER_ID","O"."CUSTOMER_ID" "CUSTOMER_ID","O"."ORDER_DATE" "ORDER_DATE","O"."AMOUNT" "AMOUNT" FROM "IMSI"."CUSTOMERS" "C","IMSI"."ORDERS" "O" WHERE "C"."CITY"='New York' AND "C"."AGE">25 AND "O"."AMOUNT">100 AND "C"."CUSTOMER_ID"="O"."CUSTOMER_ID"
---
> SELECT "C"."CUSTOMER_ID" "CUSTOMER_ID","C"."NAME" "NAME","C"."CITY" "CITY","C"."AGE" "AGE","O"."ORDER_ID" "ORDER_ID","O"."CUSTOMER_ID" "CUSTOMER_ID","O"."ORDER_DATE" "ORDER_DATE","O"."AMOUNT" "AMOUNT" FROM "IMSI"."CUSTOMERS" "C","IMSI"."ORDERS" "O" WHERE "C"."CUSTOMER_ID"="O"."CUSTOMER_ID" AND "C"."CITY"='New York' AND "C"."AGE">25 AND "O"."AMOUNT">100
947c948
< "C"."CITY"='New York' AND "C"."AGE">25 AND "O"."AMOUNT">100 AND "C"."CUSTOMER_ID"="O"."CUSTOMER_ID"
---
> "C"."CUSTOMER_ID"="O"."CUSTOMER_ID" AND "C"."CITY"='New York' AND "C"."AGE">25 AND "O"."AMOUNT">100
949c950
< finally: "C"."CITY"='New York' AND "C"."AGE">25 AND "O"."AMOUNT">100 AND "C"."CUSTOMER_ID"="O"."CUSTOMER_ID"
---
> finally: "C"."CUSTOMER_ID"="O"."CUSTOMER_ID" AND "C"."CITY"='New York' AND "C"."AGE">25 AND "O"."AMOUNT">100
951c952
< apadrv-start sqlid=11900604097248529083
---
> apadrv-start sqlid=12864884255734882429
958c959
< call(in-use=6112, alloc=16344), compile(in-use=149632, alloc=152984), execution(in-use=4880, alloc=8088)
---
> call(in-use=6112, alloc=16344), compile(in-use=151120, alloc=157128), execution(in-use=4880, alloc=8088)
969c970
< SELECT "C"."CUSTOMER_ID" "CUSTOMER_ID","C"."NAME" "NAME","C"."CITY" "CITY","C"."AGE" "AGE","O"."ORDER_ID" "ORDER_ID","O"."CUSTOMER_ID" "CUSTOMER_ID","O"."ORDER_DATE" "ORDER_DATE","O"."AMOUNT" "AMOUNT" FROM "IMSI"."CUSTOMERS" "C","IMSI"."ORDERS" "O" WHERE "C"."CITY"='New York' AND "C"."AGE">25 AND "O"."AMOUNT">100 AND "C"."CUSTOMER_ID"="O"."CUSTOMER_ID"
---
> SELECT "C"."CUSTOMER_ID" "CUSTOMER_ID","C"."NAME" "NAME","C"."CITY" "CITY","C"."AGE" "AGE","O"."ORDER_ID" "ORDER_ID","O"."CUSTOMER_ID" "CUSTOMER_ID","O"."ORDER_DATE" "ORDER_DATE","O"."AMOUNT" "AMOUNT" FROM "IMSI"."CUSTOMERS" "C","IMSI"."ORDERS" "O" WHERE "C"."CUSTOMER_ID"="O"."CUSTOMER_ID" AND "C"."CITY"='New York' AND "C"."AGE">25 AND "O"."AMOUNT">100
983c984
< SELECT "C"."CUSTOMER_ID" "CUSTOMER_ID","C"."NAME" "NAME","C"."CITY" "CITY","C"."AGE" "AGE","O"."ORDER_ID" "ORDER_ID","O"."CUSTOMER_ID" "CUSTOMER_ID","O"."ORDER_DATE" "ORDER_DATE","O"."AMOUNT" "AMOUNT" FROM "IMSI"."CUSTOMERS" "C","IMSI"."ORDERS" "O" WHERE "C"."CITY"='New York' AND "C"."AGE">25 AND "O"."AMOUNT">100 AND "C"."CUSTOMER_ID"="O"."CUSTOMER_ID"
---
> SELECT "C"."CUSTOMER_ID" "CUSTOMER_ID","C"."NAME" "NAME","C"."CITY" "CITY","C"."AGE" "AGE","O"."ORDER_ID" "ORDER_ID","O"."CUSTOMER_ID" "CUSTOMER_ID","O"."ORDER_DATE" "ORDER_DATE","O"."AMOUNT" "AMOUNT" FROM "IMSI"."CUSTOMERS" "C","IMSI"."ORDERS" "O" WHERE "C"."CUSTOMER_ID"="O"."CUSTOMER_ID" AND "C"."CITY"='New York' AND "C"."AGE">25 AND "O"."AMOUNT">100
987c988
< call(in-use=6568, alloc=16344), compile(in-use=160312, alloc=161320), execution(in-use=4880, alloc=8088)
---
> call(in-use=6568, alloc=16344), compile(in-use=161792, alloc=163200), execution(in-use=4880, alloc=8088)
989c990
< kkoqbc-subheap (create addr=0x7fda09855818)
---
> kkoqbc-subheap (create addr=0x7f3c157c9818)
993c994,995
< SELECT *
---
> SELECT /*+ ORDERED_PREDICATES */
> *
1041c1043
< finally: "C"."CITY"='New York' AND "C"."CUSTOMER_ID"="O"."CUSTOMER_ID" AND "C"."AGE">25 AND "O"."AMOUNT">100
---
> finally: "C"."CUSTOMER_ID"="O"."CUSTOMER_ID" AND "C"."CITY"='New York' AND "C"."AGE">25 AND "O"."AMOUNT">100
1057c1059
< *** 2024-05-19T16:00:53.440877+09:00
---
> *** 2024-05-19T16:01:12.520709+09:00
1068c1070
< *** 2024-05-19T16:00:53.441044+09:00
---
> *** 2024-05-19T16:01:12.520823+09:00
1073c1075
< *** 2024-05-19T16:00:53.442787+09:00
---
> *** 2024-05-19T16:01:12.521038+09:00
1128c1130
< *** 2024-05-19T16:00:53.443063+09:00
---
> *** 2024-05-19T16:01:12.521244+09:00
1136c1138
< *** 2024-05-19T16:00:53.443118+09:00
---
> *** 2024-05-19T16:01:12.521297+09:00
1141c1143
< *** 2024-05-19T16:00:53.444175+09:00
---
> *** 2024-05-19T16:01:12.521393+09:00
1301d1302
< io = NOCOST, cpu = 50.000000, sel = 0.010000 flag = 2048 ("C"."CITY"='New York')
1302a1304
> io = NOCOST, cpu = 50.000000, sel = 0.010000 flag = 2048 ("C"."CITY"='New York')
1398c1400
< AP: Checking validity for query block SEL$58A6D7F6, sqlid=aa9vhbzahx2pv
---
> AP: Checking validity for query block SEL$58A6D7F6, sqlid=b52a0275tgs3x
1408d1409
< io = NOCOST, cpu = 50.000000, sel = 0.010000 flag = 2048 ("C"."CITY"='New York')
1409a1411
> io = NOCOST, cpu = 50.000000, sel = 0.010000 flag = 2048 ("C"."CITY"='New York')
1459d1460
< io = NOCOST, cpu = 50.000000, sel = 0.010000 flag = 2048 ("C"."CITY"='New York')
1460a1462
> io = NOCOST, cpu = 50.000000, sel = 0.010000 flag = 2048 ("C"."CITY"='New York')
1510d1511
< io = NOCOST, cpu = 50.000000, sel = 0.010000 flag = 2048 ("C"."CITY"='New York')
1511a1513
> io = NOCOST, cpu = 50.000000, sel = 0.010000 flag = 2048 ("C"."CITY"='New York')
1561d1562
< io = NOCOST, cpu = 50.000000, sel = 0.010000 flag = 2048 ("C"."CITY"='New York')
1562a1564
> io = NOCOST, cpu = 50.000000, sel = 0.010000 flag = 2048 ("C"."CITY"='New York')
1612d1613
< io = NOCOST, cpu = 50.000000, sel = 0.010000 flag = 2048 ("C"."CITY"='New York')
1613a1615
> io = NOCOST, cpu = 50.000000, sel = 0.010000 flag = 2048 ("C"."CITY"='New York')
1663d1664
< io = NOCOST, cpu = 50.000000, sel = 0.010000 flag = 2048 ("C"."CITY"='New York')
1664a1666
> io = NOCOST, cpu = 50.000000, sel = 0.010000 flag = 2048 ("C"."CITY"='New York')
1714d1715
< io = NOCOST, cpu = 50.000000, sel = 0.010000 flag = 2048 ("C"."CITY"='New York')
1715a1717
> io = NOCOST, cpu = 50.000000, sel = 0.010000 flag = 2048 ("C"."CITY"='New York')
1765d1766
< io = NOCOST, cpu = 50.000000, sel = 0.010000 flag = 2048 ("C"."CITY"='New York')
1766a1768
> io = NOCOST, cpu = 50.000000, sel = 0.010000 flag = 2048 ("C"."CITY"='New York')
1816d1817
< io = NOCOST, cpu = 50.000000, sel = 0.010000 flag = 2048 ("C"."CITY"='New York')
1817a1819
> io = NOCOST, cpu = 50.000000, sel = 0.010000 flag = 2048 ("C"."CITY"='New York')
1867d1868
< io = NOCOST, cpu = 50.000000, sel = 0.010000 flag = 2048 ("C"."CITY"='New York')
1868a1870
> io = NOCOST, cpu = 50.000000, sel = 0.010000 flag = 2048 ("C"."CITY"='New York')
1918d1919
< io = NOCOST, cpu = 50.000000, sel = 0.010000 flag = 2048 ("C"."CITY"='New York')
1919a1921
> io = NOCOST, cpu = 50.000000, sel = 0.010000 flag = 2048 ("C"."CITY"='New York')
1969d1970
< io = NOCOST, cpu = 50.000000, sel = 0.010000 flag = 2048 ("C"."CITY"='New York')
1970a1972
> io = NOCOST, cpu = 50.000000, sel = 0.010000 flag = 2048 ("C"."CITY"='New York')
2020d2021
< io = NOCOST, cpu = 50.000000, sel = 0.010000 flag = 2048 ("C"."CITY"='New York')
2021a2023
> io = NOCOST, cpu = 50.000000, sel = 0.010000 flag = 2048 ("C"."CITY"='New York')
2071d2072
< io = NOCOST, cpu = 50.000000, sel = 0.010000 flag = 2048 ("C"."CITY"='New York')
2072a2074
> io = NOCOST, cpu = 50.000000, sel = 0.010000 flag = 2048 ("C"."CITY"='New York')
2122d2123
< io = NOCOST, cpu = 50.000000, sel = 0.010000 flag = 2048 ("C"."CITY"='New York')
2123a2125
> io = NOCOST, cpu = 50.000000, sel = 0.010000 flag = 2048 ("C"."CITY"='New York')
2173d2174
< io = NOCOST, cpu = 50.000000, sel = 0.010000 flag = 2048 ("C"."CITY"='New York')
2174a2176
> io = NOCOST, cpu = 50.000000, sel = 0.010000 flag = 2048 ("C"."CITY"='New York')
2224d2225
< io = NOCOST, cpu = 50.000000, sel = 0.010000 flag = 2048 ("C"."CITY"='New York')
2225a2227
> io = NOCOST, cpu = 50.000000, sel = 0.010000 flag = 2048 ("C"."CITY"='New York')
2275d2276
< io = NOCOST, cpu = 50.000000, sel = 0.010000 flag = 2048 ("C"."CITY"='New York')
2276a2278
> io = NOCOST, cpu = 50.000000, sel = 0.010000 flag = 2048 ("C"."CITY"='New York')
2354c2356
< kkoqbc-subheap (delete addr=0x7fda09855818, in-use=53544, alloc=65664)
---
> kkoqbc-subheap (delete addr=0x7f3c157c9818, in-use=53432, alloc=55896)
2357c2359
< call(in-use=48376, alloc=136616), compile(in-use=193152, alloc=196304), execution(in-use=5992, alloc=8088)
---
> call(in-use=48376, alloc=120896), compile(in-use=194744, alloc=196352), execution(in-use=5992, alloc=8088)
2365c2367
< call(in-use=48376, alloc=136616), compile(in-use=198880, alloc=200496), execution(in-use=5992, alloc=8088)
---
> call(in-use=48376, alloc=120896), compile(in-use=200472, alloc=203912), execution(in-use=5992, alloc=8088)
2370c2372
< SPD: Inserted felem, fid=6180727837993378186, ftype = 1, freason = 1, dtype = 0, dstate = 0, dflag = 0, ver = NO, keep = NO
---
> SPD: Modified felem, fid=6180727837993378186, ftype = 1, freason = 1, dtype = 0, dstate = 0, dflag = 0, ver = NO, keep = NO
2373c2375
< SPD: Inserted felem, fid=8640607886690486867, ftype = 1, freason = 1, dtype = 0, dstate = 0, dflag = 0, ver = NO, keep = NO
---
> SPD: Modified felem, fid=8640607886690486867, ftype = 1, freason = 1, dtype = 0, dstate = 0, dflag = 0, ver = NO, keep = NO
2378c2380
< SPD: Inserted felem, fid=1214517127999599805, ftype = 1, freason = 1, dtype = 0, dstate = 0, dflag = 0, ver = NO, keep = NO
---
> SPD: Modified felem, fid=1214517127999599805, ftype = 1, freason = 1, dtype = 0, dstate = 0, dflag = 0, ver = NO, keep = NO
2381c2383
< SPD: Inserted felem, fid=12857328587164504002, ftype = 1, freason = 1, dtype = 0, dstate = 0, dflag = 0, ver = NO, keep = NO
---
> SPD: Modified felem, fid=12857328587164504002, ftype = 1, freason = 1, dtype = 0, dstate = 0, dflag = 0, ver = NO, keep = NO
2384,2396c2386,2398
< SPD: Inserted felem, fid=15932266577089275857, ftype = 1, freason = 2, dtype = 0, dstate = 0, dflag = 0, ver = NO, keep = NO
< kkodpAddRwsIdRef: node 0x7fda097490a8 with subplan 1 added
< kkodpAddRwsIdRef: node 0x7fda09748378 with subplan 2 added
< kkodpAddRwsIdRef: node 0x7fda09748c78 with subplan 1 added
< kkodpAddRwsIdRef: node 0x7fda09749948 with subplan 1 added
< kkodpAddRwsIdRef: node 0x7fda09749570 with subplan 1 added
< kkodpAddRwsIdRef: node 0x7fda09749258 with subplan 1 added
< kkodpAddRwsIdRef: node 0x7fda097490a8 with subplan 1 added
< kkodpAddRwsIdRef: node 0x7fda09748c78 with subplan 2 added
< kkodpAddRwsIdRef: node 0x7fda09748a18 with subplan 2 added
< kkodpAddRwsIdRef: node 0x7fda09748690 with subplan 2 added
< kkodpAddRwsIdRef: node 0x7fda09748378 with subplan 2 added
< kkodpAddRwsIdRef: node 0x7fda09748c78 with subplan 65535 added
---
> SPD: Modified felem, fid=15932266577089275857, ftype = 1, freason = 2, dtype = 0, dstate = 0, dflag = 0, ver = NO, keep = NO
> kkodpAddRwsIdRef: node 0x7f3c15730940 with subplan 1 added
> kkodpAddRwsIdRef: node 0x7f3c1572fbd0 with subplan 2 added
> kkodpAddRwsIdRef: node 0x7f3c15730618 with subplan 1 added
> kkodpAddRwsIdRef: node 0x7f3c1b180578 with subplan 1 added
> kkodpAddRwsIdRef: node 0x7f3c15730db8 with subplan 1 added
> kkodpAddRwsIdRef: node 0x7f3c15730af0 with subplan 1 added
> kkodpAddRwsIdRef: node 0x7f3c15730940 with subplan 1 added
> kkodpAddRwsIdRef: node 0x7f3c15730618 with subplan 2 added
> kkodpAddRwsIdRef: node 0x7f3c15730348 with subplan 2 added
> kkodpAddRwsIdRef: node 0x7f3c1572ff78 with subplan 2 added
> kkodpAddRwsIdRef: node 0x7f3c1572fbd0 with subplan 2 added
> kkodpAddRwsIdRef: node 0x7f3c15730618 with subplan 65535 added
2442,2444c2444,2447
< sql_id=aa9vhbzahx2pv plan_hash_value=2054468610 problem_type=3 command_type=3
< ----- Current SQL Statement for this session (sql_id=aa9vhbzahx2pv) -----
< SELECT *
---
> sql_id=b52a0275tgs3x plan_hash_value=2054468610 problem_type=3 command_type=3
> ----- Current SQL Statement for this session (sql_id=b52a0275tgs3x) -----
> SELECT /*+ ORDERED_PREDICATES */
> *
2450,2451c2453,2455
< sql_text_length=137
< sql=SELECT *
---
> sql_text_length=170
> sql=SELECT /*+ ORDERED_PREDICATES */
> *
2528a2533,2536
> Hint Report:
> Query Block: SEL$58A6D7F6
> ORDERED_PREDICATES
> End Hint Report
4602,4605c4610,4613
< SEL$2 0xf21cb80 (PARSER)
< SEL$58A6D7F6 0xf21cb80 (VIEW MERGE SEL$2; SEL$1; SEL$2) [FINAL]
< SEL$1 0x985c998 (PARSER)
< SEL$58A6D7F6 0xf21cb80 (VIEW MERGE SEL$2; SEL$1; SEL$2) [FINAL]
---
> SEL$2 0x1b18cb80 (PARSER)
> SEL$58A6D7F6 0x1b18cb80 (VIEW MERGE SEL$2; SEL$1; SEL$2) [FINAL]
> SEL$1 0x157d0498 (PARSER)
> SEL$58A6D7F6 0x1b18cb80 (VIEW MERGE SEL$2; SEL$1; SEL$2) [FINAL]
4608c4616
< call(in-use=69192, alloc=169352), compile(in-use=264192, alloc=326160), execution(in-use=28480, alloc=32424)
---
> call(in-use=69192, alloc=159040), compile(in-use=265856, alloc=328384), execution(in-use=28496, alloc=32424)
4612a4621
> atom_hint=(@=0x7f3c1b18aa08 err=0 resol=1 used=1 token=886 org=1 lvl=2 txt=ORDERED_PREDICATES ())
|
참조 :
'ORACLE > Performance Tuning ' 카테고리의 다른 글
| 오라클 23ai append_values 힌트를 사용한 벌크 인서트 성능 비교 (0) | 2024.07.09 |
|---|---|
| 오라클 19c hash 조인 과다 실행시 부하 테스트 (0) | 2024.05.28 |
| 오라클 19c insert 시 commit 주기에 따른 성능 테스트 (0) | 2024.05.23 |
| 오라클 19c 일반 insert, insert all, union all insert 성능 테스트(컬럼 10개) (0) | 2024.05.22 |
| 오라클 19c 일반 insert, insert all, union all insert 성능 테스트(컬럼 1개) (0) | 2024.05.19 |