OraQA

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 short variant of Pete Finnigan’s hidden parameter Script:
http://www.petefinnigan.com/check_parameter.sql

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 !!

HTH
Karl

PS.: Thanks Pete for this great hidden parameter script!

Filed under Administration, SQL, Tuning. Follow any responses to this entry through this RSS 2.0 feed.