 |
|
I/O 대기 시간 분석을 통한 성능 향상
Oracle Tips by Burleson Consulting
|
I/O 대기 시간 분석을 통한 성능
향상저자 : Donald K. Burleson
물리적 I/O 대기 시간 분석은 성능 향상을 위한 중요한 방법입니다.
튜닝 프로젝트의 중요한 분야는 물리적 읽기 대기 이벤트를 분석하고 향상시키는 작업입니다.
데이타베이스에 저장된 데이터를 읽는 데에 시간을 많이 소요하기 때문이며 성능에 결정적인 영향을 주기 때문입니다.
S시스템 병목의 대부분은 물리적 I/O 대기 시간으로 발생하기 때문에 데이타베이스에서 시스템 대기
시간 튜닝 프로젝트의 중요성이 높아지고 있습니다. 10046 대기 이벤트(Level 8 이상) 분석 기술을 선호하는 사람도 있고, Oracle
MOSC에서 제공하는 trcanzr.sql(see
Next Steps) 툴을 사용해서 10046 트레이스 덤프를 통해 병목지점을 알아냅니다. 한편으로 몇몇 전문가들은 덤프와 실시간 대기
이벤트 추적을 선호하기도 합니다.
분석 작업 중에 Oracle 데이타베이스는 대기 이벤트를 지속적으로 발생하는 상황에서 순간적으로
문제가 될만한 시스템 병목 원인을 포착하는 것은 쉬운 일이 아닙니다. 사실 튜닝이 잘된 데이타베이스에서도 병목 지점은 있습니다. 예를 들면 연산
중심의 데이타베이스는 CPU 병목이, 데이터 웨어하우스에서는 디스크 읽기 병목이 있습니다. 이론상으로 대기 시간과 연관된 하드웨어 자원 접근이
빨라진다면 데이타베이스는 빠른 수행능력을 보일 것입니다.
본 문서에서는 데이타베이스 튜닝의 중요한 요소인 대기 분석에 대한 몇 가지 방법을 다루겠습니다.
I/O 병목, CPU 병목, 메모리 병목과 같은 대기 시간이 있으며, 병목의 원인이 파악되면 데이타베이스 전문가와 함께 통해 문제점을 해결할 수
있을 것입니다.
대기 이벤트를 파악하기 위해서, Oracle 데이타베이스에서는 v$system_event,
v$session_wait 과 같은 뷰를 제공하여 문제 해결을 할 수 있습니다. 어떤 객체가 병목과 관련 있는지 알려주지 않지만,
v$system_event 뷰는 I/O 관련 이벤트와 중요한 관련이 있습니다. 이런 점을 보완하기 위해서 Oracle9i
Release 2에서 v$segment_statistics 뷰가 추가되었습니다. v$session_wait 뷰를 통해 자세한 파일과 블록
데이터에 대한 정보를 얻고, 블록 위치를 이용해 문제가 되는 해당 객체를 찾을 수 있습니다. 데이타베이스가 대기를 하고 있는 순간이 아니라면
시스템 이벤트는 매우 빠르게 발생하기 때문에 문제되는 객체를 찾기란 쉽지 않습니다. 순간적인 물리적 I/O 대기 정보와 관련된
v$session_wait 뷰를 사용해서 문제 원인을 찾는 방법을 고안해야 합니다.
v$system_event 뷰에는 300 개 이상의 대기 이벤트를 정의하고 있습니다. 본문에서는
두 가지 가장 중요한 물리적 I/O 대기 이벤트에 대해서만 다루겠습니다. 어떤 Oracle 데이타베이스에서도 발생하는 두 가지 중용한 I/O
읽기 대기입니다.
- db 파일 순차적 읽기 대기: 순차적 읽기 대기는 ROWID로
테이블을 접근하거나, 인덱스 블록 접근할 때와 같이 단일 블록을 읽을 때 발생합니다. 분산(복수 블록) 읽기와 반대되는 개념입니다.
- db 파일 분산 읽기 대기 : 전체 테이블 스캔이나 정렬 작업을 하게
되면 분산 읽기 대기가 발생합니다.
대기 이벤트를 튜닝은 1) 물리적 읽기 대기를 하는 객체를 확인하고, 2)튜닝 기술을 활용해
문제를 해결하는 단계를 거치게 됩니다. 이 방법을 살펴보고 대기 상태를 확인하는 방법에 대해서 알아보겠습니다.
물리적 읽기 대기 대처 방법
물리적 읽기 대기를 하는 객체를 확인할 때, Statspack 패키지를 사용해 대기와 관련된
SQL 문을 확인하고, 다음과 같은 순서로 문제를 해결합니다. 모든 상황에 최적의 방법은 아니므로 환경에 맞게 사용해야 합니다
- SQL 튜닝 : 디스크 대기 시간을 줄이는 가장 간단하면서도 가장
강력한 방법입니다. 전체 테이블 스캔을 하는 구문에 인덱스를 사용하는 것처럼, SQL 문장 튜닝을 통해 디스크 I/O를 줄이는 것은 가장
효과적으로 문제 해결을 하는 방법일 것입니다. 다음과 같은 세부적인 기술을 사용할 수 있습니다.
- 조인 순서 변경 : ORDERED 힌트를 이용해 조인되는
테이블의 순서를 변경하는 것과 같은 경우 유리합니다.
- 인덱스 변경 : 함수 기반 인덱스를 추가하거나 I/O 이벤트가
덜 발생하도록 INDEX 힌트를 이용해서 SQL 튜닝을 할 수 있습니다.
- 테이블 조인 방법 변경 : 순차적 읽기에서 해쉬 조인보다
중첩된 조인에서 I/O 대기가 적게 발생합니다. USE_NL 과 같은 힌트를 사용해서 조인 방법을 변경할 수 있습니다.
Oracle9i에서 새롭게 추가된 pga_aggregate_target를 사용하지 않는다면 hash_area_size
매개변수를 변경해서 해쉬 조인을 튜닝을 할 수 있습니다.
- 문제가 있는 SQL 수행시간 변경 : 특정시간대 특정 SQL 수행으로
디스크 대기가 반복적으로 발생하면, 다른 시간대로 수행 시간을 재 조정해 보십시오.
- 스키마 재 분석 :
dbms_utility.analyze_schema 패키지에서 생성된 dbms_statsIn은 최선의 SQL 실행 계획을 리턴하지 않는
경우도 있습니다. dbms_stats 패키지를 사용해 스키마 분석을 하십시오. 만일 컬럼 데이터 값이 비대칭이라면 막대그래프를 추가하는
작업이 필요합니다.
- 다중 스핀들을 이용한 디스크 I/O 분산 : iostat이나 EMC
Symmetrics Manager 같은 디스크 모니터링 툴에서 확인한 바에 의하면 디스크 채널 경합으로 디스크 읽기 대기가 발생하는
경우가 있습니다. 하드웨어 문제로 인해 디스크 대기가 발생한다면, 문제되는 객체의 MINEXTENTS와 NEXT 인자를 변경해 복수
디스크에 복수 데이터 파일로 스트립하여 다중 디스크 스핀들(Spindles) 접근이 가능하게 설정할 수 있습니다.
- KEEP Pool 사용 : 데이터가 적은 테이블을 전체 스캔하는 경우
KEEP Pool을 구현하여 분산 읽기를 줄일 수 있습니다. 자세한 내용은 "Advanced Tuning with Statspack"
(Next Steps참고)을 참고하십시오.
- 데이터 버퍼 캐쉬 크기 증가 : 더 많은 데이터 블록을 RAM에 두면
읽기 대기가 발생할 확률이 줄어 듭니다.
위 방법 중에 SQL 튜닝이 물리적 읽기 대기를 줄이는 가장 중요합니다. "Diagnosing
Performance with Statspack" (Oracle Magazine, 2000년 4,5월;
Next Steps참고)을 참고하면, Statspack을 이용해 문제가 발생하는 SQL문을 찾고 분석하는 방법과
stats$sqltext을 사용해 SQL 기록을 보관하며, 읽기 대기가 발생했을 때 해당 SQL 문을 얻는 방법 그리고 SQL 문의 실행 계획
정보를 얻고 최적의 실행 계획을 적용하는 방법을 자세히 설명하고 있습니다.
비효율적인 SQL 실행으로 불필요한 전체 테이블 스캔할 수 있습니다. 예들 들어 10 개 행만
리턴하는 질의를 수행하는데, 100 블록 테이블을 전체 스캔할 수 있습니다.
이제 솔루션을 알아보았고, 물리적 읽기 대기의 원인을 수정하는데 필요한 데이터 수집 방법을
알아보겠습니다.
실시간 대기 이벤트 정보 수집
문서에서는 Oracle 튜닝에 대한 포괄적인 방법 보다, 디스크 I/O 문제 해결을 위한 통찰력에
중점을 두겠습니다. v$views는 정보를 저장하는 곳이고 Oracle 인스턴스를 시작한 이후에 대기 발생 회수를 단지 확인할 수 있습니다.
대기와 관련된 객체를 확인하기 위해서 다른 방법을 취해야 합니다.
v$session_wait 뷰를 사용해 질의를 수행한 시점의 디스크 대기 정보를 확인 할 수
있습니다. 대기 정보가 일시적으로 저장되고 사라지기 때문에 모든 실행 대기 현상을 포착하는 것은 쉽지 않습니다. 따라서
v$session_wait 뷰를 이용해서는 반복적으로 샘플 데이터를 얻고, 분석을 통해 추후에 자세한 정보를 확인하는 방법을 사용합니다.
대기가 발생한 시점을 포착할 수 있고 대기가 발생한 파일과 블록 넘버를
v$session_wait를 통해 확인 할 수 있으며, 그 정보를 이용하면 어느 테이블, 인덱스에서 문제가 발생했는지 확인할 수 있습니다.
v$session_wait 뷰에서 필요한 정보를 저장하는 테이블을 먼저 정의합니다. 리스트 1은
이벤트 이름, 대기 시간, 파일 ID(P1) 블록 넘버(P2) 등의 v$session_wait 뷰의 컬럼 정보 저장할 테이블 구조를 보여줍니다.
v$session_wait의 정보를 저장할 테이블을 정의했고, 다음 단계는 특정 이벤트가 발생할
때 시스템 대기 정보를 INSERT 하는 테이블을 구성합니다. 한시적인 데이터를 유지하는 뷰의 특성상 INSERT가 수행되어도 값이 저장되지
않을 수도 있습니다. UNIX의 crontab에 INSERT 문(리스트 2 스크립트 참조)을 등록해서 60초 마다 실행되도록 설정합니다.
MS-Windows에서는 AT 명령어를 사용 합니다. cron 서비스가 수행될 때
v$session_wait를 조회하고 테이블에 저장합니다.
마지막으로 UNIX 서버의 crontab 목록에 60초마다 스크립트가 실행되도록 합니다. 런타임
I/O 대기에 대한 모든 정보를 수집하기 위한 전부입니다.
# ************************************************************
# Run 60-second check for run-time waits
# ************************************************************
* * * * * /opt/oracle/oracheck/get_waits.ksh mysid > /tmp/wt.lst
몇 일간 스크립트가 실행하고, 결과를 분석합니다. 분석작업에서는 리스트 4와 같이 테이블 스페이스
이름, 블록 이름을 확인하며 진행합니다. 다음과 같이 I/O 대기가 발생한 시점의 시간정보와 블록 ID를 확인 합니다.
아래는 스크립트 실행 결과입니다. I/O 대기가 발생한 정확한 시간과 블록 ID를 확인할 수
있습니다.
System Wait File Block
Date Event ID ID
-------------------- ------------------------------ ----- -------------
23/01/2003 17:40:02 db file sequential read 6 300,929
23/01/2003 18:00:03 db file sequential read 8 35,936
23/01/2003 21:04:02 db file sequential read 3 65,162
23/01/2003 21:08:02 db file sequential read 6 23,031
23/01/2003 21:09:01 db file sequential read 5 40,585
23/01/2003 21:10:01 db file sequential read 6 512,663
23/01/2003 21:11:02 db file sequential read 5 26,609
23/01/2003 21:12:01 db file sequential read 5 40,584
대기 타입과 시간 정보로 구성된(리스트 5 참조) 대기 회수에 대한 정보를 얻었습니다. 언제
I/O 튜닝이 필요한 시간대를 알 수 있기 때문에 대기 분석에 매우 중요합니다.
아래는 스크립트 결과를 보여줍니다. 다음 샘플 결과는 날짜별 I/O 대기 시간의 합을 확인하여,
시간의 변화에 따라 병목 현상이 틀리게 발생한다는 점을 알 수 있을 것입니다. 9:00PM-10:00PM 사이에 병목이 주로 발생한다고 분석할
수 있습니다.
Wait Wait
Date Hr. Event Count
-------------------- ------------------------------ -----
23-jan-2003 17 db file sequential read 1
23-jan-2003 18 db file sequential read 1
23-jan-2003 21 db file sequential read 53
23-jan-2003 22 db file sequential read 8
23-jan-2003 23 db file sequential read 7
24-jan-2003 00 db file sequential read 1
24-jan-2003 03 db file sequential read 1
24-jan-2003 05 db file sequential read 1
24-jan-2003 09 db file sequential read 4
24-jan-2003 11 db file sequential read 2
24-jan-2003 13 db file scattered read 1
24-jan-2003 13 db file sequential read 2
24-jan-2003 14 db file sequential read 2
24-jan-2003 17 db file sequential read 1
24-jan-2003 21 db file sequential read 75
특정 테이블이나 인덱스에 대한 I/O 대기 추적하기
파일 넘버와 블록 넘버를 테이블 이름과 인덱스 이름으로 변환하면 분석작업을 좀더 용이하게 할 수
있을 것입니다. 이를 위해서는 dba_extents 뷰를 사용해서 테이블의 시작 블록과 마지막 블록을 계산할 수 있습니다. 어느 객체가 읽기
대기, 버퍼 대기가 있었는지 확인할 수 있습니다. 다음과 같이 dba_extents 뷰를 조인해서 세그먼트 이름을 추가하면 분석이 조금 더
용이할 것입니다. (리스트 6 참조)
스크립트를 수행하면 다음과 같은 결과를 얻을 수 있고, 모든 세그먼트에 10회 이상의 디스크 읽기
대기가 있었다는 것을 알 수 있습니다.
Wait Segment Segment Wait
Event Name Type Count
---------- ----------------------------------- ---------- ------------
SEQ_READ SYSPRD.S_EVT_ACT_F51 INDEX 72
SEQ_READ SYSPRD.S_ACCNT_POSTN_M1 INDEX 41
SEQ_READ SYSPRD.S_ASSET_M3 INDEX 24
SEQ_READ SYSPRD.S_ASSET_M51 INDEX 19
SEQ_READ SYSPRD.S_COMM_REQ_U1 INDEX 11
여기까지 우리는 순차적 읽기 대기가 발생한 인덱스를 찾는 방법과 SQL 튜닝, 분산 저장 방법에
대해 알아보았습니다. 다음 장에서는 "hot 블록"에 대해서 알아보겠습니다.
분석을 종료하기위해서 'Hot blocks"에 대한 모든 것을 알아보겠습니다.
stats$real_time_waits 테이블을 이용해서, 복수의 대기를 하고 있는 데이터 블록을 확인하는 방법을 알아보겠습니다.(리스트 7
참고)
대기가 발생한 세그먼트, 블록, 대기 이벤트 회수를 알 수 있습니다.
Multiple
Block
Wait Segment Segment Block Wait
Event Name Type Number Count
---------- ------------------------------ ---------- ---------- --------
SEQ_READ SYSPRD.S_EVT_ACT_F51 INDEX 205,680 7
SEQ_READ SYSPRD.S_EVT_ACT TABLE 401,481 5
SEQ_READ SYSPRD.S_EVT_ACT_F51 INDEX 471,767 5
SEQ_READ SYSPRD.S_EVT_ACT TABLE 3,056 4
SEQ_READ SYSPRD.S_EVT_ACT_F51 INDEX 496,315 4
SEQ_READ SYSPRD.S_DOC_ORDER_U1 INDEX 35,337 3
|
복수 대기를 하고 있는 데이터
블록을 식별할 수 있기 때문에 매우 중요합니다. 각 데이터 블록을
확인해서 세그먼트 헤더에 대한 경합을 알 수 있습니다.
트랜드 기반 이벤트 대기
분석
이벤트 대기에 대한 자세한
정보를 확인하면, 데이터 블록을 확인하고 트랜드 보고서를 생성할 수
있습니다. 리스트 8의 SQL 문을 사용해서 시간 단위의 순차적 읽기
지연 평균을 알아낼 수 있습니다.
주기적인 스케줄 작업을
하는 데이타베이스는 일종의 특질을 갖고 있습니다. 특질을 분석하기 위해
Statspack 패키지를 사용해 SQL 문을 생성하고 적절히 최적화할
수 있습니다.
읽기 대기가 계속
발생한다면 스케줄을 변경해서 다른 시간대에 문제가 되는 SQL 문을
수행하는 방법을 사용합니다. 실행 시간을 변경하면서 I/O 변화를
살펴보십시오.
일 단위로 트랜드를
확인하기 위해서는 리스트 9와 같이 순차적 읽기 대기 평균 정도를
발생하는 질의를 수행할 수도 있습니다.
그림 1과 같이 I/O 대기 데이터를 그래픽으로 나타내 보면
데이타베이스에서 반복되는 대기 지연 트랜드를 찾을 수 있습니다.(여기서는
Microsoft Excel 차트 위저드를 사용했습니다) 문제를
발생시키는 테이블과 인덱스를 찾기 위한 모든 정보는
stats$real_time_waits 뷰에 있습니다. Statspack
패키지와 함께 사용하면 stats$sql_summary와 같은 SQL
문을 얻어, SQL 레벨에서 디스크 대기 이벤트 분석을 할 수 있습니다.
|
|
<< 그림 1.
시간별 실시간 대기 평균 그래프 >> |
위 경우를 분석하면, db
파일 순차적 읽기 대기는 주로 2:00AM-3:00AM과
9:00PM-12:00PM 시간대에 최대값을 확인하고 Statspack
패키지를 이용해 그 시간대에 수행된 SQL 문을 조사합니다.
U본문에서 제공하는
스크립트를 이용해 요일별 읽기 대기를 요약할 수 있습니다.(그림
2 참조) 매 화요일과 목요일에 분산 읽기 대기가 최대값을
나타내며 이 날짜에 수행된 SQL 문을 역시 Statspack을 이용해
확인 합니다.
|
|
<< 그림 2.
요일별 실시간 대기 평균 그래프 >> |
대기 현상에 대한 소스를
확인 할 수 없기 때문에 유용하지 않을 수 도 있습니다. (Oracle9i
Release2 사용자는 statistics_level을 7이상으로
설정하면, v$ segment_statistics 뷰를 통해 정보를 확인
할 수 있습니다.)
하지만
stats$real_time_waits에 저장된 대기 원인을 알고 있기
때문에 쉽게 문제 객체를 확인할 수 있습니다. (리스트 10 참고)
문제가 있는 테이블과
인덱스는 다음과 비슷한 형식으로 파악될 수 있습니다.
Block
Segment Segment Wait
Date Hr. Name Type Count
-------------------- ------------------------------ ---------- --------
23-jan-2003 21 SYSPRD.S_COMM_REQ_SRC_U1 INDEX 23
23-jan-2003 21 SYSPRD.S_EVT_ACT TABLE 44
23-jan-2003 21 SYSPRD.S_EVT_ACT_F51 INDEX 16
23-jan-2003 22 SYSPRD.S_EVT_ACT TABLE 32
물리적 읽기 대기가 발생한
객체를 확인하면, 문제 해결을 위해 추가적인 디스크 스핀들 기법으로
객체를 분산할 수 있습니다.
검색과 튜닝
문서에서 사용한 실시간
대기 측정 방법을 사용하면 쉽게 물리적 읽기 대기가 발생한 객체를 확인
할 수 있습니다. 문제의 객체를 찾으면, Statspack을 사용해
SQL 레벨에서 방안을 찾고 튜닝을 합니다. 물리적 읽기 대기에서 튜닝은
리소스에 대한 경합을 줄이거나 데이터 버퍼 캐쉬 크기를 늘리는 방법으로,
실질적으로는 SQL 튜닝, 복수 디스크로 객체 스트립하기, KEEP
Pool 사용하기, SQL 문 수행시간 조정을 하는 것입니다.
Graham
Wood, Oracle사, Statspack 개발자와 Oracle
Guru에게 감사합니다. 정확하고 실용적인 글을 쓰게 도와주신
Graham에게 특별한 감사의 마음을 전합니다.
Donald K.
Burleson Oracle 데이타베이스 최고 전문가 중 한명. 저서 17권, 100회
이상 잡지 기고를 했고, Oracle Internal 편집장으로 활동
중. 최근 저서로 Rampant Techpress에서 "Creating
a self-tuning Oracle Database"을 출판.http://www.dba-oracle.com
,
http://www.remote-dba.net/사이트
참조 |
|
|
|
|
