Shulou(Shulou.com)06/01 Report--

Unexpired Block is not released in the Undo rollback segment, occupying more than 90% of the undo table space.

Causes the database transaction to wait seriously. DML runs unusually slowly. There are also ora-01555 errors in the JOB operation.

The specific settings of the following UNDO tablespaces are asked in detail, as follows:

1 、 undo_retention=3600

2. Retention guarantee of the tablespace is not set

3. UNDO tablespace is set to non-automatic expansion.

4. Database version 11.2.0.1.0

Oracle gave a parameter, "_ smu_debug_mode" = 33554432, changed it to the system, reported a lot of ORA-01555, and quickly changed it back.

Did you run into bug? Check that there is indeed a very similar bug:5387030 in oracle 10.2.0.2-3.

Normally, if the undo tablespace is set to a fixed size and does not automatically expand, oracle enables the Automatic Tuning of undo retention feature.

When Automatic Tuning of undo retention is enabled, oracle ignores the setting of undo_retention and automatically adjusts undo_retention to an appropriate value based on undo tablespace size and system load. This value is generally greater than the maximum elapsed time for all transactions.

The bug phenomenon of 10gR2 is that as long as undo tablespace automatic management is set, no matter whether automatic extension is turned on or not, no matter how much undo_retention is set, the new features of Automatic Tuning of undo retention will be enabled.

The solution to this bug:

10.2.0.2Compact 10.2.0.3 has a corresponding patch. This bug has been fixed in 10.2.0.4. It is recommended to find time to stop and play patch.

Set the implicit parameter _ smu_debug_mode=33554432, and modify the tuned_undoretention algorithm to max (maxquerylen secs + 300 undotted values)

Set the implicit parameter _ undo_autotune=false to turn off the automatic undo retention adjustment feature

At 10.2.0.4 and later, the bug was fixed. The problem with my friend is definitely not caused by this bug.

When consulting this bug, I found that the enabling conditions of Automatic Tuning of undo retention are exactly the same as those of my friends. Is this related to Automatic Tuning of undo retention?

I checked the official documents, and it is true.

The system checked, and sure enough, undo_retention was automatically adjusted:

Finally, to sum up, ah, in oracle 10.2.0.4 undo 11g, if you encounter the problem of 100% usage of Oracle, it will not be released. It is no longer recommended to adjust hidden parameters to solve the problem of high undo occupancy.

It is also recommended to set the automatic expansion of undo space + limit the maximum file size to solve the problem.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.