I have some scripts which monitor the health of the Oracle and its OS( mainly Unix platforms).
Yesterday, I saw one of my scripts that gathers IO of datafile cannot get a correct output in an Oracle 10g RAC. The cluster of the cluster on machine RHEL 5.3 operating system and everything seems quite normal, not error was found in alert log.
When we query the dynamic view v$filestat, we will find the value of of the columns are wrong. The WRITETIM column of all the datafiles is zero. The value of the column is incorrect as we have set timed_statistics to true in our database and the phywrts for the datafile are non-zero, it is not possible the physical write operations takes "zero" seconds to complete.
SQL> select file#,phyrds,phywrts,readtim,writetim from v$filestat; FILE# PHYRDS PHYWRTS READTIM WRITETIM ---------- ---------- ---------- ---------- ---------- 1 20769 12432 2111 0 2 141 42192 6 0 3 47766 195797 4704 0 4 328 238 0 0 5 120860 56738 8249 0 6 8 6 0 0 7 121177 21454 8590 0 8 902 3285 62 0 9 8922 26226 587 0 10 4095646 744810 584 0 10 rows selected.
In fact, this problem caused by the Bug 9622663 and related Bug 7045446. Based on the bug notes from metalink, these two bugs only happen in Oracle 10203 when datafiles are stored in ASM. However it appears Oracle 10204 is also affected. It seems this bug is not fixed until 11gR2 --the latest version of Oracle release.
There is a patch to fix the Bug 9622663, however, the patch is only available in HP Unix and IBM AIX. I am going to raise a patch request for Linux X86-64 platform and hope Oracle development team will hear me.
