Dev · Oracle

치환(&)과 바인드(:) — 하나는 클라이언트가, 하나는 오라클이 처리한다

&name 과 :name 은 화면에서 거의 똑같이 생겼고, 실행 결과도 자주 같다. 그래서 같은 기능의 다른 표기라고 착각하기 쉬운데 둘은 서로 다른 컴퓨터에서 처리된다. 치환은 SQL*Plus·Toad 가 문자열을 갈아 끼운 뒤 보내고, 바인드는 문장을 그대로 보낸 다음 값을 따로 넘긴다. 이 한 줄 차이가 하드파스·공유 커서·SQL 인젝션·실행계획까지 전부 갈라놓는다.

Archive · Oracle 9i

원문은 2018년 8월, 오라클 9i 와 Toad 를 쓰는 현장에서 정리한 것이다. 치환과 바인드의 동작 자체는 9i 부터 지금까지 그대로지만, 실행계획 쪽은 9i 가 확실히 다르다 — 바인드 값을 엿보는 bind peeking 은 9i 에 있고, 그것을 보정하는 적응형 커서 공유는 11g 에 가서야 생긴다. 그래서 9i 에서는 바인드가 손해인 구간이 지금보다 넓다. 6절이 그 이야기다.

원문에 있던 Toad·SQL*Plus 스크린샷은 옛 도메인을 정리하면서 링크가 끊기므로, 같은 내용을 화면 재현으로 다시 그렸다.

1. 갈리는 지점은 "누가 처리하는가"다

같은 조회를 두 방식으로 쓰고, 오라클이 실제로 받는 문장을 나란히 놓으면 차이가 한눈에 보인다.

치환 & — 클라이언트가 갈아 끼운다

DEFINE sal = 10000
SELECT * FROM employees
 WHERE salary > &sal;

오라클이 받는 문장:
… WHERE salary > 10000
오라클은 &sal 을 본 적이 없다.

바인드 : — 오라클이 읽는다

VAR sal NUMBER
EXEC :sal := 10000
SELECT * FROM employees
 WHERE salary > :sal;

오라클이 받는 문장:
… WHERE salary > :sal
값 10000 은 문장과 따로 전달된다.

치환은 편집기의 찾아 바꾸기와 같은 층위의 기능이다. 문자열을 갈아 끼우는 것이 전부라 타입이라는 개념이 없고, 무엇으로 바꾸든 오라클 입장에서는 그냥 다른 SQL 문자열이 하나 더 온 것이다.

바인드는 오라클의 파서가 아는 자리표시자다. :sal 은 타입을 가진 변수여서, 숫자가 들어갈 수 없는 자리에 숫자 바인드를 쓰면 구문 오류로 막힌다. 그리고 문장 자체가 값과 무관하게 항상 같은 문자열이라는 점이 뒤에 나올 모든 이야기의 출발점이다.

2. 치환 — `DEFINE`, `&`, 그리고 `&&`

DEFINE tbname = employees
SELECT * FROM &tbname;
UNDEFINE tbname

DEFINE 으로 값을 정하고, 쓸 때는 이름 앞에 앰퍼샌드를 붙인다. UNDEFINE 으로 해제한다. 값에 공백이나 구두점이 들어가면 따옴표로 감싸는데, 이때 따옴표는 구분자라서 값에 남지 않는다. 즉 DEFINE tbname = 'employees' 라고 써도 치환되는 것은 employees 다.

DEFINE 줄이 없으면 클라이언트가 값을 물어본다. SQL*Plus 는 명령줄에서, Toad 는 대화상자로.

Toad for Oracle — Variable Substitution

Enter a value for tbname:

employees|

CancelOK

SQL*Plus 라면 같은 상황에서 Enter value for tbname: 를 콘솔에 찍고 기다린다.

Toad 의 F5 와 F9 가 다르게 도는 이유

원문이 짚은 부분이고, 지금도 그대로 맞다. F5(Run as script) 는 편집기의 내용을 스크립트로 통째로 돌리므로 DEFINE 줄이 먼저 실행되고, 뒤의 &tbname 은 조용히 치환된다. F9(Execute statement) 는 커서가 놓인 한 문장만 떼어 실행하므로 DEFINE 줄은 아예 실행되지 않고, 정의되지 않은 변수를 만나 대화상자를 띄운다.

같은 파일, 같은 커서 위치인데 단축키에 따라 결과가 달라진다는 게 요점이다. 스크립트를 배포할 때는 F5 로만 확인하고 넘기지 말 것 — 받는 사람이 F9 로 돌리면 다르게 돈다.

원문에 없던 두 가지 — `&&` 와 `SET DEFINE OFF`

&& 는 물어본 값을 세션에 저장한다. 한 스크립트에서 같은 변수를 여러 번 쓰는데 & 로만 쓰면 나올 때마다 다시 묻는다. 첫 참조를 && 로 쓰면 그때 DEFINE 된 것처럼 남아 이후에는 묻지 않는다.

SET DEFINE OFF 는 치환 기능 자체를 끈다. 데이터에 앰퍼샌드가 들어 있으면 치환이 원치 않게 끼어들어 값이 잘려 들어간다 — 아래가 그 전형이다.

INSERT INTO vendor VALUES ('AT&T');
-- Enter value for t:  ← 데이터를 넣으려는데 변수를 물어본다

SET DEFINE OFF
INSERT INTO vendor VALUES ('AT&T');
SET DEFINE ON

Toad 에도 같은 목적의 옵션이 있다 — View → Toad Options → Editor → Behavior 의 "Prompt for substitution variables" 를 끄면 된다. 원문이 스크린샷으로 보여 준 옵션이 이것이고, 주석 안의 앰퍼샌드까지 변수로 인식해 프롬프트가 뜨는 경우에 특히 쓸모가 있다.

3. 바인드 — `VAR`, `EXEC`, `PRINT`

VAR sal NUMBER
EXEC :sal := 10000
SELECT * FROM employees WHERE salary > :sal;
PRINT sal

VAR(=VARIABLE)로 이름과 타입을 선언하면, 그 세션이 끝날 때까지 콜론을 붙여 참조할 수 있다. PRINT 는 지금 담긴 값을 찍는다 — 원문에 없던 명령인데, 값이 제대로 들어갔는지 확인할 유일한 방법이라 사실상 한 세트다.

EXEC 은 마법이 아니라 익명 블록의 축약이다. 아래 두 줄은 완전히 같다. 이 사실을 알아 두면 PL/SQL 블록과 바인드 변수가 어떻게 맞물리는지가 정리된다.

EXEC :sal := 10000;
-- 위 한 줄이 아래로 풀려 서버에 전달된다
BEGIN :sal := 10000; END;

Toad 에서는 위 스크립트를 F5 로 돌리면 그대로 실행되지만, SELECT 줄에 커서를 두고 F9 를 누르면 :sal 이 정의되지 않았다며 값을 묻는 대화상자가 뜬다. F9 는 VAR·EXEC 줄을 실행하지 않으므로, Toad 가 그 자리에서 대신 선언하고 값을 받아 넣는 것이다.

Toad for Oracle — Bind Variables

Variable :sal Datatype NUMBER

10000|

CancelOK

치환 프롬프트와 달리 데이터 타입 칸이 있다. 오라클이 아는 변수이기 때문이다.

편할 때는 편한 동작이지만 기대면 안 된다. 배치 스크립트를 sqlplus 로 돌릴 때는 아무도 물어봐 주지 않는다. 스스로 선언하고 할당하는 법을 알고 있어야 하는 이유가 이것이다.

4. 바인드가 빠른 진짜 이유 — 그리고 원문의 과장

오라클은 SQL 을 받으면 먼저 라이브러리 캐시에서 같은 문장을 찾는다.

하드 파스 — 없으면 구문 분석 → 의미 분석(테이블·권한 확인) → 최적화(실행계획 생성) → 커서 생성까지 전부 한다. 비싼 쪽은 압도적으로 최적화다.
소프트 파스 — 있으면 이미 만들어 둔 실행계획을 그대로 붙여 쓴다.

찾는 기준은 SQL 텍스트다. 한 글자라도 다르면 다른 SQL 이다 — 대소문자도, 공백 하나도, 주석도. 그래서 리터럴을 문자열로 붙여 만든 SQL 은 값이 바뀔 때마다 매번 하드 파스가 되고, 바인드를 쓴 SQL 은 값이 무엇이든 문장이 같으니 한 번만 하드 파스된다.

원문 정정

원문은 두 번째 실행에서 "파싱 + 실행계획 생성에 들어가는 시간이 0에 가까워진다"고 썼다. 0 은 아니다. 소프트 파스도 라이브러리 캐시를 뒤지고 래치를 잡는 비용을 낸다.

그리고 이 정정이 결론을 더 강하게 만든다. 리터럴 SQL 의 진짜 피해는 쿼리 하나의 응답 시간이 아니라 동시 사용자가 늘었을 때 나온다. 서로 다른 SQL 이 수만 건씩 캐시에 쌓이면서 공유 풀을 밀어내고, library cache 래치 경합이 시스템 전체를 느리게 만들고, 심하면 ORA-04031: unable to allocate … shared pool 로 터진다. 한 명이 쓸 때는 티가 안 나다가 운영에서 무너지는 전형적인 형태다.

5. 성능보다 먼저인 이유 — SQL 인젝션

원문이 통째로 빠뜨린 부분인데, 실무에서는 이쪽이 더 중요하다. 바인드 값은 파싱이 끝난 뒤에 들어간다. 문장의 구조는 값이 도착하기 전에 이미 확정돼 있으므로, 값에 무엇을 넣어도 구문이 될 수 없다.

문자열을 이어 붙인 동적 SQL — 뚫린다v_sql := 'SELECT * FROM employees WHERE last_name = ''' || p_name || '''';
EXECUTE IMMEDIATE v_sql;

-- p_name 에 다음이 들어오면
x'' OR ''1''=''1
-- 완성된 문장: WHERE last_name = 'x' OR '1'='1'  → 전 행이 나온다

바인드 — 같은 입력이 들어와도 그냥 '못 찾는 이름'이다v_sql := 'SELECT * FROM employees WHERE last_name = :name';
EXECUTE IMMEDIATE v_sql INTO v_row USING p_name;

치환(&)은 인젝션 방어가 아니다. 방어는커녕 그 자체가 문자열 이어붙이기라서, 사용자 입력을 & 로 받는 스크립트는 인젝션의 정의 그대로다. 치환은 내가 짠 스크립트에서 테이블 이름이나 날짜를 손으로 바꿔 넣는 용도지, 바깥에서 들어온 값을 담는 자리가 아니다.

참고로 테이블 이름·컬럼 이름은 바인드로 넘길 수 없다. 파싱 시점에 확정돼야 하는 식별자이기 때문이다. 그래서 그 부분만은 문자열로 조립할 수밖에 없는데, 그때는 반드시 허용 목록(화이트리스트)으로 걸러야 한다. 9i 이후 버전이라면 DBMS_ASSERT.SIMPLE_SQL_NAME 같은 검증 함수가 도움이 된다 — 다만 9i 에는 기본으로 없다. 10g Release 2 에서 들어왔다.

6. 바인드가 손해인 구간 — 9i 에서 특히

바인드를 항상 쓰라는 조언에는 예외가 있다. 값에 따라 좋은 실행계획이 달라지는 컬럼이다.

status 컬럼의 99.9% 가 'DONE' 이고 나머지 0.1% 만 'PENDING' 이라고 하자. 리터럴로 쓰면 오라클은 히스토그램을 보고 'PENDING' 에는 인덱스를, 'DONE' 에는 전체 스캔을 각각 고를 수 있다. 바인드로 쓰면 문장이 하나뿐이므로 계획도 하나뿐이다.

버전	바인드 값과 실행계획
9i	bind peeking 이 도입됐다. 하드 파스 때 첫 실행의 바인드 값을 한 번 엿보고 그 값에 맞춰 계획을 만든 뒤, 그 커서가 캐시에 살아 있는 동안 모든 실행이 그 계획을 쓴다. 첫 실행이 `'PENDING'` 이었으면 이후 `'DONE'` 조회까지 인덱스를 타며 기어간다. 반대도 마찬가지다.
11g+	적응형 커서 공유(Adaptive Cursor Sharing) 가 붙었다. 바인드 값에 따라 성능이 갈리는 것을 감지해 같은 SQL 에 계획을 여러 개 유지한다. 9i 의 이 문제가 대부분 사라진다.

그래서 9i 레거시에서는 "무조건 바인드"가 통하지 않는다. 편중이 심한 컬럼의 조건은 의도적으로 리터럴로 두거나, 힌트·저장된 아웃라인(Stored Outline)으로 계획을 고정하는 판단이 필요하다. "월요일 아침 첫 실행이 그 주의 성능을 결정한다"는 말이 9i 현장에서 나온 이유가 이것이다.

소스를 고칠 수 없을 때 — `CURSOR_SHARING`

반대 상황도 있다. 리터럴 SQL 을 쏟아내는 오래된 애플리케이션인데 소스를 손댈 수 없는 경우, 오라클이 리터럴을 시스템 생성 바인드로 바꿔치기 하도록 시킬 수 있다.

값	동작	지금 쓸 수 있나
`EXACT`	기본값. 바꾸지 않는다	그대로
`FORCE`	리터럴을 전부 바인드로 치환해 커서를 공유한다	지금도 유효. 다만 위의 편중 문제를 그대로 떠안는다
`SIMILAR`	계획이 달라질 수 있는 리터럴만 남기고 나머지를 치환 — 9i 에서 도입	11.2 에서 폐기 예고, 12.1 에서 지원이 끊겼다 — 설정해도 `FORCE` 로 동작한다. 여기에 기대고 있으면 업그레이드 때 계획이 통째로 바뀐다

응급 처방이지 해법이 아니다. 고칠 수 있는 소스라면 바인드로 고치는 쪽이 언제나 낫다. 9i 레거시를 올릴 계획이 있다면 SIMILAR 를 쓰고 있는지부터 확인할 것.

7. 정리

기준	치환 `&`	바인드 `:`
처리 주체	클라이언트 (SQL*Plus·Toad)	오라클 서버
선언	`DEFINE` / `UNDEFINE`	`VAR` / `EXEC` / `PRINT`
타입	없다 — 전부 문자열	있다 — 안 맞으면 오류
오라클이 받는 문장	값마다 다르다	항상 같다
파싱	값이 바뀔 때마다 하드 파스	한 번 하드 파스 후 공유
SQL 인젝션	막지 못한다 (그 자체가 이어붙이기)	막는다
실행계획	값별로 최적화 가능	계획 하나 (9i 는 첫 값에 고정)
테이블·컬럼 이름	넣을 수 있다	넣을 수 없다
맞는 자리	내가 손으로 돌리는 스크립트의 대상 바꾸기	애플리케이션이 반복 실행하는 모든 SQL

한 줄로 줄이면 이렇다. 구조가 바뀌면 치환, 값만 바뀌면 바인드. 사용자에게서 온 값이면 선택지는 바인드 하나뿐이고, 값에 따라 계획이 갈리는 편중 컬럼은 — 특히 9i 라면 — 그 규칙의 유일한 예외다.