STATSPACK을 이용한
오라클 데이타베이스 트랜드 분석

도날드 K. 벌레슨(Donald K. Burleson)

그 모든 오라클 데이타베이스를 정밀하게 튜닝하기 위해서는 과거 데이타 모음 처리 방법과 데이타를 시간 경과에 따라 데이타베이스 성능에 영향을 미치는 변화들을 보여주는 보고서로 번역하는 기능이 필요하다. 오라클의 새로운 STATSPACK 유틸리티는 STATSPACK 테이블에 대한 트랜드 분석 보고서를 작성하는 기능과 함께 필요한 데이타를 수집할 수 있는 기능을 제공한다.

Oracle8 을 위한 STATSPACK 유틸리티

STATSPACK 유틸리티는 Oracle8i(릴리스 8.1.)에서 처음으로 소개됐지만, 오라클은 패치를 다운로드해 STATSPACK을 Oracle8 데이타베이스와 함께 사용할 수 있도록 지원하고 있다.

오라클 매거진(본사 Oracle Magazine) 2000년 3월 호를 시작으로 STATSPACK의 pre-Oracle8i 버전을 상세하게 다룬 기사가 시리즈로 연재되어 실려있다. 일단 Oracle8이 설치되면 DBA는 언제든 유용한 성능 데이타를 수집해 분석할 수 있는 준비를 갖추었다고 할 수 있다.

STATSPACK 운영 방법

STATSPACK 유틸리티는 Oracle Begin-Statistics(BSTAT)와 End-Statistics(ESTAT) 유틸리티의 특별 버전을 실행하는 스크립트 세트이다. 이 유틸리티들은 100개 이상의 성능 메트릭스에 대해 경과된 시간 통계를 포착한다. 그러나 BSTAT/ESTAT 유틸리티와 달리 STATSPACK는 성능 데이타를 포착해 특정한 오라클 테이블에 데이타를 저장한다.

모든 STATSPACK 테이블은 PERFSTAT 사용자가 소유하며, 모든 테이블은 STAT$로 시작된다. 부록에 전체 STATSPACK 테이블 이름 리스트를 실었다.

이 테이블에 있는 열들을 보려면 리스트 2에서 예시된 것처럼 간단히 테이블 이름을 선택하고 SQL*Plus 'DESCRIBE' 명령을 사용하면 된다.

리스트 2를 통해 buffer_pool_statistics는 전체 데이타베이스에 대한 물리적인 읽기와 쓰기 합계를 포함한 많은 정보가 포함되어 있다는 것을 알 수 있다. 먼저 테이블 구조를 이해한 다음, 용량 계획에 대한 커스터마이즈된 트랜드 보고서를 검토하게 된다. 그러면 트랜드 분석에 대해 포괄적으로 살펴본 후 이들 보고서가 작성되는 방법에 대해 살펴보기로 하겠다.

STATSPACK을 사용한 트랜드 분석

STATSPACK은 최근에 소개됐기 때문에 오라클은 'STATSREP80.SQL'이라는 단일 보고서 만을 지원하고 있다. 이는 지정된 2개 시점 사이에서 일어나는 변화를 검토할 수 있는 우수한 보고서이기는 하지만 트랜드 보고서를 만드는데 필요한 데이타를 제공하지는 않는다. 숙련된 DBA는 시간 경과에 따른 성능 데이타 측정을 통해 시간별, 날짜별, 그리고 주별 "표시(signature)"등을 자세히 파악할 수 있다는 것을 알고 있다.

모든 사람이 저마다 고유의 표시기호를 가지고 있는 것처럼 각 데이타베이스도 I/O, 정렬, 데이타 버퍼 적중률(hit ratio) 등에 대한 고유의 표시기호을 가지고 있다. 따라서 이러한 표시기호들은 특정한 데이타베이스 작업이 데이타베이스에 압력을 가하게 될 경우 이를 DBA에게 알리게 된다.

시계열(time series) 플로팅의 목적은 다양한 데이타베이스 메트릭스를 위한 표시기호들을 개발하기 위한 것이다. 예를 들어, I/O가 항상 화요일 오전 9시에 높다거나, 데이타 버퍼 적중률이 항상 수요일 오후에 낮아진다는 사실을 발견하게 될 수도 있다. DBA는 이러한 표시기호 정보를 확보함으로써 데이타베이스 성능의 최고점과 최저점에 맞는 합당한 계획을 수립할 수 있게 된다. 몇 가지 실제 STATSPACK 플롯을 살펴봄으로써 이러한 개념을 파악해 보도록 하겠다.


토탈 시계열 보고서

이들 보고서들은 지정된 기간 중에 발생한 데이타베이스의 전반적인 트랜드를 보여주고 있다. MS-Excel로 작성한 <그림 1>의 예제에서는 데이타베이스 증가율을 나타내기 위해 추가한 선형 회귀선으로 표시한 데이타베이스의 총 바이트 수에 대한 리포트이다.

<그림 1> 데이타베이스 크기 시계열 보고서

일간 보고서

일간 보고서는 시간별로 묶은 시간별 트랜드를 나타내는 것이다. <그림 2>의 예제에서는 그 날의 시간별로 평균을 낸 평균 읽기 I/O와 쓰기 I/O에 대한 표시기호를 볼 수 있다. 이 예제에서는 이 데이타베이스가 오전 7시에 최고점의 읽기 I/O 표시기호를 갖고 있으며, 다시 오후 8시에 최고점에 도달했다는 사실을 확인할 수 있다. 또한 이 데이타베이스는 상대적으로 일정한 쓰기 표시기호를 가지고 있다는 사실도 알 수 있다.

<그림 2> 시간별 평균 I/O

주간 보고서

<그림 3>에서는 날짜별로 평균을 낸 디스크 정렬을 볼 수 있다. 이 그림에서는 디스크 정렬이 월요일에 높고, 화요일에 최고점에 도달하며, 수요일에 높았다가 목요일과 금요일에는 떨어지는 확실한 표시를 볼 수 있다. 이 보고서는 DBA에게 잠재적으로 TEMP 테이블스페이스에 높은 영향을 미칠 수 있는 시간대를 신속하게 알려줄 수 있다.

<그림 3> 일간 트랜드 보고서

이제 트랜드 보고서의 유용성을 확신할 수 있기 때문에 STATSPACK로부터 추출한 몇 가지 일반 메트릭스를 점검해 보기로 하겠다.

그렇다면 어떤 방법으로 시간별 혹은 날짜별로 평균을 낼 수 있을까? 사실 이는 매우 간단하다. 날짜별로 평균 디스크 I/O를 프린트한 리스트 3을 살펴보기로 하자.

트랜드 분석에 가장 적합한 STATSPACK 데이타

STATSPACK에서 사용할 수 있는 통계가 수 백 개이기 때문에 과연 어떤 메트릭스가 가장 유용한가라는 의문이 제기된다. 본인이 집필한 "High Performance Oracle8 Tuning"에서 제시한 바 있는 어드바이스를 토대로 다음과 같은 메트릭스를 선정했다.

데이타 버퍼 적중률

이는 init.ora 파일의 db_block_buffers 파라미터에 의해 정의된 대로 오라클 데이타 버퍼의 효율성을 측정한다.

정렬

데이타베이스에서의 정렬 규모를 모니터링하는 것이 중요하며 특히 TEMP 테이블스페이스(정렬(디스크))에서 수행된 정렬 규모를 더욱 철저하게 모니터링해야 한다.

물리적 디스크 읽기

I/O 감축의 일차적 목적은 오라클 튜닝이기 때문에 시간별 디스크 읽기를 보여주는 트랜드 보고서는 재순차(re-sequence) 행을 인덱스 순서로 테이블을 재구성하는데 따른 영향을 측정하는데 매우 유용할 수 있다.

물리적 디스크 쓰기

디스크 쓰기 규모는 오라클 데이타베이스의 전체 처리 성능에 상당한 영향을 미치게 되며, 롤백 세그먼트 활동이 중요한 경우 특히 중요하다.

I/O 대기

이는 I/O 경합(contention)을 확인하고 수정하는데 사용될 수 있는 매우 중요한 메트릭스이다. 높은 I/O 대기 상태는 2개 이상의 데이타 파일에 동시에 액세스되는 경우에 발생하며, DASD의 읽기-쓰기 헤드 이동은 디스크가 I/O 종료가 대기되도록 하는 원인이 되기도 한다. 자세한 내용은 Turning Table on Disk I/O(본사 오라클 매거진, 2000년 1월호) 및 Tuning Disk I/O in Oracle8(본사 오라클 매거진 1999년 11월호) 참조.

버퍼 비지 웨이트(Buffer Busy Waits)

버퍼 버스트(bust) 대기는 오라클 데이타 블록이 버퍼에 상주하지만 필요한 프로그램이 데이타 블록을 읽어 들이기 위해 대기해야만 하는 경우 일어난다. 버퍼 비지 웨이트는 일반적으로 1 개 테이블이 동시에 'UPDATE' 혹은 'INSERT DML'을 갖고 있으며 단지 1 개의 프리리스트(freelist) 만이 테이블용으로 정의돼 있는 경우 일어날 수 있다.

다시하기(Redo) 로그 공간 요구

다시하기(Redo) 로그 공간 요구는 로그 버퍼가 다시하기 로그 활동 볼륨을 수용하기에는 너무 작을 때 발생한다.

Latch pin hit ratio

데이타베이스 인스턴스의 핀 적중률은 init.ora 파일의 shared_pool_size의 크기 조정에 대한 좋은 아이디어를 제공한다.

Table Fetch Continued Row

이 메트릭스는 데이타베이스와 연결된 행들에 대한 전반적인 지시를 제공한다. 연결이 과도한 경우 질의 서비스에 추가 I/O가 요청되며 DBA는 테이블의 PCTFREE를 늘리고 테이블이 체인들을 해제하도록 재구성해야 한다.

STATSPACK 테이블의 구조 이해와 약간의 연습을 통해 사용자 정의형 보고서를 손쉽게 작성할 수 있게 된다.

스프레드시트를 사용한 오라클 트랜드 그래프 플로팅

일단 성능 데이타가 포착되면, STATSPACK 보고서를 테이블에 대해 실행시킬 수 있다. 또한 선형 회귀와 예측 모델을 결합하고 있기 때문에 DBA는 이전 소모율을 토대로 디스크 메모리를 더 주문해야 하는 시기를 예측할 수 있다.

간단한 예제를 제시하기 위해 간단한 추출을 실행하고 이를 MS 엑셀에서 차트 위저드를 사용해 그려보기로 하겠다. 그 단계는 다음과 같다.

1. SQL*Plus에서 STATSPACK 데이타에 대한 질의를 실행시킨다.
2. 결과를 잘라내어 스프레드시트에 붙여 넣는다.
3. MS 엑셀에서 바로 전에 붙여 넣어 하이라이트로 표시된 데이타를 이용해 드롭다운 메뉴에서 'DATA'을 선택한 다음 'Text to Columns'를 선택한다. 이렇게 하면 열들이 별도의 셀들로 분리될 것이다.
4. 차트 위저드 버튼을 눌러 선 차트를 작성한다.

단계별 상세 설명 (각 단계별 이미지 대체)

1 단계. 우선 C를 사용해 데이타를 복사한다.



2 단계. 그 다음 MS 엑셀을 열고, 을 사용해 데이타를 붙여 넣는다.



3 단계. 하이라이트로 강조된 데이타 열을 사용해 드롭다운 메뉴에서 'DATA'을 선택한 후 'Text to Columns'를 선택한다. 고정 폭 구분 기능으로 'Text Wizard'를 실행시킨다.



4 단계. 그 다음은 차트 위저드를 눌러 선 차트를 선택한다.



5 단계. 그래프가 거의 완성됐다.

이제 그래픽 툴도 알게 됐으니 STATSPACK 데이타를 사용해 자동으로 DBA에게 곧 발생하게 될 문제를 경고해주는 일반적인 경고 스크립트에 대해 살펴보기로 하겠다.

DBA를 위한 커스터마이즈된 예외 경고 보고서

STATSPACK 데이타에 대한 가장 효율적인 보고서 중 하나는 통계가 사전 정의된 임계값을 초과할 때 마다 아웃 바운드(out-of-bounds) 경보를 만들어 낼 수 있는 기능이다. 부록에 있는 'alert.sql' 스크립트는 쉽게 일 단위로 실행되도록 스케줄링 할 수 있으며, DBA에게 모든 초과된 임계값을 보여주는 보고서를 제공하게 된다.

'ALERT.SQL'에 의해 측정된 임계값들:

f
일부 업체들은 경고 보고를 위해 STATSPACK를 확장시키고 유닉스 성능 메트릭스를 저장하고 있다. 이 기술에 대한 세부사항은 Automating Oracle Tuning(본사 오라클 매거진 1996년 7월호)에서 제공되고 있다. 물론, DBA는 자체적인 목적에 따라 'ALERT.SQL'을 손쉽게 정의할 수 있다. 일부 업체들은 관리자가 데이타베이스를 적절하게 운영하는데 있어서의 DBA의 효율성을 모니터링하기 위해 이 스크립트를 설치해 사용하고 있다.

유닉스 서버가 e-메일과 연결되어 있는 업체의 경우 일간 경보 보고서가 DBA가 메일로 전송된다. 이를 통해 DBA는 지속적으로 모니터링을 해야 하는 지루한 작업으로부터 해방되어 보다 전문적인 작업에 집중할 수 있게 될 것이다.


부록

'STATSPACK_ALERT.SQL' 스크립트

도날드 벌레슨은 유명한 오라클 DBA 저널인 Oracle Internals의 편집장이며, High-Performance Oracle8 Tuning, High-Performance Oracle Data Warehousing(Coriolis Publishing, 1997), 그리고 Oracle SAP Administration 등을 포함해 수많은 데이타베이스 관련 서적의 저자이기도 하다. 도날드의 웹사이트는 www.dba-oracle.com이다.