[SQLP 핵심노트] 3과목. SQL 고급활용 및 튜닝 - 1. SQL 수행 구조

시험을 준비하면서 핵심적인 부분만 노트를 하여 공개한다.

 

SQLP : 제3과목은 SQL 고급활용 및 튜닝 이다.

 

 

3과목. SQL 고급활용 및 튜닝

1. SQL 수행 구조
2. SQL 분석 도구
3. 인덱스 튜닝
4. 조인 튜닝
5. SQL 옵티마이저
6. 고급 SQL 튜닝
7. Lock과 트랜잭션 동시성 제어

 

 

 

 

 

데이터베이스
- 물리적인 OS 파일의 집합 (데이터파일, Redo Log File, Control FIle 등)

인스턴스
- SGA 공유 메모리 영역과 이를 액세스하는 프로세스 집합

SQL 언어 특징
- 구조적 (Structured) 집합적 (Set-based) 선언적 (Declarative) 언어이다.
- 절차적 (Procedural) 언어는 아니다 : PL/SQL, T-SQL에서 구현한다.



백그라운드 프로세스

System Monitor (SMON)
- 인스턴스를 관리, 장애 발생 시 인스턴스 복구하고, 임시 세그먼트와 익스텐트를 모니터링한다.

Process Monitor (PMON)
- 프로세스를 감시, 이상이 생긴 프로세스의 리소스를 복구

Database Writer (DBWn)
- 버퍼캐시에 있는 Dirty 버퍼를 파일에 기록

Log Writer (LGWR)
- Redo 로그 버퍼를 Redo 로그 파일에 기록

Archiver (ARCn)
- Redo 로그가 꽉 차면, Archive Log 파일로 백업한다.

Checkpoint (CKPT)
- 데이터베이스 변경사항인 버퍼를 데이터 파일로 저장하는 것을 보장

Recoverer(RECO)
- 데이터베이스 복구 시 사용




데이터 저장 구조

 

블록(페이지)	데이터 I/O의 최소단위, 블록 단위로 읽어들인다.
익스텐트	테이블 스페이스로부터 공간을 할당받는 단위 논리적으로 인접한 블록의 집합
세그먼트	익스텐트의 집합 하나의 테이블(비파티션 테이블)은 하나의 세그먼트이다. 테이블 세그먼트, 인덱스 세그먼트가 있다.
테이블 스페이스	세그먼트를 담는 컨테이너로, 여러 데이터 파일로 구성된다.

버퍼 블록의 상태

Free 버퍼
- 데이터가 읽히지 않아 비어 있는 상태

DIrty 버퍼
- 버퍼캐시에 적재된 이후 변경이 발생했지만, 데이터파일에 기록되지 않은 상태

Pinned 버퍼
읽기 또는 쓰기 작업을 위해 현재 액세스 되고 있는 버퍼 블록



Undo 로그
- Transaction Rollback 시 사용
- Transaction Recovery
- Read Consistency

Redo 로그
- Database Recovery
- Cache Recovery
- Fast Commit

 

Redo 메커니즘

 

Log Force at commit	트랜잭션의 영속성을 위해, 적어도 커밋 시점에는 Redo 로그를 데이터 파일에 기록한다.
Fast Commit	Redo 로그를 믿고, 빠르게 커밋을 완료할 수 있다.
Write Ahead Logging	Dirty 블록을 데이터 파일에 기록하기 전에, Redo 엔트리를 Redo 로그 파일에 기록했음이 보장되야 한다.

메모리 영역

SGA (System Global Area)
- 공유 메모리 그룹, 모든 서버 및 백그라운드 프로스세에 공유된다.
- Shared Pool, 데이터베이스 버퍼캐시, Redo 로그 버퍼, 딕셔너리 캐시

PGA (Program Global Area)
- 비공유 메모리 구조 그룹
- 한 프로세스 혹은 스레드의 개별적인 메모리 공간
- 정렬 공간 (Sort Area), 세션 정보 (Session Information), 커서 상태 정보(Cursor State), 변수 저장 공간 (Stack Area)

UGA (User Global Area)
- 로그온 정보나 세션이 필요로 하는 정보를 저장하고 있는 세션 메모리
- Shared Server의 경우 SGA 에 존재
- Dedicated Server일 경우  PGA 내부에 존재

CGA (Call Global Area)
- 데이터베이스 콜(parse, execute, fetch)에 필요하고, 끝나면 해제되는 데이터를 담고 있다.



Single Block I/O
- 기본적인 인덱스 사용 시 단일 블록으로 읽어들인다.

Multi Block I/O
- 인접한 블록을 함께 읽어 들인다.
- 한 익스텐트 경계를 넘지는 못한다.
- Index Fast Full Scan, Table Full Scan 에서 다중 블록으로 읽어들인다.

Direct Path I/O 는 버퍼 캐시를 경유하지 않는다.
(병렬 프로세스로 테이블을 Full Scan)

 

LRU 알고리즘
- 가장 오래전에 사용된 데이터 블록을 버퍼캐시에서 밀어내는 알고리즘
- 사용빈도가 높은 데이터 블록이 버퍼캐시에 오래 남아 있도록 한다.


Shared Pool

빠른 Parsing을 위해 Hard Parsing을 줄이고 Soft Parsing의 비율을 높여 Oracle Database의 성능을 높히는 것

Soft Parsing

- Library Cache에 저장되어 있는 SQL문을 수행하여 Parse 작업없이 기존에 있는 실행계획대로 실행

 

Hard Parsing

- Parsing 작업을 수행하여 SQL문 확인 및 실행계획을 새로 생성함

 

Library Cache

- Parsing 정보를 저장하는 메모리

- Oracle Instance SGA Shared Pool 영역에 존재
- SQL 과 실행계획, PL/SQL 프로그램(프로시저, 함수, 트리거 등) 캐싱

 

Data Dictionary Cache

- Parsing에 필요한 Data Dictionary 정보를 저장하는 메모리

Result Cache

- Parsing 없이 결과를 확인할 수 있도록 Query의 결과값만 저장하는 메모리

Reserved Pool

- Parsing 크기가 큰 데이터를 저장하는 메모리