dsgnmstr; Design Monster

[태그:] 쿼리튜닝

  • SQL의 속마음을 엿보다, 쿼리 성능 측정의 두 날개: EXPLAIN PLAN과 Autotrace

    SQL의 속마음을 엿보다, 쿼리 성능 측정의 두 날개: EXPLAIN PLAN과 Autotrace

    우리가 작성한 SQL 쿼리는 데이터베이스 내부에서 어떤 과정을 거쳐 결과를 반환할까요? 똑같은 결과를 만드는 쿼리라도, 데이터베이스가 어떤 길을 선택하느냐에 따라 그 실행 속도는 천차만별일 수 있습니다. 이는 마치 서울에서 부산까지 가는 방법이 KTX, 버스, 비행기 등 다양하고 각각의 소요 시간이 다른 것과 같습니다. 데이터베이스의 ‘옵티마이저(Optimizer)’는 우리가 던진 SQL을 실행하기 위한 가장 효율적인 경로, 즉 ‘실행 계획(Execution Plan)’을 수립하는 똑똑한 네비게이션과 같습니다. 하지만 때로는 이 옵티마이저가 최적이 아닌 경로를 선택하여 쿼리 성능을 저하시키기도 합니다.

    이때 개발자와 데이터베이스 관리자(DBA)에게 필요한 것이 바로 옵티마이저의 속마음을 들여다보는 도구입니다. 오라클(Oracle) 데이터베이스 환경에서 그 역할을 하는 가장 대표적인 두 가지 도구가 바로 ‘EXPLAIN PLAN’과 ‘Autotrace’입니다. 이 두 도구는 우리가 작성한 쿼리가 어떤 실행 계획을 통해 수행될 것인지, 그리고 실제 실행되었을 때 얼마나 많은 시스템 자원을 사용했는지를 분석하여 성능 병목의 원인을 찾아내고 개선할 수 있는 결정적인 단서를 제공합니다. 이 글에서는 쿼리 튜닝의 첫걸음인 EXPLAIN PLAN과 Autotrace의 사용법과 차이점을 명확히 이해하고, 이를 통해 어떻게 SQL 성능을 측정하고 최적화할 수 있는지 알아보겠습니다.

    쿼리 실행의 청사진: EXPLAIN PLAN

    EXPLAIN PLAN은 이름 그대로, 특정 SQL 문에 대한 ‘실행 계획’을 ‘설명(Explain)’해달라고 옵티마이저에게 요청하는 명령어입니다. 이 명령어의 가장 큰 특징은 SQL을 ‘실제로 실행하지 않고’, 옵티마이저가 현재의 통계 정보 등을 바탕으로 ‘어떻게 실행할 것인지’에 대한 계획만을 예측하여 보여준다는 것입니다. 이는 건축가가 건물을 짓기 전에 설계도(청사진)를 먼저 보여주는 것과 같습니다. 실제 공사를 시작하기 전에 설계도를 보며 비효율적인 구조나 문제점을 미리 파악하고 수정할 수 있는 것처럼, EXPLAIN PLAN을 통해 우리는 데이터에 아무런 영향을 주지 않고 안전하게 쿼리의 잠재적인 성능 문제를 진단할 수 있습니다.

    EXPLAIN PLAN 사용법과 결과 해석

    EXPLAIN PLAN은 보통 두 단계로 사용됩니다. 먼저 EXPLAIN PLAN FOR 명령으로 분석하고 싶은 SQL 문의 실행 계획을 생성하여 PLAN_TABLE이라는 특별한 테이블에 저장합니다. 그 다음, DBMS_XPLAN.DISPLAY 함수를 사용하여 PLAN_TABLE에 저장된 실행 계획을 사람이 보기 좋은 형태로 조회합니다.

    [EXPLAIN PLAN 사용 예시]

    SQL

    -- 1단계: 실행 계획 생성
    EXPLAIN PLAN FOR
    SELECT e.ename, d.dname
    FROM emp e, dept d
    WHERE e.deptno = d.deptno
      AND e.sal > 2000;

    -- 2단계: 생성된 실행 계획 조회
    SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

    결과로 출력되는 실행 계획 테이블은 쿼리가 어떤 순서로, 어떤 방식으로 처리되는지를 보여주는 상세한 정보의 집합입니다. 여기서 가장 중요하게 봐야 할 항목은 다음과 같습니다.

    • Operation: 수행되는 작업의 종류를 나타냅니다. TABLE ACCESS FULL(테이블 전체를 스캔), INDEX UNIQUE SCAN(인덱스를 통해 한 건의 데이터를 찾음), HASH JOIN(두 테이블을 조인하는 방식 중 하나) 등 다양한 작업이 있습니다. 이 작업의 종류를 통해 쿼리가 효율적으로 인덱스를 사용하고 있는지, 불필요한 전체 스캔을 하고 있지는 않은지 파악할 수 있습니다.
    • Name: 작업의 대상이 되는 객체(테이블, 인덱스)의 이름입니다.
    • Cost: 옵티마이저가 예측한 해당 작업의 상대적인 비용입니다. 이 비용이 높을수록 성능이 오래 걸릴 가능성이 높다고 해석할 수 있으며, 전체 실행 계획에서 어느 부분이 가장 비용이 많이 드는 병목 구간인지 식별하는 데 도움을 줍니다.
    • Rows (Cardinality): 해당 작업을 통해 반환될 것으로 예측되는 행(Row)의 개수입니다. 옵티마이저는 이 예측치를 기반으로 다음 조인 방식이나 접근 경로를 결정하므로, 통계 정보가 부정확하여 이 예측치가 실제와 크게 다를 경우 비효율적인 실행 계획이 수립될 수 있습니다.

    실행 계획은 들여쓰기 된 트리 구조로 표시되는데, 가장 안쪽에 있는 작업부터 순서대로 실행되어 바깥쪽으로 결과를 전달하는 방식으로 해석하면 됩니다.

    실제 주행 기록 분석: Autotrace Mode

    Autotrace는 EXPLAIN PLAN과 달리 SQL 문을 ‘실제로 실행’한 후에, 해당 쿼리를 위해 사용된 ‘실행 계획’과 함께 ‘실제 실행 통계(Execution Statistics)’ 정보를 함께 보여주는 강력한 기능입니다. SQL*Plus나 SQL Developer와 같은 클라이언트 도구에서 SET AUTOTRACE ON 명령으로 활성화할 수 있습니다. 이는 마치 자동차의 네비게이션이 안내한 경로(실행 계획)로 실제 주행을 마친 뒤, 총 주행 시간, 평균 연비, RPM 사용량 등의 상세한 주행 기록(실행 통계)을 함께 분석하는 것과 같습니다.

    Autotrace의 실행 통계 정보

    Autotrace가 제공하는 실행 통계 정보는 쿼리가 실행되는 동안 내부적으로 어떤 일이 일어났는지를 구체적인 수치로 보여주기 때문에, 성능 문제의 원인을 훨씬 더 깊이 있게 분석할 수 있게 해줍니다.

    [Autotrace 활성화 및 사용 예시]

    SQL

    -- Autotrace 기능 활성화
    SET AUTOTRACE ON;

    -- 분석할 쿼리 실행 (쿼리 결과와 함께 실행 계획, 통계 정보가 출력됨)
    SELECT * FROM emp WHERE deptno = 20;

    -- Autotrace 기능 비활성화
    SET AUTOTRACE OFF;

    주요 실행 통계 항목은 다음과 같습니다.

    • Consistent Gets / DB Block Gets: 메모리(버퍼 캐시)에서 읽은 데이터 블록의 수입니다. 이 수치가 높을수록 논리적인 I/O가 많았음을 의미하며, 불필요하게 많은 데이터를 읽고 있음을 시사합니다.
    • Physical Reads: 디스크에서 직접 읽어온 데이터 블록의 수입니다. 메모리에 원하는 데이터가 없어 디스크까지 접근했음을 의미하며, 이 수치가 높을수록 물리적인 I/O가 많아 성능이 심각하게 저하됩니다. 튜닝의 핵심 목표 중 하나는 이 Physical Reads를 최소화하는 것입니다.
    • Redo Size: 데이터 변경(DML) 작업 시 발생한 리두 로그의 양입니다.
    • Sorts (memory/disk): 정렬(ORDER BY, GROUP BY 등) 작업을 위해 메모리 또는 디스크를 사용한 횟수입니다. 디스크 정렬이 발생하면 성능이 크게 저하됩니다.

    이처럼 Autotrace는 “이 쿼리는 왜 느릴까?”라는 질문에 대해 “예상보다 훨씬 많은 데이터 블록을 읽고 있고(Consistent Gets 증가), 디스크 접근도 빈번하게 발생하고 있습니다(Physical Reads 증가)” 와 같이 구체적인 수치로 대답해 줄 수 있습니다.

    EXPLAIN PLAN vs Autotrace: 무엇을 언제 써야 할까?

    EXPLAIN PLAN과 Autotrace는 상호 보완적인 관계에 있으며, 목적에 따라 적절하게 사용해야 합니다.

    구분EXPLAIN PLANAutotrace
    쿼리 실행 여부실행 안 함 (예측)실제 실행 (실행 후 분석)
    제공 정보예측된 실행 계획실제 사용된 실행 계획 + 실제 실행 통계
    장점데이터 변경 없이 안전하게 분석 가능, DML 쿼리도 부담 없이 분석실제 리소스 사용량을 정확히 파악 가능, 예측과 실제의 차이 분석
    단점실제 실행 계획과 다를 수 있음, 실제 리소스 사용량은 알 수 없음쿼리를 직접 실행하므로 시간이 오래 걸리거나 시스템에 부하를 줄 수 있음
    주 사용 시점개발 단계, 쿼리 작성 중, DML 튜닝개발 완료 후, 통합 테스트, 운영 환경 성능 분석

    EXPLAIN PLAN은 개발 단계에서 쿼리를 작성하면서 수시로 실행 계획을 확인하고, 인덱스 사용 여부나 조인 순서의 타당성을 검토하는 데 매우 유용합니다. 특히 INSERT, UPDATE, DELETE와 같이 데이터를 변경하는 DML 쿼리는 실제로 실행하기 부담스러우므로, EXPLAIN PLAN을 통해 실행 계획만 미리 확인하는 것이 안전합니다.

    반면, Autotrace는 개발이 어느 정도 완료된 후나 실제 운영 환경에서 성능 문제가 발생했을 때, 그 원인을 정밀하게 분석하는 데 사용됩니다. EXPLAIN PLAN이 보여준 예측 계획과 실제 실행 계획이 다른 경우(바인드 변수 문제나 통계 정보 변경 등으로 인해)가 종종 있는데, Autotrace는 실제 사용된 계획을 보여주므로 더 정확한 분석이 가능합니다. 무엇보다 ‘실행 통계’라는 강력한 무기를 통해, 비효율적인 실행 계획이 구체적으로 어떤 자원을 얼마나 낭비하고 있는지를 객관적인 수치로 증명하고 튜닝의 방향을 설정할 수 있게 해줍니다.

    결론: 예측과 실측으로 완성하는 쿼리 튜닝

    성능 좋은 SQL을 작성하는 것은 단순히 원하는 결과를 정확하게 가져오는 것을 넘어, 그 결과를 ‘가장 효율적인 방법’으로 가져오는 것을 의미합니다. EXPLAIN PLAN과 Autotrace는 이 ‘가장 효율적인 방법’을 찾기 위한 여정에서 개발자에게 길을 안내하는 지도와 계기판의 역할을 합니다.

    EXPLAIN PLAN이라는 지도를 통해 목적지까지의 여러 경로(실행 계획)를 미리 살펴보고 최적의 경로를 설계하고, Autotrace라는 계기판을 통해 실제 주행(쿼리 실행) 시의 연비와 속도(리소스 사용량)를 측정하여 설계가 옳았는지를 검증하고 미세 조정을 가하는 것입니다. 이 두 가지 도구를 능숙하게 사용하여 옵티마이저의 생각을 읽고 그와 소통할 수 있을 때, 비로소 우리는 데이터베이스의 성능을 완전히 통제하고 사용자에게 쾌적한 서비스 속도를 제공하는 전문가로 거듭날 수 있을 것입니다.

  • 데이터베이스 검색의 마법, 인덱스(Index)로 쿼리 속도를 지배하라

    데이터베이스 검색의 마법, 인덱스(Index)로 쿼리 속도를 지배하라

    수백만, 수천만 건의 데이터가 담긴 거대한 데이터베이스 테이블에서 단 하나의 레코드를 찾아내는 작업은, 마치 넓은 사막에서 바늘을 찾는 것과 같습니다. 만약 아무런 도구가 없다면, 우리는 테이블의 첫 번째 행부터 마지막 행까지 모든 데이터를 하나씩 비교해보는 ‘풀 테이블 스캔(Full Table Scan)’이라는 무모한 방법을 사용해야만 합니다. 이는 데이터의 양이 많아질수록 시스템의 성능을 치명적으로 저하시키는 주된 원인이 됩니다. 이때, 마법처럼 검색 속도를 극적으로 향상시켜주는 도구가 바로 ‘인덱스(Index)’입니다.

    인덱스는 두꺼운 책의 맨 뒤에 있는 ‘찾아보기’나 ‘색인’과 정확히 동일한 역할을 합니다. 우리가 책에서 특정 키워드가 나오는 페이지를 찾고 싶을 때, 책의 모든 페이지를 넘겨보는 대신 색인에서 해당 키워드를 찾고 그 옆에 적힌 페이지 번호로 바로 이동하는 것처럼, 데이터베이스 인덱스는 특정 데이터가 테이블의 어느 위치에 저장되어 있는지에 대한 주소 정보를 담고 있는 별도의 자료구조입니다. 인덱스를 활용하면 데이터베이스는 전체 테이블을 뒤지는 대신, 잘 정렬된 인덱스 구조를 먼저 탐색하여 원하는 데이터의 위치를 빠르고 정확하게 찾아낼 수 있습니다. 이 글에서는 데이터베이스 성능 튜닝의 가장 핵심적인 기술인 인덱스의 동작 원리와 장단점, 그리고 효과적인 인덱스 활용 전략에 대해 깊이 있게 알아보겠습니다.

    인덱스는 어떻게 마법을 부리는가: B-Tree의 비밀

    인덱스가 빠른 검색 속도를 보장하는 비결은 내부적으로 사용하는 효율적인 자료구조에 있습니다. 대부분의 관계형 데이터베이스 관리 시스템(RDBMS)은 인덱스를 위해 ‘B-Tree(Balanced Tree)’라는 자료구조를 사용합니다. B-Tree는 이름 그대로 어느 한쪽으로 치우치지 않고 항상 균형을 유지하는 트리 구조로, 어떤 값을 찾더라도 루트(Root) 노드에서 리프(Leaf) 노드까지의 탐색 경로 길이가 거의 동일하다는 특징이 있습니다.

    B-Tree 인덱스는 크게 세 부분으로 구성됩니다. 가장 상위에는 단 하나의 ‘루트 노드’가 있고, 중간에는 여러 단계의 ‘브랜치 노드’가, 그리고 가장 하위에는 실제 데이터의 주소 값을 담고 있는 ‘리프 노드’가 있습니다. 예를 들어, ‘학생 이름’ 열에 인덱스를 생성했다면, 각 노드에는 이름의 일부와 하위 노드를 가리키는 포인터가 저장됩니다. ‘박서준’이라는 학생을 찾기 위해 데이터베이스는 먼저 루트 노드에서 ‘박’으로 시작하는 이름이 어느 브랜치 노드에 속하는지 판단합니다. 해당 브랜치 노드로 이동하여 다시 ‘박서’로 시작하는 범위를 찾아 다음 노드로 이동하는 과정을 반복합니다. 마침내 리프 노드에 도달하면 ‘박서준’이라는 값과 함께, 이 데이터가 실제 테이블의 어느 물리적 주소에 저장되어 있는지에 대한 포인터(ROWID)를 발견하게 됩니다.

    이러한 트리 구조 덕분에 데이터가 아무리 많아져도 탐색 횟수는 로그 시간 복잡도(O(log n))에 따라 매우 완만하게 증가합니다. 100만 건의 데이터가 있더라도 수십 번 이내의 탐색만으로 원하는 데이터를 찾아낼 수 있는, 이것이 바로 인덱스가 부리는 검색 속도의 마법의 원리입니다.

    양날의 검: 인덱스의 장점과 단점

    인덱스는 쿼리 성능을 향상시키는 데 매우 강력한 도구이지만, 모든 상황에 이로운 만능 해결책은 아닙니다. 인덱스를 생성하고 유지하는 데는 분명한 비용이 따르므로, 그 장점과 단점을 명확히 이해하고 사용해야 합니다.

    장점: 압도적인 조회(SELECT) 성능 향상

    인덱스의 가장 명백하고 강력한 장점은 SELECT 쿼리의 성능을 극적으로 향상시킨다는 것입니다. 특히 WHERE 절을 사용하여 특정 조건에 맞는 데이터를 검색하거나, ORDER BY 절을 통해 데이터를 정렬하거나, JOIN을 통해 여러 테이블을 연결할 때 인덱스는 결정적인 역할을 합니다. 인덱스가 없다면 풀 테이블 스캔이 불가피하지만, 적절하게 생성된 인덱스가 있다면 데이터베이스 옵티마이저는 인덱스를 사용하여 필요한 데이터에만 효율적으로 접근하는 실행 계획(Execution Plan)을 세웁니다. 이는 시스템의 응답 시간을 단축시키고, 전체적인 처리량을 높여 사용자 경험을 개선하는 데 직접적으로 기여합니다.

    단점: 쓰기(INSERT, UPDATE, DELETE) 성능 저하와 추가 저장 공간

    인덱스는 ‘읽기’ 성능을 위한 ‘쓰기’ 성능의 희생이라는 대가를 치릅니다. 테이블에 INSERT, UPDATE, DELETE 작업이 발생할 때, 데이터베이스는 테이블의 데이터뿐만 아니라 해당 테이블에 속한 모든 인덱스의 내용도 함께 수정해야 합니다. 예를 들어, 새로운 데이터가 INSERT되면, 인덱스 B-Tree의 정렬 순서를 유지하기 위해 새로운 키 값을 올바른 위치에 추가하고, 경우에 따라서는 트리의 구조를 재조정하는 복잡한 작업이 필요합니다. 따라서 인덱스가 너무 많으면 쓰기 작업 시의 부하가 커져 오히려 전체적인 시스템 성능이 저하될 수 있습니다.

    또한, 인덱스는 원본 데이터와는 별개의 추가적인 저장 공간을 차지합니다. 인덱스도 결국 하나의 데이터베이스 객체이기 때문입니다. 테이블의 크기가 크고 인덱스를 많이 생성할수록, 이들이 차지하는 디스크 공간도 무시할 수 없는 수준으로 커질 수 있습니다.

    작업 종류인덱스 없을 때인덱스 있을 때
    SELECT (조회)느림 (Full Table Scan)매우 빠름 (Index Scan)
    INSERT (삽입)빠름느림 (테이블과 인덱스 모두 수정)
    UPDATE (수정)빠름느림 (테이블과 인덱스 모두 수정)
    DELETE (삭제)빠름느림 (테이블과 인덱스 모두 수정)
    저장 공간적음추가 공간 필요

    현명한 인덱스 활용 전략

    무분별한 인덱스 생성은 오히려 시스템에 독이 될 수 있으므로, 다음과 같은 전략을 바탕으로 신중하게 인덱스를 설계하고 생성해야 합니다.

    어떤 열에 인덱스를 생성해야 하는가?

    일반적으로 다음과 같은 특성을 가진 열에 인덱스를 생성할 때 가장 효과적입니다.

    • 기본 키(Primary Key)와 외래 키(Foreign Key): 이들은 테이블 간의 관계를 맺고 데이터를 식별하는 데 핵심적인 역할을 하므로 대부분의 DBMS에서 자동으로 인덱스가 생성됩니다.
    • WHERE 절에 자주 사용되는 열: 특정 조건으로 데이터를 필터링하는 경우가 많다면 해당 열에 인덱스를 생성하는 것이 좋습니다.
    • JOIN 조건에 자주 사용되는 열: 테이블 조인 시 연결고리가 되는 열에 인덱스가 있으면 조인 성능이 크게 향상됩니다.
    • ORDER BY 절에 자주 사용되는 열: 정렬 작업의 부하를 줄여줍니다.

    카디널리티(Cardinality)를 반드시 고려하라

    카디널리티는 특정 열에 포함된 유니크한 값의 개수를 의미합니다. 인덱스는 카디널리티가 높은 열, 즉 중복도가 낮은 열에 생성해야 효율적입니다. 예를 들어, 모든 학생이 고유한 값을 가지는 ‘학번’이나 ‘이메일’ 열은 카디널리티가 매우 높으므로 인덱스 효율이 좋습니다. 반면, ‘성별’ 열처럼 ‘남’, ‘여’ 두 가지 값만 가지는 낮은 카디널리티의 열에 인덱스를 생성하는 것은 거의 의미가 없습니다. 인덱스를 통해 데이터를 걸러내도 여전히 너무 많은 데이터가 남기 때문에, 데이터베이스 옵티마이저는 차라리 풀 테이블 스캔을 하는 것이 더 빠르다고 판단할 수 있습니다.

    결론: 인덱스는 신중하게 사용하는 양날의 검

    인덱스는 의심할 여지 없이 데이터베이스의 성능을 최적화하는 가장 강력하고 기본적인 도구입니다. 느린 쿼리를 마법처럼 빠르게 만드는 인덱스의 힘은 대용량 데이터를 다루는 모든 시스템에 필수적입니다. 하지만 인덱스는 조회 성능을 위해 쓰기 성능과 저장 공간을 희생하는 명백한 트레이드오프 관계에 있는 양날의 검이라는 사실을 결코 잊어서는 안 됩니다.

    따라서 진정한 데이터베이스 전문가는 무조건 많은 인덱스를 생성하는 사람이 아니라, 시스템의 데이터 특성과 쿼리 패턴을 정확하게 분석하여 꼭 필요한 곳에, 최적의 형태로 인덱스를 설계하고 유지 관리하는 사람입니다. 불필요한 인덱스를 제거하고, 꼭 필요한 인덱스만 남겨 조회와 쓰기 성능 사이의 최적의 균형점을 찾아내는 것, 이것이 바로 인덱스를 통해 데이터베이스의 잠재력을 최대한으로 끌어내는 지혜이자 기술일 것입니다.