Examining the Optimizer Environment within Which a SQL Statement was Parsed in AWR (Doc ID 2953121.1)

Last updated on MAY 21, 2024

Applies to:

Goal

The cost-based optimizer creates an execution plan which conforms to the values of several session parameters.  (For example, the execution plan created while the value of OPTIMIZER_MODE is equal to FIRST_ROWS may differ from that created when the value is set to ALL_ROWS.)  This optimizer environment is represented by column, OPTIMIZER_ENV_HASH_VALUE, within V$SQL, V$SQLAREA, and DBA_HIST_SQLSTAT.  Two different values of OPTIMIZER_ENV_HASH_VALUE imply a difference in environment during time of parse, but they do not provide any further details.

If an execution plan for a given SQL statement has unexpectedly worsened, and if the value of OPTIMIZER_ENV_HASH_VALUE has changed as well, then knowing how the optimizer environments differ may prove quite useful.  Up until Oracle Database 23ai, this was not possible, but now, a new view for this purpose exists, called DBA_HIST_OPTIMIZER_ENV_DETAILS.  This document provides a brief explanation of how to obtain these details by showing you a few helpful queries.

For the purposes of this document, however, it is necessary to provide an example in which an execution plan changes because of a change in optimizer environment.  The table used for this purpose is a direct copy of the data found within the DBA_OBJECTS view, with a few indices applied.

This sample code is provided for educational purposes only and is not supported by Oracle Support.  Although it has been tested internally, we do not guarantee that it will work for you.  Ensure that you run it in your test environment before using.

Solution

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