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

[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