Slow execution of Delete Statements

[Michel Esber]

Linux RH 4.0 running DB2 V8 FP 11.

I have a table with ~ 11M rows and running DELETE statements is really
slow. Deleting 1k rows takes more than 3 minutes. If I run select
statements on the same table, I usually fetch rows in a reasonable
time.

The table has the following description:

MACHINE_ID VARCHAR (24)
COLLECT_TIME TIMESTAMP
PROCESS_NAME VARCHAR (64)
PROCESS_PID DECIMAL (10)

These fields are the table PK. There is also and index on (MACHINE_ID,
COLLECT_TIME).

I have run db2exfmt and the explain plan seems to indicate a low
statement cost. Statistics for table and indexes are updated.
I am puzzled by actual very slow execution speeds.

Can anyone guide me trace why and where are the execution constraints?

Thanks in advance,
Original Statement:
------------------
delete
from RTM.TBL_COLLECT _PSSTAT_WIN_RTM
where MACHINE_ID='000 423B0F8F61420F1 B8111A' and COLLECT_TIME between
'2006-04-17 00:00:55.0' and '2006-04-17 01:00:55.0'
Optimized Statement:
-------------------
DELETE
FROM RTM.TBL_COLLECT _PSSTAT_WIN_RTM AS Q1
WHERE $RID$ IN
(SELECT $RID$
FROM RTM.TBL_COLLECT _PSSTAT_WIN_RTM AS Q2
WHERE (Q2.COLLECT_TIM E <= '2006-04-17-01.00.55.000000 ') AND
('2006-04-17-00.00.55.000000 ' <= Q2.COLLECT_TIME ) AND
(Q2.MACHINE_ID = '000423B0F8F614 20F1B8111A'))

Access Plan:
-----------
Total Cost: 41.6416
Query Degree: 1

Rows
RETURN
( 1)
Cost
I/O
|
3.50129
DELETE
( 2)
41.6416
6.50129
/----+---\
3.50129 1.33235e+07
IXSCAN TABLE: RTM
( 3) TBL_COLLECT_PSS T
19.2224
3
|
1.33235e+07
INDEX: RTM
IPSSTAT_NEW_WIN

[Serge Rielau]

The plan looks fine.
Are you deleting all these rows with this one statement.
Or are you calling many delete statements with different literals in the
WHERE clause (high CPU due to compiles, package cache overflows)?

I think you need to look at basic tuning parameters (snapshots) and
sanity checks:
Is this a new system? e.g. could it be your log sits on the same disks
as the table (i/o waits)?
Could it be you're locking with another transaction (lock waits)

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Michel Esber]

Serge, thanks for the input.

My application uses parameter markers to run the statements whenever
necessary. That is : delete from table where machine_Id=? and
collect_time between ? and ?.

This server has 4 x Pentium 4 3.GHz with 16Gb. There is no memory /
cpu constraint. Linux sar utility indicates iowait times around 15%,
which is fairly the same threshold as my other servers. Also, I have
taken application snapshots and there is no indication of lock-waits.

My db2diag shows no errors at all. Seems like a lazy system to me ...
but where ...

[Michel Esber]

Also, my logs and tables are on separate disks.

Tables are on RAID 0, and logs are on RAID 1 ...

[ChrisC]

You might want to generate the plan again using parameter markers and
not defined values. In my experience, the plans can be very, very
different when generated each way - mostly depending on what the table
statistics say about the data.

[Michel Esber]

Thanks.

I have generated the plan with parameter markers, and its execution
cost is a little higher than my previous post. It seems to me that it
uses the same access plan:

Original Statement:
------------------
delete
from RTM.TBL_COLLECT _PSSTAT_WIN_RTM
where machine_Id=? and collect_time between ? and ?
Optimized Statement:
-------------------
DELETE
FROM RTM.TBL_COLLECT _PSSTAT_WIN_RTM AS Q1
WHERE $RID$ IN
(SELECT $RID$
FROM RTM.TBL_COLLECT _PSSTAT_WIN_RTM AS Q2
WHERE (:? <= :?) AND (Q2.COLLECT_TIM E <= :?) AND (:? <=
Q2.COLLECT_TIME )
AND (Q2.MACHINE_ID = :?))

Access Plan:
-----------
Total Cost: 237.781
Query Degree: 1

Rows
RETURN
( 1)
Cost
I/O
|
34.125
DELETE
( 2)
237.781
37.125
/----+---\
34.125 1.33235e+07
IXSCAN TABLE: RTM
( 3) TBL_COLLECT_PSS T
19.2742
3
|
1.33235e+07
INDEX: RTM
IPSSTAT_NEW_WIN

[Michel Esber]

Thanks.

I have generated the plan with parameter markers, and its execution
cost is a little higher than my previous post. It seems to me that it
uses the same access plan:

Original Statement:
------------------
delete
from RTM.TBL_COLLECT _PSSTAT_WIN_RTM
where machine_Id=? and collect_time between ? and ?
Optimized Statement:
-------------------
DELETE
FROM RTM.TBL_COLLECT _PSSTAT_WIN_RTM AS Q1
WHERE $RID$ IN
(SELECT $RID$
FROM RTM.TBL_COLLECT _PSSTAT_WIN_RTM AS Q2
WHERE (:? <= :?) AND (Q2.COLLECT_TIM E <= :?) AND (:? <=
Q2.COLLECT_TIME )
AND (Q2.MACHINE_ID = :?))

Access Plan:
-----------
Total Cost: 237.781
Query Degree: 1

Rows
RETURN
( 1)
Cost
I/O
|
34.125
DELETE
( 2)
237.781
37.125
/----+---\
34.125 1.33235e+07
IXSCAN TABLE: RTM
( 3) TBL_COLLECT_PSS T
19.2742
3
|
1.33235e+07
INDEX: RTM
IPSSTAT_NEW_WIN

[Gert van der Kooij]

In article <11************ *********@g10g2 000cwb.googlegr oups.com>,
mi****@us.autom atos.com says...

Thanks.

I have generated the plan with parameter markers, and its execution
cost is a little higher than my previous post. It seems to me that it
uses the same access plan:

Maybe increasing the logbuffer value in the db cfg can speed up the
deletes. The default is 8 which is to low is you want high speed deletes
etc. I've had a significant increaase in insert speed when changing this
value to 32. Increasing it above 32 didn't help in my case.

[Sumanth]

1)The delete statement deletes only 1K rows or a significant amount of rows.
If the rows are significant, the rollback segment being huge cause could
cause slowdown.
Intermediate commits can be done if the transaction can be broken down to
smaller chunks.
2)What is the value of num_io_cleaners ?
3)Is this a multiple cpu server?
4)Deletes will also have to update the indexes, your case has only two - so
this should not be a problem?
5)What is the size of your bufferpool?
6) What is the value of your LogBufSz?
7) Can you separate the IO/CPU statistics of your query

-Sumanth


"Michel Esber" <mi****@us.auto matos.com> wrote in message
news:11******** *************@g 10g2000cwb.goog legroups.com...

Thanks.

I have generated the plan with parameter markers, and its execution
cost is a little higher than my previous post. It seems to me that it
uses the same access plan:

Original Statement:
------------------
delete
from RTM.TBL_COLLECT _PSSTAT_WIN_RTM
where machine_Id=? and collect_time between ? and ?
Optimized Statement:
-------------------
DELETE
FROM RTM.TBL_COLLECT _PSSTAT_WIN_RTM AS Q1
WHERE $RID$ IN
(SELECT $RID$
FROM RTM.TBL_COLLECT _PSSTAT_WIN_RTM AS Q2
WHERE (:? <= :?) AND (Q2.COLLECT_TIM E <= :?) AND (:? <=
Q2.COLLECT_TIME )
AND (Q2.MACHINE_ID = :?))

Access Plan:
-----------
Total Cost: 237.781
Query Degree: 1

Rows
RETURN
( 1)
Cost
I/O
|
34.125
DELETE
( 2)
237.781
37.125
/----+---\
34.125 1.33235e+07
IXSCAN TABLE: RTM
( 3) TBL_COLLECT_PSS T
19.2742
3
|
1.33235e+07
INDEX: RTM
IPSSTAT_NEW_WIN

[Michel Esber]

1) No, the delete statement deletes a maximum of 2k.
2) NUM_IOCLEANERS = 50. I have no more than 6 physical disks.
3) This is a 4 CPU P4 3GHz server. Also, I have 16GB.
4) Yeah, I belive this is not a problem.
5) The bufferpool for this tablespace has 20000 16k Pages. How do I
check if this number should be higher than it is?
6) LOGBUFSZ = 2048
7) I am sorry. Is this a command line switch that I should use with
db2exfmt ?

Thanks a lot.