Display Hidden Parameters

Redneck

Dress Code

Oracle tuning

Oracle training

Oracle support

Remote Oracle

Oracle Tips by Burleson

Exercise 8A – Display Hidden Parameters

In this exercise, you will modify the query below to display lasses of hidden parameters.

This exercise will have two challenges, and your task is to modify the script below to only display those undocumented parameters that are likely to be related to your problem.

column parm_name   heading 'Parameter|Name'        format a40
column current_val heading 'Current|Value'         format a10
column default_val heading 'Instance|Value'        format a20
column descr       heading 'Parameter|Description' format a80

ttitle 'List of undocumented Oracle parameters'

select
   ksppinm               parm_name,
   session_tab.ksppstvl current_val,
   instance_tab.ksppstvl default_val,
   ksppdesc              descr
from
   x$ksppi               parm_tab,
   x$ksppcv              session_tab,
   x$ksppsv              instance_tab
where
   parm_tab.indx = session_tab.indx
and
   session_tab.indx = instance_tab.indx
and
   substr(parm_tab.ksppinm,1,1) = '_'
and
   -- remove rows where hidden parm is the same as v$ parm
   not exists
     (select name from v$parameter
      where
      name = substr(ksppinm,2,length(ksppinm))
     )
order by
   ksppinm
;

PART 1 – Display Locking parameters

You suspect that your Oracle database is encountering a problem with lock contention, and you want to get a list of all lock-related parameters. You suspect that the parameters names will contain the strings “lock”, “locks” or “enqueue”.

Your finished query will look like this:

Tue Aug 13                                                             page1

                                    Locking
                     List of undocumented Oracle parameters

Parameter                                Current    Instance
Name                                     Value      Value
---------------------------------------- ---------- --------------------        Parameter
Description
------------------------------------------------------------------------------_controlfile_enqueue_timeout             900        900
control file enqueue timeout in seconds

_disable_file_locks                      FALSE      FALSE
disable file locks for control, data, redo log files

_dyn_sel_est_num_blocks                  30         30
number of blocks for dynamic selectivity estimation

_enqueue_debug_multi_instance FALSE FALSE
debug enqueue multi instance

_enqueue_hash 375 375
enqueue hash table length

_enqueue_hash_chain_latches 1 1
enqueue hash chain latches

_enqueue_locks 2230 2230
locks for managed enqueues

_gc_releasable_locks 0 0
number of global cache locks (DFS)

_inst_locking_period 5 5
period an instance can retain a newly acquired level1 bitmap

_lm_locks 12000 12000
number of locks configured for the lock manager

_log_blocks_during_backup TRUE TRUE
log block images when changed during backup

_passwordfile_enqueue_timeout 900 900
password file enqueue timeout in seconds

_pcm_shadow_locks
number of pcm shadow locks to be allocated

_row_cache_instance_locks 100 100
number of row cache instance locks

14 rows selected.

PART 2 – Display DML parameters

You want to get a list of those undocumented parameters that relate to DML behavior. You suspect that all DML parameters will have the strings “walk”, “freelist”, “insert” or “dml” in the parameter name.

Your completed query should look like this:

DML
List of undocumented Oracle parameters

Parameter                                Current    Instance
Name                                     Value      Value
---------------------------------------- ---------- --------------------
Parameter
Description
------------------------------------------------------------------------------
_domain_index_dml_batch_size             200        200
maximum number of rows for one call to domain index dml routines

_insert_enable_hwm_brokered TRUE TRUE
during parallel inserts high water marks are brokered

_pdml_gim_sampling 5000 5000
control separation of global index maintenance for PDML

_pdml_gim_staggered FALSE FALSE
slaves start on different index when doing index maint

_pdml_slaves_diff_part TRUE TRUE
slaves start on different partition when doing index maint

_release_insert_threshold 5 5
maximum number of unusable blocks to unlink from freelist

_walk_insert_threshold 0 0
maximum number of unusable blocks to walk across freelist

7 rows selected.

ANSWER

PART 1

ttitle 'List of undocumented Oracle parameters'

select
   ksppinm               parm_name,
   session_tab.ksppstvl current_val,
   instance_tab.ksppstvl default_val,
   ksppdesc              descr
from
   x$ksppi               parm_tab,
   x$ksppcv              session_tab,
   x$ksppsv              instance_tab
where
   parm_tab.indx = session_tab.indx
and
   session_tab.indx = instance_tab.indx
and
   substr(parm_tab.ksppinm,1,1) = '_'
and
   -- remove rows where hidden parm is the same as v$ parm
   not exists
     (select name from v$parameter
      where
      name = substr(ksppinm,2,length(ksppinm))
     )
and
(
ksppinm like '%enqueue%'
or
ksppdesc like '%lock%'
or
ksppinm like '%locks%'
or
ksppinm like '%dml%'
)
order by
   ksppinm
;

PART 2

ttitle 'List of undocumented Oracle parameters'

select
   ksppinm               parm_name,
   session_tab.ksppstvl current_val,
   instance_tab.ksppstvl default_val,
   ksppdesc              descr
from
   x$ksppi               parm_tab,
   x$ksppcv             session_tab,
   x$ksppsv              instance_tab
where
   parm_tab.indx = session_tab.indx
and
   session_tab.indx = instance_tab.indx
and
   substr(parm_tab.ksppinm,1,1) = '_'
and
   -- remove rows where hidden parm is the same as v$ parm
   not exists
     (select name from v$parameter
      where
      name = substr(ksppinm,2,length(ksppinm))
     )
and
(
ksppinm like '%walk%'
or
ksppdesc like '%freelist%'
or
ksppinm like '%insert%'
or
ksppinm like '%dml%'
)
order by
   ksppinm
;

For more details, see the "Easy Oracle Series" a set of books especially designed by Oracle experts to get you started fast with Oracle database technology.