SPA(SQL Performance Analyze)를 이용한 통계 정보 수집

㈜엑셈 컨설팅본부 /DB컨설팅팀 오 경렬

1. SPA란

SPA(SQL Performance Analyze) 는 RAT(Real Application Testing) 서비스의 옵션 중에 하나 이다 . 본 문서는 SPA 를 이용해 좀더 안정적으로 통계정보를 생성하는 Process 에 대해 다룬다 .

2. 통계 정보와 SQL Tuning

튜닝은 LEADING(A) USE_NL(B) INDEX(A A(COL1)) 처럼 간단한 힌트로 대부분 해결 가능하 다 . 이것이 가능한 이유는 Leading 과 같은 힌트로 쿼리의 키 조건이 무엇인지 지정해주면 나머 지 플랜은 Oracle Optimizer 가 Query Transformation 등 최적화 알고리즘을 적용해 최적의 성능을 낼 수 있도록 플랜을 작성하기 때문이다 .

튜너 A 가 사이트에 튜닝 지원을 간다 .
사이트에 도착한 A 는 가지고 있던 스크립트로 능숙하게 Tuning 대상을 발췌한다 .
수집 기준은 Execution 이 1000 번 이상이면서 Buffer Gets 이 10000 Block 이상인 쿼리 무려 200 개의 쿼리가 추출되었다 ...
A 군은 순간 당황했지만 빠르게 안정을 되찾고 입고 있던 와이셔츠의 소매 단추를 풀고 두번 접어올리고 나서는 차고 있던 시계를 풀어 책상 위 에 올려 놓는다 .
시간 은 9 시 30 분을 지나고 있다 .
" 흠 .. 오늘 몇 개나 할 수 있을까 ...."
A 군은 알듯 말듯한 미소를 지어 보이고는 중지 손가락으로 안경을 한번 치켜 올리고 이내 불꽃 튜닝에 들어간다
그리고 .. 엑셀에 하나 둘 완료된 쿼리가 늘어날 때 마다 ... 희열을 느낀다 ....
이 글을 보는 당신은 어떻게 할 것인가
A 군의 판단이 잘못된 것인가
잘못된 판단이 아니다 최선의 판단이다 ..... 11g 전 까지는 .... 11g New Feature 인 Pendin g Statistics 를 SPA 와 함께 사용한다면 A 군의 200 개의 튜닝 대상 쿼리는 10 개 내외로 줄어 들 수도 있다 . 오라클 Optimizer 는 생각보다 똑똑하다 .

3. SPA 사용 가능 여부 확인

RAT Option 이 FALSE 면 사용할 수 없다 .

4. 사용 예

간단한 사례를 다룬다 . 본인의 것으로 흡수하고 수정해서 실무에 적용하길 바란다 .

시나리오 요약
0. 테스트 스키마 생성 및 쿼리 수행
1. SQL_SET 생성
2. ANALYSIS_TASK #1 생성
3. SCHEMA or TABLE Pending Statistcs 설정
4. 통계 정보 수집
5. Session Use Pending Statistics 설정
6. ANALYSIS_TASK #2 생성
7. ANALYSIS_TASK #1, #2 비교
8. 리포팅
9. 비교 결과 조회 스크립트

0. 테스트 스키마 생성 및 쿼리 수행

1. SQL_SET 생성

2. ANALYSIS_TASK #1 생성

ANALYSIS_TASK 는 4 가지 방법으로 생성 가능하다 .

3. SCHEMA or TABLE Pending Statistcs 설정

4. 통계정보 수집

5. Session Use Pending Statistics 설정

6. ANALYSIS_TASK #2 생성

7. ANALYSIS_TASK #1, #2 비교

8. 리포팅

9. 비교 결과 조회 스크립트

5.마치며

본 문서의 내용은 프로그램으로 치면 "Hello World" 이다 . 꼭 내용을 숙지하고 스크립트나 엑셀 메크로 등으로 자동화하여 업무에 적용하길 바란다 . 이론은 절대 경험을 이기지 못한다 .