문서유형ㅣ기술정보
분야ㅣ관리/환경설정
적용제품버전ㅣTibero6, Tibero7
문서번호ㅣTADTI205
개요
운영 중인 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;