A simple interview question

Question: How to check instance parameter values in Oracle?

Answer: show parameter xyz

WRONG!

Answer: select value from v$parameter where name = ‘xyz’

WRONG!

These commands show the session level parameter values, which are separate from instance level parameters:

SQL> show parameter session_cached_cursors

NAME                                 TYPE        VALUE
------------------------------------ ----------- --------------------------------
session_cached_cursors               integer     20

SQL> select value from v$parameter where name = 'session_cached_cursors';

VALUE
---------------------------------------------------------------------------------
20

V$SYSTEM_PARAMETER is the view which shows instance level parameters (and these are what all new sessions inherit)


SQL> select value from v$system_parameter where name = 'session_cached_cursors';

VALUE
--------------------------------------------------------------------------------
0

In this case the difference was due a logon trigger whcih did set session_cached_cursors based on v$session program name.

Note that there is another not widely known gotcha with parameters – multivalue parameters:

There are few parameters which can accept and hold multiple values at a time. For example, utl_file_dir and service_names.

From the following it seems that in my instance there’s a single parameter with multiple comma separated values:

SQL> select value from v$system_parameter where name = 'service_names';

VALUE
------------------------------------------------------------------------------------
WEB, HR, OE, MFG, ADMIN

Actually these values are treated as separate parameter values (of the same parameter name) in V$PARAMETER2 and V$SYSTEM_PARAMETER2:

SQL> select value from v$system_parameter2 where name = 'service_names';

VALUE
------------------------------------------------------------------------------------
WEB
HR
OE
MFG
ADMIN

So, in addition to well known V$PARAMETER, there’s V$PARAMETER2, V$SYSTEM_PARAMETER and V$SYSTEM_PARAMETER2 – and only V$SYSTEM_PARAMETER(2) show you the instance parameters.

And there’s more – V$SPPARAMETER, V$OBSOLETE_PARAMETER and V$PARAMETER_VALID_VALUES (introduced in 10.2) which all can be handy for diagnosing problems and for grilling people at interviews (*evil grin*).

Unlike all my other marvellous posts, this one doesn’t actually have a point – but hey it’s Friday evening in Singapore (or Saturday in Australia) already, so I wish you a happy, parameter-free weekend ;-)

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.

2 Responses to A simple interview question

  1. TedC says:

    This is exactly the reason why so many developers and others find Oracle so annoying. Why have multiple layers to get to an answer and why obfuscate something so simple. “Hey Oracle” call it what it is…. Excellent post.

  2. Amir Hameed says:

    Hi Tanel,
    I have noticed that if I log into a running Oracle instance and run “show parameter db_cache_size”, Oracle only displays the non-doubles undercore value. But if I stopped the instance, start it again and by staying in the same session, I issue the same command, Oracle will show the double underscore values:

    SQL> conn / as sysdba
    Connected.
    SQL> show parameter db_cache_size

    NAME TYPE VALUE
    ———————————— ———– ——————————
    db_cache_size big integer 1G

    SQL> shutdown immediate
    Database closed.
    Database dismounted.
    SQL>
    SQL> startup
    ORACLE instance started.

    Total System Global Area 1.0737E+10 bytes
    Fixed Size 2046784 bytes
    Variable Size 7314867392 bytes
    Database Buffers 3405774848 bytes
    Redo Buffers 14729216 bytes
    Database mounted.
    Database opened.
    SQL> show parameter db_cache_size

    NAME TYPE VALUE
    ———————————— ———– ——————————
    __db_cache_size big integer 3248M
    db_cache_size big integer 1G

    Somehow, the session that brings up an Oracle instance pulls these values from regareless of whether SPFILE is used or not (I have tested both scenarios).

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>