23ai — Analyzing Optimizer Environment Using DBA_HIST_OPTIMIZER_ENV_DETAILS

 Changes in a query’s execution plan can occur due to straightforward reasons such as adding or dropping indexes, table partitioning, or index partitioning. However, identifying the root cause of an optimizer behavior change is not always straightforward because sometimes changes in the Optimizer Environment itself cause a new execution plan to be generated.

For example, in one session the parameter OPTIMIZER_INDEX_COST_ADJ, which controls the optimizer’s inclination to use indexes, might be set to 1, while in another session it could be set to 1000! Undoubtedly, such differences in the Optimizer Environment can cause execution plans for some queries to change.

This document explains how to detect whether a change in a query’s execution plan is caused by a change in the Optimizer Environment. More specifically, it shows how to determine which parameters and environmental factors triggered the new execution plan. We will use a new capability introduced by Oracle in version 23ai to achieve this.

Background

Before Oracle 23ai, the views V$SQL, V$SQLAREA, and DBA_HIST_SQLSTAT stored a value called the Optimizer Environment Hash Value alongside the Plan Hash Value but provided no further details about it. In Oracle 23ai, the new view DBA_HIST_OPTIMIZER_ENV_DETAILS has been introduced to provide detailed insights in this area.

Note: The views V$SYS_OPTIMIZER_ENV and V$SQL_OPTIMIZER_ENV allow you to inspect Optimizer Environments in real time.

Scenario Setup

To demonstrate, we will use the OPTIMIZER_INDEX_COST_ADJ parameter to explore the DBA_HIST_OPTIMIZER_ENV_DETAILS view.

First, create a table and an index for the scenario:

SQL> CREATE TABLE usef.tbl1 AS SELECT * FROM dba_source;
Table created.

SQL> CREATE INDEX usef.indTYPE ON usef.tbl1(type);

Next, execute the following query:

SQL> SELECT * FROM VAHID.tbl1 WHERE type = 'PACKAGE BODY';

The default value of the parameter optimizer_index_cost_adj is 100. With this setting, the execution plan for the above query will be:

SQL> ALTER SYSTEM SET optimizer_index_cost_adj = 100;
System altered.

SQL> SELECT * FROM usef.tbl1 WHERE type = 'PACKAGE BODY';

By increasing the value of optimizer_index_cost_adj, we increase the cost of using the index:

SQL> ALTER SYSTEM SET optimizer_index_cost_adj = 1000;
System altered.

The SQL_ID of the above query is 3ubvmambcg5fz. For this SQL_ID, two different plan hash values exist:

SQL> SELECT sql_text, sql_id, plan_hash_value, optimizer_env_hash_value
     FROM v$sql
     WHERE sql_text LIKE '%usef.tbl1 WHERE%'
       AND sql_text NOT LIKE '%like%';

How to Detect This Change?

How can someone unaware of this change identify it? The DBA_HIST_OPTIMIZER_ENV_DETAILS view helps in this regard.

Since this view relies on AWR snapshots, first add this SQL_ID to the next snapshot:

SQL> EXEC DBMS_WORKLOAD_REPOSITORY.ADD_COLORED_SQL('3ubvmambcg5fz');
PL/SQL procedure successfully completed;

Next, manually create an AWR snapshot:

SQL> EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
PL/SQL procedure successfully completed;

Finally, query the DBA_HIST_OPTIMIZER_ENV_DETAILS view to identify the parameters related to each plan hash value:

SQL> SELECT distinct optimizer_env_hash_value,name, value FROM dba_hist_optimizer_env_details WHERE optimizer_env_hash_value in ( 3841567604,4128052869) and name='optimizer_index_cost_adj';
OPTIMIZER_ENV_HASH_VALUE NAME                           VALUE
------------------------ ------------------------------ -----
              3841567604 optimizer_index_cost_adj       100
              4128052869 optimizer_index_cost_adj       1000

In the example above, we already knew which parameter changed. In real environments, the reason for execution plan changes is usually unknown. Therefore, we need a more comprehensive query on this view:

SQL> select distinct optimizer_env_hash_value, name, value
  from dba_hist_optimizer_env_details
 where optimizer_env_hash_value in (3841567604, 4128052869)
   and name in (with a as
                (SELECT distinct name, value
                   FROM dba_hist_optimizer_env_details
                  WHERE optimizer_env_hash_value in (3841567604, 4128052869))
                 select name from a group by name having count(*) > 1
                );

The following query, taken from Oracle documentation, joins dba_hist_sqlstat and dba_hist_optimizer_env_details views to search based on a given SQL_ID:

WITH /*+ MATERIALIZE */
common_opts AS
 (SELECT e2.name, e2.value, count(DISTINCT e2.optimizer_env_hash_value)
    FROM dba_hist_sqlstat s2, dba_hist_optimizer_env_details e2
   WHERE s2.con_id = e2.con_id
     AND s2.con_id = sys_context('USERENV', 'CON_ID')
     AND s2.optimizer_env_hash_value = e2.optimizer_env_hash_value
     AND s2.sql_id = '3ubvmambcg5fz'
   GROUP BY e2.name, e2.value
  HAVING(count(DISTINCT e2.optimizer_env_hash_value) = (SELECT count(DISTINCT
                                                                    s3.optimizer_env_hash_value)
                                                         FROM dba_hist_sqlstat s3
                                                        WHERE s3.sql_id =
                                                              '3ubvmambcg5fz') AND count(DISTINCT e2.optimizer_env_hash_value) > 1))
SELECT DISTINCT e.optimizer_env_hash_value OPT_ENV,
                e.name                     OPT_PARAM,
                e.value
  FROM dba_hist_sqlstat s, dba_hist_optimizer_env_details e
 WHERE s.con_id = e.con_id
   AND s.con_id = sys_context('USERENV', 'CON_ID')
   AND s.optimizer_env_hash_value = e.optimizer_env_hash_value
   AND s.sql_id = '3ubvmambcg5fz'
   AND (e.name, e.value) NOT IN (SELECT c.name, c.value FROM common_opts c)
 ORDER BY 1, 2;