[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