The story of optimizer_features_enable downgrade and high cursor version count

Following the upgrade of one the databases to 19.6 from 11.2.0.4 , part of the application was hit by severe performance regression (No previous testing where done but this is not the subject) .So until figuring out what’s going on a quick workaround was applied by changing the parameter optimizer_features_enable to 11.2.0.4. Every thing seems to be ok after this change but one apparently simple DML that’s was running ok has seen a severe performance impact and it’s now 2 to 3 times slower.

The DML look like this :

And apparently most of the time where spend burning CPU.

Ok let’s trace it :

So this simple DML has triggered hundreds of delete on cascade on many other tables.

And it’s seem that a lot of times is spent parsing.Let’s do callstack profiling using Tanel Poder script “ostackprof.sql”

We can clearly see that the process spinning in kkscsSearchChildList() -> kkshGetNextChild() this may indicate that there is a high cursor version count as the process is walking the linked list of child cursor. Let’s check v$sqlarea

Ok this is not pretty at all. All recursive sql triggered by the delete cascade have very very high version count and it’s sill increasing so the problem goes from worse to worse (And _cursor_obsolete_threshold = 8192 does not help).

Let’s take a look at the mismatch reason

Extracted reason from v$sql_shared_cursor

So a lot of child cursor where created because of mismatch on the OPTIMIZER_MODE (requested ALL_ROWS found CHOOSE)

So even that only optimizer_features_enable has been changed the OPTIMIZER_MODE switched from ALL_ROWS to CHOOSE for recursive SQL when building the execution plan.

Extract from V$SQL_OPTIMIZER_ENV when optimizer_features_enable is set to 19.1.0 :

Top level cursor :

Recursive cursor issued from delete on cascade :

Extract from V$SQL_OPTIMIZER_ENV when optimizer_features_enable is set to 11.2.0.4 :

Top level cursor :

Recursive cursor issued from delete on cascade :

This is apparently a BUG ! Changing the optimizer_mode to CHOOSE at the system/session level which is unsupported will resolve the issue but it’s not supported. Reducing the value of “_cursor_obsolete_threshold” can also alleviate the problem. But the best option here is of course to fix the other regressed query and turn back the optimize feature enable to 19.1.0.

That’s it 🙂