Thanks for the question, Wasipon.
Asked: January 10, 2025 - 2:29 am UTC
Last updated: January 14, 2025 - 4:33 am UTC
Version: 19.24
Viewed 100+ times
You Asked
Hello, Tom
We have been facing the slow rollback after killing job of huge transaction, then we discovered several ways to speed up the rollback process.
So one possibility is altering hidden parameter "_cleanup_rollback_entries" from default value (100) to be 400.
However, I still in doubt about the origin of 400 value.
I saw some couple documents also said increase the value to be 400. But no explanation why it must be 400?
Therefore, Please answer inline questions below
1) Why the recommended value is 400, how come of this value?
2) If there is larger transaction than mentioned is killed, how I ensure that value (400) is still effective for speedup rollback process?
Thank in advance
and Connor said...
Like all underscore parameters you should only set them with the guidance of Oracle Support, so I'd raise an SR just to make sure you're covered in case anything goes wrong.
There isn't a "science" to the number - it doesn't *have* to be 400. I've seen 800 or 1000 in use from time to time.
Basically the task to clean up a transaction is
1- collect a batch of work to be undone
2- do that work
If the overhead to collect a batch is high, then reducing the frequency (ie, higher _cleanup_rollback_entries will have a more visible impact). Conversely, if most of time is in (2), then you wont see much difference.
You might want to explore altering "fast_start_parallel_rollback" - but be aware, this is essentially deciding how much server resources to dedicate to the undo. You want to ensure you dont starve normal workload of access to the CPU.
MOS note 1494886.1 is a good resource