Two weird performance problems
Posted in 2003
I have a set of queries joining two copies of what should be the same
10GB-ish table to test for duplicates, anomalies and missing rows
between the two.
If the table order is t2 -> t1, onstat -u shows a burst of disk read
activity but this soon comes to a virtual standstill. The table is far
too big for this behaviour to be accountable by buffer reads. I've
eliminated stuck logs and checkpoints too. If I force the order t1 ->
t2, it works fine and the diskreads gallop away. The equivalent index
is being used in each case. I can reproduce the problem as many times
as I like. oncheck -cd and oncheck -cI haven't found any problems with
either table.
Another problem we are having is with painfully slow ontape archives;
the time for a level 0 has deteriorated over the last few weeks from
8-10 hours to 40+ hours. The tape drive light only flickers briefly
every couple of minutes.
I know there are ontape performance problems with very old pages but I
can't help wondering whether these two problems could be related,
especially as t1 is quite a bit older than t2.
I've tried restoring the instance to an identical, spare box and the
'slow index' problem happens just the same, but a level 0 archive
completes in 1 hour 28 minutes.
Any pointers anyone?
Thanks
Andy Kent