[Oracle] Optimizer 관련 parameter

Optimizer 관련 parameter는 v$sys_optimizer_env 에서 알 수 있다.

SQL> col NAME for a50
SQL> col VALUE for a10
SQL> col DEFAULT_VALUE for a10

SQL> select NAME,VALUE,DEFAULT_VALUE from v$sys_optimizer_env;

NAME						   VALUE      DEFAULT_VA
-------------------------------------------------- ---------- ----------
parallel_execution_enabled			   false      false
optimizer_features_enable			   23.1.0     23.1.0
cpu_count					   1	      1
active_instance_count				   1	      1
parallel_threads_per_cpu			   1	      1
hash_area_size					   131072     131072
bitmap_merge_area_size				   1048576    1048576
sort_area_size					   65536      65536
sort_area_retained_size 			   0	      0
pga_aggregate_target				   524288 KB  524288 KB
parallel_query_mode				   enabled    enabled

NAME						   VALUE      DEFAULT_VA
-------------------------------------------------- ---------- ----------
parallel_ddl_mode				   enabled    enabled
optimizer_mode					   all_rows   all_rows
cursor_sharing					   exact      exact
star_transformation_enabled			   false      false
optimizer_index_cost_adj			   100	      100
optimizer_index_caching 			   0	      0
query_rewrite_enabled				   true       true
query_rewrite_integrity 			   enforced   enforced
workarea_size_policy				   auto       auto
optimizer_dynamic_sampling			   2	      2
statistics_level				   typical    typical

NAME						   VALUE      DEFAULT_VA
-------------------------------------------------- ---------- ----------
skip_unusable_indexes				   true       true
optimizer_ignore_hints				   false      false
transaction_isolation_level			   read_commi read_commi
						   ted	      ted

optimizer_use_pending_statistics		   false      false
optimizer_capture_sql_plan_baselines		   false      false
optimizer_use_sql_plan_baselines		   true       true
parallel_degree_policy				   manual     manual
parallel_degree 				   0	      0
parallel_min_time_threshold			   10	      10

NAME						   VALUE      DEFAULT_VA
-------------------------------------------------- ---------- ----------
parallel_query_default_dop			   0	      0
is_recur_flags					   0	      0
optimizer_use_invisible_indexes 		   false      false
cell_offload_processing 			   true       true
db_file_multiblock_read_count			   128	      128
cell_offload_compaction 			   ADAPTIVE   ADAPTIVE
cell_offload_plan_display			   AUTO       AUTO
parallel_degree_limit				   65535      65535
parallel_force_local				   false      false
parallel_max_degree				   1	      1
total_cpu_count 				   1	      1

NAME						   VALUE      DEFAULT_VA
-------------------------------------------------- ---------- ----------
dst_upgrade_insert_conv 			   true       true
parallel_autodop				   0	      0
parallel_ddldml 				   0	      0
parallel_execution_message_size 		   16384      16384
deferred_segment_creation			   true       true
optimizer_adaptive_reporting_only		   false      false
PMO_altidx_rebuild				   0	      0
total_processor_group_count			   1	      1
parallel_dblink 				   0	      0
optimizer_inmemory_aware			   true       true
inmemory_force					   default    default

NAME						   VALUE      DEFAULT_VA
-------------------------------------------------- ---------- ----------
inmemory_query					   enable     enable
inmemory_size					   0	      0
approx_for_aggregation				   false      false
approx_for_count_distinct			   false      false
approx_for_percentile				   none       none
containers_parallel_degree			   65535      65535
optimizer_adaptive_plans			   true       true
optimizer_adaptive_statistics			   false      false
optimizer_ignore_parallel_hints 		   false      false
parallel_min_degree				   1	      1
parallel_dop_doubled				   0	      0

NAME						   VALUE      DEFAULT_VA
-------------------------------------------------- ---------- ----------
optimizer_use_sql_quarantine			   true       true
optimizer_session_type				   NORMAL     NORMAL
optimizer_cross_shard_resiliency		   false      false
shard_queries_restricted_by_key 		   false      false
valid_shard_session_key 			   0	      0
result_cache_mode				   MANUAL     MANUAL
container_data					   ALL	      ALL
optimizer_real_time_statistics			   false      false
group_by_position_enabled			   false      false
memoptimize_writes				   HINT       HINT
json_expression_check				   off	      off

NAME						   VALUE      DEFAULT_VA
-------------------------------------------------- ---------- ----------
gwr_trigger_enabled				   0	      0
shard_enable_raft_follower_read 		   false      false
sql_transpiler					   OFF	      OFF
lockfree_reservation				   on	      on

79 rows selected.