[20250723]查询会话修改的参数.txt

[20250723]查询会话修改的参数.txt --//我以前也做过类似测试，https://www.anbob.com/archives/9041.html,我一些参数修改会话查询视图v$ses_optimizer_env看不见的 --//，不知道为什么？重复测试看看,发现21c下根本无法使用oradebug查询。 1.环境： SCOTT@book01p> @ver2 ============================== PORT_STRING                   : x86_64/Linux 2.4.xx VERSION                       : 21.0.0.0.0 BANNER                        : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production BANNER_FULL                   : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production Version 21.3.0.0.0 BANNER_LEGACY                 : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production CON_ID                        : 0 PL/SQL procedure successfully completed. 2. 测试： SCOTT@book01p> @ spid ============================== SID                           : 396 SERIAL#                       : 64916 PROCESS                       : 3641 SERVER                        : DEDICATED SPID                          : 3643 PID                           : 67 P_SERIAL#                     : 4 KILL_COMMAND                  : alter system kill session '396,64916' immediate; PL/SQL procedure successfully completed. SYS@book> @ hidez _add_col_optim_enabled SYS@book> @ pr ============================== NUM                           : 4439 N_HEX                         :  1157 CON_ID                        : 0 NAME                          : _add_col_optim_enabled DESCRIPTION                   : Allows new add column optimization DEFAULT_VALUE                 : TRUE SESSION_VALUE                 : TRUE SYSTEM_VALUE                  : TRUE ISSES_MODIFIABLE              : TRUE ISSYS_MODIFIABLE              : IMMEDIATE PL/SQL procedure successfully completed. --//缺省_add_col_optim_enabled=true。 SCOTT@book01p> alter session set "_add_col_optim_enabled"=false; Session altered. SYS@book> select name from v$ses_optimizer_env where sid=396 and name like '%add_col_optim_enabled%'; no rows selected SYS@book> select name from v$ses_optimizer_env where sid=396 and lower(name) like '%add_col_optim_enabled%'; no rows selected --//可以发现使用v$ses_optimizer_env视图并没有查询到。 3.继续： SYS@book> @ hidez _optim_peek_user_binds SYS@book> @ pr ============================== NUM                           : 4198 N_HEX                         :  1066 CON_ID                        : 0 NAME                          : _optim_peek_user_binds DESCRIPTION                   : enable peeking of user binds DEFAULT_VALUE                 : TRUE SESSION_VALUE                 : TRUE SYSTEM_VALUE                  : TRUE ISSES_MODIFIABLE              : TRUE ISSYS_MODIFIABLE              : IMMEDIATE PL/SQL procedure successfully completed. --//缺省_optim_peek_user_binds=true。 SCOTT@book01p> alter session set "_optim_peek_user_binds"=false; Session altered. SYS@book> select name from v$ses_optimizer_env where sid=396 and lower(name) like '%_optim_peek_user_binds%'; NAME ---------------------------------------- _optim_peek_user_binds --//该参数可以查询到。 4.采用oradebug方式看看。 SYS@book> oradebug setospid 3643 Oracle pid: 67, Unix process pid: 3643, NID: 4026531836, image: oracle@centtest SYS@book> oradebug dump modified_parameters 1 Statement processed. SYS@book> @ t TRACEFILE -------------------------------------------------------------------------------- /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_3653.trc --//没有看到相关修改参数。 SYS@book01p> oradebug setospid 3643 Oracle pid: 67, Unix process pid: 3643, NID: 4026531836, image: oracle@centtest SYS@book01p> oradebug dump modified_parameters 1 Statement processed. SYS@book01p> @ t TRACEFILE -------------------------------------------------------------------------------- /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_3767.trc --//没有看到相关修改参数。 5.再继续： SCOTT@book01p> alter session set optimizer_index_cost_adj=30; Session altered. SYS@book> select * from v$ses_optimizer_env where sid=396 and lower(name) like '%optimizer_index_cost_adj%'; SID         ID NAME                           SQL_FEATURE ISD VALUE     CON_ID --- ---------- ------------------------------ ----------- --- ----- ---------- 396         66 optimizer_index_cost_adj       QKSFM_CBO   NO  30             1 SYS@book> select * from v$ses_optimizer_env where sid=396 and ISDEFAULT='NO'; SID ID NAME                     SQL_FEATURE ISD VALUE CON_ID --- -- ------------------------ ----------- --- ----- ------ 396 66 optimizer_index_cost_adj QKSFM_CBO   NO  30         1 396 98 _optim_peek_user_binds   QKSFM_CBO   NO  false      1 --//再次尝试使用oradebug查询还是无法查询到结果。