v$filestat视图中可以看出数据文件的读写次数和频率，从而可以对比出数据文件所在磁盘的压力大小，将压力的文件转移到压力小的磁盘来进行优化
col PHYRDS format 999999999
col PHYWRTS format 999999999
col READTIM format 999999999
col WRITETIM format 999999999
col name for a45
select name,phyrds,phywrts,readtim,writetim
from v$filestat a,v$datafile b
where a.file#=b.file#
order by readtim desc
NAME PHYRDS PHYWRTS READTIM WRITETIM
--------------------------------------------- ---------- ---------- ---------- ----------
+DATA/ocm10g/datafile/system.262.865776743 5014 1170 199 21
+DATA/ocm10g/datafile/sysaux.266.865776743 1707 3419 82 36
+DATA/ocm10g/datafile/undotbs1.258.865776743 1017 1784 49 30
+DATA/ocm10g/datafile/example.264.865776777 6 1 0 0
+DATA/ocm10g/datafile/users.257.865776743 4 1 0 0
查看临时文件的视图
select name,PHYRDS,PHYWRTS,READTIM,WRITETIM from v$tempstat a,v$tempfile b where a.file#=b.file#;
假设案例：当表空间tbs1所对应的数据文件所在磁盘/11g/app/....读写繁重，可以迁移到/home/oracle下来减轻压力。这里只是一个示例，真实环境往往不会迁移到/home/oracle目录下
1. 查看表空间tbs1的文件所在目录
select name,phyrds,phywrts,readtim,writetim
from v$filestat a,v$datafile b
where a.file#=b.file#;
NAME PHYRDS PHYWRTS READTIM WRITETIM
-------------------------------------------------- ---------- ---------- ---------- ----------
/11g/app/oracle/oradata/OCM11G/system01.dbf 5531 30 230 1
/11g/app/oracle/oradata/OCM11G/sysaux01.dbf 597 25 39 1
/11g/app/oracle/oradata/OCM11G/undotbs01.dbf 22 21 3 1
/11g/app/oracle/oradata/OCM11G/users01.dbf 1 0 0 0
/11g/app/oracle/oradata/OCM11G/example01.dbf 6 0 1 0
/11g/app/oracle/oradata/OCM11G/tbs1.dbf 0 126 0 1
/11g/app/oracle/oradata/OCM11G/tbs2.dbf 0 126 0 1
2. 离线tbs1表空间
SYS@OCM11G >alter tablespace tbs1 offline;
3. cp 数据文件到新的路径下
SYS@OCM11G >!cp /11g/app/oracle/oradata/OCM11G/tbs1.dbf /home/oracle/tbs1.dbf
SYS@OCM11G >!ls -l /home/oracle/tbs1.dbf
-rw-r-----. 1 oracle oinstall 10493952 Jan 8 10:42 /home/oracle/tbs1.dbf
4. 重命名tbs1的数据文件
SYS@OCM11G >alter tablespace tbs1 rename datafile ‘/11g/app/oracle/oradata/OCM11G/tbs1.dbf‘ to ‘/home/oracle/tbs1.dbf‘;
Tablespace altered.
5. 上线tbs1表空间
SYS@OCM11G >alter tablespace tbs1 online;
6. 确认表空间tbs1新路径
SYS@OCM11G >select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/11g/app/oracle/oradata/OCM11G/system01.dbf
/11g/app/oracle/oradata/OCM11G/sysaux01.dbf
/11g/app/oracle/oradata/OCM11G/undotbs01.dbf
/11g/app/oracle/oradata/OCM11G/users01.dbf
/11g/app/oracle/oradata/OCM11G/example01.dbf
/home/oracle/tbs1.dbf
/11g/app/oracle/oradata/OCM11G/tbs2.dbf
7. 删除迁移前文件
SYS@OCM11G >!rm -rf /11g/app/oracle/oradata/OCM11G/tbs1.dbf
参考视图：
READTIM,WRITETIM的单位：in hundredths of second（百分之一秒）
通过移动数据文件来均衡文件I/O
原文：http://blog.itpub.net/29047826/viewspace-1394361/
