쿼리 성능에 문제가 생기면 Explain Plan 을 통해 실행계획 을 먼저 확인 (X-ray ) 한다.
그것만으로 문제점을 정확히 파악할 수 없을 때 AutoTrace 를 걸어 수행 시 실제 일량을 측정 (CT촬영) 하거나
SQL 트레이스 (MRI 촬영) 를 걸어 내부 수행 절차상 어느 단계에서 부하를 일으키는지 눈으로 확인한다.
문제점이 파악되면
쿼리를 변환하거나
옵티마이저 힌트를 사용해 튜닝 (외용약 또는 내복약 처방) 을 실시하고,
그것만으로 부족하다 판단되면 인덱스 조정 (칼을 대지 않는 시술) 을 병행한다.
그래도 성능에 만족하지 못하면 반정규화를 실시하거나 집계 테이블을 생성 (칼을 대는 절개 수술) 하도록 권고하지만
구조적 문제에 기인할 때만 사용하는 최후의 수단이다.
01 Explain plan
SQL> @? /rdbms/admin/utlxplan.sql
SQL> explain plan set statement_id = 'query1' for
2 select * from emp where empno = 7900 ;
02 AutoTrace
SQL 을 튜닝하는데 유용한 정보들을 많이 포함하고 있어 가장 즐겨 사용하는 도구 중 하나이다.
SQL> set autotrace on
SQL> select * from scott.emp where empno = 7900 ;
SQL> set autotrace traceonly explain
실행계획만 출력하면 되므로 쿼리를 실제 출력하지 않는다.
SQL*Plus에서 실행계획을 가장 쉽고 빠르게 확인해볼 수 있는 방법이다.
03 SQL 트레이스
SQL 튜닝할 때 가장 많이 사용되는 강력한 도구
(1) 자기 세션에 트레이스 걸기
SQL> alter session set sql_trace = true ;
관련 view에 대한 읽기 권한이 없다면
SQL> alter session set tracefile_identifier = 'oraking' ;
* TKProf 유틸리티
$ tkprof
$ tkprof ora10g_ora_14370_oraking.trc report.prf sys=no
Full Name
- Trace Kernel PROFile
- Trasient Kernel PROFile
- ToolKit PROFiler
* 트레이스 결과 분석
$ vi report.prf
call Parse : 커서를 파싱하고 실행계획을 생성하는 데 대한 통계
call Execute : 커서의 실행 단계에 대한 통계
call Fetch : 레코드를 실제로 Fetch 하는 데 대한 통계
count : Parse, Execute, Fetch 각 단계가 수행된 횟수
cpu : 현재 커서가 각 단계에서 사용한 cpu time
elapsed : 현재 커서가 각 단계를 수행하는 데 소요된 시간
disk : 디스크로부터 읽은 블록 수
query : Consistent 모드에서 읽은 버퍼 블록 수
current : Current 모드에서 읽은 버퍼 블록 수
rows : 각 단계에서 읽거나 갱신한 처리 건수
* AutoTrace 결과와 일치하는 항목
db block gets = current
consistent gets = query
physical reads = disk
SQL*Net roundtrips to/from client = fetch count
row processed = fetch rows
* 이벤트 트레이스
alter session set events '10046 trace name context forever, level 1' ;
alter session set events '10046 trace name context off' ;
설정할 수 있는 레벨 값은 1, 4, 8, 12
1 : 일반적인 SQL 트레이스
4 or 12 : 바인드 변수에 대한 정보 확인
8 or 12 : SQL 수행 도중 대기이벤트가 발생할 때마다 트레이스 파일에 기록
10046 트레이스를 4 이상으로 설정하면 트레이스 파일이 급격하게 커지므로 모니터링하다가 정지하는 것을 잊지 말자.
* Elapsed time = CPU time + Wait time
Elapsed time 은 Call 단위로 측정
애플리케이션 커서 캐싱 기법을 사용하지 않는 한
하나의 SELECT 문을 수행하는 동안 최소 3번의 Call 이 발생하고,
DML 문은 단 2번의 Call 이 발생한다.
- SELECT 문 = Parse Call + Execute Call + Fetch Call
- DML 문 = Parse Call + Execute Call
하나의 SQL 을 수행할 때의 Total Elapsed time 은,
수행 시 발생하는 모든 Call 의 Elapsed time 을 더해서 구한다.
cpu : 0.03, elapsed time : 0.07 이라면 나머지 0.04 는 wait 상태라고 이해하면 된다.
(2) 다른 세션에 트레이스 걸기
오라클 9i에서 Serial 번호가 3인 145번 세션에 레벨 12로 10046 이벤트 트레이스를 걸려면 아래와 같이 하면 된다.
SQL> exec dbms_system.set_ev (145, 3, 10046, 12, '') ;
트레이스를 해제할 때는 레벨을 0으로 설정
SQL> exec dbms_system.set_ev (145, 3, 10046, 0, '') ;
10g 이후는 dbms_monitor 패키지 사용
SQL> begin
2 dbms_monitor.session_trace_enable (
3 session_id => 145,
4 serial_num => 3,
5 waits => TRUE,
6 binds => TRUE);
7 end ;
8 /
해제할 때는 session_trace_diable 프로시저를 사용
SQL> begin
2 dbms_monitor.session_trace_disable (
3 session_id => 145,
4 serial_num => 3 ) ;
5 end ;
6 /
(3) Service, Module, Action 단위로 트레이스 걸기
dbms_application_info.set_module
WAS에서 커넥션을 얻을 때마다 호출되는 공통 모듈 내에 추가시켜주는 것이 좋다.
04 DBMS_XPLAN 패키지
(1) 예상 실행계획 출력
select * from table(dbms_xplan.display('PLAN_TABLE', 'SQL1', 'ALL'));
(2) 캐싱된 커서의 실제 실행계획 출력
커서란 하드 파싱 과정을 거쳐 메모리에 적재된 SQL과 Parse Tree, 실행계획, 실행하는데 필요한 정보를 담은 SQL Area
select * from table(dbms_xplan.display_cursor('[sql_id]', [child_no], '[format]'));
(3) 캐싱된 커서의 Row Source 별 수행 통계 출력
SQL문에 gather_plan_statistics 힌트를 사용하거나, 시스템 또는 세션 레벨에서 statistics_level 파라미터를 all로 설정하면,
실제 SQL을 수행하는 동안의 실행계획 각 오퍼레이션 단계별로 수행 통계를 수집한다.
05 V$SYSSTAT
v$sysstat : 인스턴스 기동 후 현재까지 누적된 수행 통계치를 시스템 레벨로 확인하고자 할 때 사용하는 뷰
v$sesstat : 개별 세션별로 확인할 때 사용하는 뷰
v$mystat : 현재 접속해있는 본인 세션에 대한 수행 통계 확인하는 뷰
(1) 시스템 수행 통계 수집 및 분석
두 구간 사이의 변화량을 구해 판명해야 함
(2) Ratio 기반 성능 분석
Buffer Nowait % : 버퍼 블록을 읽으려할 때 buffer busy waits 대기 없이 곧바로 읽기에 성공한 비율
Read Nowait % : Redo 로그를 기록할 공간을 요청하지 않고 곧바로 Redo 엔트리를 기록한 비율
이 비율이 낮다면, 로그 스위칭이 느리거나 너무 자주 발생함을 의미한다.
스위칭 횟수가 문제라면 Redo 로그 파일 크기를 증가시킬 필요가 있다.
자주 발생하지 않는데도 낮은 수치를 보인다면, I/O 서브시스템이 느린것으로
Redo 로그 파일을 덜 바쁜 디스크 또는 전용 디스크로 옮기는 것을 고려해야 한다.
Buffer Hit % : 디스크 읽기를 수반하지 않고 버퍼 캐시에서 블록 찾기에 성공한 비율
Latch Hit % : 래치 경합 없이 첫 번째 시도에서 곧바로 래치를 획득한 비율
Library Hit % : 라이브러리 캐시에 이미 적재된 SQL 커서를 실행하거나 오브젝트 정보를 읽으려할 때
해당 커서 또는 오브젝트 정보가 힙 영역에서 찾아진다면 히트 성공
Soft Parse % : 실행계획이 라이브러리 캐시에서 찾아져 하드 파싱을 일으키지 않고 SQL을 수행한 비율
이 비율이 낮다면 바인드 변수를 사용하도록 애플리케이션을 개선해야 한다.
Execute to Parse % : Parse Call 없이 곧바로 SQL을 수행한 비율, 즉 커서를 애플리케이션에서 캐싱한 채 반복 수행한 비율
Parse CPU to Parse Elapsed % : 파싱 총 소요 시간 중 CPU time이 차지한 비율
소프트 파싱은 하드 파싱에 비해 매우 빠르므로 CPU to Parse Elapsed % 값에 미치는 영향이 작다.
하드 파싱은 발생 횟수가 적더라도 매우 느리므로 영향력이 크다.
% Non-Parse CPU : SQL을 수행하면서 사용한 전체 CPU time 중 파싱 이외의 작업이 차지한 비율
In-memory Sort % : 전체 소트 횟수에서 In-Memory 소트 방식으로 수행한 비율
Memory Usage % : Shared Pool 내에서 현재 사용 중인 메모리 비중
% SQL with executions > 1 : 전체 SQL 개수에서 두 번 이상 수행된 SQL이 차지하는 비중
% Memory for SQL w/exec > 1 : 전체 SQL 이 차지하는 메모리 중 두 번 이상 수행된 SQL이 차지하는 메모리 비중
06 V$SYSTEM_EVENT
v$system_event : 인스턴스 기동 후 현재까지 누적된 이벤트 발생 현황을 시스템 레벨로 확인하고자 할 때 사용
v$session_event : 개인 세션별 확인
v$session_wait : 세션별로 현재 진행 중이거나 바로 직전에 발생했던 이벤트 정보만 보여줌
07 Response Time Analysis 방법론과 OWI
OWI에 기반한 튜닝은 모니터링과 튜닝을 반복하면서 병목을 해소해 나가는 방법론이다.
08 Statspack / AWR
Statspack 과 AWR은 거의 같은 내용을 담고 있으며, 다른 점이 있다면 정보를 수집하는 방식에 있다.
Statspack 은 SQL을 이용한 딕셔너리 조회 방식인데 반해
AWR은 DMA(Digital Memory Access) 방식으로 SGA를 직접 액세스 하기 때문에 좀 더 빠르게 정보를 수집할 수 있다.
(1) Statspack / AWR 기본 사용법
Statspack 에서는 PERFSTAT 계정 밑에 'stats$' 로 시작하는 뷰를 통해 수집된 성능 정보들을 조회한다.
AWR에서는 SYS 계정 밑에 'dba_hist_' 로 시작하는 뷰를 이용한다.
(2) Statspack / AWR 리포트 분석
AWR 맨 첫장의 요약보고서
Load Profile : 항목
Per Second : 각 측정 지표 값들을 측정 시간으로 나눈 것
Per Transaction : 각 측정 지표 값들을 트랜잭션 갯수로 나눈 것
%Blocks changed per Read : 읽은 블록 중 갱신이 발생하는 비중
Rollback per transaction % : 최종적으로 커밋되지 못하고 롤백된 트랜잭션 비중
Recursive Call % : 전체 Call 발생 횟수에서 Recursive Call 이 차지하는 비중
Rows per Sort : 소트 수행 시 평균 몇 건씩 처리했는지
Instance Efficiency Percentages (Target 100%) : 인스턴스 효율성
Shared Pool Statistics : Shared Pool 사용 통계
Top 5 Timed Events : AWR 리포트 구간 동안 누적 대기 시간이 가장 컸던 대기 이벤트 5개
09 ASH (Active Session History)
별도의 Third Party 모니터링 도구 없이 오라클 내에서 세션 레벨 실시간 모니터링을 가능케하는 강력한 기능으로서,
OWI의 활용성을 극대화해준다.
10 V$SQL
v$sql은 개별 SQL 커서의 수행 통계를 분석할 목적으로도 많이 활용되지만,
집중 튜닝이 필요한 대상 SQL을 선정하는데 활용할 수 있는 매우 유용한 도구다.
11 End-To-End 성능관리
시스템 전체를 관장하는 입장에서는 전체적인 시각에서 시스템을 바라볼 수 있는 도구가 필요하다.
APM (Application Performance Management) 툴을 이용하면
Web, AP, DB Zone 으로 나눠 어느 구간에서 병목이 발생하는지를 실시간으로 모니터링 할 수 있다.
12 데이터베이스 성능 고도화 정석 해법
1단계 2단계 3단계 4단계
모니터링 → 분석 → 튜닝 → 평가
자료 수집 진단
데이터베이스 성능 튜닝 3대 핵심 요소
- 라이브러리 캐시 최적화
- 데이터베이스 Call 최소화
- I/O 효율화 및 버퍼캐시 최적화
데이터 모델 및 DB 설계 : 업무적 특성을 반영한 정규화된 데이터 모델을 바탕으로 DBMS의 물리적 특성과 성능을 고려한 설계 변경,
그리고 DBMS 요소에 대한 설계를 실시하는 것
애플리케이션 튜닝 : Lock 경합, 파싱 부하, 데이터베이스 Call 발생량을 최소화하고, 인덱스 설계 조정 및 SQL 변경을 통해
I/O 효율을 높이는 등의 튜닝 요소
데이터베이스 튜닝 : 초기화 파라미터 설정, SGA/PGA 메모리 구성, Redo 파일 구성, 테이블스페이스 및 데이터파일 구성 등에 관한 전략을 조정하는 것
시스템 튜닝 : OS 레벨에서의 CPU와 메모리 구성, I/O 서브시스템 구성(파일시스템, 디스크, 스트라이핑 등), 네트워크 설정 같은 하드웨어적 튜닝
데이터베이스 성능을 좌우하는 열쇠는, 고품질 데이터 모델과 효과적으로 구현된 애플리케이션