Re: Oracle goes ballistic

Install Statspack and run snaps at perhaps five minute intervals until you capture the "slow" period. You can get the 8.1.6 version of Statspack and retrofit it to 8.0.x with an included script to create a view of the multiple buffer pools. Put Statspack in its own tablespace, or a tools tablespace with its own disk so you don't induce contention while running snapshots. Statspack has otherwise insignificant overhead.

Also, get concurrent sar reports for that same period to make sure the box and operating system are healthy enough. Make sure you get memory, cpu and I/O info from sar for that period.

You can use cron to kick off the snaps and the sar, which makes it easy to synchronize the two and to turn them back off again. Once you are past this issue, 1/2 hour snaps on an ongoing basis are excellent for continuing monitoring, but for diagnostics use the shorter interval to "zoom" in on the problem.

Do five minute snaps until you have the problem period covered, then run a report for that five or ten minute period to see exactly what is going on. If you don't know how to interpret the thing, post it here and I will take a look at it, as will others. You can also upload it to the Oraperf site if it is running that day.

• ricky