어느 한 집합에 있는 데이터 중 다른 집합에 존재하지 않는 데이터를(차집합) 추줄하기 위한 방법으로 MINUS를 사용.
MINUS는 두 집합 간의 차집합을 추출할 때，SQL 작성이 쉽고 가독성도 뛰어나 자주 사용
하지만 MINUS를 사용한 SQL이 성능 문제가 발생할 경우，SQL을 변경하지 않고 성능을 개선하려면
MINUS 대신 NOT EXISTS로 대체한 SQL로 재작성하여 성능을 개선하는 것이 가능할 수 있다.
추출 대상이 되는 데이터 집합의 Where절에 효율적인 상수조건이 있고 비교 대상인 다른
집합에는 Where절이 없는 경우 SQL을 각각 MINUS와 NOT EXISTS를 사용하여 작성한 후 두 SQL간 성능 차이를 확인.
alter session set trace
var b1 varchar2(10)
var b2 nurnber
var b3 nurnber
exec :b1 := 'A'
exec :b2 := 200000
exec :b3 := 300000
SELECT c1, c2, c3
FROM SUBQUERY_T2
WHERE c2 = :b1
AND c1 >= :b2
AND c1 <= :b3
MINUS
SELECT c4, c5, c6
FROM SUBQUERY_T1
alter session set trace
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.001 0 0 0 0
Execute 1 0.000 0.002 0 0 0 0
Fetch 21 19.921 20.198 37315 37583 0 1923
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 23 19.921 20.202 37315 37583 0 1923
Misses in library cache during parse : 1
Optimizer Goal : ALL_ROWS
Parsing user : SYSTEM (ID=5)
Rows Row Source Operation
------- -----------------------------------------------------------------------
1923 MINUS (cr=37583 pr=37315 pw=0 time=3587 us)
3846 SORT UNIQUE (cr=275 pr=12 pw=0 time=6664 us cost=276 size=46152 card=3846)
3846 FILTER (cr=275 pr=12 pw=0 time=31628 us)
3846 TABLE ACCESS BY INDEX ROWID SUBQUERY_T2 (cr=275 pr=12 pw=0 time=21209 us cost=275 size=46152 card=3846)
3846 INDEX RANGE SCAN SUBQUERY_T2_IDX_01 (cr=12 pr=12 pw=0 time=6449 us cost=12 size=0 card=3846)
250000 SORT UNIQUE (cr=37308 pr=37303 pw=0 time=408391 us cost=84113 size=192000000 card=16000000)
16000000 TABLE ACCESS FULL SUBQUERY_T1 (cr=37308 pr=37303 pw=0 time=27021566 us cost=10338 size=192000000 card=16000000)
트레이스 결과를 보면， 비교 대상인 SUBQUERY_T1 에는 조회 조건이 없어 FULL Table Scan
으로 수행하고 모든 데이터 (1,600 만건)에 대한 정렬 작업까지 수행하고 있다.
SELECT c1,
c2,
c3
FROM SUBQUERY_T2 T2
WHERE c2 = :bl
AND c1 >= :b2
AND c1 <= :b3
AND NOT EXISTS (SELECT /*+ UNNEST NL_AJ */'x'
FROM SUBQUERY_T1 T1
WHERE t1.c4 = t2.c1
AND t1.c5 = t2.c2
AND t1.c6 = t2.c3);
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.001 0 0 0 0
Execute 1 0.000 0.004 0 0 0 0
Fetch 1 0.000 0.000 0 0 0 0
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 3 0.000 0.004 0 0 0 0
Misses in library cache during parse : 1
Misses in library cache during execute : 1
Optimizer Goal : ALL_ROWS
Parsing user : SYSTEM (ID=5)
Rows Row Source Operation
------- -----------------------------------------------------------------------
0 FILTER (cr=0 pr=0 pw=0 time=0 us)
0 NESTED LOOPS ANTI (cr=0 pr=0 pw=0 time=0 us cost=32988 size=912 card=38)
0 TABLE ACCESS BY INDEX ROWID SUBQUERY_T2 (cr=0 pr=0 pw=0 time=0 us cost=275 size=46152 card=3846)
0 INDEX RANGE SCAN SUBQUERY_T2_IDX_01 (cr=0 pr=0 pw=0 time=0 us cost=12 size=0 card=3846)
0 TABLE ACCESS BY INDEX ROWID SUBQUERY_T1 (cr=0 pr=0 pw=0 time=0 us cost=15 size=1476960 card=123080)
0 INDEX RANGE SCAN SUBQUERY_T1_IDX_01 (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=13)
NOT EXISTS를 사용한 SQL의 트레이스 결과 Full Table Scan은 인덱스 스캔으로 수행 방식이 변경,
정렬 작업이 제거되어，MINUS를 사용했을 때 보다 성능이 크게 개선.
MINUS를사용한 SQL 성능문제
비교 대상 테이블에서 데이터 추줄하는 방식이 비효율(FTS)인데도 불구하고 조회 조건이 없어 개선을 하기 힘들다.
비교 대상 테이블에서 많은 데이터가 추출되면 정렬 작업이 과다하게 발생한다는 것.
테스트2의 예제와 같이 비교대상 테이블에 대한 Where절에 조건이 없어 Full Table Scan을
수행해 성능 문제가 발생하거나，비교 대상 테이블에서 추출되는 데이터가 많아 정렬 작업에 대한 부하가 과도한 경우 MINUS를 NOT EXISTS로 변경하여 개선.
MINUS를 NOT EXISTS로 변경한 SQL은 추출된 데이터 총 건수가 서로 다른 결과를 추출할 수 있다.
MINUS를 사용하는 SQL의 실행계획 중 SORT UNIQUE 오퍼레이선은 MINUS 연산으로 Unique 값을 추줄한다는 것을 의미.
NOT EXISTS를 사용한 SQL의 실행계획에는 SORT UNIQUE 오퍼레이션이 존재하지 않는다.
따라서 Select절에 나열된 컬럼의 조합이 Unique 하지 않다면 NOT EXISTS를 이용한 SQL은 중복된 값이 추출될 가능성이 있다.
이런 이유로 MINUS를 무조건 NOT EXISTS로 변경한 경우, 성능은 개선되었지만 데이터 정합성이 훼손되는 현상이 발생할 수 있는 위험이 존재한다.
일반적으로 MINUS 사용시 대부분 Select 절에 Primary Key컬럼(Unique컬럼)이 포함되어 있어
NOT EXISTS로 바꿀경우 데이터가 잘못 추출되는 문제가 발생하지 않는다. 테스트 SQL도
Primary Key 컬럼이 Select절에 포함.(DISTINCT 처리를 하지 않고 단순히 NOT EXISTS로
변경하여도 정합성이 훼손되지 않은 것이다. 하지만 이는 일반적인 경우일 뿐 Select절에 나열된 컬럼 조합이 Unique 하지 않은 상황이 얼마든지 존재할 수 있다.
따라서 데이터 정합성을 위해서 Select 절에 나열된 컬럼의 조합이 Unique 한지 반드시 체크해야 한다.
MINUS와 NOT EXISTS를 사용한 경우에 총 추출 건수가 달라지는 상황 테스트.
SELECT *
FROM (SELECT 1 no
FROM dual
UNION ALL
SELECT 2
FROM dual
UNION ALL
SELECT 2
FROM dual)
MINUS
SELECT *
FROM (SELECT 1 no
FROM dual);
MINUS 연산은 중복 값이 제거되므로 2가 2건 추출되지 않고 1건만 추출.
MINUS를 NOT EXISTS로 변경하여 처리.
SELECT x.*
FROM (SELECT 1 no
FROM DUAL
UNION ALL
SELECT 2
FROM DUAL
UNION ALL
SELECT 2
FROM DUAL) x
WHERE NOT EXISTS (SELECT 'x'
FROM (SELECT 1 no
FROM dual) y
WHERE x.no=y.no);
동일한 데이터가 2건이 추출되어 결과가 달라지게 된다. 따라서 결과 값을 동일하게 하려면 DISTINCT를 부여해야 한다.
SELECT distinct x.*
FROM (SELECT 1 no
FROM DUAL
UNION ALL
SELECT 2
FROM DUAL
UNION ALL
SELECT 2
FROM DUAL) x
WHERE NOT EXISTS (SELECT 'x'
FROM (SELECT 1 no
FROM dual) y
WHERE x.no=y.no);
MINUS로 작성된 SQL을 NOT EXISTS로 작성할 경우，항상 DIST1NCT를 사용하는 것은 성능상 유리하지 않은 이유를 알아 보자.
SQL에 DISTINCT를 사용하면，oracle은 중복된 값을 제거하기 위해 내부적으로 정렬 작업을 수행하게 된다.
그런데 SQL의 추출 건수가 적은 경우에는 정렬 작업에 대한 부담이 적겠지만, 많은 경우라면 정렬 작업에 대한 부하도 커진다.
그러므로 항상 DISTINCT를 사용하는 것은 성능상 유리하지 않다.
그러므로 MINUS 대신 NOT EXISTS를 사용하여 SQL을 작성할 경우 Select절에 나열된 컬럼 조합이 Unique한지 먼저 판단해야 한다.
그리고 반드시 Unique 하지 않은 경우에만 DISTINCT를 사용.
확인자 역할을 수행하는 테이블이란 From절에 나열된 테이블 중 Select 절에 추출하는 컬럼은 없고,
단순히 Where 절에 조인 조건이나 Filter 조건으로만 사용되는 테이블을 말한다.
단순히 확인자로서의 역할만 수행하는 테이블에 대해서 조인으로 처리할 때，종종 예상치 않은 비효율이 발생할 수 있다.
확인자 역할의 테이블을 조인으로 수행하도록 처리할 때 발생하는 성능 문제와 그 해결방법에 대해 알아 보자.
var b1 varchar2(10)
var b2 number
var b3 number
exec :b1 := 'A'
exec :b2 := 200000
exec :b3 := 300000
SELECT /*+ LEADING(T2 T1 T3) USE_NL(T2 T1 T3) */
DISTINCT t2.c1, t2.c2, t3.c3
FROM SUBQUERY_T2 T2,
SUBQUERY_T1 T1,
SUBQUERY_T1 T3
WHERE t2.c2 :b1
AND t2.c1 >= :b2
AND t2.c1 <= :b3
AND t2.c1 = t1.c4
AND t2.c2 = t1.c5
AND t1.c4 = t3.c1
AND t1.c5 = t3.c2
SQL 및수행결과정리
? 조인순서 : T2 → T1 →T3(조인은 고정，T2와 T3의 조인 연결 조건없음)
? 데이터 추출: t2.c1, t2.c2, t3.c3
? 조건 절: WHERE t2.c2 = :b1 AND t2.c1)=:b2 AND t2.c1(=:b3 AND t2.c1=t1.c4 AND t2.c2=t1.c5 AND t1.c4 = t3.c1 AND t1.c5 = t3.c2)
KeyPoint
T2.C1컬럼은 Unique 하지만,T1.C4컬럼은 Unique 하지 않다. 그리고 테이블 T3의 c1컬럼과 C3 컬럼 값이 각각 Unique 하다.
SQL 트레이스 결과
T2 테이블을 먼저 읽어 3,846건 추출
그리고 T1테이블과 1:M 조인을 수행하여 데이터가 123,072 건으로 크게 증가
추출된 데이터 123,072 건은 다시 T3 테이블과 1:1 조인을 수행하여 총 123,072 건 추출
최종 추출된 123,072 건을 DISTINCT 처리하여 중복 값을 제거한 후 1,923 건으로 크게 감소
즉，T1 테이블과 조인 처리 시 불필요한 데이터가 증가했다고 판단
따라서 T2 테이블과 T3 테이블을 먼저 조인 한 후에 마지막으로 T1 테이블과 조인하도록
조인 순서를 바꾸어 준다면 성능이 개선 될 것으로 예상된다. 하지만 T2 와T3 테이블 간의
직접적인 조인 조건이 없어 조인 순서를 변경할 수 없기 때문에 이 방법은 사용할 수 없다.
조인 순서를 변경할 수 없으므로，더 이상 성능 개선방법이 없어 보인다.
하지만 개선방법은 있다. 만약 처음부터 중복 값을 주출하지 않는다면，조인 연결 시도횟수가
줄어 들어 비효율이 개선될 수 있다.
우선 T2와 T1 테이블을 조인할 때 데이터가 급격히 증가한 것으로 보아 T1과의 조인 시에
중복 데이터가 많이 발생했을 것으로 예상. 그리고 실제로도 데이터를 분석해 보면，T1과의
조인 시에 중복된 데이터가 많이 발생하는 것을 확인할 수 있다. 다행히 T1 테이블의
경우 데이터는 추출하지 않고 조인 연결만 관여하는 확인자 역할을 하는 테이블이다.
값을 추줄하지 않고 단순히 데이터 존재 유무를 확인하는 목적으로 사용된 테이블이므로
EXISTS를 사용하는 서브쿼리로 대체해도 데이터 정합성을 훼손하지 않았다. 그래서 아래와
같이 EXISTS를 사용한 서브쿼리로 SQL을 변경하여 수행하면 된다.
Note. 단，SUM이나 COUNT 등의 그룹 함수를 수행하는 경우는 데이터가 훼손될 수 있으므로
데이터 정합성 체크는 필히 수행
SELECT /*+ LEADING(T2 Tl@SUB T3) USE_NL(T2 Tl@SUB T3) */
t2.c1, t2.c2, t3.c3
FROM SUBQUERY_T2 T2,
SUBQUERY_T3 T3
WHERE t2.c2=:b1
AND t2.c1 >= :b2
AND t2.c1 <= :b3
AND EXISTS(SELECT /*+ QB_NAME(SUB) */ 'x'
FROM SUBQUERY_T1 T1
WHERE t1.c4=t2.c1
AND t1.c5=t2.c2
AND t1.c4=t3.c1
AND t1.c5=t3.c2);
트레이스 결과
T2 테이블에서 추출된 3,846 건에 대해 T1과의 조인 부분을 EXISTS로 변경한 결과
추줄된 데이터가 1,923 건으로 크게 줄었다. 따라서 T3 테이블의 조인 연결도
123,072 번에서 1,923 번으로 대폭 줄어들어 성능이 많이 개선.
지금까지 확인자 역할을 수행하는 테이블이 조인으로 처리되어 발생하는 성능 문제의 원인과
개선방법에 대해서 알아 보았다. 이와 유사한 형태의 SQL을 작성할 때 SQL의 성격을
면밀히 파악하는 것은 물론이고 서브쿼리의 특성을 잘 활용하여 가장 효율적인 방법으로
SQL을 작성해야 할 것이다.
WHERE절의 서브쿼리를조인으로 변경하자
서브쿼리를 조인으로 변경하여 성능 문제를 개선하는 방법
서브쿼리가 한 개만 포함된 SQL 이라면，Optimizer는 하나의 서브쿼리에 대한 Cost를 계산하면 되므로，
통계정보만 정확하다면 비교적 효율적인 실행계획을 수립할 수 있게 된다.
하지만 Where 절에 서브쿼리가 많다면，Optimizer가 SQL에 대한 최적의 실행계획을 수립하는 것은 힘들다.
왜냐하면，서브쿼리들이 가질 수 있는 모든 조합에 대한 Cost를 계산해야 하므로 Optimizer가 실행계획을 최적화하는 과정이 더 부하가 될 수 있어 과감하게
정확도를 포기하고 모든 서브쿼리를 Unnest 수행한 것과 모두 수행하지 않은 것 두 가지의 Cost만 계산하기 때문이다.
따라서 SQL에 서브쿼리가 많다면，효율적인 실행계획을 수립할 확률이 급격히 떨어질 수 밖에 없다.
이것이 앞에서 언급했던 서브쿼리를 남용하지 말아야 하는 이유이다.
ALTER SYSTEM SET "_optim_peek_user_binds"=FALSE
SELECT t1.*
FROM SUBQUERY_T1 t1
WHERE EXISTS(SELECT 'x'
FROM SUBQUERY_T2 t2
WHERE t2.c2 like :b1
AND t2.c3 >= :b2
AND t2.c3 <= :b3
AND t2.c1=t1.c4
AND t2.c2=t1.c5
AND EXISTS(SELECT 'x'
FROM SUBQUERY_T3 t3
WHERE t3.c2 like :b4
AND t3.c3 >= :b5
AND t3.c3 <= :b6
AND t3.c1=t1.c4
AND EXISTS(SELECT 'x'
FROM SUBQUERY_T1 t1
WHERE t1.c5 like :b7
AND t1.c4 >= :b8
AND t1.c4 <= :b9
AND t1.c4=t1.c4)));
*Bind Variable Peaking란(이하 BVP) Bind 변수를 가진 SQL에 대해서 최초 파싱할 때，실제 Bind 값을 먼저 확인하고,
히스토그램을 참조하여 최적화하는 것을 말한다. 최초 하드파싱하는 SQL에 대해서는 가장 최적의 실행계획을 수립할 수
있다는 장점이 있다. 하지만 SQL이 최초 파싱 될 시점 배치프로그램이 수행되었고, Optimizer가 히스토그램을 참조해
Full Table Scan을 하는 실행계획을 수립했다면，이후 온라인에서 수행되는 SQL이 기간에 대한 Bind값의 기간이 짧아
Index Scan이 성능상 유리함에도 불구하고，기존에 수립한 실행계획에 대해 소프트 파싱으로 Full Table Scan을
하는 실행계획을 그대로 사용하게 되어 성능문제가 발생할수 있는 것이다. 이와 같은상황은 확률상 낮은 편이지만，
발생할경우 성능에 미치는 영향이 매우 심각하기 때문에 보통 BVP 기능은 False로 운영하는게 일반적이다.
BVP는 히든 파라미터인 _OPTIM_PEEK_USER_BINDS로 변경 가능하다.
앞의 SQL을 보면 서브쿼리가 총 3개 존재하고 각 서브쿼리 내의 Where 절 조건은 Bind 변수로 처리되어 있다.
위 SQL은 서브쿼리 외에는 별도의 상수 조건이 존재하지 않기 때문에 서브쿼리를 먼저 수행한 후 Main SQL이
비교 할 값을 전달받아 수행해야 SQL 성능이 양호 할 것이다. 우선 첫 번째 서브쿼리가 (SUBQUERY_T2) 먼저
수행할 때 성능이 유리하도록 Bind 변수 값을 설정한 후 트레이스 결과를 확인하자.
var b1 varchar2(10)
var b2 number
var b3 number
var b4 varchar2(10)
var b5 number
var b6 number
var b7 varchar2(10)
var b8 number
var b9 number
exec :b1 : = 'A'
exec :b2 : = 200000
exec :b3 : = 200100
exec :b4 : = '%'
exec :b5 : = 100000
exec :b6 : = 300000
exec :b7 : = '%'
exec :b8 : = 100000
exec :b9 : = 300000
SQL 작성자는 Bind 변수에 어떤 값을 사용할지 미리 알고 있기 때문에，SUBQUERY_T2를
먼저 수행해야 가장 효율적인 처리가 된다는 것을 알고 있다. 그러나 Optimizer는 Bind 변수에
어떤 값이 입력되는지 모른 체 실행계획을 수립해야 한다. 그래서 Optimizer는 앞의
트레이스 결과와 같이 SUBQUERY_T2가 아닌 SUBQUERY_T3 테이블을 먼저 읽고 수행하는 것이
효율적이라고 잘못 판단하여 비효율이 발생하게 되었다. 따라서 Optimizer가 잘못 수립한
실행계획을 SQL 작성자가 의도했던 대로，SUBQUERY_T2를 먼저 수행하도록 유도해야 한다.
이런 경우 성능 개선방법으로 서브쿼리를 조인으로 변경하는 것이다. 우선 서브쿼리로 작성된
부분을 인라인 뷰로 바꾸어 SQL을 작성하고 추출 데이터가 중복 값을 가진 경우에는 DISTINCT를
부여해 중복을 제거해 준다. 마지막으로 인라인 뷰로 바뀐 서브쿼리를 조인 순서를 조절하는
LEADING 힌트를 사용하여 실행계획을 제어하면 의도한 대로 SQL을 수행할수 있다.
아래는 SUBQUERY_T2를 먼저 수행하도록 SQL을 재작성 한 것이다.
SELECT /*+ LEADING(T2 T1) USE_NL(T2 Tl) */t1.*
FROM SUBQUERY_T1 t1,
(SELECT DISTINCT t2.c1,
t2.c2
FROM SUBQUERY_T2 t2
WHERE t2.c2 like :b1
AND t2.c3 >= :b2
AND t2.c3 <= :b3) t2
WHERE t2.c1=t1.c4
AND t2.c2=t1.c5
AND EXISTS(SELECT 'x'
FROM SUBQUERY_T3 t3
WHERE t3.c2 like :b4
AND t3.c3 >= :b5
AND t3.c3 <= :b6
AND t3.c1=t1.c4)
AND EXISTS(SELECT 'x'
FROM SUBQUERY_T1 t1
WHERE t1.c5 like :b7
AND t1.c4 >=:b8
AND t1.c4 <=:b9
AND t1.c4=t1.c4)
SQL을 재 작성하고 힌트를 부여하여，의도했던 대로 SQL이 수행되었다.
기존에 90.32초 소요되던 SQL이 0.08초 만에 수행되는 만족스러운 결과를 얻을 수 있었다.
SQL 작성 시 Where절에 많은 서브쿼리가 포함될 경우，성능이 좋지 않은 실행계획을 수립 할 확률이 매우 높다.
이런 경우 힌트를 사용하여 실행계획을 제어하기 어렵기 때문에, 서브쿼리를 조인으로 변경하는 것을 검토해야 한다.