[20241030]使用hidez.sql查询遇到的问题.txt

--//最近一直在21c下测试library cache muext的相关测试，遇到使用hidez.sql查询隐含参数的问题

1.环境：
SYS@book> @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.测试：

SYS@book> show spparameter _mutex
$ strings /u01/app/oracle/dbs/spfilebook.ora | grep mutex

SYS@book> @ hidez ^_mutex
NAME               DESCRIPTION       DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES ISSYS_MOD
------------------ ----------------- ------------- ------------- ------------ ----- ---------
_mutex_spin_count  Mutex spin count  TRUE          255           255          FALSE IMMEDIATE
_mutex_wait_scheme Mutex wait scheme TRUE          2             2            FALSE IMMEDIATE
_mutex_wait_time   Mutex wait time   TRUE          1             1            FALSE IMMEDIATE
--//以上缺省设置。

SYS@book> alter system set "_mutex_wait_scheme"=1 scope=memory;
System altered.

SYS@book> alter system  set "_mutex_wait_time"=20 scope=memory;
System altered.

SYS@book> @ hidez ^_mutex
NAME               DESCRIPTION       DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES ISSYS_MOD
------------------ ----------------- ------------- ------------- ------------ ----- ---------
_mutex_spin_count  Mutex spin count  TRUE          255           255          FALSE IMMEDIATE
_mutex_wait_scheme Mutex wait scheme TRUE          1             1            FALSE IMMEDIATE
_mutex_wait_time   Mutex wait time   TRUE          20            20           FALSE IMMEDIATE

--//修改了_mutex_wait_scheme，_mutex_wait_time参数，但是DEFAULT_VALUE=true，感觉不对啊，明明我了参数已经不是缺省值，为什
--//么显示的还是true。
--//注：ISSES 表示session是否可以修改，ISSYS_MOD指示修改是否立即生效。

--//使用tpt的pd查询。
SYS@book> @ pd _mutex
Show all parameters and session values from x$ksppi/x$ksppcv...
       NUM N_HEX NAME                       VALUE   DESCRIPTION
---------- ----- -------------------------- ------- ------------------------------------------------------------
      1158   486 _kspmxs_upgrade_mutexes    64      KSPMXS upgrade mutex
      3520   DC0 _kqr_hot_copy_sample_size  1000000 number of mutex gets between periodic checks for hot objects
      3521   DC1 _kqr_hot_copy_sleep_limit  500     threshold of mutex sleeps to enable object hot copies
      3553   DE1 _mutex_wait_time           20      Mutex wait time
      3554   DE2 _mutex_spin_count          255     Mutex spin count
      3555   DE3 _mutex_wait_scheme         1       Mutex wait scheme
      3575   DF7 _kgx_latches               1024    # of mutex latches if CAS is not supported.
7 rows selected.
--//作者写的缺少我写的其他信息。

--//找一个参数optimizer_index_cost_adj测试看看。
SYS@book> @ hidez ^optimizer_index_cost_adj
NAME                                     DESCRIPTION                     DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES ISSYS_MOD
---------------------------------------- ------------------------------- ------------- ------------- ------------ ----- ---------
optimizer_index_cost_adj                 optimizer index cost adjustment TRUE          100           100          TRUE  IMMEDIATE

SYS@book> alter session set optimizer_index_cost_adj=99;
Session altered.

SYS@book> @ hidez ^optimizer_index_cost_adj
NAME                                     DESCRIPTION                     DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES ISSYS_MOD
---------------------------------------- ------------------------------- ------------- ------------- ------------ ----- ---------
optimizer_index_cost_adj                 optimizer index cost adjustment TRUE          99            100          TRUE  IMMEDIATE

SYS@book> alter system set optimizer_index_cost_adj=99;
System altered.

SYS@book> @ hidez ^optimizer_index_cost_adj
NAME                                     DESCRIPTION                     DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES ISSYS_MOD
---------------------------------------- ------------------------------- ------------- ------------- ------------ ----- ---------
optimizer_index_cost_adj                 optimizer index cost adjustment TRUE          99            99           TRUE  IMMEDIATE

SYS@book> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.

SYS@book> startup
ORACLE instance started.
Total System Global Area  805306280 bytes
Fixed Size                  9691048 bytes
Variable Size             473956352 bytes
Database Buffers          314572800 bytes
Redo Buffers                7086080 bytes
Database mounted.
Database opened.

SYS@book> @ hidez ^optimizer_index_cost_adj
NAME                     DESCRIPTION                     DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES ISSYS_MOD
------------------------ ------------------------------- ------------- ------------- ------------ ----- ---------
optimizer_index_cost_adj optimizer index cost adjustment FALSE         99            99           TRUE  IMMEDIATE

SYS@book> show spparameter optimizer_index_cost_adj
SID      NAME                          TYPE                           VALUE
-------- ----------------------------- ------------------------------ ----------------------------
*        optimizer_index_cost_adj      integer                        99

--//只有写入spfile文件，重启才能判断DEFAULT_VALUE是否是true或是false。
SYS@book> alter system reset optimizer_index_cost_adj sid='*';
System altered.

--//结合吸收tpt的pd2.sql脚本，修改如下：
$ cat hidez.sql
col name format a40
col description format a66
col session_value format a22
col default_value format a22
col system_value format a22

select
   a.indx + 1 num,
   to_char(a.indx + 1, 'XXXX') n_hex,
   a.ksppinm  name,
   a.ksppdesc DESCRIPTION,
   b.ksppstdf DEFAULT_VALUE,
   b.ksppstvl SESSION_VALUE,
   c.ksppstvl SYSTEM_VALUE,
   DECODE (BITAND (a.ksppiflg / 256, 1), 1, 'TRUE', 'FALSE')  ISSES_MODIFIABLE,
   DECODE
       (
          BITAND (a.ksppiflg / 65536, 3)
         ,1, 'IMMEDIATE'
         ,2, 'DEFERRED'
         ,3, 'IMMEDIATE'
         ,'FALSE'
       ) ISSYS_MODIFIABLE
from x$ksppi a, x$ksppcv b, x$ksppsv c
where a.indx = b.indx
 and a.indx = c.indx
-- and lower(a.ksppinm) like lower('%&1%')
--escape '\'
and regexp_like (lower(a.ksppinm)||' '||lower(a.ksppdesc) ,lower('&1')
--and regexp_like (lower(a.ksppinm) ,lower('&1')
)
order by 1;

SYS@book> @ hidez mutex
       NUM N_HEX NAME                      DESCRIPTION                                                  DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES ISSYS_MOD
---------- ----- ------------------------- ------------------------------------------------------------ ------------- ------------- ------------ ----- ---------
      1158   486 _kspmxs_upgrade_mutexes   KSPMXS upgrade mutex                                         TRUE          64            64           FALSE FALSE
      3520   DC0 _kqr_hot_copy_sample_size number of mutex gets between periodic checks for hot objects TRUE          1000000       1000000      FALSE IMMEDIATE
      3521   DC1 _kqr_hot_copy_sleep_limit threshold of mutex sleeps to enable object hot copies        TRUE          500           500          FALSE IMMEDIATE
      3553   DE1 _mutex_wait_time          Mutex wait time                                              TRUE          1             1            FALSE IMMEDIATE
      3554   DE2 _mutex_spin_count         Mutex spin count                                             TRUE          255           255          FALSE IMMEDIATE
      3555   DE3 _mutex_wait_scheme        Mutex wait scheme                                            TRUE          2             2            FALSE IMMEDIATE
      3575   DF7 _kgx_latches              # of mutex latches if CAS is not supported.                  TRUE          1024          1024         FALSE FALSE
7 rows selected.

SYS@book> @ hidez ^_mutex
       NUM N_HEX NAME               DESCRIPTION       DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES ISSYS_MOD
---------- ----- ------------------ ----------------- ------------- ------------- ------------ ----- ---------
      3553   DE1 _mutex_wait_time   Mutex wait time   TRUE          1             1            FALSE IMMEDIATE
      3554   DE2 _mutex_spin_count  Mutex spin count  TRUE          255           255          FALSE IMMEDIATE
      3555   DE3 _mutex_wait_scheme Mutex wait scheme TRUE          2             2            FALSE IMMEDIATE