[DataBase - Oracle] 오라클 아키텍처 (내부 동작 및 데이터 구조 등)

 

리두로그버퍼(Redo Log Buffer)

• 오라클은 갑작스러운 장애 발생시 백업된 데이터파일에 리두로그버퍼를 합쳐서 데이터를 복구한다.
• 리두로그버퍼에서 리두로그파일로 저장되는 시점

---

DB버퍼캐시

 

 - 버퍼상태

• Pinnded 버퍼 : 현재 사용중인 버퍼, 변경을 했지만 아직 COMMIT은 되지 않은 상태
• Dirty 버퍼 : COMMIT을 했지만 아직 데이터파일로 저장은 되지 않은 상태
  • 리두로그파일에는 저장되었지만 데이터파일에는 저장안된 상태
• Free 버퍼 : 사용가능한 버퍼, 데이터가 데이터파일로 저장되었거나 사용되지 않은 상태\

 - DB버퍼캐시의 데이터가 데이터파일로 저장되는 경우

• Checkpoint 신호가 발생했을 때
• DDL명령이 실행될 때

---

Shared Pool

 

 - 라이브러리캐시

• 파싱된 SQL문과 실행계획을 매핑하여 저장
• 캐시 공간이 부족하면 버려졌다가 다시 하드파싱 후 저장된다.

 - 딕셔너리캐시

• 오라클의 모든 객체(테이블, 뷰, 인덱스, 시퀀스 등)에 대한 정보들을 저장
• 테이블 데이터를 찾을 때 그 시작점이 된다.

---

LGWR(Log Writer) Background Process

 

 - LGWR Background Process는 Log Buffer에 있는 내용을 Log File에 기록하는 일을 한다.

 - 아래와 같은 시점에서 작업이 진행된다.

• Commit 되었을 때
• Log Buffer가 1/3 찼을 때
• Log Buffer가 1MB 사용했을 때
• timeout

 

---

 

INSERT문 실행시 내부동작

 

1. SQL문 실행

서버프로세스

• 구문분석
  • SQL문의 문법을 확인 (키워드 검사)
  • 테이블명, 칼럼명, 데이터 타입 확인 (의미 검사)
  • SQL문을 실행한 계정의 권한 확인 (권한 검사)
• Shared Pool의 라이브러리캐시에 해당 쿼리 저장 (쿼리 재사용을 위해)
• 입력할 데이터를 리두로그버퍼와 DB버퍼캐시에 기록

2. COMMIT 실행

---

백그라운드 프로세스

LGWR (Log writer)

• 리두로그버퍼 데이터 -> 리두로그파일로 저장
• DB버퍼캐시 상태 변경 (Pinned --> Dirty)
• Checkpointer가 DBWR에게 데이터 저장 신호 전달
• DBWR가 DB버퍼캐시 데이터 -> 데이터파일로 저장

---

SELECT문 실행시 내부동작

서버프로세스

• 구문분석
  • SQL문의 문법을 확인 (키워드 검사)
  • 테이블명, 칼럼명, 데이터 타입 확인 (의미 검사)
  • SQL문을 실행한 계정의 권한 확인 (권한 검사)
  • 라이브러리캐시에 SQL 존재하는지 확인
    • 있으면 곧바로 실행 (소프트 파싱)
      • bind변수만 달라졌을 경우 소프트 파싱
    • 없으면 옵티마이저가 최적화 과정을 거쳐 실행계획 생성 (하드 파싱)
      • 테이블, 인덱스, 칼럼에 대한 통계정보 존재하는지 확인
      • 인덱스가 존재한다면 어떤 인덱스가 최적인지
      • CPU를 많이 소모하는 작업
• Shared Pool의 라이브러리 캐시에 쿼리와 실행계획 저장 (쿼리 재사용을 위해서)
• DB버퍼캐시에 해당 테이블의 데이터 있는지 확인
  • 있으면 캐시에서 데이터 접근 (메모리 I/O)
  • 없으면 디스크에 접근해서 해당 테이블 데이터를 DB버퍼캐시로 복사 (디스크 I/O)
• DB버퍼캐시의 데이터 중 원하는 결과만 추출하여 유저프로세스로 전송 (블록 단위)

---

SELECT문 논리적 실행순서

• FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY

---

UPDATE문 실행시 내부동작

 

1. SQL문 실행

서버프로세스

• 구문분석
  • SQL문의 문법을 확인 (키워드 검사)
  • 테이블명, 칼럼명, 데이터 타입 확인 (의미 검사)
  • SQL문을 실행한 계정의 권한 확인 (권한 검사)
• Shared Pool의 라이브러리캐시에 해당 쿼리 저장 (쿼리 재사용을 위해)
• DB버퍼캐시에 해당 테이블의 데이터 있는지 확인
  • 없으면 디스크에 접근해서 해당 테이블 데이터를 DB버퍼캐시로 복사
  • DB버퍼캐시에 변경 조건에 맞게 데이터 수정
• 입력할 데이터를 리두로그버퍼와 DB버퍼캐시에 기록

2. COMMIT 실행

---

백그라운드 프로세스

• 리두로그버퍼 데이터 -> 리두로그파일로 저장
• DB버퍼캐시 상태 변경 (Pinned --> Dirty)
• Checkpointer가 DBWR에게 데이터 저장 신호 전달
• DBWR가 DB버퍼캐시 데이터 -> 데이터파일로 저장

---

데이터베이스의 저장구조

 

 

데이터베이스는 다음과 같은 구조로 데이터를 저장한다

• 테이블 스페이스 : 세그먼트를 담는 콘테이너
• 세그먼트 : 데이터 저장공간이 필요한 오브젝트 (테이블, 인덱스, 파티션 등)
• 익스텐트 : 공간을 확장하는 단위, 연속된 블록 집합
• 블록 : 데이터를 읽고 쓰는 단위
• 데이터 파일 : 디스크 상의 물리적인 OS 파일

---

데이터 액세스 방법

 

테이블 또는 인덱스 블록을 읽는 방식으로는 시퀸셜 액세스와 랜덤 액세스가 존재한다.

• 시퀸셜 액세스 : 논리적 혹은 물리적으로 블록들이 연결된 순서에 따라 차례대로 블록을 읽는 방식이다.
• 랜덤 액세스 : 레코드 하나를 읽기 위해 한 블록씩 접근하는 방식이다. 인덱스를 통해 데이터를 읽을 때 사용되는 방식이다.

이와 관련되어 데이터를 스캔하는 방식은 다음과 같다.

• Table Full Scan : 테이블에 속한 블록 전체를 읽어서 사용자가 원하는 데이터를 찾는 방식이다. 시퀸셜 액세스를 통해 읽는다.
• Index Range Scan : 인덱스에스 일정량을 스캔하면서 얻은 ROWID로 테이블 레코드를 찾아가는 방식이다. 랜덤 액세스를 통해 읽는다.

---

논리적 I/O 와 물리적 I / O

 

디스크 I/O가 SQL의 성능을 결정한다. SQL을 최적화하기 위해 DISK I/O를 줄여야 한다. DBMS에는 DB 버퍼 캐시가 있으며, 디스크에서 읽은 데이터블록을 캐싱해두어서 I/O콜을 줄일 수 있다. I/O 기준을 잡을 때 두 가지 개념이 존재한다.

• 논리적 I/O

SQL을 수행하면서 읽은 총 블록 I/O이다.

• 물리적 I/O

SQL을 수행하면서 디스크에서 발생한 총 블록 I/O를 말한다.

---

Single Block I/O와 Multi Block I/O

 

• Single block I/O

한 번에 한 블록 씩 요청해서 메모리에 적재하는 방식을 Single Block I/O라고 한다. 인덱스를 통한 소규모 데이터를 읽을 때 사용된다.

• Multi Block I/O

한번에 여러 블록 씩 요청해서 메모리에 적재하는 방식을 Multi Block I/O라고 한다. 대량으로 데이터를 읽을 때 유용하다.

---

인덱스는 항상 빠르지 않다.

 

일반적으로 우리는 인덱스를 쓰면 조회할 때 성능이 올라가는 것으로 알고 있다.

그 이유는 인덱스는 특정 조건에 맞는 데이터를 B+트리와 같은 자료구조를 통해 단 시간에 빠르게 찾을 수 있기 때문이다.

그러나 인덱스 컬럼의 조건에 맞는 데이터 양이 전체 데이터 양에 꽤 많은 portion을 차지하고 있다면 인덱스를 통한 Index Range Scan(랜덤 액세스)가 인덱스를 사용하지 않고 전체 테이블 블록을 읽어오는 Full Scan보다 느릴 수 있다.

Table Full Scan은 Multi Block I/O 방식으로 디스크 블록을 읽는다. 한 블록에 속한 모든 레코드를 한번에 읽어들이고, 캐시에서 못 찾으면 한 번의 IO를 통해 인접한 수십 수백개의 블록을 한번에 I/O하는 메커니즘이다.

Index Range Scan을 통한 테이블 액세스는 랜덤 액세스와 SIngle Block I/O 방식으로 디스크 블록을 읽는다. 캐시에서 블록을 못찾으면, 레코드 하나를 일기 위해 매번 잠을 자는 I/O 메커니즘이다. 즉 많은 데이터를 읽을 때 Table Full Scan 보다 불리하다. 또 읽었던 블록을 반복해서 읽기 때문에 비 효율적이다.

 

 

 

 

참고 :
https://velog.io/@ilhoon93
https://myjamong.tistory.com/200
친절한 SQL 튜닝 (도서)