Oracle 下查看隐含参数


可以通过以下方式查询Oracle的隐含参数：

set linesize 132
col name format a30
col value format a25
select
x.ksppinm "name",
y.ksppstvl "value",
y.ksppstdf "isdefault",
decode(bitand(y.ksppstvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE') ismod,
decode(bitand(y.ksppstvf,2),2,'TRUE','FALSE') isadj
from
sys.x$ksppi x,
sys.x$ksppcv y
where
x.inst_id = userenv('Instance') and
y.inst_id = userenv('Instance') and
x.indx = y.indx and
x.ksppinm like '%_&par%'
order by
translate(x.ksppinm, ' _', ' ')
/

测试：

16:22:11 SYS@ test1 >set linesize 132

16:22:27 SYS@ test1 >col name format a30

16:22:27 SYS@ test1 >col value format a25
16:22:27 SYS@ test1 >select
16:22:27   2  x.ksppinm "name",
16:22:27   3  y.ksppstvl "value",
16:22:27   4  y.ksppstdf "isdefault",
16:22:27   5  decode(bitand(y.ksppstvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE') ismod,
16:22:27   6  decode(bitand(y.ksppstvf,2),2,'TRUE','FALSE') isadj
16:22:27   7  from
16:22:27   8  sys.x$ksppi x,
16:22:27   9  sys.x$ksppcv y
16:22:27  10  where
16:22:27  11  x.inst_id = userenv('Instance') and
16:22:27  12  y.inst_id = userenv('Instance') and
16:22:27  13  x.indx = y.indx and
16:22:27  14  x.ksppinm like '%_&par%'
16:22:27  15  order by
16:22:27  16  translate(x.ksppinm, ' _', ' ')
16:22:27  17  /
Enter value for par: optim
old  14: x.ksppinm like '%_&par%'
new  14: x.ksppinm like '%_optim%'
name                           value                     isdefault ISMOD      ISADJ
------------------------------ ------------------------- --------- ---------- -----
_add_col_optim_enabled         TRUE                      TRUE      FALSE      FALSE
_aggregation_optimization_sett 0                         TRUE      FALSE      FALSE
ings
_db_file_optimizer_read_count  8                         TRUE      FALSE      FALSE
_db_noarch_disble_optim        FALSE                     TRUE      FALSE      FALSE
_disable_cell_optimized_backup FALSE                     TRUE      FALSE      FALSE
s
_disable_sample_io_optim       FALSE                     TRUE      FALSE      FALSE
_drop_table_optimization_enabl TRUE                      TRUE      FALSE      FALSE
ed
_enable_NUMA_optimization      FALSE                     TRUE      FALSE      FALSE
_log_max_optimize_threads      128                       TRUE      FALSE      FALSE
object_cache_optimal_size      102400                    TRUE      FALSE      FALSE
_optim_adjust_for_part_skews   TRUE                      TRUE      FALSE      FALSE
name                           value                     isdefault ISMOD      ISADJ
------------------------------ ------------------------- --------- ---------- -----
_optim_dict_stats_at_db_cr_upg TRUE                      TRUE      FALSE      FALSE
_optim_enhance_nnull_detection TRUE                      TRUE      FALSE      FALSE
_optimizer_adaptive_cursor_sha TRUE                      TRUE      FALSE      FALSE
ring

创建查询隐含参数视图：

CREATE VIEW all_parameters
AS
     SELECT i.ksppinm name,
            i.ksppdesc description,
            CV.ksppstvl VALUE,
            CV.ksppstdf isdefault,
            DECODE (BITAND (CV.ksppstvf, 7),
                    1, 'MODIFIED',
                    4, 'SYSTEM_MOD',
                    'FALSE')
               ismodified,
            DECODE (BITAND (CV.ksppstvf, 2), 2, 'TRUE', 'FALSE') isadjusted
       FROM sys.x$ksppi i, sys.x$ksppcv CV
      WHERE     i.inst_id = USERENV ('Instance')
            AND CV.inst_id = USERENV ('Instance')
            AND i.indx = CV.indx
            AND i.ksppinm LIKE '/_%' ESCAPE '/'
   ORDER BY REPLACE (i.ksppinm, '_', '')

测试：

15:47:07 SYS@ orcl> select name,value from all_parameters;

NAME                                     VALUE
---------------------------------------- --------------------------------------------------
_xsolapi_sql_minus_threshold             1000
_xsolapi_sql_optimize                    TRUE
_xsolapi_sql_prepare_stmt_cache_size     16
_xsolapi_sql_remove_columns              TRUE
_xsolapi_sql_result_set_cache_size       32
_xsolapi_sql_symmetric_predicate         TRUE
_xsolapi_sql_top_dimension_hints
_xsolapi_sql_top_measure_hints
_xsolapi_sql_use_bind_variables          TRUE
_xsolapi_stringify_order_levels          FALSE
_xsolapi_support_mtm                     FALSE
......

15:47:25 SYS@ orcl>select name,value from all_parameters where name like '%resetlog%';

NAME                                     VALUE
---------------------------------------- --------------------------------------------------
_allow_resetlogs_corruption              FALSE
_no_recovery_through_resetlogs           FALSE