V$SYS_OPTIMIZER_ENV

Oracle 11gR1

V$SYS_OPTIMIZER_ENV displays the contents of the optimizer environment for the instance. The optimizer environment stores the value of the main parameters used by the Oracle optimizer when building the execution plan of a SQL statement. Hence, modifying the value of one or more of these parameters (for example, by issuing an ALTER SYSTEM statement) could lead to plan changes.

The parameters displayed by this view are either regular initialization parameters (such as OPTIMIZER_FEATURES_ENABLE) or pseudo parameters (such as ACTIVE_INSTANCE_COUNT).

Column	Datatype	Description
ID	NUMBER	Unique identifier of the parameter in the optimizer environment
NAME	VARCHAR2(40)	Name of the parameter
SQL_FEATURE	VARCHAR2(64)	Associated feature control ID
ISDEFAULT	VARCHAR2(3)	Indicates whether the parameter is set to the default value (YES) or not (NO)
VALUE	VARCHAR2(25)	Value of the parameter
DEFAULT_VALUE	VARCHAR2(25)	Default value of the parameter

Note:

1.

select * from V$SYS_OPTIMIZER_ENV;

ID	NAME	ISDEFAULT	VALUE	DEFAULT_VALUE
2	parallel_execution_enabled	YES	true	true
9	optimizer_features_enable	NO	10.1.0	10.2.0.2
11	cpu_count	YES	16	16
12	active_instance_count	YES	1	1
13	parallel_threads_per_cpu	YES	2	2
14	hash_area_size	YES	131072	131072
15	bitmap_merge_area_size	YES	1048576	1048576
16	sort_area_size	YES	65536	65536
17	sort_area_retained_size	YES	0	0
24	pga_aggregate_target	YES	3145728 KB	3145728 KB
25	_pga_max_size	NO	629140 KB	204800 KB
35	parallel_query_mode	YES	enabled	enabled
36	parallel_dml_mode	YES	disabled	disabled
37	parallel_ddl_mode	YES	enabled	enabled
38	optimizer_mode	YES	all_rows	all_rows
48	cursor_sharing	YES	exact	exact
50	star_transformation_enabled	YES	false	false
66	optimizer_index_cost_adj	YES	100	100
67	optimizer_index_caching	YES	0	0
70	query_rewrite_enabled	YES	true	true
71	query_rewrite_integrity	NO	stale_tolerated	enforced
101	workarea_size_policy	YES	auto	auto
105	optimizer_dynamic_sampling	YES	2	2
112	statistics_level	YES	typical	typical
114	skip_unusable_indexes	YES	true	true
165	optimizer_secure_view_merging	NO	false	true

2. In Oracle 10.1 and above, optimizer environment variables are externalized at:

instance level: V$SYS_OPTIMIZER_ENV

session level: V$SES_OPTIMIZER_ENV

statement level: V$SQL_OPTIMIZER_ENV

Use the values in these views when determining why execution plans differ.

3. Optimizer Environment Variable values reported by the dynamic performance views include:

active_instance_count parallel_dml_mode

bitmap_merge_area_size parallel_execution_enabled

cpu_count parallel_query_mode

cursor_sharing parallel_threads_per_cpu

db_file_multiblock_read_count pga_aggregate_target

hash_area_size query_rewrite_enabled

optimizer_dynamic_sampling query_rewrite_integrity

optimizer_features_enable skip_unusable_indexes

optimizer_index_caching sort_area_retained_size

optimizer_index_cost_adj sort_area_size

optimizer_mode star_transformation_enabled

optimizer_mode_hinted statistics_level

parallel_ddl_mode workarea_size_policy

Oracle dynamic performance views