tables.MostAccessed.sql

<- Scripts

-- tables.MostAccessed.sql
-- Purpose: List of tables in database by most accessed.
-- LIO (Logical I/O)  refers to reading and writing from/to memory (ex: SGA Buffer Cache). 
-- PIO (Physical I/O) refers to reading and writing from/to disk. 

COL RANK  FORMAT 999
COL OWNER FORMAT a15
COL OBJ# FORMAT 999999
COL OBJECT_TYPE FORMAT a15
COL OBJECT_NAME FORMAT a35
COL VALUE FORMAT 999999999
COL UNIT  FORMAT a25
COL xxx FORMAT xxx
COL xxx FORMAT xxx
COL xxx FORMAT xxx

-- Proccess
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;