RE: IDS 9.20

Unfortunately I do not have a box which I can test on at the moment, so I am posting ideas which might have no basis in reality... Do you know if lngspins goes through the roof when you end up doing that sequential scan? It seems to that informix is not really making the database share resources in this situation. I have seen pretty active systems with long queries running quite often where the system didn't seem to freeze. The only time the systems seem to come to an absolute screeching halt is when there are long spins going on. Having never logged into an HP box, I do not know how fast an N4000 is, but I have seen an E10000 exhibit similar behaviour when running into LRU contention.(the lru contention was causing long spins) Hope this helps, Will >===== Original Message From Scott Black <sblack@elsouth.com> ===== >I'm not sure about 9.20, but I've seen this on 7.x. It always comes >back to an 'or' in the where clause. > >We've had the engine basically seem to stop processing all requests but >the one in question for as long as the query takes to execute (or as >long as it takes me to kill it). The box is not out of resources at >all. The engine is processing just a few dozen calls (or so onperf >tells me) and all other threads just sit idle. Immediately after I kill >the query, the box comes back to life. > >By the time I track it down, there is always an 'or' clause in the where >statement that seems to confuse the optimizer. If I re-write it without >the 'or' or rewrite it with some more parenthesis, the problem usually >clears up. > >Doubt this will help, but it's worth a look. > > -----Original Message----- >From: Neil Truby [mailto:ntruby@netcomuk.co.uk] >Sent: Sunday, August 13, 2000 7:34 PM >Posted To: informix >Conversation: IDS 9.20 >Subject: IDS 9.20 > >IDS 9.20 HC-2 on HP-UX 11.0. > >I posted a query a few days ago about performance degradation, but >answered >it myself when I discovered a runaway select/update process caused the >issues. > >But having thought about it, and reproduced the issue with a sequential >scan/delete construct, I'm still not happy. I find it hard to believe >that >a single query, however heavy duty, could freeze out other users to the >extent it did. When it was running it could take 20 seconds to build >the >table list for a dbaccess->query-> info request - it was that bad. And >we're talking about an HP N4000 here - a seriously meaty machine. > >Could it be an IDS 9.20 thing, that a single query could have such an >impact? I can't remember it on7.x. Anyway, I'm going to do some >comparative testing. > >But, if anyone does know of any issues in this area, I'd love to know. >If >youhave any dazzling insight could you copy it to >neil.truby@londis.co.uk, >as I don't get to the newsgroup too frequently. > >thanks >Neil ------------------------------------------------------------ This e-mail has been sent to you courtesy of OperaMail, as a free service from Opera Software, makers of the award-winning Web Browser, Opera. Visit us at http://www.opera.com/ or our portal at: http://www.myopera.com/ Your free e-mail account is waiting at: http://www.operamail.com/ ------------------------------------------------------------