Db의 물리적인 성능 판단

어플리케이션을 만들어서 여러 서버에 설치하는 경우, 각 지역별 사용량에 따라서 DB상의 데이터 크기와 성능이 달라지는 경우가 있다.
그런데 DB가 스키마도 같고, 색인도 같고, 실행계획도 같고, 테이블 크기도 비슷한데 유난히 한두개의 지역에서 너무 느리다고 계속 민원이 들어오는 현상이 발생했다.
당시에는 노후화된 하드웨어의 영향이 클 것으로 생각했는데, 공공기관 특성상 방화벽을 잘 안열어주기때문에 직접 서버에 들어가서 확인하기가 어려웠다.
또한 단순히 “당신네들 컴퓨터가 너무 구려서 느린겁니다.” 라고 말하기보다는 정상사용중인 다른 지역과 성능수치를 비교하여 얼마나 하드웨어 성능이 낮은지를 얘기하는 것이 더욱 설득력이 클 것이라는 생각이 들었다.
그러므로 DB가 설치된 PC의 하드웨어 성능을 고려하여 종합적인 DB의 성능을 측정 하는 방법을 찾아보았다.

오라클에서 쿼리 수행 명령을 사용하면 Optimizer가 최적의 실행계획을 선택하여 실행한다.
이 때, 하드웨어 성능을 고려하여 최적의 판단을 내리기 위하여 사전에 시스템 통계를 수집한다.

SELECT * FROM SYS.AUX_STATS$;

통계항목	구분	설명
cpuspeednw	NoWorkload	NoWorkload 상태에서 측정된 CPU의 속도(단위 : Millions/sec)
ioseektim	NoWorkload	I/O Seek Time을 뜻하며, 데이터를 읽으려고 디스크 헤드를 옮기는 데 걸리는 시간을 나타낸다. 대개 5~15ms의 수치를 보이며 물리적인 디스크의 성능/RAID에 따라 달라진다. io seek time = seek time + latency time + os overhead time
iotfrspeed	NoWorkload	I/O transfer 속도를 뜻하며, 하나의 OS프로세스가 I/O서브시스템으로부터 데이터를 읽는 속도를 나타낸다.
sreadtim	Workload	평균적인 Single Block I/O 속도 (단위:ms)
mreadtim	Workload	평균적인 Multiblock I/O 속도 (단위:ms)
cpuspeed	Workload	현재 시스템에서 단일 CPU가 수행할 수 있는 표준 오퍼레이션 개수 (단위: 백만/s)
mbrc	Workload	Multiblock I/O 방식을 사용할 때 평균적으로 읽은 블록 수
maxthr	Workload	I/O 서브시스템의 최대 처리량(단위 : byte/s)
slavethr	Workload	병렬 Slave의 평균적인 처리량(단위 : byte/s)

시스템 통계를 수집하지 않았다면 ioseektim : 10, iotfrspeed : 4096 기본값이 부여되어 있다.

Workload 시스템 통계를 수집하고 반영하는 순간 NoWorkload 시스템 통계는 무시된다.
Workload 시스템 통계가 수집되기 전 까지는 아래 공식을 이용해 추정된 값을 사용한다.

cpuspeed = cpuspeednw                                    
mbrc     = db_file_multiblock_read_count                   
sreadtim = ioseektim + db_block_size / iotfrspeed          
mreadtim = ioseektim + mbrc * db_block_size / iotfrspeed

NoWorkload는 오라클이 무작위로 I/O를 발생시켜 측정한 값인 반면, Workload는 실제 애플리케이션에서 발생하는 부하를 측정한 값이므로 측정하지 않았다면 아무값도 나오지 않는다.

측정 방법은 다음과 같다.

-- 통계 수집 테이블 생성
BEGIN
    DBMS_STATS.CREATE_STAT_TABLE(
        OWNNAME => USER,
        STATTAB => 'mystats',
        TBLSPACE => 'USERS',
        GLOBAL_TEMPORARY => FALSE);
END;

-- 시스템 통계 수집(NoWorkload)
BEGIN
  DBMS_STATS.GATHER_SYSTEM_STATS( GATHERING_MODE => 'NOWORKLOAD' );
END;

-- 720분 동안 통계정보를 수집한다. (Workload)
BEGIN
  DBMS_STATS.GATHER_SYSTEM_STATS(
    GATHERING_MODE => 'interval',
    INTERVAL => 720,
    STATTAB => 'mystats',
    STATID => 'OLTP');
END;

-- 통계정보 수집 시작 (Workload)
BEGIN
  DBMS_STATS.GATHER_SYSTEM_STATS(
    GATHERING_MODE => 'start',
    STATTAB => 'mystats',
    STATID => 'OLTP');
END;

-- 통계정보 수집 종료 (Workload)
BEGIN
  DBMS_STATS.GATHER_SYSTEM_STATS(
    GATHERING_MODE => 'stop',
    STATTAB => 'mystats',
    STATID => 'OLTP');
END;

Workload는 실제 어플리케이션에서 발생하는 부하를 기준으로 각 항목의 통계치를 측정하는 반면, NoWorkload는 모든 데이터파일 중에서 오라클이 무작위로 I/O를 발생시켜 통계를 수집한다.
따라서 NoWorkload도 시스템 부하가 어느정도 있는 상태에서 수집되는 것이 바람직하다.

개발서버와 운영서버가 분리되어 있다면 한 쪽에서만 측정하고 다음과 같은 방법으로 지정할 수도 있다.

BEGIN
  DBMS_STATS.SET_SYSTEM_STATS('CPUSPEED', 500);
  DBMS_STATS.SET_SYSTEM_STATS('SREADTIM', 5.0);
  DBMS_STATS.SET_SYSTEM_STATS('MREADTIM', 30.0);
  DBMS_STATS.SET_SYSTEM_STATS('MBRC', 12);
END;

시스템 통계를 적용하더라도 기존에 캐싱되어 있던 실행계획에는 영향을 미치지 않으므로 Shared Pool을 비워야 적용된다.

ALTER SYSTEM FLUSH SHARED_POOL;

만약에 위의 방법을 통하여 성능을 측정하여도 정상DB와 비정상적으로 느린DB의 차이가 명확이 보이지 않는다면 index skew, sparse, fragmentation 또는 오라클 버전 차이에 따른 Optimizer 동작 차이에 대하여 찾아봐야 할 것으로 보인다.