데이터베이스 성능튜닝

주요요소

설 명

사 례

설계관점

(모델링관점)

- 데이터 모델링, 인덱스 설계

- 데이터파일, 테이블 스페이스 설계

- 데이터베이스 용량 산정

- 반정규화

- 분산파일 배치

DBMS관점

- CPU, 메모리 I/O에 관한 관점

- Buffer, Cache크기

SQL관점

- join, Indexing, SQL Execution Plan

- Hash / Join

절 차

설 명

수 행

- 시스템의 현 상태와 문제점을 도출하기 위한 과정

- 사용자 인터뷰, 설계검토, 시스템 구성 검토, 자원사용현황 검토, SQL Trace 분석 등 수행

- 사용자 인터뷰

- 설계 검토

- 시스템 구성도검토

- 자원사용현황분석

- SQL Trace분석

- 진단결과 도출된 문제점에 대한 원인을 분석하고 해결방안 제시.

- 설계내용 튜닝

- SQL튜닝

- DBMS튜닝

- OS, H/W 튜닝

- 1차적으로 튜닝방안 적용 후 2차 테스트 분석이나 결과 평가를 통한 최종안 적용 및 개선효과 보고자료 작성

- 튜닝 후 자료수집 /분석

- 튜닝결과 평가

- 산출물 작성

목표

내용

처리능력

(Throughput)

-해당작업을 수행하기 위해 소요되는 시간으로 수행되는 작업량을 나눔

-처리능력 = 트랜잭션 수 / 시간

-전체적인 시스템 시각에서 측정되고 평가함

처리시간

(Throughput Time)

-작업이 완료되는데 소요되는 시간

-배치프로그램의 성능목표를 설정함

※ 배치 작업 시간 단축

·병행처리(Parallel Processing)를 실시

·인덱스 스캔보다 FULL 테이블 스캔으로 처리

·Nest-Loop 조인보다 Hash 조인으로 처리

·대량 작업을 하기 위한 SORT_AREA, HASH_AREA의 메모리 확보

·병목을 없애기 위한 작업계획 수립

·대형 테이블인 경우는 파티션 고려

응답시간

(Response Time)

-사용자가 키를 누른 때부터 시스템이 응답할 때까지의 시간

-일반적으로 OLTP 시스템에서 성능지표가 됨

※ 응답 시간 향상

·인덱스를 이용하여 엑세스 경로 단축

·부분 범위 처리 실시

·Sort-Merge 조인이나 Hash 조인을 사용하지 않고 Nest-Loop조인 처리

·불필요한 결과 정렬 작업을 없애거나 인덱스를 이용한 정렬

·잠김(Locking) 발생 억제. 예를 들어 시퀀스 오븍젝트 이용

·하드 파싱 억제

로드시간

(Load Time)

-정기적이거나 비정기적으로 발생되는 데이터베이스에 데이터를 로드하는 작업 수행시간

※ 로드시간 단축

·로그파일을 생성하지 않고 다이렉트 로드(Direct Load) 사용

·병렬로드 작업 실시 및 디스크 I/O 경합이 없도록 작업 분산

·인덱스가 많은 테이블은 인덱스 삭제하고 데이터로드 후 인덱스 재생

·파티션을 이용하여 작업을 단순화

구분

내용

CPU 튜닝

- Peak Time 시에 60~70%의 사용량 유지 권고

- CPU증설 또는 CPU 과다 점유하는 프로세스를 찾아서 해결

메모리 튜닝

- DBMS의 사용 메모리의 최적화 필요

- 업무를 가능하게 하기 위한 충분한 메모리 확보

- DBMS 메모리 + Session 메모리

I/O 튜닝

- I/O를 분산시킬 수 있도록 데이터 파일의 재배치

- Raid를 이용-> I/O가 많이 일어나는 것은 Raid 0/1에 배치

네트워크튜닝

- Ping, Ftp를 이용하여 응답시간 분석

작업수행방식 개선

-우선순위, Parallel Processing 구현, Data Sharing 구현

구분

내용

I/O 최소화

실제 필요한 Data만을 Read함

(SQL Tuning, Index Tuning, Partitioning 필요)

Buffer Pool 튜닝

추후에 사용한 가능성이 높은 Data는 Buffer Pool에 오래 머물도록 함

Commit/Check Point 튜닝

Commit/Check Point 주기 조절

Thread/Reuse

동일한 SQL 문의 사용을 통한 Parsing 방지(?)

Middleware의 기능과 연동

구분

내용

테이블의 분할/통합

- 논리적으로는 통합된 단일 테이블이지만 DBMS가 지원하는 파티션 기능 적용함으로써 액세스 효율화로 DB I/O를 최적화 할 수 있음

- DBMS에 따라 파티션 기능이 제약되는 경우에는 테이블의 수평분할 고려

- 액세스 패턴에 따라 단일 테이블을 1:1로 수직분할 고려할 수 있음

식별자 지정

- 본질 식별자와 인조 식별자의 선택에 따라 정보의 상속과 단절에 영향을 줄 수 있음

효율적인 인덱스 전략

- 최소한의 인덱스로 최대의 효과를 얻을 수 있는 최적의 인덱스 구조 수립

- 인덱스 분포도 15% 이하 유용 유도(15% 이상이면 Full Scan 유리)

적절한 데이터 타입 선택

- 조인 시 연결되는 컬럼의 데이터 타입이 다른 경우 내부적인 변형에 의해 인덱스가 있음에도 불구하고 활용을 못해 조인 순서나 조인 방식을 달리 선택하는 비효율 발생 할 수 있음

구 분

내 용

예 시

옵티마이저에 대한 이해

옵티마이저의 목표 : SQL로 요구된 결과를 최소의 비용으로 처리할 수 있는 처리경로를 결정

- 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;