[정보처리기사] Part02-03. 데이터조작 프로시저 최적화

 

 

목차

 

  쿼리 성능 측정

  소스코드 인스펙션

 

 

쿼리 성능 측정

 

정의

• DB에서 프로시저에 있는 SQL 실행 계획을 분석하여 시간이 오래 걸리는 부분을 수정하여 최소의 시간으로 원하는 결과를 얻도록 프로시저를 수정하는 활동
• RDB, 특히 오라클 쿼리 성능을 측정하는 방법에는 크게 TKPROF 명령어 EXPLAIN PLAN 두 가지 방법이 있음

 

RDB에서의 SQL 처리 이해

SQL 처리 흐름

• 구문 분석 (Parsing)
  • 사용자가 요청한 SQL 문이 실행 계획이 있는지 확인
  • 실행 계획이 있다면 과거에 사용된 문장이기 때문에 구문 분석을 할 필요 없이 바로 실행함
  • 실행 계획이 없다면 옵티마이저는 인덱스, 데이터 양 등을 분석하여 가장 빠르게 검색해 줄 수 있는 실행 계획을 찾음

 

• 실행 (Execution)
  • 실행 계획에 따라서 메모리 영역의 DB 버퍼 캐시 영역에 해당 데이터가 있는지 확인
  • 만일, 버퍼 캐시 영역에 있다면 그대로 실행하고, 존재하지 않는다면 DB에서 읽어서 버퍼 캐시 영역에 저장 후 SQL 실행

 

• 인출 (Fetch)
  • 서버 프로세스는 데이터 버퍼 캐시 영역에서 관련 테이블 데이터를 읽어서 클라이언트로 보내줌
  • SELECT 문의 경우에만 실행되는 단계

 

쿼리 성능 최적화를 위한 고려사항

• 개발자는 SQL 특성을 충분히 이해하고 SQL 문을 적절히 구사할 수 있는 기본적인 능력을 갖추어야 함
• 개발자는 SQL 작성 시 옵티마이저 일련의 행위를 이해하고 있어야 함
• 구문분석 단계에서 옵티마이저의 실행 계획에 따라서 실행 속도의 차이는 크게 날 수 있음
• 옵티마이저의 실행 계획이 비정상적이라면 개발자는 Hint 같은 조건을 부여하여 실행 계획 수정 가능

 

TKPROF 기반 쿼리 성능 측정

TKPROF (Trace Kernel PROFile) 정의

• Oracle DBMS에서 실행되는 SQL 문장에서 분석 정보를 제공하여 개발자가 특정 SQL 문장을 어떻게 사용해야 할 것인지에 대한 가이드 라인을 제공하는 도구

 

TKPROF 결과로 파악할 수 있는 분석 정보 내용

• Call : 커서 상태에 따라 Parse, Execute, Fetch 3개의 Call로 나누어 각각의 통계 정보를 보여줌
  • Parse : 커서를 파싱ㅇ하고 실행 계획을 생성하는 통계
  • Execute : 커서의 실행 (insert/update/delete) 단계에 대한 통계
  • Fetch : 레코드를 실제로 Fetch(select)하는 통계
  • Count : Parse, Execute, Fetch 각 단계가 수행된 횟수
  • CPU : 현재 커서가 각 단계에서 사용한 CPU 시간 (초)
  • Elapsed : 현재 커서가 각 단계의 시작에서 종료까지 총 경과 시간 (초)
  • Disk : 물리적인 디스크로부터 읽은 블록 수
  • Rows : 각 단계에서 읽거나 갱신한 처리 건수

 

TKPROF 활용한 Trace 유형

• Instance level 추적
  • 모든 SQL 수행에 대한 Trace 파일을 생성하여 부하가 많음
• Session level 추적
  • 특정 프로세스 별로 추적 파일을 생성

 

• 모든 SQL을 Trace하는 경우는 거의 없고 Session level을 일반적으로 활용함
• Trace Enable 하는 것은 DB 부하가 수반되므로 필요한 경우를 제외하고는 Disable하는 것이 좋음

 

Trace 관련 파라미터 설정

• TKPROF를 통하여 트레이스 설정을 ON하면 user_dump_dest 파라미터로 지정된 서버 디렉토리 밑에 확장자가 .trc파일로 생성됨
  • sql_trace (default=false)
    • 자신의 세션에 트레이스를 설정
    • SQL > alter session set sql_trace = true;
  • timed_statistics (default=false)
    • CPU시간, 실행 시간 등 시간에 관련된 정보 표시
  • max_dump_file_size (default=500)
    • Trace 파일의 최대 크기 (단위 : OS 블럭 수)
  • user_dump_dest
    • Trace 파일이 생성될 디렉토리

 

Trace 관련 파라미터 설정 확인 방법

• 시스템 사용자 권한으로 DB connect : sqlplus "/ as sysdba"
• SQL Mode에서 show parameter 또는 show parameter parameter_name

 

EXPLAIN PLAN 기반 쿼리 성능 측정

EXPLAIN PLAN 정의

• 사용자들이 SQL 문의 액세스 경로를 확인하여 성능 개선을 할 수 있도록 SQL 문을 분석하고 해석하여 실행 계획을 수립하고, 관련 테이블 (plan_table)에 저장하도록 지원해 주는 도구

 

EXPLAIN PLAN 준비

• 해당 사용자로 DB 접속하여 PLAN Table 생성
• SQL > @$ORACLE_HOME/rdbms/admin/utlxplan
• PLUSTRACE Role 생성
• SQL > Sqlplus "/ as sysdba" --Sys user로 DB 접속
• SQL > @ORACLE_HOME/sqlplus/admin/plustrace --PLUSTRACE role 생성
• SQL > grant plustrace to scott; -- 사용자에게 PLUSTRACE 권한 부여

 

EXPLAIN PLAN 실행

• 해당 사용자로 DB 접속 후 Autotrace mode를 on으로 전환
  • Autotrace 명령어를 사용하기 전 반드시 PLAN_TABLE이 생성되어야 하고, PLUSTRACE 권한을 가지고 있어야 함
  • Autotrace Mode 전환
    • SQL > set autotrace on [off, traceonly]
    • On : SQL 문의 실행 결과와 실행 계획과 통계 정보를 보여주는 옵션
    • Off : AUTOTRACE 해지하는 옵션
    • TRACEONLY : 실행 계획과 통계 정보만을 제공하는 옵션
  • Mode를 off로 전환할 때까지 plan_table에 write 됨
  • Autotrace Mode 확인
    • SQL > show autotrace

 

• PLAN_TABLE 확인
  • SQL > SELECT a.ename, a.sal, b.dname FROM emp a, dept b WHERE a.deptno = b.deptno;
  • 선택된 Row 확인
  • Plan 내용 확인

 

• PLAN_TABLE 결과 항목 의미
  • Recursive call : 재귀 호출 횟수
  • DB block gets : 현재 블록이 요구된 횟수
  • Consistent gets : 한 블록에 대해 요구된 Consistent Read 횟수
  • Physical reads : 디스크로부터 읽어 들인 데이터 블록의 총 개수
  • Redo size : Redo 로그가 만들어진 크기
  • Byte sent via SQL*Net to client : 클라이언트로 보내진 바이트 수
  • Byte received via SQL*Net from client : 클라이언트로부터 받은 바이트 수
  • Sort(Memory) : 메모리에서 일어난 소트 수
  • Sort(Disk) : 디스크에서 일어난 소트 수
  • Row processed : 연산을 하는 동안 처리한 Row 수

 

 

SQL 성능 개선 순서

• 문제 있는 SQL 식별
  • 애플리케이션의 성능을 관리하거나 모니터링 하기 위한 툴인 APM (Application Performance Management) 등을 활용
  • Oracle의 경우, TKPROF 또는 SQL_Trace와 같은 유틸리티를 사용함

 

• 옵티마이저 (Optimizer) 통계 확인
  
  • Analyze Object_type Object_name Operation STATISTICS;
  • 예시 : ANALYZE TABLE emp COMPUTE STATISTICS;

 

• SQL문 재구성
  • 가능한 한 WHERE절을 이용하여 범위가 아닌 특정 값 지정으로 범위를 줄임으로써 처리 속도 빠르게 함
  • WHERE 절의 COLUMN에 연산자를 사용하여 COLUMN 변경이 발생하면 인덱스를 활용하지 못하게 됨을 이해하여 COLUMN 변경 연산자를 쓰지 않음

 

• 인덱스 재구성
  • 실행 계획을 검토하여 기존 인덱스 열 순서를 변경하거나 추가 할 수 있도록 함
  • 인덱스 추가 시 정상적으로 처리되고 있던 다른 SQL에 심각한 영향을 줄 수 있으므로 주요 SQL 질의 결과를 함께 검토함

 

• 실행 계획 유지관리
  • DB 버전 업그레이드, 데이터 전환 등 시스템 환경의 변경 사항 발생 시에도 실행 계획이 유지되고 있는지 모니터링, 관리

 

소스코드 인스펙션 (Source Code Inspection)

 

정의

• DB 성능 향상을 위해 프로시저 코드를 보면서 성능 문제점을 개선해 나가는 행동

 

SQL 코드 인스펙션 대상

• 사용되지 않는 변수 (unused variable)
  • 프로시저에서 선언은 되었지만 본문에서는 전혀 사용되지 않는 변수

 

• 사용되지 않는 서브쿼리 (unused subquery item)
  • 컬럼이 선언은 되었지만 외부 쿼리(outer query)에서 참조가 되지 않음

 

• Null 값과 비교 (Null comparison)
  • Null 값과 비교하는 프로시저 소스가 있는 경우

 

• 과거의 데이터 타입을 사용 (Deprecated type)
  • 데이터 타입이 바뀌었지만 과거의 타입을 그대로 쓰는 소스가 있는 경우

 

SQL 코드 인스펙션 절차

 

1. 계획 : 문제되는 SQL 코드 선별, 문제점 인식 및 인스펙션 참여자 선정

2. 개관 (Overview) : SQL 코드 문제점 공유 (실행 시간, 자원 사용량 등), 계획 및 방법 공유

3. 준비 : 각자 SQL 소스코드 분석, 문제점 확인

4. 검사 : 공식적인 SQL 인스펙션 수행, 문제점 토의

5. 재작업 : SQL 소스코드 수정 및 실행 시간 재 측정

6. 추적 (Follow-up) : 개선 효과 분석