SQL 실행원리 와 과정

Oracle 19c에서 SQL이 실행될 때 내부적으로 어떤 단계들을 거치는지 초보자도 이해할 수 있도록 쉬운 말로, 단계별로 하나씩 설명드릴게요.

⸻

Oracle SQL 실행 단계

• 단계별 용어 설명

용어	설명
SQL	Structured Query Language. 오라클 DB에 명령을 내리는 언어. 예: SELECT, INSERT, UPDATE, DELETE
파싱 (Parsing)	SQL 문을 분석하여 문법 검사 및 필요한 객체(테이블, 컬럼 등) 확인. 실행 계획 재사용 여부도 판단
하드 파싱 (Hard Parse)	이전에 실행된 적 없는 새로운 SQL일 때, 처음부터 실행 계획을 다시 만드는 작업. 성능 부담이 큼
소프트 파싱 (Soft Parse)	같은 SQL 문이 이미 실행된 적이 있을 때, 기존 실행 계획을 재사용함. 속도가 빠름
바인드 변수 (Bind Variable)	SQL 안에서 값 대신 사용하는 변수. 예: `:id`. 성능과 보안에 좋음
옵티마이저 (Optimizer)	SQL을 가장 빠르고 효율적으로 실행하기 위해 최적의 실행계획을 만드는 엔진
실행 계획 (Execution Plan)	옵티마이저가 만든 SQL 실행 방법의 설계도. 어떤 인덱스를 쓸지, 어떤 순서로 처리할지 결정
커서 (Cursor)	SQL 실행 결과를 저장하고, 그 결과를 한 행씩 처리하기 위한 메모리 영역
페칭 (Fetching)	SELECT 결과를 한 행씩 가져오는 작업
DML	Data Manipulation Language. 데이터를 다루는 명령어: INSERT, UPDATE, DELETE, SELECT 등
DDL	Data Definition Language. 데이터 구조를 정의하는 명령어: CREATE, ALTER, DROP 등

SQL 실행시 오라클 처리 절차

1. 사용자가 SQL 문을 보냄

   • 사용자가 SELECT * FROM employees; 같은 SQL 문을 작성해서 Oracle DB에 보냅니다.

2. 파싱(Parsing)

   • 오라클이 이 SQL 문장을 문법적으로 맞는지 확인하고,

   • 어떤 테이블, 컬럼, 권한을 쓰는지 체크합니다.

   • 만약 같은 SQL 문이 이전에 실행된 적이 있다면, 기존 결과(실행계획)를 재사용할 수 있습니다. (이를 Soft Parse라고 함)

   • 처음 실행이거나 다른 SQL이면 새로운 계획을 짭니다. (이를 Hard Parse라고 함)

3. 바인딩(Bind)

   • SQL 문 안에 변수가 있다면 (WHERE id = :1 같은), 여기에 실제 값을 넣습니다.

   • 주로 애플리케이션에서 사용하는 기능입니다.

4. 최적화(Optimization)

   • SQL이 효율적으로 실행되도록 가장 빠른 실행 방법(실행계획, Execution Plan)을 계산합니다.

   • 예: 인덱스를 쓸까? 테이블 전체를 읽을까?

5. 실행(Execution)

   • 실제로 데이터를 읽거나 쓰는 작업이 시작됩니다.

   • 예: 테이블에서 필요한 행을 찾아서 읽습니다.

6. 결과 반환(Fetching)

   • 실행된 결과를 한 줄씩 사용자에게 돌려줍니다.

   • 예: SELECT면 결과 행을 돌려주고,

   • INSERT, UPDATE, DELETE면 몇 개 행이 영향을 받았는지 알려줍니다.

⸻

• 실행 흐름 요약

- 단계 설명

단계	설명
1. SQL 전송	사용자가 SQL 문을 Oracle DB에 보냄
2. 파싱 (Parsing)	SQL 문법 검사, 필요한 테이블/컬럼/권한 확인. 이전 실행 계획이 있으면 재사용 (Soft Parse), 없으면 새로 생성 (Hard Parse)
3. 바인딩 (Binding)	변수(SQL 바인드 변수)에 실제 값을 연결. 주로 애플리케이션에서 사용
4. 최적화 (Optimization)	가장 효율적인 실행 방법(실행계획)을 계산. 인덱스 사용 여부 등 결정
5. 실행 (Execution)	실제 데이터 조회 또는 변경 작업 수행
6. 결과 반환 (Fetching)	결과를 사용자에게 반환. SELECT는 결과 행 반환, DML(INSERT, UPDATE, DELETE)은 처리된 행 수 반환

⸻

1. EXPLAIN PLAN이란?

• SQL 문이 어떻게 실행될 예정인지 보여주는 계획표입니다.
• 인덱스를 쓸지, 테이블을 어떻게 읽을지 등을 확인할 수 있어 성능 튜닝에 매우 중요합니다.
• 실행 결과를 알려주는 건 아니고, 실행 “전”에 Oracle이 세운 실행 전략을 보여줍니다.

⸻

2. 사용 방법

1단계: PLAN_TABLE 준비 (보통 기본 제공됨)

@?/rdbms/admin/utlxplan.sql

• 한 번만 실행하면 됩니다. PLAN_TABLE이라는 테이블이 생성됩니다.

⸻

2단계: 실행 계획 수집

EXPLAIN PLAN FOR
SELECT * FROM employees WHERE department_id = 10;

⸻

3단계: 실행 계획 보기

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

⸻

3. 예제

EXPLAIN PLAN FOR
SELECT first_name FROM employees WHERE employee_id = 100;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

⸻

4.요약

단계	설명	예제
1. PLAN_TABLE 생성	실행 계획을 저장할 테이블 생성 (한 번만 실행하면 됨)	@?/rdbms/admin/utlxplan.sql
2. EXPLAIN PLAN 실행	특정 SQL 문에 대한 실행 계획을 저장	EXPLAIN PLAN FOR SELECT * FROM employees;
3. 실행 계획 조회	저장된 실행 계획을 확인	SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

⸻

실행 계획을 해석하는 법

• 실행 계획(Execution Plan) 해석 - 주요 용어와 의미

실행 방식	설명	성능 특징
TABLE ACCESS FULL	테이블 전체를 처음부터 끝까지 읽음	느릴 수 있음 (행이 많을수록 비효율적)
TABLE ACCESS BY INDEX ROWID	인덱스로 찾은 후, 해당 행을 테이블에서 읽음	보통 빠름 (인덱스를 쓰기 때문에)
INDEX UNIQUE SCAN	인덱스에서 정확히 하나의 값을 빠르게 찾음 (PK 또는 Unique Index)	매우 빠름
INDEX RANGE SCAN	인덱스에서 범위 조건(>, <, BETWEEN 등)으로 여러 값을 검색	빠름
INDEX FULL SCAN	전체 인덱스를 처음부터 끝까지 읽음 (WHERE 절 없이 인덱스 컬럼만 SELECT할 때 등)	테이블보다 빠를 수 있음
NESTED LOOPS	작은 테이블 기준으로 반복해서 다른 테이블을 조회	소량의 데이터에 적합
HASH JOIN	두 테이블을 해시 테이블로 결합하여 조인	대량의 데이터 조인에 적합
MERGE JOIN	정렬된 두 테이블을 병합하면서 조인	정렬된 데이터 조인 시 효율적

⸻

해석 예시

EXPLAIN PLAN FOR
SELECT first_name FROM employees WHERE employee_id = 100;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

결과 예:

| Id | Operation                   | Name       |
|----|-----------------------------|------------|
|  0 | SELECT STATEMENT            |            |
|  1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES  |
|  2 |   INDEX UNIQUE SCAN         | EMP_EMP_ID_PK |

해석: • INDEX UNIQUE SCAN: PK 인덱스를 사용해 employee_id = 100을 빠르게 찾음 • TABLE ACCESS BY INDEX ROWID: 인덱스로 찾은 위치를 바탕으로 테이블에서 실제 데이터를 읽음

이런 식으로 계획을 보면 인덱스를 잘 사용하고 있는지, 전체 테이블을 읽는 불필요한 작업은 없는지 판단할 수 있어요.

⸻