Insert statement taking a long time to complete.

[Michel Esber]

Hello,

DB2 V8 LUW FP 11.

My applications eventually takes a very long time to process simple
insert statements.
Here´s the output of a event monitor:

Text : INSERT INTO MyTable
VALUES('000D60984D9A586AAD001128','2006-09-23-02.25.00',0,103923200,1843939328,298211000,6931647 000,1834013105000,128034627001536546,6779,252559,0 ,33,54219,12803462700153654)
-------------------------------------------
Start Time: 09/25/2006 13:53:51.471750
Stop Time: 09/25/2006 13:54:21.137034
Exec Time: 29.665284 seconds
Number of Agents created: 1
User CPU: 0.000000 seconds
System CPU: 0.000000 seconds
Fetch Count: 0
Sorts: 0
Total sort time: 0
Sort overflows: 0
Rows read: 0
Rows written: 1
Internal rows deleted: 0
Internal rows updated: 0
Internal rows inserted: 0
Bufferpool data logical reads: 0
Bufferpool data physical reads: 0
Bufferpool temporary data logical reads: 0
Bufferpool temporary data physical reads: 0
Bufferpool index logical reads: 0
Bufferpool index physical reads: 0
Bufferpool temporary index logical reads: 0
Bufferpool temporary index physical reads: 0
SQLCA:
sqlcode: 0
sqlstate: 00000

There are no clustered index and/or triggers defined. However, there
are several others applications reading/writing into the same table.
Locking doesn´t seem to be an issue, as all applications that read
data from the table use the UR isolation.

How do I figure out why the statement took so long to complete? Besides
event monitoring, any traces available? Any other clues ?

Thanks in Advance, Michel

[shenanwei]

Is there any Constraint check take a long time?
Take an explain first to see if it is only an insert..
Michel Esber wrote:

Hello,

DB2 V8 LUW FP 11.

My applications eventually takes a very long time to process simple
insert statements.
Here´s the output of a event monitor:

Text : INSERT INTO MyTable
VALUES('000D60984D9A586AAD001128','2006-09-23-02.25.00',0,103923200,1843939328,298211000,6931647 000,1834013105000,128034627001536546,6779,252559,0 ,33,54219,12803462700153654)
-------------------------------------------
Start Time: 09/25/2006 13:53:51.471750
Stop Time: 09/25/2006 13:54:21.137034
Exec Time: 29.665284 seconds
Number of Agents created: 1
User CPU: 0.000000 seconds
System CPU: 0.000000 seconds
Fetch Count: 0
Sorts: 0
Total sort time: 0
Sort overflows: 0
Rows read: 0
Rows written: 1
Internal rows deleted: 0
Internal rows updated: 0
Internal rows inserted: 0
Bufferpool data logical reads: 0
Bufferpool data physical reads: 0
Bufferpool temporary data logical reads: 0
Bufferpool temporary data physical reads: 0
Bufferpool index logical reads: 0
Bufferpool index physical reads: 0
Bufferpool temporary index logical reads: 0
Bufferpool temporary index physical reads: 0
SQLCA:
sqlcode: 0
sqlstate: 00000

There are no clustered index and/or triggers defined. However, there
are several others applications reading/writing into the same table.
Locking doesn´t seem to be an issue, as all applications that read
data from the table use the UR isolation.

How do I figure out why the statement took so long to complete? Besides
event monitoring, any traces available? Any other clues ?

Thanks in Advance, Michel

[Michel Esber]

Is there any Constraint check take a long time?

Take an explain first to see if it is only an insert..

Thanks for the reply.

There are no constraints defined. The insert times do vary a lot, from
less than a second to 30+s. This is what puzzles me.

Here is the acess plan:

Total Cost: 6.40367
Query Degree: 1

Rows
RETURN
( 1)
Cost
I/O
|
1
INSERT
( 2)
6.40367
1
/-----+----\
1 1.22838e+06
TBSCAN TABLE: RTM
( 3) TBL_COLLECT_IOST
3.35364e-05
0
|
1
TABFNC: SYSIBM
GENROW

Thanks, Michel

[Serge Rielau]

This doesn't look like an optimizer problem.
Somewhere your inserts are stuck.
Do you use any sort off conection pooling?
Connection concentrator or connection pooling?
Are you going strait to DB2 or through a middle tier?

Have you tried db2pd?

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

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

[Michel Esber]

Serge, thanks for the reply.

I have two basic applications: one that only inserts data into the DB,
and another that reads/deletes data from the same tables.

When I am only inserting data into the DB, my statements usually
complete very quickly and I do not see any problems (such as statements
taking 30s to insert 1 row).
When both applications are Up, my insert times increase in average and
I frequently see simple insert statements taking a very long time to
complete.

The application that reads/write data has been coded to work with UR
and deletes data in chunks.

We have recently (finally!) migrated to DB2 v8, and our OLTP
environments databases are experiencing this symptom. I did not face
the same problem with db2 V7.

Besides Infocenter, any good manual/training/reading to master db2pd ?

Thanks, Michel

This doesn't look like an optimizer problem.
Somewhere your inserts are stuck.
Do you use any sort off conection pooling?
Connection concentrator or connection pooling?
Are you going strait to DB2 or through a middle tier?

Have you tried db2pd?

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

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/