Frequently Used Tables Query

Frequently Used Tables Query

We can find out which tables and indexes are in use through two different mechanisms.

V$SEGMENT_STATISTICS

Of course Oracle tracks the tables in use. In this case, we can see this information in the view V$SEGMENT_STATISTICS. This view contains a lot of information about any all of the different segments in your Oracle database. A [segment][seg] is the set of extents (data blocks) allocated to a single database object. The V$SEGMENT_STATISTICS view needs to be pivoted to get the information we need – it contains one row for reads, one for writes, etc.

This basic query will let us see the volume of activity on different segments in the database:

SELECT  vss.owner,
        vss.object_name,
        vss.subobject_name,
        vss.object_type ,
        vss.tablespace_name ,
        SUM(CASE statistic_name WHEN 'logical reads' THEN value ELSE 0 END
            + CASE statistic_name WHEN 'physical reads' THEN value ELSE 0 END) AS reads ,
        SUM(CASE statistic_name WHEN 'logical reads' THEN value ELSE 0 END) AS logical_reads ,
        SUM(CASE statistic_name WHEN 'physical reads' THEN value ELSE 0 END) AS physical_reads ,
        SUM(CASE statistic_name WHEN 'segment scans' THEN value ELSE 0 END) AS segment_scans ,
        SUM(CASE statistic_name WHEN 'physical writes' THEN value ELSE 0 END) AS writes
FROM    v$segment_statistics vss
WHERE   vss.owner NOT IN ('SYS', 'SYSTEM')
GROUP BY vss.owner,
        vss.object_name ,
        vss.object_type ,
        vss.subobject_name ,
        vss.tablespace_name
ORDER BY reads DESC;

This approach will show us information about reads, writes, and segment scans (full table scans) since the dynamic view was last cleared. Typically, this is going to show us information since the database was last started.

The downside of V$SEGMENT_STATISTICS is that it’s across the lifetime of that process. If you happen to have great database up time, a table or index could show up in V$SEGMENT_STATISTICS even though it hasn’t been used in weeks or months.

QUERY WAITS

In order to determine which queries are hitting which tables, we can start sampling the current waits in the system. V$SESSION_WAIT contains a bunch of information including the cryptic p1, p2, and p3 columns. For disk related waits, we only need the p1 and p2 waits. These refer to the file and block number that are a part of the wait. To see what’s going on, we can just do something like:

SELECT  vs.username ,
        vsw.wait_class,
        vsw.EVENT AS wait_type ,
        vsw.WAIT_TIME_MICRO / 1000 AS wait_time_ms ,
        vsw.TIME_REMAINING_MICRO / 1000 AS time_remaining_ms ,
        vsw.STATE ,
        de.SEGMENT_NAME ,
        de.SEGMENT_TYPE,
        de.OWNER ,
        de.TABLESPACE_NAME
FROM    V$SESSION_WAIT vsw
        JOIN V$SESSION vs ON vsw.SID = vs.SID
        LEFT JOIN DBA_EXTENTS de ON vsw.p1 = de.file_id
                                    AND vsw.p2 BETWEEN de.BLOCK_ID AND (de.BLOCK_ID + de.BLOCKS)
WHERE   vsw.wait_class <> 'Idle'
        AND vs.username IS NOT NULL
ORDER BY wait_time_ms DESC;


This query will show us:

Who’s running a query
What their current wait is
If it’s a disk wait, which tablespace, object, and object type is being waited on.
The problem with this approach, though, is that it will only catch the queries that are running at the moment you sample. With enough samples, this can be effective, but sampling the system is going to put a small load on it.

ACTIVE SESSION HISTORY

If you’re licensed for the Oracle Diagnostic Pack, you’ll have access to the Active Session History. If you aren’t licensed for the Diagnostic Pack, keep reading, but don’t run these queries on any of your Oracle systems – your Oracle auditors will love you, but your accountants won’t.

Active Session History samples data over time and lets us know which queries have been running inside a given time window. The upside of this is that we can dig into the data collected and figure out if the tables we’re interested in have been used inside a given time window.


SELECT  du.username,
        s.sql_text,
        MAX(ash.sample_time) AS last_access ,
        sp.object_owner ,
        sp.object_name ,
        sp.object_alias as aliased_as ,
        sp.object_type ,
        COUNT(*) AS access_count
FROM    v$active_session_history ash
        JOIN v$sql s ON ash.force_matching_signature = s.force_matching_signature
        LEFT JOIN v$sql_plan sp ON s.sql_id = sp.sql_id
        JOIN DBA_USERS du ON ash.user_id = du.USER_ID
WHERE   ash.session_type = 'FOREGROUND'
        AND ash.SQL_ID IS NOT NULL
        AND sp.object_name IS NOT NULL
        AND ash.user_id <> 0
GROUP BY du.username,
        s.sql_text,
        sp.object_owner,
        sp.object_name,
        sp.object_alias,
        sp.object_type
ORDER BY 3 DESC;

You can use this query as a basis to help you isolate who is using which tables and how frequently the queries are being run. If you’re looking for one particular table or index, you can start filtering through the query plan operations to find the object in question.

THREE WAYS TO FIND THE TRUTH

So, there you have it – three ways to find some version of the truth. Looking at V$SEGMENT_STATISTICS will let you understand the way tables have been used historically, but it won’t help you understand if a table has been used recently. V$SESSION_WAIT can be sampled to get a better idea of which tables and indexes are being used and who is using them. And if you need an accurate view, and you’re licensed for the Diagnostic Pack, you can use V$ACTIVE_SESSION_HISTORY to review how frequently a table or index has been used over time.