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

* 쿼리 성능 측정

☞ DB에서 프로시저에 있는 SQL 실행 계획 분석하여 시간 오래 걸리는 부분 수정하여 최소 시간으로 원하는 결과 얻도록 프로시저 수정하는 활동

☞ RDB, 특히 Oracle 쿼리 성능 측정 방법에는 TKPROF 명령어, EXPLAIN PLAN 이 있음

 

    1. SQL 처리 흐름

        ▶ SQL 처리 단계

            ▷ 구문 분석 (Parsing)

                - 사용자가 요청한 SQL 문이 실행 계획 있는지 확인

                - 실행 계획 있음 : 과거에 사용된 문장이기에 구문 분석할 필요 없이 바로 실행

                - 실행 계획 없음 : 옵티마이저는 인덱스 등 분석해 가장 빠르게 검색해 줄 수 있는 실행 계획 찾음

            ▷ 실행 (Execution)

                - 실행 계획 따라 메모리 영역의 DB 버퍼 캐시 영역에 해당 데이터가 있는지 확인

                - 버퍼 캐시 영역에 있음 : 그대로 실행

                - 존재하지 않음 : DB에서 읽어서 버퍼 캐시 영역에 저장 후 SQL 실행

            ▷ 인출 (Fetch)

                - 서버 프로세스는 데이터 버퍼 캐시 영역에서 관련 테이블 데이터 읽어서 클라이언트로 보내줌

                - SELECT문 경우에만 실행되는 단계

 

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

        - 개발자는 SQL 특성을 충분히 이해하고 SQL 문을 적절히 구사할 수 있는 기본적인 능력 갖춰야 함

        - 개발자는 SQL 작성 시 옵티마이저의 일련 행위를 이해하고 있어야 함

        - 구문 분석 단계에서 옵티마이저 실행 계획에 따라서 실행 속도 차이는 크게 날 수 있음

        - 옵티마이저 실행 계획 비정상적이라면 개발자는 Hint 같은 조건 부여하여 실행 계획 수정 가능

 

    3. 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

                - 각 단계에서 읽거나 갱신한 처리 건수

 

        ▶ TKPPROF를 활용한 Trace 유형

            ▷ Instance level 추적

                - 모든 SQL 수행에 대한 Trace 파일 생성하여 부하 많음

                - 모든 AQL을 Trace하는 경우는 거의 없음

            ▷ SEssion level 추적

                - 특정 프로세스 별로 추적 파일 생성

                - 일반적으로 활용되는 유형

 

        ▶ Trace 관련 파라미터 설정

            ▷ 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 파일 생성될 디렉토리 (생성된 디렉토리 밑에 확장자가 .trc.파일로 생성됨)

 

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

            - 시스템 사용자 권한으로 DB connect : sqlplus "/ as sysdba"

            - SQL Mode에서 show parameter 또는 show parameter parameter_name

 

    4. EXPLAIN PLAN 기반 쿼리 성능 측정

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

 

        ▶ 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 수

 

 

    5. SQL 성능 개선 순서

        ▶ SQL 성능 개선 순서

            ① 문제 있는 SQL 식별

                - APP 성능 관리하거나 모니터링 하기 위한 툴인 APM(Application Performance Management) 등을 활용

                - Oracle 경우, TKPROF 또는 SQL_Trace 같은 유틸리티 사용

            ② 옵티마이저 통계 확인

                - 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 veriable)

            - 프로시저에서 선언은 되었지만 본문에서는 전혀 사용되지 않는 변수

        ▷ 사용되지 않는 서브쿼리 (unused subquery item)

            - 컬럼이 선언은 되었지만 외부 쿼리 (outer query)에서 참조 되지 않음

        ▷ Null 값과 비교 (Null comperison) 

            - Null 값과 비교하는 프로시저 소스 있는 경우

        ▷ 과거 데이터 타입 사용 (Deprecated type)

            - 데이터 타입이 바뀌었지만 과거 타입 그대로 쓰는 소스가 있는 경우

 

    ▶ SQL 코드 인스펙션 절차

        1. 계획

            - 문제되는 SQL 코드 선별, 문제점 인식 및 인스펙션 참여자 선정

        2. 개관 (Overview)

            - SQL 코드 문제점 공유(실행 시간, 자원 사용량 등), 계획 및 방법 공유

        3. 준비

            - 각자 SQL 소스 코드 분석, 문제점 확인

        4. 검사

            - 공식적인 SQL 인스펙션 수행, 문제점 토의

        5. 재작업

            - SQL 소스 코드 수정 및 실행 시간 재 측정

        6. 추적(Follow-up)

            - 개선 효과 분석