I've got this situation:
A session, building an index on a fragmented temporary table, spawns 4
additional threads, one goes into a critical section (flag X in
position 5), one goes into a wait for a flush of a log buffer (G in
position 1)
Other sessions fall into a wait state, waiting for a flush of a log
buffer, (flag G in position 1)
The instance appears to be frozen, but the initial thread in a
critical section exits that state, and then the serevrs starts
porcessing normally again. The frozen state lasts between 1 and 3
minutes.
I had posted a message 2 weeks ago, July 24; I have the attachments to
add (some onstats showing sessions and threads) and new information:
We couldn't live with the wait state, so we reduced temp dbspaces to
one, the index build has only one thread, and the problem disappears.
I'm still trying to understand what it happening, and how a log buffer
flush can be blocked for a couple of minutes. I'd also like to speed
up the process involved, and that sort of requires more than one temp
dbspace.
Has anyone seen a 'G' flag before, and has anyone a reasonable
explanation of what is happening?
tia
James
Regards,
James White
white_a_james@notsohotmail.com