Hungry DBA .com for DBAs who like food
Useful Oracle Parameter Settings
 
 

Parameter Name

Details

_B_TREE_BITMAP_PLANS

This parameter’s default value changed from FALSE in Oracle 8i to TRUE in Oracle 9i.

This can lead to an undesirable impact on performance thus this should be set to FALSE.

_INDEX_JOIN_ENABLED

This parameter’s default value changed from FALSE in Oracle 8i to TRUE in Oracle 9i.

This can lead to an undesirable impact on performance thus this should be set to FALSE.

_KGL_LARGE_HEAP_WARNING_THRESHOLD

This parameter was introduced in 10G Release 2.  This will eliminate the following error message:
Memory Notification: Library Cache Object loaded into SGA
Heap size XXXXK exceeds notification threshold

For example set it as follows:

ALTER SYSTEM SET "_KGL_LARGE_HEAP_WARNING_THRESHOLD" = 8388608 SCOPE = SPFILE;

_OPTIM_PEEK_USER_BINDS

This is a new parameter in Oracle 9i. However, we have found that it can cause an undesirable impact on performance thus this should be set to FALSE (see "bind variable peeking").

QUERY_REWRITE_ENABLED

In order to make use of the Function Based Indexes this parameter must be set to TRUE.

OPTIMIZER_SECURE_VIEW_MERGING

This parameter was introduced in 10G Release 2.  Setting this parameter to FALSE is required otherwise it is possible for the optimizer to create different execution plans for identical statements on identical objects issued from different user schemas.

_OPTIMIZER_CONNECT_BY_COST_BASEDSetting this parameter to FALSE is required for a workaround to the Oracle error ORA-600 [qkacon:FJswrwo].

_OPTIMIZER_JOIN_ELIMINATION_ENABLED

This parameter is required as a workaround to an Oracle bug in 10.2.0.3

 
The following SQL can be used to find the values of all Oracle hidden parameters in your database:

select x.inst_id,x.indx+1,ksppinm,ksppity,ksppstvl,ksppstdf,
decode(bitand(ksppiflg/256,1),1,'TRUE','FALSE'),
decode(bitand(ksppiflg/65536,3),1,'IMMEDIATE',2,'DEFERRED',3,'IMMEDIATE','FALSE'),
decode(bitand(ksppstvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE'),
decode(bitand(ksppstvf,2),2,'TRUE','FALSE'),
ksppdesc
from x$ksppi x,
x$ksppcv y
where (x.indx = y.indx)
/*and (translate(ksppinm,'_','#') not like '#%'
or (translate(ksppinm,'_','#') like '#%'and ksppstdf = 'TRUE'))*/
order by 3;