[sql] DB튜닝 기초

1. Tuning 이란?

- SQL : 정확한 데이터의 추출이 목적

- SQL Tuning : SQL을 기반으로 효율적으로 데이터를 추출하는 것

- 데이터베이스튜닝은 얼마나 빨리, 얼마나 정확하게, 그리고 얼마나 효율적으로 찾는가에 있다.

2. 데이터베이스 성능 튜닝

(1) 성능에 영향을 주는 컴퓨팅 환경에서의 주요 개발 요소들

- Downsizing : 메인 프레임 환경에서 좀 더 작은 시스템을 사용하게 될 때는 이전과 같은 성능을 기대할 수는 없으므로 튜닝은 필수 요소

- Client-server and distributed databases : 시스템이 팽창하면서 새로운 H/W 환경이나 OS, 네트워크 등을 포함하게 된다면 전반적인 컴퓨팅 환경을 보다 더 잘 파악해야 함.

- 병렬 서버 : 데이터베이스가 공유된 디스크를 접근한다면 Locking 문제를 야기 할 수 있음.

- Graphical User Interfaces : 시스템을 좀 더 사용하기 쉽게 하므로 하나의 시스템에는 보다 많은 동시 사용자들이 들어오게 됨.

(2) 왜 데이터베이스 성능 튜닝을 하는가?

추가적인 장비 구입 억제, 제품 비용 감소, 자원의 낭비 억제, 보다 경쟁적인 우위를 가질 수 있음, 조직내의 처리량 증가

(3) 누가 데이터베이스 성능 튜닝을 하는가

- 응용 설계자 : 응용시스템에서 모든 사용자들이 데이터의 흐름을 이해할 수 있도록 시스템을 디자인 할 

- 응용 개발자 : Statement Tuning 과정에서 응용 모듈과 SQL문을 쉽고 빠르게 확인할 수 있도록 구현

- DBA : 비정상적인 시스템 성능 확인과 수정을 위해 시스템 Activity를 주의 깊게 모니터링하고 시스템을 튜닝

- 하드웨어/소프트웨어 관리자 : 사용자들이 효과적으로 시스템을 디자인하고 관리할 수 있도록 하드웨어/소프트웨어 환경을 튜닝

(4) 언제 데이터베이스 성능 튜닝을 하는가

시스템을 개발하기 위해서는 계획, 분석/설계 개발, 검수, 운영의 단계를 거치며 각 단계에서의 튜닝을 고려

- 시스템 계획단계 : 하드웨어/소프트웨어 자원, 데이터베이스 자원, 사용자 자원

- 분석/설계단계 : 어디에서 데이터를 비 정규화 할 것인가? 어디에 성능 증가를 위한 데이터의 중복을 도입할 것인가?

- 개발단계 : Optimizer를 선택할 때, SQL 문장을 Tuning시, 적용업무에 가장 적함한 잠금(Locking)의 범위 결정시

- 검수단계 : 실제 운영상황과 동일한 운영환경에서 테스팅 수행

- 운영단계 : 시스템을 계속 운영하면서 성능과 시스템의 사용자 요구사항을 분석하며, 여러 가지 도구를 이용하여 시스템을 지속적이고 효과적으로 Tuning하도록 함.

튜닝의 최적 시점 : 분석/설계 단계, 최소의 비용으로 최대의 효과를 기대할 수 있다. 모델링이 중요

(5) 데이터베이스 성능 튜닝의 목표 설정은 어떻게?

- 성능 목표의 정확한 정의가 필요

- 가장 중요한 요소를 설정

- 성능 향상을 위한 선택 과정이 용이한가를 조사

3. 개발단계 이후 튜닝을 실시하기 전 선행 작업

(1) Tuning의 절차는 어떻게 될까?

- 시스템에 문제가 있는지 분석

- 문제를 일으킨 원인 분석

- 목표 설정

- 튜닝 작업 실시

- 결과를 분석하여 정리

(2) Explain Plan 이란?

- Explain Plan : SQL문을 분석하고 해석하여 실행계획을 수립, 실행계획 테이블에 저장 해주는 명령

- 실행계획: SQL이 요구한 데이터를 추출하기 위해 오라클이 차례로 수행하는 작업방법

- Explain Plan을 확인함으로써 index가 적용되어 지는 순서가 확인 가능하며 사용자들이 SQL문의 액세스 경로를 확인할 수 있음.

(3) Optimizer란?

- Rule Based Optimizer 

데이터베이스에 접근하는 경로를 찾는데, 사전 정의된 규칙을 사용

예측가능하고 통제 가능하므로 대부분의 DBA, 프로그래머들이 선호

- Cost Based Optimizer : 

데이터베이스 정보를 활용.

데이터베이스 정보들은 생성된 테이블을 ANALYZE명령에 의해 분석된 후에야 활용 가능. 

ANALYZE 명령은 과부하를 초래하기 때문에 항상 최선의 선택은 아님.

(4) SQL_TRACE의 방법

SQL_TRACE 설정 -> 세션별로 실행통계 모아짐 -> TRACE 파일 생성 -> TKPROF -> 출력

- SQL_TRACE의 지정 : INIT.ORA 의 파라메터를 이용하여 설정, 세션단위로도 설정이 가능

(5) TKPROF란?

- SQL_TRACE를 통하여 생성된 TRACE 파일은 TKPROF 유틸리티를 통하여 분석이 가능한 파일형식으로 전환하여 출력하게 되며 출력된 파일을 사용자가 분석하게 된다. 

- TRACE의 종류 :  SQL문장 분석을 위한 TRACE, 시스템 이상 시 발생하는 TRACE

(6) Access Path

- SQL문에서 WHERE 조건문의 형태를 분석하는 자료인 ACCESS PATH를 조사함으로써 시스템 전체적으로 각 TABLE의 엑세스 형태를 분석 기록한다. ACCESS PATH는 주요 컬럼에 대하여 조사 및 기록한다. 또한 조사한 ACCESS PATH는 인덱스 검증 및 클러스터링을 검토하는 중요한 자료로 쓰이게 된다.

내용 정리

+ 왜 튜닝을 하는가?

- 튜닝의 장점

추가적인 장비 구입을 억제

제품 비용을 감소시키며 자원의 낭비 억제

보다 경쟁적인 우위를 가질 수 있음

조직내의 처리량 증가

사용자들은 보다 생선적이 될 수 있음

-튜닝은 언제

시스템의 성능 향상을 기대하고자 할 경우

현 시스템에 대해 불만이 없으면 할 필요 없음

+ 튜닝은 누가 언제 하는가?

- 응용 설계가의 Tuning은? - 분석/설계 단계

관계형 데이터베이스를 설계하는 목적은 할 수 있는 최상의 상태로 데이터를 정규화 하는데 있으나 고전적인 데이터베이스 설계의 관점에서 공통적인 개념들을 적용해야 할 필요도 있음.

어디에서 데이터를 비 정규화 할 것인가

어디에 성능 증가를 위한 데이터의 중복을 도입할 것인가

프로그램을 후에 변경하기 쉽고 튜닝하기 위하여 반드시 단위화 해야 함

작은 프로그램은 일반적으로 더 적은 메모리를 필요로 한다.

단위화는 유지 또는 간편하게 한다.

- 응용 개발자의 Tuning은? - 개발/구현 단계

개발에 사용되는 SQL 문장을 Tuning함

개발자는 Oracle을 사용할 경우라면, EXPLAIN PLAN이나 TKPROF등의 유틸리티의 사용에 친숙해지고 그들의 프로그램에 광범위하게 적용할 수 있어야 한다.

- DBA의 Tuning은? - 개발/구현 단계

사용하고자 하는 최적화 기술(Optimizer)을 선택

비용기반 최적화(Cost-based Optimizer)는 병렬질의나 분산 데이터베이스를 선택했을 때 보다 좋은 이점을 가짐.

비용기반 최적화의 가장 좋은 특징은 경험이 적은 프로그래머가 사용했을 때 규칙기반 최적화(Rule-based Optimizer)보다 좀 더 빠른 시간 내에 Tuning된 code를 얻을 수 있다.

데이터베이스를 효과적으로 관리

성능에 관련된 문제를 능동적으로 대처한다.

수시로 최종사용자가 사용하는 것처럼 데이터베이스의 여러 프로시저 등을 실행하고 반응시간 등을 관찰한다.