참고소스
https://gitlab.com/kimdongy1000/sqltuningproject
EX23 참고
스칼라 서브쿼리
이름에도 알 수 있다 시피 스칼라라는 단어 뜻 자체가 단일 값을 의미합니다 즉 서브 쿼리 결과가 반드시 하나의 값으로 귀결되어야 합니다
쿼리
1
2
3
4
5
6
7
8
9
SELECT empno,
ename,
sal,
hiredate,
(SELECT d.dname FROM dept d WHERE d.deptno = e.deptno) AS dname
FROM emp e
WHERE sal >= 2000
지금처럼 거의 대부분 select 문안에서 하나의 값을 리턴하는 쿼리처럼 사용됩니다 이때 이 쿼리 emp 와 dept는 deptno를 기준으로 유일한 하나의 값을 return 하게 됩니다 이 쿼리는 다음과 같은 쿼리의 효과와 동일하다
1
2
3
4
5
6
7
8
9
10
SELECT e.empno ,
ename ,
e.sal ,
e.hiredate ,
d.dname
FROM emp e LEFT OUTER JOIN dept d ON e.deptno = d.deptno
WHERE e.sal >= 2000
이렇게 조인한 결과와 동일하고 dept 테이블과 조인에 실패하면 dname 이 null 이 나오는 것도 동일하다 차이가 있다면 스칼라 서브 쿼리는 처리 과정에서 캐싱 작용이 일어나는 데 있다
스칼라 서브쿼리 캐싱 효과
스칼라 서브 쿼리로 조인하면 오라클은 조인 횟수를 최소화하려고 입력 값과 출력값을 내부 캐시에 저장해둔다 조인할 때마다 일단 캐시에서 이력 값을 찾아보고 있으면 출력값을 return 하고 없으면 조인을 해서 찾아두고 결과가 나오면 그 결과를 버리지 않고 캐시에 저장해둔다 만약 캐시 해두지 않고 매번 조인을 하게 된다면 데이터가 많은 스칼라 서브 쿼리는 성능이 망가지게 됩니다
위 쿼리에서 입력값은 e.deptno이고 출력값은 d.name이다
캐싱의 부작용
모든 캐시가 다 그렇듯 캐시 공간은 늘 부족하다 스칼라 서브쿼리 캐싱 효과는 입력 값의 종류가 소수여서 해시 충돌 가능성이 적을 때 효과가 있다 반대의 경우라면 캐시를 매번 확인하는 비용 때문에 오히려 성능이 나빠지고 CPU 사용률만 높게 만들고 메모리도 더 사용한다
부작용쿼리
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
EXPLAIN PLAN FOR
SELECT t.TRANSACTION_ID
,t.CUSTOMER_ID
,t.SALES_ORG_ID
,t.TRANSACTION_TYPE_CODE
,(SELECT TRANSACTION_TYPE_NAME FROM EX23_TRANSACTION_TYPE WHERE TRANSACTION_TYPE_CODE = t.TRANSACTION_TYPE_CODE) TRANSACTION_TYPE_NAME
FROM EX23_TRANSACTION t
WHERE t.TRANSACTION_DATE >= to_char(add_months(sysdate , -3) , 'yyyymmdd')
PLAN_TABLE_OUTPUT |
-----------------------------------------------------------------------------------------------+
Plan hash value: 1828152491 |
|
-----------------------------------------------------------------------------------------------|
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time ||
-----------------------------------------------------------------------------------------------|
| 0 | SELECT STATEMENT | | 3748 | 153K| 141 (2)| 00:00:01 ||
|* 1 | HASH JOIN RIGHT OUTER| | 3748 | 153K| 141 (2)| 00:00:01 ||
| 2 | TABLE ACCESS FULL | EX23_TRANSACTION_TYPE | 20 | 260 | 3 (0)| 00:00:01 ||
|* 3 | TABLE ACCESS FULL | EX23_TRANSACTION | 3748 | 106K| 138 (2)| 00:00:01 ||
-----------------------------------------------------------------------------------------------|
|
Predicate Information (identified by operation id): |
--------------------------------------------------- |
|
1 - access("T"."TRANSACTION_TYPE_CODE"="C"."TRANSACTION_TYPE_CODE"(+)) |
3 - filter("T"."TRANSACTION_DATE">=TO_CHAR(ADD_MONTHS(SYSDATE@!,(-3)),'yyyymmdd')) |
먼저 동일한 하나의 쿼리를 보자 이 쿼리에 EX23_TRANSACTION 테이블은 총 10만 건이 들어있다 서브 쿼리는 20건이 존재한다 20개의 데이터 면 캐시에 모두 저장하고 남는다 지금처럼 3748건을 읽는 동안 거래구분코드별 조인 액세서는 최소 한 번씩만 발생하고 그 이후로는 모두 캐시에서 데이터를 찾으므로 조인 성능을 높이는데 큰 도움이 된다
만약 서브쿼리의 데이터가 엄청많다면
1
2
3
4
5
6
7
8
9
10
EXPLAIN PLAN FOR
SELECT t.TRANSACTION_ID
,t.CUSTOMER_ID
,t.SALES_ORG_ID
,t.TRANSACTION_TYPE_CODE
,(SELECT TRANSACTION_TYPE_NAME FROM EX23_TRANSACTION_TYPE WHERE TRANSACTION_TYPE_CODE = t.TRANSACTION_TYPE_CODE) TRANSACTION_TYPE_NAME
FROM EX23_TRANSACTION t
WHERE t.TRANSACTION_DATE >= to_char(add_months(sysdate , -3) , 'yyyymmdd')
동일한 쿼리라고 생각을 하고 이때 서브쿼리 데이터가 20건에서 백만 건으로 늘어났다고 가정을 하자 이 데이터는 아무리 캐시 크기를 잡는다고 해도 데이터가 너무 많다 그래서 메인 쿼리 3748건을 서칭하는 동안 매번 서브 쿼리의 백만 건의 데이터에서 서칭을 하게 되는데 이때 캐시 히트율이 낮아질 것이므로 그만큼 성능은 더 떨어진다 이런 쿼리는 스칼라 서브 쿼리보다 조인으로 해결하는 것이 성능에 유리하다 만약 같은 상황으로 조인으로 해결한다면 다음과 같은 성능을 보여주게 된다
서브쿼리 VS 조인
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
EXPLAIN PLAN FOR
SELECT t.TRANSACTION_ID
,t.CUSTOMER_ID
,t.SALES_ORG_ID
,t.TRANSACTION_TYPE_CODE
,c.TRANSACTION_TYPE_NAME
FROM EX23_TRANSACTION t LEFT OUTER JOIN EX23_TRANSACTION_TYPE C ON t.TRANSACTION_TYPE_CODE = c.TRANSACTION_TYPE_CODE
WHERE t.TRANSACTION_DATE >= to_char(add_months(sysdate , -3) , 'yyyymmdd')
-----------------------------------------------------------------------------------------------+
Plan hash value: 1828152491 |
|
-----------------------------------------------------------------------------------------------|
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time ||
-----------------------------------------------------------------------------------------------|
| 0 | SELECT STATEMENT | | 3748 | 153K| 141 (2)| 00:00:01 ||
|* 1 | HASH JOIN RIGHT OUTER| | 3748 | 153K| 141 (2)| 00:00:01 ||
| 2 | TABLE ACCESS FULL | EX23_TRANSACTION_TYPE | 20 | 260 | 3 (0)| 00:00:01 ||
|* 3 | TABLE ACCESS FULL | EX23_TRANSACTION | 3748 | 106K| 138 (2)| 00:00:01 ||
-----------------------------------------------------------------------------------------------|
|
Predicate Information (identified by operation id): |
--------------------------------------------------- |
|
1 - access("T"."TRANSACTION_TYPE_CODE"="C"."TRANSACTION_TYPE_CODE"(+)) |
3 - filter("T"."TRANSACTION_DATE">=TO_CHAR(ADD_MONTHS(SYSDATE@!,(-3)),'yyyymmdd')) |
이렇게 성능이 좋아진 것을 볼 수 있다 이것으로 정리할 수 있는 내용은 다음과 같다
스칼라 서브 쿼리의 데이터가 적으면 캐시 히트율이 높아지기 때문에 이때는 조인보다 스칼라 서브 쿼리가 유리하다 -> 스칼라 서브 쿼리가 소규모일 때 추천
만약 스칼라 서브 쿼리의 데이터가 너무 많다면 캐시 히트율이 낮아지기 때문에 이때는 조인에 비해 턱없이 성능이 좋지 않다 -> 스칼라 서브 쿼리가 대규모일 때 추천
만약 입력값이 다양하다면 캐싱은 더 이상 무리라고 옵티마이저가 판단하기 때문에 저장을 하지 않고 매번 서칭을 하게 됩니다
만약 메인 쿼리가 결과가 적다면 어떨까 이때도 소규모 스칼라 서브 쿼리라 할지라도 결과 셋이 소규모이기 때문에 캐싱 된 입력값을 제대로 활용 못할 수도 있다 그렇기 때문에 결과가 작은 경우라면 굳이 스칼라 서브쿼리 보다는 조인으로 해결하는 것이 좋다