[20200420]V$SES_OPTIMIZER_ENV 查不到刚修改的隐含参数.txt

--//链接http://www.itpub.net/thread-2133210-1-1.html的问题：
ORACLE 11204,

1 在sqlplus里执行语句：alter session set "_add_col_optim_enabled" = false;
2 然后找出该进程的SID: select sid from v$mystat group by sid;
3 之后再查询 select name from v$ses_optimizer_env where sid=xxx:上面查询的sid值，
但查询结果里，没有看到 "_add_col_optim_enabled" 这个隐含参数，WHY ?

--//在自己的测试环境重复测试：
1.环境:
SCOTT@book> @ ver1

PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SYS@book> @ hide _add_col_optim_enabled
NAME                   DESCRIPTION                        DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES ISSYS_MOD
---------------------- ---------------------------------- ------------- ------------- ------------ ----- ---------
_add_col_optim_enabled Allows new add column optimization TRUE          TRUE          TRUE         TRUE  IMMEDIATE

--//缺省为true。

2.测试：
--//session 1:
SCOTT@book> @ spid
       SID    SERIAL# PROCESS                  SERVER    SPID       PID  P_SERIAL# C50
---------- ---------- ------------------------ --------- ------ ------- ---------- --------------------------------------------------
        15       1425 12506                    DEDICATED 12507       25        253 alter system kill session '15,1425' immediate;

SCOTT@book> alter session set "_add_col_optim_enabled" = false;
Session altered.

SCOTT@book> show parameter _add_col_optim_enabled
NAME                   TYPE    VALUE
---------------------- ------- -----
_add_col_optim_enabled boolean FALSE

--//session 1:
SYS@book> select name from v$ses_optimizer_env where sid=15 and name like '%add_col_optim_enabled%';
no rows selected
--//确实没有看到。

3.使用oradebug查看：
--//session 2:
SYS@book> oradebug setospid 12507
Oracle pid: 25, Unix process pid: 12507, image: oracle@gxqqqdg4 (TNS V1-V3)

SYS@book> oradebug dump modified_parameters 1
Statement processed.

SYS@book> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/book/book/trace/book_ora_12507.trc

--//检查转储:
*** 2020-04-20 11:17:05.571
Received ORADEBUG command (#2) 'dump modified_parameters 1' from process 'Unix process pid: 12516, image: <none>'
DYNAMICALLY MODIFIED PARAMETERS:
  nls_language             = AMERICAN
  nls_territory            = AMERICA
  nls_sort                 = BINARY
  nls_date_language        = AMERICAN
  nls_date_format          = YYYY-MM-DD HH24:MI:SS
  nls_currency             = $
  nls_numeric_characters   = .,
  nls_iso_currency         = AMERICA
  nls_calendar             = GREGORIAN
  nls_time_format          = HH.MI.SSXFF AM
  nls_timestamp_format     = YYYY-MM-DD HH24:MI:SS.FF
  nls_time_tz_format       = HH.MI.SSXFF AM TZR
  nls_timestamp_tz_format  = YYYY-MM-DD HH24:MI:SS.FF TZH:TZM
  nls_dual_currency        = $
  nls_comp                 = BINARY
  _add_col_optim_enabled   = FALSE
~~~~~~~~~~~~~~~~~~~~~~~~

4.继续测试：
--//session 1:
SCOTT@book> alter session set optimizer_index_cost_adj=10;
Session altered.

--//session 2:
SYS@book> select * from v$ses_optimizer_env where sid=15 and lower(name) like '%optimizer_index_cost_adj%';
SID ID NAME                     SQL_FEATURE ISD VALUE
--- -- ------------------------ ----------- --- -----
 15 66 optimizer_index_cost_adj QKSFM_CBO   NO  10

SYS@book> oradebug dump modified_parameters 1
Statement processed.

--//检查转储:
*** 2020-04-20 11:22:11.711
Received ORADEBUG command (#4) 'dump modified_parameters 1' from process 'Unix process pid: 12516, image: <none>'
DYNAMICALLY MODIFIED PARAMETERS:
  nls_language             = AMERICAN
  nls_territory            = AMERICA
  nls_sort                 = BINARY
  nls_date_language        = AMERICAN
  nls_date_format          = YYYY-MM-DD HH24:MI:SS
  nls_currency             = $
  nls_numeric_characters   = .,
  nls_iso_currency         = AMERICA
  nls_calendar             = GREGORIAN
  nls_time_format          = HH.MI.SSXFF AM
  nls_timestamp_format     = YYYY-MM-DD HH24:MI:SS.FF
  nls_time_tz_format       = HH.MI.SSXFF AM TZR
  nls_timestamp_tz_format  = YYYY-MM-DD HH24:MI:SS.FF TZH:TZM
  nls_dual_currency        = $
  nls_comp                 = BINARY
  optimizer_index_cost_adj = 10
  _add_col_optim_enabled   = FALSE

5.继续测试:
--//按照ZALBB的说法,其它隐含参数是可以查询到.继续测试看看:
--//session 1：
SCOTT@book> @ spid

       SID    SERIAL# PROCESS                  SERVER    SPID       PID  P_SERIAL# C50
---------- ---------- ------------------------ --------- ------ ------- ---------- --------------------------------------------------
        15       2315 17879                    DEDICATED 17880       25         59 alter system kill session '15,2315' immediate;


SCOTT@book> alter session set "_optim_peek_user_binds"=false ;
Session altered.

SCOTT@book> show parameter _optim_peek_user_binds
NAME                   TYPE    VALUE
---------------------- ------- ------
_optim_peek_user_binds boolean FALSE

--//session 2:
SYS@book> select * from v$ses_optimizer_env where sid=15 and lower(name) like '%optim_peek_user_binds%';
 SID  ID NAME                   SQL_FEATURE ISD VALUE
---- --- ---------------------- ----------- --- ------
  15  98 _optim_peek_user_binds QKSFM_CBO   NO  false

--//session 1:
SCOTT@book> alter session set "_add_col_optim_enabled"=false ;
Session altered.

--//session 2:
SYS@book> select * from v$ses_optimizer_env where sid=15 and lower(name) like '%add_col_optim_enabled%';
no rows selected

--//确实有点奇怪，这个参数_add_col_optim_enabled无法看到。看来比较保险就是使用
SYS@book> oradebug setospid 17880
Oracle pid: 25, Unix process pid: 17880, image: oracle@gxqqqdg4 (TNS V1-V3)

SYS@book> oradebug dump modified_parameters 1
Statement processed.

SYS@book> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/book/book/trace/book_ora_17880.trc

--//检查转储:
Received ORADEBUG command (#1) 'dump modified_parameters 1' from process 'Unix process pid: 12516, image: <none>'
DYNAMICALLY MODIFIED PARAMETERS:
  nls_language             = AMERICAN
  nls_territory            = AMERICA
  nls_sort                 = BINARY
  nls_date_language        = AMERICAN
  nls_date_format          = YYYY-MM-DD HH24:MI:SS
  nls_currency             = $
  nls_numeric_characters   = .,
  nls_iso_currency         = AMERICA
  nls_calendar             = GREGORIAN
  nls_time_format          = HH.MI.SSXFF AM
  nls_timestamp_format     = YYYY-MM-DD HH24:MI:SS.FF
  nls_time_tz_format       = HH.MI.SSXFF AM TZR
  nls_timestamp_tz_format  = YYYY-MM-DD HH24:MI:SS.FF TZH:TZM
  nls_dual_currency        = $
  nls_comp                 = BINARY
  _optim_peek_user_binds   = FALSE
  _add_col_optim_enabled   = FALSE