Tuesday, April 12, 2011

Oracle, virtual machines and weird time

If you have used virtual machines a lot, you might know that sometimes there are issues with the time of the virtual machines and that is a good practice to keep the time in sync (with services such like NTP). The problems I saw with virtual machine's time where related mostly with heavy load and hardware not optimal for that load, but there are more problems related to virtual machines out there.

Some day I had a weird problem with an Oracle 10g database: suddenly there was a lot of AWR data and the tablespace was growing fast. The snap interval was one hour and the retention period seven days, but upon further inspection I noticed that snapshots where being taken every minute:

SQL> select snap_interval, retention from dba_hist_wr_control;

SNAP_INTERVAL RETENTION
---------------------------------------- -------------------
+00000 01:00:00.0 +00007 00:00:00.0

SQL> select SNAP_ID, STARTUP_TIME, BEGIN_INTERVAL_TIME, END_INTERVAL_TIME from dba_hist_snapshot order by SNAP_ID;

SNAP_ID STARTUP_TIME BEGIN_INTERVAL_TIME END_INTERVAL_TIME
-------- ---------------------------- --------------------------- --------------------------
8123 04-JUL-10 07.34.43.000 PM 19-JUL-10 10.07.28.091 AM 19-JUL-10 10.08.35.353 AM
8124 04-JUL-10 07.34.43.000 PM 19-JUL-10 10.08.35.353 AM 19-JUL-10 10.09.46.060 AM
8125 04-JUL-10 07.34.43.000 PM 19-JUL-10 10.09.46.060 AM 19-JUL-10 10.10.56.047 AM
8126 04-JUL-10 07.34.43.000 PM 19-JUL-10 10.10.56.047 AM 19-JUL-10 10.12.04.798 AM
8127 04-JUL-10 07.34.43.000 PM 19-JUL-10 10.12.04.798 AM 19-JUL-10 10.13.13.762 AM
8128 04-JUL-10 07.34.43.000 PM 19-JUL-10 10.13.13.762 AM 19-JUL-10 10.14.24.432 AM
...

I checked a lot of things in the Oracle database but after finding nothing I guessed there was a problem in the operating system. The time was fine but the top command refreshed the information very fast, therefore I tried with top -d 300 and this time the information was refreshed every three seconds ... Even if it was meant to be refreshed every five minutes with top -d 300.

After finding this I notified the sysadmins about this situation and some time later the problem was fixed, but the sysadmins never told me what the problem was; I think that the problem was related to a VMware bug:

Weird timing voodoo. Linux top command very fast

No comments:

Post a Comment