tstop_blkwrt.sql

----------------------------------
-- SCRIPT  tstop_blkwrt.sql
-- INICO DO SCRIPT 
-- ALEXANDRE PIRES 14/05/2012
----------------------------------
rem
rem ARQUIVO
rem    tstop_blkwrt.sql
rem
rem FINALIDADE
rem    Lista das tablespaces mais acessadas em blocos escritos
rem
rem MODIFICACOES
rem 14/05/2012 ALEXANDRE PIRES
rem

set feedback off

break on report
compute sum of WRITEPCT on report
compute sum of READPCT on report
compute sum of IOPCT on report

select * from
  (  SELECT TABLESPACE_NAME
     , sum(PHYRDS) as PHYRDS
     , sum(PHYWRTS) as PHYWRTS
     , sum(PHYBLKRD) as PHYBLKRD
     , sum(PHYBLKWRT) as PHYBLKWRT
     , sum(READPCT) as READPCT
     , sum(WRITEPCT) as WRITEPCT
     , sum(IOPCT) as IOPCT
    FROM
     ( SELECT
           decode (dt.extent_management, 'LOCAL', 'L|', 'D|')
       ||  ts.name
       as  tablespace_name
       ,   fs.PHYRDS
       ,   fs.PHYWRTS
       ,   fs.PHYBLKRD
       ,   fs.PHYBLKWRT
       ,   100 * ratio_to_report(fs.PHYBLKRD) over () as readpct
       ,   100 * ratio_to_report(fs.PHYBLKWRT) over () as writepct
       ,   100 * ratio_to_report(fs.PHYBLKRD+fs.PHYBLKWRT) over () as iopct
       FROM V$FILESTAT fs, V$DATAFILE df, v$tablespace ts, dba_tablespaces dt
       WHERE fs.file# = df.file#
         AND df.ts# = ts.ts#
         AND dt.tablespace_name = ts.name )
    GROUP BY tablespace_name
    ORDER BY writepct desc )
where rownum < 11
/
prompt
clear breaks compute
set feedback 6
----------------------------------
-- FIM DO SCRIPT  --