Use these statements to determine the physical Disk I/O performed on Tablespaces or Data Files.

Tablespace Files Physical Reads and Writes

set pages 500 lines 110

clear col bre buffer
col tablespace_name format a15 hea 'TABLESPACE'
col file_name format a24 hea 'DATAFILE'
break on tablespace_name skip

select tablespace_name, regexp_replace(file_name,'^.*.\/.*.\/', '') file_name,
sum(phyrds) reads, sum(phywrts) writes, sum(phyrds)+sum(phywrts) total
from DBA_data_files, v$filestat
where file_id=file# group by tablespace_name, file_name
order by tablespace_name, file_name;

TABLESPACE......DATAFILE......................READS.....WRITES......TOTAL
--------------- ------------------------ ---------- ---------- ----------
EXAMPLE.........example.265.612285957............13..........8.........21
TST10...........tst10.269.612743101............5237........135.......5372
................tst10.270.612743103.............296........182........478
................tst10.271.612743105.............344........214........558
................tst10.272.612743107.............199.........87........286
................tst10.273.612743109..............73.........46........119
................tst10.274.612743111..............47.........29.........76
SYSAUX..........sysaux.262.612285947...........6807.....387189.....393996
SYSTEM..........system.260.612285927..........10292......47703......57995
UNDOTBS1........undotbs1.261.612285943...........33.....105733.....105766
USERS...........users.266.612285961.............291.......6848.......7139
................users.267.612285961.............297.......7528.......7825

Datafile Physical Reads and Writes

set pages 500 lines 110
clear col bre buffer
bre on REPORT;
comp sum lab Total min lab Minimum max lab Maximum of reads on REPORT;
comp sum lab Total min lab Minimum max lab Maximum of writes on REPORT;
comp sum lab Total min lab Minimum max lab Maximum of total on REPORT;
col datafile format a45

select name datafile, phyrds reads, phywrts writes, phyrds+phywrts total
from v$datafile a, v$filestat b where a.file# = b.file# order by total desc;

DATAFILE...........................................READS.....WRITES......TOTAL
--------------------------------------------- ---------- ---------- ----------
+DATA0/orcl/datafile/sysaux.262.612285947...........6807.....387337.....394144
+DATA0/orcl/datafile/undotbs1.261.612285943...........33.....105762.....105795
+DATA0/orcl/datafile/system.260.612285927..........10292......47710......58002
+DATA0/orcl/datafile/users.267.612285961.............297.......7528.......7825
+DATA0/orcl/datafile/users.266.612285961.............291.......6848.......7139
+DATA0/orcl/datafile/tst10.269.612743101............5237........135.......5372
+DATA0/orcl/datafile/tst10.271.612743105.............344........214........558
+DATA0/orcl/datafile/tst10.270.612743103.............296........182........478
+DATA0/orcl/datafile/tst10.272.612743107.............199.........87........286
+DATA0/orcl/datafile/tst10.273.612743109..............73.........46........119
+DATA0/orcl/datafile/tst10.274.612743111..............47.........29.........76
+DATA0/orcl/datafile/example.265.612285957............13..........8.........21
..............................................----------.----------.----------
Minimum...............................................13..........8.........21
Maximum............................................10292.....387337.....394144
Total..............................................23929.....555886.....579815

Use these statements to determine the physical Disk I/O performed on Tablespaces or Data Files.

Tablespace Files Physical Reads and Writes

set pages 500 lines 110

clear col bre buffer
col tablespace_name format a15 hea 'TABLESPACE'
col file_name format a24 hea 'DATAFILE'
break on tablespace_name skip

select tablespace_name, regexp_replace(file_name,'^.*.\/.*.\/', '') file_name,
sum(phyrds) reads, sum(phywrts) writes, sum(phyrds)+sum(phywrts) total
from DBA_data_files, v$filestat
where file_id=file# group by tablespace_name, file_name
order by tablespace_name, file_name;

TABLESPACE......DATAFILE......................READS.....WRITES......TOTAL
--------------- ------------------------ ---------- ---------- ----------
EXAMPLE.........example.265.612285957............13..........8.........21
TST10...........tst10.269.612743101............5237........135.......5372
................tst10.270.612743103.............296........182........478
................tst10.271.612743105.............344........214........558
................tst10.272.612743107.............199.........87........286
................tst10.273.612743109..............73.........46........119
................tst10.274.612743111..............47.........29.........76
SYSAUX..........sysaux.262.612285947...........6807.....387189.....393996
SYSTEM..........system.260.612285927..........10292......47703......57995
UNDOTBS1........undotbs1.261.612285943...........33.....105733.....105766
USERS...........users.266.612285961.............291.......6848.......7139
................users.267.612285961.............297.......7528.......7825

Datafile Physical Reads and Writes

set pages 500 lines 110
clear col bre buffer
bre on REPORT;
comp sum lab Total min lab Minimum max lab Maximum of reads on REPORT;
comp sum lab Total min lab Minimum max lab Maximum of writes on REPORT;
comp sum lab Total min lab Minimum max lab Maximum of total on REPORT;
col datafile format a45

select name datafile, phyrds reads, phywrts writes, phyrds+phywrts total
from v$datafile a, v$filestat b where a.file# = b.file# order by total desc;

DATAFILE...........................................READS.....WRITES......TOTAL
--------------------------------------------- ---------- ---------- ----------
+DATA0/orcl/datafile/sysaux.262.612285947...........6807.....387337.....394144
+DATA0/orcl/datafile/undotbs1.261.612285943...........33.....105762.....105795
+DATA0/orcl/datafile/system.260.612285927..........10292......47710......58002
+DATA0/orcl/datafile/users.267.612285961.............297.......7528.......7825
+DATA0/orcl/datafile/users.266.612285961.............291.......6848.......7139
+DATA0/orcl/datafile/tst10.269.612743101............5237........135.......5372
+DATA0/orcl/datafile/tst10.271.612743105.............344........214........558
+DATA0/orcl/datafile/tst10.270.612743103.............296........182........478
+DATA0/orcl/datafile/tst10.272.612743107.............199.........87........286
+DATA0/orcl/datafile/tst10.273.612743109..............73.........46........119
+DATA0/orcl/datafile/tst10.274.612743111..............47.........29.........76
+DATA0/orcl/datafile/example.265.612285957............13..........8.........21
..............................................----------.----------.----------
Minimum...............................................13..........8.........21
Maximum............................................10292.....387337.....394144
Total..............................................23929.....555886.....579815