종합 사례 - 주문 조회 화면을 단계적으로 튜닝하기

이 장에서 배우는 것

주문 조회 화면은 인덱스 하나를 추가했다고 튜닝이 끝나지 않는다. 테이블 방문을 줄인 뒤에도 조인 횟수가 많을 수 있고, 조인을 줄인 뒤에도 정렬과 깊은 페이지 탐색이 남을 수 있다. 이번 사례에서는 같은 화면을 인덱스, 조인, 정렬, 페이징 순서로 개선한다. 각 단계에서 결과가 같은지 확인하고, 실행계획의 실제 처리 행 수와 논리 읽기를 함께 기록한다.

앞 장에서 다룬 경합과 달리 여기서는 단일 요청이 수행하는 작업량에 집중한다. 최종 목표는 가장 작은 숫자를 만드는 데 있지 않다. 어떤 변경이 어떤 작업을 줄였는지 설명하고, 화면의 조회 의미를 유지한 상태에서 개선 효과를 재현하는 데 있다.

• 한 번에 하나의 주요 변경을 적용하고 전후 수치를 해석한다.
• 페이지에 필요한 주문을 먼저 고른 뒤 고객 정보를 결합할 수 있는 조건을 판단한다.
• 인덱스의 정렬 순서와 페이지 종료 조건을 연결한다.
• 깊은 페이지를 키셋 페이징으로 바꾸고 경계값을 검증한다.
• 직접 만든 모의 문제 15개로 실행계획과 측정 결과를 설명한다.

문제 상황

온라인 서점 운영자가 결제 완료 주문을 최신순으로 조회한다. 화면에는 주문 번호, 주문 시각, 고객명이 표시된다. 한 페이지는 20건이며, 운영자가 오래된 주문을 확인하려고 뒤쪽 페이지로 이동하면 응답이 느려진다. 첫 페이지가 빠르다는 이유로 인덱스가 충분하다고 판단했던 것이 출발점이다.

실습 데이터는 고객 1,000명과 주문 60,000건이다. 주문 번호가 5의 배수인 12,000건을 결제 완료 상태인 P로 만든다. 주문 시각은 1,000건마다 하루씩 증가하며, 같은 시각에서는 주문 번호가 큰 주문을 먼저 보여 준다. 비교 대상은 앞의 10,000건을 건너뛴 다음 20건이다. 실제 서비스의 날짜·상태 분포를 재현한 부하 시험이 아니라, 처리량의 차이를 드러내기 위한 통제된 사례다.

모든 주문은 존재하는 고객을 참조하고 고객 번호는 유일하다. 고객 조건으로 주문을 제외하지 않으며, 실습 도중에는 데이터를 변경하지 않는다. 이 조건이 있어야 주문 20건을 먼저 선택한 뒤 고객을 조인해도 원래 화면과 같은 결과가 나온다.

단계마다 바꾸는 요소와 확인할 작업

단계	주요 변경	확인 대상
S0	전체 주문과 고객을 조인	주문 전체 스캔과 정렬
S1	주문을 덮는 인덱스 추가	주문 테이블 방문 감소
S2	페이지 선택 뒤 고객 조인	고객 조회가 20건으로 감소
S3	정렬 순서에 맞는 인덱스	대상 주문 전체 정렬 제거
S4	직전 행을 기준으로 탐색	앞의 10,000건 순회 감소

단계를 나누고 측정 기준을 고정한다

논리 읽기는 필요한 블록을 버퍼 캐시에서 찾아 읽은 작업량이다. 이미 캐시에 있는 블록을 다시 방문해도 증가한다. 따라서 반복 실행으로 물리 읽기가 감소했다고 해서 SQL 자체가 처리하는 일이 줄었다고 단정할 수 없다. 이번 코드는 각 조회 커서의 V$SQL.BUFFER_GETS 증가량을 기록한다. 측정용 조회나 결과 저장용 DML의 읽기를 여기에 합산하지 않는다.

V$SQL 값은 커서가 공유 풀에 적재된 이후의 누적값이므로 실행 전후 차이를 사용한다. 각 SQL에 단계별 주석을 붙이고 실행 횟수 증가량이 1인지 검사한다. 같은 실습 SQL을 다른 세션에서 동시에 실행하면 측정값이 섞일 수 있다. 공유 풀에서 커서가 사라지는 상황까지 견디는 계측기는 아니므로, 전용 실습 세션과 짧은 측정 구간을 전제로 한다.

실행계획에는 예상 행 수만 보지 않고 실제 행 수인 A-Rows와 연산 시작 횟수인 Starts를 남긴다. 모든 결과를 끝까지 가져온 뒤 DBMS_XPLAN.DISPLAY_CURSOR의 ALLSTATS LAST 형식을 호출한다. 실행계획 각 줄의 Buffers를 더해서 총량을 만들지는 않는다. 부모 연산과 자식 연산의 통계가 중복되는 방식으로 표시될 수 있기 때문이다.

아래 수치는 해석 방법을 설명하기 위해 작성한 가정값이다. 실제 측정 결과가 아니며 코드의 예상 출력도 아니다. 블록 크기, 통계, 패치 수준, 캐시 상태에 따라 달라지는 값을 재현 결과처럼 제시하지 않는다. 실습에서는 생성되는 보고서의 실측값으로 이 표를 대체한다.

논리 읽기 비교 방법을 보여 주는 설명용 가정값

단계	논리 읽기	직전 단계 대비 감소	해석할 변화
S0	1,420	기준	전체 주문과 고객 읽기
S1	320	1,100	좁은 인덱스로 주문 처리
S2	270	50	고객 접근 범위 축소
S3	225	45	순서대로 읽다가 중단
S4	68	157	페이지 경계 근처에서 탐색

S2의 논리 읽기 감소가 작더라도 조인 처리 행 수는 크게 줄 수 있다. 반대로 S3에서 정렬이 없어져도 논리 읽기가 크게 줄지 않을 수 있다. 정렬의 CPU 사용량과 임시 공간 사용량은 블록 읽기 하나로 표현되지 않는다. 실제 화면에서는 경과 시간과 동시 요청 처리량도 따로 측정해야 한다.

인덱스와 조인 순서로 불필요한 행을 줄인다

S1의 인덱스는 상태, 고객 번호, 주문 시각, 주문 번호 순서다. 주문에서 필요한 값이 모두 들어 있으므로 커버링 인덱스(covering index) 역할을 한다. 상태 P의 인덱스 항목만 읽어 주문 테이블 방문을 줄인다. 다만 고객 번호가 주문 시각보다 앞에 있으므로 전체 주문을 최신순으로 반환하지는 못한다.

S0과 S1은 고객을 먼저 읽어 해시 조인(hash join)을 수행하도록 실습 힌트를 넣는다. S1에서도 페이지를 확정하기 전에 결제 완료 주문 12,000건이 고객과 결합된다. 이때 고객명은 정렬에도 주문 필터에도 필요하지 않다. 따라서 S2는 주문만으로 페이지를 정하고 고객 정보는 나중에 가져온다.

S2의 바깥쪽 쿼리는 페이지 결과를 선행 입력으로 삼아 중첩 루프 조인(nested loops join)을 수행한다. 페이지 안쪽에서 주문 20건이 확정되므로 고객 기본 키 조회도 20회가 되는지 확인한다. 실제 계획에서는 기본 키 인덱스와 고객 테이블 접근 연산의 Starts를 읽는다.

이 변경은 조인의 의미를 확인한 뒤에만 적용한다. 고객 등급이 주문 선택 조건이면 페이지 내부에서 그 조건을 평가해야 한다. 고객 한 명에 여러 행이 대응하는 이력 테이블을 조인한다면 행 수도 달라진다. 실습의 외래 키, 고객 기본 키, 고객 필터 부재는 성능 설명을 위한 부속 조건이 아니라 결과 동등성의 근거다.

힌트는 단계별 차이를 관찰하기 위한 실험 장치다. 운영 SQL에 그대로 복사할 처방은 아니다. 실행계획이 의도와 다르면 인덱스 사용 여부, 쿼리 블록의 경계, 통계 상태부터 확인한다. 힌트가 적혀 있다는 사실만으로 해당 접근 경로가 선택되었다고 판단하지 않는다.

정렬 생략과 페이지 탐색은 다른 문제다

S3에서는 상태, 주문 시각 내림차순, 주문 번호 내림차순, 고객 번호 순서로 새 인덱스를 만든다. 상태가 하나의 값으로 고정되므로 그 아래 항목은 화면의 정렬 순서와 맞는다. 선두 N건 조회(Top-N)를 위한 종료 조건이 적용되면 주문 12,000건을 모두 정렬하지 않고 필요한 위치까지 읽다가 멈출 수 있다.

그러나 OFFSET 10000은 사라지지 않았다. 반환하는 주문은 20건이어도 페이지 내부에서는 앞선 10,000건을 지나야 한다. 계획에 WINDOW NOSORT STOPKEY와 같은 연산이 나타나더라도 인덱스 입력의 A-Rows가 20인지 10,020인지 구분해야 한다. 바깥쪽에는 최종 출력 순서를 보장하기 위한 20건 정렬이 남을 수 있다. 이 작은 정렬과 주문 전체를 대상으로 한 정렬을 구별한다.

S4의 키셋 페이징(keyset pagination)은 직전 페이지 마지막 행의 주문 시각과 주문 번호를 함께 전달한다. 내림차순이므로 더 작은 시각이거나, 같은 시각이면서 더 작은 주문 번호를 선택한다. 실습의 경계는 주문 번호 10005, 주문 시각 2025-01-11이다. 이 값은 앞의 10,000번째 결제 완료 주문이며, 실습 데이터 생성식으로 계산해 넣는다. 운영 화면은 실제 응답의 마지막 행에서 받은 값을 사용한다.

복합 경계의 OR 조건 전체가 인덱스 시작 조건으로 바뀐다고 가정하지 않는다. 코드에는 주문 시각이 경계 이하라는 조건도 명시한다. 이 조건이 범위 접근에 사용되면 같은 날짜의 최신 항목부터 읽어 경계 앞의 200건을 거른 뒤 20건을 찾는 형태가 가능하다. 따라서 이 데이터에서도 인덱스가 반드시 20건만 읽는다고 설명하면 부정확하다. 실제 계획의 접근 조건과 필터 조건을 나누어 확인한다.

같은 시각에 주문이 많이 몰리면 이 잔여 탐색도 커질 수 있다. 키셋 방식이라는 이름보다 실행계획에서 시작 위치를 얼마나 좁혔는지가 중요하다. 또한 키셋 방식은 다음 페이지 이동에 맞는다. 임의의 페이지 번호로 바로 이동하려면 경계값을 별도로 확보해야 한다.

이 사례를 Oracle 19c와 MySQL 8에서 적용할 때의 차이

항목	Oracle 19c	MySQL 8
페이지 제한	OFFSET … ROWS FETCH NEXT … ROWS ONLY	LIMIT 20 OFFSET 10000
실제 계획	실행 후 DISPLAY_CURSOR로 행 수와 버퍼 통계 확인	8.0.18 이상에서 EXPLAIN ANALYZE로 실제 실행 확인
읽기 수치	커서의 BUFFER_GETS 증가량 사용	동일한 의미의 값을 실행계획에서 바로 얻을 수 없음
인덱스 정렬	복합 인덱스의 순서와 접근 조건 확인	InnoDB의 내림차순 인덱스와 선택된 접근 경로 확인

MySQL의 행 접근 횟수나 저장 엔진의 전역 읽기 수치를 Oracle의 논리 읽기와 같은 단위로 놓지 않는다. 사실 확인에는 Oracle의 DBMS_XPLAN 설명, V$SQL 항목 정의, MySQL의 EXPLAIN 설명을 참고할 수 있다.

완성 코드

다음 파일은 SQL*Plus에서 실행하는 완전한 Oracle 실습 스크립트다. macOS에서는 SQL*Plus 클라이언트로 별도 Oracle 19c 서버에 접속하며, Linux에서도 같은 방식으로 실행한다. 운영 데이터가 없는 새 실습 스키마에서 한 번 실행한다. 기존 객체를 자동 삭제하지 않으므로 같은 이름의 객체가 있으면 중단된다.

계정에는 테이블·인덱스 생성 권한과 테이블스페이스 할당량이 필요하다. 익명 블록에서 사용하는 DBMS_SQL, DBMS_STATS, DBMS_XPLAN 실행 권한도 필요하다. 관리자는 계측에 필요한 SYS.V_$SQL, SYS.V_$SQL_PLAN, SYS.V_$SQL_PLAN_STATISTICS_ALL, SYS.V_$SESSION 조회 권한을 준비해야 한다. 코드는 저장 프로시저를 만들지 않으며, 정상 환경에서는 익명 블록이 경고 없이 컴파일되고 실행된다.

실제 SQL 다섯 개를 실행하고 반환된 주문 번호의 순서를 비교한다. 논리 읽기와 실행계획은 테이블에 저장한 뒤 case-report.txt로 출력한다. 인덱스 생성과 통계 수집 비용은 화면 SQL의 측정값에 포함하지 않는다.

case-study.sql

whenever sqlerror exit failure rollback
set echo off feedback off verify off heading off
set pagesize 0 linesize 220 trimspool on tab off
set serveroutput off sqlblanklines on
set termout off

create table cs_customer (
  customer_id number primary key,
  customer_name varchar2(40) not null
);

create table cs_orders (
  order_id number primary key,
  customer_id number not null references cs_customer(customer_id),
  ordered_at date not null,
  status varchar2(1) not null,
  memo varchar2(200) not null
);

create table cs_measure (
  stage varchar2(2) primary key,
  logical_reads number not null,
  row_count number not null,
  order_ids varchar2(1000) not null
);

create table cs_plan (
  stage varchar2(2) not null,
  line_no number not null,
  plan_text varchar2(4000)
);

insert into cs_customer
select level, 'C' || to_char(level, 'FM0000')
from dual connect by level <= 1000;

insert into cs_orders
select level,
       mod(level - 1, 1000) + 1,
       date '2025-01-01' + floor((level - 1) / 1000),
       case when mod(level, 5) = 0 then 'P' else 'N' end,
       rpad('x', 200, 'x')
from dual connect by level <= 60000;

commit;

begin
  dbms_stats.gather_table_stats(
    user, 'CS_CUSTOMER', cascade => true,
    method_opt => 'FOR ALL COLUMNS SIZE 1');
  dbms_stats.gather_table_stats(
    user, 'CS_ORDERS', cascade => true,
    method_opt => 'FOR ALL COLUMNS SIZE 1');
end;
/

declare
  v_baseline varchar2(1000);
  v_expected varchar2(1000);
  v_sql varchar2(4000);
  v_inner varchar2(3000);

  procedure run_stage(p_stage varchar2, p_sql varchar2) is
    v_cursor integer;
    v_result integer;
    v_id number;
    v_date date;
    v_name varchar2(40);
    v_count number := 0;
    v_ids varchar2(1000);
    v_gets_before number;
    v_gets_after number;
    v_exec_before number;
    v_exec_after number;
    v_sql_id varchar2(13);
    v_child number;
  begin
    -- A: 실행 전에 같은 SQL의 누적값을 읽는다.
    select nvl(sum(buffer_gets), 0), nvl(sum(executions), 0)
      into v_gets_before, v_exec_before
      from v$sql
     where sql_text = p_sql;

    -- B: 결과를 모두 가져와 실행 통계를 완성한다.
    v_cursor := dbms_sql.open_cursor;
    dbms_sql.parse(v_cursor, p_sql, dbms_sql.native);
    dbms_sql.define_column(v_cursor, 1, v_id);
    dbms_sql.define_column(v_cursor, 2, v_date);
    dbms_sql.define_column(v_cursor, 3, v_name, 40);
    v_result := dbms_sql.execute(v_cursor);

    while dbms_sql.fetch_rows(v_cursor) > 0 loop
      dbms_sql.column_value(v_cursor, 1, v_id);
      dbms_sql.column_value(v_cursor, 2, v_date);
      dbms_sql.column_value(v_cursor, 3, v_name);
      v_count := v_count + 1;
      v_ids := v_ids || to_char(v_id, 'FM9999990') || ',';
    end loop;

    dbms_sql.close_cursor(v_cursor);
    v_cursor := null;

    -- C: 실행 횟수와 반환 결과를 검사한다.
    select nvl(sum(buffer_gets), 0), nvl(sum(executions), 0)
      into v_gets_after, v_exec_after
      from v$sql
     where sql_text = p_sql;

    if v_exec_after - v_exec_before != 1
       or v_gets_after < v_gets_before then
      raise_application_error(-20001, 'Cursor measurement changed');
    end if;

    if v_count != 20 or v_ids != v_expected then
      raise_application_error(-20002, 'Unexpected page');
    end if;

    if p_stage = 'S0' then
      v_baseline := v_ids;
    elsif v_ids != v_baseline then
      raise_application_error(-20003, 'Page mismatch');
    end if;

    insert into cs_measure
    values (p_stage, v_gets_after - v_gets_before, v_count, v_ids);

    -- D: 이번 실행의 실제 계획을 보관한다.
    select sql_id, child_number
      into v_sql_id, v_child
      from (
        select sql_id, child_number
          from v$sql
         where sql_text = p_sql and executions > 0
         order by last_active_time desc, child_number desc
      )
     where rownum = 1;

    insert into cs_plan
    select p_stage, rownum, plan_table_output
      from table(dbms_xplan.display_cursor(
        v_sql_id, v_child, 'ALLSTATS LAST +PREDICATE'));

  exception
    when others then
      if v_cursor is not null then
        if dbms_sql.is_open(v_cursor) then
          dbms_sql.close_cursor(v_cursor);
        end if;
      end if;
      raise;
  end;

  function early_join(p_stage varchar2, p_access varchar2)
    return varchar2 is
  begin
    return 'select /*+ gather_plan_statistics leading(c o) ' ||
           'use_hash(o) ' || p_access || ' */ /* CS_' || p_stage ||
           ' */ o.order_id, o.ordered_at, c.customer_name ' ||
           'from cs_customer c join cs_orders o ' ||
           'on o.customer_id = c.customer_id ' ||
           'where o.status = ''P'' ' ||
           'order by o.ordered_at desc, o.order_id desc ' ||
           'offset 10000 rows fetch next 20 rows only';
  end;

  function late_join(p_stage varchar2, p_inner varchar2)
    return varchar2 is
  begin
    return 'select /*+ gather_plan_statistics leading(p c) ' ||
           'use_nl(c) no_merge(p) */ /* CS_' || p_stage ||
           ' */ p.order_id, p.ordered_at, c.customer_name ' ||
           'from (' || p_inner || ') p join cs_customer c ' ||
           'on c.customer_id = p.customer_id ' ||
           'order by p.ordered_at desc, p.order_id desc';
  end;

  function page_sql(p_index varchar2, p_tail varchar2)
    return varchar2 is
  begin
    return 'select /*+ index(o ' || p_index || ') */ ' ||
           'o.order_id, o.ordered_at, o.customer_id ' ||
           'from cs_orders o where o.status = ''P'' ' || p_tail;
  end;
begin
  for i in 0 .. 19 loop
    v_expected := v_expected ||
      to_char(10000 - 5 * i, 'FM9999990') || ',';
  end loop;

  run_stage('S0', early_join('S0', 'full(o) full(c)'));

  -- E: 주문 테이블 방문을 줄이되 정렬 문제는 남긴다.
  execute immediate
    'create index cs_cover on cs_orders ' ||
    '(status, customer_id, ordered_at, order_id)';

  run_stage('S1', early_join('S1', 'index(o cs_cover) full(c)'));

  v_inner := page_sql('cs_cover',
    'order by o.ordered_at desc, o.order_id desc ' ||
    'offset 10000 rows fetch next 20 rows only');
  run_stage('S2', late_join('S2', v_inner));

  -- F: 화면의 정렬 순서와 맞는 인덱스를 추가한다.
  execute immediate
    'create index cs_feed on cs_orders ' ||
    '(status, ordered_at desc, order_id desc, customer_id)';

  v_inner := page_sql('cs_feed',
    'order by o.ordered_at desc, o.order_id desc ' ||
    'offset 10000 rows fetch next 20 rows only');
  run_stage('S3', late_join('S3', v_inner));

  -- G: 직전 페이지의 마지막 행을 경계로 사용한다.
  v_inner := page_sql('cs_feed',
    'and o.ordered_at <= date ''2025-01-11'' ' ||
    'and (o.ordered_at < date ''2025-01-11'' ' ||
    'or (o.ordered_at = date ''2025-01-11'' ' ||
    'and o.order_id < 10005)) ' ||
    'order by o.ordered_at desc, o.order_id desc ' ||
    'fetch first 20 rows only');
  run_stage('S4', late_join('S4', v_inner));

  commit;
end;
/

spool case-report.txt
prompt MEASUREMENTS
select stage || ' rows=' || to_char(row_count, 'FM9990') ||
       ' logical_reads=' || to_char(logical_reads, 'FM9999999999990')
from cs_measure
order by stage;

prompt ORDER_IDS
select stage || ' ' || order_ids
from cs_measure
order by stage;

prompt EXECUTION_PLANS
select stage || ' ' || plan_text
from cs_plan
order by stage, line_no;
spool off

set termout on
prompt PASS: 5 stages returned the same 20 order IDs.
prompt Report: case-report.txt
exit success

줄별 해설

객체 생성 부분은 주문에 200바이트 메모를 넣는다. 화면에 필요 없는 넓은 컬럼이 있는 테이블과 필요한 값만 담은 인덱스의 차이를 관찰하기 위해서다. 메모는 어느 조회의 반환 목록에도 넣지 않는다. 고객명은 고객 번호로 결정되므로 이 고정 데이터에서는 주문 번호 순서가 같으면 표시되는 고객명도 같다.

A 부분은 SQL 문자열 전체가 같은 커서의 통계를 합산한다. SQL_ID 하나만 저장해 누적값을 그대로 출력하는 방식과 달리, 이전 실행이 있더라도 이번 실행의 증가량을 구한다. 여러 자식 커서가 존재할 가능성을 고려해 합계를 사용하지만, 실행계획은 최근 활성 자식 커서 하나에서 읽는다. 운영 진단에서는 대상 자식 커서를 더 엄밀히 식별해야 한다.

B 부분의 세 DEFINE_COLUMN 호출은 반환 컬럼의 자료형과 순서에 대응한다. FETCH_ROWS를 끝까지 반복해야 마지막 페이지 행까지 처리된 실행 통계를 볼 수 있다. 화면에서 20건만 출력하고 클라이언트 커서를 조기에 닫는 다른 실험과 섞지 않는다.

C 부분은 행 개수와 주문 번호의 순서를 함께 검사한다. 20건이라는 개수만 같아서는 같은 페이지라고 할 수 없다. 기대 번호는 10000부터 5씩 감소해 9905까지다. 날짜와 고객명까지 일반적인 결과 동등성 검사를 구현한 것은 아니므로, 실제 시스템에서는 화면의 모든 컬럼도 비교한다.

D 부분은 실행 직후 계획을 테이블에 저장한다. 이후 다른 SQL을 실행한 다음 인자 없이 DISPLAY_CURSOR를 부르면 엉뚱한 커서를 볼 수 있으므로 SQL_ID와 자식 번호를 지정한다. 보고서 저장 자체의 읽기는 화면 SQL의 BUFFER_GETS에 포함되지 않는다.

E 부분의 인덱스는 필터와 컬럼 확보를 해결한다. F 부분의 인덱스는 여기에 정렬 순서를 더한다. 인덱스 생성 시 통계가 수집되는 정상적인 Oracle 환경을 전제로 하며, 보고서에서 예상 행 수가 크게 어긋나면 통계 상태도 확인한다. 실습에서는 두 인덱스를 남겨 비교하지만 운영 배포에서는 중복 인덱스의 유지 비용을 검토한다.

G 부분은 OFFSET을 제거한다. 주문 시각 상한과 복합 경계를 함께 써서 시작 범위를 좁히고, 같은 시각의 주문 사이에서도 다음 위치를 구별한다. 완성 코드에 있는 날짜 리터럴과 번호는 재현성을 위한 값이다. 애플리케이션에서는 날짜를 DATE 또는 해당 컬럼과 맞는 타임스탬프 자료형의 바인드 변수로 전달한다.

실행 결과

다음 명령은 SQL*Plus가 설치되어 있고 Oracle Wallet 접속 별칭 booklab이 준비된 macOS/Linux 셸에서 실행한다. 다른 인증 방식을 사용하면 접속 문자열만 바꾼다.

sqlplus -s /@booklab @case-study.sql

오류 없이 완료되었을 때 터미널 출력은 다음과 같다. 스크립트 내부 출력은 파일로 보내고, 성공 문구는 모든 결과 검사가 끝난 뒤에만 표시한다.

PASS: 5 stages returned the same 20 order IDs.
Report: case-report.txt

보고서는 다음 명령으로 연다. 논리 읽기와 실행계획은 실행한 서버가 생성한 값이므로 고정 예상 출력으로 제시하지 않는다.

cat case-report.txt

보고서의 ORDER_IDS 구간에는 S0부터 S4까지 동일한 번호 목록이 기록된다. 그중 S4 행은 다음과 정확히 일치한다.

S4 10000,9995,9990,9985,9980,9975,9970,9965,9960,9955,9950,9945,9940,9935,9930,9925,9920,9915,9910,9905,

실제 계획에서 확인할 전후 차이

비교	변경 전 관찰점	변경 후 관찰점	판정 근거
S0 → S1	주문 전체 스캔	CS_COVER 범위 스캔	주문 테이블 접근 제거와 읽기 변화
S1 → S2	페이지 제한 전 해시 조인	페이지 제한 뒤 고객 조회	고객 기본 키 접근 Starts 약 20
S2 → S3	주문 집합의 정렬	인덱스 순서와 중단 연산	대량 정렬 제거, 입력 약 10,020건
S3 → S4	OFFSET 위치까지 순회	날짜 범위와 복합 경계 필터	인덱스 읽기 범위와 Buffers 감소

표는 실제 계획의 고정 복사본이 아니라 판독 기준이다. 특히 S4에서 필터 후 A-Rows가 20이라고 해서 방문한 인덱스 항목도 20개라고 해석하지 않는다. 접근 조건에 날짜 상한이 들어가는지, 잔여 조건이 어디서 평가되는지, 커서의 논리 읽기가 줄었는지를 함께 확인한다. PASS는 결과 일치를 뜻하며 성능 개선을 자동 판정하지 않는다.

실무에서 자주 틀리는 것

동일 시각의 주문을 구별하지 않는다

다음 코드는 날짜가 같은 주문의 순서를 확정하지 않는다. 데이터가 그대로여도 페이지 경계가 불안정할 수 있다.

order by ordered_at desc

유일한 주문 번호를 두 번째 정렬 기준으로 사용한다. 경계 토큰에도 두 값을 함께 보관한다.

order by ordered_at desc, order_id desc

고객 필터를 페이지 선택 뒤로 옮긴다

다음 형태는 고객 조건에 맞지 않는 주문까지 먼저 20건에 포함하므로 화면에 20건보다 적게 나올 수 있다.

select p.order_id
from (
  select order_id, customer_id, ordered_at
  from cs_orders
  where status = 'P'
  order by ordered_at desc, order_id desc
  fetch first 20 rows only
) p
join cs_customer c on c.customer_id = p.customer_id
where c.customer_name like 'C00%'

고객 조건이 주문의 포함 여부를 결정하면 페이지를 확정하기 전에 적용한다.

select o.order_id
from cs_orders o
join cs_customer c on c.customer_id = o.customer_id
where o.status = 'P'
  and c.customer_name like 'C00%'
order by o.ordered_at desc, o.order_id desc
fetch first 20 rows only

경계 행을 다음 페이지에 다시 포함한다

동일 시각에서 주문 번호에 등호를 넣으면 직전 페이지의 마지막 주문이 다시 나온다.

where ordered_at < :last_time
   or (ordered_at = :last_time and order_id <= :last_id)

내림차순의 다음 페이지는 경계 행 자체를 제외한다. 다른 필터와 결합할 때는 OR 전체를 괄호로 묶는다.

where status = 'P'
  and ordered_at <= :last_time
  and (ordered_at < :last_time
       or (ordered_at = :last_time and order_id < :last_id))

커서 누적 읽기를 한 번의 실행 비용으로 표시한다

다음 조회 결과를 곧바로 이번 실행의 읽기라고 보고하면 이전 실행의 비용이 섞인다.

select buffer_gets
from v$sql
where sql_id = :sql_id and child_number = :child_no;

같은 측정 범위에서 실행 전후 값을 확보하고 차이를 사용한다. 실행 횟수 차이도 함께 확인해야 한다.

select :gets_after - :gets_before as logical_reads,
       :exec_after - :exec_before as executions
from dual;

한눈에 보기

성능 개선과 결과 보존을 함께 확인하는 기준

변경	줄이는 작업	남는 작업	결과 보존 조건
커버링 인덱스	주문 테이블 방문	조인과 정렬	반환 컬럼과 필터 유지
페이지 뒤 조인	고객 결합 행 수	주문 페이지 선택	고객 조인이 주문을 제외·증식하지 않음
정렬 인덱스	대량 정렬	OFFSET 순회	정렬 컬럼과 방향 일치
복합 경계	이전 페이지 순회	경계 부근 필터	유일한 순서와 정확한 경계값

실습 뒤에는 조회 외의 비용도 평가한다. 인덱스를 늘리면 주문 입력과 상태 변경에 유지 작업이 추가된다. 또한 여러 페이지를 서로 다른 시점에 조회하면 주문 상태 변경이나 삭제로 화면 구성이 달라질 수 있다. 키셋 방식은 페이지 간 동일 시점의 데이터까지 보장하지 않는다. 이번 비교는 고정된 데이터에서 수행한 결과 동등성 검증이다.

연습 문제

다음은 이 사례를 위해 새로 만든 모의 문제 15개다. 네 묶음으로 나누었으며 각 질문에 판단 근거를 함께 쓴다.

1. 측정과 해석

   문제 1. 첫 실행의 논리 읽기가 900, 두 번째 실행이 910인데 경과 시간은 절반이 되었다. SQL의 읽기 작업이 줄었다고 결론 내릴 수 있는가.

   문제 2. 커서의 BUFFER_GETS가 실행 전 8,000, 실행 후 8,420이다. 이번 실행의 논리 읽기는 얼마인가.

   문제 3. 문제 2의 구간에서 EXECUTIONS가 2 증가했다. 420을 한 요청의 비용으로 기록해도 되는가.

   문제 4. 실행계획의 부모 Buffers가 300이고 자식 Buffers가 280이다. 총 읽기를 580으로 기록해야 하는가.

2. 인덱스와 조인

   문제 5. S1에 주문 메모를 반환 컬럼으로 추가했다. 기존 인덱스가 계속 주문 테이블 방문을 없애는가.

   문제 6. 고객 등급이 주문 선택 조건으로 추가되었다. S2처럼 무조건 주문 20건부터 고를 수 있는가.

   문제 7. 주문 한 건에 배송 이력 세 건이 연결된다. 페이지 뒤에 이력을 그대로 조인하면 반환 행 수는 유지되는가.

   문제 8. S2의 고객 기본 키 인덱스 Starts가 20이고 A-Rows가 20이다. 무엇을 확인한 것이며 무엇은 확인하지 못한 것인가.

3. 정렬과 페이징

   문제 9. 상태, 고객 번호, 주문 시각, 주문 번호 순서의 인덱스로 전체 결제 완료 주문의 최신순 정렬을 생략할 수 있는가.

   문제 10. S3에서 정렬이 생략되었다. OFFSET 10000의 처리 비용도 사라졌는가.

   문제 11. 주문 시각만 경계로 사용하면서 같은 시각의 주문이 100건 존재한다. 어떤 누락 가능성이 있는가.

   문제 12. S4의 최상위 A-Rows가 20이다. 인덱스 항목도 정확히 20개만 방문했다고 결론 내릴 수 있는가.

4. 운영 적용

   문제 13. 마지막 행의 경계값 없이 사용자가 501번째 페이지를 직접 선택했다. 키셋 SQL만으로 즉시 같은 위치를 찾을 수 있는가.

   문제 14. 두 인덱스를 모두 유지하니 주문 상태 변경이 느려졌다. 조회 성능이 좋아졌으므로 둘 다 유지해야 하는가.

   문제 15. S4를 운영에 배포하기 전 최소한 어떤 결과 검사와 성능 검사를 해야 하는가.

정답과 해설

1. 결론 내릴 수 없다. 논리 읽기는 비슷하다. 물리 읽기 감소, 대기 변화, CPU 환경 차이 등을 확인해야 한다. 캐시가 따뜻해진 효과를 SQL 작업량 감소와 구분한다.

2. 420이다. 누적값 8,420을 그대로 기록하지 않는다. 전후에 같은 SQL과 같은 집계 범위를 사용했다는 조건이 필요하다.

3. 안 된다. 두 실행의 합계일 수 있다. 210으로 나누면 구간 평균은 얻을 수 있지만 특정 화면 요청의 개별 비용은 알 수 없다. 이 실습은 이런 구간을 오류로 처리한다.

4. 아니다. 계획 줄의 통계는 단순 합산 대상이 아니다. 최상위 통계와 커서 단위 증가량으로 전체 비용을 확인하고 하위 연산은 비용 발생 위치를 찾는 데 사용한다.

5. 아니다. 메모가 인덱스에 없으므로 값을 얻으려면 주문 테이블을 방문해야 한다. 넓은 메모까지 인덱스에 넣기보다 페이지 확정 뒤 필요한 주문만 방문하는 구조를 먼저 검토한다.

6. 무조건 적용할 수 없다. 등급 필터가 주문의 포함 여부를 바꾸므로 페이지 확정 전에 평가해야 한다. 그렇지 않으면 20건을 골랐다가 일부가 탈락해 다음 후보를 놓친다.

7. 유지되지 않는다. 조인 결과가 주문당 세 행으로 증가할 수 있다. 화면이 주문 단위인지 배송 이력 단위인지 먼저 정하고 대표 이력 선택이나 집계 규칙을 명확히 해야 한다.

8. 고객 인덱스 연산이 20회 시작되어 합계 20행을 반환했음을 확인했다. 이것만으로 전체 논리 읽기가 20이라는 뜻은 아니며 주문을 찾는 비용도 설명하지 못한다.

9. 일반적으로 생략할 수 없다. 상태가 고정되어도 고객 번호별로 묶인 순서가 먼저다. 고객 번호도 단일 값으로 고정되는 별도 조회라면 판단이 달라진다.

10. 사라지지 않는다. 출력은 20건이어도 앞의 10,000건을 통과해야 한다. 정렬 생략은 순서를 만드는 비용을 줄이고, 키셋은 탐색 위치를 좁힌다.

11. 경계보다 작은 시각만 선택하면 같은 시각에 남은 주문이 누락될 수 있다. 경계 이하를 선택하면 앞 페이지 주문이 반복될 수 있다. 유일한 주문 번호를 함께 비교한다.

12. 결론 내릴 수 없다. 필터에서 버린 항목이 있을 수 있다. 특히 같은 날짜의 경계 앞 주문을 읽는 작업은 최종 반환 행 수에 드러나지 않을 수 있다.

13. 경계 없이 같은 위치를 바로 찾는 기능은 없다. 앞 페이지의 경계, 미리 저장한 위치 정보 또는 별도의 위치 탐색이 필요하다. 화면의 직접 이동 요구와 다음 페이지 이동 요구를 구분한다.

14. 그렇지 않다. 다른 SQL의 이용 여부와 입력·상태 변경 비용을 함께 평가한다. 실습용 CS_COVER가 운영에서도 필요한지 확인하고, 제거 후보는 실제 부하를 바탕으로 결정한다.

15. 같은 시각이 많은 경계, 첫 페이지, 마지막 페이지, 결과가 없는 조건에서 번호·순서·표시 컬럼을 비교한다. 실행계획의 접근 조건과 실제 행 수, 논리 읽기, 경과 시간을 여러 깊이에서 기록한다. 마지막으로 실제 변경과 동시 요청이 있는 환경에서 화면 의미와 처리량을 검증한다.