Abdul hafeez kalsekar -- OCS/OCE/OCP: Oracle Database Parameters influencing Optimizer -- v$sys_optimizer_env , v$sql_optimizer_env and v$ses_optimizer

We always have question what database parameters influence optimizer behavior .

Oracle has views v$sys_optimizer_env , v$sql_optimizer_env and v$sess_optimizer_env that reflects database parameters that influence database optimizer behavior

Ideally its useful when comparing 2 database performance and comparing pre and post migration performance .

Optionally we can set Optimizer trace 10053

How to Obtain Tracing of Optimizer Computations (EVENT 10053) (Doc ID 225598.1)

SQL> select name, isdefault from v$ses_optimizer_env

2 where sid = 265

3 order by isdefault, name;

NAME ISD

---------------------------------------- ---

_pga_max_size NO

active_instance_count YES

bitmap_merge_area_size YES

cpu_count YES

cursor_sharing YES

hash_area_size YES

is_recur_flags YES

optimizer_capture_sql_plan_baselines YES

optimizer_dynamic_sampling YES

optimizer_features_enable YES

optimizer_index_caching YES

optimizer_index_cost_adj YES

optimizer_mode YES

optimizer_secure_view_merging YES

optimizer_use_invisible_indexes YES

optimizer_use_pending_statistics YES

optimizer_use_sql_plan_baselines YES

parallel_ddl_mode YES

parallel_degree YES

parallel_dml_mode YES

parallel_execution_enabled YES

parallel_query_default_dop YES

parallel_query_mode YES

parallel_threads_per_cpu YES

pga_aggregate_target YES

query_rewrite_enabled YES

query_rewrite_integrity YES

result_cache_mode YES

skip_unusable_indexes YES

sort_area_retained_size YES

sort_area_size YES

star_transformation_enabled YES

statistics_level YES

transaction_isolation_level YES

workarea_size_policy YES

select

child_number, name, value

from v$sql_optimizer_env

where

sql_id = 'b6pkmrqrgxh2d'

order by

child_number,

name

;

CHILD_NUMBER NAME VALUE

------------ ---------------------------------------- -------------------------

_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