enq: HW - contention and latch: enqueue hash chains

An application uses serialized java objects to stored in the database to overcome the application server failovers. The java objects are stored as BLOB. Application has been using the same session storing mechanism for number of years (7+ years) and gone through the upgrades of Oracle versions 10.2 -> 11.1 -> 11.2 without any performance regress. During the inception (with a 10.2) high wait times on enq: HW - contention were encountered which was remedied with the use of the event 44951 (refer 740075.1)
However during a test, involving large number of concurrent application sessions (x10 baseline) the test system encountered high enq: HW - contention even though event 44951 has been set with level 1024.

Following actions were tried in order to reducing the number of and time spent on HW - contention event.
1. Large initial extent size for the lob segment.

  STORE AS "LOB_SEG"
  (
    ...(INITIAL 5368709120 NEXT 134217728 ...
  );

2. Large value for next extent, ideally this should be greater than the (average size of the LOB inserted x number of concurrent inserts).

STORE AS "LOB_SEG"
  (
    ...INITIAL 5368709120 NEXT 134217728 ...
  );

3. Tablespace with uniform extent allocation and extent size is greater than the (average size of the LOB inserted x number of concurrent inserts).
4. Large chunk size for lob segment and tablespace block size equal to the chunk size. Lob segment was placed in a tablespace with a block size of 32K and chunk size 32K was used for the lob semgment.

  STORE AS SECUREFILE "LOB_SEG"
  (
    TABLESPACE "TBS32K" CHUNK 32768
  );

However these actions only slightly reduced the HW - contention wait events and the performance was not satisfactory. At this point it was decided to use the secure file for the lob segments as it was considered better in performance compared to basic file.



Investigating secure file related issues came across the following MOS note (1532311.1) which mentioned high waits on buffer busy waits and enq: TX - contention when there are frequent updates on secure file lobs. Solution for this was to increase the secure file concurrency estimate hidden parameter (_securefiles_concurrency_estimate). However instead of relying on a parameter that depends on the concurrency, the application was modified by replacing the statement sequence of (insert/update) with an (insert/delete/insert).
Running the same test as earlier resulted in high latch: enqueue hash chain waits.

This wait event was as a result of bug 13775960 which results high enqueue hash chains for concurrent inserts on secure files (refer 13775960.8). Luckily there's patch for the bug (13775960 which supersede the patch 13395403) and once applied the enqueue hash chain wait events were resolved.
Even though Oracle documents says that secure file out perform the basic files as in this case the secure file themselves pose some issues of their own which must be tested against.

Useful metalink notes
Bug 13775960 - "enqueue hash chains" latch contention for delete/insert Securefile workload [ID 13775960.8]
Securefiles DMLs cause high 'buffer busy waits' & 'enq: TX - contention' wait events leading to whole database performance degradation [ID 1532311.1]
Bug 2530125 - Hang possible with "enqueue hash chains" latch held during deadlock detection [ID 2530125.8]
Bug 13395403 - "enqueue hash chains" latch contention on Securefile blob DMLs - superseded [ID 13395403.8]
'enq HW - contention' For Busy LOB Segment [ID 740075.1]
How To Analyze the Wait Statistic: 'enq: HW - contention' [ID 419348.1]