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