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
@jloracle
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf
of Stefan Koehler [contact@xxxxxxxx]
Sent: 22 July 2015 10:28
To: oracle-l@xxxxxxxxxxxxx; eagle.f@xxxxxxxxx
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: @OracleSK
Eagle Fan <eagle.f@xxxxxxxxx> 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> )
