Make a Real DBA 오라클 데이터베이스 관리시스템 아키텍처 ㈜ 신한시스템즈 김 종 근

오라클 DBMS 아키텍처 DBMS 구성요소 개요 메모리 구조 SQL 구문 처리 구조 백그라운드 프로세스 Q & A

DB DBMS 구성요소 개요 DBMS란? 데이터를 저장하고 관리해주는 백엔드 소프트웨어 DBMS 구성요소 개요 ‘김종근’ •••••• INSERT DB 이석창 ‘송호섭’ ‘박동진’ UPDATE 김종근 박동진 송호섭 문찬섭 ‘이석창’ DELETE 김종근 박동진 문찬섭 ‘*’ SELECT

DB DBMS D B M S DBMS란? Physical Disk DBMS란? 데이터를 저장하고 관리해주는 백엔드 소프트웨어 ‘김종근’ •••••• INSERT DB D B M S R e q P u r e o s c t e s r 이석창 ‘송호섭’ ‘박동진’ UPDATE 김종근 Physical Disk 박동진 송호섭 문찬섭 ‘이석창’ DELETE 김종근 박동진 문찬섭 ‘*’ SELECT

DB DBMS DBMS의 두 구성 요소 engine Software & Repository Physical Disk 김종근 문찬섭 이석창 송호섭 박동진 Physical Disk DBMS engine Software Repository

DB Fast Processing? Client Request Processing Oracle Process Server DBMS 구성요소 개요 Fast Processing? Client Request Processing Server Machine DB Oracle Process Client Physical Memory Physical Disk

DB Fast Processing? Cache & Processes Server Machine Oracle Oracle DBMS 구성요소 개요 Fast Processing? Cache & Processes Server Machine DB Oracle Process Oracle Process Client Data Cache Area Physical Memory Physical Disk

DB 오라클의 두 구성 요소 Instance Database Instance and Database SGA Server DBMS 구성요소 개요 오라클의 두 구성 요소 Instance and Database Server Machine Instance Database DB Server Process Background Process Client SGA System Global Area Physical Memory Physical Disk

DB Instance 구성 요소 SGA & Background Process SGA Server Machine Instance 메모리 구조 Instance 구성 요소 SGA & Background Process Server Machine Instance Database CKPT DBWR LGWR PMON SMON DB Server Process Client SGA Physical Memory Physical Disk

SGA 구조 SGA(System Global Area) Shared Pool Database Buffer Cache 메모리 구조 SGA 구조 SGA(System Global Area) Shared Pool Database Buffer Cache Redo Log Buffer SHARED_POOL_SIZE DB_BLOCK_SIZE DB_CACHE_SIZE LOG_BUFFER

- 데이터베이스 딕셔너리 정보를 캐쉬하는 영역 메모리 구조 Shared Pool Dictionary Cache - 데이터베이스 딕셔너리 정보를 캐쉬하는 영역 Shared Pool Library Cache Dictionary Cache - 딕셔너리 정보? 데이터베이스에 대한 메타데이터 예) 사용자, 테이블, 인덱스 등에 대한 정보

Shared Pool Library Cache - SQL 및 PL/SQL 코드 캐시 영역 메모리 구조 Shared Pool Library Cache - SQL 및 PL/SQL 코드 캐시 영역 Shared Pool Library Cache Dictionary Cache - 같은 쿼리에 대한 재분석 회수를 줄임으로 해서 성능을 증대 - SQL, P-CODE, E-PLAN을 같이 저장 - 사용상의 고려 사항 ① SELECT * FROM emp; ② Select * from emp; ※ 실행 결과는 같지만, Library Cache내에서는 다른 구문으로 판단

Database Buffer Cache DB Buffer Cache - 사용자가 사용하는 데이터를 캐시 DB 버퍼 블럭 메모리 구조 Database Buffer Cache DB Buffer Cache Database Buffer Cache - 사용자가 사용하는 데이터를 캐시 DB 버퍼 블럭 오라클의 최소 I/O단위, 생성시 결정 - 버퍼의 3가지 상태 현재 액세스 중인 버퍼 Pinned Buffer 한 번도 사용되지 않았거나, 사용은 되었지만 데이터파일의 상태와 같은 버퍼 Free Buffer DML로 인해 데이터가 변경되고 아직 기록되지 않은 버퍼 Dirty Buffer

Database Buffer Cache Reusable Buffer? 메모리 구조 Database Buffer Cache Reusable Buffer? Database Buffer Cache - LRU(Least Recently Used) Algorithm 최근에 사용된 것이 다시 사용될 확률이 높다! - 구현? LRU List라는 버퍼에 대한 리스트 구조를 관리 Reusable Buffer Search 3 2 4 1 5 MRU LRU 3 2 4 1 5 3 2 4 1 5

Redo Log Buffer Redo Log Buffer 메모리 구조 Redo Log Buffer Redo Log Buffer Redo Log Buffer - DB Buffer Cache의 변경 사항(Redo Entry) 을 기록하는 영역 - 순환 버퍼 구조로 사용 - 버퍼의 내용은 리두 로그 파일로 기록됨

SGA 구조 SGA 관련 파라미터 메모리 구조 파라미터 설명 동적변경 SGA_MAX_SIZE SGA 전체 최대 사이즈(9i) (이 범위 내에서 동적 리사이징 가능) N SHARED_POOL_SIZE Shared Pool의 사이즈 Y DB_CACHE_SIZE 표준 DB Buffer Cache 사이즈 DB_nK_CACHE_SIZE 비표준 블록에 대한 Cache 사이즈(9i) LOG_BUFFER Redo Log Buffer 사이즈 LARGE_POOL_SIZE Large Pool 사이즈 JAVA_POOL_SIZE Java Pool 사이즈

Database 구성 요소 Database 구성 파일 DBMS 구성요소 개요 Control file Data file Redo log files Database Parameter file Password file Archived log files Alert file Trace file

Database 구성 파일 Data File Redo Log File Control File DBMS 구성요소 개요 Database 구성 파일 Data File - 사용자 데이터 및 시스템 데이터(딕셔너리)가 기록되는 파일 - 최소 하나 이상의 파일을 사용하며, 용도 및 데이터 성격에 따라 여러 개의 파일로 구분하여 사용. Redo Log File - 데이터베이스의 변경 내용(트랜잭션 정보)이 기록되는 파일 - 최소 2개의 그룹을 구성하여 운영 Control File - 데이터베이스 구성 파일들에 대한 정보 파일 - 데이터베이스 명, 동기화 정보 등을 기록

기타 파일 Parameter File Alert File Trace File Password File DBMS 구성요소 개요 기타 파일 Parameter File $ORACLE_HOME/dbs/init<SID>.ora BACKGROUND_DUMP_DEST/alert_<SID>.log *_DUMP_DEST/ora_<PNAME>_<PID>.trc $ORACLE_HOME/dbs/orapw<SID> LOG_ARCHIVE_DEST/* - 인스턴스의 성격을 결정하는 파라미터들을 파일로 만들어 놓은 파일 Alert File - 오라클의 주 메시지 파일 Trace File - 에러 로그 및 기타 추적 정보에 대한 상세 파일 Password File - Sysdba 계정에 대한 패스워드 기록 파일 Archive Log File - Online Redo Log 파일을 백업한 파일

ORACLE RDBMS Structure Shared Pool Database Buffer Cache Redo Log Buffer CKPT DBWR LGWR PMON SMON etc Instance Data file Control file Redo log files Database Parameter file Password file Archived log files Alert file Trace file

SQL문 처리 구조(1) Parsing Instance Database SQL Statement Process – syntax check - semantic check - security check - execution planning Shared Pool Database Buffer Cache Redo Log Buffer CKPT DBWR LGWR PMON SMON Instance SQL Client Server Process SELECT ename, job FROM emp; Data file Control file Redo log files Database

SQL문 처리 구조(2) Execute(DML) Instance Database SQL Statement Process Shared Pool Database Buffer Cache Redo Log Buffer CKPT DBWR LGWR PMON SMON Instance SQL Client Server Process Result SELECT ename, job FROM emp; Data file Control file Redo log files Database

SQL문 처리 구조(3) Fetch(SELECT) Instance Database SQL Statement Process Shared Pool Database Buffer Cache Redo Log Buffer CKPT DBWR LGWR PMON SMON Instance SQL Client Server Process Result SELECT ename, job FROM emp; Data file Control file Redo log files Database

SQL문 처리 구조 Server Process Instance Database SQL Statement Process CKPT Shared Pool Database Buffer Cache Redo Log Buffer CKPT DBWR LGWR PMON SMON Instance SQL Client Server Process Result SELECT ename, job FROM emp; Data file Control file Redo log files Database

Log Writer Process Log Writer Process LGWR 프로세스는 리두 로그 버퍼의 연속적 일부분을 Background Process Log Writer Process Log Writer Process LGWR 프로세스는 리두 로그 버퍼의 연속적 일부분을 온라인 리두로그 파일로 기록하는 프로세스이다. • 커밋과 동시에 커밋된 데이터에 대해서는 무조건 기록한다. • 커밋이 일어나지 않더라도 다음의 경우에 로그 버퍼의 내용이 기록된다. - 매 3초마다. - 리두 로그 버퍼의 1/3이상이 기록되었을 때 - DBWR 프로세스가 변경된 블록을 기록하기 전에(필요시) Instance Shared Pool Database Buffer Cache Redo Log Buffer SQL Client Server Process Result PMON SMON CKPT DBWR LGWR Data file Control file Redo log files Database

Database Writer Process Background Process Database Writer Process Database Writer Process DBWR 프로세스는 DB 버퍼 캐시의 더티 버퍼를 기록하는 프로세스이다. • 서버 프로세스가 재사용 가능한 버퍼블럭을 찾을 수 없을 경우 • 체크포인트(checkpoint )를 진행시키기 위해 정기적으로 기록한다. Instance Shared Pool Database Buffer Cache Redo Log Buffer SQL Client Server Process Result PMON SMON CKPT DBWR LGWR Data file Control file Redo log files Database

Checkpoint Process Checkpoint Process Background Process Checkpoint Process Checkpoint Process CKPT 프로세는 모든 데이터 파일의 헤더 정보를 업데이트하고 컨트롤 파일에 체크포인트에 대한 상세 정보를 기록하는 프로세스이다. • 체크포인트가 발생하는 시점 - 로그 스위치 발생 시 - abort 옵션을 제외한 데이터베이스 종료시 - ALTER SYSTEM CHECKPOINT; 커맨드에 의해 - 초기화 파라미터 설정값에 의한 시점에 Instance Shared Pool Database Buffer Cache Redo Log Buffer SQL Client Server Process Result PMON SMON CKPT DBWR LGWR Data file Control file Redo log files Database

System Monitor Process Background Process System Monitor Process Instance System Monitor Process • 인스턴스 시작시 인스턴스 복구를 수행한다.(필요시) • 사용되지 않는 임시 세그먼트의 정리작업을 수행한다. • 딕셔너리 관리 테이블스페이스에 대해 free extent들의 병합작업을 수행한다. Shared Pool Database Buffer Cache Redo Log Buffer SQL Client Server Process Result PMON SMON CKPT DBWR LGWR Data file Control file Redo log files Database

Process Monitor Process Background Process Process Monitor Process Instance Process Monitor Process • 사용자 프로세스의 실패시 프로세스 복구를 수행한다. • 사용자 프로세스가 사용하던 자원에 대한 할당 해제 및 버퍼 캐시의 정리를 수행한다. Shared Pool Database Buffer Cache Redo Log Buffer SQL Client Server Process Result PMON SMON CKPT DBWR LGWR Data file Control file Redo log files Database

Archiver Process Archiver Process Instance Background Process Archiver Process Instance Archiver Process ARCH 프로세스는 아카이브로그 모드로 운영하는 데이터베이스에 대해 온라인 리두 로그 파일을 지정한 저장소로 백업하는 프로세스이다. - 파일 아카이빙은 로그 스위치와 동시에 시작한다. Shared Pool Database Buffer Cache Redo Log Buffer SQL Client Server Process Result PMON SMON CKPT DBWR LGWR ARCH Data file Control file Redo log files Database Archived log files

ORACLE RDBMS Architecture Instance Shared Pool Database Buffer Cache Redo Log Buffer SQL Client Server Process Result PMON SMON CKPT DBWR LGWR ARCH Data file Control file Redo log files Database Archived log files

데이터베이스 시작과 종료 OPEN STARTUP MOUNT SHUTDOWN NOMOUNT SHUTDOWN SQL> startup ORACLE instance started. Total System Global Area 30901048 bytes Fixed Size 729912 bytes Variable Size 25165824 bytes Database Buffers 4194304 bytes Redo Buffers 811008 bytes Normal Operation OPEN Data files and Log files open Database mounted. STARTUP MOUNT Database opened. SHUTDOWN SQL> Control file open NOMOUNT Instance create Init file open SHUTDOWN STARTUP NOMOUNT ALTER DATABASE MOUNT; ALTER DATABASE OPEN;

감사합니다

Q & A