Statspack을 통한 시스템 튜닝

Statspack을 통한 시스템 튜닝

저자 Rich Niemiec

 

Statspack 보고서에 대기 이벤트가 무엇을 의미하며, 이러한 이벤트를 튜닝하는 방법을 확인하십시오.

Oracle 유틸리티를 단 두 개만 사용하여 Oracle9i Database 시스템의 성능 문제를 확인 및 모니터링할 수 있다면 이러한 툴은 Oracle Enterprise Manager(현재 Release 4.0에서 제공되고 있음)와 Statspack이 될 것입니다. Oracle8i Release 8.1.6에서 이전 버전의 Oracle Database의 성능 모니터에 사용할 수 있는 UTLBSTAT/UTLESTAT 스크립트가 Statspack 유틸리티로 대체되었습니다. Statspack은 이러한 스크립트를 위한 여러 향상된 기능을 제공합니다. 본 칼럼에서는 대기 이벤트와 관련된 문제점을 해결하는 방법에 대해 살펴보도록 하겠습니다. 대기 이벤트가 발생하면 리소스를 사용할 수 있거나, 작업이 완료되는 등의 특정 시점이 될 때까지 시스템은 대기 상태가 됩니다.

 

상위 5개 대기 이벤트

시스템의 병목 현상을 제거하려는 경우에 가장 먼저 살펴보아야 할 부분이 Statspack 보고서의 상위 5개 대기 이벤트(Top 5 Wait Events ) 부분입니다. 보고서의 이 부분에는 상위 5개 대기 이벤트, 전체 대기 이벤트 목록 및 백그라운드 대기 이벤트가 표시됩니다. 시스템의 TIMED_STATISTICS 초기화 매개 변수가 true로 설정되면 대기 시간을 기준으로 이벤트의 순서가 지정되며, 일부 이벤트에서 대기가 표시되지 않기 때문에 이 설정이 더 선호되는 편입니다. TIMED_STATISTICS가 false로 설정되면 대기 개수를 기준으로 이벤트의 순서가 지정됩니다.

목록 1은 래치(latch free) 대기 뿐만 아니라 단일 블록 읽기(db 파일 순차 읽기)와 관련된 다수의 대기를 보여 줍니다. 이 목록에서는 일부 데이타 파일 및 로그 파일 쓰기 작업에 많은 대기가 있다는 것을 알 수 있습니다. 여기에서 주요 문제점이 되는 부분을 식별하려면 Statspack의 다른 부분에서 세부 보고서를 조사하여 목록의 범위를 좁혀야 합니다.

 

대기 이벤트 해결

다음 10가지 항목은 대기 이벤트를 일으키는 가장 일반적인 원인입니다. 각 항목에는 원인에 대한 설명과 이와 관련된 해결 방법이 함께 나와 있습니다.

 

1. DB 파일 분산 읽기(DB File Scattered Read). 이것은 일반적으로 전체 테이블 스캔과 관련된 대기를 나타냅니다. 메모리로 들어간 전체 테이블 스캔은 인접한 버퍼에 포함되는 대신, 버퍼 캐시 전체에서 분산됩니다. 여기서 대기 개수가 많다는 것은 테이블에 인덱스가 없거나 사용되지 않았을 수 있음을 의미합니다. 상황에 따라 인덱스 스캔보다 전체 테이블 스캔이 더 효율적일 수 있지만, 이러한 대기가 표시되면 전체 테이블 스캔이 필요한지 검사해야 합니다. 전체 테이블 스캔이 LRU(Least Recently Used) 목록의 끝 부분에 위치하므로 작은 테이블을 캐시하여 이러한 테이블이 반복적으로 읽히는 것을 방지해야 합니다.

 

2. DB 파일 순차 읽기(DB File Sequential Read). 이 이벤트는 일반적으로 단일 블록 읽기(예: 인덱스 읽기)를 나타냅니다. 여기서 대기 개수가 많다는 것은 테이블의 잘못된 조인 순서나 선택적이지 않은 인덱스화를 나타냅니다. 트랜잭션이 많은 적절히 조정된 시스템의 경우 대기 개수가 많은 것이 정상이지만, 상황에 따라 이것은 문제가 될 수 있습니다. 따라서 이러한 대기 통계를 Statspack 보고서의 다른 알려진 문제점(예: 불충분한 SQL)과 상호 관련시켜 조사해야 합니다. 또한 인덱스 스캔이 필요한지 여부와 여러 테이블 조인의 조인 순서를 확인해야 합니다. 이러한 대기가 표시되는 빈도는 DB_CACHE_SIZE에 의해 결정되기도 합니다. 문제의 해시 영역 조인은 PGA 메모리에 나타나야 하지만 이러한 조인은 순차 읽기에 대한 많은 수의 대기를 발생시킬 수 있는 메모리 호그가 될 수도 있습니다. 또한 이러한 조인은 직접 경로 읽기/쓰기 대기로 표시될 수도 있습니다.

 

3. 사용하지 않는 버퍼(Free Buffer). 이것은 사용할 수 있는 버퍼가 없기 때문에 시스템이 메모리에서 버퍼를 대기하고 있는 중임을 나타냅니다. 이 범주의 버퍼 대기는 모든 SQL이 조정될 경우 DB_BUFFER_CACHE를 늘려야 한다는 것을 나타낼 수 있습니다. 또한 이 대기는 선택적이지 않은 SQL로 인해 데이타가 인덱스 블록으로 버퍼 캐시를 채운다는 것을 나타낼 수 있는데, 이 경우 시스템 처리를 기다리고 있는 이러한 특정 명령문을 위한 버퍼를 전혀 남겨두지 않게 됩니다. 이것은 일반적으로 상당한 양의 DML(insert/update/delete)이 수행 중이고 DBWR(Database Writer)이 쓰기 속도가 충분하지 않기 때문에 버퍼 캐시가 여러 버전의 동일한 버퍼로 가득차게 되고, 결과적으로 효율성이 크게 떨어진다는 것을 의미합니다. 이 문제를 해결하기 위한 방법으로 더 많은 DBWR 프로세스를 사용하여 증분 체크포인트를 가속화하거나 실제 디스크 수를 늘리는 것을 고려할 수 있습니다.

 

4. 버퍼 사용 중(Buffer Busy). 이것은 버퍼를 공유할 수 없는 방식으로 사용 중인 버퍼 또는 버퍼 캐시로 읽어들이고 있는 버퍼에 대한 대기입니다. 버퍼 사용 중 대기는 1%를 초과해서는 안 됩니다. 버퍼 대기 통계 섹션(또는 V$WAITSTAT)을 검사하여 세그먼트 헤더에 대기가 존재하는지 확인한 다음, 만약 그럴 경우에는 사용 가능 목록 그룹을 늘리거나 pctused와 pctfree 간의 간격을 늘립니다. 대기가 실행 취소 목록에 있는 경우에는 롤백 세그먼트를 추가하여 이 문제를 해결할 수 있고, 대기가 실행 취소 블록에 있는 경우에는 이 일관성 읽기를 제공하는 테이블의 데이타 밀도를 줄이거나 DB_CACHE_SIZE를 늘려야 합니다. 

대기가 데이타 블록에 있는 경우에는 데이타를 다른 블록으로 이동하여 이러한 핫 블록을 방지하거나, 테이블의 사용 가능 목록을 늘리거나, LMT(Locally Managed Tablespace)를 사용할 수 있습니다. 대기가 인덱스 블록에 있으면 인덱스를 다시 작성하거나, 인덱스를 분할하거나, 역방향 키 인덱스를 사용해야 합니다. 더 작은 블록 크기를 사용하여 데이타 블록과 관련된 버퍼 사용 중 대기를 방지할 수도 있습니다. 이 경우에는 단일 블록 내의 레코드 수가 줄어들기 때문에 "핫" 블록이 만들어지지 않습니다. DML(insert/update/ delete)이 발생하면 Oracle Database는 블록 상태에 "관계 있는" 모든 사용자(Interested Transaction List, ITL)를 포함한 정보를 블록에 기록합니다. 

이 영역의 대기를 줄이려면 여러 ITL 슬롯을 허용하기 위해 블록에 공간을 만드는 initrans를 늘립니다. 또한 이 블록이 존재하는 테이블에서 pctfree를 늘릴 수도 있습니다. 이렇게 하면 지정된 initrans를 사용하여 작성한 슬롯이 충분하지 않을 경우, ITL 정보가 maxtrans에 의해 지정된 최대 수까지 기록됩니다.

다음 단계 Statspack에 대해 더 자세히 알아보려면 OTN에서 "Statspack을 통한 성능 조정"을 참조하십시오. otn.oracle.com/deploy/performance/pdf/ statspack_tuning_otn_new.pdf Rich Niemiec의 "Oracle Performance Tuning Tips and Techniques"을 Amazon.com에서 구입할 수 있습니다. amazon.com/oracle 지원을 받으려면 MetaLink를 방문하십시오 oracle.com/support/metalink/index.html Oracle9i 에 대한 교육을 받으려면 다음 사이트를 방문하십시오. otn.oracle.com/oramag/oracle/searchou.html

 

5. 사용 가능한 래치(Latch Free). 래치는 시스템 글로벌 영역(SGA)에서 공유 메모리 구조를 보호하는 데 사용되는 하위 수준 대기열 메커니즘이며, 실제로는 상호 배제 메커니즘이라고 부릅니다. 

매우 신속하게 획득 및 해제되는 메모리의 잠금과 유사한 기능을 가진 래치는 공유 메모리 구조에 대한 동시 액세스를 차단하는 데 사용됩니다. 래치를 사용할 수 없으면 사용 가능한 래치 실패가 기록됩니다.

대부분의 래치 문제는 바인드 변수(라이브러리 캐시 래치), 리두(redo) 생성 문제 (리두 할당 래치), 버퍼 캐시 경합 문제(캐시 버퍼 LRU 체인) 및 버퍼 캐시의 핫 블록(캐시 버퍼 체인) 사용이 실패하는 것과 관련됩니다. 

또한 버그와 관련된 래치 대기가 존재할 수 있으므로 이러한 대기가 의심스러운 경우에는 MetaLink에서 버그 보고서를 검사합니다(oracle.com/support). 래치 실패 비율이 0.5%를 초과하면 문제점을 조사해야 합니다. 래치 대기는 별도의 항목으로 자세히 다룰 필요가 있습니다. 따라서 다음에 게시될 Oracle Magazine 칼럼에서 이에 대해 자세하게 설명할 것입니다.

 

6. 대기열에 넣기(Enqueue). 대기열에 넣기는 공유 리소스를 보호하는 잠금입니다. 잠금은 공유 리소스(레코드의 데이타)를 보호하여 두 명의 사용자가 동일한 데이타를 동시에 업데이트하는 것을 방지합니다. 대기열에 넣기는 선입 선출법(FIFO)을 사용하는 대기열 메커니즘을 포함합니다(Oracle의 래치 메커니즘은 FIFO가 아님).

일반적으로 대기열에 넣기 대기는 ST, HW, TX4 및 TM 대기열에 넣기를 가리킵니다. ST 대기열에 넣기는 딕셔너리 관리 테이블스페이스의 할당이나 공간 관리에 사용됩니다. 문제가 있는 딕셔너리 관리 테이블스페이스에 대해서는 LMT를 사용하거나, 확장 영역을 미리 할당하거나, 최소한 다음 확장 범위를 더 크게 만들어야 합니다. HW 대기열에 넣기는 고수위 세그먼트와 함께 사용되기 때문에 확장 범위를 수동으로 할당할 경우 이 대기를 우회할 수 있습니다. TX4는 가장 일반적인 대기열에 넣기 대기입니다. TX4 대기열에 넣기 대기는 일반적으로 세 가지 문제점 중 하나로 인해 발생합니다. 첫 번째 문제는 고유한 인덱스에 존재하는 중복된 값으로 인해 발생하는데, 이 경우에는 커밋/롤백을 실행하여 대기열에 넣기를 해제해야 합니다. 

두 번째 문제는 동일한 비트맵 인덱스 부분을 여러 사용자가 동시에 업데이트할 때 발생합니다. 단일 비트맵 부분이 여러 ROWID를 포함할 수 있기 때문에 여러 사용자가 동일한 부분을 업데이트하려고 할 경우에는 커밋/롤백을 실행하여 대기열에 넣기를 해제해야 합니다. 가장 흔히 볼 수 있는 세번째 문제는 여러 사용자가 동일한 블록을 업데이트할 때 발생합니다. 이 경우에는 사용 가능한 ITL 슬롯이 없을 경우 블록 수준 잠금이 발생할 수 있습니다. 이 문제는 간단하게 방지할 수 있는데, initrans 및/또는 maxtrans를 늘려 여러 ITL 슬롯을 허용하거나 테이블에서 pctfree를 늘리면 됩니다. 

마지막으로 TM 대기열에 넣기는 영향 받은 객체에 대한 DDL을 방지하기 위해 DML 도중에 발생합니다. 외래 키가 있는 경우에는 일반적으로 발생하는 이 잠금 문제를 방지하기 위해 외래 키를 인덱스해야 합니다.

 

7 로그 버퍼 공간(Log Buffer Space). 이 대기는 LGWR이 리두 로그에 쓸 수 있는 것보다 빠르게 사용자가 로그 버퍼를 쓰고 있거나 로그 스위치가 너무 느린 경우에 발생합니다. 이 문제를 해결하려면 로그 파일의 크기를 늘리거나, 로그 버퍼의 크기를 늘리거나, 더 빨리 쓸 수 있는 디스크를 사용합니다. 또한 고속의 고체 상태 디스크를 사용하는 것을 고려할 수도 있습니다.


8. 로그 파일 스위치(Log File Switch). 모든 커밋 요청은 "로그 파일 스위치(필요한 아카이브)" 또는 "로그 파일 스위치(불완전한 체크포인트)"를 기다립니다. 아카이브 디스크가 가득찼거나 속도가 느리지 않은지 확인해야 하며, DBWR은 I/O로 인해 너무 느릴 수 있습니다. 경우에 따라서는 더 크거나 많은 리두 로그를 추가해야 할 수 있고, DBWR이 문제인 경우에는 데이타베이스 기록자를 추가해야 할 수도 있습니다. 

9. 로그 파일 동기화(Log File Sync). 사용자가 데이타를 커밋하거나 롤백하면 LGWR은 세션의 리두를 로그 버퍼에서 리두 로그로 비웁니다. 로그 파일 동기화 프로세스는 이 작업이 성공적으로 끝날 때까지 기다려야 합니다. 여기서 대기 이벤트를 줄이려면 더 많은 레코드의 커밋을 시도합니다(예를 들어, 한 번에 하나씩 커밋하는 대신 50개를 일괄적으로 커밋). LGWR에서 아카이브 효과를 줄이려면 리두 로드를 더 빠른 디스크에 저장하거나 다른 물리적 디스크에 저장합니다. RAID 5는 속도가 매우 느리기 때문에 쓰기 작업이 많은 애플리케이션에서는 사용해서는 안 됩니다. 대신, 정보를 고속으로 쓸 수 있는 파일 시스템 직접 I/O 또는 원시 장치를 사용하는 것을 고려합니다.


10. 휴지 이벤트(Idle Event). 출력 이후에 나열되는 여러 휴지 대기 이벤트가 존재하는데, 이러한 이벤트를 무시할 수 있습니다. 일반적으로 휴지 이벤트는 각 부분의 맨 아래에 나열되며, 이 이벤트에는 클라이언트에 대한 또는 클라이언트로부터의 SQL*Net 메시지나 다른 백그라운드 관련 타이밍 등이 포함됩니다. 휴지 이벤트는 stats$idle_event 테이블에 나열됩니다.

 

지속적인 조정

다음에 게시될 Oracle Magazine에서는 또 다른 상위 대기 이벤트인 래치에 대해 설명할 계획입니다. 일반적으로 보게 되는 래치 대기와 이러한 대기를 조정하여 최상의 성능을 확보하는 방법에 대해 설명될 것입니다.

Rich Niemiec은 TUSC(www.tusc.com)의 CEO이자 International Oracle Users Group(www.ioug.org)의 회장입니다. 본 칼럼의 편집은 Steve Adams가 수고해주셨습니다.

대기 이벤트 퀵 레퍼런스 가이드
대기 문제	해결 방법
DB 파일 분산 읽기	여러 전체 테이블 스캔을 나타냄: 코드를 조정하고 작은 테이블을 캐싱합니다.
DB 파일 순차 읽기	여러 인덱스 읽기를 나타냄: 코드(특히 조인)를 조정합니다.
사용하지 않는 버퍼(Free Buffer)	DB_CACHE_SIZE를 늘리고, 체크포인트를 줄이고, 코드를 조정합니다..
사용 중인 버퍼	세그먼트 헤더: 사용 가능 목록 또는 사용 가능 목록 그룹을 추가합니다
사용 중인 버퍼	데이타 블록: initrans 및/또는 maxtrans를 늘립니다.
사용 중인 버퍼	데이타 블록: "핫" 데이타를 분리하고 역방향 키 인덱스 및/또는 더 작은 블록을 사용합니다.
사용 중인 버퍼	실행 취소 헤더: 롤백 세그먼트 또는 영역을 추가합니다.
사용 중인 버퍼	실행 취소 블록: 더 자주 커밋을 실행하고 더 큰 롤백 세그먼트 또는 영역을 사용합니다.
사용 가능한 래치	래치 세부 사항을 조사합니다.
대기열에 넣기-ST	LMT를 사용하거나 큰 확장 영역을 미리 할당합니다.
대기열에 넣기-HW	최고 수위보다 높게 확장 영역을 미리 할당합니다.
대기열에 넣기-TX4	테이블이나 인덱스에서 initrans 및/또는 maxtrans를 늘립니다.
대기열에 넣기-TX4	외래 키를 인덱싱하고 테이블의 애플리케이션 잠금을 검사합니다.
로그 버퍼 공간	로그 버퍼를 늘리고 더 빠른 디스크를 리두 로그에 사용합니다.
로그 파일 스위치	아카이브 대상이 가득 찼거나 느리지 않은지 확인하고 더 크거나 많은 리두 로그를 추가합니다.
로그 파일 동기화	한 번에 더 많은 레코드를 커밋하고 더 빠른 리두 로그 디스크나 원시 장치를 사용합니다
휴지 이벤트	이벤트를 무시합니다.

일반 휴지 이벤트
이벤트	휴지 이벤트 유형
Dispatcher timer	공유 서버
Lock manager wait for remote message	Oracle9i Real Application Clusters
Pipe get	사용자 프로세스
pmon timer	백그라운드 프로세스
PX Idle wait	병렬 질의
PX Deq Credit: need buffer	병렬 질의
PX Deq Credit: send blkd	병렬 질의
rdbms ipc message	백그라운드 프로세스
smon timer	백그라운드 프로세스
SQL*Net message from client	사용자 프로세스
virtual Circuit status	공유 서버