Re: Has any Informix DBA had to do the following?

Is the query plan the same? Has update statistics been run on the procedure? Can you turn tracing on in the procedure and look at the output to see where it is spending its time? On Oct 29, 3:24 pm, "malcolm.iiug" <mali...@btopenworld.com> wrote: > Now I'm trying to investigate an interesting problem. The same query was > run twice, with the identical select statement. When it was run during a > busy period it took about 30minutes. When it was run with very few other > users it was cancelled after two hours, I've been trying to understand why. > I've looked at paging - none. Disk access - busy disks but no predominant > disk and nothing going at more then 50%. CPU - less than 40% for the > duration of the query. > Only strange thing is that this is a stored procedure which drops a table, > creates a new one, and then selects 99% from an existing table and inserts > corresponding rows into this new table. The stored procedure code is CRAP, > and the database design also leaves a lot to be desired. > I've monitored it for the past three months and sometimes it runs in about > 30 minutes and sometimes takes much, much longer. > > Any ideas would be welcome. > > Regards > > malcolm > > -----Original Message----- > From: informix-list-boun...@iiug.org [mailto:informix-list-boun...@iiug.org] > > On Behalf Of Art S. Kagel > Sent: 29 October 2007 18:40 > To: informix-l...@iiug.org > Subject: Re: Has any Informix DBA had to do the following? > > On Oct 29, 9:45 am, "Ian Michael Gumby" <im_gu...@hotmail.com> wrote: > > Here's an interesting situation. > > > Had a long running query that seemed to go no where. Like it got lost. > > Usually when this happens I've found that it's not really hung. Just > doing something I didn't expect like table scanning > when I expected an indexed search. > > First thing I do is watch the onstat -u for the session over time to > see if the engine thinks that it's doing something. If that's not > conclusive I'll run xtree on the server, attach to the session, and > watch it run. Xtree is an under appreciated tool, IMHO. > > Art S. Kagel > > > Has anyone had that happen to them running IDS? > > > I mean I had to literally file a help desk ticket to get the DBA to clear > > the thread. > > > I've never had that problem in either Sybase or IDS. > > > It was driving me bonkers and I had to give up on a multi-threaded python > > app. Seems that Oracle's cx_Oracle isn't thread safe. (Ok cx_Oracle may > not > > be under Oracle...) > > > Just griping. > > > -G > > _______________________________________________ > Informix-list mailing list > Informix-l...@iiug.orghttp://www.iiug.org/mailman/listinfo/informix-list