오라클 성능고도화 원리와 해법 Ⅰ (1장)

오라클 아키텍처

기본 아키텍처

  - 주목할 점은 리스너에 연결 요청을 하는 순간 하나의 프로세스를 띄우고 PGA 메모리를 할당한다는 사실이다.

  - 소프트웨어 세계에서 가장 화두인 재사용성은 데이터베이스 성능 튜닝의 핵심 원리이기도 하다

 

DB 버퍼 캐시

  - SQL 성능을 좌우하는 가장 중요한 성능지표는 액세스 하는 블록 개수이며, 옵티마이저의 판단에 가장 큰 영향을 미치는 것도 액세스해야 할 블록 개수다

  - SGA 내에는 수없이 많은 자료구조가 사용되고 있으며 그 중 가장 많이 사용되는 것이 해시 테이블이고, DB 버퍼 캐시도 해시 테이블 구조로 관리된다

  - DB 버퍼 캐시 내에서 데이터 블록을 해싱하기 위해 사용되는 키 값은 데이터 블록 주소(DBA)다

  - 버퍼 헤더만 해시 체인에 연결되며, 실제의 데이터 값이 필요해지면 버퍼 헤더에 있는 포인터를 이용해 다시 버퍼 블록을 찾아가는 구조다

 

버퍼 Lock

  - 캐시된 버퍼 블록을 읽거나 변경하려는 프로세스는 먼저 버퍼 헤더로부터 버퍼 Lock을 획득해야 한다

  - 버퍼 내용을 읽기만 할 때는 Share 모드, 변경할 때는 Exclusive 모드로 Lock을 설정한다

  - 액세스를 직렬화하기 위한 메커니즘이므로 당연히 Exclusive 모드 Lock은 한 시점에 하나의 프로세스만 얻을 수 있다

  - 버퍼 헤더에 Pin을 설정하려고 사용하는 오브젝트를 '버퍼 핸들'이라고 부르며, 버퍼 핸들을 얻어 버퍼 헤더에 있는 소유자 목록에 연결시키는 방식으로 Pin을 설정한다

  - 버퍼 Pinning을 통해 래치 획득 과정을 생략한다면 논리적인 블록 읽기 횟수를 획기적으로 줄일 수 있다

  - 버퍼 Pinning은 하나의 데이터베이스 Call(Parse, Execute, Fetch) 내에서만 유효하다. 즉, Call이 끝나고 사용자에게 결과를 반환하고 나면 Pin은 해제되어야 한다

 

Redo

  - 오라클은 데이터파일과 컨트롤 파일에 가해지는 모든 변경사항을 하나의 Redo 로그엔트리로서 Redo 로그에 기록한다

  - Redo 로그 3가지 목적 1) Database Recovery, 2) Cache Recovery, 3) Fast Commit

  - 영속성을 보장받으려면 최소한 커밋 시점에는 Redo 정보가 메모리가 아닌 디스크 상에 안전하게 저장되었음이 확인되어야 한다

  - log file sync 이벤트 : LGWR 프로세스가 로그 버퍼 내용을 Redo 로그 파일에 기록할 때까지 서버 프로세스가 대기하는 현상 때문에 발생

 

Undo

  - Undo 세그먼트 3가지 목적 1) Transaction Rollback, 2) Transaction Recovery, 3) Read Consistency

 

문장 수준 읽기 일관성

  - 문장 수준 읽기 일관성은, 단일 SQL문이 수행되는 도중에 다른 트랜잭션에 의해 데이터의 추가, 변경, 삭제가 발생하더라도 일관성 있는 결과집합을 리턴하는 것을 말한다

  - 오라클은 Shared Lock을 사용하지 않고 Undo 세그먼트에 저장해 둔 Undo 데이터를 활용하므로 그런 조치 없이도 완벽한 문장 수준 읽기 일관성을 보장한다

  - 쿼리가 시작되기 전에 이미 커밋된 데이터만 읽고, 쿼리 시작 이후에 커밋된 변경사항은 읽어들이지 않는다. 변경이 발생한 블록을 읽을 때는 현재의 Current 블록으로부터 CR 블록을 생성해서 쿼리가 시작된 시점으로 되돌린 후 그것을 읽는다

  - Current 블록은 디스크로부터 읽혀진 후 사용자의 갱신사항이 반영된 최종 상태의 원본 블록

  - 오라클은 SCN 이라고 하는 시간정보를 이용해 데이터베이스의 일관성 있는 상태를 식별 (쿼리 시작 지점과 블록 마지막 변경시점 확인, Global 변수)

  - SCN은 읽기 일관성과 동시성 제어를 위해 사용되고, 생성된 Redo 로그 정보의 순서를 식별하는 데에도 사용되며, 마지막으로 데이터 복구를 위해서도 사용

  - 만약 CR 블록을 과거 상태로 되돌리는 과정에서 필요한 Undo 정보가 덮어 쓰여저 계속 롤백을 진행할 수 없을 때 악명 높은 Snapshot too old 에러가 발생한다

 

Consistent vs Current 모드 읽기

  - 오라클은 Consistent 모드로 읽고, Current 모드로 갱신한다

  - 1) select는 Consistent 모드로 읽는다 2) insert, update, delete, merge는 Current 모드로 읽고 쓴다. 다만 갱신할 대상 레코드를 식별하는 작업만큼은 Consistent 모드로 이루어진다

  - 스칼라 서브쿼리는 특별한 이유가 없는 한 항상 Consistent 모드로 읽기를 수행한다

 

블록 클린아웃

  - 블록 클린아웃은 트랜잭션에 의해 설정된 로우 Lock을 해제하고 블록 헤더에 커밋 정보를 기록하는 오퍼레이션이다

  - Delayed 블록 클린아웃 작업 1) ITL 슬롯에 커밋 정보 저장 2) 레코드에 기록된 Lock Byte 해제 3) Online Redo에 Logging

 

Snapshot too old

  - 발생원인 1) Undo 블록이 다른 트랜잭션에 의해 이미 재사용돼 필요한 Undo 정보를 얻을 수 없는 경우 2) 커밋된 트랜잭션 테이블 슬롯이 다른 트랜잭션에 의해 재사용돼 커밋 정보를 확인할 수 없는 경우

  - 오라클은 일반 데이터 블록과 마찬가지로 Undo 세그먼트 헤더 블록을 갱신한 내용도 Undo 레코드로서 기록한다

  - Delayed 블록 클린아웃에 의해 Snapshot too old가 발생하는 원인은, '최저 커밋 SCN'이 쿼리 SCN보다 높아질 정도로 갑자기 트랜잭션이 몰리는 데에 있으며, 이때는 추정에 의한 블록 SCN이 쿼리 SCN보다 높아지게 된다

  - 회피 방법 1) 불필요한 커밋 자주 수행 X 2) fetch across commit 피하기 3) 트랜잭션 몰리는 시간 제외 4) 같은 블록 여러 번 방문에는 Nested Loop 형태 회피 5) 대량 업데이트 후 곧봐로 테이블 Full Scan

 

대기 이벤트

  - 발생 상황 1) 필요로 하는 특정 리소스가 다른 프로세스에 의해 사용 중일때 2) 선행작업이 완료되기를 기다릴 때 3) 할 일이 없을 때

 

Shared Pool

  - 딕셔너리 캐시: 테이블, 인덱스 같은 오브젝트는 물론 테이블스페이스, 데이터파일, 세그먼트, 익스텐트, 사용자, 제약, Sequence, DB Link에 관한 정보 캐싱 (로우 캐시)

  - 라이브러리 캐시 : 사용자가 던진 SQL과 그 실행계획을 저장해 두는 캐시영역

  - 특히 최적화는 하드 파싱을 무겁게 만드는 가장 결정적인 요인인데, 같은 SQL을 처리하려고 이런 무거운 작업을 반복 수행하는 것이 매우 비효율적이다. 그래서 만든 것이 라이브러리 캐시 영역이다. (SQL과 실행계획 재사용성 높이기 위해)