Skip to main content

Search

Welcome to Tibero GTS!

How to Identify Main Tables Used When Building a DW

Document Type | Technical Information

Category | Monitoring/Inspection

Document Number | TMOTI031

 

Overview

This guide explains how to identify the main tables primarily used in a Legacy DB when building a DW.
 
 

Method

You can identify tables used in SQL by using object# in v$sql_plan.
 

1. Extract Table List

Extract a list of tables from SQL executed within the last 7 days.
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. Check Table Usage Frequency

Sum of execution counts for tables in queries used within the last 7 days. This shows the frequency of table usage.
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;

Note

The larger the sum(executions), the more frequently the table is used within the DB.
Example) Result of running query 2. Check Table Usage Frequency

 

 

 

Related to