When we’re making changes to a database, it’s important to know which tables and indexes are in use. If we don’t need an index, maybe we can drop it. If nobody is using a table, do we still need it?
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
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; |
|
1 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
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;sqs |
|
1 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
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;s |
|
1 |
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.
8 Comments. Leave new
use GV$SEGMENT_STATISTICS if you are on cluster database.
Great point – we can use the
GV$version of these views if the database is RAC.To be certain of table usage, you can set up auditing: http://oracle-base.com/articles/10g/auditing-10gr2.php
Audit data in AUD$ (the audit data table) can soon mount-up, so, to make querying audit data faster, i recommend indexing AUD$: http://oracle-dba-scripts.blogspot.co.uk/2012/07/indexing-aud_20.html
Hello
Thank you very much for sharing all this valuable support.
In relation WITH the information GET from V$SEGMENT_STATISTICS. Is there any way how can I join this view with ANY OTHER sys table or view TO GET THE LAST DATE OF LOGICAL OR PHYSICAL READING ON TABLES?
I need to work on a research about how to get this information from tables not in auditing.
Thanks so much in advance
A very quick google search turns up this article from Tom Kyte When was the last time, a table was accessed?.
Thanks for your response. I have already gone across what Tom suggest. It is mainly related with the auditing stuff. In my case I don’t have the auditing set up for tables that I need to check some reading on them. In the case of the second suggestion about dba_hist_seg_stat and Dba_objects the proble is that you only get the last time of DDL, but not about DML.
Gondi
hi , i want to monitor only few of tables(Which are not in sys,sysaux,system)on which users are doing inserts and updates presently.
Sivakumar – for questions, hit a Q&A site like https://dba.stackexchange.com.