Oracle Database Discussions

Hi All,

We have a transaction table containing CLOB column holding XML data. A transaction performs insert,select and delete on this table and rate of insert is 30K/hour to 35K/hour.
Size of each xml data will be on an average 450KB. We have pre allocated around 33 GB to this table(31 GB for LOBSEGMENT and 2 GB for LOBINDEX) and anytime this table hold only less than 500 records which comes around 500MB max.We have used RETENTION(15 mins) for LOB storage, LOGGING is disabled for LOB and table and chunk size is 32KB.

And we have set event "44951 TRACE NAME CONTEXT FOREVER, LEVEL 1024" for enq: HW contention.

We had issues with enq:HW contention on this table and when we manually allocate extents it goes away and will not occur. But yesterday we drop and recreated this table to modify chunk(8KB to 32KB) and disabled logging parameters for improving the performance of insert and reduce the log generation for DB. We manually allocated extents till 26GB after table was recreated and later due to HW contention we increased it to 33GB and later to 100GB as HW contention started coming again after 2 hours.

We are not seeing HW contention for past 5 hours after we allocated 100GB to this table.

Even though we have sufficient space in LOBSEGMENT and deleted LOB is stored in UNDO tablesapce for consistent read, why we are seeing enq:HW contention on this table?

Oracle 11.2.0.1, ASM , 2 node RAC
RHEL 5 ,64 Bit

Thanks,
Arun

Edited by: Arun on Aug 12, 2011 4:28 AM