V$SES_OPTIMIZER_ENV

Oracle 11gR1

V$SES_OPTIMIZER_ENV displays the contents of the optimizer environment used by each session. When a new session is first created, it automatically inherits its optimizer environment from the optimizer environment defined at the instance level by V$SYS_OPTIMIZER_ENV. The value of certain parameters can be dynamically modified by issuing an ALTER SESSION statement.

Column	Datatype	Description
SID	NUMBER	Session identifier. This column can be used to join with V$SESSION on the SID column.
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 for the session

Note:

1.

select * from V$SES_OPTIMIZER_ENV where sid = 1487;

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

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