DB성능개선방안

I.    데이터 처리Business Agility 수행을 위한 데이터베이스 튜닝(DB Tuning)의 개요

가.데이터베이스 튜닝의 정의

- 환경(파라미터), DB어플리케이션(sql), 구조(Data모델)의 조정을 통하여 Database의 성능을 향상시키는 성능최적화 기술

- 데이터베이스 응용, 데이터베이스 자체, 그리고 운영체제의 조정 등을 통하여 최적의 자원으로 최적의 성능(응답속도)를 얻을 수 있도록 개선하는 작업

- End user인 고객관점에서 빠른 업무 처리를 지원하기 위해 데이터베이스가 처리 성능을 보장하는 모든 개념

나.정보시스템 성능과 관련된 주요 요소

1)    H/W관련

-    CPU, Memory, N/W, Disk …… System configuration resources 부족

2)    S/W관련

-    DBMS: DB Design, Optimizing 전략, SQL 효율

-    Application Architecture: 2-tier, 3-tier, EJB, OLTP, OLAP(adhoc), Batch, c, COBOL, PL/SQL, JSQL

3)    업무프로세스 관련

-    업무 처리방식의 문제, Load Balancing 정책

다.데이터베이스 성능저하의 주요요인

- 대부분의 성능저하 주요 원인은 DB관련 비효율

- 성능저하의 핵심은 CPU/Memory 부하가 아니 비효율적인 I/O

- DB처리 효율화가 핵심

가.    DB의 좋은 성능을 위한 조건

-개선순서: 설계관점 -> DBMS관점 -> SQL관점

주요요소	설 명	사 례
설계관점 (모델링관점)	- 데이터 모델링, 인덱스 설계 - 데이터파일, 테이블 스페이스 설계 - 데이터베이스 용량 산정	- 반정규화 - 분산파일 배치
DBMS관점	- CPU, 메모리 I/O에 관한 관점	- Buffer, Cache크기
SQL관점	- join, Indexing, SQL Execution Plan	- Hash / Join

I.    Database 튜닝의 절차 및 효과

가.   Database 튜닝의 절차

절 차	설 명	수 행
시스템 성능 진단 튜닝/분석 단계 결과 단계	- 시스템의 현 상태와 문제점을 도출하기 위한 과정 - 사용자 인터뷰, 설계검토, 시스템 구성 검토, 자원사용현황 검토, SQL Trace 분석 등 수행	- 사용자 인터뷰 - 설계, 시스템 구성도검토 - 자원사용현황분석 - SQL Trace분석
	- 진단결과 도출된 문제점에 대한 원인을 분석하고 해결방안 제시.	- 설계내용 튜닝 - SQL튜닝 - DBMS튜닝 - OS, H/W 튜닝
	- 1차적으로 튜닝방안 적용 후 2차 테스트 분석이나 결과 평가를 통한 최종안 적용 및 개선효과 보고자료 작성	- 튜닝 후 자료수집/분석 - 튜닝결과 평가 - 산출물 작성

나.   데이터베이스 튜닝의 효과

- 최종 사용자들에 대한 고객 만족을 실현

- DBMS 시스템을 기반으로 한 Application의 품질 향상에 기여함

- 실무 경험을 통한 튜닝 방법과 관련한 지식 Database 구축으로

  지식 공유 가능

II.   Database 튜닝의 3요소 및 튜닝기법

가.    Database 튜닝의 3요소 

구 분	설 명	기법 예
DB설계	- 데이터베이스 설계 단계에서 성능을 고려하여 설계	Table 분할/통합, 식별자 지정, 인덱스 등
DB환경	- 성능을 고려하여 메모리나 블록 크기 등을 지정	CPU, 메모리 등
SQL문 튜닝	- 성능을 고려하여 SQL 문장을 작성	힌트, 부분범위 등

나.    DB 설계 측면의 튜닝

구 분	내 용
테이블의 분할/통합	- 논리적으로는 통합된 단일 테이블이지만 DBMS가 지원하는 파티션기능 적용함으로써 액세스 효율로DB I/O 를 최적화 할 수 있음 - DBMS에 따라 파티션기능이 제약되는 경우에는 테이블의 수평분할 고려 - 액세스 패턴에 따라 단일 테이블을 1:1 로 수직분할을 고려할 수 있음
식별자 지정	- 본질 식별자와 인조식별자의 선택에 따라 정보의 상속과 단절에 영향을 줄 수 있음
효율적인 인덱스 전략	- 최소한의 인덱스로 최대의 효과를 얻을 수 있는 최적의 인덱스 구조 수립 - 인덱스 분포도 15% 이하 유용 유도(15% 이상이면 Full Scan 유리)
적절한 데이터타입 선택	- 조인 시 연결되는 컬럼의 데이터 타입이 다른 경우 내부적인 변형에 의해 인덱스가 있음에도 불구하고 활용을 못해 조인순서나 조인방식을 달리 선택하는 비효율 발생 할 수 있음

다.    DBMS 환경에 따른 튜닝

- DBMS의Configuration Parameter 값들을 조정함으로써 최적의 DBMS 자원 활용이 이루어지도록 함

구 분	내 용
CPU튜닝	- Peak Time 시에 60~70%의 사용량 유지 권고 - CPU증설 또는 CPU 과다 점유하는 프로세스를 찾아서 해결
메모리튜닝	- DBMS의 사용 메모리의 최적화 필요 - 업무를 가능하게 하기 위한 충분한 메모리 확보 - DBMS 메모리 + Session 메모리
I/O튜닝	- I/O를 분산시킬 수 있도록 데이터 화일의 재배치 - Raid를 이용-> I/O가 많이 일어나는 것은 Raid0,1에 배치
네트워크튜닝	Ping, Ftp를 이용하여 응답시간 분석

라.    SQL 튜닝

구 분	내 용	예 시
옵티마이저에 대한 이해	- RBO (Rule Based Optimizer): 통계정보가 없는 상태서 미리 정해진 Rule에 따라 실행계획 수립 - CBO (Cost Based Optimizer): 통계정보로부터 모든 Access Path 고려하여 실행계획 수립 - 옵티마이저가 선택한 실행계획을 확인하고 최적화된 실행계획 수립이 이루어지도록 Factor 부여	SELECT /*+ rule */: RBO로 강제설정 SELECT /*+ all_rows*/:전체적인 처리능력 최적화(CBO)
힌트사용	- 옵티마이저가 항상 최적화된 실행계획을 수립하는 것은 아니므로 힌트를 사용하여 원하는 실행계획으로 유도	SELECT /*+ hint [ { hint } ... ] */
부분범위 처리	- 조건을 만족하는 전체집합이 아닌 일부분만 액세스하고도 결과를 리턴 할 수 있도록 하여 온라인 프로그램에서 응답시간(Response Time)을 최소화 할 수 있음	- 인덱스를 이용해서 sort 대체 - 인덱스만으로 액세스해도 되는 구조를 만듦 - max값을 구할 경우 인덱스 이용 - exists 활용 - rownum 활용
인덱스 활용	- 인덱스가 있음에도 불구하고 SQL을 잘못 기술함으로써 무용지물로 만드는 오류를 없애야 함	인덱스 활용을 위해 아래 경우 배제 - 인덱스 칼럼의 변형 - not operator - null, not null 사용
조인방식/ 조인순서	- 동일한 SQL문이라도 조인방식과 조인순서에 따라 처리속도는 매우 큰 차이를 가져올 수 있으므로 작성한 SQL이 어떤 실행계획으로 수립되는 지 반드시 확인 후 조정
다중처리 (Array Processing)	- 배치작업의 경우 한번의 DBMS호출로 여러 건을 동시에 처리할 수 있는 다중처리 활용
병렬쿼리 (Parallel Query)	- 배치작업의 경우 하나의 SQL을 여러 개의 CPU가 병렬로 분할 처리하게 함으로써 처리속도 향상 가져옴	Insert /*+ parallel(member_tmp,8)*/ into member_tmp Select /*+ parallel(member,8)*/ from member;
Dynamic SQL 지양	- 조건절에 입력된 값을 먼저 Binding 한 후 실행계획을 수립하는 Dynamic SQL은 파싱 부하가 커지므로 입력 값을 Binding 하기 전에 실행계획을 수립하는 Static SQL을 가급적 사용하도록 함	Select * From Tab Where Col1 = ‘1’; Select * From Tab Where Col1 = ‘2’; Select * From Tab Where Col1 = ‘3’; è   Select * From Tab Where Col1 = :v1;

III.  데이터베이스 성능 저하 예방방안

가.   상시 모니터링 및 관리

- 데이터베이스에 치명적인 문제가 발생 하기 전에 주기적으로 모니터링 되고 관리 되어야 함.

- 향후 튜닝에 필요한 정상적인 상황일 때의 데이터베이스 로그 정보 보관 필요

나.   개발자에 대한 데이터베이스 원리 교육

- 개발자에 대해서SQL 튜닝 기법 및 SQL 수행 원리 교육 필요

- 프로그램 알고리즘 교육 필요, Source Review 제도 도입

다.   1회성 작업이 아닌 지속적인 튜닝 실시

- 데이터베이스 튜닝은 1회성 작업이 아니라 지속적으로 이루어져야 함

라.   튜닝 결과에 대한Historical Information 구축

- 튜닝 수행 시 발생된 문제, 문제분석 결과, 해결 내용에 대해서 기업의 KMS에 등록이 필요, 즉 관리 되어야 함.