How to find optimizer and database underscore/hidden parameter in Oracle

This article is part of Oracle database administration tutorials and has query,explanation and examples on how to find the  underscore/hidden parameters for Optimizer and in general any oracle database underscore parameters

Query to list all the underscore parameter with Description

set lines 132
column name format a45
column description format a50
select
ksppinm name,
ksppdesc description
from
x$ksppi
where
substr(ksppinm,1,1) = '_'
order by
1,2;

How to find values of all  underscore parameter

column name format a45
column value format a10
column UPDATE_COMMENT format a30
SELECT x.ksppinm name, y.ksppstvl VALUE,
ksppstcmnt update_comment FROM x$ksppi x,
x$ksppcv y WHERE x.inst_id = userenv('Instance') AND y.inst_id = userenv('Instance') AND
x.indx = y.indx AND substr(x.ksppinm,1,1) = '_' ;

 

SELECT
x.ksppinm name,
y.ksppstvl VALUE,
decode(ksppity,
1, 'BOOLEAN',
2, 'STRING',
3, 'INTEGER',
4, 'PARAMETER FILE',
5, 'RESERVED',
6, 'BIG INTEGER',
'UNKNOWN') typ,
decode(ksppstdf,
'TRUE', 'DEFAULT VALUE',
'FALSE', 'INIT.ORA') isdefault,
decode(bitand(ksppiflg / 256, 1),
1, 'IS_SESS_MOD(TRUE)',
'FALSE') isses_modifiable,
decode(bitand(ksppiflg / 65536, 3),
1, 'MODSYS(NONDEFERED)',
2, 'MODSYS(DEFERED)',
3, 'MODSYS(*NONDEFERED*)',
'FALSE') issys_modifiable,
decode(bitand(ksppstvf, 7),
1, 'MODIFIED_BY(SESSION)',
4, 'MODIFIED_BY(SYSTEM)',
'FALSE') is_modified,
decode(bitand(ksppstvf, 2),
2, 'ORA_STARTUP_MOD(TRUE)',
'FALSE') is_adjusted,
ksppdesc description,
ksppstcmnt update_comment
FROM x$ksppi x,
x$ksppcv y
WHERE x.inst_id = userenv('Instance')
AND y.inst_id = userenv('Instance')
AND x.indx = y.indx
AND substr(x.ksppinm,1,1) = '_'

How to find optimizer underscore parameter

SELECT x.ksppinm name, y.ksppstvl VALUE,
ksppstcmnt update_comment FROM x$ksppi x,
x$ksppcv y WHERE x.inst_id = userenv('Instance') AND y.inst_id = userenv('Instance') AND
x.indx = y.indx AND x.ksppinm LIKE '%optim%%';

 

SELECT
x.ksppinm name,
y.ksppstvl VALUE,
decode(ksppity,
1, 'BOOLEAN',
2, 'STRING',
3, 'INTEGER',
4, 'PARAMETER FILE',
5, 'RESERVED',
6, 'BIG INTEGER',
'UNKNOWN') typ,
decode(ksppstdf,
'TRUE', 'DEFAULT VALUE',
'FALSE', 'INIT.ORA') isdefault,
decode(bitand(ksppiflg / 256, 1),
1, 'IS_SESS_MOD(TRUE)',
'FALSE') isses_modifiable,
decode(bitand(ksppiflg / 65536, 3),
1, 'MODSYS(NONDEFERED)',
2, 'MODSYS(DEFERED)',
3, 'MODSYS(*NONDEFERED*)',
'FALSE') issys_modifiable,
decode(bitand(ksppstvf, 7),
1, 'MODIFIED_BY(SESSION)',
4, 'MODIFIED_BY(SYSTEM)',
'FALSE') is_modified,
decode(bitand(ksppstvf, 2),
2, 'ORA_STARTUP_MOD(TRUE)',
'FALSE') is_adjusted,
ksppdesc description,
ksppstcmnt update_comment
FROM x$ksppi x,
x$ksppcv y
WHERE x.inst_id = userenv('Instance')
AND y.inst_id = userenv('Instance')
AND x.indx = y.indx
AND x.ksppinm LIKE '%optim%';

How the find values of a particular Underscore parameter

column name format a45
column value format a10
column UPDATE_COMMENT format a30

SELECT x.ksppinm name, y.ksppstvl VALUE,
ksppstcmnt update_comment FROM x$ksppi x,
x$ksppcv y WHERE x.inst_id = userenv('Instance') AND y.inst_id = userenv('Instance') AND
x.indx = y.indx AND x.ksppinm = '&1';

 

SELECT
x.ksppinm name,
y.ksppstvl VALUE,
decode(ksppity,
1, 'BOOLEAN',
2, 'STRING',
3, 'INTEGER',
4, 'PARAMETER FILE',
5, 'RESERVED',
6, 'BIG INTEGER',
'UNKNOWN') typ,
decode(ksppstdf,
'TRUE', 'DEFAULT VALUE',
'FALSE', 'INIT.ORA') isdefault,
decode(bitand(ksppiflg / 256, 1),
1, 'IS_SESS_MOD(TRUE)',
'FALSE') isses_modifiable,
decode(bitand(ksppiflg / 65536, 3),
1, 'MODSYS(NONDEFERED)',
2, 'MODSYS(DEFERED)',
3, 'MODSYS(*NONDEFERED*)',
'FALSE') issys_modifiable,
decode(bitand(ksppstvf, 7),
1, 'MODIFIED_BY(SESSION)',
4, 'MODIFIED_BY(SYSTEM)',
'FALSE') is_modified,
decode(bitand(ksppstvf, 2),
2, 'ORA_STARTUP_MOD(TRUE)',
'FALSE') is_adjusted,
ksppdesc description,
ksppstcmnt update_comment
FROM x$ksppi x,
x$ksppcv y
WHERE x.inst_id = userenv('Instance')
AND y.inst_id = userenv('Instance')
AND x.indx = y.indx
AND x.ksppinm = '&1';

How to find background process information

select nm, max(description) descript
from
(select regexp_replace( name,'[0-9a-z]','#') nm, description
from v$bgprocess)
group by nm
order by nm;

How to find a session with high archive logs

SELECT s.sid, s.serial#, s.username, s.program, i.block_changes
FROM v$session s, v$sess_io i
WHERE s.sid = i.sid
ORDER BY 5 desc;

How to find the long running queries

set linesize 1000
select
OPNAME,
sid,SOFAR/TOTALWORK*100,
to_char(start_time,'dd-mon-yy hh:mi') started,
elapsed_seconds/60,time_remaining/60
from
v$session_longops
where
sid =&sid