Re: Random problems with performance in IDS 10 TC6

Maybe also some locking on the table by other processes causing exceccive waits. Or maybe make sure checkpoints are not blocking or taking a long time during those times. If there are transactions, make sure log fills aren't waiting to get backed up. ----- Original Message ----- From: david@smooth1.co.uk Sent: Fri, September 5, 2008 13:22 Subject:Re: Random problems with performance in IDS 10 TC6 On 3 Sep, 21:35, kem...@gmail.com wrote: > Hi, > > At my company we recently upgraded to IDS 10 TC6 and have been running > it since march this year. Lately we've been experiencing some major > problems with performance running certain stored procedures. The > problem does not occur every time we run the SP. It does some quite > randomly, at least we haven't been able to distinguish a pattern for > the performance loss. > > For example; A SP that almost every day take around 5 - 10 minutes > some days run for 2-3 hours. The SP does basically the same thing > everyday. It scans through an indexed table for changed values and if > so updates or inserts rows in another table. Each table is in a > separate DBSpace and these in turn on separate disks. > > We've of course been running update statistics regularly, we actually > run it every night. Buffered writes and reads are > 98% before and > after running the SP. There are no sequential scans. There's not a lot > bufwaits and normally no other processes are running on the server > (Windows 2003 server). We've run basically every oncheck command there > is to check the consistency of the tables in the database and all > seems fine. I should probably also mention that the B-Tree Scanner is > turned off during execution of the SP. > > Has anyone had similar problems? Any ideas on what to look for or > check? onstat -u onstat -g ses for the session. onstat -g ath for the thread id several times. DISK - Check disk service times MEMORY - Check for paging/swapping CPU - Check for other processes use the cpu. NETWORK - check network i/o What else is running on the box at the time? Is this on a SAN whether other machines may be using the disk? Have you run hardware diags on the host? Run with set explain on and check the query plans do not change. _______________________________________________ Informix-list mailing list Informix-list@iiug.org http://www.iiug.org/mailman/listinfo/informix-list ----- End of original message -----