How to Find Top IO on Data Files

  • by
In my case, I was planning to move some hot data files to a new mount point with SSD disks underneath, so I have to know what data files are really hot in terms of current disk IO.

Here I take advantage of Statspack's data to retrieve valuable information of IO on data files.
# sqlplus "/ as sysdba"
...
SQL> select sp.tsname tablespace_name, sp.filename, df.bytes/1024/1024/1024 size_gb, sum(sp.phyrds) total_read, sum(sp.phywrts) total_write, sum(sp.phyrds) + sum(sp.phywrts) total_read_write, (sum(sp.phyrds) + sum(sp.phywrts)) / (df.bytes/1024/1024/1024) total_read_write_per_gb from perfstat.stats$filestatxs sp inner join sys.dba_data_files df on df.file_name = sp.filename where sp.snap_id between 2001 and 2200 group by sp.tsname, sp.filename, df.bytes/1024/1024/1024 order by 7 desc;

In the statement, I sort the top IO (read + write) traffic on data files per GB as the result, it can make me know the best candidates of data file in the database. Oppositely, I can also identify the coldest data files for moving them to those archive disks with slower transfer rate by sorting them reversely.

Before applying the above statement for your database, you have to replace SNAP_ID (i.e. 2001 to 2200 in this case) into yours.

Leave a Reply

Your email address will not be published. Required fields are marked *