Why does Oracle parameter count change during session lifetime?

I was once asked a question, why does Oracle change its parameter count during session lifetime?

The question arose from the following observation that v$parameter shows more parameters after you adjust some hidden parameter value:

SQL>
SQL> select count(*) from v$parameter;

  COUNT(*)
----------
       288

SQL>
SQL> alter session set "_complex_view_merging"=false;

Session altered.

SQL> select count(*) from v$parameter;

  COUNT(*)
----------
       289

Looks like the parameter count was just increased by one!

“It sure seems like the hidden parameter don’t exist before they are actually modified”:

SQL> show parameter _unnest_subquery   (no rows returned)
SQL>
SQL>
SQL> alter session set "_unnest_subquery"=false;

Session altered.

SQL>
SQL> show parameter _unnest_subquery

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_unnest_subquery                     boolean     FALSE
SQL>

So it seems like Oracle was “creating” the hidden parameter when it was modified.

This is not the reality though. All parameters for session are created during session startup and stored in shared pool.

The answer lies in the view text of GV$PARAMETER view. This example is from an 11g database, older versions like 9.2 do have less checks in the where clause. Note that the output is manually formatted for better readability:

SQL> select view_definition text from v$fixed_View_definition where view_name = 'GV$PARAMETER';

TEXT
----------------------------------------------------------------------------------------------------
select
   x.inst_id,x.indx+1,ksppinm,ksppity,ksppstvl, ksppstdvl, ksppstdf,
   decode(bitand(ksppiflg/256,1),1,'TRUE','FALSE'),
   decode(bitand(ksppiflg/65536,3),1,'IMMEDIATE',2,'DEFERRED',3,'IMMEDIATE','FALSE'),
   decode(bitand(ksppiflg,4),4,'FALSE',decode(bitand(ksppiflg/65536,3), 0, 'FALSE', 'TRUE')),
   decode(bitand(ksppstvf,7),1,'MODIFIED',4,'SYSTEM_MOD','FALSE'),
   decode(bitand(ksppstvf,2),2,'TRUE','FALSE'),
   decode(bitand(ksppilrmflg/64, 1), 1, 'TRUE', 'FALSE'),
   ksppdesc, ksppstcmnt, ksppihash
from
   x$ksppi x, x$ksppcv y
where
   (x.indx = y.indx)
and (
      (translate(ksppinm,'_','#') not like '##%')
   and (
               (translate(ksppinm,'_','#') not like '#%')
            or (ksppstdf = 'FALSE')
            or (bitand(ksppstvf,5) > 0)
   )
)

The bold section above says that do not display any undocumented parameters (starting with underscore) unless they are:

1. non-default (ksppstdf = false)
2. OR they have been modified since session start (either bit 1 or 4 of ksppstvf bitmap is set)

So, thanks to this condition, undocumented parameters appear in the V$PARAMETER view only when they are non-default or have been modified during session lifetime (even if they’re modified back to the original value).

Note that on Oracle 9.2 you may experience a bug where the ksppstdf variable is not set even if you change the parameter from its default.

Finally, as the show parameter sqlplus command just issues a select against v$parameter under the hood, it is dependent on v$parameter’s behavior – it doesn’t show you undocumented parameters unless their value has been changed.