RE: Lob HW contention

I am a little puzzled by the observation that 
"When the free blocks in the LOB segments exceeds the PCTVERSION and high concurrent DMLs running on the table, we see HW contention."

When  the free blocks exceeds pctversion then (some of) the free blocks should be re-used and so there shouldn't be any need to acquire for the HW enqueue which I believe is about protecting the tablespace from conflicting attempts to allocate the same space to different segments. It sounds more as if Oracle is having a problem recognising that the space has become free when your level of concurrency goes up and starts to allocate new extents as a consequence.

Can you give us a little more information about the lob segment definition, how the inserts (and updates and deletes) take place.

Off the top of my head I don't recall if any of the dbms_lob or dbms_space / dbms_space_admin packages have a routine for reporting and cleaning up the space management bitmaps for LOB segments, but I know there are some space management procedures that check the internal consistency of extents and bitmaps - have you tried running any of these to see if you have blocks marked as full when they should be marked as empty ?



Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
_at_jloracle

________________________________________
From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Stefan Koehler [contact@soocs.de]
Sent: 22 July 2015 10:28
To: oracle-l_at_freelists.org; eagle.f_at_gmail.com
Subject: Re: Lob HW contention

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-l


--