KISS principle : SQL patches to the rescue

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 🙂

3 thoughts on “KISS principle : SQL patches to the rescue

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s