본문 바로가기

데이터베이스(DA, AA, TA)/Oracle

[오라클] 트러블 슈팅 팁(1)

트러블의 종류



실제 환경에서 데이터베이스 트러블이 발생하면 원인을 찾아야 합니다. 그런데 어디서부터 어떻게 확인해야 할까요? 일단 데이터베이스와 관련한 트러블(오류) 종류를 크게 나누면 아래와 같습니다.


 장애 패턴

 패턴 개요

 장애 예

 사양 오류

 애플리케이션 또는 데이터 문제로 발생하는, 데이터베이스 동작은 문제없는 사양의 오류

 데이터 문제로 발생하는 오류, 예를 들어 테이블 필드 정의 자릿수보다 큰 숫자 데이터를 저장하려 하면 'ORA-12899: value too large for column'이 발생함.

 내부 오류 등 예상 밖의 동작

 예상하지 못한 사태로 데이터베이스에 어떤 문제가 발생한 경우의 내부 오류 등

 ORA-600, ORA-7445(내부 오류) 또는 아카이브 로그 출력 대상이 DISKFULL 되어 출력할 수 없는 경우 발생하는 ORA-257, 데이터 파일 또는 제어 파일 손상 또는 예상치 못한 프로세스 다운 등에 의한 ORA 오류 등

 성능 지연 문제

 기능 오류가 아닌, 성능 지연으로 발생하는 트러블

 데이터베이스 오류로 감지되지 않고 애플리케이션 측에서 타임아웃 오류 또는 사용자 클레임으로 발각, 잡넷으로 경고 감지, CPU 사용량 증가 또는 메모리 고갈처럼 OS 리소스 모니터로 경고감지(확인해보면 장시간 실행되는 SQL이 존재하기 때문) 등


장애가 발생하면 일단 어디에서 어떤 오류가 발생하는지 또는 처리 지연 문제인지 등을 확인해야 합니다. 이를 확인했다면 위의 표를 사용해 어떤 종류의 트러블인지 구분할 수 있습니다.



* 사양 오류

애플리케이션 측에서 오류를 감지해 애플리케이션 로드로 출력된 것을 모니터링 툴에서 감시하여 장애가 발각되거나, 사용자 클레임 등으로 발각되는 경우입니다. 이러한 종류의 문제는 오류 메시지를 기반으로 오라클 오류 메시지 매뉴얼 등을 참조하면서 원인을 찾아야 합니다.



* 내부 오류 등 예상 외 동작

일반적으로 오라클 데이터베이스의 경고 로그에 ORA 오류가 발생합니다(ORA-600 등, 경우에 따라서는 출력되지 않을 수 있습니다)


이런 종류의 문제는 오라클의 오류 메시지, 오류 코드를 기반으로 오라클 오류 메시지 매뉴얼 또는 오라클 공개 기술 정보 등을 참조해서 원인을 찾아야 합니다. 원인을 찾았다면 그에 대한 대처를 수행합니다. 만약 원인을 찾지 못했다면 오라클 서포트에 문의하는 것이 좋습니다. 이때 경고 로그 이외에도 오류 발생 시간 부근의 트레이스 파일 또는 Core 파일이 출력되지 않았는지 등도 확인하여 문의시 함께 제출하면, 오라클 서포트 쪽에서의 대응이 훨씬 원활하게 진행될 것입니다.



* 성능 지연 문제

이런 종류의 문제는 일단 성능 바틀넥이 있는 부분을 확인해야 합니다. 따라서 테이크닉이 조금은 필요한 부분입니다. 성능 넥이 일어나는 부분이 데이터베이스에 없는 경우도 있는데요, 이러한 때는 오라클 서포트에 문의해도 빠르게 대응할 수 없습니다. 


이유도 잘 모르겠는데 장애 지연 상황이 해소되었다면, 과거 상태를 분석해야 합니다. 이때는 Statspack(Diagnostics Pack 라이선스가 있다면 AWR 또는 ASH)을 사용해서 과거 시점의 상황을 확인합니다. 만약 지연이 발생하는 중이라면 Enterprise Manager 화면에서 실시간 정보를 확인하고 V$SESSION, V$SESSION_WAIT, V$LOCK 등의 동적 성능 뷰를 참조해서 상황을 확인합니다. 여기서 확인한 상황을 바탕으로 문제를 찾으면 됩니다.



AWR 이벤트 활용


'DB CPU가 높은 상태라면 아무 문제도 없다'라는 말이 있습니다. Statspack 리포트 또는 AWR(Auto Workload Repository) 리포트의 Top 5 Timed Events(또는 Top 10 Foreground Events by Total Wait Time)을 확인한 사람들이 자주 하는 이야기 입니다.


이는 DB CPU가 대기 이벤트 상태가 아니므로 'DB CPU가 높다' = '대기가 적게 CPU를 사용한다'고 생각하는 것입니다. 하지만 대기가 적어 DB CPU가 높다는 것은 정말 아무 문제가 없다는 의미일까요?


이번 팀에서는 Top 5 Timed Event를 확인할 때 추가로 확인하면 좋을 부분들을 소개합니다. Top 5 Timed Event를 확인할 때 이들 부분을 확인한다면 정확하게 분석할 수 있습니다.



* DB CPU가 높다 <> 문제가 없다

DB CPU가 높다는 것이 대기가 적다는 뜻은 아닙니다. CPU 처리에 많은 시간이 든다는 의미입니다. 예를 들어 배치 처리에서 DB CPU가 높을때, 다른 대기 이벤트 시간이 짧더라도 DB CPU가 길면 배치처리 시간도 길어집니다. 자주 있는 경우로는, SQL 처리 1회는 대기 없이 처리하지만 해당 SQL이 배치 처리 중 반복해 실행되면 배치 시간이 길어지는 경우가 있습니다. 한번의 SQL 처리시간이 대기가 거의 없는 10msec이라도, 100만번 반복되면 3시간 정도 걸립니다.


따라서 'DB CPU가 높다' = '문제가 없다'라는 공식은 성립하지 않습니다.


<DB 처리 시간> = <DB가 사용한 CPU 시간> + <비 유휴 대기 시간>

AWR 리포트의 내용에서는 다음과 같이 나타납니다.

<DB TIME> = <DB CPU> + <non-idle wait time>


따라서 DB CPU가 높은 경우에는 해당 상태가 적절한지를 다른 섹션을 확인하면서 판단해야 합니다. 대표적으로 CPU를 소비하는 처리로는 SQL 파싱 처리, 데이터 읽어들이기, 연산 처리 등이 있습니다. 그러므로 AWR 리포트에서 다음과 같은 다양한 섹션을 통합적으로 분석해서 판단해야 합니다.


  관점

 AWR 리포트 확인 포인트

 주목해야 할 부분

 처리량

 Load Profile

 처리량

 SQL ordered by Executions

 실행 횟수가 많은 SQL

 CPU 시간

 SQL ordered by CPU Time

 CPU 시간이 많은 SQL

 SQL 파싱 시간

 Instance Efficiency Percentages

 SQL 파싱 상태

 SQL ordered by PArse Calls

 파싱 횟수가 많은 SQL

 데이터 읽어 들이기

 Segments by Logical Reads

 접근 블록 수가 많은 세그먼트

 SQL ordered by Gets

 접근 블록 수가 많은 SQL

 SQL ordered by Reads

 디스크 접근 수가 많은 SQL



* Wait Event Histogram에서 주목할 부분

Top 10 Foreground Events by Total Wait Time에는 Wait Avg(ms)라는 필드가 있습니다. 이 값은 1회 평균 대기 시간을 파악할 때 사용할 수 있습니다. 이 필드는 대기 이벤트의 합계 시간이 발생 횟수가 많기 때문인지, 아니면 각 대기 시간이 길지 때문인지 판단할 때 유용합니다. 발생 건수가 많은 대기라면 발생 횟수를 줄이는 대책을 생각하게 되고, 각 대기 시간이 길기 때문이라면 대기 시간 자체를 감소시키는 대책을 생각하며 됩니다.


하지만 이 값은 평균값(합계 시간을 대기 횟수로 나눈 값)이므로 주의해야 합니다. 동시 처리가 많은 시스템에서는 I/O 처리 또는 같은 레코드에 대한 처리가 충돌하면서, 기다리는 레코드와 필드가 길어지고 대기 시간도 길어지는 경우가 있습니다. 따라서 가각의 평균 대기 시간이 균일하지 않을 수 있으므로 Wait Event Histogram에도 주목해야 합니다.


또한 어던 처리가 너무 느려지는 등의 문제가 발생했다면 Wait Event Histogram에 주목하기보다, 평소와는 달리 장시간 동안 실행되는 대기가 발생하지 않았음을 조사하는 것이 좋습니다(처리가 늦어지는 원인이 해당 대기인지 판단하려면 V$SESSION 또는 ASH 등의 상세한 정보가 필요합니다)



* 대기 클래스에서 주목할 부분

Statspack 리포트에는 출력되지 않지만, AWR 리포트를 보면 Wait Classes by Total Wait Time 섹션이 출력됩니다. 이 섹셔에는 대기 클래스의 대기 시간 통계가 출력됩니다.


대기 클래스란 대기 이벤트를 특징 또는 특성에 기반해 그룹으로 나눈 것입니다. 비유하자면 각각의 대기 이벤트가 나무라면 대기 클래스는 숲에 해당합니다.


각 대기 이벤트의 상세를 확인하기 전에 대기 클래스를 확인하면, 시스템의 경향 또는 보틀넥 부분을 쉽게 알 수 있으므로 어떤 부분이 문제인지 압축해서 확인할 수 있습니다.


예를 들어 "대기 이벤트인 direct path write temp 또는 direct path read가 높군"이라고 확인하는 대신, USER I/O 대기 클래스가 높다는 상황부터 파악한다고 해보겠습니다. 이렇게 하면 USER I/O 관계의 대기 이벤트의 상태, 물리 로딩이 많은 SQL 또는 물리 I/O가 많은 객체의 유무 등, 더 큰 관점에서 상태를 분석할 수 있습니다.



인터커넥트 바틀넥을 찾아내는 성능 분석 요령


시스템을 개발할 때는 성능 테스트 또는 성능 검증이라 부르는 태스크를 계획합니다. 이 작업을 간단하게 말하면 '성능 목표를 달성할 때까지 실제로 동작시켜보면서 응답 시간과 스루풋을 측정하고, 바틀넥 튜닝을 반복하는 작업'입니다.


어떤 리소스가 바틀넥인지 찾을 때 StatsPack 또는 AWR 리포트를 활용하는 것이 좋습니다. 예를 들어서 Oracle Real Application Clusters(이하 Oracle RAC) 구성의 데이터베이스에서 캐시 퓨전이 증가했다면, 네트워크에서 새로운 바틀넥이 발생하지 않았는지 확인해야 합니다. 또한 데이터베이스 서버에 CPU 또는 메모리를 추가한 경우에도 마찬가지로 네트워크에 새로운 바틀넥이 발생하지 않았는지 확인해야 합니다.


이번 팁에서는 Oracle RAC 노드가 서로 통신하는 인터커넥트에 사용되는 네트워크 스위치에 바틀넥이 걸린 경우를 다룹니다. 성능이 떨어졌을 때 오라클에서 어떤 상태가 감지되는지를 다루므로, 인터커넥트에서의 바틀넥을 찾는 요령을 이해할 수 있습니다.



* 인터커넥트 바틀넥으로 인한 대기 이벤트의 특징

인터커넥트의 네트워크 스위치에 바틀넥이 발생했을때, Global Resource 계열의 대기 이벤트에 나타나는 특징이 있습니다. Global Resource는 버퍼 캐시의 블록처럼 Oracle RAC 환경에 존재하는 리소스입니다.


Global Resource 계열의 대기 이벤트에는 다음과 같은 것이 있습니다.


  - gc [current/cr] multi block request

  - gc [current/cr] block [2/3]-way

  - gc [current/cr] block busy

  - gc [current/cr] grant 2-way

  - gc current grant busy

  - gc [current/cr] [block/grant] congested

  - gc cr failure / gc current retry


그럼 이러한 경우의 예를 살펴보겠습니다. 아래를 보면 gc cr multi block request가 크게 증가한 것을 확인할 수 있습니다. 또한 cr request retry도 증가했습니다.


cr request retry 인터커넥트 UDP 통신에서 패킷 손실(Packet Loss) 등으로 인해 블록을 주고받지 못해 다시 요구할 때 발생하는 대기 이벤트 입니다. 이러한 이벤트는 대기 카운트업이 되지 않고 재전송 요구도 없는데요, 보통 이러한 이벤트를 '마커 이벤트(Marker Event)'라고 부릅니다. 이러한 마커 이벤트는 대기 시간이 아니라 대기 횟수에 주목해야 합니다.


gc cr multi block request의 대기 시간이 증가하는 시점에서 cr request retry의 대기 횟수가 늘어납니다. 왜 그럴까요? 다음 조건들이 그 원인이라고 가설을 세운 뒤조사해보겠습니다.



* 원인을 찾아내는 과정


1. 수신 측(서버 프로세스)에서 아무것도 수신할 수 없는 경우

일단 수신측(서버 프로세스)에서 아무것도 수신할 수 없을 가능성을 조사합니다. LMS 프로세스 수에 따라 경우를 나누어 확인해보겠습니다. 다음은 검증 환경에서 cr request retry 이벤트와 GCS_SERVER_PROCESSES 매개변수의 상관 관계를 검증한 것입니다. 캐시 퓨전양과 트랜잭션양 등의 워크로드에 영향을 받을 수 있으므로, 어디까지나 참고 자료라고 생각하고 파악하면 됩니다.

결과를 보면 GCS_SERVER_PROCESSES 값이 늘어나면서 cr request retry가 줄어든다는 것을 알 수 있습니다. 하지만 cr request retry가 아직 많은 상황입니다. 따라서 첫 번째 가설은 아니라고 할 수 있습니다.


2. UDP 버퍼가 오버플로된 경우

이어서 UDP 버퍼의 오버 플로를 생각해보겠습니다. UDP 버퍼의 크기는 <DB_BLOCK_SIZE> X <DB_FILE_MULTIBLOCK_READ_COUNT>라는 공식으로 계산하고, UDP의 receive buffer size에 설정하는 것이 일반적입니다.


성능 테스트 전후에 netstat -s -p udp를 출력해서 socket overflows를 확인해보겠습니다. socket overflows가 발생하지 않는다면 UDP 버퍼에는 문제가 없다고 판단할 수 있습니다. 더 확실하게 판단할 수 있도록 UDP 패킷 손실 감소 빈도를 DB_FILE_MULTIBLOCK_READ_COUNT의 감소로 확인해도 좋습니다. 캐시 퓨전으로 멀티 블록을 읽어 들일때 최대 전송 크기는 DB_FILE_MULTIBLOCK_READ_COUNT이기 때문입니다. 현재 경우는 DB_FILE_MULTIBLOCK_READ_COUNT를 작게 해서 cr request retry를 줄일 수 있는데요. 하지만 cr request retry가 아직 발생하지 않은 상태이므로 두번째 가설도 아니라고 할 수 있습니다.


3. 네트워크 드라이버, 네트워크 스위치 측에서 패킷이 드롭된 경우

마지막 가설은 네트워크 드라이버, 네트워크 스위치에서 문제가 발생했다는 것입니다. 패킷이 드롭되는지는 데이터베이스 서버 측의 netstat -s -p udp 명령어의 incomplete headers와 bad checksums로 확인할 수 있습니다. 더 정확하게 상황을 확인하려면 네트워크 스위치 쪽에서 계측하는 편이 좋습니다.


인터커넥트 바틀넥이 의심되는 경우 원인을 어떻게 찾을 수 있는지 Statspack과 AWR 보고서로 가설을 세우고 증명하는 과정을 소개했습니다. 만약 이런 상황에서 네트워크 스위치를 교체할 수 없다면 GCS_SERVER_PROCESSES를 늘리거나 DB_FILE_MULTIBLOCK_READ_COUNT를 줄이는 대책을 사용해야 합니다.


여담이지만 cr request retry 이외에 gc current retry, gc cr failure도 비슷한 종류의 대기 이벤트입니다. 여기서 주의할 점은 retry 계열의 대기 이벤트는 대기 시간이 나오지 않으므로 대기 횟수로 확인해야 합니다. AWR의 Top5 이벤트에는 나오지 않으므로 AWR의 Foreground Wait Events 섹션을 확인해야 합니다. 또 DB_FILE_MULTIBLOCK_READ_COUNT를 변경하면 실행 계획에도 영향이 갑니다. 어느 정도 영향을 미치는지 확실하게 파악하고 매개변수를 변경해야 합니다.



2개의 AWR 리포트를 효율적으로 비교하는 방법


* 데이터베이스 분석에서의 AWR 활용

과거 성능 문제가 발생했던 시간대의 DB 성능 분석과 일반적인 헬스 체크를 위한 DB 분석에는 보통 AWR 리포트를 사용합니다. 그런데 '성능 문제가 발생했을때의 리포트를 보아도 어디가 문제인지 알 수 없는' 상황이 많습니다.


AWR 리포트를 효율적으로 살펴보려면 평소의 AWR 리포트와 비교해보는 것이 좋습니다. 문제가 있는 시간대의 리포트만 보아서는 해당 수치가 정상인지 비정상인지 알기 어렵기 때문입니다. 예를 들면 일주일 전의 같은 요일/시간의 리포트(따라서 평소의 리포트)를 추출해서 함께 비교하며 수치 변화가 큰 곳을 찾아야 합니다.


AWR 리포트를 비교할 때 텍스트 또는 HTML 형식의 리포트 파일을 나란히 놓고 위에서부터 차근차근 비교하는 경우도 있는데요, 생각만해도 눈이 굉장히 피곤한 작업니다. 이런 때는 'AWR 기간 비교 리포트(AWR Compare Periods Report)'를 사용하는 것이 편리합니다.



* AWR 기간 비교 리포트는 중급자 전용

앞서 팁 020에서는 AWR 기간 비교 리포트를 사용해 일반적인 경우와 문제가 발생했을 겨우의 AWR 리포트를 비교해서 문제가 있는 부분을 찾는 방법을 소개했습니다. 그런데 '비교해서 수치 차이를 살펴보아도 무엇이 문제인지 원인을 알 수 없는' 겨웅가 있습니다. AWR 기간 비교 리포트는 AWR 리포트를 어느 정도 해석할 수 있는 기술이 필요한 만큼 중급자 전용 기술이라고 말할 수 있습니다.


이때 AWR 데이터를 기반으로 자동으로 진단해주는 편리한 기능이 요구될 수 있습니다. 바로 ADDM이라는 기능으로 Diagnostics Pack 라이선스로 제공됩니다.



* 자동 성능 진단의 구조

ADDM(Automatic Database Diagnostic Monitor)은 오라클 데이터베이스 내부에 탑재된 성능 진단 엔진입니다. ADDM은 AWR에 축적된 가동 정보를 정기적으로 분석해서 문제를 발견하고 원인과 대처방안을 제시합니다. AWR 정보를 기반으로 분석하므로 AWR 스냅샷의 추출 간격을 지정해서 진단할 수도 잇습니다.


ADDM은 2개의 AWR 스냅샷에서 데이터베이스의 부하 정보를 추출해 진단합니다. 수동으로 데이터베이스를 진단할 수도 있지만, 보통 정기적으로 자동 실행되어 데이터베이스 성능에 문제가 없는지 감시합니다. ADDM은 AWR 스냅샷을 추출할 때 MMON 프로세스에 의해서 자동으로 실행됩니다. 자동 실행되면 추출한 스냅샷과 바로 이전의 스냅샷을 비교해서 진단합니다. 수동으로 실행할 때는 원하는 기간을 지정해서 진단할 수 있습니다.



이전에 실행한 SQL과 실행 계획 추출: AWR과 V$SQL


AUTOTRACE와 SQL 트레이스를 사용하면 실제로 SQL을 실행해서 실행 계획과 통계 정보를 추출할 수 있습니다. 하지만 패키지 제품에서 발행되어 SQL 전체 구문을 알 수 없을때, 또는 야간 배치 처리처럼 과거에 발행된 SQL과 관련된 정보를 추출하고 싶을때는 어떻게 해야할까요?


이러한때는 딕셔너리 뷰를 확인하면됩니다. 테이블 이름 등 SQL 구문의 일부를 사용해 어떤 SQL이 실행되었는지 찾을 수 있고, SQL_ID를 기반으로 해당 시점의 실행 계획을 추출할 수 있습니다.



* SQL이 저장된 테이블/뷰

SQL을 발행하면 공유 풀이라는 메모리 영역에 SQL 커서가 생성됩니다. 공유 풀에 만들어진 SQL 커서는 SQL 실행 직후에는 남아있을 가능성이 있습니다. 하지만 새로운 SQL 커서가 생성될 때 공유 풀을 비우므로, 과거의 SQL 커서가 사라집니다.


참고로 자동 워크로드 리퍼지토리 기능(AWR)으로 인해 공유 풀에 있는 SQL 커서는 정기적으로 AWR 리퍼지토리에 등록됩니다. 이때 기록 대상은 어느 정도 부하가 있는 것뿐으로, 한번 정도만 실행되는 단발적인 SQL은 기록하지 않습니다.


공유 풀에 남아있는 과거에 실행된 SQL 커서를 검색할 때는 V$SQL 뷰를 사용합니다.


SET LONG 2000000000 SELECT

SQL_ID, SQL_FULLTEXT

FROM V$SQL

WHERE SQL_TEXT LIKE '%<검색 문자열>%';


SELECT SQL_ID, SQL_FULLTEXT

FROM V$SQL

WHERE SQL_FULLTEXT LIKE '%EMP%';


SQL_ID                      SQL_FULLTEXT

------------------   -----------------------------------------------------------

8x102m2cc62k8         SELECT SQL_ID, SQL_FULLTEXT

FROM V$SQL

WHERE SQL_FULLTEXT LIKE '%EMP%'


fa99r6gjsrk1g         SELECT    ID, EMP_NO, NAME

FROM EMP

WHERE ID =2


SQL 커서가 공유 풀에서 제거된 상태라면 AWR 리파지토리에서 찾아야 합니다. 다만 부하가 적은 SQL은 저장되지 않스니다. AWR 리파지토리의 테이블을 참조하려면 Oracle Diagnostic Pack 옵션이 필요합니다.


SELECT  ss.BEGIN_INTERVAL_TIME,

st.SQL_ID,

txt.SQL_TEXT

FROM    DBA_HIST_SNAPSHOT  ss,

DBA_HIST_SQLSTAT   st,

DBA_HIST_SQLTEXT   txt

WHERE   ss.DBID            = st.DBID

  AND   ss.INSTANCE_NUMBER = st.INSTANCE_NUMBER

  AND   ss.SNAP_ID         = st.SNAP_ID

  AND   st.DBID            = txt.DBID

  AND   txt.SQL_TEXT LIKE '%<검색 문자열>%'



* 과거에 실행된 sql의 통계 정보


이러한 뷰와 테이블에는 과거에 실행된 SQL의 통계 정보도 저장됩니다.


 필드 이름

 설명

 ELAPSED_TIME

 해당 커서가 파싱, 실행, 해치를 위해 사용한 시간(마이크로 초)

 CPU_TIME

 해당 커서가 파싱, 실행, 패치를 위해 사용한 CPU 시간(마이크로 초)

 EXECUTIONS

 해당 객체가 라이브러리 캐시에 들어간 이후 처리된 실행 횟수

 BUFFER_GETS

 자식 커서가 버퍼를 읽어 들인 횟수

 DISK_READS

 자식 커서가 디스크를 읽어 들인 횟수

 PLAN_HASH_VALUE

 해당 커서의 SQL 플랜의 해시 표현


SELECT  SQL_ID,

ELAPSED_TIME,

CPU_TIME,

EXECUTIONS,

BUFFER_GETS,

DISK_READS,

PLAN_HASH_VALUE

FROM    V$SQL

WHERE   SQL_FULLTEXT LIKE '%%';  


이러한 뷰와 테이블에는 과거에 실행된 SQL의 통계 정보도 저장됩니다.


 필드 이름

 설명

 ELAPSED_TIME

 해당 커서가 파싱, 실행, 해치를 위해 사용한 시간(마이크로 초)

 CPU_TIME

 해당 커서가 파싱, 실행, 패치를 위해 사용한 CPU 시간(마이크로 초)

 EXECUTIONS

 해당 객체가 라이브러리 캐시에 들어간 이후 처리된 실행 횟수

 BUFFER_GETS

 자식 커서가 버퍼를 읽어 들인 횟수

  
  

 DISK_READS

 자식 커서가 디스크를 읽어 들인 횟수

  
  
  

 PLAN_HASH_VALUE

 해당 커서의 SQL 플랜의 해시 표현