RE: Insert into CLOB field causes enqueue

> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-
> bounce_at_freelists.org] On Behalf Of San Sridharan
> Sent: Friday, October 13, 2006 12:48 AM
> To: Mark.Bobak_at_il.proquest.com; davidsharples_at_gmail.com
> Cc: oracle-l
> Subject: Re: Insert into CLOB field causes enqueue
>
> Thank you for the response.
>
> select chr(bitand(p1,-16777216)/16777215)||
> chr(bitand(p1,16711680)/65535) "Lock"
> from v$session_wait a
> where event = 'enqueue';
>
> Lock
> ------
> HW
>
> An extract from Don Burleson's article says the following.
>
> "HW Enqueue - This type of enqueue is used with the high-water mark
> of a segment; manually allocating the extents can circumvent this
> wait. "
>
> The tablespace that holds this table is LMT with AUTO space
> management. Do I have to change this setting? Whats your thought on
> this?

Hi

The HW enqueue is used in two situations: - When the high water mark is increased. - When new extents are allocated.

In both cases the contention is related to the header block.

If the extents are small and the generation of new extents is high, increasing the extent size is the way to go. In fact pre-allocating them is just a temporary solution if you table is growing steadily.

If the problem is related to the high water mark, you have to reduce contention. Here I see only two methods: - Reducing concurrency.
- Partitioning the segment.

HTH
Chris

--
http://www.freelists.org/webpage/oracle-l