If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Unanswered: Confusingly slow simple insert query

Hi, I've been facing an issue in db2 for some time that has me very confused. We have a large-ish table of around 1.2 million records. Inserts to this table are sometimes quick, on the order of 10ms, and sometimes extremely slow, on the order of 2-5 full seconds. The table has a primary key, several foreign keys, one index on one of the foreign keys, and one check values constraint.

When queries are slow, they will be slow for quite some time. The server itself is mainly idle, certainly not heavily loaded. Other queries do not seem to show such painfully slow behaviour. We're using DB2 v9.5.

The server itself is mainly idle, certainly not heavily loaded. Other queries do not seem to show such painfully slow behaviour.

Inserts to this table are sometimes quick, ..., and sometimes extremely slow, ...

When queries are slow, they will be slow for quite some time.

From these descriptions, the first isuue that came in my mind was "index splitting".
But, the difference was too big(sometimes extremely slow, on the order of 2-5 full seconds.).
So, I'm not sure.

It may be worth to try to ajust PCTFREE(and LEVEL 2 PCTFREE in some environment)
and to see the number of inserted rows between periods of getting slow.

Those points may also be analyzed, if index splitting was in doubt.
- Key distribution of inserted rows in the indexes.
- Number of index leaf and higher level pages.
- Muximum and average index entries per page.

Once index splitting occured, it is highly possible that index splitting would continue.

Consider a case with PCTFREE 0.
If roandom keys were inserted, most keys would be inserted into different leaf pages.
So, almost every inserts would cause index splitting.
Then after enough keys inserted, almost every index pages would be splittied,
The splitted pages have enough spaces for new keys to be inserted.
Later inserts wouldn't cause index splitting.

But, if more keys were inserted, splittied pages would be gradualy filled up.
Then repeat again first scenario.

when i analyse ur query...i found that...it might that sometimes more users are connected to ur server or accessing the same table so that it will produce more i/o operations due to which it slows down....and another reason is ofcourse index splitting........it is recommended to make more indexes to improve query performance