Post List

2016년 1월 30일 토요일

SQLP 요점정리 #10 : 3과목 Tuning - 1장 1절 Database Architecture

3과목 SQL 고급 활용 및 Tuning

1장 Architecture 기반 Tuning

1.1 Oracle Architecture

image
  1. Database : Disk에 저장된 data 집합
    • Datafile
    • Redo Log File
    • Control File
  2. Instance : SGA(Shared Global Memory)와 이를 엑세스하는 process 집합
    • Memory
      • PGA (Process Global Area)
        • Process 혹은 Thread 의 개별적인 메모리 공간
        • 사용자마다 개별적으로 사용하는 공간
        • Data Sort, Session, Cursor 정보 저장
      • SGA (System Global Area)
        • Oracle Process 들이 접근하는 큰 공유 메모리
        • Serve Process와 Backgroung Process가 공용으로 사용
        • DB Buffer Cache, Shared Pool(Library Cache + Dictionay Cache), Log Buffer, Large Pool, Java Pool
        • 직렬화 매커니즘 활용 (여러 Process가 동시에 사용) : Latch, Buffer Lock, Library Cache Lock/Pin
    • Processes
      • Server Process : 사용자의 명령어 처리
        • SQL Parsing 및 최적화
        • Cursor를 열어서 block을 read
        • 읽은 data를 정렬해서 client가 요청한 결과집합을 생성하여 network로 전송
      • Background Process : User의 연결 유무와 상관없이 Background로 실행
        • SMON (System Monitor) : DBMS의 CEO (모든 작업 총관리), 임시 segment,extent 정리, dictionary 관리, 재시작시 Instance 복구 담당
        • PMON (Process Monitor) : Server Process 관리, 잘못된 Process 파괴 및 자원복구, 잘못된 Trasaction 문제 해결
        • DBWn (Databaser Writers) : Buffer Cache의 Dirty Block을 Datafile(disk)에 저장
        • LGWR (Log Writer) : Log Buffer Entry(승인된 transaction)를 Redo Logfile에 기록
        • ARCn (Archiver) : 꽉 찬 Redo Log가 덮어 쓰여지기 전에 Archive Log Directory로 Backup
        • CKPT (Check Point)
          • 이전 CP 이후 변경사항을 datafile에 기록하고록 트리거링
          • 그 정보들을 control file/datafile의 header에 기록 (저장된 data를 일치시키는 작업)
          • 장애 복구시 CP 이후 Log 데이터만 disk에 기록함
        • RECO (Recoverer Process) : 분산 시스템간의 Transaction을 맞춰주는 역할
          • 예를 들어서 서울에 있는 은행과 부산에 있는 은행간에 이체 작업을 물리적으로 다른 DB Server 간의two-phase commit (양쪽 모두 prepare-phase 한 뒤, 둘 다 commit 한 것이 확인되지 않으면 RECO가 rollback 시킴.)
  • 1개의 Instance는 1개의 Database만 액세스
  • 단, RAC(Real Application Cluster)에서는 여러 Instance가 하나의 Database 액세스 가능
  • 1개의 Instance가 여러 Database를 액세스 할 수는 없음

1.2 DMBS 연결방식

  1. 전용 서버(Dedicated Server) 방식
    • Listener가 연결요청을 받으면 Server Process를 생성하여 사용자에게 제공
    • DBMS에 큰 부담을 주기 때문에 통상 Connectin Pooling 기법을 이용하여 반복 재사용함
  2. 공유 서버(Shared Server) 방식
    • Connection Pooling을 DBMS 내부에 구현
    • 사용자는 Server Process에 직접 연결하는게 아니라 Dispatcher에 연결을 함
    • Dispatcher가 Request Queue에 등록하고, Response Queue에서 답변을 가져와서 사용자에게 전달

1.3 File 구조

1. Datafile

Block > Extent > Segment > Tablespace <-(1 : N)-> Datafile
  1. Block
    • Record의 집합
    • data I/O 단위 (Oracle은 2KB ~ 64KB, MS-SQL은 8KB)
    • 하나의 record만 필요하더라도 disk에서 buffer로 block 단위로 읽음
    • Optimizer 가 Index/Table Scan을 결정하는 기준은 record수가 아닌 block수
  2. Extent
    • Block의 집합
    • Tablespace로부터 공간을 할당받는 단위 (Oracle 다양한 크기, MS-SQL 64KB)
    • Uniform Extent : Oracle은 Extent내의 Block은 하나의 Object가 독점
    • Mixed Extent : MS-SQL은 여러 Object가 나누어 사용
  3. Segemt
    • Extent의 집합
    • Table, Index, Undo 등의 Object가 저장되는 공간
    • Object와 Segment는 1:1관계 이지만, Partition의 경우에는 1 : M 관계
    • 1개의 Segment는 여러 datafile에 걸쳐 저장이 가능 (disk I/O 경합을 분산)
  4. Tablespace
    • Segment를 담는 컨테이너, 여러 datafile로 구성

2. Temporary Tablespace (임시 데이터 파일)

  • 대량의 Sort(ORDER BY, Sort Merge JOIN), Hash(Hash JOIN) 작업 중 메모리가 부족한 경우 중간 결과집합 저장
  • 임시 데이터이므로 Redo 정보를 생성하지 않음. 복구가 안되고 백업도 필요없음.
  • Oracle은 여러개 생성하여 사용자별로 할당이 가능 (MS-SQL은 1개의 tempdb를 전역으로 사용)
CREATE TEMPORARY TABLESPACE big_temp;
ALTER USER scott TEMPORARY TABLESPACE big_temp;

3. Logfile

  • Fast Commit 매커니즘
    • 갱신작업 내용을 메모리상 Buffer Block에만 기록한체 disk에 기록되지 않았더라도 Redo Log를 믿고 빠르게 Commit
    • 장애발생시 Logfile을 이용해서 언제든 복구가 가능
  • Online Redo Log (MS-SQL에서는 Transaction Log)
    • 데이터 유실 대비용으로 수행했던 Transaction을 기록
    • 사고 발생시 마지막 CP이후 작업 내용을 재현 (캐시 복구)
    • 최소 2개 이상의 파일로 구성. 하나의 파일이 꽉 차면 다른 파일로 Log Switching. 모든 파일 꽉차면 첫번째 파일 재사용 (Round-robin)
  • Archived(=Offline) Redo Log (MS-SQL에는 없음)
    • Online Redo Log가 재사용되기 전 다른 위치로 백업해 둔 파일

4. Memory

4.1 SGA (System Global Area)
  • DB Buffer Cache
    • Datafile에서 읽은 Block의 Cache Area
    • Buffer Lock을 이용한 직렬화
    • Buffer 상태
      • Free Buffer : Clean 상태이거나 disk와 내용이 같아서 언제든지 덮어써도 무방
      • Dirty Buffer : 변경이 발생했지만 disk에 기록되기 전인 상태. 재사용하려면 disk에 기록하여 Free Buffer로 만들어야 함
      • Pinned Buffer : 현재 I/O가 진행중
    • Buffer 재사용 알고리즘
      • LRT (Least Recently Used) 알고리즘 사용
      • LRU 체인을 이용하여 사용빈도 순으로 위치를 옮김
      • Free Buffer가 필요할 때 LRU End 쪽 (가장 액세스 빈도가 낮음) 부터 사용
  • Shared Pool
    • Library Cache
      • SQL문, Execution Plan, Stored Procedure를 저장
      • Soft Parsing: 해당 SQL문에 대한 Execution Plan이 Shared Pool에 있어서 바로 재사용
      • Hard Parsing : Shared Pool에 내용이 없어서 Optimizer를 이용해서 Execution Plan을 다시 생성
    • Dictionary Cache
      • 말그대로 딕셔너리 정보를 캐시 (여러 Object의 메타정보)
  • Log Buffer
    • DB Buffer Cache의 모든 변경사항을 저장
    • DB Block Buffer에 기록하기 전에 Redo Log Buffer에 먼저 기록. LGWR이 주기적으로 Redo Logfile에 기록. 늦어도 commit 시점에는 기록 (Log Force at commit)
    • Dirty Buffer를 datafile에 기록하기 전에 항상 Log Buffer를 먼저 Logfile에 기록 (Write Ahead Logging)
    • 왜냐면 인스턴스 장애 발생시 commit하지 않은 transaction을 rollback하기 위함. 만약 datafile을 먼저 기록해 보리면 최종 commit 되지 않은 transaction이 반영되어짐.
4.1 PGA (Process Global Area)
  • UGA (User Global Area)
    • Session별로 할당
      • Dedicated Server (전용 서버) 방식에서는 PGA에 할당 (1:1)
      • Shared Server (공유 서버) 방식에서는 SGA에 할당 : Large Pool (설정되었을 경우). 그렇지 않을땐 Shared Pool
  • CGA (Call Global Area)
    • DB Call (Parse, Execute, Fetch)이 진행되는 동안만 필요한 데이터
    • Recursive Call이 발생하면 그 안의 (Parse, Execute, Fetch)에 대해서도 추가로 할당
    • Call이 끝나면 바로 해제되어 PGA로 반환
  • Sort Area
    • Sort 작업이 진행되는 동안 조금씩 Chunk 단위로 할당
    • DML은 하나의 Execute4 Call에서 처리가 완료되므로 CGA에 할당
    • SELECT의 경우 중단 단계에서는 CGA에 할당되고, 최종 결과집합 출력하기 전에는 UGA에 할당

5. Wait Event

DBMS 내부 Process들이 기다려야 할 상황에서 sleep상태로 대기하게 되는데, 그 정보가 공유 메모리 영역에 저장됨.
  • Respone Time Analysis
Session 또는 시스템 전체에 발생하는 병목 현상과 그 원인을 찾아 해결
Response Time = Service Time + Wait Time
              = CPU Time     + Queue Time
  • CPU Time : Parsing에 소비한 시간인지 Query 자체 수행시간인지 분석
  • Wait Time : 각각 발생한 Wait Event를 분석해 가장 많은 시간을 소비한 이벤트 중심으로 해결방안 모색
  • Wait Event
    • 라이브러리 캐시 부하
      • 라이브러리 캐시에서 SQL 커서를 찾고 최적화하는 과정에서 발생한 경합
        • latch: shared pool
        • latch: library cache
      • 수행중인 SQL이 참조하는 오브젝트에 다른 사용자가 DDL을 수행할 경우
        • library cache lock
        • library cache pin
    • DB Call과 네트워크 부하 : application, network 간의 소모시간
      • SQL*Net message from client : DB 경합과 상관없음. client로부터 다음 명령이 올때까지 Idle 상태로 대기
      • SQL*Net message to client
      • SQL*Net more data from client
      • SQL*Net more data to client
    • Disk I/O 부하
      • db file sequential read : Single Block I/O 수행시 발생
      • db file scattered read : Multi Block I/O 수행시 발생
      • direct patch read
      • direct patch write
      • direct patch write temp
      • direct path read temp
      • db file parallel read
    • Buffer Cache 경합
      • latch: cache buffers chains
      • latch: cache buffers lru chaine
      • buffer busy waits
      • free buffer waits
    • Lock 관련 Wait Event
      • enq: TM - contention
      • enq: TX - row lock contention
      • enq: TX - index contention
      • enq: TX - index contention
      • enq: TX - allocate ITL entry
      • enq: TX contention
      • latch free : latch를 여러번(2000번 가량) 요청했지만 계속 사용 중인 경우 잠시 Wait 상태로 빠짐
더 자세한 내용을 알고 싶으면 아래 slide를 참고하세요.
더 자세한 내용은 Oracle 성능 고도화 원리와 해법 책을 참고해야 합니다.
작성자: 시간:
라벨:

댓글 없음:

댓글 쓰기

피드 구독하기: 댓글 (Atom)