Optimizer Environment

Last update: July 2023

From time to time you may notice that a single SQL statement has several different child cursors visible in v$sql. One reason for this happening is that different sessions may be running with different optimizer environments; for example your setting for the parameter optimizer_mode might be all_rows while mine is first_rows_1. If your optimizer environment is different from my optimizer environment then we cannot share cursors and we may end up using different execution plans for the same text.

Oracle 10g allows us to see the optimizer environment at the system, session, and individual SQL level, through v$sys_optimizer_env, v$ses_optimizer_env, and v$sql_optimizer_env respectively. This can be very helpful when tracking down the source of unexpected behaviour. For example:

rem
rem     Script:         optimizer_env.sql
rem     Author:         Jonathan Lewis
rem     Dated:          May 2007
rem

select
        first_load_time, child_number, last_load_time,
        plan_hash_value, loads, executions
from
        v$sql
where
        sql_id = 'g4pkmrqrgxg3b'
;    

FIRST_LOAD_TIME     CHILD_NUMBER LAST_LOAD_TIME      PLAN_HASH_VALUE LOADS EXECUTIONS
------------------- ------------ ------------------- --------------- ----- ----------
2007-04-27/08:21:19            0 2007-04-27/08:21:19      2598313856     1          2
2007-04-27/08:21:19            1 2007-04-27/08:22:44       647803999     1          1   

When we notice that we have two child cursors for the same statement generated within a few minutes of each other (notice the last_load_time) we can run, from 10g and above, the following query query to see if it gives us any clues:

break on child_number skip 1    

select
        child_number, name, value
from    v$sql_optimizer_env
where
        sql_id = 'g4pkmrqrgxg3b'
order by
        child_number,
        name
;    

CHILD_NUMBER NAME                                     VALUE
------------ ---------------------------------------- -------------------------
           0 _db_file_optimizer_read_count            16
             active_instance_count                    1
             bitmap_merge_area_size                   1048576
             cpu_count                                2
             cursor_sharing                           exact
             hash_area_size                           131072
             optimizer_dynamic_sampling               2
             optimizer_features_enable                10.2.0.3
             optimizer_index_caching                  0
             optimizer_index_cost_adj                 100
             optimizer_mode                           first_rows_1
             optimizer_secure_view_merging            true
             parallel_ddl_mode                        enabled
             parallel_dml_mode                        disabled
             parallel_execution_enabled               true
             parallel_query_mode                      enabled
             parallel_threads_per_cpu                 2
             pga_aggregate_target                     204800 KB
             query_rewrite_enabled                    true
             query_rewrite_integrity                  enforced
             skip_unusable_indexes                    true
             sort_area_retained_size                  0
             sort_area_size                           65536
             sqlstat_enabled                          true
             star_transformation_enabled              false
             statistics_level                         typical
             workarea_size_policy                     auto  

           1 _db_file_optimizer_read_count            16
             _hash_join_enabled                       false
             active_instance_count                    1
             bitmap_merge_area_size                   1048576
             cpu_count                                2
             cursor_sharing                           exact
             hash_area_size                           131072
             optimizer_dynamic_sampling               2
             optimizer_features_enable                10.2.0.3
             optimizer_index_caching                  0
             optimizer_index_cost_adj                 100
             optimizer_mode                           first_rows_1
             optimizer_secure_view_merging            true
             parallel_ddl_mode                        enabled
             parallel_dml_mode                        disabled
             parallel_execution_enabled               true
             parallel_query_mode                      enabled
             parallel_threads_per_cpu                 2
             pga_aggregate_target                     204800 KB
             query_rewrite_enabled                    true
             query_rewrite_integrity                  enforced
             skip_unusable_indexes                    true
             sort_area_retained_size                  0
             sort_area_size                           65536
             sqlstat_enabled                          true
             star_transformation_enabled              false
             statistics_level                         typical
             workarea_size_policy                     auto  

In this case, you can see that one of the cursors was generated by a session that had disabled hash joins. One convenience of this view is that it doesn’t normally show the hidden parameters but (just like v$parameter) if someone has modified a hidden parameter the parameter does show up.

Update (9th July 2015)

In the latest version of Oracle (12.1.0.2) the number of rows visible in v$sql_optimizer_env is up to 60; but the underlying structure (x$kqlfsqce) has 470 parameters listed. Even then this doesn’t tell you everything because if a session has modified its NLS parameters the changes don’t appear in the view, and the NLS parameters could have a significant effect on execution plans.

Update (March 2022)

A quick update for 19.11.0.0 which now has 69 parameter generally visible, but 613 in total.

Update (July 2023)

Information about the optimizer environment has been part of the AWR for many years in the view dba_hist_optimizer_env, but this has only ever reported a single hashed result of the optimizer environment values. From 21c a new view dba_hist_optimzier_env_details expands the optimizer_env_hash_value to a list of parameter/value pairs. See MOS Doc id: 2953121.1

In passing the 23c version of v$sys_optimizer_env reports 78 distinct values while the underlying x$qkscesys reports 658 (which you can also find in the 10053 trace file.)