Oracle - 실행 계획으로 복잡한 쿼리 최적화해보기

배경

백오피스를 운영하면서 항상 거슬렸던 부분이 있었다. 바로 느리게 로딩되는 주문 관리 페이지다. 원인은 굉장히 오래 걸리는 주문 목록 조회 쿼리였다. 

주문 관리 페이지는 고객의 주문 정보, 배송 현황 등 다양하고 복잡한 데이터를 실시간으로 처리하고 표시해야 하는 중요한 화면이다. MD들은 업무 특성상 하루에도 수차례 이 페이지에 접속해야 하는데, 긴 로딩 시간을 감내해야 하는 상황이었다. 이는 단순히 개인의 불편함을 넘어서 전체적인 업무 효율성 저하로 이어질 수 있었다.(라고 생각한다.)

그래서 팀에 건의하여 쿼리를 최적화해보기로 했다.

 

개발 환경

• Oracle11g
• DBeaver (Community Edition 24.1)

 

쿼리 구조

쿼리 최적화를 할 때 가장 중요한 건 최적화하려는 쿼리의 역할과 기능을 아는 것이라고 생각한다. 쿼리 최적화 작업을 시작하기 전에 해당 쿼리의 목적, 처리하는 데이터, 비즈니스 로직에서의 역할 등을 충분히 파악하는 것이 중요하다. 이러한 이해를 바탕으로 불필요하거나 중복된 연사을 식별하고 제거할 수 있다. 유의미한 최적화를 위해 쿼리를 먼저 분석했다.

 

약 500줄의 쿼리이기 때문에 인내심을 가지고 확인해야했다. 많은 테이블이 Join되고 여러 Depth의 서브쿼리가 작성되어 있었다.

의사코드로 작성하면 아래와 같다.

WITH [서브쿼리1] AS (
    SELECT [컬럼들]
    FROM
    (
        SELECT [컬럼들]
        FROM [주문 테이블]
        INNER JOIN
        (
            SELECT [컬럼들]
            FROM
            (
                SELECT [컬럼들]
                FROM [주문 테이블]
                INNER JOIN[주문상품 테이블] ON [조건]
                WHERE [조건]
            )
            GROUP BY [그룹 기준]
        )
        ON [조건]
        INNER JOIN
        (
            SELECT [컬럼들]
            FROM [주문상품 테이블]
            WHERE [조건]
            GROUP BY [그룹 기준]
        )
        ON [조건]
        WHERE [조건]
        GROUP BY [그룹 기준]
    ) ORDER BY [정렬 기준]
),
WITH [서브쿼리2] AS (
	SELECT [컬럼들]
    FROM [상품카테고리 테이블]
)
SELECT [컬럼들]
FROM
(
	SELECT [로우넘버, 컬럼들]
    FROM
    (
    	SELECT [컬럼들]
        FROM [전시상품 테이블]
        INNER JOIN [상품 테이블] ON [조건]
        INNER JOIN [브랜드 테이블] ON [조건]
        INNER JOIN [상품 테이블] ON [조건]
        INNER JOIN [상품옵션 테이블] ON [조건]
        INNER JOIN [공통 테이블] ON [조건]        
        INNER JOIN [채널 테이블] ON [조건]
        LEFT JOIN [서브쿼리1] ON [조건]        
        INNER JOIN [서브쿼리2] ON [조건]
        LEFT JOIN [상품출시일 테이블] ON [조건] 
        WHERE [조건]
        ORDER BY [정렬 조건]
    )
)
WHERE [조건]
AND [페이징 조건]

 

실행 계획

우선 실행 계획을 분석해야 했다. 실행 계획은 DBMS가 쿼리를 어떻게 실행할지에 대한 정보를 제공한다.

실행 계획을 분석하면 조인 방식, 인덱스 사용 여부, 테이블 스캔 등 쿼리 실행에 영향을 미치는 요소를 확인할 수 있다. 이 정보를 통해 성능 저하의 원인을 파악하고 최적화할 수 있는 방법을 찾을 수 있다.

 

실행 계획을 분석하기 전에 알아둬야 할 것들이 있다. SELECT 쿼리의 실행 순서와 실행계획을 읽는 방법을 알아야 한다. 간단하게만 작성해두겠다.

SELECT 쿼리 실행 순서

SQL Clause	실행 순서	작성 순서
WITH (Common Table Expression)	1 (WITH 절이 있고 사용될 때)	1
FROM	2	4
WHERE	3	5
GROUP BY	4	6
HAVING	5	7
SELECT	6	2
DISTINCT	7	3
UNION / UNION ALL	8	8
ORDER BY	9	9
LIMIT / OFFSET	10	10

 

실행계획 읽는 방법

항목	설명
실행 순서	- 가장 안쪽에서 바깥쪽으로 - 같은 레벨에서 위에서 아래로
Operation	수행되는 작업 유형 (TABLE ACCESS FULL, INDEX SCAN)
Object	작업이 수행되는 테이블이나 인덱스 이름
Cost	작업 비용. Cost가 높을수록 리소스 많이 사용됨
Cardinality	각 단계에서 처리될 것으로 예상되는 Row의 수
Bytes	처리될 데이터의 예상 크기(Byte 단위)

 

시작

오라클에서는 DBMS_XPLAN이라는 기능을 통해 실행 계획을 확인할 수 있지만 DB 관리 툴인 DBeaver에서 제공하는 실행 계획 기능을 사용하기로 했다. DBeaver에서 제공하는 GUI를 통해 좀 더 편하게 실행 계획을 분석할 수 있고 분석된 각 행을 클릭하면 해당 단계의 세부 정보를 확인할 수 있다. 사용 방법은 간단하다. 실행 계획을 보고싶은 쿼리에서 Ctrl+Shift+E 키를 입력하거나 우클릭 > 실행 > 실행 계획 보기 클릭하면 된다. 

실행 계획을 보면 특정 항목에서 높은 Cost가 발생하는걸 확인할 수 있다. Depth를 열어 끝까지 확인해보자

가장 깊은 Depth에서부터 높은 Cost와 Cardinality가 발생하고 있었다. 가장 깊은 Depth는 가장 먼저 실행되는 쿼리를 나타낸다. 해당 쿼리는 WITH절을 사용하고 있었기 때문에 쿼리 실행 순서상 WITH절에서부터 높은 Cost가 발생한다고 유추해 볼 수 있다. 하지만 복잡한 쿼리에서 해당 부분이 정확히 어떤 쿼리에서 발생하는지 알기는 어렵다. 이럴 때 나는 각 실행 단계에 대한 상세 정보의 Predicate와 Projection을 통해 쿼리를 추적한다. 

• Predicate: Access Predicates와 Filter Predicates를 통해 WHERE 절의 조건이 어떻게 적용되는지 나타낸다.
• Projection: 각 단계에서 어떤 컬럼이 전달되는지 보여준다. 쿼리의 SELECT 절과 직접적으로 연관되어 있어, 쿼리의 어느 부분이 실행되고 있는지 파악하는 데 도움이 된다.

특정 단계를 클릭하면 우측에 상세 정보가 출력된다.

상세 정보를 확인해본 결과, 쿼리의 느린 성능의 주범은 첫 번째 WITH절의 서브쿼리였다. 이제 복잡한 쿼리 전체를 살펴 볼 필요없이 원인이되는 쿼리만 분리해서 개선하면 된다.

 

최적화 진행

WITH [서브쿼리1] AS (
    SELECT [컬럼들]
    FROM
    (
        SELECT [컬럼들]
        FROM [주문 테이블]
        INNER JOIN
        (
            SELECT [컬럼들]
            FROM
            (
                SELECT [컬럼들]
                FROM [주문 테이블]
                INNER JOIN[주문상품 테이블] ON [조건]
                WHERE [조건]
            )
            GROUP BY [그룹 기준]
        )
        ON [조건]
        INNER JOIN
        (
            SELECT [컬럼들]
            FROM [주문상품 테이블]
            WHERE [조건]
            GROUP BY [그룹 기준]
        )
        ON [조건]
        WHERE [조건]
        GROUP BY [그룹 기준]
    ) ORDER BY [정렬 기준]
)

우선 최적화 과정을 확인하기 위해 서브쿼리만 분리해서 실행 계획을 진행했다.

이제 단계적으로 최적화를 진행했다. 테이블 정보를 자세히 다룰 수 없기 때문에 최적화한 내용은 간단하게 작성한다.

• 불필요한 Join 제거: 두 개의 서브쿼리를 통해 두 차례 Join을 수행하던 쿼리를 단일 서브쿼리로 통합해서 한 번의 Join으로 수정했다.
• ORDER BY를 위한 서브쿼리 제거: GROUP BY 결과의 정렬을 위해 불필요하게 사용된 서브쿼리를 제거했다. SELECT 절에서 사용된 집계함수를 ORDER BY에 직접 사용할 수 있다는 점을 모르고 분리한거라고 생각된다.
• WHERE절 추가: 이 부분이 가장 중요한 개선점이었다. 전체 쿼리는 특정 기간 내의 주문만을 조회하는데, 서브쿼리에서는 기간 조건 없이 조회하면서 풀 테이블 스캔이 발생했다. 서브쿼리에도 기간 조건을 추가해줬다.

최종적으로 수정된 쿼리는 아래와 같다. 실행 계획을 보면 Cost와 Cardinality가 월등히 낮아진걸 확인할 수 있다.

WITH [서브쿼리1] AS (
    SELECT [컬럼들]
    FROM
    INNER JOIN
    (
        SELECT DISTINCT [컬럼들]
        FROM [주문상품 테이블]
        WHERE [조건]
    )
    ON [조건]
    WHERE [조건]
    GROUP BY [그룹 기준]
    ORDER BY [정렬 기준]
)

 

 

최종 쿼리

WITH [서브쿼리1] AS (
    SELECT [컬럼들]
    FROM
    INNER JOIN
    (
        SELECT DISTINCT [컬럼들]
        FROM [주문상품 테이블]
        WHERE [조건]
    )
    ON [조건]
    WHERE [조건]
    GROUP BY [그룹 기준]
    ORDER BY [정렬 기준]
),
WITH [서브쿼리2] AS (
	SELECT [컬럼들]
    FROM [상품카테고리 테이블]
)
SELECT [컬럼들]
FROM
(
	SELECT [로우넘버, 컬럼들]
    FROM
    (
    	SELECT [컬럼들]
        FROM [전시상품 테이블]
        INNER JOIN [상품 테이블] ON [조건]
        INNER JOIN [브랜드 테이블] ON [조건]
        INNER JOIN [상품 테이블] ON [조건]
        INNER JOIN [상품옵션 테이블] ON [조건]
        INNER JOIN [공통 테이블] ON [조건]        
        INNER JOIN [채널 테이블] ON [조건]
        LEFT JOIN [서브쿼리1] ON [조건]        
        INNER JOIN [서브쿼리2] ON [조건]
        LEFT JOIN [상품출시일 테이블] ON [조건] 
        WHERE [조건]
        ORDER BY [정렬 조건]
    )
)
WHERE [조건]
AND [페이징 조건]

[상단 메뉴 > 윈도우 > Show View > 쿼리 관리자]를 통해서 실행한 쿼리의 속도를 확인할 수 있다. 캐시까지 고려해서 20번씩 실행한 후 평균값을 구했다.

최종적으로 유의미한 쿼리 최적화가 이루어졌다. (5.09s -> 0.35s)

참조

https://dbeaver.com/docs/dbeaver/Query-Execution-Plan/

DBeaver Documentation

https://sqlbolt.com/lesson/select_queries_order_of_execution

SQLBolt - Learn SQL - SQL Lesson 12: Order of execution of a Query

https://docs.oracle.com/en/database/oracle/oracle-database/23/tgsql/optimizer-ref.html#GUID-CD61854E-639B-4F06-8CD2-C1980D6975DE

SQL Tuning Guide