SunnyKwak (sunnykwak.egloos.com) 2005년 2월 1일 DB 진단 및 튜닝 보고 SunnyKwak (sunnykwak.egloos.com) 2005년 2월 1일

Contents 컨설팅 개요 진단 및 튜닝 요약 시스템 운영환경 진단 및 분석 시스템 및 어플리케이션(SQL) 튜닝 내역 컨설팅 목적 시스템 환경 진단 및 튜닝 요약 진단 요약 튜닝 요약 시스템 운영환경 진단 및 분석 OS 운영현황 진단 및 분석 데이터베이스 및 어플리케이션 진단 / 분석 시스템 및 어플리케이션(SQL) 튜닝 내역 오라클 파라미터 튜닝 내역 어플리케이션(SQL) 튜닝 내역 컨설팅 수행 일정

컨설팅 개요 컨설팅 목적 현 시스템에 대한 정확한 진단 및 평가 진단 및 평가를 기반으로 시스템 및 DB 튜닝 Application / SQL 튜닝 등을 통하여 시스템 성능 개선 및 안정적인 시스템 운영기반을 확보 현시스템 진단/평가 운영시스템 자료 수집 시스템 분석 및 평가 DB 가용성 분석 및 평가 성능 분석 및 평가 시스템/DB 성능 개선 서버 모니터링 및 튜닝 DB 모니터링 및 튜닝 DB의 물리적 구조 변경 및 튜닝 Application/SQL 튜닝 Access Path 분석에 따른 인덱스 검증 시스템 안정화 및 사용자 만족도 개선 시스템 안정화 및 사용자 만족도 개선 성능 모니터링 및 튜닝 가이드 잠재적 장애요인 제거 및 장애처리 절차 수립

컨설팅 개요 시스템 환경 시스템 구성 정보 및 오라클 SGA 정보 고객 통합 DB는 귀사 포털 시스템을 위한 데이터베이스로 인사, 회계, 기획, 경영 등의 기간 업무를 처리하고 있으며, 200?년 오픈되어 현재까지 운영되고 있다. 주요 시스템 운영환경은 다음과 같다. 시스템 구성 정보 및 오라클 SGA 정보 구분 구성요소 통합 DB 서버 통합 포털 서버 1 통합 포털 서버 2 H/W Hostname SF6800 WAS1 EAI Platform SunFire6800 SunFire4800 Sun Enterprise 5500 O/S Solaris 2.8 Memory 12 GB 8 GB 6 GB CPU 900 MHz * 8 CPU 400 MHz * 6 CPU S/W DB / Appl Oracle 9.0.1.4 Jeus 3.3 WebToBe 3.2 Name Value Fixed 0.4 MB Shared Pool 704 MB DB Buffers 512 MB Redo Buffers 1 MB 소계 1217.4 MB

진단 및 튜닝 요약 OS 운영 현황 통합 DB 서버 및 통합 업무 서버 모두 아래 표와 같이 주요 성능 수치가 여유롭게 안정적으로 운영되고 있다. 구분 기준 수치 통합 DB 서버 통합 포털 서버 1 통합 포털 서버 2 CPU 사용률 80% 이하 60% 이하 20% 이하 Run Queue CPU 개수 이하 1-2 (8) 1-9 (8) 1-7 (6) Scan Rate 200 이하 Free Memory - 3.5-7GB 2.6-4GB 0.6-2GB

진단 및 튜닝 요약 DB 운영 현황 구분 주요현상 및 분석내역 해결 방안 Buffer cache hit ratio 평균 88%로 낮은 수치를 보이고 있다. (최소 90% 이상 권장) 오라클 데이터베이스의 메모리 파라미터 튜닝 Object 통계 정보 통계 정보가 누락된 테이블 및 인덱스가 존재하고 통계 정보의 정확도가 떨어지고 있다. 누락된 통계정보를 생성하고, 데이터가 늘어난 테이블의 경우 최신의 통계정보를 유지하도록 테이블 별로 Analyze 전략 수립 필요 Wait Event Full scan이 지속적으로 일어나 I/O 증가의 원인이 되고 있다. Application 및 SQL 튜닝 Literal (dynamic) SQL Bind 변수를 사용하지 앟는 SQL이 상당 수 존재하여 SQL parsing에 따른 overhead가 발생하고 있다. Bind 변수를 사용하도록 Application 수정 필요

진단 및 튜닝 요약 오라클 파라미터 튜닝 파라미터 현재 값 권장값 비고 db_cache_size 512M 2000M Physical I/O 감소 효과 sort_area_retained_size 65,536 정렬 작업 성능 개선 효과 Session_cached_cursors 50 커서를 재활용함으로써 불필요한 파싱이 발생하지 않도록 한다.

시스템 운영현황 진단 및 분석 서버 CPU 및 memory 사용 그래프 분석 결과 CPU Unilization graph (AM 0 ~ PM 12) Run queue size graph Memory utilization graph 분석 결과 CPU 사용률은 업무시간대 사용률이 30% ~ 60%를 유지하고 있어 여유있는 사용현황을 보이고 있다. Run queue 도 아주 낮은 수치를 보이고 있어, 양호한 상황이다. 이례적으로 18시경 CPU 사용률이 높게 나왔으나 일회성 업무로 판단되며, 다른 날에는 60%를 넘는 경우가 거의 없었다. 3.5G ~ 7G의 여유 메모리를 유지하고 있어, 양호한 수치를 나타내고 있다. Scan rate도 전체 시간대에서 0을 유지하고 있어 양호한 운영상태를 보여주고 있다. 여유 메모리의 일부를 Oracle SGA에 할당하여, I/O를 줄임으로써 성능 개선효과를 볼수 있으며, 남는 자원을 효율적으로 활용할 수 있다.

시스템 운영현황 진단 및 분석 데이터베이스 및 어플리케이션 진단 및 분석 Buffer cache hit ratio 평균 88%로 OLTP 시스템으로 낮은 수치를 보이고 있음. (최소 90% 이상 권장) OS 메모리 사용률 분석과 연계하여 SGA 분석 및 튜닝 필요. 데이터베이스 세션은 450-530개를 유지하고 있으며, 이중 Active session은 19개 이하로 양호한 수준을 나타내고 있음 테이블 및 인덱스 통계 정보 오브젝트 통계 정보는 옵티마이저가 실행 계획을 세우는데 필요한 정보 각 사용자 별로 통계 정보가 없는 테이블 존재 통계 정보가 있는 테이블과 없는 테이블이 조인될 경우 최적의 실행계획을 세울 수 없다. 주기적인 analyze를 통해 규칙적이고 일관적인 통계 정보 유지 필요하나, 운영 중인 시스템을 대상으로 analyze를 통한 통계 정보가 추가될 경우 실행 계획 변경에 의한 장애가 발생할 수 있는 부담이 있음. 향후 시스템 개편이나, 데이터베이스 Upgrade/migratioin 시에 반영하는 것이 좋다. Application Full scan이 지속적으로 일어나, I/O 증가의 원인이 되고 있음 성능 개선에 가장 효과적인 것은 SQL tuning으로 지속적인 관리 및 개선 필요 Dynamic SQL 바인드 변수를 사용하지 않는 SQL이 상당히 존재하여 SQL parsing에 따른 오버헤드가 발생하고 있다. 바인드 변수를 사용하도록 application 수정이 필요하다.

시스템 운영현황 진단 및 분석 사용자별 테이블/인덱스 통계정보 테이블 통계 정보 현황 인덱스 통계정보 현황 Owner, 테이블 수, analyzed, not analyzed, 최종 분석 일자 컬럼을 포함한 테이블 인덱스 통계정보 현황 Owner, 인덱스 수, analyzed, not analyzed, 최종 분석 일자 컬럼을 포함한 테이블

시스템 운영현황 진단 및 분석 통계 정보 및 어플리케이션 성능 비용기준 옵티마이저 1. SQL request 2. SQL 파싱 3. 실행계획 수립 5. Return SQL result 4. SQL 수행 통계 정보 참조

시스템 운영현황 진단 및 분석 앞선 그림은 SQL 수행되는 과정을 간략하게 보여주고 있다. 데이터베이스 옵티마이저는 요청된 SQL을 파싱하여, 실행 계획을 세운다. 이 때 옵티마이저는 SQL에 사용되고 있는 각 오브젝트의 통계 정보를 기반으로 실행계획을 수립한다. 이 때 누락되거나 정확하지 않은 통계정보가 사용될 경우 잘못된 실행계획이 수립되어, 응답시간이 나빠지는 원인이 된다. 누락되거나 부정확한 통계 정보는 사용자 늘어나거나, 신규 업무가 추가되는 경우 급격한 성능 저하를 초래할 수도 있다. 또한 많은 잘못된 실행 계획을 수립하므로, 경험 많은 DBA에 의한 지속적인 튜닝을 필요로 하게 된다. 따라서, 누락된 통계 정보를 생성하고, 데이터가 늘어난 테이블의 경우 최신의 통계정보를 유지하도록 테이블 별로 analyze 전략을 수립하는 것이 장기적으로 시급한 과제이다. 통계 정보 생성이나 삭제는 risk가 있는 작업으로, 테스트를 통한 application 성능 검증을 통해 수행하는 것이 바람직하다.