On my last blog post i described a particular problem we faced recently following a database upgrade. To resume the issue : one of the DML have seen a severe performance degradation (More than 3 times slower) caused by high version count on the recursive SQL triggered by the delete on cascade after setting the optimizer_features_enable to 11.2.0.4. The problem seems to be related to a BUG causing a switch of the optimizer_mode from ALL_ROWS to CHOOSE on recursive statement. So what’s next !
The next thing is opening an oracle support ticket, but frankly i need to fix the issue as soon as possible and not till next year :p (So let’s do that later anyway it will take someeeee timeees :p).
I listed some solution on my previous blog post such as :
1- Changing the optimizer_mode to choose at the session or instance level -> This is unsupported and the impact is too wide.This may need to be tested carefully before been applied.
2- Reducing the value of “_cursor_obsolete_threshold” -> This may alleviate the problem but still the impact is too wide.This may need to be tested carefully before been applied.
3- Marking the cursor as hot will not resolve the issue we are facing.
Why overthinking let’s keep things simple stupid :p I just created a simple PL/SQL block and patched all recursive sql issued from the delete on cascade adding the hint opt_param(‘optimizer_features_enable’ ‘19.1.0’). Until fixing the other issue and resting the value of optimizer_features_enable this will do the trick. Moral of the story be KISS !
That’s it 🙂
Hatem Mahmoud's blog 
Thanks for sharing,
Could you please paste the plsql block that u made to patch the recursive sql.
Hi , i don’t have the code any more but you can check this https://mahmoudhatem.wordpress.com/2021/10/05/load_sql_patch-sql-a-script-to-deal-with-sql-patches-and-force-matching/
Could you please provide the query to identify which sql statement is causing the high version count