V$SQL_OPTIMIZER_ENV

Oracle 11gR1

V$SQL_OPTIMIZER_ENV displays the contents of the optimizer environment used to build the execution plan of a SQL cursor. This view can be joined with V$SQLAREA on (HASH_VALUE, ADDRESS) and with V$SQL on (HASH_VALUE, CHILD_ADDRESS).

Column	Datatype	Description
ADDRESS	RAW(4 | 8)	Address of the parent cursor
HASH_VALUE	NUMBER	Hash value of the parent cursor in the library cache. The hash value is the fixed index for the view and should be used when querying V$SQL_OPTIMIZER_ENV to avoid scanning the entire library cache.
SQL_ID	VARCHAR2(13)	SQL identifier
CHILD_ADDRESS	RAW(4 | 8)	Address of the child cursor
CHILD_NUMBER	NUMBER	Child cursor number
ID	NUMBER	Unique identifier of the parameter in the optimizer environment
NAME	VARCHAR2(40)	Name of the parameter
ISDEFAULT	VARCHAR2(3)	Indicates whether the parameter is set to the default value (YES) or not (NO)
VALUE	VARCHAR2(25)	Value of the parameter

Note:

1.

select * from V$SQL_OPTIMIZER_ENV where sql_id = 'g6vymwpvdn00a';

ADDRESS	HASH_ VALUE	SQL_ID	CHILD_ADDRESS	CHILD_ NUMBER	ID	NAME	ISDEFAULT	VALUE
0000000742A9BDB0	1993998346	g6vymwpvdn00a	00000007428D5430	0	2	parallel_execution_enabled	YES	true
0000000742A9BDB0	1993998346	g6vymwpvdn00a	00000007428D5430	0	9	optimizer_features_enable	NO	10.1.0
0000000742A9BDB0	1993998346	g6vymwpvdn00a	00000007428D5430	0	11	cpu_count	YES	16
0000000742A9BDB0	1993998346	g6vymwpvdn00a	00000007428D5430	0	12	active_instance_count	YES	1
0000000742A9BDB0	1993998346	g6vymwpvdn00a	00000007428D5430	0	13	parallel_threads_per_cpu	YES	2
0000000742A9BDB0	1993998346	g6vymwpvdn00a	00000007428D5430	0	14	hash_area_size	YES	131072
0000000742A9BDB0	1993998346	g6vymwpvdn00a	00000007428D5430	0	15	bitmap_merge_area_size	YES	1048576
0000000742A9BDB0	1993998346	g6vymwpvdn00a	00000007428D5430	0	16	sort_area_size	YES	65536
0000000742A9BDB0	1993998346	g6vymwpvdn00a	00000007428D5430	0	17	sort_area_retained_size	YES	0
0000000742A9BDB0	1993998346	g6vymwpvdn00a	00000007428D5430	0	24	pga_aggregate_target	YES	3145728 KB
0000000742A9BDB0	1993998346	g6vymwpvdn00a	00000007428D5430	0	25	_pga_max_size	NO	629140 KB
0000000742A9BDB0	1993998346	g6vymwpvdn00a	00000007428D5430	0	35	parallel_query_mode	YES	enabled
0000000742A9BDB0	1993998346	g6vymwpvdn00a	00000007428D5430	0	36	parallel_dml_mode	YES	disabled
0000000742A9BDB0	1993998346	g6vymwpvdn00a	00000007428D5430	0	37	parallel_ddl_mode	YES	enabled
0000000742A9BDB0	1993998346	g6vymwpvdn00a	00000007428D5430	0	38	optimizer_mode	YES	all_rows
0000000742A9BDB0	1993998346	g6vymwpvdn00a	00000007428D5430	0	48	cursor_sharing	YES	exact
0000000742A9BDB0	1993998346	g6vymwpvdn00a	00000007428D5430	0	50	star_transformation_enabled	YES	false
0000000742A9BDB0	1993998346	g6vymwpvdn00a	00000007428D5430	0	66	optimizer_index_cost_adj	YES	100
0000000742A9BDB0	1993998346	g6vymwpvdn00a	00000007428D5430	0	67	optimizer_index_caching	YES	0
0000000742A9BDB0	1993998346	g6vymwpvdn00a	00000007428D5430	0	70	query_rewrite_enabled	YES	true
0000000742A9BDB0	1993998346	g6vymwpvdn00a	00000007428D5430	0	71	query_rewrite_integrity	NO	stale_tolerated
0000000742A9BDB0	1993998346	g6vymwpvdn00a	00000007428D5430	0	101	workarea_size_policy	YES	auto
0000000742A9BDB0	1993998346	g6vymwpvdn00a	00000007428D5430	0	105	optimizer_dynamic_sampling	YES	2
0000000742A9BDB0	1993998346	g6vymwpvdn00a	00000007428D5430	0	112	statistics_level	YES	typical
0000000742A9BDB0	1993998346	g6vymwpvdn00a	00000007428D5430	0	114	skip_unusable_indexes	YES	true
0000000742A9BDB0	1993998346	g6vymwpvdn00a	00000007428D5430	0	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