--与实例级别相对应的会话级别视图是V$SES_OPTIMIZER_ENV。此时session 级别的参数值会优先于实例级别的参数值。
--我们可以通过V$SYS_OPTIMIZER_ENV与V$SES_OPTIMIZER_ENV来获得当前或某个特定session的所有session级别参数值。

scott@ORCL> show parameter cursor_shar

NAME                                |TYPE       |VALUE
------------------------------------|-----------|------------------
cursor_sharing                      |string     |EXACT
scott@ORCL> alter session set cursor_sharing=force;

Session altered.

scott@ORCL> show parameter cursor_shar

NAME                                |TYPE       |VALUE
------------------------------------|-----------|------------------
cursor_sharing                      |string     |FORCE
scott@ORCL> alter session set optimizer_mode=first_rows_100;

Session altered.

scott@ORCL> show parameter optimizer_mode

NAME                                |TYPE                            |VALUE
------------------------------------|--------------------------------|--------------------
optimizer_mode                      |string                          |FIRST_ROWS_100 

scott@ORCL> alter session set db_file_multiblock_read_count=128;

Session altered.

scott@ORCL> select username,sid,serial# from v$session where username='SCOTT';

USERNAME                      |       SID|   SERIAL#
------------------------------|----------|----------
SCOTT                         |       159|         5

scott@ORCL> select name,value,isdefault
  2  from v$ses_optimizer_env
  3  where sid=&input_sid
  4  minus
  5  select name,value,isdefault
  6  from v$sys_optimizer_env;
Enter value for input_sid: 159
old   3: where sid=&input_sid
new   3: where sid=159

NAME                               |VALUE          |ISD
-----------------------------------|---------------|---
_db_file_optimizer_read_count      |128            |NO
cursor_sharing                     |force          |NO
optimizer_mode                     |first_rows_100 |NO