<오라클 성능 고도화 원리와 해법2> Ch04-01 쿼리 변환이란?

Posted May 6, 2024

By Mijong Go 9 min read

오라클 성능 고도화 원리와 해법2 - Ch04 쿼리 변환

오라클 성능 고도화 원리와 해법2 - Ch04-01 쿼리 변환이란?

  
SELECT DISTINCT S.SNAME
FROM S, SP
WHERE S.S# = SP.S#
AND SP.P# = 'P2'

C.J.Date 박사는 그의 유명한 저서 ‘An Introduction to Database Systems’에서 위와 같은 간단한 SQL문 하나를 가지고도 52개의 다른 SQL문으로 표현이 가능하다는 것을 보여주었다. 아래는 그중 일부를 발췌한 것이다.

  
SELECT DISTINCT S.SNAME FROM S
WHERE S.S# NOT IN (SELECT SP.S# FROM SP WHERE SP.P# = 'P2');

SELECT DISTINCT S.SNAME FROM S WHERE EXISTS
    (SELECT * FROM SP WHERE SP.S# = S.S# AND SP.P# = 'P2');

SELECT DISTINCT S.SNAME FROM S, SP
WHERE S.S# = SP.S#
GROUP BY S.SNAME, SP.P#
HAVING SP.P# = 'P2';

SELECT DISTINCT S.SNAME FROM S
WHERE S.S# = ANY (SELECT SP.S# FROM SP WHERE SP.P# = 'P2');

SELECT DISTINCT S.SNAME FROM S
WHERE (SELECT COUNT (*) FROM SP WHERE SP.S# = S.S# AND SP.P# = 'P2') > 0;

SELECT DISTINCT S.SNAME FROM S, SP WHERE SP.P# = 'P2'
GROUP BY S.S#, S.SNAME, SP.S#
HAVING SP.S# = S.S#;

......

그런데 결과가 동일하더라도 SQL 문을 어떤 형태로 작성하느냐에 따라 성능에 차이가 생길 수 있다. 예를 들어, 아래 두 쿼리도 결과는 같지만 수행 속도는 다를 수 있다. 단, 본 장에서 설명하는 쿼리 변환기가 작동하지 않는다면 말이다.

  
< 쿼리 1 >
select *
from dept d
where not exists
    (select 'x'
     from emp
     where deptno = d.deptno);

< 쿼리 2 >
select d.*
from dept a, emp e
where e.deptno (+) = d.deptno
and e.rowid is null;

애초에 가장 좋은 성능을 낼 수 있는 형태로 쿼리를 작성하면 좋겠지만 쿼리 최적화 원리를 그 만큼 잘 아는 개발자는 흔치 않다. 그래서 최근의 비용 기반 옵티마이저는 사용자 SQL을 최적화에 유리한 형태로 재작성하는 작업을 먼저 한다. 비용 기반 옵티마이저의 서브 엔진으로서 Query Transformer, Estimator, Plan Generator가 있다고 설명했는데, 이 중 Query Transformer가 그런 역할을 담당한다(그림 4-1).

쿼리 변환(Query Transformation)은, 쿼리 옵티마이저가 SQL을 분석해 의미적으로 동일(~같은 결과를 리턴)하면서도 더 나은 성능이 기대되는 형태로 재작성하는 것을 말한다. 본격적으로 최적화를 하기에 앞서 사전 정지(t) 작업을 하는 것이라고 말할 수 있다. 그런 의미에서 쿼리 변환을 ‘논리적 최적화 (Logical Optimization)’, 그 이후 단계를 물리적 최적화(Physical Optimization)라고 부르기도 한다.

DBMS 버전이 올라갈수록 쿼리 변환의 종류가 다양해지고, 더 적극적인 시도가 이루어지고 있다. 예를 들어, 오라클 8에서는 복합뷰를 사용자 힌트에 의해서만 소극적으로 수행하다가 9.1에 와서는 결과가 보장될 수 있는 형태라면 무조건 변환을 시도한다. 보다 적극적으로 쿼리 변환을 시행하는 것이다. 10g부터는 좀 더 완벽한 CBO 모드로 동작하기 시작했다. 즉, 복합뷰를 Merging한 결과가 더 나은 성능을 낼 것으로 판단될 때만 그것을 사용한다. 서브쿼리 Unnesting도 같은 진화과정을 거쳐왔다.

단순하고 기초적인 형태의 쿼리 변환만이 이루어지던 8 버전에서는 쿼리 변환을 그다지 중요한 개념으로 다루지 않았다. 하지만 9부터 옵티마이저의 핵심적인 변화가 대부분 쿼리 변환을 중심으로 진행되고 있어 이제 SQL 튜닝을 논할 때 빼놓을 수 없는 중심 주제가 되었다. 그리고 이에 대한 정확한 이해를 통해서만 세밀한 실행 계획 제어가 가능하기 때문에 중요하다.

쿼리 변환의 종류로는 아래와 같은 것들이 있다.

서브쿼리 Unnesting
뷰 Merging
조건절 Pushing
조건절 이행
공통 표현식 제거
Outer 조인을 Inner 조인으로 변환
실체화 뷰 쿼리로 재작성
Star 변환
Outer 조인 뷰에 대한 조인 조건 Pushdown
OR-expansion

위 분류는 2002년 2월에 발표된 오라클 백서 ‘Query Optimization in Oracle’와 2005년 6월에 발표된 ‘Query Optimization in Oracle Database 10g Release 2’를 참고한 것이다.

쿼리 변환은 크게 다음 두 가지 방식으로 작동한다.

휴리스틱(Heuristic) 쿼리 변환: 결과만 보장된다면 무조건 쿼리 변환을 수행한다. 일종의 규칙 기반 최적화 기법이라고 할 수 있으며, 경험적으로(최소한 동일하거나) 항상 더 나은 성능을 보일 것이라는 옵티마이저 개발팀의 판단이 반영된 것이다.
비용기반(Cost-based) 쿼리 변환: 변환된 쿼리의 비용이 더 낮을

때만 그것을 사용하고, 그렇지 않을 때는 원본 쿼리 그대로 두고 최적화를 수행한다.

오라클 9i 기준으로 볼 때, 앞서 열거한 10가지 쿼리 변환 중 1~6까지는 휴리스틱 쿼리 변환에 해당하고, 7~10은 비용기반 쿼리 변환에 해당한다. 10g부터는 서브쿼리 Unnesting과 뷰 Merging이 비용 기반 쿼리 변환으로 전환되었다. 조건절(Predicate) Pushing 중 조인 조건(Join Predicate) Pushdown도 비용 기반 쿼리 변환으로 전환되었다. 나머지는 변환된 쿼리가 항상 더 나은 성능을 제공하므로 비용 기반으로의 전환이 불필요하다. 결론적으로 말해, 필요한 부분에 대해 선이미 비용 기반으로 모두 개선이 이루어진 셈이다.

버전이 올라가면서 이외에도 조인을 제거(Join Elimination)하고 집계 서브쿼리를 분석 함수로 변환하고(Aggregate Subquery Elimination) 집합 연산을 조인으로 변환(Set Join Conversion)하는 등의 새로운 쿼리 변환 기능이 추가되었고, 이름이 알려지지 않은 것들도 여럿 발견되고 있다. 또한 11g에서 주목할 만한 신 기능이 많이 추가됐음을 발견할 수 있는데, 이를 모두 연구하고 정리하기까지 많은 시간이 필요하므로 본서에서는 다루지 않기로 하겠다.

Database, SQL Tuning

SQLP

This post is licensed under CC BY 4.0 by the author.

오라클 성능 고도화 원리와 해법2 - Ch04 쿼리 변환

오라클 성능 고도화 원리와 해법2 - Ch04-01 쿼리 변환이란?

Trending Tags