쿼리변환 특강 수강기록

Oracle

쿼리변환 특강 수강기록

함탕 2025. 1. 11. 18:23

728x90

쿼리변환 특강 수강이유

작년에 내가 운영하고 있던 서비스의 인프라가 톰캣에서 JBOSS로

그리고 티베로에서 큐브리드로 바뀐 적이 있었다.

DB마이그레이션을 하고 보니 티베로에서는 문제없던

쿼리들이 갑자기 느려지는 현상들을 발견했다.

인덱스도 제대로 옮겼고 통계정보도 최신화 시켰는데도

조회쿼리의 속도가 너무 늦었다.

내가 알고 있는 모든 DB지식을 모아서 쿼리튜닝을 시작했고

성능을 40% 이상 올려놓긴 했는데 그 이상은 무리였다.

사실 trace만 볼 수 있었다면 쿼리 조회속도를 좀 더 올릴 수도

있었을 텐데 문제가 되는 쿼리는 몇 시간이 지나도 끝나질 않았고

이로 인해 제대로 된 원인파악도 하지 못하고

나의 상상력과 분석력만으로 쿼리 튜닝을 했었다.

(어찌 보면 상상력과 분석력만으로 성능을

40%까지 올린 것도 대단했다고 생각한다. ㅎ)

DBA 같은 개발자가 되기 위해 나름 노력해 왔다고 생각했지만

나는 한참 부족한 사람이라는 것을 다시 한번 느끼게 되었다.

그러던 중 작년 친절한 SQL 튜닝 강의를 들었었던

디비안으로 부터 메일이 도착했다.

디비안에서 교육을 수강한 사람을 대상으로

쿼리변환 및 고급조인 테크닉에 대해 특강을 한다는 내용이었다.

이번 특강을 통해 조금이라도 더 DB 및 쿼리튜닝의 지식을

늘리고 싶다는 열망이 가득했기에 비용을 지불하고

특강 수강신청을 했다.

수강기록

이번 특강의 주된 내용은 쿼리변환에 대한 내용이었다.

DB는 내가 내가 작성한 쿼리를 그대로 실행하지 않는다.

옵티마이저라는 녀석이 판단을 하기에 내가 작성한 쿼리보다

더 좋은 방법이 있다면 실행계획을 바꿔서 작업을 한다.

이때 나오는 개념이 쿼리변환이다.

말 그대로 옵티마이저가 실행계획을 바꾸면서

쿼리에 변환이 생기는 것이다.

이번 특강을 통해 얻은 지식 중을 몇 가지 정리하고자 한다.

1. Subquery Unnesting

/*원본 SQL */
select * from emp
where deptno in (select deptno from dept)

위 쿼리처럼 중첩된 서브쿼리 간에는 부모와 자식이라는 종속적이고 계층적인 관계가

생기기도 하고 이러한 복잡한 상황에서는

최적화가 덜된 쿼리로 인해 간혹 조회성능이 느려지기도 하는데

이를 해결하기 위한 방법 중 하나로

Subquery Unnesting 쿼리변환이 일어나기도 한다.

/*Unnesting된 쿼리*/
SELECT e.*
  FROM DEPT d, EMP e
 WHERE e.DEPTNO = d.DEPTNO;

Subquery Unnesting이란 메인과 서브쿼리 간의 계층구조를

서로 같은 레벨로 만들어주는 것이다.

즉, 부모와 자식같이 계층적이고 복잡한 구조가 아닌

flat 한 구조로 바꾸어서 최적화 된 쿼리가 되도록 하는 것이다.

(경우에 따라 flat한 구조가 아닐 때가 더 좋을 수도 있다.)

2. View Merging

from절을 풀어낸다라고 생각하면 되는데 아래 예시를 보자.

/*SQL원본*/
SELECT * 
  FROM (SELECT * FROM EMP WHERE JOB = 'SALES') A
      ,(SELECT * FROM DEPT WHERE LOC = 'SEOUL') B
 WHERE A.DEPTNO = B.DEPNO;

FROM 절에 인라인뷰(서브쿼리)가 있다.

사람은 위 쿼리를 좀 더 직관적이라고 생각하기도 하는데

옵티마이저 입장에서는 위 쿼리가 복잡하다고 생각한다.

그래서 옵티마이저 본인이 쉽게 판단하기 위해 FROM 절의

쿼리를 풀어낸다.

/*오라클 내부에서 View Merging으로 아래 형태로 SQL 변환*/
SELECT *
  FROM EMP A
     , DEPT B
 WHERE A.DEPTNO = B.DEPTNO
   AND A.JOB = 'SALES'
   AND B.LOC = 'SEOUL';

위처럼 옵티마이저가 이해하기 쉽도록

View Merging으로 쿼리 변환이 일어나면

옵티마이저가 더 다양한 액세스 경로를 조사대상으로 삼을 수 있다.

View Merging제어 힌트로는

/*+ MERGE */

/*+ NO_MERGE */

가 있다.

3. Transitive Predicate Generation

학창 시절 수학시간에 아래와 같은 내용을 배운 적이 있을 것이다.

' A가 B와 같고 B가 C와 같으면 A는 C와 같다 '

(A=B, B=C 결론 A=C)

위와 같은 전이법칙(transitivity)이 적용되는 쿼리변환이다.

/*원본 SQL*/
SELECT * FROM DEPT d, DEP e
 WHERE e.DEPTNO = d.DEPTNO
 AND   e.JOB = 'MANAGER'
 AND   e.DEPTNO =10;

위 쿼리의 조건을 보면

e.DEPTNO = d.DEPTNO가 있다.

두 테이블의 DEPTNO가 같다는 조건이고

e.DEPTNO가 10이면

d.DEPNO도 10이라는 걸 유추할 수 있다.

/*변경된 SQL*/
SELECT * FROM DEPT d, DEP e
 WHERE e.DEPTNO = d.DEPTNO
 AND   e.JOB = 'MANAGER'
 AND   e.DEPTNO = 10
 AND   d.DEPTNO = 10;

전이법칙을 통해 위처럼 마지막 조건절에

AND d.DEPTNO = 10이 추가되어 쿼리가 변환된다.

위처럼 자동으로 조건절이 추가됨으로써, 조인되는 데이터 양을

줄일 수 있고 옵티마이저 입장에서는 또 다른 인덱스의 사용을

고려할 수 있게 되어 보다 나은 최적화 가능성이 증대된다.

(DEPT테이블에 DEPTNO로 인덱스가 있었다면 사용이 가능하게 된다.)

*유의: 테이블 조인조건에는 해당되지 않는다*

(e.DEPTNO = d.DEPTNO 이 조인조건에 대해서는 전이법칙이 일어나지 않는다)

Transitive Predicate Generation 관련 힌트로는

/*+ PUSH_PRED */

/*+ NO_PUSH_PRED */

등이 있다.

수강 이후

이번 쿼리변환 특강을 통해 튜닝기법 및

DB에 대해 조금 더 심도 있게 알게 되었다.

1. ANSI조인 때문에 튜너입장에서 제어가 힘들 수 있다는 점

2. 쿼리블록명을 이용한 실행계획 제어

3. Global Hint를 이용한 실행계획 제어

4. 테이블 조인 시 최적의 순서대로 조인조건을 잘 만들어야 한다는 점

5. OLTP환경에서 WITH문은 지양해야 한다는 점

6. 공식에 따라 실행계획을 읽는 것은 정확하지 않다는 점

7. 데이터의 특성과 자료 수를 분석하여 튜닝을 할 수 있다는 점

정말 많은 지식을 얻게 되었다.

오늘 배운 힌트기법들을 숙달하여 바로 사용하려면

오랜 연습과 많은 경험이 필요하기 때문에

지금 당장 업무하는 데 있어 바로 사용하기 힘들 수도 있다.

하지만 쿼리변환의 종류와 원리를 토대로

좀 더 나은 쿼리문을 작성할 수 있고

인덱스 생성도 효율적으로 할 수 있다고 생각한다.

내 귀중한 휴식시간을 할애하고 비용을 들여서

특강을 들었지만 시간이 하나도 아깝지 않았고

새로운 지식을 얻어간다는 행복감이 더 큰

더 주말이 되었다.

728x90