57 lines
1.9 KiB
SQL
57 lines
1.9 KiB
SQL
/**********************************************************************
|
|
* File: filestat.sql
|
|
* Type: SQL*Plus script
|
|
* Author: Tim Gorman (Evergreen Database Technologies, Inc.)
|
|
* Date: 20-Sep-99
|
|
*
|
|
* Description:
|
|
* Report against the V$FILESTAT table to detect the tablespace
|
|
* with the greatest I/O load, according the volume of reads and
|
|
* writes, weighted against the average I/O time...
|
|
*
|
|
* Because this script depends on the timing information in the
|
|
* V$FILESTAT view, please be sure to have the configuration
|
|
* TIMED_STATISTICS set to TRUE to get the full value of this
|
|
* report...
|
|
*
|
|
* Modifications:
|
|
*********************************************************************/
|
|
col ts_name format a25 truncate
|
|
col sort0 noprint
|
|
col io format a43 heading "Reads Writes|Rqsts,Blks,#Bks/Rqst"
|
|
col rds format a25 heading "Reads|Rds/Bks(#bks/Rd)"
|
|
col wrts format a25 heading "Writes|Wrts/Bks(#bks/Wrt)"
|
|
col avgiotim format 999990.0
|
|
|
|
set echo on feedback off timing off trimspool on pages 1000 lines 500
|
|
|
|
col instance new_value V_INSTANCE noprint
|
|
select lower(replace(t.instance,chr(0),'')) instance
|
|
from v$thread t,
|
|
v$parameter p
|
|
where p.name = 'thread'
|
|
and t.thread# = to_number(decode(p.value,'0','1',p.value));
|
|
spool filestat_&&V_INSTANCE
|
|
|
|
select avg(nvl(s.avgiotim,0)) * sum(nvl(s.phyrds,0) + nvl(s.phywrts,0)) sort0,
|
|
f.tablespace_name ts_name,
|
|
ltrim(to_char(sum(s.phyrds))) || ',' ||
|
|
ltrim(to_char(sum(s.phyblkrd))) || ',' ||
|
|
ltrim(to_char(sum(s.phyblkrd)/
|
|
decode(sum(s.phyrds),0,1,sum(s.phyrds)),'990.0'))
|
|
|| ' | ' ||
|
|
ltrim(to_char(sum(s.phywrts))) || ',' ||
|
|
ltrim(to_char(sum(s.phyblkwrt))) || ',' ||
|
|
ltrim(to_char(sum(s.phyblkwrt)/
|
|
decode(sum(s.phywrts),0,1,sum(s.phywrts)),'990.0')) io,
|
|
avg(s.avgiotim) avgiotim
|
|
from v$filestat s,
|
|
dba_data_files f
|
|
where f.file_id = s.file#
|
|
group by
|
|
f.tablespace_name
|
|
order by sort0 desc, ts_name
|
|
/
|
|
|
|
spool off
|