Categories
Blog

What is your hot tables in the database

The other day, an analyst came to me and asked can you tell me currently what are the hot tables, and I remembered the v$segment_statistics, this has some interesting data.
Total Reads happening:
SELECT t.owner,t.table_name,lr.value as logical_reads, pr.value as physical_reads, 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=’physical 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
AND lr.value+pr.value > 79991
ORDER BY 5 desc;
Total Writes:
select segment_name,object_type,total_physical_writes
from ( select owner||’.’||object_name as segment_name,object_type,
value as total_physical_writes
from v$segment_statistics
where statistic_name in (‘physical writes’)
order by total_physical_writes desc)
where rownum <=20;
A Jewel.