By
Published: 28 Oct 2009
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;
Dig Deeper on Oracle database design and architecture
Oracle expert Brian Peasland answers one reader's question about common pitfalls when connecting Oracle to outside programs.
Continue Reading
One reader asks expert Brian Peasland a question about datafile sizes with the Oracle RMAN duplicate 10g command.
Continue Reading
Managing parent table-child table relations in Oracle SQL environments is key to efficient programming.
Continue Reading