[DB] Oracle 쿼리 튜닝을 위한 쿼리 분석 방법

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/