[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查询还是无法查询到结果。