These days encounter an error, I do not know if it's wrong, because there is no error, but the operating table in a sudden just 2 minutes suddenly reduced, causing the software to restart. To view the alert log there is no error, but in the ASH TOP SQL box with an error like me baffled. View the SQL statement is simply an update and does not need to be optimized. Finally, Baidu, google help finally found the cause of the error, the original settings with UNDO. First to introduce under undo_retention parameter, the parameter is the removal of the section of the minimum retention time, the default Oracle will be based on the size of the table space and historical usage, automatically adjust the undo information is saved time, while ignoring the value of undo_retention undo_retention guarantee feature is enabled is to execute the following command:

ALTER TABLESPACE UNDOTBS RETENTION GUARANTEE;

In the case of automatic adjustment enabled, the the actual revocation minimum retention time to get by query V $ UNDOSTAT view on TUNED_UNDORETENTION column. Often the shortest retention time is much greater than the setting the UNDO_RETENTION. The UNDO automatic optimization function can maximize the use of the undo tablespace to meet most of the sql execution, but also poses a problem: a lot of transaction is finished, UNDO tablespace in a long time has been to maintain the utilization rate is close to 100% active state very little. This close to the state can not manually contraction, or even restart the database instance can not be mitigated, often receive undo tablespace monitoring alarm. 

Come back to talk about the enq: U.S. - Contention problem is bugs began to appear in the oracle10g (11.1.0.7 still this bug), because the system activity increased or reduced, oracle SMON process automatically ONLINE or OFFLINE rollback segments. This has led to some undo segments related to latch or enqueue hold live too long, leading to a lot of system active session waiting for the enq: U.S. - Contention. You can use the following workaround:

1 Set the event SMON not OFFLINE automatically rollback segment. 

alter system set events '10511 trace name context forever, level 1 '; 

2 set the parameters _rollback_segment_count: the number of rollback segment in the state of online; this value is set to the database busiest time of the number of rollback segments. 

alter system set "_rollback_segment_count" =; 
Here '_' at the beginning of the hidden parameters, show parameter can not see, by the following statement: 

select a.ksppinm name, b.ksppstvl value, a.ksppdesc description 
from x $ ksppi a, x $ ksppcv b 
where a.indx = b.indx 
and a.ksppinm like '% _rollback_segment_count%'; 

3. Undo autotune bug a lot. Best to disable. 

alter system set "_undo_autotune" = false; 
This method is to turn off the the UNDO automatic adjustment function, colleagues can get rid of the UNDO tablespace in a long time has been to maintain the utilization rate is close to 100%. 

4 a patch: A fix to bug 7291739 is the _highthreshold_undoretention the to set a high threshold for undo retention completely distinct from maxquerylen to set a new hidden parameter. 

alter system set "_highthreshold_undoretention" =;