473,704 Members | 6,813 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Delete explain plan question - DB2 V8 and V9


Here's the statement: delete from table a where id1=4;

Table a is a parent to table b,c,d,e,f,g,h,i ,j,k,l
Table a key is id1,id2
table b,c,d,e,f,g,h,i ,j,k,l key is id2,id3.

I delete all the rows from b,c,d,e,f,g,i,j ,k,l before deleting the
rows from table a.

The explain plan shows the following:
table a
delete timerons 5,862,065
fetch timerons 49,609
ridscan timerons 26,354
sort timerons 26,354
ixscan 25,824
index on table a -

then for table b,c,d,e,f,g,h,i ,j,k,l - and ixscan for each table with
low timerons.
table timeron
a 11
b 17
c .01
d .01
e .01
f .01
g .01
h 11
i .01
j .01
k 11
l 11

Statictics are up to date.

Why are the timerons on table a so high ?
I deleting a million rows of data but I wouldn't expect to take so
long as it is.

Mar 14 '07 #1
3 4092
I HAD AN ERROR IN THE FIRST POST - USE THIS ONE.

Here's the statement: delete from table a where id3=4;

Table a is a parent to table b,c,d,e,f,g,h,i ,j,k,l
Table a key is id1,id2
table a index2 is id3,id1

table b,c,d,e,f,g,h,i ,j,k,l key is id2,id3.

I delete all the rows from b,c,d,e,f,g,i,j ,k,l before deleting the
rows from table a.

The explain plan shows the following:
table a
delete timerons 5,862,065
fetch timerons 49,609
ridscan timerons 26,354
sort timerons 26,354
ixscan on index2 25,824
index2 on table a -

then for table b,c,d,e,f,g,h,i ,j,k,l - and ixscan for each table with
low timerons.
table timeron
a 11
b 17
c .01
d .01
e .01
f .01
g .01
h 11
i .01
j .01
k 11
l 11

Statictics are up to date.

Why are the timerons on table a so high ?
I deleting a million rows of data but I wouldn't expect to take so
long as it is.

Mar 14 '07 #2
On Mar 14, 4:30 pm, "dunleav1" <jmdunle...@com cast.netwrote:
I HAD AN ERROR IN THE FIRST POST - USE THIS ONE.

Here's the statement: delete from table a where id3=4;

Table a is a parent to table b,c,d,e,f,g,h,i ,j,k,l
Table a key is id1,id2
table a index2 is id3,id1

table b,c,d,e,f,g,h,i ,j,k,l key is id2,id3.

I delete all the rows from b,c,d,e,f,g,i,j ,k,l before deleting the
rows from table a.

The explain plan shows the following:
table a
delete timerons 5,862,065
fetch timerons 49,609
ridscan timerons 26,354
sort timerons 26,354
ixscan on index2 25,824
index2 on table a -

then for table b,c,d,e,f,g,h,i ,j,k,l - and ixscan for each table with
low timerons.
table timeron
a 11
b 17
c .01
d .01
e .01
f .01
g .01
h 11
i .01
j .01
k 11
l 11

Statictics are up to date.

Why are the timerons on table a so high ?
I deleting a million rows of data but I wouldn't expect to take so
long as it is.
Dbadvis makes a few index suggestions but nothing that will change the
explain plan dramatically.
The server is churning on CPU - 25%.
DB snapshots really don't show much.
The activity monitors show for the delete statement:
rows read 952,236
rows written 1,670,236
user cpu 67,546,390,625
*************** *******YIKES
system cpu 181,531,250

What could be causing this huge USER CPU allocation?

Mar 15 '07 #3
Ian
dunleav1 wrote:
I HAD AN ERROR IN THE FIRST POST - USE THIS ONE.

Here's the statement: delete from table a where id3=4;

Table a is a parent to table b,c,d,e,f,g,h,i ,j,k,l
Table a key is id1,id2
table a index2 is id3,id1

table b,c,d,e,f,g,h,i ,j,k,l key is id2,id3.

I delete all the rows from b,c,d,e,f,g,i,j ,k,l before deleting the
rows from table a.
It would be more helpful if you could post the actual plan
instead of trying to describe it.

Also, if you are going to delete the child rows first, why are all of
the foreign keys defined as delete cascade?

Mar 18 '07 #4

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

Similar topics

0
434
by: Clay Luther | last post by:
Again, we have an odd performance problem with PGSQL, 7.4b2. Here is the query: delete from numplan where pkid in (select numplan.pkid from numplan left outer join pilothuntgroup on numplan.pkid=pilothuntgroup.fknumplan left outer join devicenumplanmap on numplan.pkid = devicenumplanmap.fknumplan where numplan.tkpatternusage=2 and pilothuntgroup.fknumplan is null
10
2161
by: Greg Stark | last post by:
This query is odd, it seems to be taking over a second according to my log_duration logs and according to psql's \timing numbers. However explain analyze says it's running in about a third of a second. What would cause this? Is it some kind of postgresql.conf configuration failure? I have the same query running fine on a different machine. QUERY PLAN...
14
20371
by: Ina Schmitz | last post by:
Hello all, I don't succeed in displaying the explain plan. I use IBM DB2 Universal Database 8.2. I tried to do the example given in the online help for "Visual Explain". The tables EXPLAIN_STATEMENT and EXPLAIN_INSTANCE exist. With VESAMPL.DDL, I loaded the predefined execution plans. In the next step, I'ld like to display the loaded access plans. So, I right clicked on "Show Explained Statements History" and got the result:
14
5823
by: Karl O. Pinc | last post by:
Hi, Thought perhaps some other eyes than mine can tell if I'm doing something wrong here or if there's a bug somewhere. I've never passed a ROWTYPE varaible to a function but I don't see where the problem is. I keep getting errors like (the first is my debug output): NOTICE: last cycle is: 11 WARNING: Error occurred while executing PL/pgSQL function
2
8843
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
9626
by: Michel Esber | last post by:
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)
0
1905
by: JAW | last post by:
This plan seems like it should perform well. Does anyone see anything. SQL Statement Text: DECLARE MTR - RDG - EST - CSR CURSOR FOR
3
18611
by: raviva | last post by:
Hi, I want the explain plan. But when I clicked on the explain plan it says ORA-02404: specified plan table not found. I investigated on net and some books. I was asked to load the utlxplan.sql. I did that but of no use. I also changed the name of the plan table in TOAD, still no use. New thing I heard recently is to run TOADPREP.SQL. But this script is not available on our server($ORACLE_HOME/rdbms/admin). Can someone please suggest me what...
8
2983
by: Michel Esber | last post by:
Hello, Env: DB2 V8 LUW FP16 running Linux create Table X (machine_id varchar(24) not null, ctime timestamp not null); create index iFoo on X (MACHINE_ID, CTIME) allow reverse scans; alter table X add primary key (MACHINE_ID, CTIME); Our C++ application inserts data into a table X using CLI array insert
0
8766
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
8679
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
9134
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
9023
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,...
0
8974
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
6603
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
4698
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
2474
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2078
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.