SQL 처리 과정의 전체 흐름: Parse부터 Execute·Fetch까지

핵심 요약

SQL 처리 과정은 Oracle 내부 처리 단계와 Client가 Cursor를 사용하는 호출 생명주기를 나누어 이해하면 가장 정확합니다.

Oracle 내부의 기본 처리 단계
Parse → 필요 시 Optimize → Row Source Generation → Execute

Query를 사용하는 Client 호출 생명주기
Parse/Prepare → Bind·Define → Execute → Fetch 반복 → Close

두 흐름은 서로 연결되지만 완전히 같은 분류는 아닙니다.

• Oracle 공식 내부 처리 단계는 Parsing, Optimization, Row Source Generation, Execution입니다.
• Fetch는 Query 결과를 Client가 받아 가는 호출 단계이며, 실제 Row Source 작업은 Execute와 Fetch에 걸쳐 진행될 수 있습니다.
• 재사용 가능한 Cursor가 있으면 Soft Parse가 가능하고, 새로운 실행계획이 필요하면 Hard Parse 과정에서 Optimizer와 Row Source Generator가 동작합니다.

학습 목표

이 이론을 학습한 뒤에는 다음 내용을 설명할 수 있어야 합니다.

• Oracle 내부 SQL 처리 4단계와 Client 호출 생명주기를 구분한다.
• Parse Call, Cursor, Shared SQL Area, Private SQL Area의 관계를 설명한다.
• Soft Parse와 Hard Parse가 Optimize·Row Source Generation에 미치는 차이를 설명한다.
• Row Source Tree를 부모가 자식에게 행을 요청하는 반복 실행 구조로 해석한다.
• SELECT의 Execute와 Fetch, DML의 Execute가 담당하는 일을 구분한다.
• Fetch Size, 부분 Fetch, END_OF_FETCH_COUNT가 애플리케이션 처리 방식과 어떤 관계인지 설명한다.
• SQL Trace와 V$SQLSTATS의 Parse·Execute·Fetch 지표로 병목 단계를 진단한다.

1. SQL은 결과를 선언하고 Oracle은 실행 방법을 결정한다

다음 SQL은 부서번호가 10인 사원의 사원번호와 이름을 요청합니다.

SELECT empno, ename
FROM emp
WHERE deptno = 10;

사용자는 무엇을 조회할지 선언하지만 다음 처리 방법을 직접 지정하지는 않습니다.

• EMP Table 전체를 읽을지
• Index를 사용할지
• 여러 Table을 어떤 순서로 Join할지
• NL Join, Hash Join, Sort Merge Join 중 무엇을 선택할지

Oracle Optimizer는 통계정보와 Cost를 이용하여 실행계획을 선택합니다.

사용자: 원하는 결과 집합을 SQL로 선언
Oracle: 결과를 만들 Access Path·Join 순서·Join Method를 선택하고 실행

SQL은 행을 하나씩 절차적으로 명령하는 언어가 아니라 행 집합에 수행할 연산을 선언하는 집합형 언어입니다. SQLP 튜닝에서는 반복문 관점보다 전체 집합이 어떤 Row Source 경로를 따라 처리되는지를 보는 관점이 중요합니다.

2. 두 개의 흐름을 먼저 구분한다

2.1 Oracle 내부 처리 단계

Oracle의 기본 SQL 처리 단계는 다음과 같습니다.

Parsing
  → Optimization
  → Row Source Generation
  → Execution

Statement 종류와 Cursor 재사용 여부에 따라 일부 단계는 생략될 수 있습니다.

• Soft Parse에서는 기존 실행 가능한 Code를 재사용하므로 새로운 Optimization과 Row Source Generation을 건너뜁니다.
• DDL은 항상 Hard Parse하지만, DML Subquery가 포함된 경우를 제외하면 일반적으로 Optimizer가 DDL 자체의 실행계획을 선택하지 않습니다.
• Execution은 DML 처리에서 유일하게 반드시 존재하는 내부 단계입니다.

2.2 Client 호출 생명주기

OCI, JDBC, PL/SQL, SQL*Plus 같은 Client 관점에서는 다음 호출들이 나타날 수 있습니다.

Cursor Open 또는 Prepare
→ Parse
→ Bind 입력값 설정
→ Query 출력 구조 Describe·Define
→ Execute
→ Fetch 반복
→ Cursor Close

모든 Client가 이 단계를 같은 API 이름이나 같은 횟수로 호출하는 것은 아닙니다. 예를 들어 Static SQL과 Driver의 Statement Cache를 사용하면 애플리케이션 코드에 Parse 호출이 직접 보이지 않아도 내부 Cursor 재사용이 이루어질 수 있습니다.

3. 먼저 알아둘 핵심 용어

Cursor를 실행계획 그 자체로만 이해하면 안 됩니다. 실행계획은 공유 가능한 Shared SQL Area의 중요한 일부이고, Cursor는 Session별 Private SQL Area와 연결되어 Bind 값, 현재 Fetch 위치 등의 실행 상태를 관리합니다.

4. Parse: SQL을 실행 가능한 형태로 준비한다

Application이 SQL을 실행하려면 Parse Call로 Statement를 준비합니다. Parse Call은 Cursor를 열거나 생성하고, 다음 검사를 수행합니다.

4.1 Syntax Check

SQL 문법과 Keyword 배치가 올바른지 확인합니다.

-- FROM을 FORM으로 잘못 작성
SELECT * FORM emp;

4.2 Semantic Check

SQL의 의미가 Database 구조와 일치하는지 확인합니다.

• Table과 Column이 존재하는가?
• 참조 Object와 Synonym이 올바른가?
• 데이터 타입 조합이 유효한가?
• User에게 필요한 권한이 있는가?
• Object가 실행 가능한 상태인가?

모든 오류를 Parse에서 찾을 수 있는 것은 아닙니다. Deadlock, 일부 데이터 변환 오류, Constraint 충돌처럼 실행 중에만 확인 가능한 오류도 있습니다.

4.3 Shared Pool Check

Oracle은 SQL Text의 Hash와 의미, Schema, Optimizer 환경 등의 조건을 비교하여 재사용 가능한 Child Cursor가 있는지 찾습니다. SQL Text가 같더라도 다음 차이로 공유되지 않을 수 있습니다.

• 같은 Object 이름이 다른 Schema Object를 가리킴
• Optimizer Mode와 관련 Parameter가 다름
• NLS 또는 Workarea 등 실행계획에 영향을 주는 환경이 다름
• 기존 Cursor가 DDL·Statistics 변경 등으로 Invalidated됨
• Bind Metadata 또는 기타 공유 조건이 다름

5. Soft Parse와 Hard Parse

Soft Parse도 Parse 작업이므로 완전히 무료는 아닙니다. Syntax·Semantic·환경 공유 조건을 확인하고 Library Cache를 탐색하는 비용이 남습니다.

5.1 Parse Call과 Hard Parse Count는 다르다

Parse Calls = Application이 Parse를 요청한 전체 횟수
Hard Parses = 그중 새 실행 가능한 Version 생성이 필요했던 횟수

따라서 Parse Call 100회가 곧 Hard Parse 100회를 뜻하지 않습니다. 반대로 Parse Calls가 Executions와 거의 같으면 Application이 Cursor를 매번 다시 Parse하는지 확인할 필요가 있습니다.

5.2 Bind Variable의 역할

Literal이 계속 바뀌는 SQL을 Bind Variable로 작성하면 SQL Text의 변형을 줄이고 Cursor 공유 가능성을 높일 수 있습니다.

-- Literal SQL이 계속 달라짐
SELECT empno FROM emp WHERE deptno = 10;
SELECT empno FROM emp WHERE deptno = 20;

-- Bind Variable 사용
SELECT empno FROM emp WHERE deptno = :deptno;

그러나 Bind Variable을 사용해도 Schema·Optimizer 환경·Bind Metadata 등의 공유 조건이 다르면 별도 Child Cursor가 생성될 수 있습니다.

6. Optimization: 실행계획 후보를 비교한다

재사용 가능한 Cursor가 없고 DML Query를 새로 Hard Parse해야 하면 Optimizer가 실행계획 후보를 비교합니다.

SELECT empno, ename
FROM emp
WHERE deptno = 10;

후보 1: EMP Table Full Scan 후 Filter
후보 2: EMP_DEPTNO_IDX Range Scan 후 ROWID Table Access

Optimizer는 다음과 같은 정보를 사용합니다.

• Table·Index·Partition 통계
• Cardinality와 Selectivity 추정
• Column 값 분포와 Histogram
• CPU·I/O 관련 System Statistics
• Join 순서·Join Method 후보
• Optimizer Parameter와 환경

Cost는 실제 경과시간의 예측 초 단위가 아니라 동일 Optimizer 환경에서 후보 실행계획을 비교하기 위한 상대적 추정값입니다. 실제 수행시간은 Cache 상태, 동시성, Wait Event, 실제 데이터 분포, Bind 값, Storage 상태 등에 따라 Cost 순서와 다를 수 있습니다.

7. Row Source Generation과 Row Source Tree

Optimizer가 실행계획을 선택하면 Row Source Generator는 SQL Engine이 반복 실행할 수 있는 Iterative Plan을 만듭니다.

SELECT STATEMENT
  TABLE ACCESS BY INDEX ROWID EMP
    INDEX RANGE SCAN EMP_DEPTNO_IDX

각 Operation의 역할은 다음과 같습니다.

INDEX RANGE SCAN
  → 조건에 맞는 Index Entry와 ROWID를 생산

TABLE ACCESS BY INDEX ROWID
  → 자식이 전달한 ROWID로 Table Row를 읽어 생산

SELECT STATEMENT
  → 최종 Row를 Client 요청 방향으로 반환

7.1 Pull 기반 반복 실행

Row Source Tree는 단순한 위에서 아래의 시간 순서표가 아닙니다.

부모 Row Source가 다음 Row 요청
        ↓
자식 Row Source가 데이터 접근·가공
        ↓
생산한 Row를 부모에게 반환

부모가 자식에게 다음 Row를 요청하는 Pull 방식으로 이해하면 Starts, A-Rows, 반복 Probe를 해석하기 쉬워집니다.

7.2 Streaming과 Blocking Operation

Operation마다 첫 Row를 생산하는 방식이 다릅니다.

• Index Range Scan이나 단순 Filter는 조건을 만족하는 Row를 비교적 일찍 위로 전달할 수 있습니다.
• SORT ORDER BY, 일부 Aggregate, Hash Join의 Build 단계처럼 많은 입력을 먼저 소비해야 하는 Blocking 작업은 첫 Row 반환 전 시간이 길어질 수 있습니다.

따라서 SELECT의 실제 작업을 Execute 또는 Fetch 한쪽에만 고정해서 설명하면 안 됩니다. Operation 특성과 Client Fetch 요청에 따라 작업 시점이 달라집니다.

8. Execute: Cursor 실행을 시작한다

8.1 SELECT의 Execute

SELECT의 Execute는 Cursor 실행을 시작하고 Result Set을 준비합니다. SQL Trace 관점에서는 SELECT Execute가 대상 Row를 식별하는 작업을 포함할 수 있고, 실제 Row Source 처리는 이후 Fetch Call에서도 계속됩니다.

Execute
  → 첫 Fetch 요청
  → Row Source가 Row 생산
  → Client에 Row 묶음 반환
  → 다음 Fetch 요청 반복

ORDER BY처럼 Blocking 작업이 있으면 첫 Fetch가 완료되기 전 많은 읽기와 Sort가 발생할 수 있습니다. 반대로 Client가 일부 Row만 읽고 Cursor를 닫으면 전체 Row Source를 끝까지 수행하지 않을 수 있습니다.

8.2 DML의 Execute

INSERT, UPDATE, DELETE, MERGE는 실제 변경 작업이 주로 Execute 단계에 집중됩니다.

UPDATE emp
SET sal = sal * 1.1
WHERE deptno = 10;

Execute에서 발생할 수 있는 주요 작업은 다음과 같습니다.

• 변경 대상 Row 탐색
• Row·Table Lock 획득
• Table Row 변경
• Undo·Redo 생성
• 관련 Index Entry 유지
• Constraint와 Trigger 처리

일반적인 DML은 Query Result Set을 반환하지 않으므로 SELECT와 같은 반복 Fetch가 없습니다. 다만 RETURNING 등 별도 반환 기능은 일반적인 Query Fetch와 구분해서 봐야 합니다.

9. Fetch: Query 결과를 묶음으로 가져온다

Client는 Fetch Call을 반복하여 Result Set에서 Row를 가져옵니다. 한 Fetch Call에서 가져오는 행 수는 Driver와 Application의 Array Fetch·Fetch Size 설정에 영향을 받습니다.

9.1 단순 계산 예시

1,000행을 모두 가져오고 한 번의 Fetch에서 정확히 설정 크기만큼 반환된다고 단순화하면 다음과 같습니다.

Fetch Size 100 → 약 10회의 Fetch
Fetch Size 10  → 약 100회의 Fetch

실제 Fetch 횟수는 Driver 구현, 마지막 End-of-Fetch 확인 Call, LOB 처리, Prefetch 방식 등에 따라 단순 나눗셈과 조금 다를 수 있습니다.

9.2 Fetch Size의 Trade-off

Fetch Size가 너무 작으면 다음 비용이 증가할 수 있습니다.

• Fetch Call 수
• Network Round Trip
• Client·Server 경계의 반복 처리

Fetch Size가 너무 크면 다음 비용이 증가할 수 있습니다.

• Client Memory
• 사용하지 않을 Row의 선반입
• 첫 화면에 소수 Row만 필요한 Application의 불필요한 데이터 전송

Application이 20행만 필요하다면 Fetch Size를 키우기 전에 SQL 자체가 20행만 반환하도록 Top-N·Pagination과 업무 조건을 검토하는 것이 우선입니다.

9.3 부분 Fetch

Client가 결과 일부만 읽고 Cursor를 닫거나 다시 실행하면 다음 현상이 나타날 수 있습니다.

• EXECUTIONS는 증가함
• FETCHES도 일부 증가함
• Result Set을 끝까지 읽지 않았으므로 END_OF_FETCH_COUNT는 증가하지 않음

END_OF_FETCH_COUNT는 Cursor가 완전히 Fetch된 횟수이며 EXECUTIONS보다 작거나 같습니다. 이 값의 차이는 오류뿐 아니라 첫 페이지만 읽는 정상 Application 패턴에서도 발생할 수 있습니다.

10. SELECT·DML·DDL 처리 차이

11. SQL Trace와 Dynamic Performance View로 단계별 진단

11.1 SQL Trace·TKPROF

SQL Trace는 Statement별로 Parse, Execute, Fetch Count와 CPU·Elapsed·Logical I/O·Physical I/O·Rows를 제공합니다.

call      count    cpu    elapsed    disk    query    current    rows
Parse        ...
Execute      ...
Fetch        ...

해석의 기본은 다음과 같습니다.

• Parse count가 많음: 불필요한 재Parse와 Cursor 재사용 여부 확인
• Execute count가 많음: Row-by-Row 반복 실행인지 확인
• Fetch count가 반환 Row에 비해 많음: Array Fetch Size와 Client 처리 방식 확인
• SELECT의 I/O가 Fetch에 집중됨: Fetch 과정에서 Row Source 작업이 진행된 정상적인 형태일 수 있음
• DML의 current와 rows가 큼: 변경량과 Index·Constraint 유지비용 확인

11.2 V$SQLSTATS·V$SQLAREA

대표적으로 다음 Column을 연결해 봅니다.

예를 들어 PARSE_CALLS가 EXECUTIONS와 거의 같다면 Application이 실행마다 Parse하는지 확인합니다. EXECUTIONS보다 END_OF_FETCH_COUNT가 훨씬 작다면 오류, 조기 종료, Pagination, 일부 Row만 사용하는 Application 패턴을 구분해야 합니다.

누계값은 다른 Session과 과거 실행을 포함할 수 있으므로 가능하면 테스트 전후 Delta, Child Cursor 단위, SQL Trace 또는 SQL Monitor와 함께 확인합니다.

12. 처리 단계와 성능 문제 연결

13. 진단 절차

13.1 SQL과 실행 단위를 먼저 고정한다

• SQL ID와 Child Number를 확인합니다.
• 같은 SQL Text 아래 Child Cursor가 여러 개인지 확인합니다.
• Bind 값과 Optimizer 환경을 기록합니다.

13.2 Parse 문제를 분리한다

• PARSE_CALLS, EXECUTIONS, VERSION_COUNT, Invalidations를 확인합니다.
• Literal SQL 변형과 Bind 사용 여부를 확인합니다.
• 동일 Text가 다른 Schema Object를 가리키거나 환경 차이가 있는지 확인합니다.

13.3 Runtime Row Source를 확인한다

• 예상 Plan만 보지 않고 실제 Cursor Plan과 Runtime 통계를 확인합니다.
• A-Rows, Starts, Buffers, Physical Read, Temp 사용량을 Operation별로 봅니다.
• Cost와 실제 처리량이 다른 지점을 찾습니다.

13.4 Execute와 Fetch를 분리한다

• SQL Trace에서 Parse·Execute·Fetch Count와 시간을 비교합니다.
• SELECT는 Fetch Count와 반환 Row, End-of-Fetch를 함께 봅니다.
• DML은 Execute Row, Current Gets, Redo·Undo, Lock 대기를 봅니다.

13.5 Client 사용량을 확인한다

• Application이 Result Set 전체를 실제로 사용하는지 확인합니다.
• Fetch Size와 Network Round Trip을 측정합니다.
• 필요 없는 Row와 Column을 SQL에서 먼저 제거합니다.

14. 자주 하는 오해

• “SQL 처리 단계는 Parse·Optimize·Execute·Fetch 네 단계다.”
  Oracle 내부 공식 단계에는 Row Source Generation이 포함되며 Fetch는 Client Query 호출 생명주기의 단계입니다.

• “Soft Parse는 아무 작업도 하지 않는다.”
  기존 Code를 재사용하지만 Parse Call과 공유 조건 확인 비용은 남습니다.

• “같은 SQL Text면 반드시 같은 Cursor를 공유한다.”
  Schema와 Optimizer 환경, Bind Metadata, Invalidations 등의 차이로 별도 Child Cursor가 생성될 수 있습니다.

• “SELECT의 모든 I/O는 Execute에서 발생한다.”
  많은 Row Source 작업이 Fetch 중에 수행될 수 있습니다.

• “Fetch Size를 크게 하면 SQL 자체가 읽는 데이터가 줄어든다.”
  Fetch Size는 Call과 전송 묶음을 조절할 뿐, SQL의 Access Path와 총 처리 Row를 자동으로 줄이지 않습니다.

• “Cost가 가장 낮은 Plan은 실제 시간도 항상 가장 짧다.”
  Cost는 통계와 모델에 기반한 후보 비교값이며 Runtime 통계와 Wait를 별도로 검증해야 합니다.

• “Executions와 End of Fetch가 다르면 항상 오류다.”
  일부 Row만 읽고 닫는 Application에서는 정상적으로 차이가 날 수 있습니다.

15. 핵심 정리

Oracle 내부 처리
Parse
  → Syntax·Semantic·Shared Pool 확인
  → Hard Parse이면 Optimize와 Row Source Generation
  → Execute에서 Row Source 실행

Client Query 생명주기
Parse/Prepare
  → Bind·Define
  → Execute
  → Fetch 반복
  → Close

SQLP 학습에서는 다음 연결을 기억해야 합니다.

Cursor 공유 실패
→ Hard Parse 증가
→ Optimize·Row Source Generation 반복

잘못된 실행계획 또는 추정
→ Runtime A-Rows·Buffers 증가

작은 Fetch Size 또는 Row-by-Row Fetch
→ Fetch Calls·Network Round Trip 증가

부분 Result 사용
→ Executions > END_OF_FETCH_COUNT 가능

성능 분석은 “어느 단계가 느린가”를 막연히 추측하는 것이 아니라, Parse·Execute·Fetch Call 수와 Row Source별 실제 처리량을 함께 측정하는 과정입니다.