개요

운영 중인 DB에서 최근 일정 기간 동안 실행된 SQL을 분석하여 자주 사용되는 테이블을 식별하는 방법을 설명합니다.

본 방법은 DW 구축, 데이터 이관, 테이블 구조 변경 시 영향도가 높은 테이블을 사전에 파악하는 용도로 활용할 수 있으며 DB 재기동, Shared Pool 상태 등에 따라 조회 결과가 달라질 수 있습니다.

방법

최근 7일 이내 실행된 SQL 기, 사용 테이블 추출

최근 실행된 SQL을 기준으로 자주 수행된 SQL과 해당 SQL에서 사용된 테이블을 식별하는 데 목적이 있습니다.

WITH TEMP (OWNER, SQL_ID, TABLE_LIST) AS
(
 SELECT OWNER, SQL_ID,
        WM_CONCAT(OBJECT_NAME) TABLE_LIST
 FROM (
       SELECT DISTINCT BB.OWNER,
                       AA.SQL_ID,
                       BB.OBJECT_NAME
       FROM V$SQL_PLAN AA,
            DBA_OBJECTS BB
       WHERE AA.OBJECT# = BB.OBJECT_ID
         AND BB.OBJECT_TYPE IN (
             'TABLE',
             'TABLE SUBPARTITION',
             'TABLE PARTITION'
         )
         AND BB.OWNER NOT IN ('SYS', 'SYSTEM', 'LBACSYS', 'OUTLN', 'SYSGIS')
      )
 GROUP BY OWNER, SQL_ID
)
SELECT B.OWNER,
      B.TABLE_LIST,
      A.SQL_TEXT,
      A.SQL_ID,
      A.EXECUTIONS,
      A.CPU_TIME,
      A.ELAPSED_TIME,
      A.LAST_ACTIVE_TIME,
      A.FIRST_LOAD_TIME
FROM V$SQLAREA A,
    TEMP B
WHERE A.LAST_ACTIVE_TIME > TRUNC(SYSDATE - 8)
 AND A.SQL_ID = B.SQL_ID
ORDER BY A.EXECUTIONS DESC;

최근 7일 이내 실행된 SQL 기준, 테이블 별 사용 빈도(SUM(EXECUTIONS)) 집계

테이블 기준으로 SQL 실행 횟수를 합산하여, 최근 기간 동안 사용 빈도가 높은 테이블을 식별하는 데 활용할 수 있습니다.

WITH TEMP (OWNER, SQL_ID, TABLE_NAME) AS (
 SELECT DISTINCT
        BB.OWNER,
        AA.SQL_ID,
        BB.OBJECT_NAME AS TABLE_NAME
   FROM V$SQL_PLAN AA
   JOIN DBA_OBJECTS BB
     ON AA.OBJECT# = BB.OBJECT_ID
  WHERE BB.OBJECT_TYPE IN ('TABLE', 'TABLE SUBPARTITION', 'TABLE PARTITION')
    AND BB.OWNER IN ('TIBERO')   -- 특정 스키마만 대상
    -- AND BB.OWNER NOT IN ('SYS', 'SYSTEM', 'LBACSYS', 'OUTLN', 'SYSGIS') 
)
SELECT OWNER,
      TABLE_NAME,
      COUNT(*) AS SQLS,
      SUM(EXECUTIONS) AS SUM_EXECUTIONS,
      (SELECT COMMENTS
         FROM DBA_TAB_COMMENTS B0
        WHERE A0.OWNER = B0.OWNER
          AND A0.TABLE_NAME = B0.TABLE_NAME) AS COMMENTS
 FROM (
       SELECT B.OWNER,
              B.TABLE_NAME,
              A.SQL_ID,
              A.EXECUTIONS
         FROM V$SQLAREA A
         JOIN TEMP B
           ON A.SQL_ID = B.SQL_ID
        WHERE A.LAST_ACTIVE_TIME > TRUNC(SYSDATE - 8)   -- 최근 7일(범위 여유 포함)
      ) A0
GROUP BY OWNER, TABLE_NAME
ORDER BY OWNER, SUM(EXECUTIONS) DESC;