오라클 성능 고도화 원리와 해법

1 오라클 아키텍쳐

1 기본 아키텍쳐

오라클 = 데이터베이스 + 인스턴스

1.1 오라클 인스턴스

1.1.1 PGA(Program Global Area)
user process가 sever process 에게 session 을 허락해 달라는 요청이 오는 경우 PGA는 이러한 user process 를 처리하기 위한 메모리가 요구되는데 이때 사용되는 메모리 영역이 바로 PGA이다. PGA는 인스턴스에 속하지 않는다.

1.1.2 SGA(System Global Area)
Oracle이 구동되면 오라클은 기본적으로 SGA라는 메모리 영역을 가장 먼저 할당한다.

1) DB Buffer Cache

• - Data Files로부터 읽은 Data Block의 복사본을 담고 있는 영역
• - 수행하는 SQL 문장의 실제 데이터를 메모리에 저장하여 다른 사용자의 동일한 데이터의 요청 시 성능 저하의 주범인 Disk I/O를 수행하지 않고 메모리상의 데이터를 리턴하여 성능 개선을 위해 사용하고자 하는 공간
• - 아직까지 DISK에 Write하지 않은 수정된 데이터를 보유할 수도 있음
• - LRU 알고리즘에 의하여 가장 오래 전에 사용된것은 DISK로 밀어내고 가장 최근의 블록을 유지하게 하여 I/O 성능을 향상시키고자 함
• - DBWR(Database Writer Process)에 의해서 관리
• - Free Buffer는 'SERVER PROCESS'에 할당되어 사용되고, 사용 후 Dirty Buffer가 된 Buffer들은 DBWR에 의해 디스크에 씌여진 후다시 Free Buffer가 되어 'SERVER PROCESS'에 의해 재사용되는 작업을 반복함
• - Buffer Cache는 Dirty List(LRU Write List(LRUW))와 Least Recently Used(LRU) List 두 개의 List로 구성
  • => LRUW(LRU Write List) List
    • 수정되어 디스크에 반영되어야 할 블록들의 리스트
    • LRUW에 모인 Dirty Buffer는 DBWR에 의해 디스크로 쓰여지고 나면 이 Buffer는 Free Mark 되어 다시 사용될 수 있도록 LRU List의 끝부분에 위치함
  • => LRU(Least Recently Used) List
    • 최근에 읽은 Datafile Block을 Buffer Cache에 보관하고, 새로운 Block이 파일에서 읽혀질 필요가 있을 경우가장 오래된 버퍼들로부터 메모리에서 없어지도록 관리하기 위한 알고리즘

2) Shared Pool

• - Shared Pool은 하나의 데이터베이스에 행해지는 모든 SQL 문을 처리하기 위해서 사용되며 Library Cache, Datadictionary Cache로 구성되어 있음
  • Library Cache
    • 이 메모리 영역은 사용자가 같은 SQL을 재실행할 경우 Hard Parse를 생략하고 Soft Parse를 할 수 있도록 SQL 정보를 저장
    • SQL과 PL/SQL 영역을 저장하고 있음
  • Data dictionary Cache
    • 데이터베이스 테이블과 뷰에 대한 정보, 구조, 사용자 등에 대한 정보가 저장하여 Soft Parse를 할 수 있도록 함

3) Redo Log Buffer

• - 리두 로그 버퍼는 데이터베이스에서 일어난 모든 변화를 저장하는 메모리 공간
• - 이 영역에 저장된 값들은 LGWR에 의해 데이터베이스 복구에 사용되는 온라인 리두로그 파일에 저장
• - 리두 정보는 데이터가 COMMIT 되기 전에 먼저 보관이 되어야 어떤 상황에서도 복구가 가능하므로 변경 내용을 먼저 리두 로그 버퍼에 저장하고 난 후에 DB Buffer Block에 리두 로그 버퍼 내용을 적용 (SMON)

4) Streams Pool

• 오라클 10g부터 지원하는 메모리 영역이며 다른 DB로 데이터를 전달할 때 사용하는 메모리 영역

5) Large Pool

• SGA에서 대용량 메모리를 할당할 때 사용

6)Java Pool

• Oracle JVM에 접속해 있는 모든 세션에서 사용하는 자바코드가 사용하는 메모리 영역

1.1.3 백그라운드 프로세스

SMON (System Momitor)

• 시스템을 감시하는 기능.
• 인스턴스 복구 기능.
• 불필요한 임시세그먼트를 정리.

PMON (Process Monitor)

• 이전에 실패한 사용자 프로세스를 정리하는 기능
• 사용자가 사용하고 있는 리소스를 정리한다. ( Lock프로세스 소멸시 Lock해제)

DBWR (Database Writer)

• 데이타 블럭 버퍼 캐쉬와 딕셔너리 캐쉬의 내용을 관리하는 기능.
• Data Base Buffer cache 내용을 데이터 파일에 저장하는 작업을 수행

LGWR (Log Writer)

• 리두로그버퍼의 내용을 온라인 리두 로그파일에 기록한다.

CKPT (체크포인트)

• 마지막 체크포인트 이후에 변경된 모든 블록을 데이터 파일에 쓰도록 유도하고 체크포인트를 기록하기 위해 데이터 파일 헤더와 컨트롤 파일을 변경한다.

ARCH

• 아카이브 기능을 수행

참조: http://blog.naver.com/wineredhyun/89791924

2. DB 버퍼캐시

정의
사용자가 입력한 데이터를 데이터 파일에 저장하고 이를 다시 읽는 과정에서 거쳐가는 캐시영역
물리적인 I/O를 최소화하기 위해 최근에 사용한 블록에 대한 정보를 저장하는 메모리의 일정 영역 

(1) 블록단위 I/O

오라클에서 I/O는 블록단위로 이루어 진다.

• 메모리 버퍼 캐시에서 버퍼 블록을 액세스 할때 블록 I/O
• 데이터파일에 저장된 데이터 블록을 DB 버퍼 캐시로 적재할때 블록 I/O
• 캐시에서 변경된 블록을 다시 데이터파일에 저장할 때 블록 I/O 
  single block I/O : 인덱스를 경유해 테이블 액세스시.
  multi block I/O : Full Table Scan 시 
  => 옵티마이져가 인덱스를 이용해 테이블을 액세스할 지, Full Table Scan을 할 지를 결정하는 판단 기준은 읽어야 할 레코드 수가 아니라 블록 갯수이다. 
  

  (2) 버퍼 캐시 구조

  해시테이블 구조로 관리 됨. 
  해싱(hasing)알고리즘 <= 주소록에 비유
  해시버킷 - 성씨가 같은 고객은 같은 페이지
  입수시점이 같지 않으므로 해시 버킷내에서는 정렬상태를 유지 하지 않는다.(스캔방식으로 탐색) 
  해시 체인은 Shared Pool 내에 존재, 버킷(Bucket) ==> 체인(Chain) ==> 헤더(Header)의 구조.

• 해시 테이블(Hash table)은 여러개의 해시 버킷으로 구성.
• 블록의 주소(Database Block Address)와 블록클래스에 대해 해시함수 적용한 결과를 이용하여 버킷을 찾아간다.
• 해시 버킷에는 같은 해시 값을 갖는 버퍼 헤더들이 체인 형태 있다.
• 버퍼 헤더는 버퍼에 대한 메타 정보, 버퍼 메모리 영역의 실제 버퍼에 대한 포인터 값을 가지고 있다. 
  => 찾고자 하는 데이터블록 주소를 해시값으로 변환해서 해당 해시 버킷에서 체인을 따라 스캔하다가 찾아지면 바로 읽고,
  찾지 못하면 디스크에서 읽어 해시 체인에 연결한 후 읽는다.
  
  Hash table -> Hash bucket -> buffer header chain -> buffer header -> buffer body -> block header -> block body 
  

  (3) 캐시 버퍼 체인

  
  정의
  각 Database Buffer는 Data Block Address(DBA)가 Hash Function에 의해 해시되어 Hash Table에 할당되어 관리되는데, 이를 Cache Buffer Chain List라 한다.
  즉 Cache Buffer Chain List란 양방향의 링크된 리스트로, 인스턴스가 시작될 때 할당되는 Hash Table로 구성된다. 
  이 Hash Table 안의 Bucket은 각 Database Block Buffer들의 Header 정보를 가지며, 이 각 Buffer들은 LRU List 나 Dirty List의 한 가지에 속한다. 
  

• 래치
  => 같은 리소스에 대한 액세스를 직렬화 하여 리소스를 보호하기 위해 구현된 일종의 Lock 메커니즘. 
  
  • 캐시 버퍼 체인 래치이벤트의 대기원인*
    버퍼 캐시를 사용하기 위해 해시 체인을 탐색하거나 변경하려는 프로세스는 반드시 해당 체인을 관리하는 cache buffers chains 래치를 획득해야 한다. 
    cache buffers chains 래치를 획득하는 과정에서 경합이 발생하면 latch: cache buffers chains 이벤트를 대기하게 된다. 
    

    캐시 버퍼 체인 래치 경합이 발생하는 대표적인 경우 비효율적인 SQL - 동시에 여러 프로세스가 넓은 범위의 인덱스나 넓은 범위의 테이블에 대해 스캔을 수행할 경우. - 핫블록(Hot Block) - SQL 문의 작동방식이 소수의 특정 블록을 계속해서 스캔하는 형태로 작성되었다면, 여러 세션이 동시에 이 SQL 문을 수행하는 경우.

    
    

    (4) 캐시버퍼 LRU 체인

    
    두 종류의 LRU(Least Recently Used) 리스트 사용

  • Dirty 리스트* : 캐시 내에서 변경됐지만, 아직 디스크에 기록되지 않은 Dirty버퍼 블록들을 관리.LRUW(LRU Write)리스트 라고도 함.
  • LRU 리스트* : 아직 Dirty 리스트로 옮겨지지 않은 나머지 버퍼블록들을 관리함. 
    

    LRU 리스트의 버퍼의 상태 1) Free 버퍼 : 아직 데이터가 읽히지 않아 비어있는 상태 혹은 언제든지 덮어써도 무방한 상태. 2) Dirty 버퍼 : 버퍼에 캐시된 후 변경이 발생했지만, 아직 디스크에 기록되지 않아 동기화가 필요한 버퍼블록. 3) Pinned 버퍼 : 읽기 또는 쓰기 작업을 위해 현재 액세스되고 있는 버퍼블록.

    
    
    
    

oracle_memory-believe_2292.pdf
참고문헌 오라클메모리심층분석. Oracle Technical Note. 한국오라클. 박경희

3. 버퍼 Lock

(1) 버퍼Lock이란?

Oracle은 Row level을 Lock을 제공하기 때문에 서로 다른 Row를 변경하는 것은 문제가 되지 않는다. 하지만 두개의 Row가 같은 블록 안에 있다면 블록을 동시에 변경하는 것은 안되기 때문에 각 사용자는 개별적으로 Row 변경을 위해 TX Lock을 Exclusive하게 획득했다 하더라도 한 한명의 사용자 만이 블록을 변경하고 있다는 것을 보장 받아야 한다. 이 경우에 획득해야 하는 Lock을 Buffer Lock이라 한다.
만일 Buffer Lock을 획득하지 못하면 다른 Lock들과 마찬가지로 Lock을 획득할 때까지 대기해야 한다.
버퍼Lock을 획득 했다면 래치를 곧바로 해제한다.
버퍼내용을 읽기만 할 때는 Share모드, 변경을 할 때는 Exclusive모드 Lock을 설정한다.

1. 변경하고자 하는 Row에 해당하는 Block 이 존재하는 위치에 찾아가기 위해 cache buffer chains latch를 획득한다.
2. Block을 찾은 해당 Buffer에 대해 Buffer Lock을 획득하고, cache buffers chains latch를 해제한다.
3. 해당 Row에 대해 TX Lock을 획득하고 Row를 변경한다.
4. Buffer Lock을 해제한다.

• 해시 체인 래치를 획득하고 목적한 버퍼를 찾았는데 다른 프로세스가 버퍼Lock을 Exclusive모드로 점유하고 있다면, 버퍼Lock 대기자 목록에 등록하고 래치를 해제한 후 buffer busy waits 대기 이벤트를 발생한다.

(2) 버퍼핸들

• 버퍼헤더에 Pin을 설정하려고 사용하는 오브젝트를 일컬음.
• 버퍼Pin = 버퍼lock : 자신이 해당 버퍼를 사용중임을 표시하는 것.
• 버퍼핸들을 얻어 버퍼헤더에 있는 소유자 목록에 연결시키는 방식으로 Pin을 설정한다.
• 버퍼핸들을 얻으려면 cache buffer handles 래치가 필요하다.
• 오라클은 각 프로세스마다 _db_handles_cached 개수만큼 버퍼 핸들을 미리 할당해주며 기본값은 5이다.

(3) 버퍼Lock의 필요성

• 오라클의 I/O 단위는 블록이기 때문에 블록에도 Lock이 필요하다. (정합성 보존)
• Pin된 버퍼 블록은 alter system flush buffer_cache; 명령어를 사용하여 버퍼 캐시 전체를 비우려고 해도 밀려나지 않음.
• 9i에서는 alter system set events 'immediate trace name flush_cache'; 를 통해 버퍼캐시를 비울 수 있다.

(4) 버퍼 Pining

• 버퍼를 읽고 나서 버퍼Pin을 즉각 해제하지 않고 데이터베이스 Call이 진행되는 동안 유지하는 기능.
• 같은 블록을 반복적으로 읽을 때 래치획득 과정을 생략하기 때문에 논리적 블록읽기 횟수를 획기적으로 줄일 수 있음.
• 같은 블록을 재방문할 가능성이 큰 오퍼레이션을 수행할 때만 사용됨.
• 하나의 데이터베이스 Call 내에서만 유효함.

Pining의 사용 예 인덱스를 경유해 테이블을 액세스 할 때 인덱스 클러스트링 팩터가 좋다면 같은 테이블 블록을 반복 액세스할 가능성이 크다. (8i) NL 조인시 Inner테이블을 룩업하기 위해 사용되는 인덱스 루트블록.(9i) Index Skip Scan에서 브랜치 블록을 거쳐 리프 블록을 액세스 하는 동안. (9i) NL 조인시 Inner테이블의 인덱스 루트블록 뿐만 아니라 다른 인덱스 블록에 대해서도 Pining을 함. (9i) DML 수행시 Undo레코드를 기록하는 Undo블록.

출처 : http://www.gurubee.net/