Re: Lob HW contention

From: Jeremy Schneider <jeremy.schneider_at_ardentperf.com>
Date: Mon, 27 Jul 2015 10:35:18 -0400
Message-ID: <CA+fnDAbqxXOGJNWSErOd6BYct-pCwV7bajCR6g89_JE_2qfRmA_at_mail.gmail.com>

For sure you're using MSSM? Also you didn't mention if it's basicfile/securefile and didn't mention what version of the database you're on.

We had a nearly identical problem recently but we were basicfiles on 11.2 ASSM... so event 44951 (via doc 6376915.8 / doc 1476233.1 / bug 6376915) gave us a little bit of temporary relief. However the only long-term solution proved to be physically reorganizing. FWIW though we were able to redefine to securefiles and partitioning over a weekend pretty easily without any downtime.

-Jeremy

--
http://about.me/jeremy_schneider


On Tue, Jul 21, 2015 at 5:01 AM, Eagle Fan <eagle.f_at_gmail.com> wrote:


> 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 Mon Jul 27 2015 - 16:35:18 CEST