----------------------------------
-- SCRIPT tstop_blkrd.sql
-- INICO DO SCRIPT
-- ALEXANDRE PIRES 14/05/2012
----------------------------------
rem
rem ARQUIVO
rem tstop_blkrd.sql
rem
rem FINALIDADE
rem Lista das tablespaces mais acessadas em blocos lidos
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 readpct desc )
where rownum < 11
/
prompt
clear breaks compute
set feedback 6
----------------------------------
Anúncios
