데이터베이스 성능 튜닝 (Database Performance Tuning) Version 8.2

데이터베이스 성능 튜닝 (Database Performance Tuning) Version 8.2

목 차 (1) • 제 1 장 데이터베이스 성능 튜닝 개요 • 왜 튜닝을 하는가 • 누가 튜닝을 하는가 • 언제 튜닝을 하는가 • 성능에 문제를 야기하는 요인들 • 제 2 장 튜닝 절차에 관한 계획과 관리 • 응답 시간 문제 관리 • 시스템간의 부하 관리 • 표준 설계 방법론의 선택 • 보안과 성능에 관한 표준

목 차 (2) • 제 3 장 성능을 위한 설계사항들 • 데이터 모델의 선택 • 인덱스 튜닝 • 데이터베이스 비정규화 • 트리거, 패키지, 프로시져, 함수의 사용 • 제 4 장 프로그램 튜닝 • 프로그램 튜닝 개요 • SQL 최적화 • PL/SQL 최적화 • JOIN 최적화 • Locking 전략의 선택

목 차 (3) • 제 5 장 데이터베이스 관리 튜닝 • 메모리 튜닝 • 디스크 I/O 튜닝 • 데이터베이스 구성 요소의 생성 • 여러가지 튜닝 도구들의 활용 • 데이터베이스 백업과 복구에 관한 튜닝

제1장 데이터베이스 성능 튜닝의 개요 • 데이터베이스 성능튜닝이란 • 성능향상에 영향을 주는 요소들 • 왜 튜닝을 하는가 • 누가 튜닝을 하는가 • 언제 튜닝을 하는가 • 성능에 문제를 야기하는 요인들 • 설계와 개발에 있어서의 문제 • 시스템 자원의 문제

데이터베이스 성능 튜닝이란 Bad Result No Yes Determine a Solution and Set Goals Is There a Problem? Test the Solution Analyze the Result Determine the Problem? No Yes Good Result

성능향상에 영향을 주는 요소들 • 다운사이징 • Client/Server와 분산 데이터베이스 시스템 • 병렬 서버 • GUIs (Graphical User Interfaces) • BLOBs (Large binary Objects) • 사용자와 처리 업무의 증가

왜 튜닝을 하는가 • 튜닝의 경제적 잇점 • 튜닝의 인간적인 잇점

누가 튜닝을 하는가 • 응용 설계가(Application Designer) • 응용 개발자(Application Developer) • DBA(Database Administarator) • Hardware/Software 관리자

언제 튜닝을 하는가 - 계획단계 • 프로세스에 의해 요구되어지는 시스템 및 정보기술의 정의 • 하드웨어및 소프트웨어 구성 선택 • 시스템 확장을 감안한 용량계획 수립 • 시스템 분석 및 설계표준 정립 • 시스템별 성능기대치 결정 • 성능목표 설정과 우선순위 결정은 분명하고 일관성 있게 계획 분석/ 설계 개발 검수 운영

계획 언제 튜닝을 하는가 - 분석/설계단계 • 성능을 감안한 데이터모델링 및 데이터베이스 설계 • Table간의 자동화 규칙의 적절한 설계 • 성능을 감안한 프로그램 설계 및 Table 구조 변경, 추가 • 데이터요구에 적합한 인덱스 정의 • 데이터 모델과 User Interface 간의 적합성 검증 분석/ 설계 개발 검수 운영

계획 언제 튜닝을 하는가 - 개발단계 • 옵티마이저의 로직 이해와 선택 • SQL 최적화 • 적용업무 프로그램에 적합한 Locking 범위 결정 • 재사용 Library(Stored Procedure, Function)의 정립 및 적극적 활용 • 프로그램 코딩 표준의 엄격한 준수 분석/ 설계 개발 검수 운영

계획 언제 튜닝을 하는가 - 검수단계 • 실제 운영상황에 견줄만한 테스트용 데이터량의 확보 • 실제 운영환경과 동일한 시스템 구성(Configuration) • 테스트및 품질보증을 통과한 프로그램만을 현업에 적용 • 실제 운영과 테스트용 하드웨어의 별도 사용 분석/ 설계 개발 검수 운영

계획 언제 튜닝을 하는가 - 운영단계 분석/ 설계 • 시스템성능에 대한 지속적인 감시 • 시스템및 RDBMS 에서 제공하는 모든 성능감시도구 활용 • O/S 와 RDBMS 튜닝간의 적합성 검증 개발 검수 운영

언제 튜닝을 하는가 - 최적의 시점 • 따라서 Tuning의 가장 효과적인 시점은 디자인 단계=> 최소의 비용으로 최대의 효과 기대

성능에 문제를 야기 하는 요인들 • 설계와 개발에 있어서의 문제 • 시스템 자원의 문제 • 메모리 문제와 튜닝 • 디스크 I/O 문제와 튜닝 • CPU 문제와 튜닝 • 네트워크 문제와 튜닝

b. a. Database Programs Design Database 15% 20% 15% 17.5% System System 2.5% 5% Design Programs 60% 65% 설계와 개발에 있어서의 문제 • 빈약한 응답시간을 야기하는 요인간의 비교 : a • 이를 해결하기 위한 각 요소들간의 노력 요구도 : b

Memory CPU Disk Network 시스템 자원의 문제 • 병목 현상 • Memory • Disk I/O와 콘트롤러 • CPU • Network • 한 자원 요소의 개선은 다른 요소의 개선에도 영향 최초의 병목현상이 발생하는 곳 시스템 성능저하 Throughput

메모리 문제와 튜닝 • 시스템의 자유기억 공간을 5%이하로 만들지 말것 • INIT.ORA내의 메모리 조정 변수들을 조정한다. • SORT_AREA_SIZE, SORT_DIRECT_WRITES, SORT_WRITE_BUFFERS, SORT_WRITE_BUFFER_SIZE Operating System Kernel 시스템 관리자에 의하여 조정 가능 Operating System Caches ORACLE Kernel ORACLE Detached Processes DBA에 의하여 조정 가능 Optional Detached Processes DBA에 의하여 조정 가능 System Global Area (SGA) User1 DBA에 의하여 조정 가능 User2 User3 시스템 관리자에 의하여 조정 가능 User4 User5 Free Memory

디스크 I/O 문제와 튜닝 • 기본적인 디스크 분할로 성능 개선 • 디스크 체인과 이주의 방지 및 감소를 위한 노력 • 데이터베이스 프로그램 튜닝의 목적은 데이터베이스 디스크 접근을 최소로 하는 것 • INIT.ORA 매개변수의 조정으로 디스크 I/O 성능 개선 • LOG_CHECKPOINT_INTERVAL, LOG_BUFFER Operating System Tables Redo Logs Archive Logs Indexes Database Database

CPU 문제와 튜닝 • CPU를 추가 구입 전에 문제를 야기할 수 있는 요소를 확인 • 튜닝되지 않은 업무가 있는가 • 사용자들이 복수로 LOG되어 있지 않은가 • 장시간 수행되는 업무가 일반 업무시간에 수행되지 않는가 • 메모리와 디스크간의 I/O 병목현상은 없는가 • DB_BLOCK_BUFFERS, SHARED_POOL_SIZE등의 변수 값이 적절하게 되어 있는가

네트워크 문제와 튜닝 • 네트워크 병목현상의 해소 • 데이터베이스 접근의 감소 • 주의 깊은 데이터의 분산 • 튜닝 제안 사항 • Client/Server 시스템이라면, 장시간 수행되는 업무는 데이터베이스 서버쪽에 둔다 • 요구할때만 데이터베이스에 접근한다 • 패키지, 프로시져, 트리거, 함수, 제한 등의 적극 사용

제2장 튜닝 절차에 관한 계획과 관리 • 튜닝 목표 • 응답시간 문제 관리 • Check Point의 관리 • 관리자를 위한 성능 힌트 • 표준 설계 방법론의 선택 • 데이터베이스 환경 표준의 설정 • 성능 표준

튜닝 목표 • 시스템 디자인이나 관리시 특정한 성능 목표의 설정 필요 • 성능 목표의 명확한 정의 • 가장 중요한 요소 설정 • 성능 향상을 위한 선택 과정 용이 • 초기화 파라메터나 SQL문의 임의 변경 • 뚜렷한 성능 향상없이 튜닝시간만 낭비 가능

응답시간 문제 관리 • 대부분의 시스템에서 성능의 척도는 응답시간으로 관리 • 수용 가능한 응답시간의 정의 • 응답 시간의 고려는 On-Line 사용자로 한다 • 함수의 95% 정도는 2.5초 이하에 수행되어야 한다 • 전체 응답 시간이 10초를 초과해서는 안된다 • 보고서 작업의 경우 중심 업무시간대에서 3분을 넘지 않도록 한다 • 이외에 GUI화면에서 한 field에서 다른 field로의 이전 시간 간격을 1초 미만으로 하는 제한을 둘 수 있다

Plan Analyze Design Develop Accept Check Point의 관리 적용 업무의 통합 만족할수 있는 적정 응답시간을 설정한다 설계 표준을 정한다 논리적 모델에서 물리적 모델로 전환시 성능의 관점을 고려 개발을 하는 기계를 튜닝한다 개발 표준을 정한다 성능을 위한 프로그램의 수행 성능을 위한 프로그램의 테스트 데이터 양의 충분한 확보 시스템 응답시간 테스트

Tuning Cost Later Tuning Early Production Production Time 관리자를 위한 성능 힌트 • 개발팀에는 이전에 업무를 튜닝한 경험이 많은 사람을 참여 시키도록 한다 • 개발 단계 초기에서 잠재적인 문제들을 구별해내고 조정한다 • 썩은 사과 한 알이 바구니의 모든 사과를 병들게 할 수 있다

표준 설계 방법론의 선택 • 설계시에 고려해야할 요소들 • 적용 업무의 복잡도 • 예산 • 개발 스케쥴 • 개발 프로젝트 팀 구성원의 숙련도와 경험 • 조직내의 통신 채널의 방법이나 유형 • 소프트웨어/하드웨어의 선택 • 개발하기 쉽고 배우기 쉬운 개발 도구의 선택 • 하드웨어 유형이나 구성방법

데이터베이스 환경 표준의 설정 • 데이터베이스 SID • 디스크 배치 • 데이터베이스 데이터 파일 배치 • 대규모 롤백 세그먼트 • 테이블스페이스 구조 • 객체(Object) 명명 표준화 • 객체 구조

성능 표준 • 응답 시간 기대치 • 명시적 커서의 사용 • WHEN절에서 트리거의 발생 • 트리거에 비하여 함수나 프로시져의 사용으로 대체 • SQL hint의 적절한 사용 • 대규모 데이터베이스 객체를 SGA에 고정한다 • 정기적인 코드의 관찰 • INIT.ORA 매개변수의 적극적 활용

제3장 성능을 위한 설계 사항들 • 구조의 선택 • 데이터 모델 튜닝 • Index ... • 여러가지 Index • 클러스터링/해쉬/인덱스 비교 • 키에 대한 인덱스적용 • 데이터베이스 비정규화 • 데이터베이스 비정규화시 고려사항 • 테이블 재정의 - 중복테이블의 추가 • 트리거, 패키지, 프로시져, 함수의 사용

구조의 선택 • 단일구조(Unitary) • 클라이언트-서버 구조(Client-Server) • 병렬서버 구조(Parallel Server) • 분산데이터베이스 구조(Distributed Database) • 멀티쓰레드 서버 구조(Multithreaded Server)

시스템 디자인시 가이드라인 • 불필요한 네트워크 트래픽 제거 • 적절한 Server Option의 사용 • Default Locking 사용 • 추적이 가능한 응용 모듈 등록 • 최적의 Data Block 크기 선택 • 적절한 데이터의 분산

데이터 모델 튜닝 • 튜닝전 준비사항 • 데이터모델의 세부 다이아그램 • 테이블의 평균 로우 수, 한 로우의 평균크기 • 최대집중시(Peak Time) 초당 수행되는 트랜잭션 크기 • 데이터정리전략에 대한 문서 • 튜닝되지 않은 데이터모델 • 일대일 관계 • 온라인 트랜잭션 처리에 적절하지 않은 테이블 • 다수의 테이블을 Join하는 SQL문 • Full Table Scan을 자주 유도하는 SQL문

EMP_idx1 EMP Table iNDEX VALUE ROWID/Address 사원번호 성명 직위 2812 0000089D.0000.0001 2854 이태호 부장 2840 0000089D.0001.0001 2858 문상두 사원 2843 0000089D.0002.0001 2812 채정열 주임 2854 0000089D.0003.0001 2855 최미영 사원 2855 0000089D.0004.0001 2863 고운산 사원 2856 0000089D.000A.0001 2840 김성혜 과장 2863 0000089D.0003B.0001 2843 이진옥 대리 인덱스의 구조 SELECT 사원번호,성명,직위 FROM EMP WHERE 사원번호 BETWEEN ‘2843’ AND ‘2856’ Sort된 결과

인덱스 적용대상 • 적용대상 • 중,대규모 테이블(6블럭 이상) • 대상 데이터의 10% 이내의 데이터 요구인 경우 • 스캔의 범위를 줄이고자 할때 • 정렬을 필요로 하는 경우 • 테이블에 접근하지 않고 인덱스만으로 데이터 요구를 해결하고자 하는 경우

인덱스 선정기준 • 인덱스 대상 테이블 선정 • 테이블의 크기가 5~6블럭 이상 • 무작위접근(Random Access)이 빈번한 경우 • 특정범위/순서의 데이터조회가 필요한 경우 • Join의 연결고리가 되는 칼럼 • 참조무결성을 지정한 경우 연결고리되는 칼럼 • Nested Join이 일어나는 테이블 • 인덱스 대상 칼럼 선정 • 분포도와 손익분기점 : 분포도는 10~15%를 넘지 않아야 한다. • 인덱스 머지(Index Merge) • 결합인덱스(Composite Index)

인덱스 선정절차 • 해당 테이블의 가능한 모든 접근형태 수집 • 대상칼럼 선정 및 분포도 조사 • 반복 수행되는 Critical Access Path의 해결 • 클러스터링 검토 • 인덱스 칼럼의 조합 및 순서의 결정 • 시험생성 및 테스트 • 수정이 필요한 Application 조사 및 수정 • 일괄적용

인덱스의 효율적 이용 • 인덱스 테이블과 데이터 테이블은 각기 다른 디스크에 배치 • 인덱스 테이블에 대한 적절한 기억공간 할당 • 테이블 접근없이 인덱스만으로 데이터 요구 해결 • 인덱스 생성 및 삭제 시점의 효율적 운용 검토 • 데이터 로드나 일시에 많은 데이터를 입력할 경우 인덱스에 대한 부하를 줄이기 위해 인덱스를 삭제, 입력 및 로드가 끝난 이후 재생성 • 인덱스의 명명은 생성목적과 의미를 내포

인덱스의 활용 1 “다음의 경우에는 인덱스가 사용되지 않는다.” • 복합 인덱스에서 Leading Column이 제한 조건에 사용되지 못한 경우 • 인덱스칼럼의 변형 • 부정형 비교 • 널을 사용한 비교 • LIKE문의 ‘%’가 맨 처음에 위치한 경우 • Optimizer에 의한 취사선택

인덱스의 활용 2 • 인덱스 칼럼의 묵시적 가공 • CHAR, DATE => DATE를 기준WHERE ord_date = ‘01-FEB-97’ => WHERE ord_date = TO_DATE(‘01-FEB-97’,’dd-mon-yy’) • CHAR, NUMBER => NUMBER를 기준WHERE dept_code = 1234 => WHERE TO_NUMBER(dept_code) = 1234

COMPANY 테이블의 총 ROW수 :1000CITY의 구분값 :200 STATE의 구분값 :400CITY+STATE의 구분값 :200=> 선택성:CITY(200/1000), STATE(400/1000) SELECT CITY,STATE FROM COMPANY WHERE CITY = ‘NEW YORK’ AND STATE = ‘CALI’ Case 3 Case 2 Case 1 인덱스 #1 : City, State인덱스 #2 : None 인덱스 #1 : City,State인덱스 #2 : State 인덱스 #1 : City인덱스 #2 : State 인덱스의 활용 3 • 인덱스의 선택성(Selectivity) • 테이블의 전체 레코드중에서 구분값(Distinct Value)이 차지하는 비율. • 선택성 = (구분값의 갯수 / 전체 ROW 갯수) * 100 • 선택성이 높을 수록 인덱스의 분포도는 양호( Unique Index의 선택성은 1.00) 예) 테이블의 레코드 수:1000, 구분값:950 => 선택성 :950/1000 (0.95)

where COL1 = ‘A’ and COL2 between ‘111’ and ‘113’ where COL1 = ‘A’ and COL2 = ‘112’ ROWID COL2 COL1 COL1 COL2 ROWID COL1 COL2 ROWID COL2 COL1 A 110 101 110 A 101 A 110 101 ROWID COL1 COL2 110 A 101 COL1 COL2 ROWID 110 B 105 A 111 102 A 111 102 110 B 105 111 A 102 A 112 103 A 112 103 111 A 102 A 113 104 111 B 106 A 113 104 111 B 106 A 114 105 112 A 103 A 114 105 112 A 103 112 B 108 B 110 106 B 110 106 112 B 108 113 A 104 B 111 107 B 111 107 113 A 104 B 112 108 113 B 109 B 112 108 113 B 109 B 113 109 114 A 105 B 113 109 114 A 105 COL1+ COL2 COL2+COL1 COL1+ COL2 COL2+COL1 인덱스는 B*Tree 방식으로 Search하므로복합 인덱스 모두 ‘=‘조건으로 조회하는 경우 Leading Column의 분포도는 성능에 큰 영향없음 인덱스의 활용 4 • 예제 (COL2가 COL1보다 분포도가 좋은 경우) ROWID 비록 분포도가 좋은 칼럼이 Leading Column이더라도 Range 조건으로 사용되면 인덱스가 B*Tree 방식으로 조건을 만족하는 첫 레코드를 Search한 후, 레코드를 건너 뛰면서 Search하므로 불리

추가된 인덱스가 미치는 영향 SELECT * FROM SALES WHERE 부서 = ‘843’ AND 기준일자 = ‘970518’ AND 품목 = ‘B023’ INDEX1에 금액Column 추가 INDEX 1 INDEX 1 INDEX 2 INDEX 2 부서||기준일자 품목 부서||기준일자||금액 품목 INDEX 1 INDEX 1 부서||기준일자 = ‘843’||’970518’ 부서||기준일자 = ‘843’||’970518’||’%’ INDEX2 사용 INDEX1 사용 INDEX 2 INDEX 2 품목 = ‘B023’ 품목 = ‘B023’

Index Table Columns Cluster Cluster RowID Key Header 3 …………… 111 3 … …………… … … 10 Cluster Header … 123 1 1 AB ………… … 12 BD ………… … … 123 … …………… 993 … 99 DD ………… … … 123 … …………… Clustered Index • 중대형 테이블에 적용 • 6개 이상의 물리적 블록을 갖는 테이블 • 같은 순서로 자주 정렬되는 테이블 • 특정한 컬럼의 수치범위 (between)내에서 자주 접근되는 테이블 • 같은 순서를 이용해서 자주 처리되는 테이블 • 삽입이나 삭제가 자주 일어나지 않는 테이블 • Cluested Index가 설정되어 있는 컬럼에 변화가 자주 일어나지 않는 테이블

Hash Index • 중대형 테이블에 적용 • 6개 이상의 물리적 블록을 갖는 테이블 • 임의순서(Random Order)로 자주 접근되는 테이블 • 동일 컬럼의 구분값(Hash Key)을 선택 기준으로 명시한다 • 해쉬키는 자주 갱신되지 않는 값을 선택한다 • 같은 테이블에 해쉬와 Cluster를 둘 다 설정할 수 없다 • 해슁결과 동일한 논리적 주소가 발생하는 것 삼가한다 • 해쉬동의어의 발생이 일어나지 않도록 한다 • 기본키나 대체키는 해쉬키의 좋은 대상

Bitmap Index • 1과 0의 형태로 값을 저장한다 • 테이블이 크고 분포도 고르지 않을 때 적용한다. CREATE BITMAP INDEX prod_color ON prod(color) Size = ‘Small’ 0 0 1 0 0 1 0 1 0 1 0 0 0 1 0 1 Size = ‘Med’ 1 0 0 0 1 0 1 0 0 0 0 1 0 1 0 0 Size = ‘Large’ 0 1 0 1 0 0 0 0 1 0 1 0 1 0 1 0 Color = ‘Blue’ 0 1 0 1 0 0 1 0 1 0 1 0 0 0 1 0 Color = ‘Red’ 0 0 1 0 1 0 0 0 0 0 0 0 1 0 0 1 Color = ‘Green’ 1 0 0 0 0 1 0 1 0 1 0 1 0 1 0 0

클러스터링/해쉬/인덱스 비교 설계 요소 테이블 클러스터링 테이블 해쉬 Nonclustered 인덱스 중. 대형 중. 대형 중. 대형 테이블 크기 선택기준 값의 범위 불연속의 값 범위, 불연속의 값, 내장함수 출력되는 열의 순서 무작위(Random) 정렬(IF ORDERED INDEX) 정렬(ORDER BY, GROUP BY, DISTINCT, UNION, JOINS) 출력되는 칼럼의 수 다수 가능 다수 가능 다수 가능 소수일 경우 인덱스만의 접근 출력되는 열의 수 다수 가능 소수 소수이나 해쉬보다 많음 부분키 조회 예 아니오 예(특히 정렬된 인덱스의 경우

키에 대한 인덱스적용 • Primary Key • Foreign Key

데이터베이스 비정규화 • 데이터의 중복허용(칼럼/테이블) • 전제조건 : 완전히 정규화된 데이터모델 • 성능문제를 해결하기 위한 비정규화 • 기능문제를 해결하기 위한 비정규화 • 비정규화는 언제하는가 ? • SQL 테이블조인을 반복적으로 수행할 경우 • 덩치 큰 테이블에 Sort/Merge작업을 수행할 경우

데이터베이스 성능 튜닝 (Database Performance Tuning) Version 8.2