Hot file writes using AWR
                                                 Last update (2008-03-04 13:33:17)
                                                                                                                   Date added (2008-03-04 13:24:44)

This script will identify any single file who's write I/O is more than 10% of the total write I/O of the database
prompt  
prompt  This script will identify any single file who's write I/O   
prompt  is more than 10% of the total write I/O of the database
prompt

set linesize 130
set pages 999
set feedback off

break on snap_time skip 2

col filename      format a40
col phywrts       format 999,999,999
col phyrds         format 999,999,999
col snap_time    format a20

select
   to_char(begin_interval_time,'yyyy-mm-dd hh24:mi') snap_time,       
   filename,
   phywrts, 
   phyrds
from dba_hist_filestatxs 
  natural join 
       dba_hist_snapshot 
where phywrts > ( select 10 / 100 * avg(value)   
                            from dba_hist_sysstat 
                            natural join dba_hist_snapshot
                            where value > 0 and stat_name like 'physical writes'
                             )		
order by to_char(begin_interval_time,'yyyy-mm-dd hh24:mi') desc, 
phywrts  desc;
Reviews
Categories
Filters
Search