헝그리개발자

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

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)
- 데이터베이스 복구 시 사용




데이터 저장 구조

버퍼 블록의 상태

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

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

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



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

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

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 크기가 큰 데이터를 저장하는 메모리