SQL 전문가(SQLP)

SQL 전문가(SQLP) 시험 노트개념 정리17 MIN

데이터베이스 아키텍처와 저장 구조

SQLP 3과목의 첫 자리입니다. SGA와 PGA, 다섯 백그라운드 프로세스, 블록·익스텐트·세그먼트·테이블스페이스, Undo와 Redo의 역할 분담, Fast Commit, 전용 서버와 공유 서버 구조를 다룹니다.

3과목은 같은 결과를 내는 SQL이 왜 어떤 것은 0.1초에, 어떤 것은 10분에 끝나는가를 다룹니다. 그 답은 대부분 디스크에서 몇 블록을 읽었는가로 귀결되므로, 먼저 DBMS가 메모리와 디스크를 어떻게 나눠 쓰는지를 알아야 합니다. 이 노트는 오라클 구조를 기준으로 적습니다.

오라클에서 데이터베이스는 디스크에 있는 파일의 묶음이고, 인스턴스는 그 파일을 다루려고 띄운 공유 메모리와 프로세스의 묶음입니다. 파일은 데이터를 담는 데이터 파일, 변경 기록을 담는 Redo 로그 파일, 파일들의 위치와 상태를 적은 컨트롤 파일 셋입니다. 사용자는 파일에 직접 손대지 않고 늘 인스턴스를 거칩니다. 하나의 데이터베이스를 여러 인스턴스가 함께 여는 구성이 RAC입니다.

메모리 구조

SGA

SGA(System Global Area)는 인스턴스의 모든 프로세스가 함께 쓰는 공유 메모리입니다. 핵심 구성 요소는 셋입니다.

구성 요소 담는 것
DB 버퍼 캐시 데이터 파일에서 읽어 온 블록의 사본
공유 풀 파싱한 SQL과 실행계획(라이브러리 캐시), 딕셔너리 정보(딕셔너리 캐시)
로그 버퍼 파일에 쓰기 전의 변경 기록(Redo 엔트리)

DB 버퍼 캐시는 이미 읽은 블록을 메모리에 두어 다음 요청은 디스크를 거치지 않게 합니다. 블록을 바꿀 때도 디스크가 아니라 캐시 안의 사본을 바꾸며, 이렇게 디스크와 내용이 달라진 블록을 더티 버퍼라 합니다. 공유 풀의 라이브러리 캐시는 같은 SQL이 다시 오면 파싱을 건너뛰게 하는 자리로, 다음 노트에서 자세히 다룹니다.

PGA

PGA(Program Global Area)는 서버 프로세스마다 따로 갖는 전용 메모리이고 다른 프로세스와 나누지 않습니다. 정렬을 하는 작업 공간(Sort Area), 해시 조인의 해시 테이블을 만드는 공간, 세션 정보와 커서 상태가 여기에 듭니다. ORDER BY나 해시 조인이 PGA 안에서 끝나지 못하면 임시 테이블스페이스에 나눠 쓰게 되고, 그만큼 디스크 I/O가 늘어납니다.

SGA와 PGA의 차이는 「누가 보는가」입니다. 버퍼 캐시의 블록은 모든 세션이 함께 읽지만, 한 세션이 정렬하던 중간 결과는 그 세션만 씁니다. 정렬 결과는 다른 세션과 나눌 일이 없으므로 래치(공유 메모리를 지키는 짧은 잠금) 없이 빠르게 다룰 수 있습니다.

프로세스 구조

서버 프로세스

사용자 프로그램이 띄운 사용자 프로세스는 SQL을 보낼 뿐이고, 받아서 파싱·실행하고 블록을 읽는 일은 서버 프로세스가 합니다. 버퍼 캐시에 없는 블록을 데이터 파일에서 읽어 오는 것도 서버 프로세스입니다. 반면 캐시의 더티 버퍼를 파일에 쓰는 일은 서버 프로세스가 하지 않고 백그라운드 프로세스에 맡깁니다.

백그라운드 프로세스

프로세스 하는 일
DBWR 버퍼 캐시의 더티 버퍼를 데이터 파일에 쓴다
LGWR 로그 버퍼의 Redo 엔트리를 Redo 로그 파일에 쓴다
CKPT 체크포인트 때 DBWR에 쓰기를 신호하고 컨트롤 파일과 데이터 파일 헤더를 갱신한다
SMON 인스턴스 복구를 하고, 쓰지 않는 임시 세그먼트를 정리한다
PMON 비정상 종료된 프로세스의 트랜잭션을 롤백하고 잡고 있던 Lock을 푼다

LGWR이 로그 버퍼를 비우는 때는 커밋이 일어났을 때, 3초마다, 로그 버퍼가 3분의 1 찼을 때, 그리고 DBWR이 더티 버퍼를 쓰기 직전입니다. 마지막 조건은 변경 기록이 데이터보다 먼저 디스크에 가야 한다는 규칙(Write Ahead Logging) 때문입니다. 가득 찬 Redo 로그 파일을 보관용으로 복사하는 ARCH 프로세스도 있습니다.

SMON과 PMON은 이름이 비슷해 헷갈리기 쉽습니다. SMON은 인스턴스 전체의 장애 뒤 복구를, PMON은 세션 하나가 죽었을 때의 뒷정리를 맡습니다. 체크포인트도 짝으로 기억해 둡니다. 체크포인트는 어느 시점까지의 더티 버퍼가 데이터 파일에 다 쓰였음을 기록해 두는 일이고, 그 시점이 앞으로 당겨질수록 장애 뒤 인스턴스 복구가 Redo를 덜 거슬러 올라가도 되어 빨리 끝납니다.

저장 구조

블록·익스텐트·세그먼트

오라클의 저장 단위는 작은 것부터 넷입니다.

  • 블록 — I/O의 최소 단위입니다. 행 하나만 필요해도 그 행이 든 블록 전체를 읽습니다. 기본 크기는 8KB이고, SQL Server는 같은 단위를 페이지라 부릅니다.
  • 익스텐트 — 공간을 확장하는 단위로, 연속된 블록의 묶음입니다. 테이블에 공간이 모자라면 블록이 아니라 익스텐트 하나를 더 받습니다.
  • 세그먼트 — 저장 공간을 차지하는 객체 하나입니다. 테이블·인덱스·Undo 세그먼트가 각각 하나의 세그먼트이고, 익스텐트 여럿으로 이뤄집니다.
  • 테이블스페이스 — 세그먼트를 담는 논리적 그릇이고, 데이터 파일 하나 이상에 대응합니다.

논리 구조와 물리 파일

블록 이상은 논리 구조이고, 디스크에 실제로 있는 것은 데이터 파일입니다. 둘이 맞물리는 규칙이 셋입니다. 세그먼트는 한 테이블스페이스에만 속하고, 그 테이블스페이스의 여러 데이터 파일에 흩어질 수 있습니다. 반면 익스텐트 하나는 한 데이터 파일 안에서 연속된 블록입니다. 그래서 테이블 하나를 끝까지 읽는 동안 디스크 위치가 익스텐트 경계마다 끊길 수 있습니다.

익스텐트가 다섯 개이고 익스텐트마다 1MB, 블록이 8KB인 테이블은 익스텐트마다 1024÷8=1281024 \div 8 = 128 블록, 전체 128×5=640128 \times 5 = 640 블록을 차지합니다. 행이 일부 블록에만 들어 있어도 끝까지 읽는 방식(Full Table Scan)은 이 블록들을 다 훑습니다.

오라클 SQL Server
블록(기본 8KB) 페이지(8KB 고정)
익스텐트(크기 가변) 익스텐트(8페이지 = 64KB)
테이블스페이스 파일 그룹

Undo와 Redo

Undo 세그먼트

Undo는 데이터를 바꾸기 전의 값, 곧 변경 이전 이미지입니다. Undo 세그먼트에 저장되고 세 가지에 쓰입니다.

  1. 트랜잭션 롤백 — ROLLBACK하면 Undo의 이전 값으로 되돌립니다.
  2. 읽기 일관성 — 다른 세션이 아직 커밋하지 않은 블록을 읽을 때, Undo로 쿼리 시작 시점의 모습을 다시 만들어 읽습니다.
  3. 인스턴스 복구의 마지막 단계 — 장애 때 커밋되지 않았던 변경을 취소합니다.

읽기 일관성 덕분에 오라클에서는 읽는 세션과 쓰는 세션이 서로를 기다리지 않습니다.

Redo 로그

Redo는 데이터를 바꾼 뒤의 값, 곧 무엇을 어떻게 바꿨는가의 기록입니다. 로그 버퍼에 먼저 쓰이고 LGWR이 Redo 로그 파일에 옮깁니다. 쓰임은 데이터 파일이 손상됐을 때 백업에서 다시 쌓아 올리는 미디어 복구, 인스턴스가 죽어 캐시에만 있던 변경이 사라졌을 때 되살리는 인스턴스 복구, 그리고 아래의 Fast Commit입니다.

기준 Undo Redo
담는 것 변경 이전 값 변경 내용
목적 되돌리기·읽기 일관성 다시 하기(복구)
저장 위치 Undo 세그먼트(데이터 파일 안) 로그 버퍼 → Redo 로그 파일

Undo 세그먼트에 쓰는 것도 블록 변경이라 Redo가 남습니다. 그래서 인스턴스 복구는 Redo로 먼저 모든 변경(Undo 포함)을 되살린 뒤, 그 Undo로 커밋 안 된 것을 취소하는 두 단계로 갑니다.

커밋과 서버 구조

Fast Commit

커밋할 때마다 바뀐 데이터 블록을 데이터 파일에 쓰면, 흩어진 블록마다 랜덤 쓰기가 일어나 느립니다. 오라클은 커밋 때 Redo만 파일에 쓰고 데이터 블록은 더티 버퍼인 채로 둡니다. 이 방식이 Fast Commit입니다. 커밋 순서는 다음과 같습니다.

  1. 서버 프로세스가 커밋 기록을 로그 버퍼에 넣는다.
  2. LGWR이 로그 버퍼를 Redo 로그 파일에 쓴다.
  3. 쓰기가 끝나면 사용자에게 커밋 완료를 알린다.
  4. 더티 버퍼는 나중에 DBWR이 몰아서 쓴다.

Redo 로그 파일은 끝에 덧붙이는 순차 쓰기라 빠르고, 장애가 나도 Redo로 데이터 블록을 되살릴 수 있으니 지속성이 지켜집니다. 커밋했는데 데이터 파일에는 아직 옛 값이 있는 것이 정상이라는 점이 핵심입니다. 같은 이유로 커밋 때 블록마다 남아 있는 잠금 표시도 바로 지우지 않고, 다음에 그 블록을 읽는 세션이 정리합니다(Delayed Block Cleanout).

전용 서버와 공유 서버

기준 전용 서버 공유 서버
프로세스 배정 사용자 프로세스마다 서버 프로세스 하나 디스패처가 요청을 받아 공유 서버 프로세스 몇 개가 나눠 처리
요청 전달 서버 프로세스가 직접 받는다 SGA의 요청 큐와 응답 큐를 거친다
세션 정보 서버 프로세스의 PGA SGA
맞는 곳 요청이 길고 무거운 작업 접속은 많고 요청은 짧은 OLTP

전용 서버는 사용자가 늘수록 프로세스가 같은 수만큼 늘어 메모리를 많이 쓰고, 공유 서버는 프로세스 수를 고정하는 대신 한 요청이 오래 걸리면 다른 사용자가 큐에서 기다립니다. 접속 때마다 프로세스를 새로 띄우는 비용이 커서, 애플리케이션 서버는 미리 열어 둔 연결을 돌려쓰는 커넥션 풀을 씁니다.

연습 문제

  1. 사용자가 커밋했을 때 반드시 디스크에 쓰인 뒤에야 커밋 완료가 돌아오는 것은?
    ① DBWR이 쓰는 더티 버퍼
    ② LGWR이 쓰는 Redo 엔트리
    ③ CKPT가 갱신하는 컨트롤 파일
    ④ SMON이 정리하는 임시 세그먼트
    ②. Fast Commit은 Redo만 파일에 쓰고 커밋을 완료합니다. 데이터 블록은 나중에 DBWR이 씁니다.
  2. 저장 구조에 대한 설명으로 틀린 것은?
    ① 블록은 I/O의 최소 단위다
    ② 익스텐트 하나는 여러 데이터 파일에 걸칠 수 있다
    ③ 세그먼트는 한 테이블스페이스에만 속한다
    ④ 테이블스페이스는 데이터 파일 하나 이상에 대응한다
    ②. 익스텐트는 한 데이터 파일 안의 연속된 블록입니다. 여러 데이터 파일에 흩어질 수 있는 것은 세그먼트입니다.
  3. 블록 크기가 8KB이고 익스텐트마다 512KB인 테이블이 익스텐트 열두 개를 차지한다. 이 테이블의 블록 수는?
    ① 384
    ② 512
    ③ 768
    ④ 1,024
    ③. 익스텐트 하나가 512÷8=64512 \div 8 = 64 블록이고, 열두 개면 64×12=76864 \times 12 = 768 블록입니다.
  4. 다른 세션이 커밋하지 않은 행을 조회할 때 쿼리 시작 시점의 값을 돌려주는 데 쓰이는 것은?
    ① Redo 로그 파일
    ② 컨트롤 파일
    ③ Undo 세그먼트
    ④ 로그 버퍼
    ③. 읽기 일관성은 변경 이전 값을 담은 Undo로 블록의 옛 모습을 다시 만들어 얻습니다.
  5. 공유 서버 구조에 대한 설명으로 옳은 것은?
    ① 사용자 프로세스마다 서버 프로세스가 하나씩 배정된다
    ② 세션 정보는 각 서버 프로세스의 PGA에 둔다
    ③ 디스패처가 받은 요청은 SGA의 요청 큐에 들어간다
    ④ 한 요청이 오래 걸려도 다른 사용자의 요청에는 영향이 없다
    ③. ①②는 전용 서버의 설명이고, 공유 서버는 서버 프로세스를 나눠 쓰므로 긴 요청이 다른 요청을 기다리게 할 수 있습니다.
  6. 비정상 종료된 사용자 세션의 트랜잭션을 롤백하고 그 세션이 잡고 있던 Lock을 푸는 프로세스는?
    ① SMON
    ② PMON
    ③ DBWR
    ④ CKPT
    ②. 세션 하나의 뒷정리는 PMON, 인스턴스 전체의 복구는 SMON입니다.
SQL 전문가(SQLP) 시험 노트 전체 보기