[20200420]V$SES_OPTIMIZER_ENV 查不到剛修改的隱含引數.txt

[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