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.

These icons link to social bookmarking sites where readers can share and discover new web pages.
  • Digg
  • Sphinn
  • del.icio.us
  • Facebook
  • Mixx
  • Google
  • Furl
  • Reddit
  • Spurl
  • StumbleUpon
  • Technorati