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.

Note that this year’s only Advanced Oracle Troubleshooting class takes place in the end of April/May 2014, so sign up now if you plan to attend this year!

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

3 Responses to Why does Oracle parameter count change during session lifetime?

  1. Pingback: Back to work links « I’m just a simple DBA on a complex production system

  2. 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 10.2.0.3).

    regards, Tony van Esch

  3. Pingback: Articles about _unnest_subquery volume 1 « Article Directory

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>