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（百分之一秒）

ITPub博客

通过移动数据文件来均衡文件I/O

最新文章