오라클 데이터베이스 성능 튜닝 완벽 가이드: AWR·ADDM부터 주요 문제 해결까지

데이터베이스 성능 튜닝은 모든 데이터베이스 관리자(DBA)가 지속적으로 수행해야 하는 가장 일반적이면서도 가장 중요한 업무 중 하나입니다. 전문가들은 최적의 성능을 유지하기 위해 데이터베이스를 정기적으로 세밀하게 조정할 것을 권장합니다. 성능 튜닝을 통해 사용자는 리포트와 쿼리를 더 빠르게 실행하고 결과를 신속하게 얻을 수 있습니다. 이 글에서는 Oracle® 데이터베이스의 문제를 진단하고 튜닝하는 다양한 기법을 소개합니다.

튜닝 절차

튜닝의 첫 단계는 문제가 발생하는 영역을 파악하는 것입니다. 운영체제, 데이터베이스 자체, 메모리 부족 등 다양한 요인이 원인이 될 수 있습니다. 문제 영역을 식별한 후에는 해당 영역에 집중적으로 튜닝을 진행하여 최대의 효과를 얻을 수 있습니다. 이 글에서는 특히 성능 튜닝에 초점을 맞춥니다.

Oracle은 DBA가 문제를 진단하고 성능 이슈를 해결하는 데 도움이 되는 여러 도구를 제공합니다. 대표적인 도구로는 자동 데이터베이스 진단 모니터(ADDM)와 자동 워크로드 저장소(AWR) 리포트가 있으며, 문제가 발생한 시간 구간에 대해 리포트를 생성하여 분석에 활용할 수 있습니다. 이 도구들을 통해 편향되었거나 튜닝 가능한 구성 요소를 찾아내야 합니다.

AWR 리포트의 상위 시간 소요 이벤트(Top Timed Events)

AWR은 특정 인스턴스의 메모리 사용량을 통계 형태로 요약한 리포트를 생성할 수 있는 도구입니다. AWR 리포트를 생성하려면 sqlplus 프롬프트에서 다음 파일을 실행합니다.

@$ORACLE_HOME/rdbms/admin/awrrpt.sql

이 명령은 사전 저장된 템플릿을 사용하여 HTML 형식의 AWR 리포트를 생성합니다. 리포트 생성 후 Top Timed Events 섹션을 먼저 확인하면 아래 이미지와 같이 문제가 되는 영역을 빠르게 파악할 수 있습니다.

ADDM 튜닝 세션

일반적으로 이슈가 보고되면 DBA는 해당 영역만 문제 해결하거나 특정 문제만 처리하는 경우가 많습니다. 그러나 문제가 정확히 식별되지 않고 제대로 해결되지 않으면 향후 더 큰 문제로 이어질 수 있습니다. 즉, DBA가 큰 그림을 놓치기 쉽습니다. 이러한 한계를 보완하기 위해 Oracle은 DBA에게 데이터베이스 이슈에 대한 포괄적이고 완전한 시각을 제공하는 또 다른 도구, ADDM을 제공합니다. ADDM 튜닝 세션은 수동 튜닝 세션과 유사한 절차를 따릅니다. 다음 이미지에서 두 방식을 비교해 볼 수 있습니다.

이미지 출처: Oracle 11G Performance Tuning 교육 매뉴얼

ADDM 리포트는 SQL을 사용하거나 Oracle Enterprise Manager(OEM)를 통해 조회할 수 있습니다. 다음은 샘플 SQL입니다.

비효율적인 SQL과 실행 계획

AWR 또는 ADDM 리포트가 비효율적인 SQL/SQLID 섹션을 식별하면, DBMS_XPLAN을 사용하여 해당 정보를 더 자세히 조사할 수 있습니다. DBMS_XPLAN 패키지는 실행 계획(exec plan)을 조회하고 표시하는 데 사용할 수 있는 다음 테이블 함수를 제공합니다.

DISPLAY
DISPLAY_AWR
 select plan_table_output from table (DBMS_XPLAN.DISPLAY_AWR('fs22b3fgfh8xc'));
DISPLAY_CURSOR

다음 이미지는 EXPLAIN PLAN 명령의 샘플 출력으로, 쿼리가 전체 테이블 스캔을 수행하는지 아니면 인덱스를 활용해 데이터 범위를 좁히는지 확인할 수 있습니다.

ADDM 리포트는 새로운 인덱스 생성으로 성능상 이득을 얻을 수 있는지 여부도 알려줍니다. 또한 OEM에서 SQL 튜닝 어드바이저(SQL Tuning Advisor)를 실행하여 쿼리를 세밀하게 조정하고 더 나은 실행 계획을 적용할 수 있습니다. 대부분의 경우 문제가 되는 SQL에 더 나은 실행 계획을 적용하는 것만으로도 주요 성능 문제가 해결됩니다.

SQL 튜닝 어드바이저(SQL Tuning Advisor)

SQL 튜닝 어드바이저는 SQLID(SQL authorization ID)를 사용해 SQL 문을 분석하고 성능 개선 권고사항을 제공합니다. SQL 튜닝 어드바이저는 다음 소스를 분석합니다.

• Top activity: 현재 활성 상태인 상위 SQL 문을 분석합니다.
• SQL tuning sets: 사용자가 제공한 SQL 문 집합을 분석합니다.
• Historical SQL(AWR): AWR 스냅샷으로 수집된 SQL 문을 분석합니다.

다음 스크린샷은 몇 가지 예시를 보여줍니다.

장시간 실행 요청(Long Running Requests)

Oracle eBusiness Suite 데이터베이스에서는 일부 동시성(concurrent) 요청이 계속 실행되는 상황에서 장시간 실행 요청 문제가 주로 발생합니다. 이를 해결하려면 해당 동시성 요청과 연관된 데이터베이스 세션에 대한 추가 정보를 수집해야 합니다. 다음 이미지는 이 정보를 수집하는 단계를 보여줍니다.

Oracle 데이터베이스 메모리 파라미터

DBA는 AWR 리포트를 검토한 후 캐시 히트율(cache hit ratio)이 다른 캐시보다 낮은 캐시를 손쉽게 식별하여 세밀한 튜닝 대상으로 삼을 수 있습니다. 다음 이미지는 인스턴스 전체 메모리 튜닝 시 반드시 고려해야 할 주요 데이터베이스 파라미터 몇 가지를 보여줍니다.

이미지 출처: https://ora-performance-tuning.blogspot.com/2014/02/automatic-shared-memory-management.html

자주 관찰되는 대기 이벤트(Wait Events)

다음 표는 흔히 관찰되는 대기 이벤트와 그 가능한 원인을 정리한 것입니다.

표 출처: Oracle Performance Tuning 11G OCP 교육 매뉴얼, 20장, 24페이지

상위 10대 튜닝 문제점

고객들이 공통적으로 겪는 상위 10대 튜닝 문제점은 다음과 같습니다.

• 잘못된 연결 관리(Bad connection management): 개발자는 애플리케이션에서 데이터베이스에 연결하는 코드를 작성하거나 데이터를 가져오는 쿼리를 실행합니다. 데이터를 모두 가져오고 더 이상 필요하지 않다면 데이터베이스 연결을 종료해야 하지만, 실제로는 그렇지 못한 경우가 많습니다. 이로 인해 비활성(inactive) 세션이 계속 누적되며, 본래 활성 연결에 사용될 수 있는 귀중한 리소스가 낭비됩니다.

• 커서와 공유 풀의 잘못된 사용: 커서(cursor)는 개발자가 보유한 강력한 도구임에도 불구하고 대부분 제대로 활용되지 않습니다. 커서 없이 코드를 실행할 때마다 Oracle은 하드 파싱(hard parse)을 수행해야 하며, 이는 반복 실행되는 SQL 쿼리의 성능에 큰 악영향을 미칩니다. DBA는 AWR 리포트의 Instance Efficiency Percentages - Execute to Parse % 섹션을 통해 이 문제를 확인할 수 있습니다.

• 비효율적인 SQL(Bad SQL): SQL 쿼리가 작성된 방식(데이터를 가져오는 조인 조건 등)은 실행 후 성능에 지대한 영향을 미칩니다. 대용량 테이블에서는 전체 테이블 스캔을 피해야 합니다. SQL 작성 후 개발자와 DBA는 explain plan을 실행하여 데이터베이스에서 해당 SQL을 실행하는 비용을 파악해야 합니다. 커서, 바인드 변수(bind variable), 인덱스를 활용하면 효율성을 높일 수 있습니다.

• 비표준 초기화 파라미터 사용: DBA는 항상 표준 또는 권장 초기화 파라미터만 사용해야 합니다. 비표준 초기화 파라미터는 Oracle 서비스 요청(SR)에서 제안된 경우에만 사용해야 합니다.

• 데이터베이스 I/O 설계 실패: DBA가 데이터베이스 하드웨어를 선택할 때는 데이터베이스를 여러 디스크에 분산 배치하고, 네트워크 팀과 협의하여 데이터베이스 서버에서 최종 사용자까지 데이터가 오가는 속도를 검토해야 합니다. 네트워크 스위치와 라우터의 속도도 고려하여 병목 현상이나 성능 문제를 예방해야 합니다.

• 리두 로그(Redo Log) 설정 문제: 리두 로그는 리두 버퍼의 데이터를 저장하여 장애 발생 시 Oracle이 트랜잭션을 재수행할 수 있도록 하는 데 필요합니다. 리두 로그 크기가 부족하면 로그 스위칭이 잦아져 성능 문제가 발생하고, 아카이브 생성 부하도 증가합니다.

• 버퍼 캐시 내 데이터 블록 직렬화: 이 문제는 free list group 또는 undo 세그먼트 부족으로 인해 발생합니다. 활성 사용자 수는 많은데 undo 세그먼트가 적은 insert 위주의 데이터베이스에서 나타나며, 결국 성능 저하로 이어집니다.

• 전체 테이블 스캔(Full table scans): explain plan을 실행하여 쿼리에 전체 테이블 스캔이 있는지 확인해야 합니다. 전체 테이블 스캔이 나타난다면 대개 잘못된 SQL 설계를 의미하며, 인덱스 사용과 필요한 데이터 범위 축소를 통해 개선할 수 있습니다. 다만 소규모 테이블의 경우처럼 드물게는 전체 테이블 스캔이 오히려 유리한 경우도 있습니다.

• 재귀 SQL(Recursive SQL): 재귀 SQL은 올바르게 사용하면 개발자에게 큰 도움이 되지만 양날의 검이 될 수 있습니다. 올바르게 작성하면 효율적으로 결과를 제공하지만, 그렇지 않으면 데이터베이스 성능에 막대한 악영향을 미칩니다.

• 디스크 정렬(In-disk sorting): 디스크 정렬은 데이터베이스에 매우 비용이 많이 드는 작업으로, 열악한 SQL 설계와 최적화 부재를 의미합니다. 이 문제는 AWR 리포트의 Instance Activity Stats – Sorts (disk) 섹션에서 확인할 수 있습니다.

결론

DBA는 성능 튜닝을 위해 다양한 영역을 고려해야 하지만, 데이터베이스 환경에서 성능 튜닝은 데이터베이스와 애플리케이션 설계 단계부터 시작되어야 합니다. 성능 튜닝 관점을 염두에 두고 설계된 데이터베이스와 애플리케이션은 그렇지 않은 경우보다 훨씬 뛰어난 확장성을 갖추게 됩니다.

이 글에서 다룬 성능 튜닝 포인트는 빙산의 일각에 불과합니다. 데이터베이스 전반의 성능을 관리하기 위해 동료 DBA 여러분께서도 관련 주제를 꾸준히 학습하시기를 권장합니다.

의견이나 질문이 있다면 피드백 탭을 이용해 주세요.

Rackspace는 Oracle 제품에 대한 폭넓은 전문 지식을 보유하고 있으며, Oracle 투자 가치를 극대화하는 방법을 안내해 드립니다.