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.
Posted in Blog