Oracle 下查看隐含参数
可以通过以下方式查询Oracle的隐含参数：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
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
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
|
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
|
创建查询隐含参数视图：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
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;
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
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%';
|
1
2
3
4
|
NAME VALUE
---------------------------------------- --------------------------------------------------
_allow_resetlogs_corruption FALSE
_no_recovery_through_resetlogs FALSE
|
本文转自 客居天涯 51CTO博客，原文链接：http://blog.51cto.com/tiany/1367658，如需转载请自行联系原作者
