Devin.KR
통합 검색 Ctrl K

SQL 처리 과정 - 파싱·최적화·실행과 바인드 변수

개발자KR 조회 9

이 장에서 배우는 것

SQL 한 줄이 실행되기까지 데이터베이스 내부에서는 문법을 검사하고, 이미 처리한 적이 있는 SQL인지 확인하고, 필요하면 실행계획을 새로 세우는 과정을 거친다. 이 과정의 이름과 흐름을 알아 두면 "왜 이 SQL은 빠른데 저 SQL은 느린가"라는 질문에 인덱스나 조인 방식을 보기 전에 먼저 답할 수 있는 경우가 많다. 이 장은 파싱 단계, 소프트 파싱과 하드 파싱의 차이, 라이브러리 캐시(library cache)의 역할, 바인드 변수(bind variable)를 써야 하는 이유와 그 대가인 바인드 피킹(bind peeking)을 다룬다.

  • SQL이 파싱-최적화-실행 3단계를 거치는 흐름을 설명할 수 있다
  • 소프트 파싱과 하드 파싱이 라이브러리 캐시에서 어떻게 갈리는지 구분할 수 있다
  • 리터럴 SQL이 왜 커서를 과다 생성하는지 V$SQL로 직접 확인할 수 있다
  • 바인드 변수가 주는 이득과 바인드 피킹이 만드는 부작용을 함께 설명할 수 있다

문제 상황

온라인 서점의 "내 주문 조회" 화면은 회원이 로그인하면 최근 주문 내역을 보여준다. 이 장에서 다루는 ORDERS 테이블 구조는 다음과 같다.

이 장에서 사용하는 ORDERS 테이블 구조
컬럼타입제약비고
order_idNUMBER(10)PK주문번호
member_idNUMBER(10)NOT NULL인덱스 ix_orders_member, 조회 조건으로 사용
order_dateDATENOT NULL주문일자
statusVARCHAR2(10)NOT NULL주문상태
total_amountNUMBER(10)NOT NULL주문금액

세일 이벤트 기간에 이 화면의 응답이 갑자기 느려졌다. 서버 CPU 사용률은 90%를 넘었는데 정작 디스크 I/O는 평소보다 크게 늘지 않았다. AWR을 보니 대기 이벤트 상위권에 library cache: mutex X가 올라와 있었고, V$SQL을 조회하자 "SELECT * FROM orders WHERE member_id = 481203"처럼 회원ID 리터럴만 다른 SQL 텍스트가 수천 건 쌓여 있었다. 원인은 MyBatis 매퍼에서 바인드 파라미터 표기(#{memberId})를 문자열 치환 표기(${memberId})로 잘못 써서, 회원마다 완전히 다른 SQL 텍스트가 매번 새로 만들어지고 있었던 것이다. 이 장은 이 현상이 왜 CPU와 래치 경합을 만드는지, 그리고 어떻게 고치는지를 다룬다.

SQL이 실행되기까지 - 파싱과 최적화

SQL 문 하나가 서버에 도착하면 데이터베이스는 먼저 파싱(parsing) 단계를 거친다. 파싱은 크게 두 가지 검사로 이루어진다. 구문 검사(syntax check)는 SELECT, FROM 같은 키워드와 괄호 짝이 맞는지 문법만 본다. 의미 검사(semantic check)는 참조한 테이블·컬럼이 실제로 존재하는지, 이 세션이 그 객체에 접근할 권한이 있는지를 확인한다. 두 검사를 통과하면 데이터베이스는 이 SQL을 이전에 처리한 적이 있는지 라이브러리 캐시에서 찾는다.

라이브러리 캐시는 공유 풀(shared pool) 안에 있는 해시 구조로, SQL 텍스트를 해시값으로 바꿔 이미 파싱해 둔 커서(부모 커서와 자식 커서)가 있는지 검사한다. 여기서 "동일한 SQL"의 기준은 생각보다 엄격하다. 대소문자, 공백, 줄바꿈, 힌트, 그리고 리터럴 값까지 문자 단위로 완전히 같아야 같은 커서로 인정한다. WHERE member_id = 1과 WHERE member_id = 2는 사람이 보기엔 같은 쿼리지만 데이터베이스에게는 서로 다른 SQL 텍스트다.

일치하는 커서를 찾으면 소프트 파싱(soft parse)이 일어난다. 이미 만들어진 파싱 트리와 실행계획을 그대로 재사용하므로 최적화 과정을 다시 밟지 않는다. 일치하는 커서가 없으면 하드 파싱(hard parse)이 일어난다. 옵티마이저가 테이블·인덱스 통계를 바탕으로 후보 실행 경로를 비교하고 실행계획을 새로 만드는, 이 책 9장에서 다룰 카디널리티 추정까지 포함하는 무거운 작업이다. 하드 파싱은 CPU를 많이 쓸 뿐 아니라, 라이브러리 캐시에 새 커서를 등록하는 동안 다른 세션과 래치·뮤텍스를 다툰다. 동시 접속자가 많은 서비스에서 하드 파싱이 몰리면 디스크를 전혀 건드리지 않아도 CPU와 래치 경합만으로 응답 지연이 생기는 이유가 여기에 있다.

이 흐름을 V$SQL(또는 V$SQLAREA) 뷰로 관찰할 수 있다. PARSE_CALLS는 이 커서에 대해 파싱을 요청받은 총 횟수, EXECUTIONS는 실행된 총 횟수, LOADS는 이 커서가 라이브러리 캐시에 실제로 적재(=하드 파싱)된 횟수다. PARSE_CALLS가 크더라도 LOADS가 1이면, 최초 1회만 하드 파싱하고 나머지는 모두 소프트 파싱으로 처리했다는 뜻이다.

V$SQL 주요 컬럼이 말하는 것
컬럼의미
PARSE_CALLS파싱을 요청받은 총 횟수(소프트+하드)
EXECUTIONS이 커서가 실행된 총 횟수
LOADS라이브러리 캐시에 적재(하드 파싱)된 횟수
라이브러리 캐시에 일치하는 커서가 있으면 소프트 파싱, 없으면 하드 파싱으로 갈린다

바인드 변수와 바인드 피킹

바인드 변수는 리터럴 값 대신 :member_id 같은 자리표시자를 SQL 텍스트에 넣고, 실제 값은 실행 시점에 따로 전달하는 방식이다. 값이 달라져도 SQL 텍스트는 고정되므로 라이브러리 캐시는 이를 같은 커서로 인식한다. 최초 1회만 하드 파싱하고 이후에는 값이 바뀌어도 소프트 파싱으로 기존 실행계획을 재사용한다. 리터럴 SQL을 계속 쓰면 값의 가짓수만큼 부모 커서가 쌓이는데, 이를 흔히 "커서 폭발"이라 부른다. 커서가 쌓일수록 라이브러리 캐시 공간을 두고 커서끼리 경쟁하고, 오래된 커서를 밀어내는 과정(에이징 아웃) 자체도 CPU를 쓴다. 회원 수가 많은 온라인 서점처럼 조건 값의 종류가 사실상 무한한 서비스에서는 리터럴 SQL 하나가 인스턴스 전체의 공유 풀을 압박할 수 있다.

다만 바인드 변수에는 대가가 있다. 하드 파싱이 일어나는 시점에 옵티마이저는 그때 전달된 바인드 값을 실제로 "엿보고(peek)" 그 값에 맞는 실행계획을 세운다. 이를 바인드 피킹이라 한다. 컬럼 값의 분포가 고르면 문제가 없지만, 특정 값에 데이터가 쏠려 있고 그 분포가 히스토그램에 반영돼 있다면 최초 실행 값에만 맞는 계획이 캐시에 고정되고, 이후 전혀 다른 분포의 값으로 실행해도 같은 계획이 그대로 재사용될 수 있다. 이 경우 추정 건수(E-Rows)와 실제 건수(A-Rows)가 크게 어긋나면서 접근 경로가 비효율적으로 굳어진다. 11g부터 도입된 adaptive cursor sharing은 바인드 값에 민감한 커서를 감지해 추가 자식 커서를 만들어 주지만, 값이 몇 번 더 실행돼야 작동하는 사후 대응이라 최초 실행 직후의 오차까지 막아주지는 않는다. 데이터 분포와 히스토그램을 함께 살피는 습관이 여전히 필요하다.

Oracle 19c와 MySQL 8의 SQL 캐시·바인드 처리 비교
항목Oracle 19cMySQL 8비고
공유 SQL 캐시라이브러리 캐시(shared pool 내 공유 커서)인스턴스 차원의 실행계획 캐시 없음(8.0에서 쿼리 캐시 제거)MySQL은 세션별 prepared statement 캐시만 존재
바인드 변수 문법:name 형태? 또는 PREPARE ... EXECUTE ... USING문법은 다르지만 목적은 같다
바인드 피킹최초 실행 값으로 계획을 엿보고 고정실행마다 통계를 다시 반영하는 경향이 강함버전·설정에 따라 달라질 수 있어 단정은 금물
커서 공유 파라미터CURSOR_SHARING(EXACT/FORCE)해당 없음(애플리케이션이 재사용 책임)Oracle은 DB 레벨 강제 옵션이 있다
리터럴 SQL은 값마다 커서를 새로 만들지만 바인드 변수는 커서 하나를 재사용한다

완성 코드

01_setup.sql

-- 01_setup.sql : 예제 테이블과 데이터 준비 (Oracle 19c)
CREATE TABLE orders (
    order_id      NUMBER(10)    NOT NULL,
    member_id     NUMBER(10)    NOT NULL,
    order_date    DATE          NOT NULL,
    status        VARCHAR2(10)  NOT NULL,
    total_amount  NUMBER(10)    NOT NULL,
    CONSTRAINT pk_orders PRIMARY KEY (order_id)
);

CREATE INDEX ix_orders_member ON orders(member_id);

BEGIN
    FOR i IN 1..1000 LOOP
        IF i <= 400 THEN
            INSERT INTO orders
            VALUES (i, 1, DATE '2026-01-01' + MOD(i, 90), 'PAID', 10000 + i);
        ELSE
            INSERT INTO orders
            VALUES (i, MOD(i, 199) + 2, DATE '2026-01-01' + MOD(i, 90), 'PAID', 10000 + i);
        END IF;
    END LOOP;
    COMMIT;
END;
/

EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'ORDERS', METHOD_OPT => 'FOR COLUMNS member_id SIZE 254', CASCADE => TRUE);

02_bind_demo.sql

-- 02_bind_demo.sql : 리터럴 SQL과 바인드 변수 비교
ALTER SYSTEM FLUSH SHARED_POOL;

-- (1) 리터럴 SQL 20회 실행 - 값마다 다른 하드 파싱 유발
DECLARE
    v_cnt NUMBER;
BEGIN
    FOR i IN 1..20 LOOP
        EXECUTE IMMEDIATE
            'SELECT COUNT(*) FROM orders WHERE member_id = ' || i
            INTO v_cnt;
    END LOOP;
END;
/

SELECT COUNT(*) AS distinct_sql_count, SUM(loads) AS total_loads
FROM   v$sql
WHERE  sql_text LIKE 'SELECT COUNT(*) FROM orders WHERE member_id =%';

-- (2) 바인드 변수로 동일한 조회 20회 실행
DECLARE
    v_cnt NUMBER;
BEGIN
    FOR i IN 1..20 LOOP
        EXECUTE IMMEDIATE
            'SELECT COUNT(*) FROM orders WHERE member_id = :mid'
            INTO v_cnt
            USING i;
    END LOOP;
END;
/

SELECT sql_text, parse_calls, executions, loads
FROM   v$sql
WHERE  sql_text = 'SELECT COUNT(*) FROM orders WHERE member_id = :mid';

-- (3) 바인드 피킹 확인 - 편중된 값과 희소한 값을 같은 커서로 실행
VARIABLE v_mid NUMBER
EXEC :v_mid := 1;

SELECT /*+ GATHER_PLAN_STATISTICS */ SUM(total_amount)
FROM   orders
WHERE  member_id = :v_mid;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

EXEC :v_mid := 50;

SELECT /*+ GATHER_PLAN_STATISTICS */ SUM(total_amount)
FROM   orders
WHERE  member_id = :v_mid;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

줄별 해설

01_setup.sql - ORDERS 테이블에 1,000건을 넣되, member_id = 1에는 400건(전체의 40%)을 몰아 넣고 나머지 600건은 member_id 2~200에 고르게 흩뿌린다. 일부러 분포를 편중시킨 이유는 뒤에서 바인드 피킹의 오차를 재현하기 위해서다. GATHER_TABLE_STATS를 member_id 컬럼에 대해 히스토그램까지 수집하도록 호출해, 옵티마이저가 이 편중을 인식하게 만든다.

02_bind_demo.sql (1) - ALTER SYSTEM FLUSH SHARED_POOL은 라이브러리 캐시를 비워 이전 실습의 잔여 커서가 결과에 섞이지 않게 한다. DBA 권한이 있는 학습용 인스턴스에서만 실행하며, 운영 데이터베이스에서는 절대 호출하지 않는다. 이어서 EXECUTE IMMEDIATE로 member_id 값을 문자열로 직접 이어붙인 SQL을 20번 실행한다. 매번 SQL 텍스트가 달라지므로 라이브러리 캐시는 이를 20개의 서로 다른 커서로 취급하고, 모두 하드 파싱을 거친다.

02_bind_demo.sql (2) - 같은 조회를 :mid 바인드 변수 하나로 20번 실행한다. SQL 텍스트가 한 번도 바뀌지 않으므로 라이브러리 캐시는 첫 실행에서만 하드 파싱하고 나머지 19번은 이미 있는 커서를 그대로 재사용한다. 뒤이은 V$SQL 조회에서 LOADS 값으로 이 사실을 확인한다.

02_bind_demo.sql (3) - SQL*Plus의 VARIABLE로 세션 바인드 변수 v_mid를 선언하고, 값을 1로 두어 실행하면 이 시점에 전달된 값(1, 즉 40%를 차지하는 값)을 기준으로 옵티마이저가 실행계획을 결정한다. GATHER_PLAN_STATISTICS 힌트는 DISPLAY_CURSOR가 실제 처리 건수(A-Rows)까지 보여주게 만드는 장치다. 이어서 v_mid를 50으로 바꿔 같은 SQL을 다시 실행하면, SQL 텍스트가 동일하므로 라이브러리 캐시는 같은 커서를 재사용하고 실행계획도 새로 세우지 않는다. 문제는 이때 캐시된 추정 건수(E-Rows)가 여전히 처음 값(1)을 기준으로 한 400이라는 점이다.

실행 결과

SQL> @01_setup.sql
Table created.
Index created.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.

SQL> @02_bind_demo.sql
System altered.

DISTINCT_SQL_COUNT TOTAL_LOADS
------------------- -----------
                 20          20

SQL_TEXT                                              PARSE_CALLS EXECUTIONS      LOADS
------------------------------------------------------ ----------- ---------- ----------
SELECT COUNT(*) FROM orders WHERE member_id = :mid              20         20          1

SQL> EXEC :v_mid := 1;
PL/SQL procedure successfully completed.

SQL> SELECT /*+ GATHER_PLAN_STATISTICS */ SUM(total_amount)
  2  FROM orders WHERE member_id = :v_mid;

SUM(TOTAL_AMOUNT)
-----------------
          4080200

Plan hash value: 2452363053

---------------------------------------------------------------------------
| Id  | Operation          | Name   | Starts | E-Rows | A-Rows |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |      1 |        |      1 |
|   1 |  SORT AGGREGATE    |        |      1 |      1 |      1 |
|*  2 |   TABLE ACCESS FULL| ORDERS |      1 |    400 |    400 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("MEMBER_ID"=:V_MID)

SQL> EXEC :v_mid := 50;
PL/SQL procedure successfully completed.

SQL> SELECT /*+ GATHER_PLAN_STATISTICS */ SUM(total_amount)
  2  FROM orders WHERE member_id = :v_mid;

SUM(TOTAL_AMOUNT)
-----------------
            31935

Plan hash value: 2452363053

---------------------------------------------------------------------------
| Id  | Operation          | Name   | Starts | E-Rows | A-Rows |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |        |      1 |        |      1 |
|   1 |  SORT AGGREGATE    |        |      1 |      1 |      1 |
|*  2 |   TABLE ACCESS FULL| ORDERS |      1 |    400 |      3 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("MEMBER_ID"=:V_MID)

같은 Plan hash value가 두 실행에서 그대로 반복되는 것에 주목한다. 두 번째 실행은 member_id = 50이라는 희귀한 값을 조회했는데도, 첫 실행에서 만들어진 계획과 그때의 추정 건수(E-Rows = 400)를 그대로 물려받는다. 실제 처리 건수(A-Rows)는 3인데 추정치는 400으로 남아 있는 것이 바인드 피킹이 남긴 흔적이다.

실무에서 자주 틀리는 것

리터럴 값을 문자열로 이어 붙이기

조건 값을 SQL 문자열에 직접 이어붙이면 값의 가짓수만큼 하드 파싱이 반복된다.

// 틀린 코드
String sql = "SELECT * FROM orders WHERE member_id = " + memberId;
ResultSet rs = stmt.executeQuery(sql);

// 고친 코드
String sql = "SELECT * FROM orders WHERE member_id = ?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setLong(1, memberId);
ResultSet rs = ps.executeQuery();

IN 절 값 개수만큼 매번 다른 SQL을 만들기

장바구니 담긴 도서ID 목록처럼 개수가 매번 달라지는 조건을 문자열로 조립하면, 목록 길이가 다를 때마다 새 SQL 텍스트가 생겨 바인드 변수를 써도 하드 파싱이 줄지 않는다.

// 틀린 코드
String sql = "SELECT * FROM books WHERE book_id IN ("
    + ids.stream().map(String::valueOf).collect(Collectors.joining(","))
    + ")";

-- 고친 코드: 컬렉션 타입 하나를 바인드로 전달해 SQL 텍스트를 고정한다
SELECT *
FROM   books
WHERE  book_id IN (SELECT COLUMN_VALUE FROM TABLE(:book_ids));

CURSOR_SHARING을 만능 해결책으로 쓰기

애플리케이션의 리터럴 SQL을 고치는 대신 인스턴스 파라미터로 덮으려 하면, 원래 의도와 다른 바인드 변수가 강제로 생기면서 바인드 피킹 부작용이 인스턴스 전체로 번질 수 있다.

-- 틀린 대응: 근본 원인은 그대로 둔 채 전역 설정만 바꾼다
ALTER SYSTEM SET cursor_sharing = FORCE;

-- 고친 대응: 애플리케이션 코드에서 바인드 변수를 쓰도록 수정하고
-- CURSOR_SHARING은 기본값(EXACT)을 유지한다
-- 손댈 수 없는 레거시 구간만 한시적으로 세션 단위로 제한한다
ALTER SESSION SET cursor_sharing = FORCE;

한눈에 보기

파싱 방식과 바인드 피킹 정리
구분발생 조건비용대응
소프트 파싱동일한 SQL 텍스트의 공유 커서가 이미 있음낮음 - 기존 실행계획 재사용바인드 변수로 재사용률을 높인다
하드 파싱일치하는 커서 없음(리터럴 SQL, 최초 실행 등)높음 - 라이브러리 캐시 경합 + 최적화 연산바인드 변수 사용, 불가피할 때만 CURSOR_SHARING 보조 활용
바인드 피킹하드 파싱 시점의 바인드 값으로 계획을 결정분포가 고르면 무해, 쏠려 있으면 추정 오차히스토그램과 데이터 분포를 함께 점검한다
최초 바인드 값으로 정해진 실행계획이 이후 실행에도 그대로 재사용돼 추정 건수와 실제 건수가 어긋난다

연습 문제

  1. 소프트 파싱과 하드 파싱의 차이를 한 문장씩 설명하고, 두 방식이 각각 어떤 자원(CPU, 라이브러리 캐시 래치·뮤텍스)에 어떤 영향을 주는지 비교하라.
  2. 아래 Java 코드는 회원ID를 문자열로 이어붙여 SQL을 만든다. 어떤 문제가 생기는지 설명하고 PreparedStatement를 쓰도록 고쳐라. 
    String sql = "SELECT * FROM orders WHERE member_id = " + memberId;
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
  3. 바인드 피킹이 유리하게 작동하는 경우와 불리하게 작동하는 경우를 각각 데이터 분포를 예로 들어 설명하라.
  4. 어떤 SQL을 V$SQL에서 조회했더니 PARSE_CALLS는 1,000인데 LOADS는 20이었다. 이 SQL에 어떤 일이 벌어지고 있는지 설명하라.

정답과 해설

1. 소프트 파싱은 라이브러리 캐시에 이미 있는 공유 커서를 찾아 기존 파싱 트리와 실행계획을 그대로 재사용하는 것이고, 하드 파싱은 일치하는 커서가 없어 옵티마이저가 통계를 바탕으로 실행계획을 새로 세우는 것이다. 소프트 파싱은 CPU 소모가 적고 캐시 조회만 하면 되지만, 하드 파싱은 최적화 연산으로 CPU를 많이 쓰고 새 커서를 라이브러리 캐시에 등록하는 동안 다른 세션과 래치·뮤텍스를 다투므로 동시 접속이 많을수록 대기가 커진다.

2. memberId 값마다 SQL 텍스트가 달라져 라이브러리 캐시가 매번 새 커서로 인식하고 하드 파싱을 반복한다. 값의 가짓수만큼 커서가 쌓여 캐시 공간을 압박하고, 동시 접속이 많으면 하드 파싱 과정에서 래치 경합까지 발생한다. PreparedStatement로 바꾸면 SQL 텍스트가 고정되어 최초 1회만 하드 파싱하고 이후에는 소프트 파싱으로 처리된다. 

String sql = "SELECT * FROM orders WHERE member_id = ?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setLong(1, memberId);
ResultSet rs = ps.executeQuery();

3. 컬럼 값의 분포가 고르면(예: 도서 카테고리별 주문 건수가 비슷할 때) 어떤 값으로 최초 하드 파싱이 일어나든 이후 다른 값에도 비슷하게 맞는 계획이 재사용되므로 바인드 피킹이 오히려 매번 최적화 비용을 아껴 준다. 반대로 이 장의 예제처럼 특정 값(member_id = 1)에 40%가 몰려 있고 나머지 값은 희귀할 때는, 최초 실행 값이 무엇이었는지에 따라 이후 모든 실행의 계획이 한쪽으로 치우쳐 추정 건수와 실제 건수가 어긋난다.

4. 이 SQL은 1,000번 파싱을 요청받았지만 그중 20번만 실제로 라이브러리 캐시에 새로 적재(하드 파싱)됐다는 뜻이다. 완전히 동일한 텍스트라면 LOADS는 1에 가까워야 정상인데 20이라는 값은, 커서가 캐시에서 자주 밀려나 다시 만들어졌거나 바인드 변수의 데이터 타입·길이가 실행마다 달라 같은 텍스트인데도 별도 자식 커서로 재구문분석(reparse)되는 상황을 의심할 근거가 된다.

댓글 0

아직 댓글이 없습니다. 첫 댓글을 남겨 보세요.

댓글을 남기려면 로그인이 필요합니다.