How to check i/o load in database datafiles.



How to check i/o load in database datafiles.


set linesize   145
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