[SQLP] SQL 분석도구

제 1절 예상 실행계획

1. ORACLE

가) Explain Plan

• SQL 수행 전 예상 실행 계획 확인
• 10g 이전에는 plan_table 생성
• utlxpls.sql 또는 utlxplp.sql 오라클 제공 스크립트 사용
• explain plan for select ~ 

나) AutoTrace

• set autotrace on : SQL을 실제 수행하고 그 결과와 함께 실행계획 및 실행통계를 출력한다.
• set autotrace on explain : SQL을 실제 수행하고 그 결과와 함께 실행계획을 출력한다.
• set autotrace on statistics : SQL을 실제 수행하고 그 결과와 함께 실행통계를 출력한다.
• set autotrace traceonly : SQL을 실제 수행하지만 그 결과는 출력하지 않고 실행계획과 통계만을 출 력한다.
• set autotrace traceonly explain : SQL을 실제 수행하지 않고 실행계획만을 출력한다.
• set autotrace traceonly statistics : SQL을 실제 수행하지만 그 결과는 출력하지 않고 실행통계만을 출력한다.

(*on : 결과 출력, statistics : 실행통계 출력은 sql 수행이 필수)

 

다) DBMS_XPLAN 패키지

• select * from taþle(dbms_xplan.display(’PLAN_TABLE’, ’SQLl’, ’BASIC’)) ;
• 3번째 인자 값에 따른 추가 항목 확인 가능

 

2. SQL SERVER

가) 예상 실행계획 출력

• SET showplan_text ON
• SET showplan_all ON : PhysicalOp(물리연산자), LogicalOp(논리연산자), 예상로우 포함

---

제 2절 SQL 트레이스

트레이스를 통해 SQL 실제 수행 과정 분석 진행

1. ORACLE

가) SQL 트레이스 수집

• alter session set sql_trace = true / false ; 현재 세션에 트레이스 설정 
• tkprof : trace(.trc) 파일을 보기 쉽게 포맷팅 , .prf 확장자로 생성

나) SQL 트레이스 (10046) 분석

 

다) DBMS_XPLAN 패키지

• dbms_xplan.display_cursor 함수로 실쟁 실행계획 확인 가능
  SQL트레이스 정보 출력 전에
  statistics_level = all OR _rowsource_execution_statistics = true OR SQL에 gather_plan_statistics 힌트 사용.
• SQL 트레이스 정보를 SGA 메모리에 남기는 방식
• sql_id, child_number 인자에 null 사용시 set serveroutput off; 해줘야함

DBMS_XPLAN	SQL 트레이스	설명
A-Rows	rows	각 단계에서 읽거나 갱신한 건수
A-Time	time	각 단계별 소요시간
Buffers	cr	캐시에서 읽은 버퍼 블록 수
Reads	pr	디스크로부터 읽은 블록 수

 

2. SQL SERVER

 

• 3가지 옵션을 on으로 설정하여 SQL트레이스 설정
  set statistics profile on : 각 쿼리의 일반 결과집합과, 처리행
  set statistics io on : 디스크 동작 양에 대한 정보
  set statistics time on : 구문 분석, 컴파일 및 실행시 소요된 시간을 밀리초 단위로 표시
• showplan_test or showplan_all 옵션이 on된 상태라면 off 시켜줘야함
• 실제 실행계획시 확인 가능한 항목

항목	설명
테이블 검색 수	실행된 검색(=읽기, 액세스) 수 (해당 테이블에 속한 인덱스를 액세스한 횟수도 포함)
논리적 읽기 수	'데이터 캐시'(=버퍼캐시) 로부터 읽어 들인 페이지 수
물리적 읽기 수	디스크로부터 읽어 들인 페이지 수
미리 읽기 수	쿼리에 의해 캐시에 넣어진 페이지 수 (쿼리 수행시 필요한 데이터 및 인덱스 페이지 예상하고, 실제 사용전에 해당 페이지를 버퍼 캐시로 가져옴)
Rows	해당 연산자에서 처리된 실제 로우 수 (출력 로우 수)
Executes	해당 연산자가 실행된 횟수

 

---

제 3절 응답 시간 분석  

1. 대기 이벤트

가. 라이브러리 캐시 부하

 - 라이브러리 캐시에서 SQL 커서를 찾고 최적화하는 과정에서 경합이 발생했을때

• latch: shared pool
• latch: library cache

- 수행중인 SQL이 참조하는 오브젝트에 다른 사용자가 DDL 문장을 수행할 때

• library cache lock
• library cache pin

 => 라이브러리 캐시 관련 경합 급증시 심각한 동시성 저하 초래

 

나. 데이터베이스 Call과 네트워크 부하

 - 애플리케이션과 네트워크 구간에서 소모되는 시간에 따른 대기 이벤트 

• SQL*Net message from client : DB 경합과는 큰 관련이 없다. 다음 명령까지 IDLE상태로 대기
• SQL*Net message to client : 메시지 전송 후 응답시간이 늦게 도착.
• SQL*Net more data to client : 메시지 전송 후 응답시간이 늦게 도착.
• SQL*Net more data from client : 클라이언트로 부터 더 받을 데이터가 있는데 지연이 발생하는 경우

다. 디스크 I/O 부하

• db file sequential read : Single Block I/O 수행시, 한 번의 I/O Call에 하나의 데이터 블록 읽음
  ㄴ 인덱스 블록을 읽을 때, 인덱스를 거쳐 테이블 블록 액세스할때 사용
• db file scattered read : Multiblock I/O, I/O call 발생시 인접한 블록들을 같이 읽어 메모리에 적재
  ㄴ Table Full Scan , Index Fast Full Scan 시 나타남
• direct path read
• direct path write
• direct path write temp
• direct path read temp
• db file parallel read

라. 버퍼 캐시 경합 
- 버퍼 캐시에서 블록 읽는 과정에 경합 발생

• latch: cache buffers chains
• latch: cache buffers lru chain
• buffer busy watis
• free buffer waits

버퍼 캐시에서 블록을 읽더라도 해당 대기 이벤트가 심하게 발생하는 순간 동시성 현저히 저하

 

마. Lock 관련 대기 이벤트

• enq: TM - contension
• enq: TX - row lock contention
• enq: TX - index contension
• enq: TX - allocate ITL entry
• enq: TX contention
• latch free

2. 응답 시간 분석 ( Response Time Analysis ) 

Response Time = Service Time + Wait Time
                          = CPU Time       + Queue Time                       

서비스 시간 ( Service time = cpu time ) : 프로세스가 정상적으로 동작하며 일을 수행한 시간

대기 시간 ( Wait Time  = Quere Time ) : 프로세스가 잠시 수행을 멈추고 대기한 시간 

 

응답 시간 분석 방법론에 기반한튜닝은 병목해소 과정이다. 

 

3. AWR ( Automatic Workload Repository ) 

응답 시간 분석 방법론을 지원하는 Oracle의 표준 도구로 동적 성능 뷰를 특정 저장소에 저장하여 분석함. 

DMA ( Direct Memory Access ) 방식으로 SGA 공유 메모리를 직접 액세스하기 때문에 빠르고 부하가 적게 정보를 수집할 수 있다. 

(Statpack 경우 SQL을 이용한 딕셔너리 조회 방식이라 AWR에 비해 느리고 부하가 있음 -> 'stats$' 뷰 이용)

 

가. AWR 기본 사용법

• SYS 계정 밑에 있는  dba_hist_* 시작하는 뷰 이용
• sql > @?/rdbms/admin/awrrpt : 표준화한 보고서 출력하는 스크립트
• 문제점 찾아 성능 이슈 해결 목적 : peak 시간대 또는 장애 발생시점 전후로 가능한 짧은 구간 선택
• 매일의 load profile이나 요일별 sql 수행과 트랜잭션, I/O 분석시 9~18시 이런식으로 기준 두고 구간 선택

나. AWR 리포트 분석

1) 부하 프로필 (Load Profile)

2) 인스턴스 효율성(Instance Efficiency)
  Execute to Parse % 항목 외엔 100% 가까운 수치 일수록 정상

 

3) 공유 풀(Shared Pool) 통계

 

4) 최상위 5개 대기 이벤트(Top 5 Timed Events)

• CPU time 비중이 높을수록 DB 상태 건강
• latch 경합은 cpu 사용률을 높이는 주 원인으로 당시 cpu사용률도 함께봐야함.
• 트랜잭션 처리 위주 시스템에선 log file sync 대기 이벤트가 자주 발생됨
• db fiel sequential read, db file scattered read 대기가 상위인건 정상적, oltp / olap 에 따라 순서만 바뀜
  cpu time 보다 높은 점유율을 갖고 CPU TIME도 높다면 I/O튜닝이 필요한 시스템이다.

5) IO 프로파일 ( IO Profile )

6) 메모리 통계 ( Memory Statistics )

7) 캐시 크기 ( Cache sizes )