Transcript DB 진단 및 튜닝 보고

DB 진단 및 튜닝 보고
SunnyKwak (sunnykwak.egloos.com)
2005년 2월 1일
Company
Logo Contents
I.
컨설팅 개요


II.
컨설팅 목적
시스템 환경
진단 및 튜닝 요약


진단 요약
튜닝 요약
III. 시스템 운영환경 진단 및 분석


OS 운영현황 진단 및 분석
데이터베이스 및 어플리케이션 진단 / 분석
IV. 시스템 및 어플리케이션(SQL) 튜닝 내역


V.
오라클 파라미터 튜닝 내역
어플리케이션(SQL) 튜닝 내역
컨설팅 수행 일정
Company
Logo 컨설팅 개요
 컨설팅 목적




현 시스템에 대한 정확한 진단 및 평가
진단 및 평가를 기반으로 시스템 및 DB 튜닝
Application / SQL 튜닝 등을 통하여
시스템 성능 개선 및 안정적인 시스템 운영기반을 확보
현시스템 진단/평가
시스템/DB 성능 개선
운영시스템 자료 수집
시스템 분석 및 평가
DB 가용성 분석 및 평가
성능 분석 및 평가
서버 모니터링 및 튜닝
DB 모니터링 및 튜닝
DB의 물리적 구조 변경 및 튜닝
Application/SQL 튜닝
Access Path 분석에 따른 인덱
스 검증
시스템 안정화 및
사용자 만족도 개선
시스템 안정화 및 사용자
만족도 개선
성능 모니터링 및 튜닝 가
이드
잠재적 장애요인 제거 및
장애처리 절차 수립
Company
Logo 컨설팅 개요
 시스템 환경
 고객 통합 DB는 귀사 포털 시스템을 위한 데이터베이스로 인
사, 회계, 기획, 경영 등의 기간 업무를 처리하고 있으며, 200?
년 오픈되어 현재까지 운영되고 있다.
 주요 시스템 운영환경은 다음과 같다.
 시스템 구성 정보 및 오라클 SGA 정보
구분
구성요소
통합 DB 서버
통합 포털 서버 1
통합 포털 서버 2
H/W
Hostname
SF6800
WAS1
EAI
Name
Value
Platform
SunFire6800
SunFire4800
Sun Enterprise 5500
Fixed
0.4 MB
O/S
Solaris 2.8
Solaris 2.8
Solaris 2.8
Shared Pool
704 MB
Memory
12 GB
8 GB
6 GB
DB Buffers
512 MB
CPU
900 MHz * 8 CPU
900 MHz * 8 CPU
400 MHz * 6 CPU
Redo Buffers
1 MB
DB / Appl
Oracle 9.0.1.4
소계
1217.4 MB
S/W
Jeus 3.3 WebToBe 3.2
Company
Logo 진단 및 튜닝 요약
 OS 운영 현황
 통합 DB 서버 및 통합 업무 서버 모두 아래
표와 같이 주요 성능 수치가 여유롭게 안정적
으로 운영되고 있다.
구분
기준 수치
통합 DB 서버
통합 포털 서버 1
통합 포털 서버 2
CPU 사용률
80% 이하
60% 이하
20% 이하
60% 이하
Run Queue
CPU 개수 이하
1-2 (8)
1-9 (8)
1-7 (6)
Scan Rate
200 이하
0
0
0
-
3.5-7GB
2.6-4GB
0.6-2GB
Free Memory
Company
Logo
진단 및 튜닝 요약
 DB 운영 현황
구분
주요현상 및 분석내역
해결 방안
평균 88%로 낮은 수치를 보이고 있다.
(최소 90% 이상 권장)
오라클 데이터베이스의 메모리 파라미터 튜닝
통계 정보가 누락된 테이블 및 인덱스가
존재하고 통계 정보의 정확도가 떨어지
고 있다.
누락된 통계정보를 생성하고, 데이터가 늘어난
테이블의 경우 최신의 통계정보를 유지하도록
테이블 별로 Analyze 전략 수립 필요
Wait Event
Full scan이 지속적으로 일어나 I/O 증가
의 원인이 되고 있다.
Application 및 SQL 튜닝
Literal (dynamic)
SQL
Bind 변수를 사용하지 앟는 SQL이 상당
수 존재하여 SQL parsing에 따른
overhead가 발생하고 있다.
Bind 변수를 사용하도록 Application 수정 필요
Buffer cache hit
ratio
Object 통계 정보
Company
Logo
진단 및 튜닝 요약
 오라클 파라미터 튜닝
파라미터
db_cache_size
현재 값
권장값
512M
sort_area_retained_size
0
Session_cached_cursors
0
비고
2000M Physical I/O 감소 효과
65,536 정렬 작업 성능 개선 효과
50 커서를 재활용함으로써 불필요한 파싱이 발생하지 않
도록 한다.
Company
Logo 시스템 운영현황 진단 및 분석

서버 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를 줄임으로써 성능 개선효
과를 볼수 있으며, 남는 자원을 효율적으로 활용할 수 있다.
Company
Logo 시스템 운영현황 진단 및 분석

데이터베이스 및 어플리케이션 진단 및 분석

Buffer cache hit ratio




데이터베이스 세션은 450-530개를 유지하고 있으며, 이중 Active session은 19개 이하로
양호한 수준을 나타내고 있음
테이블 및 인덱스 통계 정보






오브젝트 통계 정보는 옵티마이저가 실행 계획을 세우는데 필요한 정보
각 사용자 별로 통계 정보가 없는 테이블 존재
통계 정보가 있는 테이블과 없는 테이블이 조인될 경우 최적의 실행계획을 세울 수 없다.
주기적인 analyze를 통해 규칙적이고 일관적인 통계 정보 유지 필요하나, 운영 중인 시스템을 대상
으로 analyze를 통한 통계 정보가 추가될 경우 실행 계획 변경에 의한 장애가 발생할 수 있는 부담
이 있음.
향후 시스템 개편이나, 데이터베이스 Upgrade/migratioin 시에 반영하는 것이 좋다.
Application



평균 88%로 OLTP 시스템으로 낮은 수치를 보이고 있음. (최소 90% 이상 권장)
OS 메모리 사용률 분석과 연계하여 SGA 분석 및 튜닝 필요.
Full scan이 지속적으로 일어나, I/O 증가의 원인이 되고 있음
성능 개선에 가장 효과적인 것은 SQL tuning으로 지속적인 관리 및 개선 필요
Dynamic SQL


바인드 변수를 사용하지 않는 SQL이 상당히 존재하여 SQL parsing에 따른 오버헤드가 발생하고
있다.
바인드 변수를 사용하도록 application 수정이 필요하다.
Company
Logo 시스템 운영현황 진단 및 분석
 사용자별 테이블/인덱스 통계정보
 테이블 통계 정보 현황
 Owner, 테이블 수, analyzed, not analyzed, 최종
분석 일자 컬럼을 포함한 테이블
 인덱스 통계정보 현황
 Owner, 인덱스 수, analyzed, not analyzed, 최종
분석 일자 컬럼을 포함한 테이블
Company
Logo 시스템 운영현황 진단 및 분석
 통계 정보 및 어플리케이션 성능
1. SQL request
비용기준 옵티마이저
2. SQL 파싱
3. 실행계획 수립
5. Return SQL result
4. SQL 수행
통계 정보
참조
Company
Logo 시스템 운영현황 진단 및 분석




앞선 그림은 SQL 수행되는 과정을 간략하게 보여주고 있다.
데이터베이스 옵티마이저는 요청된 SQL을 파싱하여, 실행 계획을 세운다. 이 때 옵
티마이저는 SQL에 사용되고 있는 각 오브젝트의 통계 정보를 기반으로 실행계획
을 수립한다.
이 때 누락되거나 정확하지 않은 통계정보가 사용될 경우 잘못된 실행계획이 수립
되어, 응답시간이 나빠지는 원인이 된다.
누락되거나 부정확한 통계 정보는





사용자 늘어나거나,
신규 업무가 추가되는 경우 급격한 성능 저하를 초래할 수도 있다.
또한 많은 잘못된 실행 계획을 수립하므로, 경험 많은 DBA에 의한 지속적인 튜닝을 필요
로 하게 된다.
따라서, 누락된 통계 정보를 생성하고, 데이터가 늘어난 테이블의 경우 최신의 통계
정보를 유지하도록 테이블 별로 analyze 전략을 수립하는 것이 장기적으로 시급한
과제이다.
통계 정보 생성이나 삭제는 risk가 있는 작업으로, 테스트를 통한 application 성능
검증을 통해 수행하는 것이 바람직하다.