473,846 Members | 1,861 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Slow execution of Delete Statements

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

Apr 18 '06 #1
14 9663
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
Apr 18 '06 #2
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 ...

Apr 18 '06 #3
Also, my logs and tables are on separate disks.

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

Apr 18 '06 #4
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.

Apr 18 '06 #5
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

Apr 18 '06 #6
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

Apr 18 '06 #7
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.
Apr 18 '06 #8

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

Apr 18 '06 #9
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.

Apr 18 '06 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
1328
by: Jason Kirk | last post by:
I've just installed PHP 5 under Fedora Core 2 and I've been having speed problems with xsl and the dom functions. Previously I used the sablotron extension functions under PHP 4 with script execution times of under 5 seconds ( max of 0,2M xml files, with a fair number of xsl:key statements). Under PHP 5 execution times almost seem random and never seem to use the full system resources (memory-limit wasn't included as a compile option) --...
16
21380
by: mamo74 | last post by:
Hello. I am administering a SQL Server (Enterprise Edition on Windows 2003) from some month and can't understand what is going on in the latest week (when the db grow a lot). The DB is around 250G, and has one table with 1 billion rows. It is performing in a decent way, but can't understand why a particolar table has strong performance problem.
12
4040
by: Ian Murphy | last post by:
Hopefully someone else has seen something similar. We are experiencing an odd problem with aspnet, iis6 and w2k3. I have written a small site which works fine during the day. Each morning however the initial page load can take anything from 10 minutes to over an hour. Nothing seems to occur during this time. The first page load is simply incredibly slow. It seems to 'hang' until someone else tried to load the default.aspx, though there...
6
6715
by: MadMan2004 | last post by:
Hello all! I'm having a problem with a project I'm working on and I'd like to ask for anyone's input that might be helpful. I'm building a rather large front-end application connecting to an AS400 for the back end database and I'm experiencing slow response times when executing sql statements. Some select statement response times are bad. Not all, but some. And there doesn't seem to be a consistent factor in any of the sql statements...
2
8853
by: Cornelius Buschka | last post by:
Hi, we saw the following problem: We deleted all rows from a table B referencing table A (~500000 records). No problem, but the following try to delete all records from table A (~180000) lead to a "never ending" statement. We found out, that vacuuming table B after delete did the trick. It seems to us the database has to do scan thru deleted records on B while
14
1727
by: Ryan | last post by:
We've got as slightly unusual scenario happening whereby a statement is passed to SQL which consists of two parts. BEGIN TRANSACTION DELETE * FROM Whatever BULK INSERT INTO Whatever...(etc) COMMIT TRANSACTION The first is a deletion of the data and the second is the bulk insert of replacement data into that table. The error that we see is a
17
5112
by: romixnews | last post by:
Hi, I'm facing the problem of analyzing a memory allocation dynamic and object creation dynamics of a very big C++ application with a goal of optimizing its performance and eventually also identifying memory leaks. The application in question is the Mozilla Web Browser. I also have had similar tasks before in the compiler construction area. And it is easy to come up with many more examples, where such kind of statistics can be very...
46
13166
by: dunleav1 | last post by:
I have a process that does inserts that runs 50% slower that Oracle and Mssql. Queries normally take 50% slower than normal. DB2, Oracle, Mssql all are configured on same os, same disk array (raid 1/0 - 15k disks) but DB2 I/O seems to be significantly slower. Tablespaces are SMS with automatic prefetch set. My thought was to create the log buffer array as large as possible so I don't have to switch to disk much. I'm running circular...
6
3850
by: Raj | last post by:
We have a batch process that deletes about 7-8 million records every day & takes about 30-35 mins to complete. Is there a way to optimize deletes? Below is the statement delete from fact where (key,dt) in (select key,dt from dim1 union select key,dt from dim2) Fact table has composite index on key,someothecolumn,dt.
0
9879
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9725
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10640
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10705
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
1
7877
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7050
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5714
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
4521
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
3157
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.