Optimizer Environment

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 my be running with different optimizer environments, for example your optimizer_mode may be all_rows while mine is first_rows_1. If your optimizer environment is different from my optimizer environment, then we cannot share cursors.

Oracle 10g allows us to see the optimizer environments at the system, session, and individual SQL level, through v$sys_optimizer_env, v$ses_optimizer_env, and v$sql_optimizer_env. This can be very helpful when tracking down anomalies, for example:

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 (generated within a few minutes of each other – notice the last_load_time) for the same statement, we can run the following – 10g only – 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 messes with a hidden parameter, they suddenly show up.