Statspack를 통한 고급 시스템 튜닝

Statspack를 통한 고급 시스템 튜닝 By Rich Niemiec   데이타베이스 성능 모니터링 시리즈 중 두 번째 편 이전 칼럼에서 언급했던 것처럼, 시스템 성능 문제를 모니터하고 찾기 위해 Oracle9i 유틸리티를 두 개만 선택해야 한다면 그것은 반드시 Enterprise Manager와 Statspack이어야 합니다. 그런데, Oracle9i에서는 (Oracle8i Release 8.1.6에서처럼) 데이타베이스의 성능을 모니터하기 위해서 STATSPACK을 사용할 수가 있습니다. 이 STATSPACK는 Oracle의 이전 버전에서 사용 가능하였던 UTLBSTAT/UTLESTAT 스크립트들을 대체한 것인데, 그 스크립트들을 많이 보완하여 훨씬 뛰어난 성능을 제공하고 있습니다. 이전에, 우리는 대기 이벤트들을 모니터하고 해결하기 위해서 어떻게 Statspack 리포트를 사용하는 가에 대하여 알아보았습니다. 이번에는 래치 대기를 조정하는 문제들과 문서화되지 않은 초기화 매개 변수들(밑줄로 선행된 매개 변수들)을 다룰 것입니다. 한편, 이 매개 변수들을 변경하기 전에는 Oracle Support를 통해 검증을 받는 것이 좋습니다.   래치의 기본들 래치는 메모리 내의 공유 메모리 구조 (system global area, SGA) 보호를 위해 사용하는 상호 배제 방법으로서, 프로세스에 의해서 매우 빨리 활성화되었다가 해제되는 메모리 상의 잠금과 비슷한데, 공유 메모리 구조에 대한 동시 접근을 막기 위해서 사용되어 집니다. 만약 래치를 사용할 수 없다면, 래치-프리 실패가 기록됩니다. 그리고 대부분의 래치 문제들은 바인드 변수(라이브러리-캐시 래치), 리두-생성 문제 (리두-할당 래치), 버퍼-캐시 충돌 문제(캐시-버퍼 LRU (least recently used) 체인), 버퍼 캐시 내의 핫 블록 (캐시-버퍼 체인) 등을 사용하지 않는 것과 관련 있습니다. (일부 래치 대기들은 제품 버그 때문에 발생하므로, Oracle Support의 MetaLink 사이트를 확인해 필요가 있습니다) 만약 래치 실패 비율이 0.5 % 이상이라면 문제를 조사해야만 할 것입니다. (래치 실패 비율을 구하는 방법은 아래에 나와 있습니다.) 래치는 두 가지 종류가 있습니다: willing-to-wait 래치와 not-willing-to-wait 래치 — 첫번째는 래치가 사용 가능해 지는 것을 기다리고, 두 번째는 기다리지 않습니다. 라이브러리 캐시 래치와 같은 willing-to-wait 래치는 래치를 얻으려고 하는데, 만일 없다면 기다리면서 래치를 다시 요청하게 됩니다. 그 래치는 _SPIN_COUNT라는 문서화되지 않은 초기화 매개 변수에 따라 계속 기다리게 됩니다. 만약, _SPIN_COUNT에 따라 기다린 후에도 래치를 얻지 못 했다면, 1/100초 동안 휴식한 후에 다시 깨어나게 될 것입니다. 그리고 나서, 그 래치는 _SPIN_COUNT에 따라 기다리고, 이번에는 2/100초 동안 휴식하는 것과 같이, 다시 시작하기 전에 두 번 이 과정을 실행하게 됩니다. 이 과정 후에는 래치를 얻을 때까지는 계속 휴식 시간이 두 배가 될 것이고, 래치가 휴식을 취할 때마다 래치 휴식 대기를 생성합니다. 일부 래치들은 기다리려 하지 않습니다. 리두-복사 래치의 경우와 같이 이러한 유형의 래치는 기다리지 않고, 오히려 즉각적으로 래치를 다시 얻으려 시도합니다.   대기-래치 정보 보기 V$LATCH 뷰의 immediate_gets와 immediate_misses 열들과 Statspack 리포트의 래치 섹션에서 willing-to-wait 와 not-willing-to-wait 래치 모두에 대한 정보를 볼 수가 있습니다. V$LATCH 뷰를 질의하거나 Statspack 리포트의 래치-작업 섹션을 보게 되면 얼마나 많은 프로세스들이 대기해야만 하고 (래치 실패), 또는 휴식해야만 하는지를 (래치 휴식) 알 수가 있습니다. 그리고, V$LATCHHOLDER, V$LATCHNAME, V$LATCH_CHILDREN 모두는 래치 문제들을 조사하는데 도움이 됩니다. Table 1은 래치 이름, 래치 실패 (Pct Get Miss 열), 래치 휴식 (Avg Slps/Miss 열) 등을 기술하는 Statspack 리포트의 래치-작업 섹션의 일부 목록을 보여주고 있습니다. 그리고, 이 특정 리포트는 라이브러리 캐시 문제도 보여 주는 역할도 함께 하고 있습니다. Statspack 리포트의 대기 이벤트 섹션을 검사할 때 래치의 적중률은 99%에 접근해야 하며, 실패는 1%를 넘어서는 안됩니다. 그러면, Table 1에서와 같이 리포트의 이 섹션에 있는 행들을 검사해 보도록 합시다.   사용가능한 래치: 사용 가능한 래치가 대기 이벤트 섹션에서 문제가 된다면, 리포트의 래치 섹션에도 조사해야 될 문제들이 있는 것입니다. 이 섹션은 휴식 래치 (래치를 얻지 못 했기 때문에 다음 시도까지 휴식하고 있는) 또는 스핀 래치 (기다리면서 스핀 카운트에 따라 재시도를 하는) 와 같이 어떠한 래치가 문제가 되는지를 찾는데 도움을 줍니다.   행 캐시 객체: 행 캐시 객체 래치 경합은 일반적으로 데이타 딕셔너리에 경합이 있다는 것을 의미합니다. 또는 공용 동의어들에 의존하고 있는 SQL 문을 과도하게 구문 분석한 결과일 수도 있습니다. 그런데 공유 풀을 증가하면, 이 래치 문제는 대개 해결이 됩니다. 그러므로 라이브러리 캐시 래치 문제의 경우에는 문제 발생 전에 공유 풀을 증가시키십시오.   캐시 버퍼 체인: 캐시-버퍼 체인은 데이타베이스 캐시 버퍼를 위해 SGA 버퍼 캐시를 스캔할 때 필요합니다. 그리고, 버퍼 캐시에 위치하는 핫 블록들은 (종종 액세스 되어지는) 캐시-버퍼 체인 래치 문제들을 발생시키는데, 이것들은 서툴게 조정된 SQL 문의 현상일 수도 있습니다. 또한 핫 레코드는 동일한 체인에 “해시된” 블록 뿐만이 아니라 그 블록 안에 있는 다른 레코드들에게도 문제를 발생시킬 수 있는 핫 블록을 생성합니다. 그런데, 핫 블록을 찾기 위해서는 주소를 위해 V$LATCH_CHILDREN을 질의하여야 합니다. 그런 다음, 이 래치에 의해서 보호 받고 있는 블록들을 식별하기 위하여 그것과 V$BH를 조인합니다. (그렇게 하는 것은 핫 블록에 의해 영향을 받는 모든 블록들을 보여주게 됩니다) 한편, file#에 기반한 DBA_EXTENTS와 V$BH에서 찾을 수 있는 dbablk을 질의해서 객체를 식별할 수도 있습니다. 만약, 그 핫 블록이 인덱스에 있다면 역방향 키 인덱스를 사용하는 것은 순차 레코드들을 다른 블록들로 이동시켜서 그것들이 체인내의 핫 블록에 의해서 잠기지 않게 해줍니다. 만약, 핫 블록이 인덱스 루트 블록이라면, 역방향 키 인덱스는 별로 도움이 되지 않을 것입니다. 이 경우에는 _DB_BLOCK_HASH_BUCKETS를 버퍼 개수 (DB_CACHE_SIZE/DB_BLOCK_SIZE)의 두 배보다 더 큰 소수로 설정하면 문제를 해결할 수 있습니다. Oracle9i 이전에는, 이 매개 변수의 기본값이 잘 못 설정되어 래치에 많은 경합이 발생했었습니다; Oracle9i에서는 기본값이 정확하게 소수로 설정되어 있습니다.   캐시 버퍼 LRU 체인: 캐시 버퍼 LRU 체인 래치는 버퍼 캐시에 있는 블록들의 모든 것을 포함하고 있는 LRU 체인을 스캔할 때 사용이 됩니다. 작은 버퍼 캐시, 과도한 버퍼 캐시 처리 능력, 많은 캐시 기반의 정렬, 그리고 DBWR의 작업 로드 유지 실패 등은 모두 이러한 문제들을 발생시키는 원인이 됩니다. 그러므로, 과도한 논리적 읽기 문제를 발생시킬 수 있는 질의들은 수정해야만 합니다. 그리고, 경합을 줄일 수 있는 다중의 LRU 래치들을 갖기 위해서는 초기화 매개 변수인 _DB_BLOCK_LRU_LATCHES (Oracle9i에서는 숨겨진)를 증가시킬 수도 있습니다. 일반적으로, 비-SMP (대칭 다중처리) 머신들은 오로지 단일의 LRU 래치만을 필요로 합니다. Oracle은 자동적으로 이것을 SMP 머신에 있는 CPU 개수의 반으로 설정하게 합니다. 그러므로, 각각의 데이타베이스 기록자를 위해서는 최소한 하나의 LRU 래치를 가져야만 합니다;만약 데이타베이스 기록자를 증가한다면 이 숫자도 증가시켜야 하는 것을 잊지 마십시오.   라이브러리 캐시와 공유 풀: 라이브러리 캐시 래치는 라이브러리 캐시에 있는 객체들에 대한 액세스를 연속화 합니다. 그리고, 이 래치는 SQL 프로시저, PL/SQL 프로시저, 패키지, 함수, 트리거 등이 실행될 때마다, 또는 구문 분석 작업 동안 사용되어 집니다. Oracle8i에서는 단일의 공유 풀 래치가 라이브러리 캐시에서의 메모리 할당을 보호하는 역할을 하였습니다. 그러나, Oracle9i에는 래치의 경합을 줄여 줄 수 있는 일곱 개의 하위 래치들이 있습니다. 다음 단계 READ MORE more about Statspack amazon.com/oracle Read more on Statspack in Rich Niemiec's Rich Niemiec의 “Oracle9i Performance Tuning Tips and Techniques” 에서 Statspack에 대한 더 많은 정보를 얻을 수 있습니다(Amazon.com에서 구입 가능) MetaLink에서 지원을 얻을 수 있습니다  oracle.com/kr/support/metalink 공유 풀, 라이브러리 캐시 핀, 또는 라이브러리 캐시 래치 등에서 발생하는 경합은 공유 풀이 너무 작거나 명령문들이 재사용되지 않을 때 주로 발생됩니다. 명령문은 바인드 변수가 사용되지 않고, 일반적이나 정확치 않은 SQL문들이 공유 풀을 채울 때에는 거의 재사용되지 않습니다. 그러므로, 바인드 변수를 사용하지 않으면서 많은 명령문으로 공유 풀을 채운 사용자가 바인드 변수를 사용하지 않는 또 다른 많은 명령문들로 확장된 공유 풀을 채울 것이기 때문에 단지 공유 풀의 크기를 증가시키는 것은 래치 문제를 악화시킬 수도 있습니다. 그런데, CURSOR_SHARING= FORCE (Oracle9i 에서는 CURSOR_SHARING=SIMILAR) 초기화 매개 변수를 설정하면, 이러한 문제를 해결할 수도 있고, 또한 바인드 변수가 사용되지 않을 때 발생하는 문제점들도 줄일 수가 있습니다. 그러나, 공유 풀과 라이브러리 캐시 래치 문제는 처리해야 할 SQL 문에 비하여 캐시가 너무 적게 설정되어, 라이브러리 캐시가 공백을 필요로 할 때 발생할 수도 있습니다. 한편, SQL 또는 PL/SQL 문을 로드하기 위해 공백을 사용할 수 있음과 동시에, 래치는 배타적으로 소유되어 지고, 다른 사용자들은 기다려야만 합니다. 마지막으로, DBMS_SHARED_POOL.KEEP 프로시저를 사용한다면, 공유 풀을 증가시키거나, 다량의 SQL 문과 PL/SQL 문을 메모리에 고정시킴으로써 경합을 줄일 수가 있을 것입니다.   리두 할당: 공백을 리두 로그 버퍼에 할당하는 리두 할당 래치를 통해 리두 로그 버퍼의 로드를 줄일 수 있는 NOLOGGING 기능을 사용함으로써 경합을 줄일 수가 있습니다. 그리고, 불필요한 커밋을 하지 않도록 하십시오. 리두 복사: 리두 복사 래치의 개수는 2*CPU_ COUNT의 기본값을 가지고 있습니다만, _LOG _SIMULTANEOUS_COPIES 초기화 매개 변수를 사용해서 설정될 수도 있습니다. 그리고, 이 매개 변수를 증가시키는 것은 PGA의 리두 레코드들을 리두 로그 버퍼로 복사할 때 사용되는 리두 복사 래치의 경합을 줄일 수도 있습니다. 일부 래치 문제들은 과거의 제품 버그와 관련이 있기 때문에 래치와 관련된 문제들의 경우에는 MetaLink를 항상 확인해 보아야 하고, 그리고 99% 이하의 적중률을 갖는 래치들은 조사해 보아야만 합니다. Rich Niemiec는 TUSC (www.tusc.com)의 CEO이면서 International Oracle Users Group (www.ioug.org)의 president입니다. Table 1. 라이브러리 캐시 문제를 보여 주는 Statspack 리포트 래치 입수 요청 Pct(%) 입수 실패 평균 휴식/실패 대기 시간 대기없음 요청 Pct(%) 대기없음 실패 KCL freelist 래치 4,924 0.0     0   캐시 버퍼 핸들 968,992 0.0 0.0   0   채시 버퍼 체인 761,708,539 0.0 0.4   21,519,841 0.0 캐시 버퍼 LRU 체인 8,111,269 0.1 0.8   19,834,466 0.1 라이브러리 캐시 67,602,665 2.2 2.0   213,590 0.8 리두 할당 12,446,986 0.2 0.0   0   리두 복사 320 0.0     10,335,430 0.1 user lock 1,973 0.3 1.2   0   일반 래치 문제 래피 문제 잠재적 수정 라이브러리 캐시 바인드 변수 사용;SHARED_ POOL_SIZE 조정 공유 풀 바인드 변수 사용; SHARED_ POOL_SIZE 조정 리두 할당 리두 생성 최소화와 불필요한 커밋 안 함 리두 복사 _LOG_SIMULTANEOUS_ 증가 행 캐시 객체 공유 풀 증가 캐시-버퍼 체인 _DB_BLOCK_HASH_ BUCKETS 조정 필요 캐시-버퍼 _DB_BLOCK_LRU_LRU_LATCHES 사용 또는 다중의 버퍼 풀