List of most used tables

I requested to gather most used tables list in production database.
After investigation I found some advice and of course metalink note.

Here is OTN link:  https://forums.oracle.com/forums/thread.jspa?threadID=511661


Use below query.

SQL> SELECT ROWNUM AS RANK, Seg_Lio.*  
  FROM ( SELECT St.Owner,  
          St.Obj#,  
          St.Object_Type,  
          St.Object_Name,  
          St.VALUE,  
          'LIO' AS Unit  
       FROM V$segment_Statistics St  
       WHERE St.Statistic_Name = 'logical reads'  
     ORDER BY St.VALUE DESC) Seg_Lio  
 WHERE owner not in ('SYS', 'SYSTEM') and object_type='TABLE' and ROWNUM <= 20  
 UNION ALL  
 SELECT ROWNUM AS RANK, Seq_Pio_r.*  
  FROM ( SELECT St.Owner,  
          St.Obj#,  
          St.Object_Type,  
          St.Object_Name,  
          St.VALUE,  
          'PIO Reads' AS Unit  
       FROM V$segment_Statistics St  
       WHERE St.Statistic_Name = 'physical reads'  
     ORDER BY St.VALUE DESC) Seq_Pio_r  
 WHERE owner not in ('SYS', 'SYSTEM') and object_type='TABLE' and ROWNUM <= 20  
 UNION ALL  
 SELECT ROWNUM AS RANK, Seq_Pio_w.*  
  FROM ( SELECT St.Owner,  
          St.Obj#,  
          St.Object_Type,  
          St.Object_Name,  
          St.VALUE,  
          'PIO Writes' AS Unit  
       FROM V$segment_Statistics St  
       WHERE St.Statistic_Name = 'physical writes'  
     ORDER BY St.VALUE DESC) Seq_Pio_w  
 WHERE owner not in ('SYS', 'SYSTEM') and object_type='TABLE' and ROWNUM <= 20  
 UNION ALL  
 SELECT ROWNUM AS RANK, Seq_Pio_w.*  
  FROM ( SELECT St.Owner,  
          St.Obj#,  
          St.Object_Type,  
          St.Object_Name,  
          St.VALUE,  
          'PIO READS Direct' AS Unit  
       FROM V$segment_Statistics St  
       WHERE St.Statistic_Name = 'physical reads direct'  
     ORDER BY St.VALUE DESC) Seq_Pio_w  
 WHERE owner not in ('SYS', 'SYSTEM') and object_type='TABLE' and ROWNUM <= 20  
 UNION ALL  
 SELECT ROWNUM AS RANK, Seq_Pio_w.*  
  FROM ( SELECT St.Owner,  
          St.Obj#,  
          St.Object_Type,  
          St.Object_Name,  
          St.VALUE,  
          'DB Block changes' AS Unit  
       FROM V$segment_Statistics St  
       WHERE St.Statistic_Name = 'db block changes'  
     ORDER BY St.VALUE DESC) Seq_Pio_w  
 WHERE owner not in ('SYS', 'SYSTEM') and object_type='TABLE' and ROWNUM <= 20  

For detail please refer to metalink note: 252597.1

Also you may select below views to get some necessary information.

--tables which modificated

 SQL> select * from dba_tab_modifications where table_owner='????' 

Starting Oracle 11g if your statistics_level`s value TYPICAL Oracle automatically will gather and monitor your tables.

--List of monitored tables

SQL> select * from dba_tables where owner='?????' and monitoring='YES' 

 SQL> select owner, monitoring, count(1)   
 from dba_tables   
 group by owner, monitoring   
 order by 1, count(1)