Database Reference
In-Depth Information
Dynamic Performance Views
There are three dynamic performance views that provide information about the execution environment:
v$sys_optimizer_env
gives information about the execution environment at the instance
level. For example, it's possible to find out which initialization parameters aren't set to the
default value:
•
SQL> SELECT name, value, default_value
2 FROM v$sys_optimizer_env
3 WHERE isdefault = 'NO';
NAME VALUE DEFAULT_VALUE
--------------------------- ----- -------------
star_transformation_enabled true false
v$ses_optimizer_env
gives information about the execution environment for each session.
Because no column provides information about whether an initialization parameter has been
modified at the system or session level, a query like the following can be used for that purpose:
•
SQL> SELECT name, value
2 FROM v$ses_optimizer_env
3 WHERE sid = 124 AND isdefault = 'NO'
4 MINUS
5 SELECT name, value
6 FROM v$sys_optimizer_env;
NAME VALUE
-------------- -------------
cursor_sharing force
optimizer_mode first_rows_10
v$sql_optimizer_env
gives information about the execution environment for each child
cursor present in the library cache. For example, with a query like the following, it's possible
to find out whether two child cursors belonging to the same parent cursor have a different
execution environment:
•
SQL> SELECT e0.name, e0.value AS value_child_0, e1.value AS value_child_1
2 FROM v$sql_optimizer_env e0, v$sql_optimizer_env e1
3 WHERE e0.sql_id = e1.sql_id
4 AND e0.sql_id = 'a5ks9fhw2v9s1'
5 AND e0.child_number = 0
6 AND e1.child_number = 1
7 AND e0.name = e1.name
8 AND e0.value <> e1.value;
NAME VALUE_CHILD_0 VALUE_CHILD_1
-------------------- ------------- -------------
hash_area_size 33554432 131072
optimizer_mode first_rows_10 all_rows
cursor_sharing force exact
