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> select count(*) from v$parameter;


SQL> alter session set "_complex_view_merging"=false;

Session altered.

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


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> alter session set "_unnest_subquery"=false;

Session altered.

SQL> show parameter _unnest_subquery

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_unnest_subquery                     boolean     FALSE

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';

   x.inst_id,x.indx+1,ksppinm,ksppity,ksppstvl, ksppstdvl, ksppstdf,
   decode(bitand(ksppiflg,4),4,'FALSE',decode(bitand(ksppiflg/65536,3), 0, 'FALSE', 'TRUE')),
   decode(bitand(ksppilrmflg/64, 1), 1, 'TRUE', 'FALSE'),
   ksppdesc, ksppstcmnt, ksppihash
   x$ksppi x, x$ksppcv y
   (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.

NB! If you want to move to the "New World" - and benefit from the awesomeness of Hadoop, without having to re-engineer your existing applications - check out Gluent, my new startup that will make history! ;-)

This entry was posted in Oracle and tagged , . Bookmark the permalink.

One Response to Why does Oracle parameter count change during session lifetime?

  1. Tony says:

    Hi Tanel,

    I ran into this behaviour with ‘show parameter’ and underscore parameters.

    when I startup an instance and I run show parameter from the same sqlplus session, I get all parameters with that substring including underscore parameters (version

    regards, Tony van Esch

Leave a Reply

Your email address will not be published. Required fields are marked *