튜닝 절차 - 느린 SQL 찾기부터 효과 측정까지
이 장에서 배우는 것
온라인 서점의 주문 집계 화면이 느려졌다는 요청을 받았다고 가정한다. 실행계획을 열면 전체 테이블 스캔이 보인다. 곧바로 인덱스를 추가하고 싶어지지만, 그 전에 확인할 것이 있다. 이 SQL이 화면 지연의 주된 원인인지, 한 번의 실행이 비싼지, 작은 비용의 실행이 지나치게 반복되는지부터 구분해야 한다.
이 장에서는 기본서에서 배운 실행계획과 블록 읽기를 실제 튜닝 절차에 연결한다. 문제 SQL을 찾고, 비교 가능한 기준을 만든 뒤, 하나의 가설을 검증한다. 특정 접근 경로를 항상 좋은 것으로 분류하는 대신, 같은 결과를 더 적은 자원으로 얻었다는 근거를 남기는 것이 목표다.
- 화면 지연과 데이터베이스의 SQL 통계를 연결해 조사 대상을 정한다.
- 논리 읽기, 경과 시간, 실행 횟수를 함께 해석한다.
- 가설 하나에 변경 하나를 대응시키고 실행계획과 측정값으로 검증한다.
- 측정 조건과 결과를 튜닝 기록표에 남겨 재현과 복구에 활용한다.
문제 상황
운영 담당자는 온라인 서점의 ‘일별 주문 금액’ 화면을 연다. 날짜를 선택하면 해당 날짜에 접수된 주문 금액의 합계가 표시된다. 처음에는 빠르게 응답했지만 주문 데이터가 누적된 뒤부터 조회가 지연된다. 화면은 날짜 하나를 조회하는데, 오래된 주문까지 계속 읽는 듯하다.
담당자는 최근 주문이 많은 날에 문제가 생긴다고 설명한다. 그러나 사용자의 설명은 조사 출발점이다. 애플리케이션 로그에서 요청 시작과 종료, 요청 식별자, SQL 실행 구간을 연결해 보니 지연된 요청에서 날짜별 합계 SQL이 한 번 실행되었다. 연결 확보와 결과 표시 구간보다 SQL 실행 구간이 길었다. 이에 따라 이번 조사는 해당 SQL의 단일 실행 비용에 집중한다.
실습 데이터는 1,000일 동안 매일 100건씩 쌓인 주문 100,000건이다. 주문 시각에는 시·분·초가 들어가며, 주문 시각 인덱스는 이미 존재한다. 조회 대상인 2025년 1월 1일에는 100건이 있고 합계는 149,500이다. 기존 SQL은 주문 시각에 TRUNC 함수를 적용해 날짜를 비교한다.
이번 변경은 날짜 조건의 표현만 바꾼다. 인덱스 생성이나 컬럼 추가를 개선안에 포함하지 않는다. 실습 준비 과정에서 만드는 인덱스는 운영에 이미 존재하던 구조를 재현한다. 작은 날짜 범위를 요청하는데 넓은 범위를 읽는다는 가설을 확인하는 데 필요한 만큼만 접근 경로를 살펴본다.
문제 SQL을 찾는 두 가지 관점
조사에는 요청에서 출발하는 방법과 데이터베이스 전체에서 출발하는 방법이 있다. 특정 화면만 느리다면 요청 식별자와 실행 시각으로 SQL을 좁힌다. 서버 전체의 처리량이 떨어졌다면 같은 시간 구간에서 자원을 많이 소비한 SQL부터 살펴본다. 두 방법은 서로 보완한다. 전체 자원 소비가 큰 SQL이 특정 화면 지연의 원인이라는 보장은 없다.
Oracle의 자동 워크로드 저장소(AWR)는 스냅샷 사이의 부하와 SQL 통계를 비교하는 데 사용한다. 경과 시간 합계나 논리 읽기 합계가 큰 SQL을 후보로 삼되, 실행 횟수도 확인한다. 모든 SQL의 모든 실행을 남기는 요청 추적 로그가 아니므로, 보고서에 없다는 사실만으로 문제가 없다고 판단해서는 안 된다.
AWR 사용에는 Oracle Diagnostics Pack의 라이선스 조건 확인이 필요하다. 보고서 생성뿐 아니라 관련 기능과 저장 데이터의 사용 범위를 함께 확인한다. 이 장의 실습은 AWR을 사용하지 않고 현재 커서의 통계를 조회한다. 기능의 사용 조건은 Oracle 19c 라이선스 안내에서 확인할 수 있다.
MySQL의 슬로우 쿼리 로그(slow query log)는 설정한 시간 기준 등을 만족하는 실행을 기록한다. 느린 실행의 SQL과 소요 시간, 조사한 행 수 등을 찾는 출발점이다. 다만 시간 임계값보다 짧은 SQL이 매우 자주 실행되어 전체 부하를 키우는 상황은 이 로그만으로 파악하기 어렵다. 실행 횟수와 누적 비용을 집계하는 통계를 함께 봐야 한다.
| 조사 목적 | Oracle 19c | MySQL 8 | 해석할 때의 주의점 |
|---|---|---|---|
| 시간 구간의 부하 확인 | AWR 스냅샷 차이 | 성능 스키마 집계값의 구간 차이 | 서로 같은 보관 방식이나 수집 범위를 제공하지 않는다. |
| 느린 개별 실행 찾기 | 요청 추적과 SQL 추적 등을 연결 | 슬로우 쿼리 로그 | 수집 설정 밖의 실행은 빠질 수 있다. |
| 현재 SQL의 누적 비용 | V$SQL의 BUFFER_GETS, ELAPSED_TIME, EXECUTIONS | 성능 스키마의 문장별 횟수와 시간 집계 | 누적값을 그대로 비교하지 않고 측정 구간의 차이를 구한다. |
| 실제 실행계획 확인 | 실행 후 DBMS_XPLAN.DISPLAY_CURSOR | 8.0.18 이상에서 EXPLAIN ANALYZE | MySQL의 조사 행 수를 Oracle의 논리 읽기와 같은 단위로 취급하지 않는다. |
운영 SQL을 찾을 때는 SQL 식별자만 기록하지 않는다. 발생 시간대, 서비스와 화면, 대표 바인드 값, 요청당 실행 횟수도 남긴다. 같은 SQL이라도 하루를 조회할 때와 수년을 조회할 때 필요한 작업량이 다르다. SQL 문장이 같다는 이유만으로 서로 다른 요청을 한 집단으로 묶으면 평균값이 원인을 가린다.
세 지표로 비교 기준을 만든다
논리 읽기(logical reads)는 SQL이 버퍼를 통해 블록을 얻는 작업량을 나타낸다. 같은 블록을 반복해서 얻으면 반복 작업도 집계될 수 있으므로, 서로 다른 블록의 개수와 같지 않다. Oracle의 V$SQL에서는 BUFFER_GETS를 통해 커서의 누적 작업량을 확인한다. 디스크 읽기가 거의 없어도 논리 읽기와 그에 따른 처리 비용은 클 수 있다.
경과 시간(elapsed time)은 사용자가 체감하는 지연과 연결되지만 측정 경계를 명시해야 한다. 브라우저 요청 시간에는 연결 확보, 네트워크, 애플리케이션 처리 등이 포함된다. V$SQL의 ELAPSED_TIME은 데이터베이스 커서에 누적된 시간이며 단위는 마이크로초다. 병렬 실행에서는 작업 프로세스의 시간이 반영되므로 단순한 화면 응답 시간으로 읽으면 안 된다.
실행 횟수(executions)는 단일 실행 비용을 전체 부하로 연결한다. 한 번에 논리 읽기 50,000회를 수행하는 SQL과 500회를 수행하는 SQL 중 어느 쪽부터 고칠지는 횟수에 따라 달라진다. 전자가 하루 두 번 실행되고 후자가 분당 1,000번 실행된다면, 후자가 지속적으로 더 큰 작업량을 만들 수 있다. 반대로 긴급한 단건 요청의 지연이 문제라면 단일 실행 시간이 우선이다.
| 지표 | 구간 값 계산 | 실행당 값 계산 | 판단에 쓰는 질문 |
|---|---|---|---|
| 논리 읽기 | 종료 BUFFER_GETS − 시작 BUFFER_GETS | 구간 논리 읽기 ÷ 구간 실행 횟수 | 같은 결과를 얻는 블록 작업이 줄었는가 |
| DB 경과 시간 | 종료 ELAPSED_TIME − 시작 ELAPSED_TIME | 구간 시간 ÷ 구간 실행 횟수 ÷ 1,000 | 실행당 밀리초가 줄었는가 |
| 실행 횟수 | 종료 EXECUTIONS − 시작 EXECUTIONS | 요청 수와 함께 해석 | 호출 자체가 불필요하게 반복되는가 |
실행 횟수의 차이가 0이면 실행당 값을 계산할 수 없다. 측정 사이에 커서가 사라졌거나 다시 적재되었다면 이전 누적값과 이어서 계산해서도 안 된다. 같은 SQL 식별자 안에 여러 자식 커서가 존재할 수 있으므로 자식 번호까지 확인한다. 여러 인스턴스의 통계를 비교한다면 인스턴스 구분도 필요하다.
비교 조건에는 데이터량과 분포, 바인드 값, 통계 정보, 세션 설정, 동시 부하, 결과를 끝까지 가져왔는지를 포함한다. 조회 결과의 첫 화면만 가져온 실행과 전체 결과를 가져온 실행은 같은 실험이 아니다. 이번 SQL은 합계 한 행을 변수로 받으므로 각 호출에서 결과를 모두 소비한다.
첫 실행에는 파싱과 캐시 준비의 영향이 섞일 수 있다. 실습에서는 각 SQL을 한 번 예열한 뒤 열 번 실행한 구간을 측정한다. 이는 캐시가 준비된 반복 조회를 비교하는 방법이며 최초 요청의 지연을 평가하는 방법은 아니다. 운영의 공유 풀이나 버퍼 캐시를 비우지 않는다. 여러 묶음으로 반복할 때는 전후 실행 순서도 번갈아 시간대 편향을 살핀다.
가설 하나를 변경 하나로 검증한다
이번 가설은 ‘날짜를 비교하려고 주문 시각 컬럼을 가공하면서, 소량의 주문을 조회하는 요청에도 넓은 범위를 읽는다’이다. 기존 조건을 해당 날짜의 시작 이상, 다음 날짜의 시작 미만이라는 범위 조건으로 바꾼다. 자정부터 다음 자정 직전까지를 포함하므로 Oracle DATE 값에 저장된 시각을 보존하면서 같은 날짜를 선택한다.
바인드 값은 자정으로 정규화된 DATE라는 전제를 둔다. 운영에서 문자열을 받는다면 입력 형식과 변환 위치를 별도로 정해야 한다. 사용자의 지역 날짜를 시간대가 있는 저장 값에 대응시키는 경우도 이 실습의 DATE 비교와 구분해야 한다. 조건식만 비슷하다고 결과 의미까지 같아지는 것은 아니다.
변경 후에는 결과, 실행계획, 작업량, 시간을 차례로 확인한다. 결과가 다르면 성능 비교를 중단한다. 결과가 같다면 실제 커서의 계획에서 접근 경로와 처리 행 수를 확인한다. 그다음 실행당 논리 읽기와 경과 시간을 비교한다. 실행계획이 달라졌다는 사실만으로 개선을 판정하지 않는다.
예상하는 계획은 변경 전의 전체 테이블 스캔과 변경 후의 인덱스 범위 스캔이다. 그러나 실행계획은 데이터와 통계, 환경에 따라 달라진다. 변경 후에도 전체 스캔이 선택되면 그 사실을 기록하고 가설을 다시 검토한다. 원하는 그림을 얻기 위해 여러 힌트와 구조 변경을 동시에 넣으면 어떤 변경이 효과를 냈는지 알기 어렵다.
실행계획의 상위 단계에 표시된 논리 읽기에는 하위 단계의 작업이 포함될 수 있다. 계획의 모든 Buffers 값을 더해서 SQL 전체 비용으로 만들지 않는다. 이 실습은 전체 작업량을 V$SQL의 구간 차이로 계산하고, 계획은 그 작업이 어떤 경로에서 발생했는지 설명하는 데 사용한다.
완성 코드
다음 파일은 SQL*Plus에서 실행하는 완전한 실습 스크립트다. macOS와 Linux의 클라이언트에서 Oracle 19c 데이터베이스에 접속해 실행한다. 비어 있는 실습 스키마를 사용하며, 같은 이름의 테이블이 있으면 생성 단계에서 중단한다. 재실행을 위해 기존 객체를 자동 삭제하지 않는다.
실습 계정에는 테이블과 인덱스를 만들 권한 및 테이블스페이스 할당량이 필요하다. 통계 수집과 실행계획 출력 패키지를 실행할 수 있어야 하며, V_$SQL, V_$SQL_PLAN, V_$SESSION, V_$SQL_PLAN_STATISTICS_ALL을 조회할 권한도 필요하다. 운영에서는 관리자가 필요한 범위로 부여한다. 이 코드는 저장 프로시저를 만들지 않고 익명 PL/SQL 블록을 실행한다.
측정마다 고유한 주석을 SQL에 넣어 이전 실행의 커서와 구분한다. 실험 중 같은 커서를 다른 세션이 실행하거나 커서가 교체되지 않는 조건을 전제로 한다. 집계에 예상과 다른 실행 횟수가 들어오면 비교를 중단한다.
tuning_process.sql
-- [01] 실행 중 오류가 발생하면 실패 상태로 종료한다.
WHENEVER OSERROR EXIT FAILURE
WHENEVER SQLERROR EXIT SQL.SQLCODE ROLLBACK
SET ECHO OFF
SET VERIFY OFF
SET FEEDBACK OFF
SET HEADING OFF
SET PAGESIZE 0
SET LINESIZE 220
SET TRIMSPOOL ON
SET TAB OFF
SET SERVEROUTPUT ON SIZE UNLIMITED
-- [02] 기존 운영 구조를 재현한다.
CREATE TABLE tproc_orders (
order_id NUMBER NOT NULL,
ordered_at DATE NOT NULL,
total_amount NUMBER(10, 0) NOT NULL,
memo VARCHAR2(200) NOT NULL
) NOPARALLEL;
INSERT INTO tproc_orders (
order_id, ordered_at, total_amount, memo
)
SELECT LEVEL,
DATE '2024-01-01'
+ FLOOR((LEVEL - 1) / 100)
+ MOD(LEVEL, 100) / 1440,
1000 + MOD(LEVEL, 100) * 10,
RPAD('x', 200, 'x')
FROM dual
CONNECT BY LEVEL <= 100000;
COMMIT;
CREATE INDEX tproc_orders_ix1
ON tproc_orders (ordered_at);
-- [03] 데이터 생성 후 통계를 한 번 수집한다.
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(
ownname => USER,
tabname => 'TPROC_ORDERS',
method_opt => 'FOR ALL COLUMNS SIZE 1',
cascade => TRUE
);
END;
/
DECLARE
-- [04] 두 SQL이 공유하는 실험 조건이다.
c_day CONSTANT DATE := DATE '2025-01-01';
c_runs CONSTANT PLS_INTEGER := 10;
c_expected CONSTANT NUMBER := 149500;
l_tag VARCHAR2(32) := RAWTOHEX(SYS_GUID());
l_before VARCHAR2(1000);
l_after VARCHAR2(1000);
PROCEDURE run_case(
p_name IN VARCHAR2,
p_sql IN VARCHAR2,
p_range IN BOOLEAN
) IS
l_sql_id VARCHAR2(13);
l_child NUMBER;
l_sum NUMBER;
l_gets0 NUMBER;
l_us0 NUMBER;
l_exec0 NUMBER;
l_gets1 NUMBER;
l_us1 NUMBER;
l_exec1 NUMBER;
l_count NUMBER;
-- [05] 결과 한 행을 모두 받아 실행을 끝낸다.
FUNCTION execute_once RETURN NUMBER IS
l_value NUMBER;
BEGIN
IF p_range THEN
EXECUTE IMMEDIATE p_sql
INTO l_value USING c_day, c_day + 1;
ELSE
EXECUTE IMMEDIATE p_sql
INTO l_value USING c_day;
END IF;
RETURN l_value;
END;
-- [06] 같은 자식 커서의 누적값을 읽는다.
PROCEDURE snapshot(
p_gets OUT NUMBER,
p_us OUT NUMBER,
p_exec OUT NUMBER
) IS
BEGIN
SELECT buffer_gets, elapsed_time, executions
INTO p_gets, p_us, p_exec
FROM v$sql
WHERE sql_id = l_sql_id
AND child_number = l_child;
END;
PROCEDURE assert_result(p_value IN NUMBER) IS
BEGIN
IF p_value IS NULL OR p_value != c_expected THEN
RAISE_APPLICATION_ERROR(
-20001, '주문 금액 검증 실패'
);
END IF;
END;
BEGIN
-- [07] 예열한 커서를 찾은 뒤 시작 값을 읽는다.
l_sum := execute_once;
assert_result(l_sum);
SELECT sql_id, child_number
INTO l_sql_id, l_child
FROM v$sql
WHERE sql_text = p_sql
AND executions > 0
ORDER BY child_number DESC
FETCH FIRST 1 ROW ONLY;
snapshot(l_gets0, l_us0, l_exec0);
-- [08] 동일한 조건으로 열 번 실행한다.
FOR i IN 1 .. c_runs LOOP
l_sum := execute_once;
assert_result(l_sum);
END LOOP;
snapshot(l_gets1, l_us1, l_exec1);
l_count := l_exec1 - l_exec0;
IF l_count != c_runs
OR l_gets1 < l_gets0
OR l_us1 < l_us0 THEN
RAISE_APPLICATION_ERROR(
-20002, '커서 통계 구간 검증 실패'
);
END IF;
-- [09] 고정 결과와 환경에 따라 달라지는 값을 구분한다.
DBMS_OUTPUT.PUT_LINE(
'검증: ' || p_name
|| ' 합계=' || TO_CHAR(l_sum, 'FM9999999990')
|| ', 실행 증가=' || TO_CHAR(l_count, 'FM90')
);
DBMS_OUTPUT.PUT_LINE(
'측정: ' || p_name || ' 논리 읽기/회='
|| TO_CHAR(
(l_gets1 - l_gets0) / l_count,
'FM9999999990D00',
'NLS_NUMERIC_CHARACTERS=''.,'''
)
|| ', DB 경과 ms/회='
|| TO_CHAR(
(l_us1 - l_us0) / l_count / 1000,
'FM9999999990D000',
'NLS_NUMERIC_CHARACTERS=''.,'''
)
);
-- [10] 방금 측정한 자식 커서의 마지막 실행을 출력한다.
DBMS_OUTPUT.PUT_LINE('계획: ' || p_name);
FOR r IN (
SELECT plan_table_output
FROM TABLE(
DBMS_XPLAN.DISPLAY_CURSOR(
l_sql_id,
l_child,
'ALLSTATS LAST'
)
)
) LOOP
DBMS_OUTPUT.PUT_LINE(r.plan_table_output);
END LOOP;
END;
BEGIN
-- [11] 수집 힌트와 측정 방식은 전후 동일하다.
l_before :=
'SELECT /*+ gather_plan_statistics */ '
|| '/* tp_before_' || l_tag || ' */ '
|| 'SUM(o.total_amount) FROM tproc_orders o '
|| 'WHERE TRUNC(o.ordered_at) = :d';
l_after :=
'SELECT /*+ gather_plan_statistics */ '
|| '/* tp_after_' || l_tag || ' */ '
|| 'SUM(o.total_amount) FROM tproc_orders o '
|| 'WHERE o.ordered_at >= :d '
|| 'AND o.ordered_at < :e';
run_case('변경 전', l_before, FALSE);
run_case('변경 후', l_after, TRUE);
DBMS_OUTPUT.PUT_LINE('검증: 전후 합계 일치');
END;
/
EXIT SUCCESS
줄별 해설
[01]은 오류가 난 상태에서 뒤의 출력만 보고 성공했다고 판단하지 않도록 한다. SQL*Plus의 안내 문구는 줄이되, PL/SQL이 출력하는 검증 결과와 측정값은 남긴다. 데이터 정의문에는 암묵적 커밋이 있으므로 오류 종료의 ROLLBACK이 이미 만든 테이블까지 없애지는 않는다.
[02]는 하루마다 100건씩 배치한다. 날짜에 더하는 정수는 경과 일수이고, 분을 1,440으로 나눈 값은 하루 안의 시각이다. MOD 값은 각 날짜에서 0부터 99까지 한 번씩 등장한다. 따라서 하루 금액은 100 × 1,000 + 10 × 4,950으로 149,500이다. 메모 컬럼은 행에 일정한 폭을 주기 위한 것이며 조회 결과에는 사용하지 않는다.
[03]은 데이터와 인덱스를 준비한 뒤 통계를 수집한다. 변경 전과 변경 후 사이에는 다시 수집하지 않는다. 비교 중 통계까지 바꾸면 조건식 변경과 통계 변경의 영향을 구분하기 어렵다. 이 실습의 균등 분포에서는 컬럼별 히스토그램을 만들지 않도록 지정한다.
[04]는 날짜, 반복 횟수, 예상 합계를 한곳에 둔다. 고유 주석은 커서 검색의 충돌을 줄인다. 이 주석을 운영의 모든 요청에 붙여 사용하는 방식으로 확대하면 SQL 공유를 해칠 수 있다. 여기서는 소수의 실험용 커서를 식별하려는 목적이다.
[05]에서 변경 전 SQL에는 바인드 하나를, 변경 후 SQL에는 시작과 종료 바인드 두 개를 전달한다. 동적 SQL에서는 위치에 맞게 값을 전달한다. 합계 한 행을 INTO로 받으므로 첫 행만 가져온 뒤 열린 결과 집합을 남기는 문제가 없다. NULL도 실패로 처리해 대상 행이 사라진 경우를 놓치지 않는다.
[06]과 [07]은 예열을 마친 커서의 시작 누적값을 읽는다. SQL 문장 전체를 SQL_TEXT와 비교할 수 있도록 문장을 짧게 유지했다. 자식 번호가 큰 커서를 택하는 방식은 이 통제된 실습의 선택 규칙이다. 운영의 여러 자식 커서 중 문제 실행을 식별하는 일반 규칙으로 사용해서는 안 된다.
[08]은 매 실행의 결과를 확인한 뒤 종료 누적값을 읽는다. PL/SQL의 반복문이나 결과 검증에 든 시간은 대상 커서의 ELAPSED_TIME 차이에 직접 포함되지 않는다. 여기서 측정하는 것은 스크립트 전체 소요 시간이 아니라 해당 SQL 커서의 구간 비용이다.
[09]는 누적값 차이를 실행 횟수 차이로 나눈다. 시간에는 마이크로초를 밀리초로 바꾸는 나눗셈도 적용한다. 숫자 출력의 소수점 문자를 지정해 세션의 숫자 표기 설정에 따른 혼동을 줄인다. 실행당 평균만으로 편차를 알 수는 없으므로 운영 판단에는 여러 묶음의 측정도 필요하다.
[10]의 ALLSTATS LAST는 마지막 실행의 행 소스 통계를 요청한다. 여기서 보는 계획의 실제 행 수와 Buffers는 마지막 한 번의 값이고, 앞서 출력한 측정값은 열 번의 평균이다. 둘의 측정 범위를 구분한다. [11]의 gather_plan_statistics는 실제 통계 수집을 요청하며 접근 경로를 강제하는 힌트가 아니다. 수집 자체의 비용이 있으므로 전후에 동일하게 적용한다.
실행 결과
다음 명령은 SQL*Plus가 설치되어 있고, 지갑에 실습 계정의 접속 별칭 BOOKLAB이 구성된 환경을 전제로 한다. 별칭은 실제 환경에 맞춘다. 지갑을 사용하지 않는다면 SQL*Plus에서 실습 계정으로 대화형 접속한 뒤 파일을 실행한다. 암호를 명령행 문자열에 넣을 필요는 없다.
sqlplus -s /@BOOKLAB @tuning_process.sql > tuning_process.log
grep '^검증:' tuning_process.log
오류 없이 완료되었을 때 위의 검증 줄 추출 명령이 출력하는 내용은 다음과 같다.
검증: 변경 전 합계=149500, 실행 증가=10
검증: 변경 후 합계=149500, 실행 증가=10
검증: 전후 합계 일치
실제 측정값과 계획은 같은 로그에서 확인한다. 논리 읽기와 시간은 저장 구조, 캐시, 부하 등에 따라 달라지므로 고정된 예상 숫자로 지정하지 않는다. 접속 실패나 오류 종료가 있었다면 검증 줄 일부가 존재하더라도 성공한 실험으로 보지 않는다.
grep '^측정:' tuning_process.log
cat tuning_process.log
다음은 이 데이터에서 기대하는 접근 경로를 저자가 요약한 것이다. DBMS_XPLAN의 출력 원문이나 실행을 보증하는 결과가 아니다. 실제 로그에서는 각 단계의 추정 행 수와 실제 행 수, 시작 횟수, Buffers 및 조건 정보를 함께 읽는다.
변경 전의 예상 경로
SELECT STATEMENT
SORT AGGREGATE
TABLE ACCESS FULL TPROC_ORDERS
필터: TRUNC(ORDERED_AT) = :D
변경 후의 예상 경로
SELECT STATEMENT
SORT AGGREGATE
TABLE ACCESS BY INDEX ROWID [BATCHED] TPROC_ORDERS
INDEX RANGE SCAN TPROC_ORDERS_IX1
접근: ORDERED_AT >= :D AND ORDERED_AT < :E
BATCHED 표시는 환경에 따라 나타날 수 있음을 뜻한다. 변경 전의 테이블 스캔도 필터를 통과한 행은 100건일 수 있다. 실제 출력 행 수가 100이라는 이유로 테이블의 100행만 조사했다고 읽어서는 안 된다. 또한 SORT AGGREGATE라는 이름만으로 대량 정렬이 병목이라고 판단하지 않는다.
아래 기록표의 성능 수치는 계산과 기록 방법을 설명하기 위한 가정값이다. 이 원고에서 Oracle을 실행해 얻은 실측값이 아니다. 독자는 스크립트가 출력한 값으로 교체한다. 예를 들어 실행당 논리 읽기가 3,240에서 9로 줄었다면 감소율은 약 99.72%다. 이 비율이 다른 데이터나 운영 부하에도 유지된다고 확대 해석하지 않는다.
| 기록 항목 | 변경 전 | 변경 후 | 판정 또는 보관 내용 |
|---|---|---|---|
| 요청과 바인드 | 일별 합계, 2025-01-01 | 동일한 날짜 | 자정의 DATE 값 |
| 데이터와 통계 | 100,000건, 준비 후 수집 | 동일 | 비교 도중 데이터 변경 없음 |
| 변경 내용 | 컬럼에 TRUNC 적용 | 시작 이상·종료 미만 | 인덱스와 통계 변경 없음 |
| 접근 경로 | 전체 테이블 스캔 | 인덱스 범위 스캔 후 테이블 접근 | 실제 계획 원문을 첨부 |
| 결과 합계 | 149,500 | 149,500 | 실습의 예상 결과와 일치 |
| 측정 실행 횟수 | 10 | 10 | 각 SQL의 예열 1회 제외 |
| 논리 읽기/회 | 3,240 | 9 | 가정값 기준 약 99.72% 감소 |
| DB 경과 시간/회 | 8.400ms | 0.180ms | 가정값이며 여러 묶음으로 재확인 |
| 복구 방법 | 기존 조건식 보관 | 변경 SQL 보관 | 배포 버전과 복구 담당자 기록 |
이 실습에서 성능이 기대만큼 개선되지 않아도 실패한 학습은 아니다. 계획이 같다면 조건식 변경이 접근 경로를 바꾸지 못한 이유를 조사한다. 논리 읽기는 줄었는데 시간이 비슷하다면 반복 측정과 대기 상황을 확인한다. 논리 읽기 감소는 작업량에 대한 근거이며, 화면 응답 개선은 요청 전체를 다시 측정해야 확정할 수 있다.
실무에서 자주 틀리는 것
서로 다른 수명의 누적값을 비교한다
오랫동안 실행된 기존 커서와 방금 생성된 변경 커서의 누적 논리 읽기를 비교하면 변경안이 과도하게 좋아 보인다. 다음 조회 결과만으로 전후 우열을 정하는 것이 잘못이다.
-- 잘못된 비교: 커서가 적재된 뒤의 누적값을 그대로 비교한다.
SELECT sql_id, child_number, buffer_gets
FROM v$sql
WHERE sql_id IN (:before_id, :after_id);
같은 커서의 시작과 종료를 읽고 구간 차이를 비교한다. 실행 증가가 0이거나 커서가 교체된 구간은 비교에서 제외한다.
-- 고친 계산: 검증된 동일 커서의 구간 값으로 계산한다.
SELECT (:gets_end - :gets_begin)
/ NULLIF(:exec_end - :exec_begin, 0) AS gets_per_exec
FROM dual;
추정 계획을 실행 결과로 기록한다
EXPLAIN PLAN은 SQL을 실행해 실제 읽기와 처리 행 수를 수집하는 명령이 아니다. 다음 결과를 실제 실행 통계로 기록하면 바인드와 실행 환경의 차이를 놓칠 수 있다.
-- 잘못된 기록: 이 출력만으로 실제 비용을 판단한다.
EXPLAIN PLAN FOR
SELECT SUM(total_amount)
FROM tproc_orders
WHERE ordered_at >= DATE '2025-01-01'
AND ordered_at < DATE '2025-01-02';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
통계 수집을 요청해 실행하고 결과를 모두 가져온 다음, 해당 커서의 식별자와 자식 번호를 지정한다. 아래 조회의 바인드에는 확인한 커서 값을 넣는다.
SELECT /*+ gather_plan_statistics */ SUM(total_amount)
FROM tproc_orders
WHERE ordered_at >= DATE '2025-01-01'
AND ordered_at < DATE '2025-01-02';
SELECT *
FROM TABLE(
DBMS_XPLAN.DISPLAY_CURSOR(
:target_sql_id, :target_child, 'ALLSTATS LAST'
)
);
종료 시각까지 포함해 결과를 바꾼다
BETWEEN은 양쪽 경계를 포함한다. 다음 조건은 다음 날 자정 주문까지 포함한다. 실제로 실습 데이터에는 다음 날 자정 주문이 존재하므로 합계 검증에서 차이를 발견할 수 있다.
-- 잘못된 날짜 범위
WHERE ordered_at BETWEEN DATE '2025-01-01'
AND DATE '2025-01-02'
날짜 단위 조회에는 시작을 포함하고 다음 날짜의 시작을 제외한다. 마지막 시각을 임의로 만들어 넣는 방식보다 경계의 의미가 명확하다.
-- 고친 날짜 범위
WHERE ordered_at >= DATE '2025-01-01'
AND ordered_at < DATE '2025-01-02'
여러 변경을 묶어 원인을 잃는다
조건식을 바꾸면서 인덱스와 통계도 바꾸면 성능 차이의 원인을 분리하기 어렵다. 다음은 한 실험에 서로 다른 변경을 섞은 예다.
-- 잘못된 실험 구성: 세 변수를 한꺼번에 바꾼다.
CREATE INDEX tproc_orders_ix2
ON tproc_orders (ordered_at, total_amount);
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'TPROC_ORDERS');
SELECT SUM(total_amount)
FROM tproc_orders
WHERE ordered_at >= DATE '2025-01-01'
AND ordered_at < DATE '2025-01-02';
이번 실험에서는 준비한 구조와 통계를 유지한 채 조건식만 바꾼다. 다른 변경이 필요하면 별도의 가설과 기록표로 평가한다.
-- 고친 실험 구성: 기존 구조에서 조건식만 변경한다.
SELECT /*+ gather_plan_statistics */ SUM(total_amount)
FROM tproc_orders
WHERE ordered_at >= DATE '2025-01-01'
AND ordered_at < DATE '2025-01-02';
한눈에 보기
| 단계 | 수행할 일 | 남길 증거 | 다음 단계의 조건 |
|---|---|---|---|
| 대상 선정 | 요청과 SQL 비용 연결 | 발생 구간, SQL, 호출 횟수 | 증상과의 연관성 확인 |
| 기준 측정 | 동일 조건에서 반복 실행 | 바인드, 계획, 구간 통계 | 측정 경계와 커서 확인 |
| 가설과 변경 | 예상 원인 하나를 변경 하나로 검증 | 변경 SQL과 예상 효과 | 결과 의미 유지 |
| 효과 검증 | 결과·계획·읽기·시간 비교 | 전후 로그와 편차 | 요청의 성능 목표 충족 |
| 적용과 관찰 | 대표 조건을 넓히고 운영에서 확인 | 배포 기록, 관찰 구간, 복구 방법 | 다른 요청의 회귀 여부 확인 |
단일 합계가 같다는 검증은 이번 화면의 출력 계약에 맞춘 것이다. 여러 행을 반환하는 조회라면 행 수뿐 아니라 키별 값, 중복, NULL, 정렬 요구까지 확인해야 한다. 성능 기록에는 개선된 수치와 함께 검증하지 못한 조건도 남긴다. 다음 장에서는 이 절차를 유지하면서 테이블 랜덤 액세스를 줄이는 변경을 살펴본다.
연습 문제
- 같은 10분 동안 SQL A는 20회 실행되어 논리 읽기 1,000,000회를 기록했다. SQL B는 10,000회 실행되어 논리 읽기 5,000,000회를 기록했다. 실행당 논리 읽기를 계산하고, 전체 부하 절감과 단건 지연 개선에서 조사 우선순위가 어떻게 달라지는지 설명하라.
- 변경 전 SQL은 예열 없이 한 번 실행했고, 변경 후 SQL은 열 번 실행한 뒤 마지막 실행 시간만 기록했다. 변경 후 시간이 절반이 되었다. 이 결과만으로 개선을 확정하기 어려운 이유와 다시 측정할 절차를 작성하라.
- 완성 코드의 변경 후 조건을 BETWEEN :d AND :e로 바꾸고 나머지를 유지하면 어떤 결과가 발생하는가. 실습 데이터의 다음 날 자정 주문 금액까지 계산해 설명하라.
- 변경 후 실제 계획에서 인덱스 범위 스캔이 나타났고 논리 읽기는 줄었다. 그런데 화면 응답 시간은 거의 같았다. 추가로 확인할 항목 세 가지와 튜닝 기록표에 남길 결론을 작성하라.
정답과 해설
- SQL A는 실행당 50,000회, SQL B는 실행당 500회다. 전체 논리 읽기 절감이 목표라면 구간 작업량이 더 큰 B를 우선 조사할 근거가 있다. 특정 요청의 단건 지연이 목표라면 A의 실행당 경과 시간과 대기 원인을 함께 확인한다. 논리 읽기만으로 응답 시간의 우열을 확정할 수는 없다.
- 예열 여부와 표본 수가 다르고 변경 후에는 마지막 값만 골랐다. 동일 데이터와 바인드, 세션 조건에서 각 SQL을 같은 횟수만큼 예열하고 같은 실행 횟수의 구간 차이를 측정한다. 여러 묶음에서 실행 순서를 번갈아 비교한다. 최초 실행 지연이 목적이라면 반복 조회 실험과 분리해 측정한다.
- BETWEEN은 종료 경계도 포함하므로 2025년 1월 2일 자정 주문이 추가된다. 그 행은 MOD 값이 0이므로 금액이 1,000이다. 합계는 150,500이 되며 assert_result가 검증 실패 오류를 발생시킨다. 조건을 빠르게 실행하더라도 원래 화면의 결과와 다르므로 개선안으로 채택할 수 없다.
- 요청 전체에서 SQL 구간의 비중, 연결 확보나 네트워크 등 SQL 밖의 지연, 측정 시점의 동시 부하와 대기 상황을 확인한다. SQL 호출 횟수가 늘었는지도 유효한 조사 항목이다. 기록표에는 ‘해당 SQL의 블록 작업량 감소는 확인했으나 화면 응답 목표 달성은 미확인’이라고 남기고 요청 단위 측정을 이어간다.