ASM parameters and underscore parameters
http://anuj-singh.blogspot.com/2011/11/oracle-hidden-parameters.html
col "Instance Value" format a20
col parameter format a30
set pagesize 200
select a.ksppinm "Parameter", c.ksppstvl "Instance Value"
from x$ksppi a, x$ksppcv b, x$ksppsv c
where a.indx = b.indx and a.indx = c.indx
and ksppinm like '%asm%'
order by a.ksppinm;
Parameter Instance Value
------------------------------ --------------------
_asm_acd_chunks 1
_asm_allow_only_raw_disks TRUE
_asm_allow_resilver_corruption FALSE
_asm_ausize 1048576
_asm_blksize 4096
_asm_disk_repair_time 14400
_asm_droptimeout 60
_asm_emulmax 10000
_asm_emultimeout 0
_asm_kfdpevent 0
_asm_libraries ufs
_asm_maxio 1048576
_asm_stripesize 131072
_asm_stripewidth 8
_asm_wait_time 18
_asmlib_test 0
_asmsid asm
asm_diskgroups DATA
asm_diskstring /dev/raw/raw1
asm_power_limit 1
20 rows selected.
=====================
set pagesize 200
column parameter format a37
column description format a30 word_wrapped
column "Session Value" format a10
column "Instance Value" format a10
select
a.ksppinm "Parameter",
a.ksppdesc "Description",
b.ksppstvl "Session Value",
c.ksppstvl "Instance Value"
from
x$ksppi a,
x$ksppcv b,
x$ksppsv c
where
a.indx = b.indx
and a.indx = c.indx
and a.ksppinm like '%asm%' escape '\'
order by 1
/
SQL> set pagesize 200
SQL> /
Parameter Description Session Va Instance V
------------------------------------- ------------------------------ ---------- ----------
_asm_acd_chunks initial ACD chunks created 1 1
_asm_allow_only_raw_disks Discovery only raw devices TRUE TRUE
_asm_allow_resilver_corruption Enable disk resilvering for FALSE FALSE
external redundancy
_asm_ausize allocation unit size 1048576 1048576
_asm_blksize metadata block size 4096 4096
_asm_disk_repair_time seconds to wait before 14400 14400
dropping a failing disk
_asm_droptimeout timeout before offlined disks 60 60
get dropped (in 3s ticks)
_asm_emulmax max number of concurrent disks 10000 10000
to emulate I/O errors
_asm_emultimeout timeout before emulation 0 0
begins (in 3s ticks)
_asm_kfdpevent KFDP event 0 0
_asm_libraries library search order for ufs ufs
discovery
_asm_maxio Maximum size of individual I/O 1048576 1048576
request
_asm_stripesize ASM file stripe size 131072 131072
_asm_stripewidth ASM file stripe width 8 8
_asm_wait_time Max/imum time to wait before 18 18
asmb exits
_asmlib_test Osmlib test event 0 0
_asmsid ASM instance id asm asm
asm_diskgroups disk groups to mount DATA DATA
automatically
asm_diskstring disk set locations for /dev/raw/r /dev/raw/r
discovery aw1 aw1
asm_power_limit number of processes for disk 1 1
rebalancing
20 rows selected.
----
for fix control
set pages 5000 lines 200
col name format a30
col cur_val format a20
select i.ksppinm name , v.ksppstvl cur_val, v.ksppstdf default_val, v.ksppstvf
from x$ksppi i, x$ksppcv v
where i.indx = v.indx
and i.ksppinm = '_fix_control';
NAME CUR_VAL DEFAULT_V KSPPSTVF
------------------------------ -------------------- --------- ----------
_fix_control 9550277:OFF FALSE 0
SELECT i.instance_name instance,
-- b.ksppstvl "Session_Value",
c.ksppstvl value,c.ksppstvl/1024/1024 meg,c.ksppstvl/1024/1024/1024 gig,
-- above is instance_value
a.ksppinm "PARAMETER",KSPPDESC "DESCRIPTION"
FROM
x$ksppi a,
x$ksppcv b,
x$ksppsv c,
v$instance i
WHERE
a.indx = b.indx
AND
a.indx = c.indx
AND
(a.ksppinm LIKE '/_/_%' escape '/' or a.ksppinm LIKE 'db_keep_cache_size' or a.ksppinm LIKE 'db_recycle_cache_size')
and a.ksppinm not in ('__oracle_base')
-- and a.ksppinm in ('__shared_pool_size','__db_cache_size','__large_pool_size','__java_pool_size','__streams_pool_size','__pga_aggregate_target','__sga_target','memory_target','_pga_limit_target_perc')
and c.ksppstvl not in ('TRUE','FALSE')
order by 3
/
col DESCRIPTION for a30
col VALUE for a20
col PARAMETER for a30
SELECT i.instance_name instance,
-- b.ksppstvl "Session_Value",
c.ksppstvl value,c.ksppstvl/1024/1024 meg,c.ksppstvl/1024/1024/1024 gig,
-- above is instance_value
a.ksppinm "PARAMETER",KSPPDESC "DESCRIPTION"
FROM
x$ksppi a,
x$ksppcv b,
x$ksppsv c,
v$instance i
WHERE
a.indx = b.indx
AND
a.indx = c.indx
AND
(a.ksppinm LIKE '/_/_%' escape '/' or a.ksppinm LIKE 'db_keep_cache_size' or a.ksppinm LIKE 'db_recycle_cache_size')
and a.ksppinm not in ('__oracle_base')
and c.ksppstvl not in ('TRUE','FALSE')
order by 3
/
===
set linesize 300 pagesize 300
col parameter for a30
col session for a28
col instance for a12
col "Session Value" for a15
col "Instance Value" for a15
col IS_SESSION_MODIFIABLE for a25
col IS_SYSTEM_MODIFIABLE for a25
col description for a70
col comment for a30
col "Default Value" for a15
SELECT a.ksppinm "Parameter",
b.KSPPSTDF "Default Value",
b.ksppstvl "Session Value",
c.ksppstvl "Instance Value",
decode(bitand(a.ksppiflg/256,1),1,'TRUE','FALSE') IS_SESSION_MODIFIABLE,
decode(bitand(a.ksppiflg/65536,3),1,'IMMEDIATE',2,'DEFERRED',3,'IMMEDIATE','FALSE') IS_SYSTEM_MODIFIABLE
FROM x$ksppi a,
x$ksppcv b,
x$ksppsv c
WHERE a.indx = b.indx
AND a.indx = c.indx
AND a.ksppinm LIKE '/_%' escape '/'
and upper(a.ksppinm) like upper('%&1%')
/
===
set linesize 300 pagesize 300
col name for a40
col VALUE for a100
col "THREAD#" for a10
column num noprint
SELECT num, '*' "THREAD#", name, value FROM v$PARAMETER WHERE NUM IN (SELECT num FROM v$parameter WHERE (isdefault = 'FALSE' OR ismodified <> 'FALSE') AND name NOT LIKE 'nls%'
MINUS
SELECT num FROM gv$parameter gvp, gv$instance gvi WHERE num IN (SELECT DISTINCT gvpa.num FROM gv$parameter gvpa, gv$parameter gvpb WHERE gvpa.num = gvpb.num AND gvpa.value <> gvpb.value AND (gvpa.isdefault = 'FALSE' OR gvpa.ismodified <> 'FALSE') AND gvpa.name NOT LIKE 'nls%') AND gvi.inst_id = gvp.inst_id AND (gvp.isdefault = 'FALSE' OR gvp.ismodified <> 'FALSE') AND gvp.name NOT LIKE 'nls%')
UNION
SELECT num, TO_CHAR(thread#) "THREAD#", name, value FROM gv$parameter gvp, gv$instance gvi WHERE num IN (SELECT DISTINCT gvpa.num FROM gv$parameter gvpa, gv$parameter gvpb WHERE gvpa.num = gvpb.num AND gvpa.value <> gvpb.value AND (gvpa.isdefault = 'FALSE' OR gvpa.ismodified <> 'FALSE') AND gvp.name NOT LIKE 'nls%') AND gvi.inst_id = gvp.inst_id AND (gvp.isdefault = 'FALSE' OR gvp.ismodified <> 'FALSE') AND gvp.name NOT LIKE 'nls%' ORDER BY 1, 2;
THREAD# NAME VALUE
---------- ---------------------------------------- ----------------------------------------------------------------------------------------------------
* processes 3000
* sga_max_size 204010946560
* large_pool_size 2684354560
* pga_aggregate_limit 42949672960
* _ksb_restart_policy_times 0, 60, 120, 240
======
VARIABLE BgnSnap NUMBER
VARIABLE EndSnap NUMBER
VARIABLE DID NUMBER
VARIABLE INST_NUMBER number
exec select max(snap_id) -24 into :BgnSnap from dba_hist_snapshot ;
exec select max(snap_id) into :EndSnap from dba_hist_snapshot ;
exec select DBID into :DID from v$database;
exec select INSTANCE_NUMBER into :INST_NUMBER from v$instance ;
set linesize 300 pagesiz 300
col NAME for a45
col BVAL for a20
col EVAL for a20
select e.parameter_name name
, b.value bval
, decode(b.value, e.value, NULL, e.value) eval
from dba_hist_parameter b
, dba_hist_parameter e
where b.snap_id(+) = :BgnSnap
and e.snap_id = :EndSnap
and b.dbid(+) = :DID
and e.dbid = :DID
and b.instance_number(+) = 1
and e.instance_number = 1
and b.parameter_hash(+) = e.parameter_hash
and ( nvl(b.isdefault, 'X') = 'FALSE'
or nvl(b.ismodified,'X') != 'FALSE'
or e.ismodified != 'FALSE'
or nvl(e.value,0) != nvl(b.value,0)
)
and e.parameter_name not like '\_\_%' escape '\'
order by e.parameter_name;
