Overview

This explains a method to identify frequently used tables by analyzing SQL executed over a recent period in an operating database.

This method can be used to pre-identify tables with high impact during DW construction, data migration, or table structure changes. The query results may vary depending on DB restart, Shared Pool status, etc.

Method

Extract frequently used tables based on SQL executed within the last 7 days

The purpose is to identify frequently executed SQL and the tables used in those SQL statements based on recently executed 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;

Aggregate usage frequency (SUM(EXECUTIONS)) per table based on SQL executed within the last 7 days

By summing the number of SQL executions per table, this can be used to identify tables with high usage frequency during the recent period.

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')   -- Target only specific schema
    -- 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)   -- Last 7 days (including buffer range)
      ) A0
GROUP BY OWNER, TABLE_NAME
ORDER BY OWNER, SUM(EXECUTIONS) DESC;