How many (order of magnitude is close enough) rows are in the table?
Is the activity level of particular rows flat across all rows or is there
something (such as a time of origin “birthdate” or “birthdate” plus something
else) that correlates reasonably with change rate for a row?
Is the identification of a particular row to be updated bundled in a modular
process or scattered in application code?
I don’t have a canned solution for you and I think it would take careful
concentrated study to determine whether some sort of phased on line shuffle of
rows to a partitioned result is practical in your particular case and whether
the effort would create a net benefit.
Whether some strategic pattern can be applied to convert your current death
spiral to some form of scaling to infinity approach is an open question.
It seems likely to me that this requires a professional engagement.
mwf
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Eagle Fan
Sent: Tuesday, July 21, 2015 5:01 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Lob HW contention
Hi:
We have a non-partitioned table with about 3.5TB lob segments.
The update rate on the table is high and it caused HW enqueue contention on LOB
segment. When the free blocks in the LOB segments exceeds the PCTVERSION and
high concurrent DMLs running on the table, we see HW contention.
The table will be decommissioned early next year, if we have a temporary
solution which can sustain until it's decommissioned, that would be wonderful.
We can increase the PCTVERSION to higher number to prevent the HW contention,
but then the lob segment will increase too fast, about 80GB per day. We don't
have enough space to sustain until early next year.
We can rebuild the table as hash partitioned table which will resolve the
problem. But It needs a lot of time for a 3.5TB LOB rebuild. And we have more
than 20 databases which have the similar problem.
We are running on LMT, Manual Segment Space management tablespace, so shrink
space doesn't work for it.
Is there any other workaround?
--
Eagle Fan (www.dbafan.com)
