SQL 수행 구조
데이터베이스 아키텍처
1. 데이터베이스와 인스턴스의 정의
오라클에서는 디스크에 저장된 데이터 집합(DATAFILES, REDO LOG FILES, CONTROLFILES)를 데이터베이스, SGA 공유 메모리 영역과 이를 엑세스하는 프로세스 집합을 인스턴스라 칭함.
2. 오라클 백그라운드 프로세스 설명
- SMON : System Monitor, 장애 발생 시스템 재기동 시 인스턴스 복구 수행/임시 세그먼트,익스텐트 모니터링
- PMON : Process Monitor, 이상이 생긴 프로세스가 사용하던 리소스 복구
- DBWn : Databse Writer, 버퍼캐시에 있는 Dirty 버퍼를 데이터파일(디스크)에 기록
- LRWR : Log Writer, 로그 버퍼 엔트리를 Redo 로그 파일(디스크)에 기록
- ARCn : Archiver, 꽉찬 REDO 로그 덮어쓰여지기 전에 아카이브 로그 디렉토리로 백업
- CKPT : Checkpoint,
- RECO : Recoverer, 분산 트랜잭션 문제 발생 시 해결
3. 데이터 저장 구조에 대한 설명
- 블록(페이지) : 데이터를 읽고 쓰는 단위
- 익스텐트 : 공간을 확장하는 단위
- 세그먼트 : 익스텐트 집합 (연속할 필요는 없음, 두개 다른 데이터파일에 있는 익스턴트가 하나의 세그먼트로 구성 될 수 있음)
- 테이블스페이스 : 세그먼트를 담는 컨테이너로, 여러 데이터파일로 구성
4. 테이블스페이스 - 세그먼트 - 익스텐트 - 블록
5. 오라클 UNDO 사용하는 목적
- Transaction Rollback
- Transaction Recovery
- Read Consistency
6. 오라클 REDO 로그 설명
- Online Redo 로그는 최소 두개 이상 파일로 구성되어야함.(번갈아 사용해서 차면 archive에 떨굼)
- 로그 스위치 주기는 빠를 수록 좋다? (X)
스위치가 많이 일어나 백업되지 못한 ONLINE REDO에 스위칭 일어나면 DB HANG 발생 할 수 있음.
- 로그 스위치 주기는 빠를 수록 좋다? (X)
- FAST COMMIT : COMMIT 후 실시간으로 디스크에 반영되면 부하 유발함. 따라서 REDO LOG 파일에만 APPEND로 정확하게 기록 후 커밋을 완료함(실제 데이터파일에 변경은 배치로 일괄 작업됨)
- Database Recovery(=Media Recovery)/Cache Recovery/Fast Commit
7. REDO 메커니즘
- FAST COMMIT
- WRITE AHEAD LOGGING : 버퍼캐시 블록 갱신 전에 먼저 REDO 엔트리를 로그버퍼에 기록, DBWR가 일하기 전에 LGWR가 REDO 로그버퍼를 디스크에 기록했음이 보장되어야 함.
- LOG FORCE AT COMMIT : 메모리상에 로그 버퍼는 유실 될 수있으니, 최소한 트랜잭션종료(커밋) 시 로그를 데이터파일에 기록해야함.
8. 오라클 SGA 구성요소
- SGA?
SYSTEM GLOBAL AREA의 약자로
DB BUFFER CACHE / REDO LOG BUFFER / SHARED POOL(Library Cache, Dictionary Cache) 로 구성
9. 오라클의 메모리 캐시에 대한 설명
- DB버퍼캐시 : 테이블/인덱스/UNDO 블록 캐싱
- 라이브러리캐시 : SQL, PL/SQL, FUNCTION, 트리거 등 캐싱
- 딕셔너리 캐시 : 테이블/인덱스/데이터파일 정보, 시퀀스 캐싱
- RESULT 캐시 : SQL 결과집합 캐싱
10. 버퍼캐시 블록과 데이터파일 블록간 동기화가 필요한 블록?
- Dirty 버퍼 : 버퍼 캐시에서 변경이 발생했지만, 아직 데이터파일에 기록되지 않아 동기화가 필요한 버퍼블록
- Free 버퍼 : 비어있거나 재사용가능한 버퍼
- Pinned 버퍼 : 읽기/쓰기 작업을 위해 액세스 되고있는 버퍼 블록
SQL 처리 과정
11. SQL의 정의
- SQL : Structured Query Language, Set-based, declarative
12. SQL 처리 과정 중 문법, 의미상 오류 체크하는 단계
SQL 파싱
13. SQL 처리 과정 중 데이터 딕셔너리에 수집해둔 토계정보 활용하는 단계
SQL 최적화
14. SQL 최적화 단계에서 옵티마이저가 최종 하나를 선택하는 궁극의 기준
예상 비용 COST
15. 오라클 옵티마이저 힌트에 대한 설명
옵티마이저 힌트는 명령어(Directives)이므로 특별한 이유 없는 한 그대로 실행됨.
16. 옵티마이저 힌트가 무시되는 경우
- 문법적으로 맞지 않게 힌트 기술하거나 잘못된 참조
- 논리적으로 불가능하거나 의미적으로 맞지 않게 힌트 기술
- 옵티마이저에 의해 내부적으로 쿼리가 변형된 경우
참고로 INDEX(A) 하면 A에 어떤 인덱스 쓸지 옵티마이저가 선택함?????
17. 아래 옵티마이저는 힌트 잘 동작함
/*+ INDEX(E, EMP_X01) INDEX(D DEPT_X03) */
18. 옵티마이저 힌트 간략 정리
데이터베이스 I/O 메커니즘
19. 블록 단위 I/O 하지 않는 오퍼레이션
- 테이블/컬럼 정보를 딕셔너리 캐시에 적재할때 (ROW CACHE로 ROW 단위 I/O)
20. 흠..
22. 버퍼캐시 히트율
- BCHR : (논 / 전체) 100 = (전체 - 물리 / 전체) 100 = (1- (물/전체)) *100
27. 데이터베이스 I/O원리에 대한 설명
- 한쿼리가 같은 블록을 반복해서 액세스 하면 버퍼캐시 히트율은 높아짐
- Multiblock I/O는 한번에 I/O 콜로 여러 데이터 블록을 읽어 메모리에 적재하는 방식
- 테이블 FULL SCAN 시, 작은 익스텐트로 구성되어있다면 I/O CALL이 늘음
→ Multiblock I/O라도 익스텐트 하나에 대해서만 I/O함. 익스텐트 경계를 넘지못함.
28. 병렬쿼리 자주 사용하면?
CPU 메모리 자원을 많이 사용하고, 잦은 체크포인트 수행으로 LGWR 작업량이 증가해 커밋 성능 지연되어 온라인 트랜잭션 처리에 나쁜 영향 줄 수 있음.
postgreSQL Mysql MariaDB