Re: Good Informix bug found

In article <3223B8C4.7588@cyberramp.net>, Greg <greg@cyberramp.net> writes >June Tong wrote: >> snip..... >> >> What gets locked up and what do you kill -9? I'd be really surprised if it's >> really locked up; more likely it's just taking a long time to return a result. > >On 7.13.UC2 the whole instance is shot. The offending user gets no more >responses, nor >does anyone else. Kill -9 is only option I can find. On 7.12.UC5, offending >user gets >no more response, others still OK. Onmonitor;Mode;Immediate Shutdown works. > >Users thread appears to be active and consuming CPU, no pagereads though. I >don't know >what its doing, but it isn't productive. > Try the following commands which should give you more info:- onmonitor Status Profile - hold return down, which values change? Pahe reads may not increase since it is reading from the OnLine buffer cache rather than from disk. onstat -g act - which threads are active? onstat -g glo, which VPs are accumulaing time, is longspins increasing?. onstat -g sch is spins/busy waits increasing?. onstat -g ses <session id>, what is the session doing?. >One month ago, this query ran almost instantaneously (on 7.13.UC2). It's been >in the >client for a long time, so it ran on v5 also. > >> >> How big are your tables? How long did you wait before you kill -9'ed it? Is >> your smallint indexed? If you turn on SET EXPLAIN, which table is being >joined >> to which? I'll bet it's reading the table with the smallint first, and then >> joining that to the char(6). It doesn't matter whether your char(6) is >indexed >> because it won't use your index anyway. That's what happens when you compare >a > >Tables are roughly 4K and 2K rows. A modest handful of rows changed in each >table in the >last month, probably no more than 100ish per table (some new, some disappeared, >most >stayed the same). I waited about 15 minutes a couple of times. There wasn't >ever going >to be a response. I tried "set explain on", nothing was written to >"sqexplain.out". > >> char to a numeric. What it does is sequentially scan your whole table, >> converting every row's char(6) column to an int, and then comparing it with >> your smallint. That's because if you have >> >> SELECT t1.*, t2.* FROM t1, t2 WHERE t1.char6 = t2.smint { and t2.smint = 5 } >> >> all the following values of char6 should test true: >> "5 " >> " 5" >> " 5 " >> "00005" >> "5.000" >> { I could go on, but you get the idea. } >> >> Therefore, it can't use the index (if there is one). > >Thanks for the description, I hadn't quite realized just how nasty this >was...I'm even >more amazed this worked so fast last month. > >> >> See? And you thought Tech Support was lying to you... > >I didn't think they were lying when they suggested we shouldn't do such joins, I >fully >agree it's poor practice, but I can't just change it immediatly without breaking >lots of >other stuff. What *did* irritate me was that they didn't seem to think this >query >causing total engine brain lock was much of a problem. > >Thanks for your interest. Greg > >> >> Junesnip.... -- David Williams