문서유형ㅣ기술정보
분야ㅣ모니터링/점검
문서번호ㅣTMOTI031
개요
DW 구축시 Legacy DB 의 주로 사용하는 테이블을 식별할 수 있는 방법을 안내합니다.
방법
v$sql_plan 에 object# 이용 시 sql 에서 사용한 테이블 식별이 가능합니다.
1. 테이블 리스트 추출
최근 7 일이내 실행된 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') )
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;
2. 테이블 사용 빈도 확인
최근 7 일 이내 사용된 쿼리의 테이블에 대한 execution count sum. 테이블 사용 빈도를 알 수 있습니다.
WITH TEMP (OWNER, SQL_ID, TABLE_NAME)
AS
(SELECT DISTINCT BB.OWNER,
AA.SQL_ID,
BB.OBJECT_NAME TABLE_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 in ('A')
)
SELECT OWNER, TABLE_NAME,count(*) sqls, SUM(EXECUTIONS), (select comments from dba_tab_comments b0 where a0.owner=b0.owner and a0.table_name=b0.table_name)
comments
FROM
(
SELECT B.OWNER,
B.TABLE_NAME,
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-1)
AND A.SQL_ID=B.SQL_ID
) a0
GROUP BY OWNER, TABLE_NAME
ORDER BY OWNER,SUM(EXECUTIONS) DESC;참고
sum(executions)이 큰 값일수록 DB 내에서 자주 사용하는 테이블 입니다.
예시) 2. 테이블 사용 빈도 확인쿼리 수행 결과
