Oracle DB 기본 관리 - 01. 오라클 데이터베이스 구조 탐색

■ Real Application Cluster

  - 오라클의 고가용성을 위한 cluster 구성

  - 여러 인스턴스를 묶어서 하나의 데이터베이스에 연결해서 하나의 인스턴스처럼 사용

■ Connection vs Session

  - Session : connection 중 특정 연결

 

■ 메모리 구조

1) SGA (Share Global Area, System Global Area)

  - Shared pool 

    . Library cache : Parse 할 때 사용했던 P_code &실행계획 저장

    . Dictionary cache : Parse할 때 사용했던 데이터 딕셔너리 캐시 저장

  - DB buffer cache

    . 데이터 파일에서 읽은 Block 저장

  - Redo log buffer

    . 데이터 변경 사항 저장

    . Select을 제외한 모든 작업을 기록

2) PGA (Private Global Area, Program Global Area)

 

■ SQL 처리 과정 : Parse (complie) → Execute → Fetch

 

1. SELECT

select ename, sal
from emp
where empno = 7788;

 

1) Parse

  ① Syntax : 문법이 올바른지 확인

  ② Semantic & Prviliege : 테이블, 컬럼이 생성되어 있는지, 해당 테이블에 권한이 있는지 확인

  ③ P_code + 실행계획 : 컴파일된 코드 값이 만들어지고, SQL 수행을 위한 비용 최적화 진행

  ※ Hard parse : ① & ② & ③ 수행

  ※ Soft parse : Library cache에 실행계획 저장되어 있는 경우, ① & ②만 수행 (③ 생략하므로 굉장히 가벼워진다!)

2) Execute

  - SQL문 수행에 필요한 데이터를 DB buffer cache에 기록하는 작업

  - 필요한 block이 DB buffer cache에 없다면 data file로부터 데이터를 읽어 메모리에 기록 → physical I/O로 인한 성능 저하

  - DB buffer cache에 올라가게 되면 완료된 것

3) Fetch : Select 할 때만 수행하는 작업

 

2. UPDATE 

update enp
set = sal*1.1
where empno = 7788;

1) Parse

  - update문이 실행된 적 없으므로 hard parse 수행

2) Execute

  ( ! 모든 작업은 메모리에서 수행된다 )

  ① DB buffer cache에 block 있는지 확인 (없다면 기록)

  ② 변경 작업 취소를 대비하여 undo block에 있는지 확인 (없다면 현재 buffer 상태 기록)

  ③ redo log buffer에 update문 기록

  ④ DB buffer cache에서 데이터 변경 (commit, rollback 가능한 상태, 변경 작업은 완료되었으나 반영된 것은 아닌 상태를 DB buffer cache에 기록함)

예시1) rollback 하는 경우

  - redo log buffer에 rollback 기록 (어떤 변경 작업에 대한 rollback인지까지)

  - buffer cache 데이터 다시 변경

예시2) commit 하는 경우

  - redo log buffer에 commit 기록

  - redo log file에 기록 *중요* (LGWR가 기록해준다, 언제? commit할 때)   

 

-------------- 나중에 정리 ---------------

 

    - 만약 redo가 없으면?

    - 데이터 파일로부터 동일 블럭을 읽어와서 메모리 내 동일 블럭이 다른 데이터로 존재

    - 메모리 저장 공간 관리를 위해 LRU 알고리즘으로 첫번째 블럭(1100)이 데이터에 저장되고 두번째 블럭(1000)만 메모리에 남음

    - 데이터 정합성을 위해 메모리 내 동일 블럭은 존재할 수 없음

    - 변경되기 전 데이터를 전문적으로 저장함 : undo data file에 저장

    - 메모리에도 undo block 저장

   

    ④-2 commit 하는 경우,

        데이터 파일에 바로 기록하지 않음 (why? block 단위 I/O이고 정확한 위치에 저장해야 하는데 동일 block 내 commit 또 발생 시 physical I/O 발생 이것은 성능 저하)

        commit 보장을 위해 데이터 파일에 바로 저장하지 않음

        대신 redo log buffer를 redo log 파일에 저장하는 것으로 commit 보장해줄게

        redo log buffer에 commit 기록

        redo log file에 기록 *중요* (LGWR가 기록해준다, 언제? commit할 때)     

  

예시) Update (1000→1100) & Update (A→b) & commit 을 싣고 redo log file에 기록하고, redo log buffer는 지워진다

만약 DB 장애나서 재기동 한 경우,

commit한 데이터가 아직 data file에 반영되지 않은 상태

SMON이 비정상 종료인지 감지하여 redo log file을 읽어서 data file을 복구 (인스턴스 리커버리라고 함)

명시적인 commit이 없으면 rollback (약속임)

 

시나리오: select문 -> update (1000->1100) -> rollback -> update(1000->1100) -> commit  -> update(A->b) -> DB 장애 -> 재기동

 

-------------- 나중에 정리 ---------------

 

■ 백그라운드 프로세스

1) DBWR

  - DB buffer cache에 변경된 block을 데이터 파일에 기록하는 역할

  - 언제? 대표적으로 DB buffer cache가 가득 차게 되면 오래된 블럭부터 데이터 파일에 기록 (그외에도 있음)

2) LGWR

  - redo log buffer를 redo log file에 기록하는 역할

    . commit 할 때

    . log switch : redo log file을 다 쓰고 다음 redo log file으로 넘어갈 때

    . redo log buffer가 1/3 찼을 때

    . DBWR가 disk에 변경된 block을 쓰기 전에

    . 마지막 쓰기 작업 후 3초가 지났을 때

3) CKPT는

  - checkpoint event가 발생하게 되면 DBWR에게 디스크에 기록하도록 알리고 DBWR는 작업 완료 후에 CKPT에 알려준다

  - control file과 data file의 header에 CSN을 기록

  - checkpoint란, 메모리에 있는 buffer block의 내용과 disk의 data block간의 내용을 맞추는 것

  - 목적: 장애로 인한 DB 재기동 시 빠른 복구를 위한 것

    . DB 장애

    . DB 재기동

    . Instance Recovery : Redo log file을 순차적으로 읽어서 복구 시도하며 checkpoint 이후의 변경사항에 대해서만 진행하면 됨

  ※ checkpoint event : DBWR가 buffer cache 내의 변경된 모든 block을 datafile에 기록하는 event

4) SMON

  - 인스턴스 시작 시 redo log file을 읽어서 복구 수행 (비정상 종료 시)

5) PMON

  - User Process ↔ Server Process 연결을 모니터링 

  - 비정상적인 종료를 확인하여 변경 작업들을 rollback 시킨다 (rollback 시키지 않으면 다른 user들은 해당 block에 접근 불가, wait)

6) RECO

  - 분산 데이터베이스 환경에서 사용

  - 기본적으로 기동 되나 분산 데이터베이스 환경이 아니면 사용하지 않음

  - 예시) H1의 prod DB, H3의 orcl DB 있음. 나는 prod DB에 접속한 상태에서 H1↔H3의 db_link 통해 H3의 orcl DB 접근 가능

select *
from emp @ {db_link name};

7) LREG

  - static registration : 리스너에게 DB 정보를 수동으로 입력

  - dynamic registration : DB가 리스너에게 DB 정보를 자동으로 등록

8) ARCn

  - DB 운영 mode : redo log file 재사용과 관련 있음

    ① Noarchivelog (기본)

      - redo log file을 재사용하기 위해서는 변경 내용이 DB buffer cache->Data file로 반영되었는지 확인 필요

      - 즉, checkpoint event가 발생해야 재사용

      - 그러나 복구 불가 (redo log file을 계속해서 재사용하므로 이전 변경 내용이 저장되어 있지 않음)

    ② Archivelog 

      - 복구를 대비하여 Redo log file의 복사본 생성

 

■ PGA

  - 세션 별 작업 공간

  - sort 작업 등과 같이 개별 작업은 PGA에서 수행

select * 
from sales 
where sales_dt between '2002-03-01' and '2002-03-31' 
order by cust_city;

  - Stack space

  - Cursor state: P_code 상태를 저장 (P_code or cursor or parse tree)

  - User session data: DB 레벨 parameter 중에서 작업 동안 나만의 session parameter 변경 값을 저장

  - SQL work area (튜닝 가능한 영역)

    . sort_area_size, hash_area_size, create_bitmap_merge_area_size 등으로 관리

    . 지금은 pga_aggregate_target으로 PGA 전체 사이즈만 설정하여 SQL work area는 사용자에 맞게 유연하게 사용하도록 함 

    . 예시) 1GB중 200M를 stack, cursor, user로 사용하고 있음 (사용자 수에 비례), 나머지 800M는 SQL work area로 사용

 

※ DB 튜닝은 2가지 : SQL 문장 튜닝 & 데이터베이스 서버 튜닝

 

■ In-Memory Column Store

  - SELECT 작업을 빠르게 하기 위해

  - 컬럼의 개수는 적으면서 행 건수는 많은 경우에 적합 (DW 환경)

  - raw store format라면, 컬럼의 개수는 적으면서 행 건수는 많은 경우라면 불필요한 행도 다 읽어야 해

  - 메모리로 읽어올 때 열 형식으로 변환

 

■ Grid Infrastructure

  - cluster 구성을 위한 clusterware + ASM으로 구성

  - RAC 구성이라면 cluster 환경의 daemon 프로세스가 인스턴스, 리스너 등을 자동으로 재기동

 

■ 데이터베이스 저장 영역 구조

* 데이터베이스 파일 : 항상 접근 가능해야 한다

  1) Data file : 데이터를 저장하는 파일

    - Block : 데이터 처리하는 최소 단위

  2) Redo log file : 리두로그 버퍼를 저장하는 파일, 복구 작업을 위해.

  3) Control file : 데이터베이스의 제어 정보를 가지고 있는 파일

* 기타 등등

  - parameter file, backup file, archived redo log file, trace file 등

  - password file : 일반적인 password는 데이터 딕셔너리에 저장, 단 sysdba는 password file에 저장하고 처음 접속 시 sysdba는 비밀번호 접속이 설정되어 있지 않음. 운영 시 비밀번호를 묻는 설정으로 변경해야 함.

  - alert log file : 인스턴스 마다 있는 경고성 로그 파일 (장애 시 해당 파일을 먼저 확인해야 함)

 

■ 논리적 및 물리적 데이터베이스 구조

  - 물리적 구조 : datafile

  - 논리적 구조 : tablespace > segment > extent > block

    . create 명령어로 만들 수 있는 건, object (객체)라고 합니다.

    . object 중에서 저장 공간이 필요한 건, segment라고 합니다.

    . segment 종류는 4가지 : table / index / undo / temporary

    . extent는 저장공간 할당 단위 (기본적으로 8개 blocks = 64KB)

    . block은 데이터 처리 최소 단위 (기본적으로 8KB)

  - system tablespace: 데이터 딕셔너리 테이블과 같은 핵심 기능 저장

  - sysaux tablespace: 자동 성능 진단을 위한 정보를 저장

 

■ Multitenant

  - 클라우드 환경을 지원하기 위한 목적으로 탄생 (1개 인스턴스가 여러 데이터베이스에 접근)

  - 12c부터 등장하였으나 실제 많이 사용하지 않음

 

■ ASM

예시)

20M [ emp1 |     ] disk1

20M [ emp2 |     ] disk2

20M [ dept1 |     ] disk3

20M [ dept2 |     ] disk4

20M [ dept3 |     ] disk5

물리적인 disk 5개를 묶어서 Disk Group을 만든다 (DATA)

테이블스페이스 insa를 DATA에 100MB 크기로 만든다면 5개 disk에 균등하게 배분

테이블 emp 1M를 만든다면 5개 disk에 균등하게 배분

 

20M [ emp1 |     ] disk1    20M [               ]

20M [ emp2 |     ] disk2    20M [               ] 

20M [ dept1 |     ] disk3    20M [ emp1 |     ]

20M [               ] disk4    20M [ emp2 |     ]

20M [               ] disk5    20M [ dept1 |     ]

미러링을 하면 사용 가능한 공간은 25GB가 된다 (이것이 이중화, 삼중화의 개념이구나)

 

성능 저하 방지를 위해 데이터를 10개 disk에 균등하게 분배하는 작업 필요

이를 위해 일반적으로 Raid0, Raid1 기법을 쓴다

미러링도 지원하나봐

오라클은 ASM으로, 기본적으로 스트라이핑 방식으로 분산 저장