ASM parameters and underscore parameters

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;