Re: 7.x to 9.x Performance issue in the extreme

----- Original Message ----- From: "Tom Lowrie" <tlowrie@munis.com> To: <informix-list@iiug.org> Sent: Thursday, October 02, 2003 11:36 Subject: 7.x to 9.x Performance issue in the extreme > Greetings, > > The problem: I run an identical program on Server A and Server B. On > Server A the program runs in 12 seconds. On Server B it takes 1.5 > hours. > > Server A: IBM RS/6000 H80, AIX 4.3.3.0.11, IBM IDS 7.31.UC7 > > Server B: IBM RS/6000 S80, AIX 4.3.3.0.9, IBM IDS 9.30.UC2 > > The only difference in the hardware on these 2 servers is Server A has > 6 Gigabytes of RAM while Server B has 4. The SSA Disk subsystems are > identical. > They are not identical at all. The informix versions between A and B are different. I have certainly seen exactly the problem you are experiencing simply from changing the engine and nothing else. I would monitor the processes as they run, chances are that only one or 2 sql statements are impacted. You should be able to spot them easily enough using onstat -g ses <sid> or onstat -g sql <sid>. Once you have identified the problem queries I would run the statements with 'set explain on' to see the query plans. The differences are likely to be obvious. I would then use optimizer directives to force B to execute the query in the same manner as A. If that works set about running statistics on B to see if you can improve things [remember to take out the directives]. I always recommend starting with low on its own [remember to drop distributions] and work upwards from there. Mark sending to informix-list