데이터베이스 최적화: 전문가 성능 튜닝 기법

데이터베이스에서 성능 튜닝을 수행하는 것은 모든 데이터베이스 관리자 (DBA)가 지속적이고 정기적으로 수행해야 하는 가장 일반적이면서도 가장 중요한 활동 중 하나입니다. 전문가들은 데이터베이스를 정기적으로 미세 조정하여 성능을 최적화할 것을 권장합니다. 미세 조정은 사용자가 보고서와 쿼리를 더 빠르게 실행하고 결과를 더 빨리 얻을 수 있도록 도와줍니다. 이 게시물은 Oracle® 데이터베이스의 문제 해결 및 튜닝을 위한 몇 가지 기법을 공유합니다.

튜닝 단계

시작하려면 문제가 발생하는 영역을 식별해야 합니다. 문제를 유발할 수 있는 가능한 영역에는 운영 체제, 데이터베이스, 메모리 부족 등이 포함됩니다. 문제 영역을 식별한 후에는 최대 이점을 위해 해당 영역을 튜닝할 수 있습니다. 이 블로그는 성능 튜닝에만 초점을 맞춥니다.

Oracle은 DBA에게 문제 진단과 성능 문제 해결을 돕기 위한 몇 가지 도구를 제공했습니다. 이러한 도구에는 자동 데이터베이스 진단 모니터 (ADDM) 및 자동 워크로드 리포지토리 (AWR) 보고서가 포함되며, 문제 기간 동안 생성하여 분석에 사용할 수 있습니다. 기울어진 또는 조정 가능한 구성 요소를 찾기 위해 도구를 사용해야 합니다.

AWR 보고서의 상위 시간 이벤트

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

@$ORACLE_HOME/rdbms/admin/awrrpt.sql

이는 사전 저장된 템플릿을 사용하여 HTML 형식으로 AWR 보고서를 생성합니다. AWR 보고서를 생성한 후, 다음 이미지와 같이 AWR 보고서의 Top Timed Events 섹션에서 문제 영역을 빠르게 확인할 수 있습니다:

ADDM 튜닝 세션

일반적으로 문제가 보고되면 DBA는 해당 영역만 문제 해결하거나 해당 특정 문제를 해결할 수 있습니다. 그러나 문제가 올바르게 식별되고 해결되지 않으면 미래에 더 큰 문제로 이어질 수 있습니다. DBA는 여기서 큰 그림을 놓칠 수 있습니다. DBA가 데이터베이스 문제에 대해 더 크고 완전한 그림을 얻을 수 있도록 돕기 위해 Oracle은 DBA를 위한 또 다른 도구인 ADDM을 제공했습니다. ADDM 튜닝 세션은 수동 튜닝 세션과 유사한 절차를 따릅니다. 다음 이미지는 비교를 보여줍니다:

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

SQL 또는 Oracle Enterprise Manager (OEM)를 통해 ADDM 보고서를 검색하세요. 다음 이미지는 샘플 SQL을 보여줍니다:

나쁜 SQL과 실행 계획

AWR 또는 ADDM 보고서가 나쁜 SQL/SQLID 섹션을 식별한 후, DBMS_XPLAN을 사용하여 그에 대한 더 많은 정보를 수집할 수 있습니다. DBMS_XPLAN 패키지는 실행 (exec) 계획을 검색하고 표시하는 데 사용할 수 있는 다음 테이블 함수를 제공합니다:

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

다음 이미지는 EXPLAIN PLAN 명령어의 샘플 출력을 보여주며 쿼리가 풀 테이블 스캔을 사용하는지 또는 일부 인덱스를 사용하여 데이터를 좁히는지 식별합니다:

ADDM 보고서는 몇 가지 새로운 인덱스를 생성하여 성상 이점을 얻을 수 있는지 반영합니다. OEM에서 SQL 튜닝 어드바이저를 실행하여 쿼리를 미세 조정하고 더 나은 실행 계획을 사용할 수도 있습니다. 대부분의 경우, 문제가 있는 SQL에 더 나은 계획을 사용하면 주요 성능 문제가 해결됩니다.

SQL 튜닝 어드바이저

SQL 튜닝 어드바이저는 SQL 권한 부여 ID (SQLID)를 사용하여 분석하고 솔루션을 제안함으로써 SQL 문을 분석하고 성능 권장 사항을 얻는 데 도움이 됩니다. SQL 튜닝 어드바이저는 다음 소스를 분석합니다:

상위 활동: 현재 활성화된 상위 SQL 문을 분석합니다.
SQL 튜닝 세트: 제공한 SQL 문 세트를 분석합니다.
이력 SQL (AWR): AWR 스냅샷에서 수집된 SQL 문을 분석합니다.

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

장시간 실행 요청

Oracle eBusiness Suite 데이터베이스에서, 장시간 실행 요청 문제는 몇 개의 동시 요청이 계속 실행되는 곳에서 주로 발생합니다. 이를 해결하려면 동시 요청과 관련된 데이터베이스 세션에 대한 더 많은 정보를 수집해야 합니다. 다음 이미지는 이 정보를 수집하는 단계를 보여줍니다

정보:

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

AWR 보고서를 검토한 후, DBA는 캐시 히트 비율이 다른 캐시보다 낮은 캐시를 쉽게 식별할 수 있으므로 미세 조정이 필요한 캐시를 식별할 수 있습니다. 다음 이미지는 인스턴스 전체 메모리 튜닝을 위해 고려해야 할 몇 가지 상위 데이터베이스 파라미터를 보여줍니다.

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

일반적으로 관찰되는 대기 이벤트

다음 표는 몇 가지 일반적인 대기 이벤트와 그 가능한 원인을 보여줍니다:

데이터베이스 최적화: 전문가 성능 튜닝 기법 (

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

상위 10 튜닝 문제

고객이 일반적으로 직면하는 상위 10 튜닝 문제는 다음과 같습니다:

나쁜 연결 관리: 개발자는 애플리케이션에서 데이터베이스에 연결하는 코드를 작성하거나 데이터베이스에서 데이터를 가져오기 위해 쿼리를 실행합니다. 데이터가 가져와지고 더 이상 아무것도 필요하지 않으면 코드는 데이터베이스에 대한 연결을 닫아야 합니다. 그러나 이것은 종종 일어나지 않아 데이터베이스에서 비활성 세션 수가 증가합니다. 이 세션들은 다른 활성 연결에 사용될 수 있는 소중한 리소스를 사용합니다.
커서와 공유 풀의 나쁜 사용: 커서는 개발자의 무기고에 있는 도구로 대부분 활용되지 않습니다. 커서 없이 Oracle은 실행할 때마다 코드를 하드 파싱해야 합니다. 이는 반복적으로 실행되는 SQL 쿼리의 성능에 큰 영향을 미칩니다. DBA는 Instance Efficiency Percentages - Execute to Parse %. AWR 보고서 섹션을 देखकर 이 문제를 식별할 수 있습니다.
나쁜 SQL: SQL 쿼리가 작성되는 방식(데이터를 가져오기 위한 조인 조건)은 실행 후 해당 SQL의 성능에 큰 영향을 미칩니다. 큰 테이블에 대해 풀 테이블 스캔을 피해야 합니다. SQL이 준비된 후, 개발자와 DBA는 해당 SQL에 대해 실행 계획을 실행하여 데이터베이스에서 실행 비용을 이해해야 합니다. 커서, 바인드 변수, 인덱스를 사용하면 효율성을 높일 수 있습니다.
비표준 초기화 파라미터 사용: DBA는 항상 표준 또는 권장 초기화 파라미터만 사용해야 합니다. Oracle 서비스 요청 (SR)에서 제안할 때만 비표준 초기화 파라미터를 사용하세요.
데이터베이스 I/O 잘못 설정: DBA가 데이터베이스 하드웨어를 선택할 때 데이터베이스를 여러 디스크에 분산시키고 네트워크 팀을 데이터베이스 서버에서 최종 사용자에게 데이터가 이동하는 속도와 그 반대로 논의에 참여시켜야 합니다. DBA는 네트워크 스위치와 라우터의 속도를 고려하여 병목 현상이나 성능 문제를 방지해야 합니다.
리두 로그 설정 문제: 리두 로그는 리두 버퍼에서 데이터를 저장하여 어떤 충돌이 발생하더라도 Oracle이 트랜잭션을 다시 실행할 수 있도록 합니다. 리두 로그 크기가 부적절하면 데이터베이스에서 여러 스위치가 발생하여 성능 문제를 일으킬 수 있습니다. 이는 아카이브 생성에도 부담을 증가시킵니다.
버퍼 캐시에서 데이터 블록 직렬화: 이는 자유 리스트 그룹 또는 언도 세그먼트 부족으로 인해 발생합니다. 이 상황은 큰 활성 사용자 기반을 가진 삽입이 많은 데이터베이스지만 더 적은 언도 세그먼트로 인해 발생하며, 이는 궁극적으로 성능 문제로 이어집니다.
풀 테이블 스캔: 실행 계획을 실행하여 쿼리에서 풀 테이블 스캔을 확인하세요. 일반적으로 풀 테이블 스캔을 하는 쿼리는 나쁜 SQL 설계를 반영하며, 인덱스를 사용하고 필요한 데이터를 좁혀서 수정할 수 있습니다. 몇 가지 경우, 풀 테이블 스캔은 특히 작은 테이블의 경우 유익할 수 있습니다.
재귀 SQL: 재귀 SQL은 올바르게 사용되면 개발자에게 큰 도움이 될 수 있지만, 양날의 검이 될 수도 있습니다. 올바르게 수행하면 효율적으로 출력을 제공합니다. 그렇지 않으면 데이터베이스 성능에 큰 영향을 미칩니다.
디스크 내 정렬: 디스크 내 정렬은 데이터베이스에 매우 비용이 많이 드는 작업입니다. 나쁜 SQL 설계와 나쁜 최적화를 나타냅니다. Instance Activity Stats – Sorts (disk) AWR 보고서 섹션에서 문제를 식별할 수 있습니다.

결론

DBA가 성능 튜닝을 위해 다른 영역을 고려해야 하지만, 데이터베이스 세계에서 성능 튜닝은 데이터베이스와 애플리케이션 설계 단계에서 시작됩니다. 성능 튜닝 관점에서 설계된 데이터베이스와 애플리케이션은 성능 튜닝 고려 없이 설계된 애플리케이션보다 훨씬 더 확장 가능합니다.

이 블로그에서 다룬 성능 튜닝 포인트는 빙산의 일각에 불과합니다. 동료 DBA들이 이 주제에 대해 더 많이 읽어서 전반적인 데이터베이스 성능을 관리할 것을 권장합니다.

피드백 탭을 사용하여 의견을 남기거나 질문하세요.

데이터베이스 서비스에 대해 자세히 알아보세요.

Rackspace는 Oracle 제품에 대한 광범위한 지식을 가지고 있습니다. 우리가 어떻게 귀하의 Oracle 투자를 극대화할 수 있는지 자세히 알아보세요.