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_BASED | Setting 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;