View Hidden Parameter or UnDocumented Parameter Information from Oracle Database ( _gby_hash_aggregation_enabled )

Well most of the time we don't care about what are the hidden parameters a database has and what it actually does and of course oracle kept it hidden for a purpose.

Any modifications done to the hidden parameters without suggestions from Oracle support may or may not result in a disaster of your database.

So now lets see how we can fetch the information for Hidden Parameters.

**************************************** Step By Step Procedure ******************************************************

#########################################
# 1) Using V$parameter
#########################################

Usually we check in V$parameter for database parameters. Most of the hidden parameters cant be seen in V$parameter

Select name,VALUE,ISDEFAULT,ISSES_MODIFIABLE,ISSYS_MODIFIABLE from v$parameter where name ='_gby_hash_aggregation_enabled';

no rows selected


=====================================================================================================================

#########################################
# 2) How to check Hidden Parameters
#########################################

set lines 200
col Parameter for a40
col "Session Value" for a25
col "Instance Value" for a25

SELECT a.ksppinm "Parameter",b.ksppstvl "Session Value",c.ksppstvl "Instance Value" FROM   x$ksppi a,x$ksppcv b,x$ksppsv c WHERE  a.indx = b.indx
AND    a.indx = c.indx AND    a.ksppinm LIKE '/_gby%hash%' escape '/';

Parameter                                Session Value             Instance Value
---------------------------------------- ------------------------- -------------------------
_gby_hash_aggregation_enabled            TRUE                      TRUE

=====================================================================================================================
 Now we can see the hidden parameters Set at Database level.

 I didnt get satisfied by just knowing the values set at database level. Then i want to know whether the parameter can be modified at session or system level
=====================================================================================================================

#########################################
# 3) Hidden Parameters Information ( Detailed )
#########################################

set lines 200
col name for a33
col value for a20
col description for a50
col IS_SESSION_MODIFIABLE for a25
col IS_SYSTEM_MODIFIABLE for a25
col IS_MODIFIED for a25

select par.ksppinm name,val.ksppstvl value,par.ksppity type, val.ksppstdf is_default,decode(bitand(par.ksppiflg/256,1), 1,'True', 'False' ) is_session_modifiable,decode(bitand(par.ksppiflg/65536,3), 1,'Immediate', 2,'Deferred' , 3,'Immediate', 'False' ) is_system_modifiable,par.ksppdesc description
from x$ksppi par, x$ksppsv val where par.indx = val.indx  and par.ksppinm = '_gby_hash_aggregation_enabled';

NAME                              VALUE                      TYPE IS_DEFAUL IS_SESSION_MODIFIABLE     IS_SYSTEM_MODIFIABLE      DESCRIPTION
--------------------------------- -------------------- ---------- --------- ------------------------- ------------------------- --------------------------------------------------
_gby_hash_aggregation_enabled     TRUE                          1 TRUE      True                      Immediate                 enable group-by and aggregation using hash scheme

Well i'm happy to get this info after searching for quite some time. Happy Learning....

=====================================================================================================================
 Comments Are Always welcome
=====================================================================================================================