How to find the optimizer settings for a given "Session" or "sql_id"

During a query’s cost estimation the optimizer picks up many values or parameters to evaluate cost or invoke hints etc to produce an execution plan. v$sess_optimizer_env is a good view to know about any anamolies of a given statement. This can be useful when you comparing UAT and production environment where the query results are not same


For for a specific "session"

set lines 200
col name for a40
col username for a10
col value for a40
select a.sid,c.username,a.name,a.value
from v$ses_optimizer_env a
join v$sys_optimizer_env b on a.id=b.id
join v$session c on a.sid=c.sid  and c.sid=191
where a.value<>b.value
and c.username is not null
and c.username not in (‘SYS’,’SYSTEM’,’DBSNMP’)
order by a.sid,a.name;
      SID USERNAME   NAME                                     VALUE
———- ———- —————————————- —————————————-
        22 SCOTT      workarea_size_policy                     manual
       267 ADAM       optimizer_mode                           first_rows

For a specific "sql_id"

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