Search

Top 60 Oracle Blogs

Recent comments

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: