SQL 성능 튜닝 핵심

01

SQL은 어떻게 처리되나: 메모리·프로세스·블록 I/O

튜닝이란 결국 같은 결과를 더 적은 일로 얻는 것입니다. 그러려면 데이터베이스가 SQL 한 줄을 받아 무슨 일을 하는지 먼저 알아야 합니다. 큰 그림은 세 가지입니다. 데이터는 디스크의 데이터 파일에 있고, 자주 쓰는 데이터는 메모리에 올려 두며, 그 사이에서 프로세스가 일합니다.

데이터베이스와 인스턴스: 창고와 작업장

Oracle은 디스크에 있는 것과 메모리에 있는 것을 다른 이름으로 부릅니다. 디스크에 저장된 파일들이 데이터베이스, 그 파일을 쓰려고 켜 둔 메모리와 프로세스가 인스턴스입니다. 데이터베이스는 창고, 인스턴스는 창고 앞에 차린 작업장이라고 생각하면 쉽습니다. 서버를 끄면 작업장은 사라지지만 창고는 그대로 남습니다.

위에서 말한 세 가지(디스크·메모리·프로세스)로 나눠 보면 이렇습니다.

버퍼 캐시·공유 풀·리두 로그 버퍼처럼 모든 사용자가 함께 쓰는 메모리를 묶어 SGA, 서버 프로세스 하나가 혼자 쓰는 메모리를 PGA라고 부릅니다.

UPDATE 한 번이 지나가는 길

이 구성들이 어떻게 함께 움직이는지 UPDATE 한 번을 따라가 보면 잘 보입니다.

커밋이 빠른 이유가 4번에 있습니다. 데이터 파일 여기저기에 블록을 바로 쓰는 대신, 짧은 기록 하나를 파일 끝에 이어 쓰기만 하면 되기 때문입니다.

그러면 4번과 5번 사이에 서버가 꺼지면 어떻게 될까요. 커밋은 끝났는데 데이터 파일에는 아직 반영되지 않은 상태입니다. 이때 다시 켜면서 리두 로그 파일의 기록을 처음부터 차례로 다시 실행해 빠진 변경을 채웁니다. 그래서 이 기록을 "다시 하기"라는 뜻의 리두(redo)라고 부릅니다. 반대로 바뀌기 전 값을 남겨 두었다가 되돌릴 때 쓰는 기록은 언두(undo)이고, 9장에서 다룹니다.

SQL 한 줄이 실행되기까지

SQL을 보내면 데이터베이스는 곧바로 데이터를 읽지 않습니다. 먼저 “이 SQL을 어떤 방법으로 실행할까”부터 정합니다. 내비게이션이 출발하기 전에 경로부터 찾는 것과 같습니다.

1단계에서 같은 SQL을 찾으면 2·3단계를 건너뛰고 바로 실행합니다. 이것이 소프트 파싱입니다. 못 찾아서 2·3단계를 모두 거치면 하드 파싱입니다. 경로를 처음부터 다시 찾는 셈이라 하드 파싱은 무겁습니다. 같은 SQL을 다시 쓰게 만드는 방법은 3장(바인드 변수)에서 봅니다.

데이터는 블록 단위로 읽는다

이제 4단계(실행)에서 실제로 데이터를 읽는 모습을 보겠습니다. 데이터베이스는 행을 한 줄씩 꺼내지 않습니다. 행 여러 개가 담긴 블록(보통 8KB) 단위로 읽습니다. 행 하나만 필요해도 그 행이 든 블록 전체를 읽습니다. 블록을 읽을 때는 언제나 버퍼 캐시부터 봅니다.

• 캐시에 있으면 메모리에서 바로 읽습니다. 이것이 논리 I/O입니다.
• 캐시에 없으면 디스크에서 읽어 캐시에 올린 다음 읽습니다. 디스크까지 다녀오는 이 읽기가 물리 I/O입니다.

행을 찾아가는 두 가지 길: 전체 스캔과 인덱스

그러면 필요한 행이 어느 블록에 있는지는 어떻게 알까요. 방법은 크게 두 가지입니다. 두꺼운 책에서 “옵티마이저”가 나오는 쪽을 찾는 상황에 빗대 보겠습니다.

• 테이블 전체 스캔: 책을 첫 쪽부터 끝까지 넘기며 찾습니다. 테이블의 블록을 처음부터 끝까지 모두 읽습니다.
• 인덱스: 책 뒤의 찾아보기에서 “옵티마이저 → 52쪽”을 보고 그 쪽으로 바로 갑니다. 인덱스는 컬럼 값과 그 행이 있는 위치를 값 순서로 정리해 둔 별도의 목록이고, 이것도 블록에 저장됩니다. 맨 위 블록(루트)에서 시작해 중간 블록(브랜치)을 거쳐 맨 아래 블록(리프)까지 내려가면 원하는 값과 행의 위치가 나오고, 그 위치의 테이블 블록을 읽습니다.

인덱스의 구조와 읽는 방식은 4장에서 자세히 봅니다. 여기서는 두 길이 블록을 읽는 방식이 어떻게 다른지만 보겠습니다. 아래 그림에서 처음 실행과 다시 실행, 인덱스로 읽기와 전체 읽기를 바꿔 보세요.

한 블록씩 읽기와 여러 블록씩 읽기

디스크에서 블록을 읽는 방법은 두 가지이고, 기준은 “다음에 읽을 블록을 미리 아는가”입니다.

• 인덱스로 찾아갈 때는 루트 블록을 읽어야 다음 블록이 정해지고, 인덱스를 다 읽어야 테이블의 어느 블록으로 갈지 정해집니다. 미리 알 수 없으니 한 번에 한 블록씩 읽습니다. 싱글 블록 I/O입니다.
• 테이블 전체를 읽을 때는 처음부터 끝까지 읽을 것을 이미 압니다. 그래서 이어진 블록 여러 개를 한 번에 묶어 읽습니다. 멀티블록 I/O입니다.

표의 마지막 줄, 기다릴 때 이름(대기 이벤트)은 헷갈리기 쉽습니다. 디스크를 처음부터 차례로 읽는 전체 스캔이 db file sequential read일 것 같지만, 실제로는 db file scattered read이고 한 블록씩 읽는 쪽이 db file sequential read입니다. scattered는 디스크가 아니라 메모리 쪽 이야기로, 한 번에 읽은 여러 블록을 버퍼 캐시의 떨어진 빈자리에 흩어 담는다는 뜻입니다(Oracle 문서의 설명). sequential은 한 블록씩 차례로 읽는다고 기억하면 됩니다.

인덱스가 언제나 빠르지는 않다

두 읽기 방식의 차이에서 중요한 결론이 나옵니다. 인덱스로 많은 행을 읽으면 전체 스캔보다 느려질 수 있습니다. 행 100만 개가 블록 1만 개에 들어 있는 테이블(블록 하나에 100행)에서 1%인 1만 행을 읽는다고 하고, 디스크 읽기 횟수를 세어 보겠습니다.

인덱스 쪽이 다섯 배 많습니다. 인덱스도 테이블처럼 디스크에 블록으로 저장되고 버퍼 캐시를 거쳐 읽히지만, 늘어나는 것은 인덱스가 아니라 테이블 블록 읽기입니다. 1만 행의 값과 주소는 인덱스 리프 블록 수십 개에 모여 있어 금방 읽지만, 찾은 주소마다 테이블 블록을 찾아가야 하고, 행이 테이블 여기저기 흩어져 있으면 거의 매번 다른 블록입니다(이미 읽은 블록은 캐시에서 끝나 1만 번보다는 적습니다). 여러 블록 읽기 한 번이 한 블록 읽기보다 조금 더 걸리긴 해도 여덟 배까지 걸리지는 않으니, 횟수 차이가 대부분 시간 차이로 이어집니다. Oracle 옵티마이저도 한 블록 읽기 시간(SREADTIM)과 여러 블록 읽기 시간(MREADTIM)을 따로 재어 비용을 계산합니다.

반대로 읽을 행이 테이블 안에 모여 있으면 블록 하나에서 여러 행을 얻으니 인덱스가 훨씬 오래 유리합니다. 행이 모여 있는 정도(클러스터링 팩터)와 인덱스가 불리해지는 지점(손익분기점)은 5장에서 봅니다.

SQL이 블록을 읽으며 일한다는 것을 봤습니다. 그러면 어떤 SQL이 블록을 얼마나 읽었는지는 어떻게 알 수 있을까요. 다음 장에서 봅니다.

02

무엇이 느린지 보기: 실행 계획과 분석 도구

느린 SQL을 고치려면 먼저 어디가 느린지 알아야 합니다. 짐작으로 인덱스부터 만들면 엉뚱한 곳을 고치기 쉽습니다. 확인할 것은 세 가지이고, 각각 보는 도구가 다릅니다.

실행 계획 보기

실행 계획은 옵티마이저가 고른 실행 방법입니다. 어느 테이블을 어떤 순서로, 인덱스로 읽을지 전체를 읽을지, 어떻게 붙일지가 적혀 있습니다. EXPLAIN PLAN으로 볼 수 있습니다.

EXPLAIN PLAN FOR
SELECT e.ename, d.dname
FROM   dept d, emp e
WHERE  d.loc = '서울' AND e.deptno = d.deptno;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

EXPLAIN PLAN은 SQL을 실제로 실행하지 않고 “이렇게 하겠다”는 계획만 보여 줍니다. 그래서 예상 실행 계획이라고 부릅니다. 실제로 실행할 때는 바인드 변수 값 등에 따라 계획이 달라질 수 있습니다.

실제로 쓰인 계획은 SQL을 실행한 뒤 DBMS_XPLAN.DISPLAY_CURSOR로 꺼내 봅니다. 이때 gather_plan_statistics 힌트를 붙여 실행하면 줄마다 예상 행 수(E-Rows)와 실제 행 수(A-Rows)가 나란히 나옵니다. 둘이 크게 다르면 옵티마이저가 잘못 예상한 것이고, 그 줄이 문제의 출발점입니다.

실행 계획 읽는 순서

실행 계획은 들여쓰기로 된 나무 모양이라, 위에서 아래로 읽으면 틀립니다. 규칙은 두 가지뿐입니다.

• 안쪽이 먼저: 들여쓰기가 깊은 줄이 먼저 일하고, 결과를 한 단계 바깥 줄에 넘깁니다.
• 같은 깊이면 위가 먼저: 같은 부모 아래 나란히 있는 줄은 위에 있는 줄부터 일합니다.

아래 그림에서 단계를 넘겨 보세요.

그림의 순서를 말로 풀면 이렇습니다. 지역 인덱스로 서울 부서를 찾고(3), 부서 테이블에서 행을 가져오고(2), 부서마다 사원 인덱스로 사원을 찾고(5), 사원 테이블에서 행을 가져와(4), 둘을 짝지어(1) 돌려줍니다(0).

실제로 얼마나 일했나: AutoTrace와 TKPROF

실행 계획은 “어떻게”만 알려 주고 “얼마나”는 알려 주지 않습니다. 실제로 읽은 블록 수를 보는 도구가 둘 있습니다.

간단히 볼 때는 SQL*Plus의 AutoTrace를 씁니다. SET AUTOTRACE ON을 켜고 SQL을 실행하면 결과 뒤에 실행 계획과 통계가 붙어 나옵니다. 결과 행은 필요 없으면 TRACEONLY를 붙입니다. 통계에서 자주 보는 항목은 이렇습니다.

자세히 볼 때는 SQL 트레이스를 씁니다. ALTER SESSION SET SQL_TRACE = TRUE로 켜면 실행 기록이 트레이스 파일에 남고, 이 파일을 TKPROF로 읽기 좋게 정리합니다. 보고서는 이렇게 생겼습니다.

call     count   cpu  elapsed   disk   query  current   rows
------- ------ ----- -------- ------ ------- -------- ------
Parse        1  0.00     0.00      0       0        0      0
Execute      1  0.00     0.00      0       0        0      0
Fetch        5  0.02     0.35    120     480        0     52
------- ------ ----- -------- ------ ------- -------- ------
total        7  0.02     0.35    120     480        0     52

줄은 호출 단계(Parse·Execute·Fetch)이고, 칸은 그 단계에서 한 일입니다.

가장 먼저 볼 것은 query + current, 곧 논리 I/O입니다. 위 예에서는 52행을 얻으려고 480블록을 읽었습니다. 행 하나에 블록 9개꼴입니다. 이 비율이 크면 필요 없는 블록을 많이 읽고 있다는 신호입니다. 트레이스에는 실행 계획 줄마다 읽은 블록 수(cr)와 걸린 시간도 찍혀 있어서, 어느 줄이 일을 많이 했는지 짚을 수 있습니다.

어디서 기다렸나: 응답 시간 분석

SQL이 느린 이유는 일을 많이 해서일 수도 있고, 무언가를 기다려서일 수도 있습니다. 그래서 걸린 시간을 둘로 나눠 봅니다.

응답 시간 = 서비스 시간(CPU로 일한 시간) + 대기 시간(기다린 시간)

기다린 시간은 무엇을 기다렸는지 이름(대기 이벤트)과 함께 기록됩니다. 가장 오래 기다린 이벤트부터 해결하면 되므로, 히트율 같은 비율을 보는 것보다 원인을 훨씬 바로 가리킵니다.

시스템 전체를 볼 때는 AWR(Automatic Workload Repository)을 씁니다. 일정 간격으로 통계를 저장해 두었다가, 두 시점 사이에 어떤 대기가 가장 많았고 어떤 SQL이 일을 가장 많이 했는지 보고서로 보여 줍니다. 예전 버전에서는 Statspack이 같은 일을 했습니다.

실행 계획은 옵티마이저가 고른 결과입니다. 다음 장에서는 옵티마이저가 무엇을 보고 그 계획을 고르는지 봅니다.

03

옵티마이저는 어떻게 고르나: 비용·통계·SQL 공유·쿼리 변환

같은 SQL도 실행 방법은 여러 가지입니다. 어느 인덱스를 쓸지, 어느 테이블부터 읽을지, 테이블을 어떤 방식으로 붙일지에 따라 수십 가지 조합이 나옵니다. 이 중 하나를 고르는 프로그램이 옵티마이저입니다.

내비게이션에 빗대면 이해가 쉽습니다. 옛날 내비게이션은 “고속도로 우선” 같은 정해진 규칙으로 길을 골랐고, 요즘 내비게이션은 실시간 교통 정보로 걸릴 시간을 계산해 고릅니다. 옵티마이저도 두 가지가 있습니다.

옵티마이저가 보는 것: 통계 정보

CBO는 SQL을 실제로 돌려 보지 않습니다. 미리 모아 둔 통계 정보를 보고 “몇 건이 나올지, 블록을 몇 개 읽을지”를 계산합니다. 교통 정보가 틀리면 내비게이션이 엉뚱한 길로 안내하듯, 통계가 낡거나 틀리면 계획도 틀립니다. 통계는 DBMS_STATS 패키지로 모읍니다.

몇 건이 나올지 예상하기: 선택도와 히스토그램

계산의 중심은 “이 조건이면 몇 건이 나올까”입니다. 전체 중 조건에 맞는 비율을 선택도(selectivity), 그 비율에 전체 행 수를 곱한 예상 건수를 카디널리티(cardinality)라고 합니다.

예를 들어 주문 100만 건의 상태 컬럼에 ‘완료’·‘대기’·‘취소’ 세 값이 있다고 합시다. 옵티마이저가 아는 것이 “값이 세 가지”뿐이라면 값마다 1/3씩 있다고 봅니다. 서로 다른 값의 수(NDV)로 나누는 것이라 선택도 = 1/NDV입니다. 실제로는 완료가 90%, 취소가 1%처럼 한쪽으로 쏠려 있어도 말이죠.

그러면 1%밖에 안 되는 ‘취소’를 찾을 때도 33만 건이 나온다고 예상하고, 인덱스 대신 테이블 전체를 읽는 길을 고릅니다. 이것을 바로잡는 것이 값마다 실제 건수를 적어 둔 히스토그램입니다. 아래 그림에서 직접 켜고 꺼 보세요.

무엇을 빨리 원하나: ALL_ROWS와 FIRST_ROWS

옵티마이저에 목표를 줄 수도 있습니다. 기본인 ALL_ROWS는 결과 전체를 가장 빨리 끝내는 계획을, FIRST_ROWS_n은 처음 n건을 가장 빨리 보여 주는 계획을 고릅니다. 배치 작업은 ALL_ROWS, 첫 페이지만 빨리 띄우면 되는 화면 조회는 FIRST_ROWS가 맞습니다.

힌트: 옵티마이저에 길을 알려 주기

통계로도 맞는 길을 못 찾을 때는 SQL 안에 /*+ ... */ 모양의 주석으로 힌트를 줍니다. 예를 들어 /*+ INDEX(e EMP_DEPT_IDX) */는 그 인덱스를 쓰라는 뜻입니다.

주의할 점이 하나 있습니다. 힌트는 철자가 틀리거나 테이블 별칭이 맞지 않아도 에러가 나지 않고, 그냥 주석으로 무시됩니다. 힌트를 줬다면 실행 계획에 실제로 반영됐는지 꼭 확인합니다.

SQL 공유와 바인드 변수

1장에서 같은 SQL을 다시 실행하면 하드 파싱을 건너뛴다고 했습니다. 그런데 “같은 SQL”의 기준이 엄격합니다. 글자 하나까지 같아야 합니다. 대소문자, 공백, 주석이 달라도, 조건에 넣은 값이 달라도 다른 SQL로 봅니다.

-- 값마다 다른 SQL — 매번 하드 파싱
SELECT * FROM member WHERE member_id = 'M001';
SELECT * FROM member WHERE member_id = 'M002';

-- 바인드 변수 — 한 번 만든 실행 계획을 계속 재사용
SELECT * FROM member WHERE member_id = :id;

값 자리에 :id 같은 바인드 변수를 쓰면 문장이 늘 같아져서, 실행 계획 하나를 모든 값이 함께 씁니다. 같은 SQL이 초당 수천 번 들어오는 시스템이라면 이 차이가 큽니다.

단점도 있습니다. 실행 계획을 만들 때 실제 값이 무엇인지 모른다는 점입니다. 앞 그림처럼 ‘완료’와 ‘취소’에 맞는 길이 다르다면, 어느 값으로 계획을 만들었느냐에 따라 다른 값은 손해를 봅니다. Oracle은 처음 실행할 때 들어온 값을 엿보고 계획을 만들고(바인드 변수 엿보기, bind peeking), 11g부터는 값에 따라 계획을 나눠 쓰는 적응적 커서 공유(Adaptive Cursor Sharing)로 이를 보완합니다. 값에 따라 계획이 꼭 달라야 하는 조건이라면, 그 조건만 값을 그대로 쓰는 것도 방법입니다.

쿼리 변환: 옵티마이저가 SQL을 고쳐 쓴다

옵티마이저는 계획을 고르기 전에 SQL 자체를 결과가 같은 다른 모양으로 고쳐 쓰기도 합니다. 실행 계획에 내가 쓰지 않은 조인이 보이거나, 써 둔 서브쿼리가 사라졌다면 이것 때문입니다. 대표적인 변환은 다섯 가지입니다.

서브쿼리를 풀지 않으면 실행 계획에 FILTER가 나옵니다. 바깥 행마다 서브쿼리를 한 번씩 실행하는 방식입니다. 다만 같은 입력값의 결과는 기억해 두었다가 다시 씁니다(필터 캐싱). 그래서 입력값 종류가 적으면 생각보다 빠르고, 종류가 많으면 행마다 서브쿼리를 도는 것과 같아 느립니다.

옵티마이저가 고르는 길 가운데 가장 자주 나오는 것이 인덱스입니다. 인덱스가 어떻게 생겼고 어떻게 읽히는지부터 봅니다.