Friday, April 16, 2010

High 'enq: US - contention' waits (Contention with auto-tuned undo retention)

This is a  bug (Bug 7291739 )  confirmed as being affected (10.2.0.4) and fixed in 10.2.0.5
Symptoms
Waits for "latch: row cache objects"

Waits for "enq: US - contention"

Description
High 'latch: row cache objects' contention may be seen on dc_rollback_segments together with high 'enq: US - contention' waits when using system managed undo with an auto tuned undo retention period.

This fix allows users to specify a high threshold for undo retention via  the "_highthreshold_undoretention" parameter. The tuned value of undo retention  will then not exceed the set value. This underscore parameter is a dynamic  parameter and its value can be changed with

For 11.2
alter system set "_highthreshold_undoretention"=new value;
ALTER SYSTEM SET "_FIRST_SPARE_PARAMETER"=new value;

Workaround
Disable auto tuning by setting "_undo_autotune" = false
alter system set "_undo_autotune" = false

No comments: