Skip to main content

Search

Welcome to Tibero GTS!

How to Search for Frequently Used Tables

Document Type | Technical Information

Category | Monitoring/Inspection

Document Number | TMOTI035

 

Overview

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

Method

You can identify tables used in SQL by using object# in v$sql_plan.
Note
Tables that are frequently used can be considered as such if the sum(executions) value is large within the DB.
 
1. Extract table list 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. Sum of execution counts for tables used in queries within the last 7 days. Check table usage frequency

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;

 

 

 

Related to