Oracle Scratchpad

December 28, 2008

timed_os_stats

Filed under: Infrastructure,Statistics — Jonathan Lewis @ 9:20 pm GMT Dec 28,2008

There is a startup parameter called timed_os_statistics, carrying a warning in the manuals that said:

Note: Gathering OS statistics is very expensive. Oracle Corporation recommends that you set this parameter in an ALTER SYSTEM statement rather than in the initialization parameter file, and that you reset the value to zero as soon as the needed statistics have been gathered.

My memory is that when you set this parameter, a session could call the operating system for O/S statistics relating to the work done and populate some extra statistics in the v$sesstat and v$sysstat view. 

The statistics were class 16, and all had names starting with “OS”, and the advisory was that you set the parameter to a value in seconds which would dictate the length of time between calls for OS stats (although Metalink note 47360.1 has an interesting historical comment about Oracle 7).

A couple of odd things though:

  • I’ve noticed that there are NO such statistics available in my Windows databases (even hidden in the x$ under the v$), even back in 8.1.7.4
  • I’ve seen a couple of cases (Solaris I think) where the OS stats are populated even when timed_os_statistics = 0.

So posisbly some platform specific changes have been made to the feature since it first came out, but no-one has been assigned to update any of the related documentation.

Does anyone have any better information ?

7 Comments »

  1. Jonathan,

    Here’s how things look on 10.2.0.4 on AIX. The behaviour appears to be as documented. Hopefully the formatting is ok.

    sys@DBA1PROD> select version from v$instance;

    VERSION
    -----------------
    10.2.0.4.0

    sys@DBA1PROD> show parameter timed_os_statistics

    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    timed_os_statistics                  integer     0
    sys@DBA1PROD> select name, value from v$sysstat where name like 'OS%';

    NAME                                              VALUE
    -------------------------------------------- ----------
    OS User time used                                     0
    OS System time used                                   0
    OS Maximum resident set size                          0
    OS Integral shared text size                          0
    OS Integral unshared data size                        0
    OS Integral unshared stack size                       0
    OS Page reclaims                                      0
    OS Page faults                                        0
    OS Swaps                                              0
    OS Block input operations                             0
    OS Block output operations                            0
    OS Socket messages sent                               0
    OS Socket messages received                           0
    OS Signals received                                   0
    OS Voluntary context switches                         0
    OS Involuntary context switches                       0

    16 rows selected.

    sys@DBA1PROD> alter system set timed_os_statistics=5 scope=memory;

    System altered.

    sys@DBA1PROD> select name, value from v$sysstat where name like 'OS%';

    NAME                                              VALUE
    -------------------------------------------- ----------
    OS User time used                                   217
    OS System time used                                  23
    OS Maximum resident set size                     764772
    OS Integral shared text size                     241762
    OS Integral unshared data size                    38216
    OS Integral unshared stack size                       0
    OS Page reclaims                                  36172
    OS Page faults                                     1760
    OS Swaps                                              0
    OS Block input operations                             0
    OS Block output operations                            0
    OS Socket messages sent                               0
    OS Socket messages received                           0
    OS Signals received                                   0
    OS Voluntary context switches                       969
    OS Involuntary context switches                    2784

    16 rows selected.

    sys@DBA1PROD> /

    NAME                                              VALUE
    ---------------------------------------------------------------- ----------
    OS User time used                                   220
    OS System time used                                  26
    OS Maximum resident set size                     764772
    OS Integral shared text size                     244154
    OS Integral unshared data size                    38434
    OS Integral unshared stack size                       0
    OS Page reclaims                                  36172
    OS Page faults                                     1761
    OS Swaps                                              0
    OS Block input operations                             0
    OS Block output operations                            0
    OS Socket messages sent                               0
    OS Socket messages received                           0
    OS Signals received                                   0
    OS Voluntary context switches                      1147
    OS Involuntary context switches                    2799

    16 rows selected.

    sys@DBA1PROD> alter system set timed_os_statistics=0 scope=memory;

    System altered.

    I’ve certainly noticed significant differences in OS metrics on Windows which isn’t surprising. For example, CPU percentages are the order of the day, rather than runnable processes.

    To complicate things further, 10g has some statistics in V$OSSTAT (and the related AWR view) containing OS-reported time values (e.g. BUSY_TIME, USER_TIME etc.) that are updated when timed_os_statistics are not enabled. Maybe that’s what you’ve seen in your Solaris example? e.g.

    sys@DBA1PROD> show parameter timed_os_statistics

    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    timed_os_statistics                  integer     0
    sys@DBA1PROD> select name, value from v$sysstat where name like 'OS%';

    NAME                                              VALUE
    -------------------------------------------- ----------
    OS User time used                                     0
    OS System time used                                   0
    OS Maximum resident set size                          0
    OS Integral shared text size                          0
    OS Integral unshared data size                        0
    OS Integral unshared stack size                       0
    OS Page reclaims                                      0
    OS Page faults                                        0
    OS Swaps                                              0
    OS Block input operations                             0
    OS Block output operations                            0
    OS Socket messages sent                               0
    OS Socket messages received                           0
    OS Signals received                                   0
    OS Voluntary context switches                         0
    OS Involuntary context switches                       0

    16 rows selected.

    sys@DBA1PROD> select * from v$osstat;

    STAT_NAME                                         VALUE  OSSTAT_ID
    -------------------------------------------- ---------- ----------
    NUM_CPUS                                              4          0
    IDLE_TIME                                            11          1
    BUSY_TIME                                          9753          2
    USER_TIME                                          7946          3
    SYS_TIME                                           1807          4
    IOWAIT_TIME                                           8          5
    AVG_IDLE_TIME                                         2          7
    AVG_BUSY_TIME                                      2438          8
    AVG_USER_TIME                                      1986          9
    AVG_SYS_TIME                                        451         10
    AVG_IOWAIT_TIME                                       2         11
    OS_CPU_WAIT_TIME                                  16900         13
    RSRC_MGR_CPU_WAIT_TIME                                0         14
    LOAD                                         .025390625         15
    NUM_CPU_CORES                                         2         16
    NUM_VCPUS                                             2         18
    NUM_LCPUS                                             4         19
    PHYSICAL_MEMORY_BYTES                        2.3085E+10       1008

    18 rows selected.

    sys@DBA1PROD> select count(*) from all_objects;

     COUNT(*)
    ----------
        42830

    sys@DBA1PROD> select * from v$osstat;

    STAT_NAME                                         VALUE  OSSTAT_ID
    -------------------------------------------- ---------- ----------
    NUM_CPUS                                              4          0
    IDLE_TIME                                            12          1
    BUSY_TIME                                         24585          2
    USER_TIME                                         20190          3
    SYS_TIME                                           4395          4
    IOWAIT_TIME                                           9          5
    AVG_IDLE_TIME                                         2          7
    AVG_BUSY_TIME                                      6145          8
    AVG_USER_TIME                                      5046          9
    AVG_SYS_TIME                                       1096         10
    AVG_IOWAIT_TIME                                       2         11
    OS_CPU_WAIT_TIME                                  46900         13
    RSRC_MGR_CPU_WAIT_TIME                                0         14
    LOAD                                         .028320313         15
    NUM_CPU_CORES                                         2         16
    NUM_VCPUS                                             2         18
    NUM_LCPUS                                             4         19
    PHYSICAL_MEMORY_BYTES                        2.3085E+10       1008

    18 rows selected.

    sys@DBA1PROD> select name, value from v$sysstat where name like 'OS%';

    NAME                                              VALUE
    -------------------------------------------- ----------
    OS User time used                                     0
    OS System time used                                   0
    OS Maximum resident set size                          0
    OS Integral shared text size                          0
    OS Integral unshared data size                        0
    OS Integral unshared stack size                       0
    OS Page reclaims                                      0
    OS Page faults                                        0
    OS Swaps                                              0
    OS Block input operations                             0
    OS Block output operations                            0
    OS Socket messages sent                               0
    OS Socket messages received                           0
    OS Signals received                                   0
    OS Voluntary context switches                         0
    OS Involuntary context switches                       0

    16 rows selected.

    sys@DBA1PROD>

    Comment by Doug Burns — December 29, 2008 @ 3:15 pm GMT Dec 29,2008 | Reply

  2. Jonathan,
    without knowing anything about timed_os_statistics I’d suggest to create a small testcase and use dtruss [1] (on Solaris) or similar tools to inspect the process. So at least it might be possible to see where the os_stats are gathered. This might also help to estimate the real impact.
    I hope we will find more accurate infos.

    [1] http://www.brendangregg.com/DTrace/dtruss_example.txt

    Comment by Martin Berger — December 29, 2008 @ 8:37 pm GMT Dec 29,2008 | Reply

  3. Doug,

    Thanks for the example – so at least one platform honours the manuals.

    I’ve now found one example in my collection of statspack outputs showing a 9.2.0.8 with timed_os_statistics not set, timed_statistics set to true, and stats like “OS Voluntary context switches” appearing with non-zero values – so I can be fairly sure that I wasn’t confusing these OS stats with the new v$osstat stuff in 10g.

    Martin,
    Thanks for the suggestions, but I wasn’t really concerned about the cost and method of collection; it was more a question of whether there was any better information available about the rules explaining the circumstances that would cause collection to take place.

    Maybe it’s a case of “if it’s very cheap it get’s done, if not you have to asked for it explicitly, and if it’s windows it just doesn’t happen”.

    Comment by Jonathan Lewis — December 29, 2008 @ 10:46 pm GMT Dec 29,2008 | Reply

  4. Well statspack shows system wide stats, maybe someone had set statistics_level = all temporarily in some session for SQL execution plan stats collection (which also enables timed_os_statistics).

    But yep, as the OS stats are OS dependent, they vary by underlying OS. Solaris has the most detailed stats due their microstate accounting (stuff like time spent in trap handling, serving page faults and scheduling latency).

    I suspect that Windows port doesn’t have OS stats in use due the single-process/multithreaded architecture, thus it’s not possible to gather detailed *thread level* OS stats on the windows versions Oracle needs to run on.

    Normally the OS stats are gathered using getrusage() syscall, but on Solaris an ioctl() on /proc/PID is done which give microstate accounting data instead.

    Timed OS stats gathering syscalls are made in the end of DB call in modern versions, but only if the SGA time has increased by more than timed_os_stats seconds since last sample was taken.

    Comment by Tanel Poder — December 30, 2008 @ 4:25 pm GMT Dec 30,2008 | Reply

    • Tanel,
      Thanks for the feedback.

      I like your suggestion about the Windows port, it’s certainly makes sense.

      That’s a good thought, too, about the session level setting for the timed_os_statistics – but in the statspack sample I have (which has lots of sessions) the value for the OS statistic for User CPU was consistent with Oracle’s “CPU used by this session” – which could still be a logon trigger enabling the feature at the session level for every session, of course.

      Comment by Jonathan Lewis — December 31, 2008 @ 11:30 am GMT Dec 31,2008 | Reply

  5. The OS statistics were already in some UNIX ports as early as version 6 and version 7 of Oracle. They were introduced by the Sequent base port. Not all ports and platforms could implement this for various reasons, like not supporting the the getrusage() system call and not supporting all the different kind of process stats that were support by the Sequent (Dynix).

    One of the major problems/draw backs of these OS stats and timed_os_statistics are that they are retrieved on the Oracle process level by and kept in v$sesstat (that is the session level) and v$sysstat (which is a summation of v$sesstat + a special session that keeps track of all the stats of the sessions that have logged off, so that is your history basically).

    Now that means if you have multiple sessions per process (MTS and/or Oracle Application Server), you need to be careful with how you interpret these values.

    Comment by akolk — February 9, 2009 @ 5:44 pm GMT Feb 9,2009 | Reply

  6. OS stats on 11.2.0.3.5 AIX are being populated even when timed_os_statistics=0

    Comment by Jesse — April 16, 2013 @ 1:46 pm GMT Apr 16,2013 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 4,521 other followers