GATHER PLAN STATISTICS

오라클 DB의 예상 실행 계획만으로는 성능 개선에 어려움을 겪을 때가 있음. 실제 실행 계획을 보고 문제점을 진단할 수 있어야 함.
gather_plan_statistics 힌트는 Oracle SQL에서 SQL 문의 실제 실행 통계를 수집할 수 있는 기능.
이 힌드를 사용하면 Oracle DB는 SQL 실행 계획의 각 단계에서 처리된 행 수, 사용된 메모리 양 및 각 단계에서 사용된 시간과 같은 자세한 통계를 수집함.

GATHER_PLAN_STATISTICS으로 수집 가능한 정보

• 각 단계에서 수행되는 레코드 수, 시간 및 I/O 통계
• 각 단계에서 사용된 실행 계획
• SQL 문의 최적 실행 계획과 실제 실행 계획의 차이를 나타내는 비교 정보

GATHER PLAN STATISTICS 사용 법

SQL 문에 다음과 같이 간단히 추가하면 됨.

  SELECT /*+ gather_plan_statistics */ column1, column2, ...
  FROM TABLE
  WHERE ...

DBMS_XPLAN.DISPLAY_CURSOR

성능 문제를 진단하고 SQL 문을 최적화하는데 유용함.
/*+ gather_plan_statistics */ SQL을 실행한 후, DBMS_XPLAN.DISPLAY_CURSOR 함수를 사용하여 실행 계획과 관련된 통계를 볼 수 있음.
DISPLAY_CURSOR 함수는 실행 계획과 통계의 자세한 보고서를 반환하므로 SQL 문의 성능을 분석하고 최적화할 부분을 식별하는 데 사용할 수 있음.

• 최적 실행 계획과 실제 실행 계획의 비교
• 각 실행 계획 단계에서 수행된 레코드 수, 시간 및 I/O 통계
• 각 실행 계획 단계에서 사용된 인덱스 및 조인 방법 등의 정보

PREDICATE INFORMATION 섹션에서는 WHERE절과 JOIN 조건에 대한 추가 정보도 제공함

  SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT => 'ALLSTATS LAST'));
  SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(null, null, 'ALLSTATS LAST'));

DISPLAY_CURSOR 매개 변수

• SQL_ID : 실행 계획과 통계를 검색하려는 SQL 문의 SQL_ID
• CURSOR_CHILD_NO : [OPTIONAL] 실행 계획을 검색하려는 SQL 문의 부모 커서 번호, 생략시 첫 번째 커서(부모 커서)가 대상이 됨
• FORMAT : 출력 형식 지원, 여러가지 출력 옵션이 있음.
  FORMAT=>'ALLSTATS LAST'를 사용하면 모든 실행계획 정보와 통계를 검색함.

SQL_ID 찾기

SQL_ID를 가져오기 위해 현재 DB에 접속한 SESSION에서 실행한 SQL 문의 히스토리를 검색하여 정보를 가져옴.

  --------------------------------------------------------------------------------
  -- SQL_ID, CHILD_NUMBER 추출
  --------------------------------------------------------------------------------
  SELECT
    SA.SQL_ID,
    S.CHILD_NUMBER,
    SA.SQL_TEXT,
    SA.MODULE,
    SA.LAST_LOAD_TIME,
    SA.LAST_ACTIVE_TIME,
    SA.PLAN_HASH_VALUE,
    SA.OPTIMIZER_COST,
    SA.FETCHES,
    SA.EXECUTIONS,
    SA.cpu_time,
    SA.ELAPSED_TIME,
    S.DISK_READS,
    S.PARSE_CALLS,
    S.BUFFER_GETS,
    S.ROWS_PROCESSED,
    SA.PARSING_USER_ID,
    SA.PARSING_SCHEMA_ID,
    SA.PARSING_SCHEMA_NAME
  FROM V$SQLAREA SA
    /* V$SQLAREA : 공유 SQL 영역 */
    INNER JOIN V$SESSION SS
      /* V$SESSION : 현재 세션에 대한 정보 */
      ON SA.PARSING_USER_ID = SS.USER#
      AND SA.PARSING_SCHEMA_ID = SS.SCHEMA#
    INNER JOIN V$SQL S
      /* V$SQL : 공유 SQL 영역 내 쿼리에 대한 정보 */
      ON SA.SQL_ID = S.SQL_ID
  WHERE SS.AUDSID = USERENV('SESSIONID')
    /* 현재 세션과 같은 SESSION ID */
    AND SS.SID = USERENV('SID')
    /* 특정 스키마 */
    AND SA.PARSING_SCHEMA_NAME = 'IDLOOK'
    /* 약 15분 이내에 실행한 쿼리만 조회 */ 
    AND SA.LAST_ACTIVE_TIME >= SYSDATE - 0.01
    /* 제외 */
    AND sa.PARSING_SCHEMA_NAME NOT IN ('SYS', 'SYSTEM')
    AND sa.SQL_TEXT NOT LIKE '%DBMS%'
    AND sa.SQL_TEXT NOT LIKE '%V$%'
    /* 실행 모듈이 운영 또는 타 시스템에서 실행한 쿼리라면 제외 */
    AND SA.MODULE NOT IN ('DBMS_SCHEDULER', 'JDBC Thin Client', 'w3wp.exe')
    /* 특정 문자열 제외*/
    AND NOT REGEXP_LIKE(UPPER(SA.SQL_TEXT), 'V\$SQL|PLAN_TABLE|DBMS_XPLAN|EXTRACTVALUE\(|XMLSEQUENCE\(|CURRENT_SCHEMA|DBA_|DBMS_UTILITY|CONSTRAINT')
  ORDER BY SA.LAST_ACTIVE_TIME DESC, SA.SQL_ID, S.CHILD_NUMBER
;

권한 문제

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALLSTATS LAST'));
쿼리 실행 시 권한 부족한 경우 DBA 계정으로 로그인하여 권한 부여 작업을 수행하거나 DBA 권한이 있는 사용자에게 권한을 부여해야 함.
DBMS_XPLAN.DISPLAY_CURSOR 함수를 사용하려면 V$SESSION, V$SQL_SESSION, V$SQL(OPTIONAL), V$SQL_PLAN_STATISTICS_ALL 권한이 필요. (해당 권한이 없는 경우 ORA-01031: insufficient privileges 오류가 발생)

작동 조건

• PLAN_STATISTICS 정보는 다음 조건 중 하나를 만족해야 함.
  • STATISTICS_LEVEL PARAMETER 값을 ALL로 변경 한 경우
    ALTER SESSION SET STATISTICS_LEVEL = ALL;
  • _ROWSOURCE_EXECUTION_STATISTICS PARAMETER 값을 TRUE로 변경한 경우
  • GATHER_PLAN_STATISTICS HINT를 사용

조회 결과 분석

• Id, Operation, Name :
  흔히 봐온 플랜 정보, 자원에 대한 접근 순서와 방법을 나타냄. 접근 순서를 변경할 수 있는 힌트 절은 ORDERED, LEADING이 있음.
  또한 접근 방법을 변경할 수 있는 힌트절은 USE_NL, USE_HASH, USE_MERGE가 있음.
• Starts :
  오퍼레이션을 수행한 횟수를 의미한다. Starts * E-Rows 의 값이 A-Rows 값과 비슷하다면, 통계정보의 예측 Row 수와 실제 실행 결과에 따른 실제 Row 수가 유사하다고 함.
  만약 값에 큰 차이가 있다면 통계정보가 실제의 정보를 제대로 반영하지 못했다고 봐야 한다고 함.
  이로 인해 오라클의 Optimizer가 잘못된 실행 계획을 수립할 수도 있음을 염두에 둬야 함.
• E-Rows (Estimated Rows) :
  통계정보에 근거한 예측 Row 수를 의미. 통계정보를 갱신할수록 값이 매번 다를 수 있으며, 대부분의 DB 운영에서는 통계정보를 수시로 갱신하지 않으므로 해당 값에 큰 의미를 둘 필요는 없음.
  하지만 E-Rows 값과 A-Rows 값이 현격하게 차이가 있다면 오라클이 잘못된 실행 계획을 세울 수도 있음을 인지해야 하며 통계정보 생성을 검토해 보아야 함.
• A-Rows (Actual Rows) :
  쿼리 실행 결과에 따른 실제 Row 수를 의미.
  A-Rows 에서 중요한 여러 정보를 추정 할 수 있음.
• A-Time (Actual Elapsed Time) :
  쿼리 실행 결과에 따른 실제 수행 시간을 의미.
  실행 시점의 여러 상황이 늘 가변적이고 또한 메모리에 올라온 Block의 수에 따라서 수행 시간이 달라지므로 해당 값에 큰 의미를 두지 않는게 좋음.
• Buffers (Logical Reads) :
  논리적인 Get Block 수를 의미.
  해당 값은 오라클 옵티마이저가 일한 총량을 의미하므로, 튜닝을 진행할 때 중요한 요소로 보임.

위의 헤더에서 튜닝 시 가장 중요하게 활용되는 부분은 Buffers, A-Rows.
Buffers 값을 통해서 Get Block의 총량을 알 수 있고, A-Rows를 통해 플랜 단계별로 실제 Row 수를 알 수 있음.

DBMS_XPLAN.DISPLAY_CURSOR 조회 결과

  SELECT * FROM TABLE (DBMS_XPLAN.DISPLAY_CURSOR('3YTNQSYC1PXJ8', NULL, 'ADVANCED ALLSTATS ALL -PROJECTION +ROWS +BYTES +PREDICATE'));

• 관련 정보 원문 : https://docs.oracle.com/database/121/ARPLS/d_xplan.htm#ARPLS70132
• 참고
  • http://jmkjb.blogspot.com/2015/06/gatherplanstatistics.html
  • https://m.blog.naver.com/itperson/220840180396
  • https://argolee.tistory.com/8
  • http://gnujava.com/board/article_view.jsp?article_no=1898&menu_cd=30&board_no=17&table_cd=EPAR06&table_no=06
  • https://argolee.tistory.com/108
  • https://chat.openai.com/

SELECT 시에는 꼭 필요한 column 만 불러오기

많은 필드를 불러 올수록 DB는 더 많은 로드를 부담하게 되기 때문에 꼭 필요한 열만 물러오도록 한다.

  -- WORST
  SELECT * FROM TABLE;
  -- BETTER
  SELECT COLUMN1, COLUMN2, COLUMN4, COLUMN8 FROM TABLE;

WHERE절에서 연산을 걸지 않는다.

연산이 들어가게 되면 TABLE FULL SCAN을 하면서 모든 값을 탐색, 계산 한 뒤 조건 충족 여부를 판단하기 때문에 좋지 않다.

  -- WORST
  SELECT COLUMN1, COLUMN4
  FROM TABLE
  WHERE FLOOR(COLUMN4) = 2;
  -- BETTER
  SELECT COLUMN1, COLUMN4
  FROM TABLE
  WHERE COLUMN4 BETWEEN 4 AND 5;

LIKE 조회시 와일드카드 % 는 가급적 뒤에만 붙이자

COLUMN6 LIKE %DF 는 TABLE FULL SCAN을 유발한다.
COLUMN5 IN ('ASDF', 'ERDF'), COLUMN5 = 'ASDF' OR COLUMN5 = 'ERDF' 같은 형태가 낫다.

SELECT DISTINCT, UNION ALL 과 같이 중복을 제거하는 연산은 자제한다

중복을 제거하는 연산은 시간이 많이 걸린다. 불가피하게 사용해야 할 경우 EXISTS, GROUP BY를 활용하는게 낫다.
DISTINCT는 원하는 컬럼에 대해서 중복을 제거하는 것이 아니라 SELECT 해온 모든 ROW에서 중복을 제거하므로 속도가 느려진다.

같은 내용의 조건이라면 GROUP BY 연산의 HAVING 보다는 WHERE 절을 사용하는 것이 좋다

쿼리 실행 순서에서 WHERE 절이 HAVING 절 보다 먼저 실행된다. 따라서 WHERE 절로 미리 데이터를 작게 만들면 GROUP BY 절에서 다뤄야 하는 데이터 크기가 작기 때문에 효율적인 연산이 가능하다.

VIEW VS MVIEW (MATERIALIZED VIEW)

뷰는 질의 할때마다 해당 쿼리를 재 실행하는 것과 같음. 속도가 느림. 다만 데이터는 LIVE 함.
MVIEW는 세팅을 어떻게 하느냐에 따라 질의 할 쿼리를 재사용하여 가져옴. 스냅샷 처럼 이전에 만들어 놓은 엠뷰 테이블에서 데이터를 가져옴.
비용이 많이 들어가고 데이터가 고정적인 경우 엠뷰를 만들어서 사용하는게 좋음. 인덱스도 생성 가능.
리프레시 타임이 많이 드는 경우 데이터가 LIVE하지 않은 문제가 있음.

CHAR VS VARCHAR

4byte 이하는 CHAR, 그 이상은 VARCHAR가 나음.

• CHAR : 고정 길이 문자열
• VARCHAR : 가변 길이 문자열

BLOB VS TEXT

많은 양의 데이터 저장, 디폴트 값 지정 안됨, 문자열 뒷부분 공백 제거 안되는 공통점이 있음.
차이점으로 BLOB은 대소문자를 구분, TEXT는 구분 안함.

PRIMARY KEY, UNIQUE KEY

PK와 Unique Key는 자동으로 인덱스가 생성됨.

쿼리 실행 순서

SUB-QUERY -> MAIN-QUERY

INNER JOIN시 테이블 배치 순서

3개 이상의 테이블을 INNER JOIN을 할 때는 크기가 가장 큰 테이블을 FROM 절에 배치하고 INNER JOIN 절에 남은 테이블을 작은 순서대로 배치하는 것이 좋다.
테이블 한두개차이는 상관 없지만 많은 테이블을 JOIN 할 경우 JOIN의 경우의 수가 생기고 OPTIMIZER가 PLANNING을 하면서 비용이 증가된다.

실행 계획 type이 index라면 한 번 더 살펴보기

EXPLAIN으로 실행 계획을 확인 했을 때 type이 index인 부분이 있다. where 절 조건문에서 사용하는 테이블의 컬럼에 인덱스가 제대로 걸려있지 않기 때문에 TABLE FULL SCAN을 한다.
해당 테이블에 index를 걸고 다시 실행 계획을 INDEX RANGE SCAN으로 변경되었고 실제 실행 속도가 1/4로 줄었다. 아래는 type의 종류로 성능이 낮은 것 부터 높은 순으로 정렬했다.

• ALL : 테이블을 처음부터 끝까지 탐색하여 데이터를 찾음 (TABLE FULL SCAN)
• INDEX : 인덱스를 처음부터 끝까지 탐색하여 데이터를 찾는 방식 (INDEX FULL SCAN)
• RANGE : 특정 범위 내에서 인덱스를 사용하여 원하는 데이터를 추출, 데이터가 방대하지 않다면 준수함. (INDEX RANGE SCAN)
• REF : 조인 할 때 Primary Key 혹은 Unique Key 가 아닌 Key로 매칭 한 경우
• EQ_REF : 조인 할 때 Primary Key로 매칭

1. Debug Mode 활용

디버그 모드로 애플리케이션을 구동시키면 아래 스크린샷과 같이 변수, 객체 등의 값을 알 수 있다. log를 찍거나 system print 등을 사용하지 앖아도 실시간으로 값을 알 수 있고 브레이크 포인트를 사용해서 트래킹 하는데 수월하니 앞으로는 IDE의 디버그 모드를 적극 활용하자. intellij 나 eclipse 모두 지원한다.

2. Comment 제거

아래 기준을 참고해서 과감하게 기존 코멘트를 제거한다.

• 위 디버그 모드 활용과 더불어 단순 값 확인용 코멘트는 제거한다.
• 의미없는 ‘//////////////’ 와 같은 구분선 종류의 코멘트는 제거한다.
• Github을 통해 트래킹, 복원이 쉽기 때문에 불필요한 주석은 과감하게 제거한다.

3. 선언, 초기화, 디폴트에 신경쓰자

코드 품질은 디테일에서 나온다. 그리고 디테일은 기본기 없이 챙기기 힘들다. 아래 스크린샷을 보자.

• 선언부 이후에 로직 중에 항상 해당 변수에 값을 할당한다면 굳이 선언하면서 초기화 할 필요가 없다.
• 의미없는 값으로 대충 초기화 하는 습관은 멀리하자. 초기화 하는 값은 일반적으로 디폴트 값이어야 하고 이 디폴트 값이 뭔지 정확히 알고서 초기화 해야한다.

4. 가독성을 높이자

// Before
// 결제상점아이디에 따른 분기
if (TRD_NO.indexOf(oldIdPart) >= 0) {
    sMallID = oldMid;
}

// After
if (TRD_NO.contains(oldIdPart)) {
    sMallID = oldMid;
}

위 코드는 파라미터로 넘긴 string의 존재 여부를 검사한 뒤 그에 따른 처리를 하기위한 것으로 보인다. 그래서 자바의 String에 있는indexOf() 메소드를 사용해 >= 0 조건으로 판별한다. 이 코드는 기능상 아무 이상 없이 작성자의 의도대로 잘 작동한다.

앞서 이야기한 것처럼 이런 코드에서 보이는 디테일을 잡으면 가독성과 품질이 상승한다.

indexOf 와 contains

자바에는 각 객체를 위한 유틸 메소드들이 많다. 그 중 contains라는 메소드로 indexOf를 대체할 수 있다. contains의 내용은 아래 스크린샷처럼 indexOf를 래핑한 메소드다. 따라서 성능이나 기능에선 차이가 없다고 봐도 된다.

그럼 왜 indexOf를 contains로 대체하려고 하는거고 언제 써야 할까?

indexOf와 contains는 용도가 다르다.

indexOf는 스트링이 시작되는 index를 int값으로 반환하고 contains는 스트링의 포함 여부를 판단해 boolean으로 반환한다. 따라서 특정 비즈니스 로직 혹은 알고리즘을 구현하기 위한 경우가 아니면 일반적인 경우에 contains의 목적으로 더 많이 쓰게 된다.

Readability

물론 indexOf를 보고 >= 0 조건을 보면 뭘 하려는지 알 수 있다. 하지만 contains라는 단어가 그 모든걸 포함하기 때문에 훨씬 더 직관적이다. contains쓰면 indexOf라는 메소드는 잘 썼는지, 뒤에 >=0 인지 > 0 인지 > -1 인지 잘못쓰진 않았는지 이런 생각 할 필요도 없다. 가독성을 정의할 때 그저 문자가 잘 읽히는 정도를 넘어서 (특히 메소드는) 이름에 맞는 목적과 기능을 신뢰할 수 있어서 불필요한 생각도 줄여주는 수준까지 갈 수 있도록 신경써야 한다.

5. Naming - 변수명은 중요하다

현재 레거시 코드의 문제점

• 
프론트 HTML tag'name을 그대로 백엔드 로직에 사용함
• 마크업과 프론트 사정에 따른 hChk, pChk 와 같은 변수명 백엔드에서 실제 용도를 구분하기 어려움
• 변수명과 주석이 일치하지 않거나 주석의 뜻을 담아내기에 부족한 변수명이 많음

실제 주문취소에서 볼 수 있는 케이스는 아래와 같다.

    // bad case
    String[] hChk = request.getParameterValues("hChk");//선택여부

    // good case
    String[] cancelSelectYn = request.getParameterValues("hChk"); // 취소선택여부

위 변수가 가리키고 있는 건 주문 취소 시 상품 별 체크박스 값이다.(아래 스크린샷) 따라서 hChk 라는 이름을 그대로 백엔드 로직에도 차용한다면 코드 전체를 트래킹 해야 하는 수고가 더해진다. 그래서 이름을 취소선택여부, 취소신청여부 등으로 하고 변수명도 이에 맞춰 수정하는 것이 좋다.

하지만 실제로 레거시 시스템은 볼륨도 크고 복잡한 상호관계를 이미 가지고 있는 상태기 때문에 변수명만 바꾸기에 위험하다. 래거시 프로젝트 소스코드도 마찬가지이며 따라서 변수명을 바꾸려 할 땐 아래 항목을 점검해서 진행한다.

• JSP와 JAVA에서 동일하게 사용하고 있는 변수가 response나 query에서 반드시 동일하도록 짜여 있는지
• 주석과 변수명이 다르다면 둘 중 어느 것이 맞는지, 둘 다 틀린지
• 변수명을 바꿨을 때 영향 범위가 백엔드 비즈니스 로직에만 해당되는지

6. 조회와 요청, 트랜젝션

1. 사용자의 화면에 보이는 내용은 화면을 그리는 시점에 유효한 정보이다(화면을 실시간으로 갱신하지 않는한)
2. 현재 프로세스는 <step 2> 와 같고 주문 취소 요청 시 JSP에 뿌려진 주문/결제 값으로 환불요청을 시작한다
3. 때문에 주문 조회 시점과 환불 요청 시점 차이가 있을 때 주문 상태 차이가 발생할 수 있고 중복 발생 가능성 또한 있는 구조다.
4. 개선 프로세스는 <step 3> 와 같이 사용자 요청을 받고 현재 주문과 환불 진행상태 등을 DB에서 조회하는 ② 부터 완료된 주문/결제/환불 정보를 DB에 업데이트하는 ⑥ 까지를 하나의 트랜잭션으로 묶는다.

대부분의 레거시 프로젝트 코드에는 화면에서 가져온 값들로 무언가 처리하는 로직이 많은데 전반적인 수정이 필요하다.

7. 3-tier Architecture

아래 쿼리를 먼저 보자.

// 이런 패턴
, X.BANK_CD                <!--은행코드-->                        
, CASE WHEN X.BANK_CD IS NOT NULL
  THEN DECODE(X.BANK_CD, '02', '산업', '03', '기업', '05', '외환', '06', '국민', '07', '수협', '11', '농협', '20', '우리', '23', 'SC제일', '27', '한국씨티', '31', '대구'
                  , '32', '부산', '34', '광주', '35', '제주', '37', '전북', '39', '경남', '45', '새마을금고', '48', '신협', '71', '우체국', '81', '하나', '88'
                  , '신한(계좌이체)', '26', '신한(가상계좌)','S0', '동양증권', 'S1', '미래에셋', 'S2', '신한금융투자', 'S3', '삼성증권', 'S6', '한국투자증권' , 'SG', '한화증권')
  ELSE '-'
  END    BANK_NM            <!--은행명-->

// 비슷한 패턴
, X.MEMO                <!--메모(가상)-->
, NVL(X.MEMO, 'X') SHW_MEMO                <!--화면용메모(가상)-->

// 비슷한 패턴
, X.RECP_PSN_TELNO            <!--수령인전화번호-->
, CASE WHEN  LENGTH(REPLACE(NVL(X.RECP_PSN_TELNO, 'X'), '-', '')) <![CDATA[ < ]]> 9
  THEN 'X'
  ELSE X.RECP_PSN_TELNO
  END   SHW_RECP_PSN_TELNO            <!--수령인전화번호-->

안좋은 케이스로 보이는 건..

• 쿼리 안에서 데이터 가공을 한다는 것이다. 게다가 화면에 보여줄 목적인 것들이 꽤 있다.
• 코드 관리나 관련 메소드가 애플리케이션에 있는게 아니라 쿼리에 들어가 있다.

대부분의 레거시 프로젝트들은 JSP, Spring, Oracle 기반이지만 잘 구분된 계층 구조를 이루고 있지 않다. 해당 계층에서 해야 할 일들이 다른 계층으로 번져간다면 결국 문제가 생길때마다 화면~데이터 모든 영역의 코드를 살펴봐야만 어느 부분이 문제인지 찾아낼 수 있다. 그래서 앞으로 신규 개발하는 화면, 기능은 이러한 강한 결합을 피하고 우리 시스템과 기술이 지향하는 3티어 계층에 맞춰 프로그래밍을 하도록 한다. 추후 가이던스를 마련하고 공유하겠지만 먼저 간단하게 요약하면 아래와 같다.

• 화면 코드에(JSP)에 비즈니스 로직 넣지 말자
• 쿼리에 비즈니스 로직, 하드코딩, 화면 표시용 글자 넣지 말자(특수한 경우 제외)
• 컨트롤러에 비즈니스 로직 넣지 말자
• 실제 필요한 것들만 파라미터로 전달하고 용도에 맞는 모델을 만들자

출처 : 같이 일 했던 선배가 컨플루언스에 올린 글