Re: Lob HW contention
Date: Wed, 22 Jul 2015 11:28:30 +0200 (CEST)
Message-ID: <1690058411.236258.1437557310474.JavaMail.open-xchange_at_app03.ox.hosteurope.de>
Hi,
what is your freepools setting? Is there any time of the day where the table is not used or not used so frequently?
Unfortunately the number of freepools can't be increased after the LOB has been created for MSSM LOBs, but you may can pre-allocate the needed extents for the next day (ALTER TABLE .. ALLOCATE EXTENT) in such a time window. This maybe a work-around until next year.
Best Regards
Stefan Koehler
Freelance Oracle performance consultant and researcher
Homepage: http://www.soocs.de
Twitter: _at_OracleSK
> Eagle Fan <eagle.f_at_gmail.com> hat am 21. Juli 2015 um 11:01 geschrieben:
>
> 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 (<http://www.dbafan.com> )
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Jul 22 2015 - 11:28:30 CEST