OS 환경 : Oracle Linux 8.7 (64bit)
DB 환경 : Oracle Database 19.27.0.0
방법 : 오라클 19c UNION ALL 쿼리 사용자 정의 함수 호출 성능 테스트
조회 쿼리의 SELECT 절에서 사용자 정의 함수를 호출하면 조회 건수만큼 컨텍스트 스위칭(SQL과 PL/SQL 사이의 전환)이 발생할 수 있음
사용자 정의 함수 내부에서 다시 테이블을 조회하는 경우에는 함수 호출뿐만 아니라 함수 내부 SQL의 Buffers도 함께 발생함
하지만 이런 함수 내부 SQL 동작은 XPLAN 실행계획에서는 별도의 Operation으로 표시되지 않음
만약 UNION ALL 쿼리를 사용하는 경우 이로 인해 UNION ALL 하위 쿼리의 Buffers보다 UNION ALL 자체 Operation의 Buffers가 크게 증가하여 UNION ALL 자체가 많은 Buffers를 사용한 것처럼 보일 수 있음
하지만 UNION ALL은 위아래 쿼리의 결과를 연결하는 Operation이기 때문에 이건 말이 안됨
SELECT 절에 있는 사용자 정의 함수가 각 행마다 실행되면 함수 내부 SQL에서 발생한 Buffers가 상위 실행 통계에 포함되면서 UNION ALL 이후 Buffers가 증가한 것처럼 보이게 되는것임
본문에서는 동일한 UNION ALL 쿼리에서 상품명(PRODUCT_NAME)을 가져오는 방식을 세 가지로 변경하여 실행계획과 Buffers를 비교해봄
테스트
테스트 테이블 및 함수 생성
1. 사용자 정의 함수 직접 호출
2. Scalar Subquery로 사용자 정의 함수 호출
3. 사용자 정의 함수 제거 및 조인으로 변경
세 쿼리는 COMPLETE와 CANCEL 주문을 각각 10,000건씩 조회하고 TEST_ORDER_IX1 인덱스를 사용하도록 동일하게 설정함
참고로 외부 쿼리에서 SUM을 수행하는 이유는 UNION ALL에서 반환된 20,000건의 PRODUCT_NAME을 모두 사용하도록 하기 위함임
외부에서 PRODUCT_NAME을 사용하지 않거나 sqlplus에서 일부 행만 fetch하면 함수가 모든 행에 대해 동작하지 않을 수 있음
테스트
테스트 테이블 및 함수 생성
상품 테이블 생성
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
SQL>
drop table test_product purge;
create table test_product
(
product_code varchar2(10),
product_name varchar2(100),
constraint test_product_pk primary key (product_code)
);
insert into test_product
select 'P' || lpad(level, 3, '0'),
'PRODUCT_' || lpad(level, 3, '0')
from dual
connect by level <= 10;
commit;
|
P001부터 P010까지 총 10개의 상품을 생성함
고객 테이블 생성
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
SQL>
drop table test_customer purge;
create table test_customer
(
customer_id number,
customer_name varchar2(100),
customer_grade varchar2(10),
constraint test_customer_pk primary key (customer_id)
);
insert into test_customer
select level,
'CUSTOMER_' || lpad(level, 5, '0'),
case mod(level, 3)
when 0 then 'GOLD'
when 1 then 'SILVER'
else 'BRONZE'
end
from dual
connect by level <= 10000;
commit;
|
고객 데이터 10,000건을 생성함
주문 테이블 생성
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
SQL>
drop table test_order purge;
create table test_order
nologging
as
select level as order_id,
mod(level - 1, 10000) + 1 as customer_id,
'P' || lpad(mod(level - 1, 10) + 1, 3, '0') as product_code,
case
when level <= 10000 then 'COMPLETE'
when level <= 20000 then 'CANCEL'
else 'STATUS_' || lpad(mod(level - 20001, 98) + 1, 3, '0')
end as order_status,
mod(level, 1000) + 100 as order_amount,
date '2026-01-01' + mod(level, 365) as order_date
from dual
connect by level <= 1000000;
commit;
|
주문 데이터 100만 건을 생성함
COMPLETE와 CANCEL은 각각 10,000건으로 구성하고 나머지 980,000건은 STATUS_001부터 STATUS_098까지 각각 10,000건씩 저장함
PRODUCT_CODE는 P001부터 P010까지 반복해서 저장함
따라서 COMPLETE와 CANCEL 주문에는 상품별로 각각 1,000건이 존재함
PK 및 ORDER_STATUS 인덱스 생성
|
1
2
3
4
5
|
SQL>
alter table test_order
add constraint test_order_pk primary key (order_id);
create index test_order_ix1 on test_order(order_status);
|
COMPLETE와 CANCEL이 전체 주문 데이터에서 각각 1%만 차지하도록 구성하고 ORDER_STATUS 인덱스를 생성함
세 테스트 모두 INDEX 힌트를 사용하여 TEST_ORDER_IX1 인덱스로 조회함
함수 처리 방식 외에는 동일한 실행 조건을 만들기 위함임
통계정보 수집
|
1
2
3
4
5
6
7
8
9
|
SQL>
begin
dbms_stats.gather_table_stats(user, 'TEST_PRODUCT', cascade => true);
dbms_stats.gather_table_stats(user, 'TEST_CUSTOMER', cascade => true);
dbms_stats.gather_table_stats(user, 'TEST_ORDER', cascade => true);
end;
/
PL/SQL procedure successfully completed.
|
데이터 분포 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
SQL>
set lines 200 pages 1000
col order_status for a15
select order_status, count(*)
from test_order
group by order_status
order by order_status;
ORDER_STATUS COUNT(*)
--------------- ----------
CANCEL 10000
COMPLETE 10000
STATUS_001 10000
STATUS_002 10000
STATUS_003 10000
...
STATUS_098 10000
100 rows selected.
|
COMPLETE와 CANCEL은 각각 10,000건이고 STATUS_001부터 STATUS_098도 각각 10,000건임
COMPLETE와 CANCEL의 상품별 건수 확인
|
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
col product_code for a15
col order_status for a15
select product_code, order_status, count(*)
from test_order
where order_status in ('COMPLETE', 'CANCEL')
group by product_code, order_status
order by product_code, order_status;
PRODUCT_CODE ORDER_STATUS COUNT(*)
--------------- --------------- ----------
P001 CANCEL 1000
P001 COMPLETE 1000
P002 CANCEL 1000
P002 COMPLETE 1000
P003 CANCEL 1000
P003 COMPLETE 1000
P004 CANCEL 1000
P004 COMPLETE 1000
P005 CANCEL 1000
P005 COMPLETE 1000
P006 CANCEL 1000
P006 COMPLETE 1000
P007 CANCEL 1000
P007 COMPLETE 1000
P008 CANCEL 1000
P008 COMPLETE 1000
P009 CANCEL 1000
P009 COMPLETE 1000
P010 CANCEL 1000
P010 COMPLETE 1000
20 rows selected.
|
각 UNION ALL Branch에서 10,000건을 처리하지만 PRODUCT_CODE는 종류가 10개만 존재함
상품별로 동일한 PRODUCT_CODE가 1,000번씩 반복되므로 Scalar Subquery Cache 적용 전후를 비교하기 좋은 형태임
사용자 정의 함수 생성
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
SQL>
drop function get_product_name;
create or replace function get_product_name
(
p_product_code varchar2
)
return varchar2
is
v_product_name test_product.product_name%type;
begin
select product_name into v_product_name
from test_product
where product_code = p_product_code;
return v_product_name;
end;
/
Function created.
|
GET_PRODUCT_NAME 함수는 PRODUCT_CODE를 입력받아 TEST_PRODUCT 테이블에서 PRODUCT_NAME을 조회하여 반환함
함수 호출 방식에 따른 차이를 확인하기 위해 DETERMINISTIC 기능과 RESULT_CACHE는 사용하지 않음
1. 사용자 정의 함수 직접 호출
UNION ALL 위아래 쿼리의 SELECT 절에서 GET_PRODUCT_NAME 함수를 직접 호출함
|
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
|
SQL>
SELECT /*+ gather_plan_statistics */
SUM(
ORDER_ID
+ LENGTH(CUSTOMER_NAME)
+ LENGTH(PRODUCT_CODE)
+ LENGTH(PRODUCT_NAME)
+ ORDER_AMOUNT
) AS CHECK_SUM
FROM
(
SELECT /*+ INDEX(A TEST_ORDER_IX1) */
A.ORDER_ID,
B.CUSTOMER_NAME,
A.PRODUCT_CODE,
GET_PRODUCT_NAME(A.PRODUCT_CODE) AS PRODUCT_NAME,
A.ORDER_AMOUNT
FROM TEST_ORDER A,
TEST_CUSTOMER B
WHERE A.CUSTOMER_ID = B.CUSTOMER_ID
AND A.ORDER_STATUS = 'COMPLETE'
UNION ALL
SELECT /*+ INDEX(A TEST_ORDER_IX1) */
A.ORDER_ID,
B.CUSTOMER_NAME,
A.PRODUCT_CODE,
GET_PRODUCT_NAME(A.PRODUCT_CODE) AS PRODUCT_NAME,
A.ORDER_AMOUNT
FROM TEST_ORDER A,
TEST_CUSTOMER B
WHERE A.CUSTOMER_ID = B.CUSTOMER_ID
AND A.ORDER_STATUS = 'CANCEL'
);
SELECT * FROM DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST -rows -Projection +HINT_REPORT +outline');
Plan hash value: 2003823136
--------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 |00:00:00.45 | 40256 | | | |
| 1 | SORT AGGREGATE | | 1 | 1 |00:00:00.45 | 40256 | | | |
| 2 | VIEW | | 1 | 20000 |00:00:00.45 | 40256 | | | |
| 3 | UNION-ALL | | 1 | 20000 |00:00:00.44 | 40256 | | | |
|* 4 | HASH JOIN | | 1 | 10000 |00:00:00.02 | 130 | 1722K| 1722K| 1962K (0)|
| 5 | TABLE ACCESS FULL | TEST_CUSTOMER | 1 | 10000 |00:00:00.01 | 45 | | | |
| 6 | TABLE ACCESS BY INDEX ROWID BATCHED| TEST_ORDER | 1 | 10000 |00:00:00.01 | 85 | | | |
|* 7 | INDEX RANGE SCAN | TEST_ORDER_IX1 | 1 | 10000 |00:00:00.01 | 30 | | | |
|* 8 | HASH JOIN | | 1 | 10000 |00:00:00.02 | 126 | 1722K| 1722K| 1992K (0)|
| 9 | TABLE ACCESS FULL | TEST_CUSTOMER | 1 | 10000 |00:00:00.01 | 45 | | | |
| 10 | TABLE ACCESS BY INDEX ROWID BATCHED| TEST_ORDER | 1 | 10000 |00:00:00.01 | 81 | | | |
|* 11 | INDEX RANGE SCAN | TEST_ORDER_IX1 | 1 | 10000 |00:00:00.01 | 28 | | | |
--------------------------------------------------------------------------------------------------------------------------------------
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$2")
OUTLINE_LEAF(@"SEL$3")
OUTLINE_LEAF(@"SET$1")
OUTLINE_LEAF(@"SEL$1")
NO_ACCESS(@"SEL$1" "from$_subquery$_001"@"SEL$1")
INDEX_RS_ASC(@"SEL$3" "A"@"SEL$3" ("TEST_ORDER"."ORDER_STATUS"))
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$3" "A"@"SEL$3")
FULL(@"SEL$3" "B"@"SEL$3")
LEADING(@"SEL$3" "A"@"SEL$3" "B"@"SEL$3")
USE_HASH(@"SEL$3" "B"@"SEL$3")
SWAP_JOIN_INPUTS(@"SEL$3" "B"@"SEL$3")
INDEX_RS_ASC(@"SEL$2" "A"@"SEL$2" ("TEST_ORDER"."ORDER_STATUS"))
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$2" "A"@"SEL$2")
FULL(@"SEL$2" "B"@"SEL$2")
LEADING(@"SEL$2" "A"@"SEL$2" "B"@"SEL$2")
USE_HASH(@"SEL$2" "B"@"SEL$2")
SWAP_JOIN_INPUTS(@"SEL$2" "B"@"SEL$2")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("A"."CUSTOMER_ID"="B"."CUSTOMER_ID")
7 - access("A"."ORDER_STATUS"='COMPLETE')
8 - access("A"."CUSTOMER_ID"="B"."CUSTOMER_ID")
11 - access("A"."ORDER_STATUS"='CANCEL')
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 2
---------------------------------------------------------------------------
6 - SEL$2 / A@SEL$2
- INDEX(A TEST_ORDER_IX1)
10 - SEL$3 / A@SEL$3
- INDEX(A TEST_ORDER_IX1)
83 rows selected.
|
union all 상, 하단 쿼리 블록에서 각각 TEST_CUSTOMER, TEST_ORDER 테이블을 읽고 이때 각각 130, 126의 Buffers만 소모함
하지만 ID 3번 UNION-ALL 부분에서 약 4만가량의 Buffers를 소모함
xplan 결과에선 이 이유를 찾을수 없음
2. Scalar Subquery로 사용자정의 함수 호출
GET_PRODUCT_NAME 함수 호출 부분을 Scalar Subquery로 감싸서 실행함
|
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
|
SQL>
SELECT /*+ GATHER_PLAN_STATISTICS */
SUM(
ORDER_ID
+ LENGTH(CUSTOMER_NAME)
+ LENGTH(PRODUCT_CODE)
+ LENGTH(PRODUCT_NAME)
+ ORDER_AMOUNT
) AS CHECK_SUM
FROM
(
SELECT /*+ INDEX(A TEST_ORDER_IX1) */
A.ORDER_ID,
B.CUSTOMER_NAME,
A.PRODUCT_CODE,
(
SELECT /*+ NO_UNNEST */
GET_PRODUCT_NAME(A.PRODUCT_CODE)
FROM DUAL
) AS PRODUCT_NAME,
A.ORDER_AMOUNT
FROM TEST_ORDER A,
TEST_CUSTOMER B
WHERE A.CUSTOMER_ID = B.CUSTOMER_ID
AND A.ORDER_STATUS = 'COMPLETE'
UNION ALL
SELECT /*+ INDEX(A TEST_ORDER_IX1) */
A.ORDER_ID,
B.CUSTOMER_NAME,
A.PRODUCT_CODE,
(
SELECT /*+ NO_UNNEST */
GET_PRODUCT_NAME(A.PRODUCT_CODE)
FROM DUAL
) AS PRODUCT_NAME,
A.ORDER_AMOUNT
FROM TEST_ORDER A,
TEST_CUSTOMER B
WHERE A.CUSTOMER_ID = B.CUSTOMER_ID
AND A.ORDER_STATUS = 'CANCEL'
);
SELECT * FROM DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST -rows -Projection +HINT_REPORT +outline');
Plan hash value: 3446041270
--------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 |00:00:00.02 | 298 | | | |
| 1 | SORT AGGREGATE | | 1 | 1 |00:00:00.02 | 298 | | | |
| 2 | VIEW | | 1 | 20000 |00:00:00.02 | 298 | | | |
| 3 | UNION-ALL | | 1 | 20000 |00:00:00.02 | 298 | | | |
| 4 | FAST DUAL | | 10 | 10 |00:00:00.01 | 0 | | | |
|* 5 | HASH JOIN | | 1 | 10000 |00:00:00.01 | 131 | 1722K| 1722K| 2025K (0)|
| 6 | TABLE ACCESS FULL | TEST_CUSTOMER | 1 | 10000 |00:00:00.01 | 46 | | | |
| 7 | TABLE ACCESS BY INDEX ROWID BATCHED| TEST_ORDER | 1 | 10000 |00:00:00.01 | 85 | | | |
|* 8 | INDEX RANGE SCAN | TEST_ORDER_IX1 | 1 | 10000 |00:00:00.01 | 30 | | | |
| 9 | FAST DUAL | | 10 | 10 |00:00:00.01 | 0 | | | |
|* 10 | HASH JOIN | | 1 | 10000 |00:00:00.01 | 127 | 1722K| 1722K| 2064K (0)|
| 11 | TABLE ACCESS FULL | TEST_CUSTOMER | 1 | 10000 |00:00:00.01 | 46 | | | |
| 12 | TABLE ACCESS BY INDEX ROWID BATCHED| TEST_ORDER | 1 | 10000 |00:00:00.01 | 81 | | | |
|* 13 | INDEX RANGE SCAN | TEST_ORDER_IX1 | 1 | 10000 |00:00:00.01 | 28 | | | |
--------------------------------------------------------------------------------------------------------------------------------------
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$3")
OUTLINE_LEAF(@"SEL$2")
OUTLINE_LEAF(@"SEL$5")
OUTLINE_LEAF(@"SEL$4")
OUTLINE_LEAF(@"SET$1")
OUTLINE_LEAF(@"SEL$1")
NO_ACCESS(@"SEL$1" "from$_subquery$_001"@"SEL$1")
INDEX_RS_ASC(@"SEL$4" "A"@"SEL$4" ("TEST_ORDER"."ORDER_STATUS"))
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$4" "A"@"SEL$4")
FULL(@"SEL$4" "B"@"SEL$4")
LEADING(@"SEL$4" "A"@"SEL$4" "B"@"SEL$4")
USE_HASH(@"SEL$4" "B"@"SEL$4")
SWAP_JOIN_INPUTS(@"SEL$4" "B"@"SEL$4")
INDEX_RS_ASC(@"SEL$2" "A"@"SEL$2" ("TEST_ORDER"."ORDER_STATUS"))
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$2" "A"@"SEL$2")
FULL(@"SEL$2" "B"@"SEL$2")
LEADING(@"SEL$2" "A"@"SEL$2" "B"@"SEL$2")
USE_HASH(@"SEL$2" "B"@"SEL$2")
SWAP_JOIN_INPUTS(@"SEL$2" "B"@"SEL$2")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("A"."CUSTOMER_ID"="B"."CUSTOMER_ID")
8 - access("A"."ORDER_STATUS"='COMPLETE')
10 - access("A"."CUSTOMER_ID"="B"."CUSTOMER_ID")
13 - access("A"."ORDER_STATUS"='CANCEL')
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 4
---------------------------------------------------------------------------
4 - SEL$3
- NO_UNNEST
7 - SEL$2 / A@SEL$2
- INDEX(A TEST_ORDER_IX1)
9 - SEL$5
- NO_UNNEST
12 - SEL$4 / A@SEL$4
- INDEX(A TEST_ORDER_IX1)
94 rows selected.
|
Scalar Subquery는 한 행과 한 컬럼의 값을 반환하는 Subquery임
본문에서는 GET_PRODUCT_NAME 함수의 반환값을 Scalar Subquery의 결과로 사용함
각 Branch는 10,000건을 처리하지만 GET_PRODUCT_NAME 함수에 전달되는 PRODUCT_CODE는 P001부터 P010까지 10종류임
Scalar Subquery 캐싱 효과가 적용되면 같은 PRODUCT_CODE에 대한 결과를 재사용할 수 있기때문에 함수 호출과 함수 내부 TEST_PRODUCT 조회 횟수가 줄어들 수 있음
다만 Scalar Subquery 캐싱 효과가 모든 함수 호출을 반드시 한 번씩만 실행하도록 보장하는 것은 아님
캐시의 충돌과 옵티마이저 변환 등에 따라 실제 함수 호출 횟수는 달라질 수 있기 때문에 실행계획의 Buffers와 실행시간을 기준으로 확인해야 함
실행계획에서는 Scalar Subquery에 해당하는 FAST DUAL Operation과 Starts 부분을 확인해보면 10인것을 확인할 수 있음
COMPLETE와 CANCEL은 서로 다른 Query Block이므로 각 Branch의 Scalar Subquery 통계를 각각 확인해야 함
이 쿼리는 Scalar Subquery를 사용함으로써 298 Buffers를 소모함
3. 사용자 정의 함수 제거 및 조인으로 변경
GET_PRODUCT_NAME 함수를 제거하고 TEST_PRODUCT 테이블에서 PRODUCT_NAME을 직접 조인하여 조회함
|
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
|
SQL>
SELECT /*+ GATHER_PLAN_STATISTICS NO_FACTORIZE_JOIN(@"SET$1") */
SUM(
ORDER_ID
+ LENGTH(CUSTOMER_NAME)
+ LENGTH(PRODUCT_CODE)
+ LENGTH(PRODUCT_NAME)
+ ORDER_AMOUNT
) AS CHECK_SUM
FROM
(
SELECT /*+ INDEX(A TEST_ORDER_IX1) */
A.ORDER_ID,
B.CUSTOMER_NAME,
A.PRODUCT_CODE,
C.PRODUCT_NAME,
A.ORDER_AMOUNT
FROM TEST_ORDER A,
TEST_CUSTOMER B,
TEST_PRODUCT C
WHERE A.CUSTOMER_ID = B.CUSTOMER_ID
AND A.PRODUCT_CODE = C.PRODUCT_CODE
AND A.ORDER_STATUS = 'COMPLETE'
UNION ALL
SELECT /*+ INDEX(A TEST_ORDER_IX1) */
A.ORDER_ID,
B.CUSTOMER_NAME,
A.PRODUCT_CODE,
C.PRODUCT_NAME,
A.ORDER_AMOUNT
FROM TEST_ORDER A,
TEST_CUSTOMER B,
TEST_PRODUCT C
WHERE A.CUSTOMER_ID = B.CUSTOMER_ID
AND A.PRODUCT_CODE = C.PRODUCT_CODE
AND A.ORDER_STATUS = 'CANCEL'
);
SELECT * FROM DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST -rows -Projection +HINT_REPORT +outline');
Plan hash value: 761653089
---------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 |00:00:00.02 | 268 | | | |
| 1 | SORT AGGREGATE | | 1 | 1 |00:00:00.02 | 268 | | | |
| 2 | VIEW | | 1 | 20000 |00:00:00.02 | 268 | | | |
| 3 | UNION-ALL | | 1 | 20000 |00:00:00.02 | 268 | | | |
|* 4 | HASH JOIN | | 1 | 10000 |00:00:00.01 | 136 | 1722K| 1722K| 2039K (0)|
| 5 | TABLE ACCESS FULL | TEST_CUSTOMER | 1 | 10000 |00:00:00.01 | 45 | | | |
|* 6 | HASH JOIN | | 1 | 10000 |00:00:00.01 | 91 | 1316K| 1316K| 1083K (0)|
| 7 | TABLE ACCESS FULL | TEST_PRODUCT | 1 | 10 |00:00:00.01 | 6 | | | |
| 8 | TABLE ACCESS BY INDEX ROWID BATCHED| TEST_ORDER | 1 | 10000 |00:00:00.01 | 85 | | | |
|* 9 | INDEX RANGE SCAN | TEST_ORDER_IX1 | 1 | 10000 |00:00:00.01 | 30 | | | |
|* 10 | HASH JOIN | | 1 | 10000 |00:00:00.01 | 132 | 1722K| 1722K| 2011K (0)|
| 11 | TABLE ACCESS FULL | TEST_CUSTOMER | 1 | 10000 |00:00:00.01 | 45 | | | |
|* 12 | HASH JOIN | | 1 | 10000 |00:00:00.01 | 87 | 1316K| 1316K| 1048K (0)|
| 13 | TABLE ACCESS FULL | TEST_PRODUCT | 1 | 10 |00:00:00.01 | 6 | | | |
| 14 | TABLE ACCESS BY INDEX ROWID BATCHED| TEST_ORDER | 1 | 10000 |00:00:00.01 | 81 | | | |
|* 15 | INDEX RANGE SCAN | TEST_ORDER_IX1 | 1 | 10000 |00:00:00.01 | 28 | | | |
---------------------------------------------------------------------------------------------------------------------------------------
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$2")
OUTLINE_LEAF(@"SEL$3")
OUTLINE_LEAF(@"SET$1")
OUTLINE_LEAF(@"SEL$1")
NO_ACCESS(@"SEL$1" "from$_subquery$_001"@"SEL$1")
FULL(@"SEL$3" "C"@"SEL$3")
INDEX_RS_ASC(@"SEL$3" "A"@"SEL$3" ("TEST_ORDER"."ORDER_STATUS"))
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$3" "A"@"SEL$3")
FULL(@"SEL$3" "B"@"SEL$3")
LEADING(@"SEL$3" "C"@"SEL$3" "A"@"SEL$3" "B"@"SEL$3")
USE_HASH(@"SEL$3" "A"@"SEL$3")
USE_HASH(@"SEL$3" "B"@"SEL$3")
SWAP_JOIN_INPUTS(@"SEL$3" "B"@"SEL$3")
FULL(@"SEL$2" "C"@"SEL$2")
INDEX_RS_ASC(@"SEL$2" "A"@"SEL$2" ("TEST_ORDER"."ORDER_STATUS"))
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$2" "A"@"SEL$2")
FULL(@"SEL$2" "B"@"SEL$2")
LEADING(@"SEL$2" "C"@"SEL$2" "A"@"SEL$2" "B"@"SEL$2")
USE_HASH(@"SEL$2" "A"@"SEL$2")
USE_HASH(@"SEL$2" "B"@"SEL$2")
SWAP_JOIN_INPUTS(@"SEL$2" "B"@"SEL$2")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("A"."CUSTOMER_ID"="B"."CUSTOMER_ID")
6 - access("A"."PRODUCT_CODE"="C"."PRODUCT_CODE")
9 - access("A"."ORDER_STATUS"='COMPLETE')
10 - access("A"."CUSTOMER_ID"="B"."CUSTOMER_ID")
12 - access("A"."PRODUCT_CODE"="C"."PRODUCT_CODE")
15 - access("A"."ORDER_STATUS"='CANCEL')
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 3
---------------------------------------------------------------------------
3 - SET$1
- NO_FACTORIZE_JOIN(@"SET$1")
8 - SEL$2 / A@SEL$2
- INDEX(A TEST_ORDER_IX1)
14 - SEL$3 / A@SEL$3
- INDEX(A TEST_ORDER_IX1)
Note
-----
- this is an adaptive plan
101 rows selected.
|
사용자 정의 함수를 호출하지 않고 TEST_PRODUCT 테이블을 직접 조인함으로써 268 buffer를 소모함
실행 결과 비교
| 구분 | 처리 행 수 | Branch Buffers | 전체 Buffers | Branch 외 추가 Buffers | 상품 조회 실행 | A-Time |
| 사용자 정의 함수 직접 호출 | 20,000 | 256<br>130 + 126 | 40,256 | 40,000 | 실행계획에서 별도 확인 불가 | 0.45초 |
| Scalar Subquery로 함수 호출 | 20,000 | 258<br>131 + 127 | 298 | 40 | FAST DUAL Starts 10 + 10 | 0.02초 |
| TEST_PRODUCT 직접 조인 | 20,000 | 268<br>136 + 132 | 268 | 0 | TEST_PRODUCT Starts 1 + 1<br>A-Rows 10 + 10 | 0.02초 |
테스트1은 union all 상, 하단 쿼리 블록에서 각각 TEST_CUSTOMER, TEST_ORDER 테이블을 읽고 이때 각각 130, 126의 Buffers만 소모함, 하지만 ID 3번 UNION-ALL 부분에서 Buffers를 약 4만가량 소모함, xplan 결과에선 이 이유를 찾을수 없음
테스트2는 Scalar Subquery의 Starts와 Buffers를 확인하여 동일한 PRODUCT_CODE 결과가 재사용됐는지 확인 가능함, 총 298 Buffers를 소모함
테스트3은 TEST_PRODUCT는 각 Branch에서 Starts 1, A-Rows 10인것을 확인할 수 있고 총 268 Buffers를 소모함
결론 :
UNION ALL 쿼리의 SELECT 절에서 사용자 정의 함수를 직접 호출한 경우 COMPLETE와 CANCEL Branch 자체에서는 각각 130과 126의 Buffers만 사용함
하지만 상위 UNION-ALL의 전체 Buffers는 40,256으로 증가함
두 Branch의 Buffers 256을 제외하면 함수 처리 과정에서 40,000개의 Buffers가 추가로 발생함
실행계획만 보면 UNION ALL Operation에서 많은 Buffers가 발생한 것처럼 보일 수 있음
실제로는 GET_PRODUCT_NAME 함수 내부에서 TEST_PRODUCT 테이블을 조회하여 발생한 Buffers로 해당 내용은 실행계획에 별도 Operation으로 표시되지 않음
사용자 정의 함수 호출을 Scalar Subquery로 감싼 경우 각 Branch의 FAST DUAL Starts가 10으로 확인됨
각 Branch에서 10,000건을 처리했지만 실제 PRODUCT_CODE는 10종류이므로 동일한 입력값의 결과가 Scalar Subquery Cache에서 재사용됨
이로 인해 전체 Buffers가 40,256에서 298로 감소했고 A-Time은 0.45초에서 0.02초로 감소함
사용자 정의 함수는 그대로 사용했지만 반복 호출 횟수가 줄어들면서 함수 내부 SQL에서 발생하는 Buffers도 감소한것임
사용자 정의 함수를 제거하고 TEST_PRODUCT 테이블을 직접 조인한 경우 전체 Buffers는 268이고 A-Time은 0.02초로 확인됨
TEST_PRODUCT는 각 Branch에서 한 번씩 읽었고 함수 호출에 따른 컨텍스트 스위칭 부하(SQL과 PL/SQL 사이의 전환)도 발생하지 않음
본문 테스트에서는 TEST_PRODUCT를 직접 조인한 방식의 Buffers가 가장 적었고 Scalar Subquery 방식도 직접 함수 호출 방식보다 Buffers가 크게 감소했음
다만 Scalar Subquery Cache의 효과는 입력값의 종류와 분포에 따라 달라질 수 있음
예를들어 입력값 종류가 많거나 캐시 충돌이 발생하면 본문 테스트처럼 함수 호출 횟수(Buffers)가 크게 줄어들지 않을 수 있음
하지만 일반적으로 함수를 조인으로 푸는 방식이 쉽지는 않기 때문에 대안으로 Scalar Subquery로 변경하는 방안을 적용해 볼 수 있음
이렇게 UNION ALL 상위 Operation의 Buffers가 하위 Operation보다 크게 증가하는 경우 UNION ALL 자체를 원인으로 판단하면 안 됨
본문처럼 SELECT 절에 사용자 정의 함수가 있는지와 해당 함수 내부에서 SQL을 수행하는지도 같이 확인해봐야됨
참조 :
https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Scalar-Subquery-Expressions.html
'ORACLE > Performance Tuning' 카테고리의 다른 글
| 오라클 19c CLUSTER_BY_ROWID 힌트 사용 테스트 (0) | 2026.08.14 |
|---|---|
| 오라클 19c Batch I/O 동작 및 버퍼 사용량 테스트 (0) | 2026.08.11 |
| 오라클 19c stale percent 변경 및 자동통계수집 (0) | 2026.06.22 |
| 오라클 19c 펜딩 통계(Pending Statistics) (0) | 2026.06.18 |
| 오라클 19c 병렬 ITAS를 이용한 Clustering Factor 개선 테스트 (0) | 2026.06.17 |