SQL 튜닝이론 - 스칼라 서브쿼리 튜닝을 통한 최적화 방안

SQL 튜닝이론 - 스칼라 서브쿼리 튜닝을 통한 최적화 방안

 

스칼라 서브쿼리의 특징

 

아래와 같은 GET_DNAME 함수를 사용하는 아래 쿼리를 실행하면, 함수 안에 있는 SELECT 쿼리를 메인 쿼리 건수만큼 재귀적으로 반복 실행합니다.

 

create or replace function GET_DNAME(p_deptno number) return varchar2
is
  l_dname dept.dname%TYPE;
begin
  select dname into l_dname from dept where deptno = p_deptno;
  return l_dname;
exception
  when others then
    return null;
end;
/

SELECT EMPNO, ENAME, SAL, HIREDATE
, GET_DNAME(E.DEPTNO) AS DNAME
FROM EMP E
WHERE SAL >= 2000

 

아래 스칼라 서브쿼리는 메인쿼리의 레코드마다 정확히 하나의 값만 반환합니다. 메인쿼리 건수만큼 DEPT 테이블을 반복해서 읽는다는 측면에서 함수와 비슷해 보이지만, 함수처럼 '재귀적으로' 실행하는 구조가 아닙니다. 스칼라 서브쿼리는 컨텍스트스위칭 없이 메인쿼리와 서브쿼리가 하나의 쿼리로 실행됩니다.

 

SELECT EMPNO, ENAME, SAL, HIREDATE
, (SELECT dname from dept d where d.deptno = e.deptno) AS DNAME
FROM EMP E
WHERE SAL >= 2000

 

스칼라 서브쿼리를 사용한 위 쿼리문은 아래 OUTER 조인문 처럼 NL 조인 방식으로 실행됩니다. DPET와 조인에 실패하는 EMP 레코드는 DNAME에 NULL 값을 출력한다는 점도 동일합니다. 차이가 있다면, 스칼라 서브쿼리는 처리 과정에서 캐싱이 발생합니다.

 

SELECT /*+ordered use_nl(d)*/
e.EMPNO, e.ENAME, e.SAL, e.HIREDATE, d.dame
FROM EMP E, dept d
where d.deptno(+) = e.deptno
WHERE e.SAL >= 2000

 

스칼라 서브쿼리 캐싱 효과

스칼라 서브쿼리로 조인하면 오라클은 조인 횟수를 최소화하기위해 입력 값과 출력 값을 캐싱합니다. 조인 할 때마다 일단 캐시버퍼에서 '입력 값'을 찾아보고, 찾으면 저장된 출력 값을 반환합니다. 캐시에서 찾지 못할 때만 조인을 수행하며 조인 결과는 버리지 않고 캐시에 저장합니다. 스칼라 서브쿼리의 입력 값은, 스칼라 서브쿼리에서 참조하는 메인 쿼리의 컬럼의 값입니다.

 

  select empno, ename, sal, hiredate
  ,(
    select d.dname -- 출력 값 : D.dame
    from dept d
    where d.deptno = e.empno -- 입력 값 : e.empno
  )
  from emp e
  where sal >= 2000

 

스칼라 서브쿼리 캐싱은 필터 서브쿼리 캐싱과 같은 기능입니다. 이런 캐싱 메커니즘은 조인 성능을 높이는데 큰 도움이 됩니다. 메인쿼리 집합이 아무리 커도 조인할 데이터를 대부분 캐시에서 찾는다면, 조인 수행횟수를 최소화할 수 있기 때문입니다. 캐싱은 쿼리 단위로 이루집니다. 쿼리를 시작할 때 PGA 메모리에 공간을 할당하고, 쿼리를 수행하면서 공간을 채워나가며, 쿼리를 마치는 순간 공간을 반환합니다.

 

스칼라 서브쿼리의 캐싱 기능을 활용한 튜닝 기법이 있습니다. SELECT-LIST 에 사용한 함수는 메인쿼리 결과 건수 만큼 반복 수행되는데, 아래와 같이 스칼라 서브쿼리를 덧씌우면 캐싱을 통해 호출 횟수를 최소화할 수 있습니다. 함수에 내장된 SELECT 쿼리도 그만큼 덜 수행하게 됩니다. 사용자 정의함수는 무겁기 때문에 실행횟수를 최소화 해야합니다.

 

SELECT EMPNO, ENAME, SAL, HIREDATE
, (select GET_DNAME(E.DEPTNO) FROM DUAL) AS DNAME
FROM EMP E
WHERE SAL >= 2000

 

스칼라 서브쿼리 캐싱 부작용

 

캐시 공간은 늘 부족합니다. 스칼라 서브쿼리에 사용하는 캐시도 매우 작은 메모리 공간입니다. 결론적으로 스칼라 서브쿼리의 캐싱 효과는 입력 값 종류가 소수여서 해시 충돌 가능성이 작을 때 효과가 있습니다. 반대의 경우라면 캐시를 매 번 확인하는 비용 때문에 오히려 성능이 나빠지고 메모리와 CPU 사용률만 높아집니다.

 

아래 쿼리는 거래구분코드로 20개 값이 존재합니다. 20개면 캐시에 모두 저장하고도 남는 수준입니다. 그렇다면 메인쿼리에서 50,000개의 거래를 읽는 동안 거래구분코드별 조인 액세스는 최초 한 번씩만 발생합니다. 이후로는 모두 캐시에서 데이터를 찾아 조인 성능을 높이는데 큰 도움이 됩니다.

 

select 거래번호, 고객번호, 영업조직ID, 거래구분코드
,(select 거래구분명 from 거래구분 where 거래구분코드 = T.거래구분코드) 거래구분명
From 거래 t
where 거래일자 >= TO_CHAR(ADD_MONTHS(SYSDATE, -3), 'YYYYMMDD')

 

아래의 쿼리에 사용된 테이블에 저장된 고객번호의 종류는 100만개 입니다. 캐시에 도저히 담을 수 없을만큼 많은 고객번호가 존재합니다. 그렇다면 메인쿼리에서 50,000 개 거래를 읽는 동안 캐시를 매번 탐색하지만, 대부분 데이터를 찾지 못해 결국 조인을 해야합니다. 불필요한 캐시 탐색 때문에 일반 조인문보다 느려지고 불필요한 자원만 낭비하게 됩니다.

 

select 거래번호, 고객번호, 영업조직ID, 거래구분코드
,(select 고객명 from 고객 where 고객번호 = T.고객번호) 고객명
From 거래 t
where 거래일자 >= TO_CHAR(ADD_MONTHS(SYSDATE, -3), 'YYYYMMDD')

 

위 쿼리에서 스칼라 서브쿼리가 성능에 도움이 되려면, 최근 3개월간 수백 명 이내 일부 고객만 거래만 조회해야합니다.함수 호출을 줄이기 위해 스칼라 서브쿼리를 덧씌우는 경우가 있습니다. 아래 쿼리문에서 체결 테이블에 입력된 매도계좌번호, 매수계좌번호가 무수히 많다면 스칼라 서브쿼리 캐싱효과를 전혀 기대할 수 없습니다

 

select
매수계좌번호
,(select acnt_nm(매수계좌번호) from dual) 매수계좌명
,매도계좌번호,
(select acnt_nm(매도계좌번호) from dual) 매도계좌명
from 체결
where 종목코드 =: 종목코드
and 채결일자 =: 채결일자
...

 

메인 쿼리 집합이 매우 작은 경우 스칼라 서브쿼리 캐싱이 성능에 도움을 주지 못합니다. 스칼라 서브쿼리의 캐싱은 쿼리 단위로 이루어집니다. 쿼리 단위로 쓰고 버린다는 뜻으로 메인쿼리 집합이 클 수록 재활용성이 높아 효과도 좋습니다. 반대로 메인쿼리 집합이 작으면 캐시 재사용성도 낮습니다.

 

예를 들어, 아래 쿼리는 스칼라 서브커리 캐싱 효과를 거의 기대할 수 없고 오히려 성능을 떨어뜨리는 요인입니다. 고객당 계좌가 많지 않기 때문에 보통 고객당 관리지점과 개설지점이 한 개일 것이므로 쓰지도 않을 캐시를 할당해서 값을 채웠다가 바로 버리게 됩니다.

 

select
(select brcn_nm(관리지점코드) from dual) 관리지점명
,(select brcn_nm(개설지점코드) from dual) 개설지점명
from 계좌
where 고객번호 = :고객번호

 

쿼리 단위로는 느낄수 없는 미미한 차이겠지만, 전체적으로 이런 패턴을 불필요하게 많이 사용했을 때 시스템에 미치는 영향이 커집니다.

두 개 이상의 값 반환

 

아래는 스칼라 서브쿼리를 사용할 때의 실행계획이다.

 

c.고객번호, c.고객명
,(
  select round(avg(거래금액), 2) 평균거래금액
  from 거래
  where 거래일시 >= trunc(sysdate, 'mm')
  and 고객번호 = c.고객번호
)
from 고객 c
where c.가입일시 >= trunc(add_months(sysdate,-1), 'mm')

-- EXECUTION PLAN
-- SELECT STATEMENT
--   SORT AGGREGATE
--     TABLE ACCESS BY INDEX ROWID BATCHED OF '거래'
--       INDEX RANGE SCAN OF '거래_X02' (INDEX)
--     TABLE ACCESS (FULL) OF '고객'
--      INDEX (RANGE SCAN) OF '고객_X01' (INDEX)

 

메인쿼리가 실행계획 아래쪽에 있고, 스칼라 서브쿼리 부분은 위쪽에 있습니다. 실행계획 표현방식은 NL 조인과 다르지만, 프로세싱 과정은 NL 조인과 같습니다. NL조인처럼 부분범위 처리도 가능합니다. NL 조인과 다른점이 있다면 캐싱효과가 나타난다는 점입니다.

 

스칼라 서브쿼리에는 치명적인 제약 사항은 두 개 이상의 값을 반환할수 없다는 것 입니다.

 

c.고객번호, c.고객명
,(
  select avg(거래금액), min(거래금액), max(거래금액)
  from 거래
  where 거래일시 >= trunc(sysdate, 'mm')
  and 고객번호 = c.고객번호
)
from 고객 c
where c.가입일시 >= trunc(add_months(sysdate,-1), 'mm')

 

대안으로 작성된 아래의 쿼리는 거래 테이블에서 같은 데이터를 반복해서 읽는 비효율이 있습니다.

 

c.고객번호, c.고객명
,(select avg(거래금액) from 거래 where 거래일시 >= trunc(sysdate, 'mm') and 고객번호 = c.고객번호)
,(select min(거래금액) from 거래 where 거래일시 >= trunc(sysdate, 'mm') and 고객번호 = c.고객번호)
,(select max(거래금액) from 거래 where 거래일시 >= trunc(sysdate, 'mm') and 고객번호 = c.고객번호)
from 고객 c
where c.가입일시 >= trunc(add_months(sysdate,-1), 'mm')

 

이럴 때 사용해온 전통적인 튜닝방식은 다음과 같습니다.

 

select c.고객번호, c.고객명
,to_number(sbstr(거래금액, 1, 10))
,to_number(sbstr(거래금액, 11, 10))
,to_number(sbstr(거래금액, 21, 10))
,(select avg() from 거래 where 거래일시 >= trunc(sysdate, 'mm') and 고객번호 = c.고객번호)
,(select min(거래금액) from 거래 where 거래일시 >= trunc(sysdate, 'mm') and 고객번호 = c.고객번호)
,(select mas(거래금액) from 거래 where 거래일시 >= trunc(sysdate, 'mm') and 고객번호 = c.고객번호)
from (
select c.고객번호, c.고객명
, (select lpad(avg(거래금액), 10) || lpad(min(거래금액), 10) || max(거래금액)
  from 거래
  where 거래일시 >= trunc(sysdate, 'mm')
  and 고객번호 = c.고객번호) 거래금액
from 고객 C
where c.가입일시 >= trunc(add_months(sysdate,-1), 'mm')
)

 

구하는 값들을 문자열로 모두 결합하고, 바깥쪽 엑세스 쿼리에서 substr 함수로 다시 분리하는 방식입니다. 아래와 같이 오브젝트 TYPE을 사용하는 방법도 있으나, TYPE을 미리 선언해 두어야 하는 불편함 때문에 잘 쓰이지 않습니다.

 

create or replace type 거래금액_T as object
(평균거래금액 Number, 최소거래금액 number, 최대거래금액 Number)

select c.고객번호, c.고객명
,거래.금액.평균거래금액
,거래.금액.최소거래금액
,거래.금액.최대거래금액
from (
select c.고객번호, c.고객명
, (
  select 거래금액_T(avg(거래금액),min(거래금액), max(거래금액) ) 금액
  from 거래
  where 거래일시 >= trunc(sysdate, 'mm')
  and 고객번호 = c.고객번호) 거래
where c.가입일시 >= trunc(add_months(sysdate,-1), 'mm')
)

 

두 개 이상의 값을 반환하고 싶을 때, 이런저런 고민 없이 아래와 같이 인라인 뷰를 사용하면 편합니다.

 

c.고객번호, c.고객명, t.평균거래, t.최소거래, t.최대거래
from 고객 c
, (
  select 고객번호, avg(거래금액) 평균거래, min(거래금액) 최소거래, max(거래금액) 최대거래
  from 거래
  where 거래일시 >= trunc(sysdate, 'mm')
  group by 고객번호
) t
where c.가입일시 >= trunc(add_months(sysdate,-1), 'mm')
and t.고객번호(+) = c.고객변호

-- EXECUTION PLAN
-- SELECT STATEMENT
--   HASH (GROUP BY)
--    NESTED LOOPS (OUTER)
--      TABLE ACCESS BY INDEX ROWID OF '고객'
--        INDEX RANGE SCAN OF '고객_X01'
--      TABLE ACCESS BY INDEX ROWID OF '거래'
--        INDEX RANGE SCAN OF '거래_X02'

 

그런데 인라인 뷰를 이용하면, 당월 거래 전체를 읽어야 하거나 GROUP BY 때문에 부분범위 처리가 안 되는 문제(merge 시)가 있습니다.

 

c.고객번호, c.고객명, t.평균거래, t.최소거래, t.최대거래
from 고객 c
, (
  select /*+no_merge push_pred*/
  고객번호, avg(거래금액) 평균거래, min(거래금액) 최소거래, max(거래금액) 최대거래
  from 거래
  where 거래일시 >= trunc(sysdate, 'mm')
  group by 고객번호
) t
where c.가입일시 >= trunc(add_months(sysdate,-1), 'mm')
and t.고객번호(+) = c.고객변호

-- EXECUTION PLAN
-- SELECT STATEMENT
--    NESTED LOOPS (OUTER)
--      TABLE ACCESS BY INDEX ROWID OF '고객'
--        INDEX RANGE SCAN OF '고객_X01'
--      VIEW PUSHED PREDICATE
--        SORT(GROUP BY)
--          TABLE ACCESS BY INDEX ROWID OF '거래'
--            INDEX RANGE SCAN OF '거래_X02'

 

스칼라 서브쿼리 UNNESTING

 

스칼라 서브쿼리도 NL 방식으로 조인하므로 캐싱효과가 크지 않으면 랜덤 i/o 부담이 있습니다. 그래서 다른 조인 방식을 선택하기 위해 스칼라 서브쿼리를 일반 조인문으로 변환해야하는 경우가 많습니다. 특히 병렬 쿼리에선 될 수 있으면 스칼라 서브쿼리를 사용하지 않아야 합니다. 대량 데이터를 처리하는 병렬 쿼리는 해시 조인으로 처리해야 효과적이기 때문입니다. 어떤 이유에서건, 사용자가 직접 쿼리를 변환해야 하는 상황에서 길고 복잡한 스칼라 서브쿼리를 만나면 난감합니다.

 

오라클 12c 부터 스칼라 서브쿼리도 unnesting이 가능합니다 _optimizer_unnest_scalar_sql_ 파라미터를 false로 설정함으로써 일단 문제를 해결하는데, 장기적으로 이 기능을 쓸지 여부는 시스템 특성에 따라 고민해봐야합니다.이 파라미터를 true로 설정하면, 스칼라 서브쿼리를 unnesting 할지 여부를 옵티마이저가 결정합니다. false로 설정하면 옵티마이저가 이 기능을 사용하지 않지만 사용자가 unnest 힌트로 유도해야 합니다. 아래는 스칼라 서브쿼리를 unnesting 할 때 실행계획입니다. 스칼라 서브쿼리인데도 NL 조건이 아닌 해시 조인으로 실행 될수 있는 이유는 unnesting 되었기 때문입니다.

 

c.고객번호, c.고객명
,(select /*+unnest*/ round(avg(거래금액), 2) 평균거래금액
  from 거래
  where 거래일시 >= trunc(sysdate, 'mm')
  and 고객번호 = c.고객번호
)
from 고객 c
where c.가입일시 >= trunc(add_months(sysdate,-1), 'mm')

-- EXECUTION PLAN
-- SELECT STATEMENT
--  HASH JOIN(OUTER)
--    TABLE ACCESS FULL OF '고객'
--    VIEW OF 'SYS.VW_SSQ_1'
--      HASH (GROUP BY)
--        TABLE ACCESS (FULL) OF '거래'

-- Unnest 와 merge 힌트를 같이 사용했을 때의 실행계획이다.

-- SELECT STATEMENT
--  HASH (GROUP BY)
--    HASH JOIN(OUTER)
--      TABLE ACCESS FULL OF '고객'
--      TABLE ACCESS (FULL) OF '거래'

 

12c 업그레이드 이후 스칼라 서브쿼리 UNNESTING 으로 인해 일부 쿼리에 문제가 생겼을 때, _optimizer_unnest_scalar_sql_ 파라미터를 false로 설정하지 않고 아래와 같이 no_unnest 힌트를 이용해 부분적으로 문제를 해결할 수도 있습니다

 

c.고객번호, c.고객명
,(select /*+no_unnest*/ round(avg(거래금액), 2) 평균거래금액
  from 거래
  where 거래일시 >= trunc(sysdate, 'mm')
  and 고객번호 = c.고객번호
)
from 고객 c
where c.가입일시 >= trunc(add_months(sysdate,-1), 'mm')

-- EXECUTION PLAN
-- SELECT STATEMENT
--   SORT(AGGREGATE)
--     TABLE ACCESS BY INDEX ROWID OF '거래'
--       INDEX (RANGE SCAN) OF '거래_X02'
--   TABLE ACCESS BY INDEX ROWID OF '고객'
--     INDEX (RANGE SCAN) OF '고객_X01'