Q
Manage Learn to apply best practices and optimize your operations.

How to use V$SEGMENT_STATISTICS to find the most accessed Oracle table

Learn how to use the V$SEGMENT_STATISTICS view to find the most accessed table in an Oracle database with this query from Oracle expert Brian Peasland.

Is there a way to find the database table that is accessed the most?
You can query the V$SEGMENT_STATISTICS view for this information. Look for tables that have a high number of physical reads and logical reads. A query similar to the following might provide what you are looking for:

SELECT t.owner,t.table_name,lr.value+pr.value AS total_reads
FROM (SELECT owner,object_name,value FROM v$segment_statistics
WHERE statistic_name='logical reads') lr,
SELECT owner,object_name,value FROM v$segment_statistics
WHERE statistic_name='logical reads') pr,
dba_tables t
WHERE lr.owner=pr.owner AND lr.object_name=pr.object_name
  AND lr.owner=t.owner AND lr.object_name=t.table_name
ORDER BY 3;

This was last published in October 2009

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close