Database Server is crawling !!!

[Steve Lewis]

Sri,

You cannot have the database update statistics by itself as it goes along. It is a manual process.

The processes may be asleep because they have nothing to do. They may be blocked as Tim says. If you do an 'onstat -g spi' you can see if you have lots of spinlock activity. Use Glance to check the reason for process suspension and number of system calls to semctl and semmask.

All the informix installations I have seen increased the value of kernel parameter semume, but you haven't.

The more I look at your stats, the more I think that the database is not the problem. It just doesn't look busy enough. Have you not thought that the reason your database is idle is because the slowness is elsewhere?

Are you using Java or VB middleware?

If so, check how much time is being spent in the application and the middleware, compared with how much time is spent in the database.

I had a very similar experience with this before. Everybody blamed the database tuning for weeks when the problem was with the Java. Don't immediately accept the arguments of your programmers, check the log file.

Tim, that was a super SQL. Bring back DB/Cockpit!!!