请帮忙看看我的数据文件是否存在I/O冲突？

显示全部楼层 · 2005-10-30 17:05:33

SQL> select substr(a.file#,1,2) "file#",a.status,a.bytes,b.phyrds,b.phywrts,substr(a.name,1,50) "filename" from v$datafile a,v$filestat b where a.file#=b.file#;
file# STATUS BYTES PHYRDSPHYWRTS filename
----- ------- ---------- ---------- ---------- --------------------------------------------------------------------------------
1 SYSTEM 2831155207716589949 /oracle/oracle/oradata/bais/system01.dbf
2 ONLINE104857602419324191 /oracle/oracle/oradata/bais/tools01.dbf
3 ONLINE 16252928024316 684411 /oracle/oracle/oradata/bais/rbs01.dbf
4 ONLINE209715202419324191 /oracle/oracle/oradata/bais/temp01.dbf
5 ONLINE209715202419324191 /oracle/oracle/oradata/bais/users01.dbf
6 ONLINE209715202419324191 /oracle/oracle/oradata/bais/indx01.dbf
7 ONLINE209715202419324191 /oracle/oracle/oradata/bais/drsys01.dbf
8 SYSTEM 2097152009435876692 /oracle/oracle/dbs/system02.dbf
9 ONLINE 1048576002419324191 /oracle/oracle/dbs/users02.dbf
10ONLINE 2097152002419324191 /oracle/oracle/dbs/temp02.dbf
11ONLINE 2097152002419324191 /oracle/oracle/dbs/indx02.dbf
12ONLINE 2097152002419324191 /oracle/oracle/dbs/tools02.dbf
13ONLINE4294967296119664323 602026 /oracle/oracle/dbs/baisdata.001
14ONLINE4294967296 315221 683234 /oracle/oracle/dbs/baisindex.001
15ONLINE107374182477151566742219 /oracle/oracle/dbs/baistemp.001
16ONLINE10737418242419324191 /oracle/oracle/dbs/baisrbs.001
17ONLINE42949672962419324191 /oracle/oracle/dbs/baissnap.001
18ONLINE107374182424314 121931 /oracle/oracle/dbs/baisroll.001
18 rows selected
SQL>
通过以上结果，如何看出我的数据文件是否存在I/O冲突？判断有冲突的依据是什么？请指教谢谢了：）

千问 · 2005-10-30 17:05:33

Astitle

最初由小新发布
[B]SQL> select substr(a.file#,1,2) "file#",a.status,a.bytes,b.phyrds,b.phywrts,substr(a.name,1,50) "filename" from v$datafile a,v$filestat b where a.file#=b.file#;
file# STATUS BYTES PHYRDSPHYWRTS filename
----- ------- ---------- ---------- ---------- --------------------------------------------------------------------------------
1 SYSTEM 2831155207716589949 /oracle/oracle/oradata/bais/system01.dbf
2 ONLINE104857602419324191 /oracle/oracle/oradata/bais/tools01.dbf
3 ONLINE 16252928024316 684411 /oracle/oracle/oradata/bais/rbs01.dbf
4 ONLINE209715202419324191 /oracle/oracle/oradata/bais/temp01.dbf
5 ONLINE209715202419324191 /oracle/oracle/oradata/bais/users01.dbf
6 ONLINE209715202419324191 /oracle/oracle/oradata/bais/indx01.dbf
7 ONLINE209715202419324191 /oracle/oracle/oradata/bais/drsys01.dbf
8 SYSTEM 2097152009435876692 /oracle/oracle/dbs/system02.dbf
9 ONLINE 1048576002419324191 /oracle/oracle/dbs/users02.dbf
10ONLINE 2097152002419324191 /oracle/oracle/dbs/temp02.dbf
11ONLINE 2097152002419324191 /oracle/oracle/dbs/indx02.dbf
12ONLINE 2097152002419324191 /oracle/oracle/dbs/tools02.dbf
13ONLINE4294967296119664323 602026 /oracle/oracle/dbs/baisdata.001
14ONLINE4294967296 315221 683234 /oracle/oracle/dbs/baisindex.001
15ONLINE107374182477151566742219 /oracle/oracle/dbs/baistemp.001
16ONLINE10737418242419324191 /oracle/oracle/dbs/baisrbs.001
17ONLINE42949672962419324191 /oracle/oracle/dbs/baissnap.001
18ONLINE107374182424314 121931 /oracle/oracle/dbs/baisroll.001
18 rows selected
SQL>
通过以上结果，如何看出我的数据文件是否存在I/O冲突？判断有冲突的依据是什么？请指教谢谢了：） [/B]

千问 · 2005-10-30 17:05:33

凭你贴的这个怎么看的出来啊，晕！
数据文件存在I/O冲突正常，不光是数据文件，还有控制文件和日志文件都要考虑进去。
还是作个statpack看的比较清楚

千问 · 2005-10-30 17:05:33

13 ONLINE 4294967296 119664323 602026 /oracle/oracle/dbs/baisdata.001
可以初步判断，你的这个数据文件是磁盘热点。物理读比其他数据文件大的多。需要结合statspack等手段，判断哪些表或索引访问比较频繁，考虑均衡分配到其他磁盘。