RE: Lob HW contention
Date: Wed, 22 Jul 2015 03:33:48 -0400
Message-ID: <212101d0c450$c1eda870$45c8f950$_at_rsiz.com>
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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Eagle Fan
Sent: Tuesday, July 21, 2015 5:01 AM
To: oracle-l_at_freelists.org
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)
--
http://www.freelists.org/webpage/oracle-l
Received on Wed Jul 22 2015 - 09:33:48 CEST