오라클의 논리적 I/O와 물리적 I/O의 차이 그리고 버퍼 캐시 히트율(BCHR) 구하기

오라클에서 SQL을 파싱하는 것을 내부에 저장하고 있는 라이브러리 캐시와 데이터를 캐시하고 있는 DB 버퍼캐시가 존재한다. 이 데이터를 저장하고 있는 캐시 또한 SGA의 중요한 요소 중 하나이다.

데이터 블록에 대해 캐시를 해두어서 다음번에 읽어들일때 같은 작업을 반복해서 하지 않도록 하는 기능을 제공한다.

그럼 이런 버퍼 캐시를 이용하여 처리하는 방식과 직접 디스크에 접근하여 데이터를 접근하는 방식에 차이는 무엇인지 알아보자.

---

논리적 I/O 와 물리적 I/O

 오라클에서 SQL 문장을 처리하는 과정에는 두 가지 I/O가 존재한다. 하나는 논리적 블록 I/O 나머지는 물리적 블록 I/O이다. 

논리적 I/O는 SQL 문장을 처리하는 과정에서 메모리 버퍼캐시에서 발생한 총 블록 I/O로써 메모리에 접근하여 데이터를 가져온 블록 수를 말한다.

물리적 I/O는 디스크에서 발생한 총 블록 I/O를 말한다. 대부분의 경우가 논리적 I/O와 물리적 I/O가 동시에 발생한다. (물리적 I/O가 비용이 비싸기 때문에 캐시가 적절하게 되어있는것이 중요하다.)

 

그럼 버퍼캐시에서 얼마나 히트가 되는지 다시말하자면 데이터를 캐시된 정보에서 얼마나 가지고 오는지 구하는 버퍼캐시 히트율(BCHR)을 구해보자.

 

BCHR의 공식은 (논리적 블록 읽기 - 물리적 블록 읽기) / 논리적 블록 읽기 * 100 이다.

같은 표현으로 ( 1 - ( 물리적 I/O) / (논리적 I/O) ) * 100 으로 표현한다.

공식을 보면 알 수 있듯이 물리적 디스크 I/O를 수반하지 않고 곧바로 메모리에서 찾은 비율을 나타낸다.

 

우리가 튜닝을 진행할 때 물리적 I/O를 줄이려고 노력하지만 사실 논리적 I/O를 줄여야 한다.

그 이유는 아래 공식을 살펴보자. 위의 식을 변형해보면 쉽게 이해가 가능하다.

 

물리적 I/O = 논리적 I/O * (100 - BCHR)

 

 

BCHR의 값이 시스템 상황에 따라 변경되는 값이라고 한다면 결국 물리적 I/O를 줄이는 것은 논리적 I/O를 줄여야한다.

 

그렇다면 논리적 I/O를 줄이기 위해서는 어떻게 해야하는가?

논리적 I/O를 줄이기 위해서는 메모리에서 읽는 총 블록의 개수를 줄여야 한다.

 

물리적 I/O를 늘리려면 결국 메모리 증설해서 DB 버퍼캐시를 늘리는 방법밖에 없다. 그러기 때문에 한정된 리소스내에서 성능 튜닝을 위해서 결국 논리적 I/O를 줄여야한다.

 

그럼 쿼리를 수행할 때 사용되는 논리적 I/O값을 확인하면서 수정하기 위해서는 트레이스 파일을 만들면 된다. 그럼 트레이스 파일을 만들어서 논리적 I/O값을 확인하고 BCHR을 구해보자.

 

 

---

트레이스 기능을 이용한 논리적 I/O구하기

 

Oracle의 Trace 기능은 수행되는 개별 SQL Statement의 다음과 같은 성능정보의 통계를 제공해 준다.

 

- Parse, execute, and fetch counts

- CPU and elapsed times

- Physical reads and logical reads

- Number of rows processed

- Misses on the library cache

- Username under which each parse occurred

- Each commit and rollback

 

이외에도 사용자의 SQL 문제점 뿐만 아니라 DBMS 자체가 문제가 생길 경우에도 해당 정보를 Trace 파일로 생성해 준다.

 

트레이스 파일을 만들기 위해서는 간단하게 Tool을 이용하여 처리할 수 있다.

 

 

- 쿼리박스 SQL 모니터 툴에서 트레이스 설정 화면

 

 

쿼리박스, 오렌지, 토드 같은 DBMS 툴에서 트레이스를 설정하고 쿼리를 수행하면 오라클 DB에 트레이스 파일이 생성된다. 

생성된 트레이스 파일에는 다양한 정보가 있는데 그 중 논리적 I/O를 구할 수 있는 정보를 살펴보자.

 

 

 

흔히 논리적 I/O는 Current + Query이고 물리적 I/O는 Disk 영역이라고 생각하는 경우가 많다.

 

하지만 그렇지 않다. Query와 Current를 합쳐 논리적 I/O인데 여기에는 Disk (물리적 I/O) 값이 이미 포함되어 있는 상태이다.

 

그래서 위의 경우 논리적 I/O는 407로써 BCHR을 계산하면 ( 1 - ( Disk / (Query + Current))) * 100 으로 ( 1 - ( 35 / (407 + 0))) * 100 이므로 99%이다.