Serious performance issues

In the past few weeks, we went through a major upgrade of our database where we completely redesigned a large portion of the database structure, migrated the data, and updated the applications. Since that period, we've had serious performance problems. We went from a system that averaged 30-40% cpu idle time to one that stays 0-5% idle (more often at 0% idle during the day). I have to believe the problem is inefficencies in the new database structure as that is what changed. However, I'm at a total loss as to how to find where these inefficencies are! I/O is more evenly spread out on this new system than on the old so it isn't an I/O problem (which is also supported by the sudden increase in cpu utilization). The applications (4GL code) are the same applications, the only changes made to them were to support the new structure - no major logic changes were made - yet they themselves perform worse under the new system. Anybody have any advice as to how to track down what's causing this and eliminate it? Any areas that I need to be looking at? One addition we made to this new structure that wasn't in the old was that we added referrential integrity utilizing foreign keys. Can the additional overhead from referrential integrity checks cause or contribute to our performance woes? I've considered eliminating the foreign keys (one key table is referenced by almost every other table in the system for example) and replacing them with regular indexes. Would this help? One interesting thing I've noticed is that even during periods of 0% idle time, one of my cpuvps is still performing busy waits and semops. There is stuff sitting on the ready queue (onstat -g rea) but for some reason it isn't picking it up. And, it is always the same processor that has these busy waits/semops. The other 5 cpuvps have 0 in busy waits and semops so they are staying fulling utilized (I have an 8 processor system - 6 are dedicated and affinitied to a production instance of Informix, 1 to a development instance of Informix, and the last one is left free for OS - it actually is the first physical processor). Following is the output of onstat -g sch: vp pid class semops busy waits spins/wait 1 22836 cpu 0 0 0 2 22937 adm 0 0 0 3 22938 cpu 0 0 0 4 22939 cpu 0 0 0 5 22940 cpu 0 0 0 6 22941 cpu 0 0 0 7 22942 cpu 6923 8395 9116 8 22944 lio 0 0 0 9 22945 pio 0 0 0 10 22946 aio 0 0 0 11 22947 msc 2004 0 0 12 22956 aio 0 0 0 13 22957 tli 18 27 786 -------------------------------------------------------- Name: Bill Weaver E-mail: Bill Weaver <billw@fscorp.com> Date: 06/03/99 Time: 10:53:46 (Retrospectively realizes there is no future in hindsight) --------------------------------------------------------