[ORACLE 튜닝] SQL 처리 과정과 I/O (2)

1-3. 데이터 저장 구조 및 I/O 메커니즘

1. SQL이 느린 이유

• SQL이 느린 이유는 디스크 I/O가 원인이다.
  (OS 또는 I/O 서브시스템이 I/O를 처리하는 동안 프로세스는 잠을 자기 때문)
  
  
• 프로세스
  • 프로세스는 실행 중인 프로그램을 의미한다.
  • 여러 프로세스가 하나의 CPU를 공유할 수 있지만, 특정 순간에는 하나의 프로세스만 CPU를 사용할 수 있기 때문에 실행(Running)과 실행 준비 상태(Runnable Queue)로 전환을 반복한다.
  • 프로세스가 디스크에서 데이터를 읽어야 하는 경우 CPU를 OS에 변환하고 잠시 수면(waiting) 상태에서 I/O가 완료되기를 기다린다.
  • 정해진 OS 함수를 호출(I/O Call)하고 CPU를 반환한 채 알람을 설정하고 대기 큐(Wait Queue)에서 잠을 잔다.
    
    

    

 

2. 데이터베이스 저장 구조

• 데이터를 저장하려면 먼저 테이블스페이스를 생성해야 한다. 테이블스페이스는 세그먼트를 담는 콘테이너로서, 여러 개의 데이터파일로 구성된다.
  
  

  

  
  
• 테이블스페이스를 생성하면 그림처럼 세그먼트를 생성한다. 세그먼트는 테이블, 인덱스처럼 데이터 저장공간이 필요한 오브젝트이다.
• 테이블, 인덱스를 생성할 때 데이터를 어떤 테이블스페이스에 저장할지를 지정한다.
• 파티션 구조가 아니라면 테이블, 인덱스는 각각 세그먼트에 속한다. 테이블 또는 인덱스가 파티션 구조라면, 각 파티션이 하나의 세그먼트가 된다.
• 익스텐트는 공간을 확장하는 단위, 테이블이나 인덱스에 데이터를 입력하다가 공간이 부족해지면 해당 오브젝트가 속한 테이블스페이스로부터 익스텐트를 추가로 할당한다. 익스텐트는 연속된 블록들의 집합이다.
• 익스텐트 단위로 공간을 확장하지만, 사용자가 입력한 레코드를 실제로 저장하는 공간은 데이터 블록이다. 한 블록은 하나의 테이블이 독점하므로 한 블록에 저장된 레코드는 모두 같은 테이블 레코드다.
• 한 익스텐트도 하나의 테이블이 독점한다. 한 익스텐트에 담긴 블록은 모두 같은 테이블 블록이다.
• 세그먼트에 할당된 모든 익스텐트가 같은 데이터파일에 위치하지는 않는다. 하나의 테이블스페이스를 여러 데이터 파일로 구성하면, 파일 경합을 줄이기 위해 DBMS가 데이터를 가능한 여러 데이터파일로 분산해서 저장한다.
  
   
  

  

• 위의 그림과 같이 익스텐트 내 블록은 인적한 연속된 공간이지만, 익스텐트끼리는 연속된 공간이 아니다.
• 용어 정의
  • 블록 : 데이터를 읽고 쓰는 단위
  • 익스텐트 : 공간을 확장하는 단위. 연속된 블록 집합
  • 세그먼트 : 데이터 저장공간이 필요한 오브젝트(테이블, 인덱스, 파티션, LOB 등)
  • 테이블스페이스 : 세그먼트를 담는 콘테이너
  • 데이터 파일 : 디스크 상의 물리적인 OS 파일
    
• DBA(Data Block Address)
  • 데이터베이스에서 데이터를 읽고 쓰는 단위는 블록이다.
  • 데이터 I/O 단위가 블록이므로 특정 레코드 하나를 읽고 싶어도 해당 블록을 통째로 읽는다.
  • 오라클은 기본적으로 8KB 크기의 블록을 사용하므로 1Byte를 읽기 위해서 8KB를 읽어야 한다.
  • 테이블뿐만 아니라 인덱스도 블록 단위로 데이터를 읽고 쓴다.

 

3. 블록 단위 I/O

• 데이터베이스에서 데이터를 읽고 쓰는 단위는 블록이다.
• 데이터 I/O 단위가 블록이므로 특정 레코드 하나를 읽고 싶어도 해당 블록을 통째로 읽는다.
• 오라클은 기본적으로 8KB 크기의 블록을 사용하므로 1Byte를 읽기 위해서 8KB를 읽어야 한다.
• 테이블 뿐만 아니라 인덱스도 블록 단위로 데이터를 읽고 쓴다.

 

4. 시퀀셜 액세스 vs 랜덤 액세스

• 시퀀셜 액세스
  • 논리적 또는 물리적으로 연결된 순서에 따라 차례대로 블록을 읽는 방식
  • 인덱스 리프 블록은 앞뒤를가리키는 주소 값을통해 논리적으로 서로 연결되어 있다. 이 주소값에 따라 앞 또는 뒤로 순차적으로 스캔하는 방식이 시퀀셜 액세스다.(아래 그림의 인덱스를 스캔하는 굵은 실선 화살표에 해당)
    
    
  • 논리적인 연결고리가 없는 테이블 블록 간에는 어떻게 시퀀셜 방식으로 액세스할까?
  • 오라클은 세그먼트에 할당된 익스텐트 목록을 세그먼트 헤더에 맵(map)으로 관리하고 익스텐트 맵은 각 익스텐트의 첫 번재 블록 주소 값을 갖는다.
  • 읽어야 할 익스텐트 목록을 익스텐트 맵에서 얻고, 각 익스텐트의 첫 번째 블록 뒤에 연속해서 저장된 블록을 순서대로 읽으면, Full Table Scan이 된다.(아래 그림의 테이블을 스캔하는 굵은 실선 화살표에 해당)
    
    

    

• 랜덤 액세스
  • 논리적, 물리적 순서를 따르지 않고, 레코드 하나를 읽기 위해 한 블록씩 접근하는 방식(위의 그림에서 점선 화살표에 해당)

 

 

5. 논리적 I/O vs 물리적 I/O

• DB 버퍼 캐시
  

  

  • 라이브러리 캐시가 SQL과 실행계획, DB 저장형 함수/프로시저 등을 캐싱하는 '코드 캐시'이다.
  • DB 버퍼 캐시는 '데이터 캐시'이다. 디스크에서 어렵게 읽은 데이터 블록을 캐싱해 둠으로써 같은 블록에 대한 반복적인 I/O Call을 줄이는 데 목적이 있다.
    
    

    

  • 서버 프로세스와 데이터파일 사이에 버퍼캐시가 있으므로 데이터 블록을 읽을 땐 항상 버퍼캐시부터 탐색한다.
  • 캐시에서 블록을 찾으면 프로세스가 I/O Call을 하지 않아도 된다. 반면, 캐시에서 블록을 찾지 못해 I/O Call이 발생해도 같은 블록을 두 번째 읽을 때부터는 캐시에서 블록을 읽으면 된다.
  • 버퍼 캐시는 공유 메모리 영역이므로 같은 블록을 읽는 다른 프로세스도 이득을 본다.
    
    
• 논리적 I/O vs 물리적 I/O
  • 논리적 블록 I/O
    • SQL문을 처리하는 과정에 메모리 버퍼 캐시에서 발생한 총 블록 I/O를 말한다.
    • Direct Path I/O가 작동하는 경우가 있으므로 논리적 I/O와 메모리 I/O가 정확히 같은 의미는 아니지만, 일반적으로 동일하다.
  • 물리적 블록 I/O
    • 디스크에서 발생한 총 블록 I/O를 말한다.
    • SQL 처리 도중 읽어야 할 블록을 버퍼캐시에서 찾지 못할 때만 디스크를 액세스하므로 논리적 블록 I/O 중 일부를 물리적으로 I/O 한다.
    • 메모리 I/O는 전기적 신호인 데 반해, 디스크 I/O는 액세스 암(Arm)을 통해 물리적 작용이 일어나므로 메모리 I/O에 비해 상당히(보통 10,000배쯤) 느리다.
      
      
• 버퍼 캐시 히트율
  • BCHR = ( 캐시에서 곧바로 찾은 블록 수 / 총 읽은 블록 수 ) x 100
            = ( (논리적 I/O - 물리적 I/O) / 논리적 I/O ) x 100
            = ( 1 - (물리적 I/O) / (논리적 I/O) ) x 100
  • BCHR은 읽은 전체 블록 중에서 물리적 디스크 I/O를 수반하지 않고 곧바로 메모리에서 찾은 비율이다.
  • 물리적 I/O가 성능을 결정하지만, 실제 SQL 성능을 향상하려면 물리적 I/O가 아닌 논리적 I/O를 줄여야 한다.
    
    

6. Single Block I/O vs Multiblock I/O

• 메모리 캐시가 클수록 좋지만, 데이터를 모두 캐시에 적재할 수 없다. 캐시에서 찾지 못한 데이터 블록은 I/O Call을 통해 디스크에서 DB 버퍼 캐시로 적재하고서 읽는다.
  • Single Block I/O 방식 사용하는 경우
    • 한 번에 한 블록씩 요청해서 메모리에 적재하는 방식
    • 인덱스를 이용할 때 기본적으로 인덱스와 테이블 블록 모두 이방식을 사용한다.
    • 인덱스 루트 블록을 읽을 때
    • 인덱스 루트 블록에서 얻은 주소 정보로 브랜치 블록을 읽을 때
    • 인덱스 브랜치 블록에서 얻은 주소 정보로 리프 블록을 읽을 때
    • 인덱스 리프 블록에서 얻은 주소 정보로 테이블 블록을 읽을 때
      

      

       
  • Multi Block I/O 방식 사용하는 경우
    • 캐시에서 찾지 못한 특정 블록을 읽으려고 I/O Call 할 때 디스크 상에 그 블록과 '인접한' 블록들을 한꺼번에 읽어 케시에 적재하는 방식
    • '인접한 블록'이란 같은 익스텐트에 속한 블록을 의미하고 MultiBlock I/O 방식으로 읽더라도 익스텐트 경계를 넘지 못한다.
    • 인덱스를 이용하지 않고 테이블 전체를 스캔할 때 이 방식을 사용
    • 테이블이 클수록 Multyblock I/O 단위도 크면 좋다.
      

 

 

7. Table Full Scan vs Index Range Scan

• Table Full Scan
  • 테이블에 속한 블록 '전체'를 읽어서 사용자가 원하는 데이터를 찾는 방식
• Index Range Scan
  • 인덱스에서 '일정량'을 스캔하면서 얻은 ROWID로 테이블 레코드를 찾아가는 방식
    

 

 

8.  캐시 탐색 메커니즘

• Direct Path I/O를 제외한 모든 블록 I/O는 메모리버퍼 캐시를 경유한다.
• 아래의 오퍼레이션은 모두 버퍼캐시 탐색 과정을 거친다.
  • 인덱스 루트 블록을 읽을 때
  • 인덱스 루트 블록에서 얻은 주소 정보로 브랜치 블록을 읽을 떄
  • 인덱스 브랜치 블록에서 얻은 주소 정보로 리프 블록을 읽을 때
  • 인덱스 리프 블록에서 얻은 주소 정보로 테이블 블록을 읽을 때
  • 테이블 블록을 Full Scan 할 때
    
    
    

    

• DBMS는 버퍼캐시를 그림과 같은 해시 구조로 관리한다.
• 그림은 해시함수로 모듈러(mod) 함수를 사용하는 경우를 표현한다. 여기서는 5로 나누었을 때의 나머지 값을 반환하는 모듈러 함수로 캐시 탐색 매커니즘을 설명
• 버퍼 캐시에서 블록을 찾을 때는 해시 알고리즘으로 버퍼 헤더를 찾고, 거기서 얻은 포인터로 버퍼 블록을 액세스하는 방식으로 사용한다.
  
  
• 해시 구조의 특징
  • 같은 입력 값은 항상 동일한 해시 체인(=버킷)에 연결됨
  • 다른 입력 값이 동일한 헤시 체인(=버킷)에 연결될 수 있음
  • 해시 체인 내에서는 정렬이 보장되지 않음
    
    
• 메모리 공유자원에 대한 액세스 직렬화
  • 버퍼 캐시는 SGA 구성요소이므로 버퍼캐시에 캐싱된 버퍼블록은 모두 공유자원이다.
  • 하나의 버퍼블록을 두 개 이상 프로세스가 '동시에' 접근하려고 할 때 블록 정합성에 문제가 생길 수 있다.
  • 따라서 자원을 공유하는 것처럼 보여도 내부에선 한 프로세스씩 순차적으로 접근하도록 구현해야 하며, 이를 위해 직렬화(serialization) 메커니즘이 필요하다.
  • 특정 순간 한 프로세스만 공유자원을 사용할 수 있도록 하는 매커니즘이 래치(Latch)이다.
  • SGA를 구성하는 서브 캐시마다 별도의 래치가 존재하는데, 버퍼 캐시에는캐시버퍼 체인 래치, 캐시버퍼 LRU 체인 래치 등이 작동한다.
  • 캐시버퍼 체인뿐만 아니라 버퍼블록 자체에도 직렬화메커니즘인 '버퍼 Lock'이 존재한다.
  • 이런 직렬화 매커니즘에 의한 캐시 경합을 줄이려면, SQL 튜닝을 통해 쿼리 일량(논리적 I/O) 자체를 줄여야 한다.
• 캐시버퍼 체인 래치
  • 대량의 데이터를 읽을 때 모든 블록에 대해 해시 체인을 탐색한다. DBA(Data Block Address)를 해시 함수에 입력하고 거기서 반환된 값으로 스캔해야 할 해시 체인을 찾는다.
  • 해시 체인을 스캔한은 동안 다른 프로세스가 체인 구조를 변경하는 일이 생기면 곤란하다. 이를 막기 위해 해시 체인 래치가 존재한다.
  • 그림 1-27에서 0-4까지 다섯 개 체인 앞쪽에 자물쇠가 있다고 생각하면 된다. 자물쇠를 열 수 있는 키를 획득한 프로세스만이 체인으로 진입할 수 있다.
    
• 버퍼 Lock
  • 블록을 찾으면 캐시버퍼 체인 래치는 바로 해제되야 한다. 그러나 래치를 해제한 상태로 버퍼블록 데이터를 읽고 쓰는 도중에 후행 프로세스가 같은 블록에 접근해서 데이터를 읽고 쓴다면 데이터 정합성 문제가 발생한다.
  • 이를 방지하기 위해 오라클은 버퍼 Lock을 사용한다. 캐시버퍼 체인 래치를 해제하기 전에버퍼 헤더에 Lock을 설정함으로써 버퍼블록 자체에 대한 직렬화 문제를 해결하는 것이다.