How to check i/o load in database datafiles.
set pagesize 1000
set trimout on
set trimspool on
Set Feedback off
set timing off
set verify off
Column Disque Heading 'Disque' Format A25
Column Total_ES Heading 'Total_ES' Format 99999999999
Column Charge Heading 'Charge' Format 9999.00
Column Nom_Fichier Heading 'Nom Fichier' Format A25
Column Nom_Tablespace Heading 'Nom Tablespace' Format A18
column Alloc Heading 'Alloc|(Mb)' Format 9999,999
column avgwait Heading 'Avg I/O|Time' Format 9999,999
Break on Disque Skip 2
Compute Sum of Charge on Disque
Compute Sum of Alloc on Disque
Select
Substr(DF.Name, 1, Instr(DF.Name,'/',-1,1) - 1) Disque
, T.Tablespace_Name Nom_Tablespace
, T.bytes/1048576 Alloc
, Substr(DF.Name, Instr(DF.Name,'/',-1,1)) Nom_Fichier
, FS.Phyblkrd + FS.Phyblkwrt Total_ES
, 100*(FS.Phyblkrd + FS.Phyblkwrt)/MaxIO Charge
, S_avgiotim avgwait
From
V$Filestat FS
, V$Datafile DF
, (Select Max(Phyblkrd + Phyblkwrt) MaxIO
From V$Filestat)
, (Select Sum(avgiotim) S_Avgiotim , file# S_file
From V$Filestat
Group By file#
)
, Dba_Data_Files T
Where
DF.File# = FS.File#
And
DF.File# = T.File_Id
And
DF.File# = S_File
Order By
Disque
, Charge Desc
;
Comments