Check and changed default optimizer parameter in Oracle database

Check the default optimizer parameter

select NAME,default_value from v$sys_optimizer_env;


Check the current optimzer parameters

select NAME,value from v$sys_optimizer_env;


Check changed parameter value from default value

col name for a20
col default_value for a15
col value for a10
select name,value,default_value from v$sys_optimizer_env where default_value <> value;

NAME                 VALUE      DEFAULT_VALUE
-------------------- ---------- ---------------
cursor_sharing       force      exact

Check the list of optimizer parameter

col name for a40
select NAME from v$sys_optimizer_env;

NAME
----------------------------------------
parallel_execution_enabled
optimizer_features_enable
cpu_count
active_instance_count
parallel_threads_per_cpu
hash_area_size
bitmap_merge_area_size
sort_area_size
sort_area_retained_size
pga_aggregate_target
parallel_query_mode
parallel_ddl_mode
optimizer_mode
cursor_sharing
star_transformation_enabled
optimizer_index_cost_adj
optimizer_index_caching
query_rewrite_enabled
query_rewrite_integrity
workarea_size_policy
optimizer_dynamic_sampling
statistics_level
skip_unusable_indexes
optimizer_ignore_hints
optimizer_secure_view_merging
result_cache_mode
transaction_isolation_level
optimizer_use_pending_statistics
optimizer_capture_sql_plan_baselines
optimizer_use_sql_plan_baselines
parallel_degree_policy
parallel_degree
parallel_min_time_threshold
parallel_query_default_dop
is_recur_flags
optimizer_use_invisible_indexes
cell_offload_processing
db_file_multiblock_read_count
cell_offload_compaction
cell_offload_plan_display
parallel_degree_limit
parallel_force_local
parallel_max_degree
total_cpu_count
dst_upgrade_insert_conv
parallel_autodop
parallel_ddldml
parallel_execution_message_size
deferred_segment_creation
optimizer_adaptive_reporting_only
PMO_altidx_rebuild
total_processor_group_count
parallel_dblink
optimizer_inmemory_aware
inmemory_force
inmemory_query
inmemory_size
approx_for_aggregation
approx_for_count_distinct
approx_for_percentile
containers_parallel_degree
optimizer_adaptive_plans
optimizer_adaptive_statistics
optimizer_ignore_parallel_hints
parallel_min_degree
parallel_dop_doubled