데이터베이스 (임시)

✅DB아키텍처

데이터베이스 아키텍쳐(Database Architecture)

<출처> http://www.dbguide.net/dbqna/oracle.db?cmd=view3

데이터 베이스 아키텍쳐 = 프로세스 + 공유메모리 영역 + 디스크(물리적 파일)영역

Oracle이나 MSSQL 같은 DBMS의 구조를 살펴보면 위와 같이 구성되어 있다. (위 사진은 오라클 구조)

 

프로세스(Process)

* oracle이 리눅스(linux)에서는 프로세스 단위로 생성되고 운영된다. SQL server(mssql)는 쓰레드 기반 아키텍처고 oracle이 윈도우에서는 해당 프로세스를 쓰레드로 대체한다. 어차피 같은 역할을 수행하므로 프로세스로 통칭한다.

프로세스는 크게 서버 프로세스(Server process)와 백그라운드 프로세스(Background process)로 나뉜다.

 

- 서버 프로세스

서버 프로세스는 사용자 프로세스(클라이언트)와 통신하면서 사용자의 각종 명령어를 처리하는 프로세스다.

자세히 설명하면, SQL을 파싱하고 필요하면 최적화를 수행하며, SQL을 실행하면서 블록을 읽고, 읽은 데이터를 정렬해서 클라이언트가 요청한 결과를 만들어 네트워크에 전송하는 일련의 작업을 처리하고, 백그라운드 프로세스가 할일을 백그라운드 프로세스에게 위임시키기 위한 시스템 콜 요청을 하는 프로세스다.

 

-- 서버프로세스와 클라이언트를 연결하는 방식에는 아래 2가지가 있다.

<출처> http://ndatabase.blogspot.kr/2013/07/oracle-vs.html

 

1) 전용 서버(Dedicated Server) 방식

요청이 올때 마다 서버프로세스를 새로 생성해서 연결하고 요청/응답하고 제거하는 방식 (그림에서 아래 있는 방법)

 - 과정

1. Listener가 데이터베이스 연결 요청을 받음

2. Listener가 서버 프로세스 생성 및 연결 요청

3. 서버 프로세스가 resend 패킷을 클라이언트에게 전송

4. 클라이언트에서 오는 요청에 대한 응답

* SQL을 수행할 때마다 연결 요청을 반복하면 서버 프로세스의 생성과 해제도 반복하게 되므로 DBMS에 큰 부담이 된다. 따라서 전용 서버 방식을 사용하는 OLTP성 애플리케이션에서는 Connection Pooling 기법을 사용해야 한다.

 

2) 공유 서버(Shared Server) 방식

하나의 서버 프로세스를 여러 사용자 세션이 공유하는 방식, 미리 여러 개의 프로세스를 띄워놓고 이를 공유해서 재사용한다. (그림에서 위에 있는 방법)

1. Listener가 데이터베이스 연결 요청을 받음

2. Listener가 가용한 Dispatcher 포트번호를 클라이언트에게 전송

3. Dispatcher로 온 요청을 요청큐(Request queue)에 넣음

4. 공유서버가 요청큐에서 요청을 가져와서 해결한 후 응답을 응답큐(Response queue)에 넣음

5. Dispatcher가 응답큐로 부터 결과를 가져옴

6. 결과를 클라이언트에 리턴

 

- 백그라운드 프로세스

서버 프로세스가 하는일 외에 데이터 파일을 읽어서 DB 버퍼 캐시에 적재하는 일, Dirty 블록(파일에 기록된 데이터와 메모리에 기록된 데이터가 다른 데이터가 존재하는 블록)을 캐시에서 제거해 free 블록을 확보하는 일, Redo 로그 버퍼를 비우는 일등 내부적으로 DB시스템이 잘 돌아가게 해주는 역할하는 하는 프로세스다.

* 백그라운드 프로세스 요약

Oracle	SQL Server	설명
System Monitor(SMON)	Database clean up/ shrinking thread	장애가 발생한 시스템을 재기동할 때 복구 수행, 임시 세그먼트와 익스텐트 모니터링하는 프로세스 (* 세그먼트, 익스텐트는 아래 설명)
Process Monitor(PMON)	Open Data Services(OPS)	이상이 생긴 프로세스가 사용하던 리소스 복구하기 위한 프로세스
Database Writer(DBWR)	Lazywriter thread	버퍼 캐시에 있는 Dirty 버퍼(블록)를 데이터 파일에 기록하는 프로세스
Log Writer(LGWR)	Log writer thread	로그 버퍼 엔트리를 Redo 로그 파일에 기록하는 프로세스
Archiver(ARCn)	.	꽉찬 Redo 로그가 덮어 쓰여지기 전 Archive 로그 디렉토리에 반영
Checkpoint(CKPT)	Database Checkpoint thread	Write ahead logging 방식(데이터 변경전 로그부터 쓰는 메커니즘)을 사용하는 DBMS는 Redo 로그에 기록해 둔 버퍼 블록에 대한 변경 사항 중 현재 어디까지 데이터 파일에 기록했는지(데이터 동기화)를 checkpoint로 관리하는데, 이 마지막 checkpoint 이후 로그 데이터만 디스크에 기록함으로써 인스턴스를 복구할 수 있게하는 용도로 사용되는 프로세스
Recverer(RECO)	Distributed Transaction Coordinator(DTC)	분산 트랜잭션 과정에 발생한 문제를 해결하는 프로세스

SMON, PMON, DBWR(DBWs), LGWR, CKPT 는 5개 필수 백그라운드 프로세스다. 

 

디스크(물리적) 영역 - 파일 구조

디스크 영역은 크게 데이터파일 + 임시 데이터파일 + 로그 파일로 구성된다.

- 데이터 파일

위 그림은 데이터베이스 파일 구조를 논리적 영역과 물리적 영역으로 나눠서 요약한 것이다.

물리적으로 디스크에는 데이터베이스가 여러 데이터 파일(Data File)들로 구성되어있다.

각각 데이터파일은 여러 개의 Block(단위)으로 구성되어있다.

tablespace (땅) - segment (건물) - extent (건물의 어느 한 층) - block (건물 어느 한 층의 사무실)

 

1. 블록(block)

<블록 구조>

블록에는 다양한 데이터들이 있고 블록 헤더에는 블록을 관리하기 위한 데이터가 들어있다.

하나의 블록은 2KB, 4KB, 8KB, 16KB, 32KB, 64KB의 다양한 크기를 사용할 수 있다. (SQL server는 8KB만)

하나의 블록안에는 여러 레코드(데이터)들이 들어 있다.

데이터베이스에서는 I/O를 블록단위로 한다.

그렇기 때문에 원하는 레코드가 하나여도 블록 째로 읽어들이기 때문에 성능을 좌우하는것은 읽어올 블록의 개수를 최소로 하는 것이다.

이런 특징을 갖는 블록들을 논리적으로 블록이라는 것으로 똑같이 만들었다.

 

2. 익스텐트(Extent)

익스텐트는 여러 개의 연속된 블록 집합이다. (블록의 묶음)

I/O의 단위는 블록이지만 테이블 스페이스로부터 공간을 할당하는 단위는 익스텐트다.

 

3. 세그먼트(Segment)

세그먼트는 여러 개의 익스텐트를 가지고 있는 오브젝트다.

세그먼트는 데이터베이스의 테이블, 인덱스, Undo 처럼 저장공간이 필요로 하는 오브젝트를 말한다.

 

4. 테이블 스페이스(Tablespace)

테이블 스페이스는 세그먼트를 담는 콘테이너로 여러 데이터 파일로 구성된다.

보통 MySQL같은 DBMS에서 워크벤치를 사용했을 때보면 테이블스페이스 안에다가 테이블을 만드는 것을 알 수 있다.

여러 테이블이 있는 공간을 나타낸다.

 

위와 같은 구조로 데이터파일들이 생성되고 관리된다.

 

- 임시 데이터 파일 

임시 데이터 파일은 대량의 정렬이나 해시 작업을 수행하다가 메모리 공간이 부족해지면 중간에 결과 집합을 잠시 저장하는 용도로 사용되는 파일이다. (잠시 저장되었다가 삭제됨)

 

- 로그 파일

 -- online Redo 로그

캐시에 저장된 변경사항이 아직 데이터파일로 기록되지 않은 상태에서 장애가 생겨서 데이터파일로 기록하지 못한게 날아간 경우에 이것을 복구하기 위해서 online Redo 로그를 사용한다.

online Redo로그는 최소 2개 이상의 파일로 구성되고 하나의 파일이 꽉차면 다음 파일로 로그를 스위칭하고 계속 로그를 쓰다가 모든 파일이 꽉차면 다시 첫번 째 파일부터 재사용하는 형식으로 사용한다.

 

-- Archived Redo 로그

Online Redo 로그가 재사용되기전에 다른 위치로 백업해 둔 파일이다.

 

- 컨트롤 파일

데이터베이스를 시작할 때 항상 참조되는 파일.

데이터베이스에서 사용할 모든 파일들의 절대경로와 파일크기등의 정보를 저장하고 있고 파일들의 이상유무를 확인하기 위해서 참조된다.

 

- 파라미터 파일

공유메모리 영역을 얼마만큼 할당 받을지, 컨트롤 파일의 경로와 데이터베이스의 환경설정 등 관련 모든 정보를 포함하고 있다.

 

---

공유메모리 영역 - 메모리 구조

메모리영역은 크게 시스템 공유 메모리 영역 + 프로세스 전용 메모리 영역으로 나뉜다.

1. 공유 메모리 영역은 운영체제가 제공해준 것으로 여러 프로세스가 동시에 엑세스할 수 있는 메모리 영역이다

SGA(System Global Area)라고 부르며 DB버퍼캐시, 공유 풀, 로그버퍼, Large풀, 자바풀등이 여기에 속해 있다.

 

2. 프로세스 메모리 영역은 서버프로세스가 가진 자신만의 메모리 영역으로 PGA(Process Global Area)라고 부르며, 데이터를 정렬하고 세션과 커서에 대한 상태 정보를 저장하는 용도로 쓰인다.

 

공유 메모리 영역의 프로세스들

1) DB 버퍼 캐시

디스크 파일의 데이터 파일로 부터 읽은 데이터 블록을 담는 캐시 영역이다.

모든 읽기는 DB 버퍼 캐시를 통해 이루어지고 읽고자하는 데이터가 DB 버퍼캐시에 없으면 디스크에서 읽는다.

디스크에서 읽을 때도 버퍼 캐시에 적재한 후 버퍼 캐시에서 읽어온다.

데이터 변경이 되어서 Dirty블록이 생기면 주기적으로 DBWR 프로세스가 디스크에 기록을 한다.

 

* 버퍼 블록의 상태

Free 블록 : 비어있는 상태거나 데이터가 담겼지만 파일과 동기화되어 일치하는 상태로 덮어써도 상관없는 상태인 것을 말한다.

dirty 블록 : 버퍼에 캐시된 이후 변경이 발생했지만 아직 파일에 쓰지 않아(동기화 안됨) 덮어 쓸 수 없는 상태인 것을 말한다.

pinned 블록 : 읽기 또는 쓰기 작업이 진행 중인 상태를 말한다.

 

2) 공유 풀(Shared Pool)

 공유 풀은 딕셔너리 캐시와 라이브러리 캐시로 구성된다. LRU알고리즘사용

-- 딕셔너리 캐시

테이블, 인덱스 같은 오브젝트 + 테이블 스페이스, 데이터 파일, 세그먼트, 익스텐트, 사용자, 제약에 관한 메타 데이터를 저장하는 곳이다. (테이블 메타데이터 정보같은 것들)

-- 라이브러리 캐시

사용자가 수행한 SQL문과 실행계획, 저장 프로시저를 저장해두는 캐시다.

사용자가 SQL 명령어를 통해 결과를 요청하면 이를 최적으로 수행하기 위한 루틴을 생성해야하는데 이것이 실행계획(Execution plan)이라고 한다.

쿼리 구문을 분석해서 Syntax check(문법 맞는지), semantic check(테이블 존재등 의미가 맞는지), 권한 검사(권한 있는지)를 하고 최적화 과정을 거쳐 실행계획을 생성하고 SQL실행 엔진이 이해할 수 있는 형태로 포맷팅하는 전과정을 하드 파싱이라고 한다.

최적화 과정이 가장 성능에 주요한 영향을 미치는데 이런 최적화 과정을 없애기 위해 전에 사용한 SQL문과 실행계획을 저장해두고 바로 사용할 수 있게 하기 위해서 라이브러리 캐시라는 곳에 저장해둔다.

 

3) 로그 버퍼

DB버퍼 캐시에 가해지는 모든 변경사항을 로그 파일에 기록한다.

로그를 건마다 기록하는 것보다 모아서 기록하는 것이 성능에 유리하기 때문에 두는 버퍼다.

버퍼 캐시 블록을 갱신하기 전에 변경사항을 먼저 로그버퍼에 기록해야 하며, dirty 블록을 디스크에 기록하기 전에 해당 로그 엔트리를 먼저 로그 파일에 기록해야한다. (Write Ahead Logging)

 

PGA (공유되지 않는 독립 메모리 공간)

- UGA(User Global Area)

PGA에 할당되는 메모리 공간으로 하나의 프로세스가 여러 개의 세션을 위한 독립적인 메모리 공간이 필요해지는데 이때 쓰는 것이 UGA다.

- CGA(Call Global Area)

PGA에 할당되는 메모리 공간으로 하나의 데이터베이스 Call을 넘어 다음 Call까지 계속 참조되어야 하는 정보는 UGA에 담고 Call이 진행되는 동안에만 필요한 데이터를 CGA에 담는다.

CGA는 parse call(분석), execute call(실행), fetch call(가져오기) 마다 매번 할당 받는다.

- Sort Area

데이터 정렬을 위해 사용되는 공간

출처: https://jeong-pro.tistory.com/147 [기본기를 쌓는 정아마추어 코딩블로그:티스토리]

 

---

 

DB 중요 개념 정리

✅ 데이터베이스란?

• 여러 사람의 공유 목적으로 체계화해 통합, 관리되는 데이터의 집합
• 데이터 베이스를 사용하기 이전 : 파일 시스템을 이용해 관리
  → 데이터 종속성, 중복성, 무결성의 문제 발생

✅ 데이터베이스의 특징

1️⃣ 독립성(Data Independence)

• 논리적 독립성 : 응용 프로그램과 데이터베이스 간의 독립
  • 데이터의 논리적 구조를 변경시켜도 응용 프로그램의 논리적 구조는 변경되지 않음
• 물리적 독립성 : 응용 프로그램과 물리적 장치(ex. 보조기억장치 etc.)간의 독립
  • 데이터베이스 시스템의 성능 향상을 위한 새로운 디스크의 도입에도 응용 프로그램에 영향이 없음
  • 데이터의 물리적 구조만을 변경
  • 데이터베이스의 사이즈 변경 시 관련된 응용 프로그램의 수정이 필요 없음
• cf) 종속성(Data Dependency) : 응용 프로그램의 구조가 데이터의 구조에 영향을 받음

2️⃣ 무결성 (Data Integrity)

• 데이터의 삽입, 삭제, 갱신 등의 연산 후에도 데이터 베이스에 저장되어있는 데이터가 정해진 제약조건을 항상 만족해야 함
• 여러 경로를 통해 잘못된 데이터가 발생하는 경우를 방지하기 위한 목적
• 데이터의 유효성 검사

3️⃣ 일관성 (Data Consistency)

• 데이터베이스에 저장되어있는 데이터와 특정 질의에 대한 응답이 변함 없이 일정해야 하는 조건
• 관련있는 정보를 논리적인 구조로 관리하는 체계
• 데이터 변경 시 나머지 데이터와 일치하지 않는 불일치성의 배제를 위한 목적

4️⃣ 보안성 (Data Security)

• 데이터로의 접근 권한이 있는 사용자들만 접근할 수 있도록 설정

5️⃣ 중복성 (Data Redundancy)

• 같은 데이터의 중복 저장
• 데이터의 수정, 삭제가 필요할 경우 연결되어있는 모든 데이터를 수정 및 삭제해줘야 하는 문제점 존재
• 데이터의 통합 관리를 통해 데이터의 중복을 최소화할 것

✅ 데이터베이스 시스템의 목적

1️⃣ 기존 파일 시스템 사용 시

• 데이터의 중복 및 비일관성의 문제
• 데이터의 접근 및 검색의 비효율성
• 무결성 문제
• 보안 문제

2️⃣ 데이터베이스 시스템 사용 시

• DBMS를 사용한 요구 처리
• SQL을 사용한 데이터로의 접근
• 👍 Good
  • 데이터의 중복 최소화
  • 데이터의 일관성
  • 데이터의 물리적, 논리적 독립성
  • 데이터의 무결성 문제 해결
  • 데이터의 보안 문제 해결
  • 데이터의 통합 관리 및 실시간 처리 가능
  • 저장된 자료의 공동 이용 가능
• 👎 Bad
  • 전산화 비용의 증가
  • 대용량 디스크로의 집중적인 Access로 인한 Overhead 발생

✅ 인덱스(Index)

• Index == 색인
  • 데이터 == 책의 내용
  • 데이터가 저장된 레코드 주소 == 인덱스 목록에 있는 페이지 번호
• DBMS(DataBase Management System)은 항상 정렬된 상태 유지
  • 탐색에 효율적
  • 값의 추가, 삭제, 수정 비효율적

▶️ DBMS의 인덱스 자료구조

• B-Tree : 컬럼 값의 변형 없이 원래의 값을 이용해 인덱싱 하는 방법(일반적으로 사용되는 방법)
  - 한 노드 당 자식 노드가 2개 이상 가능
  - key 값을 이용해 데이터 찾음
  - 데이터 검색 속도가 일정함
  - 균형 트리
  - 브랜치 노드에 key, data를 담을 수 있음
  
• Hash : 컬럼의 값으로 해시 값을 계산해 인덱싱
  - 매우 빠른 검색 속도
  - 값을 변형해 인덱싱하기에 값의 일부만으로 검색할 때에는 사용할 수 없음
  - B-Tree보다 Hash가 효율적일 것 같으나 SELECT의 질의 조건에 부등호(< , >)도 포함됨
  → hash에서 등호(=) 연산이 아닌 부등호(< , >) 연산의 경우 문제 발생
  

✅ 데이터 베이스 언어

1️⃣ DDL(Data Definition Language) - 데이터 정의어

• 데이터베이스 객체의 구조를 정의하는 언어
  • 데이터베이스 객체 : ex) table, view, index...

명령어기능

CREATE	테이블 생성
ALTER	테이블의 구조 수정
DROP	테이블 삭제
RENAME	테이블의 이름 변경
TRUNCATE	테이블의 초기화

2️⃣ DML(Data Manipulation Language) - 데이터 조작어

• 데이터베이스의 내부 데이터를 관리하기 위한 언어
• 데이터베이스 테이블의 레코드의 CRUD를 관리하는 언어 (create, read, update, delete)

명령어기능

SELECT	데이터베이스 내에서 데이터 검색
INSERT	테이블에 데이터 추가
UPDATE	테이블 내에 존재하는 데이터 수정
DELETE	테이블에서 데이터 삭제

3️⃣ DCL(Data Control Language) - 데이터 제어어

• 데이터베이스 및 구조에 대한 접근 권한을 제어하는 언어 (권한 부여 or 박탈)
• 데이터 관리 목적으로 보안, 무결성, 회복, 병행 제어 등을 정의할 때 사용
• 트랜잭션의 명시 및 조작이 가능한 언어

명령어기능

GRANT	권한 정의 시 사용
REVOKE	권한 삭제 시 사용

4️⃣ TCL(Transaction Control Language) - 트랜잭션 제어어

• DCL과 비슷한 맥락이나, 데이터 제어 언어가 아닌 트랜잭션을 제어하는 언어
• 논리적인 작업 단위를 묶어 DML 명령문으로 수행된 결과를 트랜잭션 별로 제어

명령어기능

COMMIT	모든 작업을 정상적으로 처리
ROLLBACK	모든 작업을 다시 돌려 놓음
SAVEPOINT	Commit 전, 특정 시점까지만 반영 or Rollback

✅ 트랜잭션(Transaction)

• 데이터베이스의 상태를 변화시키기 위해 수행되는 작업의 논리적 단위
• 데이터베이스 작업의 완전성을 보장해주기 위한 수단
• 모두 완벽하게 처리되거나 처리하지 못할 경우 원상태로 복구해 작업의 일부만이 적용되는 현상을 방지하기 위한 기능

▶️ 트랜잭션의 특성 - ACID

• Atomicity (원자성) : 트랜잭션 중간에 어떤 문제가 발생하였을 때 아래의 상황에 따라 작업 내용의 반영 여부가 달라짐
  • 모두 성공할 경우 : 모두 반영됨
  • 하나라도 실패한 경우 : 모두 반영되지 않음
• Consistency (일관성) : 트랜잭션의 처리 결과는 항상 데이터의 일관성을 보장해야 함
• Isolation (고립성) : 둘 이상의 트랜잭션이 동시에 실행되고 있을 경우, 각 트랜잭션은 서로의 간섭 없이 독립적으로 수행됨
• Durability (지속성) : 트랜잭션이 정상적으로 종료된 뒤 영구적으로 데이터베이스 작업의 결과가 저장되어야 함

▶️ 트랜잭션의 5가지 상태

• Active : 트랜잭션이 현재 진행 중인 상태
• Failed : 트랜잭션이 실행되다가 오류가 발생해 중단된 상태
  • 트랜잭션이 더 이상 정상적으로 진행될 수 없는 상태
• Aborted : 트랜잭션이 비정상 종료되어 RollBack이 수행된 상태
  • 트랜잭션이 취소되어 트랜잭션 실행 이전 데이터로 돌아간 상태
• Partially Committed : 트랜잭션의 연산이 마지막까지 실행된 후 Commit되기 이전의 상태
  • 트랜잭션의 Commit 명령이 도착한 상태
  • 트랜잭션이 Commit되기 이전 sql문이 수행되고 Commit만 남은 상태
• Commited : 트랜잭션이 성공적으로 종료되어 Commit 연산을 실행한 후의 상태
  • 트랜잭션이 정상적으로 완료된 상태

▶️ 트랜잭션 vs Lock

• 트랜잭션 : 논리적인 작업 set 자체가 모두 반영되고나 아무 것도 적용되지 않아야 함을 보장하는 개념
• Lock : 여러 커넥션에서 동시에 동일한 자원을 요구할 때, 순서대로 한 시점에 하나의 커넥션만 변경할 수 있도록 하는 개념

✅ 교착상태 (DeadLock)

• 여러 개의 트랜잭션들이 실행하지 못하고 서로 무한정으로 대기하는 상태
• 데이터베이스 : 트랜잭션들의 동시성 제어를 위한 기법으로 Locking 사용
  

  

▶️ 교착상태 발생의 필요충분 조건

• 상호배제 : 한 번에 한 개의 프로세스만 자원을 사용할 수 있어야 함
• 점유와 대기 : 프로세스가 한개의 자원을 점유하고 있고, 추가적으로 다른 프로세스의 자원을 점유하기 위해서는 대기해야 함
• 비선점 : 점유된 자원을 강제로 해제될 수 없고, 프로세스가 자원의 사용을 자발적으로 해제하기 전까지 그 자원을 얻을 수 없음
• 환형 대기 : 자원과 자원을 사용하기 위해 대기하는 프로세스들이 원형으로 구성되어 있어 자신에게 할당된 자원을 점유하여 앞 또는 뒤에 있는 프로세스의 자원을 요구해야 함

▶️ 교착상태 해결 방법

• 교착상태 예방 : 필요충분 조건 중 한가지를 제거하여 교착상태가 발생하지 않도록 사전에 방지하는 방법
  • 자원 요청을 제한해 교착상태를 예방
  • 자원 낭비가 가장 심한 방법
• 교착상태 회피 : 교착상태가 발생할 가능성을 배제하지 않고 교착상태의 발생 시 적절히 피하는 방법
• 교착상태 탐지 : 교착상태의 발생을 점검해 교착상태에 있는 프로세스와 자원을 발견하는 방법

 

---

✅ 표 관련 용어

• Relation(=table): 관계형 데이터 베이스에서 정보를 구분하여 저장하는 기본 단위
• Tuple(=Record): 테이블에서 행을 의미, 튜플은 릴레이션에서 같은 값을 가질 수 없다. 튜플의 수는 카디날리티(Cardinality)라고 한다.
• Attribute(=Field): 테이블에서 열을 의미, 같은 말로는 컬럼이라고 하며 어트뷰트의 수는 디그리(Degree)라고도 함
• 식별자(Indentifier): 여러 개의 집합체를 담고 있는 관계형 데이터 베이스에서 각각의 구분할 수 있는 논리적인 개념
  • 특성최소성: 꼭 필요한 최소한의 속성들로만 키를 구성해야 한다.
  • 유일성: 하나의 릴레이션에서 모든 행은 서로 다른 키 값을 가져야 한다.

Key

데이터베이스에서 조건에 만족하는 튜플을 찾거나 순서대로 정렬할 때 튜플들을 서로 구분할 수 있는 기준이 되는 어트리뷰트

• 후보키(Cardidate Key)
  • 릴레이션을 구성하는 속성들 중에서 튜플을 유일하게 식별하기 위해 사용하는 속성들의 부분집합
  • 기본 키로 사용할 수 있는 속성들을 의미
  • 모든 릴레이션에는 반드시 하나 이상의 후보키가 존재한다.
  • 유일성과 최소성을 만족시켜야 한다.
• 기본키(Primary Key)
  • 후보키 중에서 선택한 Main Key
  • 한 릴레이션에서 특정 튜플을 유일하게 구별
  • null 값을 가질 수 없음, 동일한 값이 중복되어 저장될 수 없음
• 슈퍼키(Super Key)
  • 한 릴레이션 내에 있는 속성들의 집합으로 구성된 키로서 릴레이션을 구성하는 모든 튜플들 중 슈퍼키로 구성된 속성의 집합과 동일한 값을 나타나지 않는다.
  • 릴레이션을 구성하는 모든 튜플에 대해 유일성을 만족 시키지만, 최소성을 만족시키지 못한다.
  • ex. 학번 + 주민번호를 사용해 슈퍼키를 만들면 유일성은 만족하지만 학번이나 주민번호 하나만 가지고 다른 튜플들을 구분할 수 있으므로 최소성을 만족하지 못한다.
• 외래키(Foregin Key)
  • 어떤 릴레이션 간의 기본키를 참조하는 속성, 테이블들 간의 관계를 나타내기 위해서 사용된다.
  • 다른 릴레이션의 기본키를 그대로 참조하는 속성의 집합을 의미
  • 외래키가 되는 속성과 기본키가 되는 속성의 이름은 달라도 되지만, 외래키의 속성의 도메인과 참조되는 기본키 속성의 도메인은 반드시 같아야 한다.
  • 외래키는 기본키를 참조하지만 기본키가 아니기 때문에 null값을 가질 수 있고, 서로 다른 튜플이 같은 값을 가질 수 있다.

관계형 데이터 모델의 제약 조건

• 기본키를 구성하는 모든 속성은 null을 가질 수 없다.
• 외래키는 참조할 수 없는 값을 가질 수 없다.

무결성이란?

무결성이란 데이터베이스에 저장된 데이터 값과 그것이 표현하는 현실세계의 실제 값이 일치하는 정확성을 의미한다.

무결성을 유지하는 방법

대표적으로 사용되는 방법은 중앙통제에 의한 데이터 갱신으로서, 이 방법은 검증 프로그램을 이용하여 모든 갱신 처리 과정에서 반드시 검증 단계를 거치도록 통제를 가한다.

검증 프로그램이 무결성을 검증하기 위해 무결성 규정을 사용한다.

 

✔️ 규정 이름: 무결성 규정을 참조할 때 사용하는 식별자
✔️ 트리거 조건: 트랜잭션의 접근 유형및 데이터, 검사할 시기 명시
✔️ 프레디킷(제약조건): 무결성을 위한 검사 조건
✔️ 위반조치 : 검사결과 무결성 위반이 발견되었을 때 처리할 조치

무결성의 종류

1. 널 무결성: 릴레이션의 특정 속성 값이 NULL이 될 수 없도록 하는 규정
2. 고유 무결성: 릴레이션의 특정 속성에 대해서 각 튜플이 갖는 값들이 서로 달라야 한다는 규정
3. 참조 무결성: 외래키 값은 Null 이거나 참조 릴레이션의 기본키 값과 동일해야 한다는 규정 즉, 릴레이션은 참조할 수 없는 외래키 값을 가질 수 없다는 규정
4. 도메인 무결성: 특정 속성의 값이, 그 속성이 정의된 도메인에 속한 값이어야 한다는 규정
5. 키 무결성: 하나의 테이블에는 적어도 하나의 키가 존재해야 한다는 규정

무결성 제약조건

데이터베이스에 들어 있는 데이터의 정확성을 보장하기 위해 부정확한 자료가 데이터베이스 내에 저장되는 것을 방지하기 위한 제약조건을 의미함

개체 무결성

릴레이션에서 기본키를 구성하는 속성은 Null값이나 중복 값을가질 수 없다.

참조 무결성

외래키 값은 Null이거나 참조 릴레이션의 기본키 값과 동일해야 한다. 즉 릴레이션은 참조할 수 없는 외래키 값을 가질 수 없다.

외래키와 참조하려는 테이블의 기본키는 도메인과 속성 개수가 같아야 한다.

관계 대수

1. 관계대수는 관계형 데이터베이스에서 원하는 정보와 그 정보를 검색하기 위해서 어떻게 유도하는 가를 기술하는 절차적 언어이다.
2. 관계대수는 릴레이션을 처리하기 위해 연산자와 연산 규칙을 제공하는 언어로 피연산자가 릴레이션이고 결과도 릴레이션이다.
3. 질의에 대한 해를 구하기 위해 수행해야 할 연산의 순서를 명시한다.
4. 대표적으로 순수 관계 연산자와 일반 집합 연산자가 있다.

순수 관계 연산자

순수 관계 연산자란 관계 데이터 베이스에 적용할 수 있도록 특별히 개발한 관계 연산자

select: 릴레이션에 존재하는 튜플 중에서 선택 조건을 만족하는 튜플의 부분집합을 구하여 새로운 릴레이션을 만든다.

project: 주어진 릴레이션에서 속성 리스트에 게시된 Attribute만을 추출하는 연산

릴레이션의 열(세로)에 해당하는 Attribute를 추출하는 것이므로 수직 연산자라고도 한다.

join: 공통 속성을 중심으로 두 개의 릴레이션을 하나로 합쳐서 새로운 릴레이션을 만드는 연산

division: 두 개의 릴레이션이 있을 때 하나의 속성(A)이 다른 하나의 속성(B)값을 모두 가진 튜플에서 B가 가진 속성을 제외한 속성만을 구하는 연산

일반 집합 연산자

수학적 집합 이론에서 사용하는 연산자로서 릴레이션 연산에도 그대로 적용할 수 있다.

일반 집합 연산자: 합집합, 교집합, 차집합, 교차곱

 

✅ 파라미터

Oracle 주요 매개변수

🖥 메모리 관리 관련

매개변수	설명
SGA_TARGET / SGA_MAX_SIZE	시스템 전역 영역(SGA) 크기 동적 조절
PGA_AGGREGATE_TARGET	프로세스 전역 영역(PGA) 크기 설정
MEMORY_TARGET / MEMORY_MAX_TARGET	SGA+PGA 통합 자동 메모리 관리
SHARED_POOL_SIZE	공유풀 크기(SQL, PL/SQL, 데이터 딕셔너리 캐시)
DB_CACHE_SIZE	버퍼 캐시 크기(자주 읽는 데이터 블록 캐싱)

⚙ 프로세스 관리 관련

매개변수	설명
PROCESSES	최대 사용자 프로세스 수
OPEN_CURSORS	세션당 최대 커서 수
SESSION_CACHED_CURSORS	세션별 커서 캐싱 개수
PARALLEL_MAX_SERVERS	병렬 쿼리 최대 서버 프로세스 수

💾 I/O 관리 관련

매개변수	설명
DB_FILE_MULTIBLOCK_READ_COUNT	한 번에 읽는 최대 블록 수
DB_FILE_DIRECT_IO_COUNT	Direct Path Read/Write I/O 크기
DBWR_IO_SLAVE_COUNT	DBWR 슬레이브 프로세스 수

🔧 시스템 자원 관리

매개변수	설명
CPU_COUNT	사용 CPU 수
RESOURCE_LIMIT	리소스 제한 기능 활성/비활성
OPTIMIZER_MODE	옵티마이저 동작 방식 설정(ALL_ROWS, FIRST_ROWS 등)
CURSOR_SHARING	커서 공유 정책(SQL 재사용률 향상)

 

2. MySQL & MariaDB 주요 매개변수

🖥 메모리 관련

매개변수	설명
innodb_buffer_pool_size	InnoDB 버퍼 풀 크기
innodb_log_file_size	InnoDB redo 로그 파일 크기
query_cache_size	쿼리 캐시 크기
tmp_table_size / max_heap_table_size	메모리 기반 임시 테이블 최대 크기

⚙ 스레드 & 연결 관련

매개변수	설명
max_connections	최대 동시 연결 수
thread_cache_size	스레드 캐싱 개수
wait_timeout	유휴 연결 종료 시간

📝 로그 관련

매개변수	설명
slow_query_log	느린 쿼리 로깅 여부
log_bin	바이너리 로그 활성화 여부 (복제·백업용)

🔧 기타 성능 관련

매개변수	설명
innodb_flush_log_at_trx_commit	트랜잭션 커밋 시 로그 플러시 빈도
innodb_file_per_table	테이블별 개별 테이블스페이스 사용 여부

 

3. PostgreSQL 주요 매개변수

🖥 메모리 관련

매개변수	설명
shared_buffers	공유 버퍼 메모리 크기
work_mem	쿼리별 임시 작업 메모리 크기
maintenance_work_mem	VACUUM, CREATE INDEX 등 유지보수 메모리
effective_cache_size	OS 캐시를 포함한 예상 전체 캐시 크기

💾 디스크 관련

매개변수	설명
wal_buffers	WAL 버퍼 크기
random_page_cost	디스크 임의 읽기 비용
effective_io_concurrency	동시 I/O 처리 가능 개수

🌐 네트워크 관련

매개변수	설명
listen_addresses	서비스 IP 주소
port	서비스 포트 번호
max_connections	최대 클라이언트 연결 수

🧹 Autovacuum 관련

매개변수	설명
autovacuum_max_workers	동시 실행 가능 autovacuum 작업자 수
autovacuum_naptime	autovacuum 주기(대기 시간)
autovacuum_vacuum_threshold / autovacuum_analyze_threshold	최소 변경 행 수 기준
autovacuum_vacuum_scale_factor / autovacuum_analyze_scale_factor	변경 비율 기준
autovacuum_work_mem	autovacuum 실행 시 메모리 크기

 

✅ Data Type

범주	Oracle	PostgreSQL	MSSQL	MySQL
정수형	NUMBER(p,0) / INTEGER	SMALLINT / INTEGER / BIGINT	TINYINT / SMALLINT / INT / BIGINT	TINYINT / SMALLINT / MEDIUMINT / INT / BIGINT
고정 소수점	NUMBER(p,s)	NUMERIC(p,s) / DECIMAL(p,s)	NUMERIC(p,s) / DECIMAL(p,s)	DECIMAL(p,s) / NUMERIC(p,s)
부동 소수점	BINARY_FLOAT / BINARY_DOUBLE	REAL / DOUBLE PRECISION	FLOAT(n) / REAL	FLOAT(p) / DOUBLE / REAL
문자 (고정 길이)	CHAR(n)	CHAR(n)	CHAR(n)	CHAR(n)
문자 (가변 길이)	VARCHAR2(n)	VARCHAR(n)	VARCHAR(n)	VARCHAR(n)
긴 문자열(텍스트)	CLOB	TEXT (TEXT, MEDIUMTEXT, LONGTEXT 등)	TEXT / NTEXT* (비추천) / VARCHAR(MAX)	TEXT / MEDIUMTEXT / LONGTEXT
이진 데이터	BLOB	BYTEA	VARBINARY / IMAGE* (비추천)	BLOB / MEDIUMBLOB / LONGBLOB
날짜	DATE (YYYY-MM-DD HH24:MI:SS)	DATE (YYYY-MM-DD)	DATE	DATE
날짜+시간	TIMESTAMP, TIMESTAMP WITH TIME ZONE	TIMESTAMP(n), TIMESTAMPTZ	DATETIME, DATETIME2, SMALLDATETIME	DATETIME, TIMESTAMP
시간	INTERVAL / TIMESTAMP	TIME, INTERVAL	TIME	TIME
불리언	없음 (CHAR(1) 또는 NUMBER(1)로 ‘Y/N’ 저장)	BOOLEAN	BIT	BOOLEAN / TINYINT(1)
자동 증가	SEQUENCE	SERIAL / BIGSERIAL	IDENTITY	AUTO_INCREMENT

※ MSSQL의 NTEXT, TEXT, IMAGE는 Deprecated 상태이며 NVARCHAR(MAX), VARCHAR(MAX), VARBINARY(MAX)로 대체 권장합니다.

※ Oracle에서는 BOOLEAN 타입이 기본적으로 없으므로, NUMBER(1) 또는 CHAR(1)을 대신 사용합니다.

 

📌 특징 및 주의사항

1. 문자 인코딩 차이
2. 자동 증가 컬럼
3. 날짜/시간 처리

 

✅ DB 모니터링 / 점검 항목

1. 성능(PERFORMANCE) 점검

구분	Oracle	PostgreSQL	MySQL
CPU/메모리 사용률	OS/DB AWR 리포트로 자원 사용 분석	pg_stat_activity, OS 모니터링	SHOW PROCESSLIST, OS 모니터링
주요 쿼리/PL 성능	AWR/ASH 분석, Top SQL 점검	pg_stat_statements 통한 쿼리 상위 리스트	slow_query_log 분석
인덱스 상태	DBA_INDEXES, 사용률 분석	pg_stat_user_indexes	SHOW INDEX FROM table
통계 갱신 여부	DBMS_STATS.GATHER_SCHEMA_STATS	ANALYZE, VACUUM ANALYZE	ANALYZE TABLE
리소스 경합	래치/락 경합, V$SESSION, V$LOCK	pg_locks	SHOW ENGINE INNODB STATUS

 

2. 장애 예방(Fault Prevention) 점검

구분	Oracle	PostgreSQL	MySQL
에러 로그 분석	alert.log , 트레이스 파일	pg_log 디렉토리 로그	에러 로그(/var/log/mysqld.log 등)
백업 검증	RMAN 백업 및 복구 테스트	pg_dump/pg_restore 복구 검증	mysqldump/백업 파일 복구 테스트
이중화 상태	Data Guard/LGWR Sync 상태 확인	Streaming Replication 지연	SHOW SLAVE STATUS (리플리케이션 지연)
장기 미사용 객체	오래 안 쓰는 인덱스/테이블 점검	unused index, orphan table	unused index, orphan table

 

3. 보안(Security) 점검

구분	Oracle	PostgreSQL	MySQL
패스워드 정책	PROFILE 확인, PASSWORD_VERIFY	password_encryption, 계정 만료	validate_password_policy
계정 사용 이력	DBA_AUDIT_SESSION	pg_stat_activity 및 audit extension	GENERAL_LOG, Audit plugin
권한 점검	DBA_TAB_PRIVS, DBA_ROLE_PRIVS	\du, \l+ (권한)	SHOW GRANTS FOR user
보안 패치	PSU, RU 적용	마이너 버전 최신화	마이너 버전 최신화

 

4. 용량(Capacity) 점검

구분	Oracle	PostgreSQL	MySQL
데이터 파일 증가율	DBA_DATA_FILES, DBA_FREE_SPACE	pg_tables, pg_class.relpages	Information_schema.TABLES
로그 파일 관리	REDO/ARCHIVE 로그 사용량	WAL 사이즈, archive 경로 용량	Binary Log 크기, purge 여부
테이블 단편화	ALTER TABLE MOVE/SHRINK SPACE	VACUUM FULL	OPTIMIZE TABLE
TEMP Tablespace	사용률 및 정리 여부	TEMP schema 사용량	tmpdir 사용량

 

5. 버전/구성(Configuration) 점검

구분	Oracle	PostgreSQL	MySQL
파라미터 변경 이력	SHOW PARAMETER, V$PARAMETER	SHOW ALL, pg_settings	SHOW VARIABLES
DBMS 버전	SELECT * FROM V$VERSION	SELECT version();	SELECT @@version;
불필요 옵션 점검	테스트 옵션 비활성화	불필요 extension 제거	불필요 plugin 제거

 

✅ 월별 점검 보고서 예시 목차

1. 시스템 자원 현황 (CPU, Memory, Disk)
2. 주요 성능 지표
3. 에러/장애 로그 분석
4. 백업 및 복구 테스트 결과
5. 이중화/HA 동기화 현황
6. 보안 점검
7. 용량 분석
8. 개선 및 조치 계획

 

 

✅ 전환 POC / 대용량 데이터베이스 전환 POC 기획 → 설계 → 구현 → 검증 → 평가의 각 단계별 내용

1️⃣ 기획 (Planning)

요구사항 분석 → 전환 대상/범위 결정 → 마이그레이션 목표 수립

내용

• POC 목적
  • 대규모 DB를 클라우드 전환할 때 성능, 안정성, 가용성, 비용 검증
• 현황 분석
  • DB 종류/규모 (예: Oracle 10TB, PostgreSQL 5TB)
  • 동시 접속량, 트랜잭션 속도, Peak Time 부하 수준
• 마이그레이션 방식 후보
  1. 오프라인 Dump/Restore
     • 장점: 간단, 도구 다양, 무비용
     • 단점: 다운타임 큼, 대용량에 부적합
  2. 온라인 복제(Replication 동기화)
     • Change Data Capture(CDC) 기반
     • 다운타임 최소(분~시간)
     • 예: AWS DMS, Oracle GoldenGate, pglogical
  3. 혼합 방식
     • 초기 Full Load → 이후 변경 데이터만 동기화 → Cutover 전 최종 싱크
• 선정 이유 예시
  • 무중단 또는 최소 다운타임이 필요 → Replication 동기화 방식 선택
  • 배치 데이터 정도만 있으면 다운타임 허용 가능 → 오프라인 Dump 가능

 

2️⃣ 설계 (Design)

목표 아키텍처와 마이그레이션 전략 설계

내용

• 목표 시스템
  • Aurora(MySQL/PostgreSQL 호환), Azure Database, Cloud Spanner 등 비교
• 아키텍처 설계
  • Multi-AZ / DR Region 구성, VPC Peering, Private Link
  • ETL/CDC 서버 별도 구성 여부
• 마이그레이션 방식 선정
  • 대용량 + 무중단 서비스 → 초기 Full Load & CDC Replication
    • 예) AWS DMS(Apache Kafka 연결 가능) + 스키마 변환기(AWS SCT)
    • 변경 데이터는 Binlog(WAL) 기반으로 실시간 반영
• 선정 이유
  • 데이터량이 크고 서비스 중단 불가 → 실시간 동기화로 이중 데이터 유지
  • 테스트 중에도 실제 데이터 동기화 → 검증 기간 확보
• 네트워크 경로
  • 전용 회선(Direct Connect, ExpressRoute) vs VPN
  • 전송 속도 / 보안 규정에 따라 결정
• 성능 목표
  • RPO: 0~3초 (Replication 지연 허용 범위)
  • RTO: Cutover 후 서비스 재개 < 10분

 

3️⃣ 구현 (Implementation)

설계에 맞춰 실제 환경에 구축 및 마이그레이션 진행

내용

• 클라우드 환경 구성
  • 대상 DB 인스턴스(고IOPS 스토리지) 생성
  • Security Group, IAM Role, Encryption 설정
• Full Load 실행
  • 병렬화된 Export/Import 수행 (ora2pg, pgloader, MyDumper 등 활용)
• CDC 기반 Replication 동기화
  • PostgreSQL: pglogical / wal2json
  • MySQL: Binlog 기반 Replication
  • Oracle: GoldenGate 또는 AWS DMS CDC
• 전환 방식
  • "Full Load + 실시간 CDC + Cutover"
  • Cutover 시점: 트래픽 최소 시간대, 애플리케이션 연결변경
• 선정 이유
  • 병행 운전 가능 → 리스크 감소
  • 데이터 재동기화 시간 짧음 → 다운타임 최소화
  • 실시간 검증 가능

 

4️⃣ 검증 (Verification)

전환 데이터와 기존 데이터의 정합성, 성능, 안정성 검증

내용

• 데이터 정합성 검증
  • Hash/SHA256 비교, 샘플링 검증, Row Count 비교
  • DataDiff 툴 및 SQL 스크립트 병행
• 성능 검증
  • 부하 테스트(TPS/QPS 유지 여부)
  • Peak 트래픽 시 레이턴시 측정
• 복구 검증
  • 클라우드 환경에서 PITR(Point in Time Recovery) 테스트
• Replication 지연 모니터링
  • CDC에서 지연 시간 1~2초 이내 유지 여부 확인
• 선정 이유
  • 실시간 동기화 시, 전환 후 바로 정상 서비스 가능하게 하기 위해 데이터 정합성이 필수

 

5️⃣ 평가 (Evaluation)

POC 결과 분석 → 본 전환 여부 결정

내용

• 성능 달성 여부
  • 클라우드에서 TPS, Latency 목표 달성 여부
• 데이터 무결성 평가
  • 전환 후 데이터오류 없음
• 다운타임 분석
  • 목표치(분 단위 이내) 달성 여부
• 비용 분석
  • Replication 툴 사용비용, 네트워크 대역 비용
• 확장성 평가
  • Scale-Out, Cross-Region 복제 가능 여부
• 최종 선정 이유
  • Replication 방식이 안정적으로 동작했고, 데이터 손실 및 다운타임이 최소
  • 운영 자동화 가능, 장애 복구 시나리오 만족

---

📌 마이그레이션 방식 & 선정 이유 요약

방식장점단점적용 적합성선정 이유

Dump/Restore	단순, 무비용	다운타임 큼, 대용량 비효율	데이터소규모, 테스트 목적	소규모/비중요 시스템 전환
Storage Level Migration	빠름, 대량 데이터 안정전송	특정 스토리지 종속	스토리지 동일 Vendor, 대역폭 충분	동일 벤더 스토리지 이전
Full Load + CDC Replication	다운타임 최소, 검증기간 확보	초기 세팅 복잡, 비용	대용량, 무중단 요구	서비스 중단 없이 대량 전환
Log-based Replication	실시간 동기화	툴 라이선스 필요	초저지연 필요	금융·통신 실시간 DB 전환

 

통계정보 수립 전략

통계정보 갱신을 언제 할지 수립을 언제 할건가? : 프로젝트의 어느단계에서 하는게 적절한지? - 초기분석 설계 단계에서 시스템에 따라 전체 갱신할지, 부분 갱신할지 정해야 됨

대부분 DB 의 Default 는

1. 자동 갱신(Auto update) : 데이터의 X% 변경 시 상시 자동 갱신

2 . Sampling 갱신 : 전체 데이터의 일부만 수집 갱신하고, 전체를 대표한다고 가정

ㄴ 통계 갱신되는 동안 생기는 DB Performance Degree 때문

그럼 통계정보 수립전략은 뭐가 제일 좋을까 ? 전통적으로정답이 없는 주제임잦은 통계정보가 SQL 성능의 일관성을 해치는 경우에는자동 + 수동을 혼합한다.

✓Critical Table 은 평상시에는 갱신하지 않고, 정기적으로 최한시 (주 1회) 수동 갱신

✓대량 배치 이후, Mig 이후 통계 수동 갱신

✓통계 갱신 전후 SQL Plan 비교, 성능 비교

 

 

Transaction Isolation level

 트랜잭션 격리 수준 별 특징 표

격리 수준 (Isolation Level)	Dirty Read	Non-Repeatable Read	Phantom Read	설명
READ UNCOMMITTED	✅ 발생 가능	✅ 발생 가능	✅ 발생 가능	커밋되지 않은 변경 사항도 읽을 수 있음 (가장 낮은 수준, 무결성 보장 거의 없음)
READ COMMITTED	❌ 방지됨	✅ 발생 가능	✅ 발생 가능	오라클 기본 수준. 커밋된 데이터만 읽지만, 다른 트랜잭션이 커밋하면 같은 쿼리도 결과가 달라질 수 있음
REPEATABLE READ	❌ 방지됨	❌ 방지됨	✅ 발생 가능 (MySQL InnoDB에서는 거의 방지)	동일 트랜잭션 내 같은 SELECT 결과 보장. MySQL InnoDB MVCC로 대부분 팬텀리드도 방지 (다만 간헐적 발생 가능)
SERIALIZABLE	❌ 방지됨	❌ 방지됨	❌ 방지됨	모든 SELECT에서 공유 잠금 사용, 동시성이 가장 낮고 완전한 일관성 보장

🗂 용어 정리

• Dirty Read
• Non-Repeatable Read
• Phantom Read

암호화

• 패스워드 해시값에 인덱스를 걸면 안됨
• 이 해시값에 단방향 암호화를 하고 이 값을 인덱싱 걸면 좋음

MSA로 대용량 데이터 이관전략

도메인별로 DB를 쪼개서 하나씩 넘김

 

실행계획(오라클)

오라클 실행계획(Execution Plan) 읽는 법

실행계획은 SQL이 실제로 어떻게 실행되는지, 어떤 경로로 데이터를 가져오는지를 보여주는 지도입니다.
EXPLAIN PLAN FOR ... 또는 SQL Developer 의 Autotrace, DBMS_XPLAN.DISPLAY 로 확인 가능합니다.

---

1️⃣ 읽는 순서

• 오른쪽에서 왼쪽, 아래에서 위로
• 각 단계는 Parent → Child 관계로 엮입니다.
• 실제 데이터 처리가 가장 안쪽 → 바깥쪽 순으로 진행됩니다.

---

2️⃣ 주요 컬럼 해석

컬럼의미해석 팁

ID	단계 번호 (계층 구조)	들여쓰기/레벨로 부모-자식 연결 확인
Operation	수행 연산	TABLE ACCESS(FULL/INDEX), JOIN(NESTED LOOP/MERGE/HASH) 등
Name	사용 테이블/인덱스 이름	어떤 객체를 액세스하는지
Rows	옵티마이저 예상 건수	실제와 차이가 크면 통계정보 문제
Bytes	예상 데이터 크기	I/O 부하 예상에 도움됨
Cost	옵티마이저 비용	낮을수록 좋다고 단정할 순 없음, 비교 지표용
Time	예상 수행 시간	통계 정확성에 따라 다름

---

3️⃣ 자주 보는 Operation 타입

타입설명튜닝 포인트

TABLE ACCESS FULL	테이블 풀스캔	인덱스 고려, 통계 갱신
INDEX RANGE SCAN	인덱스 범위 스캔	효율적, 조건절 활용 가능
INDEX UNIQUE SCAN	유니크 인덱스 100% 일치	가장 빠른 액세스
NESTED LOOPS	소량 데이터 Join에 유리	내부 쪽 액세스를 빠르게
MERGE JOIN	정렬된 두 집합을 병합	대량 데이터 중간
HASH JOIN	대량 데이터 Join에 강함	PGA 메모리 크기 중요

---

4️⃣ 해석 예시

----------------------------------------------------------------------------------
| Id  | Operation                    | Name             | Rows  | Cost (%CPU)|
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                  |       |  100  (1) |
|   1 |  HASH JOIN                   |                  |  1000 |  100  (2) |
|   2 |   TABLE ACCESS FULL          | EMP              |  1000 |   50  (2) |
|   3 |   INDEX RANGE SCAN           | DEPT_IDX         |   100 |   20  (1) |
----------------------------------------------------------------------------------

• EMP 테이블 풀스캔
• DEPT 인덱스 범위 스캔 → HASH JOIN으로 결합
• 풀스캔이 많으면 인덱스 적용 여부 검토 & 조건절 재설계 필요

 

AWR (Automatic Workload Repository) Report 분석

AWR 리포트는 성능 문제 원인 파악을 위해 DB 전체 부하와 SQL별 성능 지표를 제공합니다.
@?/rdbms/admin/awrrpt.sql 로 생성 가능.

---

1️⃣ Report 구성 주요 구간 & 해석 포인트

아래는 항목별 해설입니다.

구간주요 항목분석 포인트

1. Report Summary	DB Name, Instance, Snap ID	분석 대상 기간 확인
2. Load Profile	Transactions/sec, Redo size, Logical/Physical Reads	TPS, I/O 부하, 성능 기준선과 비교
3. Instance Efficiency	Buffer Hit %, Library Hit %, Parse %	버퍼 캐시 효율 95% 이상, 파싱률↓
4. Top 5 Timed Events / Foreground Wait Events	DB Wait Event Top 5	CPU 사용률 / 대기 이벤트(PX, I/O, latch) 원인 분석
5. Time Model Stats	DB CPU, SQL Execution	전체 수행 시간 중 어느 부분 비중이 큰지
6. SQL Statistics	Elapsed time, CPU time, Gets, Reads	비효율 SQL 식별 (Top SQL)
7. Segment Statistics	Object별 I/O, Buffer Busy Wait	특정 테이블/인덱스 핫스팟 분석
8. Wait Class Breakdown	Wait Class별 비중	I/O bound인지 CPU bound인지 판단
9. Advisory Sections	Buffer Cache, PGA, SGA Advisory	메모리 크기 튜닝 가이드
10. Init. Parameters	파라미터 설정	성능에 영향 있는 설정 변경 여부

---

2️⃣ 자주 보는 대기 이벤트 예시

대기 이벤트설명원인/개선

db file sequential read	인덱스 I/O	인덱스 효율·분포도 점검
db file scattered read	테이블 풀스캔 I/O	인덱스 적용 검토
log file sync	COMMIT 지연	로그 파일 I/O, commit 빈도 조정
buffer busy waits	블록 경합	핫 블록 분산, 파티션 적용
enq: TX - row lock	DML 경합	트랜잭션 범위 최소화

---

3️⃣ 분석 접근 순서 (실전)

1. Top Timed Events → 대기 이벤트 유형 파악
2. SQL Statistics → 문제 SQL 추출
3. Execution Plan → 실행 경로 확인 및 튜닝 포인트 식별
4. Segment Stats → 특정 오브젝트 핫스팟 확인
5. Advisory → 메모리 튜닝 여부 검토

1️⃣ Report Summary

Snap Id    Snap Time           Sessions   Curs/Sess
------- -------------------   --------   ----------
1001    25-Aug-25 14:00:00          80          3.1
1002    25-Aug-25 14:30:00          85          3.2

해석: 세션 수 변화가 거의 없고, 동시 접속자는 약 80~85명 수준 → 동시 폭증에 의한 부하는 아님.

 

2️⃣ Load Profile

Per Second        Per Transaction
-------------     ----------------
Redo size:       12,345.67      4,200.89
Logical reads:   45,000.22     15,600.11
Physical reads:   2,500.10        870.33
Executions:       1,200.55        500.00

해석:

• Logical read(메모리 논리 I/O)가 평시 20,000/s → 현재 45,000/s 두 배 증가
• Physical read 2,500/s → 디스크 I/O도 평시보다 높음
  ⇒ 메모리 캐시 효율 저하 or 풀스캔 증가 가능성 높음

---

3️⃣ Instance Efficiency %

Buffer Nowait %: 99.85
Buffer Hit %:    86.33
Library Hit %:   98.12
Parse %:          1.23

해석:

• Buffer Hit % 90% 이하 → 캐시 효율 감소
• Parse % 낮음 → 커서 재사용은 잘 되고 있음

---

4️⃣ Top Timed Events

EventWaitsTime(s)Avg Wait(ms)% DB time

db file scattered read	120,000	3,600	30	45%
db file sequential read	80,000	2,400	30	30%
CPU	-	1,500	-	20%

해석:

• db file scattered read → Full Table Scan I/O 증가
• db file sequential read → 인덱스 I/O
• % DB time 45% Full Scan → 대량 테이블 풀스캔이 원인 가능성 매우 높음

---

5️⃣ SQL Ordered by Elapsed Time

SQL_IDElapsed Time(s)ExecutionsElap per ExecSQL Text

9abcxyz	2,800	5	560.0	SELECT ... FROM SALES WHERE trunc(SALE_DATE)=:b1 ...
7defuvw	1,800	20	90.0	SELECT ... JOIN CUSTOMER ...

해석:

• SQL_ID=9abcxyz → 실행 5번밖에 안 했는데 총 시간 2,800초 → 1번당 9분 이상
• 조건절에 TRUNC(SALE_DATE) 사용 → 인덱스 무효화 → 풀스캔 발생
• 고부하 SQL 1~2개가 전체 대기를 끌어올림

---

6️⃣ Segment Statistics (Physical Reads)

TablespaceObj NamePhysical Reads

USERS	SALES	2,000,000
USERS	CUSTOMER	500,000

해석:

• SALES 테이블 → 디스크 읽기 I/O 대부분 차지
• 해당 테이블 대상 SQL이 이번 부하 원인임을 확정

---

7️⃣ 종합 진단

1. 특정 배치 SQL에서 TRUNC(SALE_DATE) 함수 사용으로 인덱스 스킵 → 풀스캔 발생
2. SALES 테이블 풀스캔으로 Logical Read, Physical Read 모두 폭증
3. Buffer Hit Ratio 감소, db file scattered read 대기 급증

---

8️⃣ 개선안

• TRUNC(SALE_DATE) 제거 후 바인드 범위 조건 사용
  sql

  -- 개선 전 WHERE TRUNC(SALE_DATE) = :b1 -- 개선 후 WHERE SALE_DATE >= :b1 AND SALE_DATE < :b1 + 1
• SALES 테이블 DATE 컬럼 기반 인덱스 확인 및 필요 시 생성
• 통계정보 최신화 (DBMS_STATS.GATHER_TABLE_STATS)
• 배치 운영 시간대 분리로 온라인 트래픽과 I/O 경합 최소화