Thursday, January 1, 2009

How to select hidden initialization parameters

The following is a script (must be connected as sys) that helped me view the effective optimizer settings on a Oracle 10.2 database.

Hence a lot of parameters are hidden (parameter name starts with ‘_’ ) you cannot retrieve them using the V$PARAMETER View. With Oracle 10G about 80 Parameters (hidden + not hidden) are defined for example matching the pattern ‘%optim%’;

The - Script is a

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%';

Talking about hidden parameters, I must include the following section:

!! DO NOT CHANGE HIDDEN PARAMETERS WITHOUT THE RECOMMENDATION OF ORACLE SUPPORT !!

No comments:

Post a Comment