[Oracle] SQL 수행 구조

SGA

• Shared Pool: 라이브러리 캐시(library cache) + 딕셔너리 캐시(dictionary cache)
  • shared pool은 SQL문을 처리하고 커서(실행 계획)를 공유하는데 사용된다.
  • 공유 SQL 영역: 수행된 SQL의 파싱 및 실행계획 정보를 저장
  • 공유 PL/SQL 영역: 수행된 PL/SQL의 파싱 및 실행계획 정보를 저장
• DB Buffer Cache
  • Disk I/O의 영향도를 줄이기 위해 Data File의 Data Block을 메모리에 적재
• Redo Log Buffer
  • DML 발생 시 정보를 가지며 Redo log file에 Write 전에 Memory에 먼저 Write 되는 영역
• Large Pool
  • Parallel Query 메시징 or 대용량의 메모리 할당이 필요한 경우 사용

👇아래글을 읽고 오면 도움이 됩니다.

[Oracle] Oracle Architecture(1)

 

---

SQL 처리 과정

👇아래글을 읽고 오면 도움이 됩니다.

[Oracle] Oracle Architecture(3) [SQL 처리 과정]

 

 

SQL 전달 및 네트워크 계층 구조 (Oracle Net Architecture)

👇사용자가 실행한 SQL이 데이터베이스 파일에 도달하기까지는 다음과 같은 계층적 흐름을 거칩니다.

 

 

1. 사용자 / 애플리케이션 (User Side)

• 역할: SQL 문장을 작성하고 실행 요청을 보냅니다. (SQL Developer, Java App 등)

2. Oracle Net Services (API 계층)

• 역할: 애플리케이션과 네트워크 간의 통로 역할을 합니다.
• 실무: 사용자의 SQL을 네트워크 패킷으로 변환하기 위한 준비를 합니다.

3. Transparent Network Substrate (TNS 계층)

• 역할: **"네트워크 하드웨어 독립성"**을 제공합니다.
• 핵심: 클라이언트가 서버의 IP나 포트를 일일이 몰라도, tnsnames.ora에 설정된 **서비스 이름(Alias)**만으로 접속할 수 있게 해주는 마법 같은 계층입니다.

4. Oracle Net Services (Network Protocol 계층)

• 역할: 실제 물리적인 통신 규약인 TCP/IP 등을 통해 패킷을 서버로 실어 나릅니다.

5. Oracle Net Listener (수문장)

• 역할: 서버 입구에서 대기하다가 접속 요청이 오면 이를 가로채서 Instance로 연결해 줍니다.
• 중요: 리스너가 떠 있지 않으면 아무리 TNS 설정이 잘 되어 있어도 접속이 불가능합니다.

6. Instance (SGA & Background Processes)

• 역할: 전달받은 SQL을 Parsing(분석)하고 메모리(SGA)에서 데이터를 찾습니다.
• 구성: Shared Pool(파싱), Buffer Cache(데이터 로드) 등의 메모리와 DBWR, LGWR 같은 프로세스가 작동합니다.

7. Database (Physical Files)

• 역할: 실제 데이터가 영구 저장된 물리적 공간입니다.
• 구성: Data Files, Redo Log Files, Control Files 등 디스크 상의 파일들입니다.

1. 요청 및 분석 (Parsing & Optimization)

• 유저의 SQL 요청을 서버 프로세스가 받아 SQL 엔진으로 전달합니다.
• Shared Pool > Library Cache를 뒤져 이전에 실행된 적이 있는지 확인합니다.
  • Soft Parse: 기존 실행 계획이 있다면 옵티마이저를 생략하고 즉시 실행합니다.
  • Hard Parse: 처음 보는 SQL이라면 옵티마이저가 최적의 실행 계획을 새로 수립합니다.
• 로우 소스 생성기가 이 계획을 바탕으로 실제 실행 가능한 '작업 지시서(Row Source Tree)'를 만들어 서버 프로세스에게 넘깁니다.

2. 인덱스 탐색 (Index Vertical Scan)

• 서버 프로세스가 인덱스의 루트 → 브랜치 → 리프 블록 순으로 내려가는 수직적 탐색을 시작합니다.
• 각 단계마다 블록이 Buffer Cache(메모리)에 있는지 확인하고, 없으면 디스크에서 메모리로 올린 후 읽습니다.
• 최종 목적지인 리프 블록에서 우리가 찾는 값의 실제 주소인 ROWID를 추출합니다.

3. 테이블 블록 위치 계산 (Hashing & Latching)

• 서버 프로세스는 얻어온 ROWID를 해시 함수에 넣어 데이터가 저장된 메모리 구역(Hash Bucket)을 찾아냅니다.
• 해당 구역의 해시 체인을 따라가며 내가 찾는 블록이 현재 메모리에 떠 있는지 확인합니다. (이때 데이터 무결성을 위해 cbc latch를 획득합니다.)

4. 데이터 추출 및 반환 (Execution & Fetch)

• 테이블 액세스
  • 메모리에 블록이 있다면 즉시 읽습니다. (Logical Read)
  • 없다면 디스크에서 해당 블록을 찾아 메모리에 적재한 후 읽습니다. (Physical Read)
• 블록 내에서 유저가 요청한 특정 컬럼만 뽑아내어 가공(정렬 등)합니다.
• 최종 결과 집합을 유저에게 반환하며 마무리 합니다.

 

SQL 4단계 간략 분석

1. Parsing(문법 분석 및 최적화)
   
   •    Syntax & Semantic Check(구문 및 의미 분석)
   •    테이블 유무 확인 및 접근 권한 확인
   • 라이브러리 캐시에서 같은 SQL 문장이 존재하는지 탐색
     1. 라이브러리 캐시에 동일한 SQL 존재(Cache Hit)하면 파싱 없이 수행 - (Soft Parsing)
     2. 라이브러리 캐시에 동일한 SQL 없으면(Cache Miss) 파싱 작업부터 다시 수행 - (Hard Parsing)
2. Optimizer(옵티마이저 - 최적화)
   • 파싱 결과를 활용하여 최적의 실행 계획을 수립
     1. 라이브러리 캐시에 있으면 논리 I/O 중심으로 실행계획 수립
     2. 라이브러리 캐시에 없으면 논리I/O + 물리I/O 방식 채택
3. Row Source Generation(로우소스 생성)
   •    옵티마이저가 정한 실행 계획을 내부적인 수행 방법 상세화(컴퓨터가 실제로 실행할 수 있는 코드로 변환)
   •    테이블 엑세스 / 조인 / 정렬 등의 다양한 로우소스 제공
   •    실행계획에 해당되는 Tree 구조의 실행계획 생성
4. Execution(실행)
   • 서버 프로세스가 메모리를 돌며 버퍼 캐시/디스크에서 데이터를 추출
   • 로우소스 트리의 최상단(root)부터 데이터를 요청하여 하위 노드(leaf block)가 데이터를 찾아 전달

 

---

데이터베이스 I/O 메커니즘

 

 

사용자로 부터 SQL 요청하면 DB Engine(CPU)은 파싱 단계에서 Disk에게 결과를 요청하는데 까지 속도는 빠름 문제는 Disk는 블록 단위로 읽고 블록을 읽는 속도가 느려 이 부분을 손을 많이 댐 

 

block 단위 I/O

• 하나의 레코드를 읽더라도 레코드가 속한 전체 블록을 읽음
• 가장 중요한 성능지표는 엑세스하는 블록 개수
• 옵티마이저의 판단에 가장 큰 영향을 미치는 것도 엑세스해야 할 블록 개수
• 적용 예시
  • 데이터 파일에서 DB 버퍼캐시로 블록단위로 적재함
  • 데이터 파일에서 블록을 직접 읽고 쓸 때
  • 버퍼 캐시에서 블록을 읽고 쓸 때
  • 버퍼 캐시에서 변경된 블록을 다시 데이터 파일에 쓸 때

 

데이터 접근 방식

• 순차 엑세스(Sequential Access) : 데이터가 저장된 순서대로 시작점을 잡고 쭉 읽어 나가는 방식(인덱스 스캔에서 많이 사용)
  • 처음부터 끝까지 돌려보는 것과 같으며, 테이블 전체 스캔(Full Table Scan)을 할 때 주로 사용
• 랜덤 엑세스(Random Access): 특정 위치의 데이터를 직접 한 블록씩 접근하여 읽거나 쓰는 방식(테이블에서 사용)
  • 인덱스를 통해 특정 행(Row) 하나를 찾아갈 때(Index Seek) 사용

구분	시퀀셜 액세스(Sequential)	랜덤 액세스(Random)
핵심 도구	Full Table Scan	Index Range Scan
I/O 단위	Multiblock I/O(한 번에 여러 개)	Single Block I/O(한 번에 한 개)
디스크 암	거의 고정(안정적)	계속 이동(바쁨)
효율성	대량 데이터 처리 시 유리	소량 데이터 정밀 타격 시 유리
성능 병목	디스크 대역폭(전송 속도)	디스크 탐색 시간(Seek Time)

랜덤 I/O를 최소화하고 순차 I/O를 늘리는게 목표!

인덱스가 무조건 좋다는 편견을 버려야 합니다. 
읽어야 할 데이터가 전체의 10~20%가 넘는다면, 차라리 인덱스를 버리고 Full Table Scan(순차 I/O)을 선택하는 것이 훨씬 빠르고, 데이터양을 더 많이 전달 할 수 있습니다.

 

 

데이터 적재 방식

• Single Block I/O: 한 번의 I/O 호출로 딱 1개의 블록만 읽어옵니다.
  • 주로 랜덤 엑세스(Random Access)와 짝을 이룹니다.
  • 인덱스를 타고 가서 하나의 블록만 조회할 때 발생합니다. 
  • 인덱스 스캔 시 자주 발생합니다.
• Multi Block I/O: 한 번의 I/O 호출로 여러 개의 블록을 한꺼번에 읽어옵니다.
  • 주로 순서 엑세스(Sequential Access)와 짝을 이룹니다.
  • 인접한 블록들을 같이 읽을때 유리합니다.
  • 대용량 데이터를 읽을때 유리합니다.

 

(중요!)논리적 I/O와 물리적 I/O

 

• 논리적 I/O: DBMS가 처리하는 모든 작업에서 발생하는 I/O(메모리 I/O + 디스크 I/O)
  • 하지만 Direct Path로 Access되는 경우는 메모리 I/O를 사용하지 않음
  • 즉, 메모리 I/O + Direct Path I/O
• 물리적 I/O: 디스크에서 발생한 총 I/O
  • 버퍼캐시에서 찾지 못한 경우에만 사용합니다.
  • 논리적 I/O 중 일부에 포함
  • 디스크에서 데이터를 읽어와서 성능저하의 원인이 됩니다.

 

I/O 효율화

• 필요한 최소 블록만 읽도록 SQL 작성
• 최적의 옵티마이저 팩터 제공(DB설계 단계)
  • 주기적인 통계정보 수집
• 필요 시 옵티마이저 힌트를 사용해 최적의 엑세스 경로로 유도

---

💡마무리하며 생각해보기

1. Table Full Scan이 Index Range Scan 보다 효율이 나쁠까?

👇정답

 

• 답변: 아니요, 항상 그렇지는 않습니다.
• 설명
  • Index Range Scan은 한 줄씩 랜덤 액세스를 하며 Single Block I/O를 합니다. 읽어야 할 양이 많아지면 택배 기사님이 수만 번 왕복하는 꼴이 됩니다.
  • Table Full Scan은 순차 액세스를 하며 Multi Block I/O를 합니다. 한 번에 트럭으로 왕창 실어 나르기 때문에, 전체 데이터의 약 10~20% 이상을 읽어야 한다면 Full Scan이 훨씬 효율적입니다.

 

 

2. Table Full Scan이 성능을 저해 할까?

👇정답

 

• 답변: 상황에 따라 다릅니다.
• 설명
  • 작은 테이블이나, 대용량 데이터를 집계(Batch)할 때는 Full Scan이 가장 빠릅니다.
  • 하지만 수억 건의 데이터가 있는 테이블에서 단 몇 건을 찾는데 Full Scan을 돌린다면, 불필요한 디스크 I/O가 폭발하며 시스템 전체의 CPU와 I/O 자원을 고갈시켜 성능을 심각하게 저해하게 됩니다.

 

 

3. Table Full Scan이 Disk Block을 많이 읽을까?

👇정답

 

• 답변: 네, 물리적인 '블록 수' 자체는 가장 많이 읽습니다.
• 설명
  •  Full Scan은 말 그대로 고수위 마크(HWM, High Water Mark) 아래의 모든 블록을 다 읽어 들입니다.
  • 하지만 중요한 건 속도입니다. 1,000개를 한 개씩 1,000번 읽는 것(Index)보다, 1,000개를 한꺼번에 10번 만에 읽는 것(Full Scan)이 시간상으로는 더 빠를 수 있습니다.

고수위 마크란?

• 오라클 세그먼트(테이블 등)에 데이터가 들어오면 블록을 하나씩 사용하게 됩니다. 이때 "현재 이 테이블에서 데이터를 써본 적이 있는 마지막 블록의 위치"를 표시해둔 이정표가 바로 HWM입니다.
• High Water Mark(HWM): 테이블에 데이터가 한 번이라도 저장되었다면 최상단 블록의 위치를 가르키는 이정표 입니다.
  • 100만건의 데이터를 INSERT 한 후 DELETE가 되더라도 HWM은 여전히 100만 번째 블록에 머물러 있습니다.
  • TRUNCATE, MOVE, SHRINK를 통해 빈 공간을 회수하여 마크를 위치를 바꿔주어야 합니다.

 

 

 

 

4. Index를 타게 하면 성능이 반드시 향상 될까?

👇 정답

• 답변: 절대 아닙니다.
• 설명
  • 성별처럼 데이터 종류가 적고(카디널리티가 낮고) 선택도가 나쁜 컬럼에 인덱스를 사용하면, 오라클은 방대한 양의 '랜덤 액세스'를 수행하게 됩니다. 이때 발생하는 물리적 I/O 비용이 테이블을 한 번에 다 읽는 Full Scan 비용을 압도적으로 초과하기 때문에 성능이 심각하게 저하되는 것입니다.

 

 

 

5. Index를 타게 하면 Disk Block을 적게 읽을까?

👇정답

• 답변: 대체로 그렇지만, '랜덤 액세스'의 함정이 있습니다.
• 설명: 필요한 행이 소량일 때는 인덱스 블록 몇 개 + 데이터 블록 몇 개만 읽으면 되므로 매우 적게 읽습니다.
  • 하지만 읽어야 할 행이 많아지면, 동일한 데이터 블록을 여러 번 중복해서 읽는 현상이 발생하여 실제로는 Full Scan보다 더 많은 블록 I/O를 유발할 수도 있습니다.

 

 

 

 

 

 

 

참조: 멋쟁이사자처럼 - 한 번에 합격라는 SQLP 과정