I have a query that is either long running or stuck. It appears to be
stuck, but I can't determine why.

II 9.2.1 (a64.lnx/103)NPTL

The query plan generated for this query is shown below.
ipm shows transaction begin and ends, but no Log writes.

How can I troubleshoot this?

Thanks

Dennis

set qep;
set optimizeonly;
delete from table1
where col1 not in (select col1 from table2);

QUERY PLAN 2,2, no timeout, of subselect
T2

Proj-rest

Partial(col1)

Pages 12132 Tups 594459

D1055 C5945

/

t2_col1_ndx

I(table2)

cIsam(NU)

Pages 8443 Tups 594459

QUERY PLAN 2,1, no timeout, of main query


SE Join

Heap

Pages 1016 Tups 204210

D2844 C20058

/ \

Proj-rest T2

Partial(col1) Heap

Pages 9076 Tups 408420 Pages 12132 Tups 594459

D734 C4084

/

t1_col1_ndx

I(table1)

cIsam(NU)

Pages 5874 Tups 408420

Do you know how to use ipm?

At a guess the query is stuck with a lock on either of the two tables in question. You can use ipm to determine if this is the case.

I've also attached a program which may help you. It's a UNIX shell script that uses the imadb ... obviously you need both of those things to execute it.

Martin Bowes

-----Original Message-----
From: droesler [mailto:droesler (AT) comcast (DOT) net]
Sent: 11 May 2011 13:59
To: info-ingres (AT) kettleriverconsulting (DOT) com
Subject: [Info-Ingres] How to check if query is long-running or stuck?

I have a query that is either long running or stuck. It appears to be
stuck, but I can't determine why.

II 9.2.1 (a64.lnx/103)NPTL

The query plan generated for this query is shown below.
ipm shows transaction begin and ends, but no Log writes.

How can I troubleshoot this?

Thanks

Dennis

set qep;
set optimizeonly;
delete from table1
where col1 not in (select col1 from table2);

QUERY PLAN 2,2, no timeout, of subselect
T2

Proj-rest

Partial(col1)

Pages 12132 Tups 594459

D1055 C5945

/

t2_col1_ndx

I(table2)

cIsam(NU)

Pages 8443 Tups 594459

QUERY PLAN 2,1, no timeout, of main query


SE Join

Heap

Pages 1016 Tups 204210

D2844 C20058

/ \

Proj-rest T2

Partial(col1) Heap

Pages 9076 Tups 408420 Pages 12132 Tups 594459

D734 C4084

/

t1_col1_ndx

I(table1)

cIsam(NU)

Pages 5874 Tups 408420



_______________________________________________
Info-Ingres mailing list
Info-Ingres (AT) kettleriverconsulting (DOT) com
http://ext-cando.kettleriverconsulti...fo/info-ingres


This part of mail contained an attachment with prohibited file name:

MIME type: application/octet-stream
File name: blocking_locks.sh
File size: 9.93 kB


The attachment was removed by Kerio Connect 7.1.4 patch 1
at secure1.fndtn.com.

On May 11, 2011, at 2:59 PM, droesler wrote:


[snip]


Marty's script will help, but my guess differs from his: I'm guessing
that your query is running but will take a long time to finish.

col1 is nullable, isn't it? If you can redefine that column as NOT NULL,
the SE-join will go away. Alternatively, try rewriting the delete as:

delete from table1 t1
where not exists (select * from table2 t2 where t1.col1 = t2.col2)

which I believe will also avoid the SE-join. (Assuming you aren't relying
on the screwy NULL semantics of the NOT IN version!)

Karl

On May 11, 7:20*am, Martin Bowes <martin.bo... (AT) ctsu (DOT) ox.ac.uk> wrote:
I don't use it often so I'm not that experienced with it.

Dennis

of three other columns in both tables. Where data is null in those
other columns I could put a default value in it before creating the
combined data.

I will try this.

Thanks

Dennis

On May 11, 7:43*am, Karl Schendel <schen... (AT) kbcomputer (DOT) com> wrote:
I deleted any rows from table1 and table2 where col1 was null and then
ran the query above
which only took a few seconds to run. If the data in col1 was null
then that entry in the
table was no good anyway.

Thanks

Dennis

Hello Dennis,

besides all suggestion I'd ask you to run inglogs script to take a snap
shot.
$II_SYSTEM/ingres/sig/inglogs/inglogs -nosystemconfig -nosystemstate
-nologfiles
This will especially show the stack what the session exactly does.
If the query runs very long, you can take a few other "snap shot" to
see what the stack does.

I must admit it is not user friendly but it can give additional info
for a resolution.

Thanks - Bilgihan


--
bilgihan