'Alter system reset' Does not Update V$SYS_OPTIMIZER_ENV or V$SES_OPTIMIZER_ENV (Doc ID 2824429.1)

Last updated on APRIL 17, 2023

Applies to:

Symptoms

When the OPTIMIZER_FEATURES_ENABLE parameter value is reset using:

     alter system reset optimizer_features_enable scope=both;

This resets the value to the original in v$parameter and v$spparameter, but not in the v$sys_optimizer_env or v$ses_optimizer_env.
Due to this, the newer sessions will still be using the modified value instead of the original value.

Consider the below testcase:

At this time, any new session launched will continue to use OFE as the modified value in the execution plan:

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
      DB_VERSION('19.1.0')

Changes

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.