Is/was it a large table, with possibly millions of rows? Are varchars in the schema? Is MAX_FILL_DATA_PAGES on? And what version are we talking about?
And was the problem reproducible easily, so all or many inserts incurring the problem?
Such occasional lengthy insert can well be a problem with finding free space for the new row, with bitmap scan returning pages 'with room', but actual pages then still not offering sufficient room for the new row. In worst case such scan would traverse the entire table, only to then decide it needs to append to the end, allocate a new extent, and from there on it simply would continue at that end ... until space got eaten up again.
To mitigate such problems
- run with MAX_FILL_DATA_PAGES off - will fill pages to lower levels, so cause higher space consumption, but bitmap's 'with room' status would be more accurate, esp. with variable length rows
- create your tables with sufficiently large (first and next) extent size, or alter their next extent size to be big
- take advantage of 14.10.xC2's new Boosted Partition Free Space Caches (PFSC) created for addressing exactly this kind of problems (s.a. Table and fragment pfsc_boost admin api).
HTH,
Andreas
------------------------------
Andreas Legner
------------------------------
Original Message:
Sent: Fri December 17, 2021 10:56 AM
From: ZhiWei Cui
Subject: how can I find who block current transaction?
I have a transaction(insert into tab values (...))。
mostly, this transaction so quickly in 30 ms.
but the transaction used 10s occasionally。
how can I found which session or whic resource block this transaction ???
------------------------------
David Cui
Technical support
gbase.cn
------------------------------
#Informix