透過移動資料檔案來均衡檔案I/O

snowdba發表於2015-01-08
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部落格 ” ，連結：http://blog.itpub.net/29047826/viewspace-1394361/，如需轉載，請註明出處，否則將追究法律責任。

相關文章