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-bounces@iiug.org [mailto:informix-list-bounces@iiug.org]
On Behalf Of Art S. Kagel
Sent: 29 October 2007 18:40
To: informix-list@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-list@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list

malcolm.iiug 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.

Locking?...You probably remembered this one...
Different query plans?...

--
Fernando Nunes
Portugal

http://informix-technology.blogspot.com
My email works... but I don't check it frequently...