FAQ
�Hi,
Can any one help me get to the bottom of this?

We have two Solaris servers one hosting Oracle 10.1 and the other 11.2. The
CPU stats on the 11g box are rounded to centiseconds while on 10g they are
inmicroseconds:

Example:
In 11g: select value from v$sys_time_model where stat_name = 'DB CPU';
VALUE

27089090000

In 10g: select value from v$sys_time_model where stat_name = 'DB CPU';
VALUE

1373214613234

It is the same in v$sess_time_model and I suspect it is an OS setting that
isat the root of the issue.

Regards

Gerry Miller

Search Discussions

  • Powell, Mark at Oct 21, 2011 at 11:13 pm
    Gerry, the Reference manual says the value is measured in microseconds so what makes you think the value is in centi-seconds? Since v$ views are generally since instance startup the comparison between the two version would only be relative if the two instance have the same start time. Is this true?


    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of Gerry Miller
    Sent: Friday, October 21, 2011 3:14 AM
    To: Oracle-L Group
    Subject: CPU rounding

    Hi,
    Can any one help me get to the bottom of this?

    We have two Solaris servers one hosting Oracle 10.1 and the other 11.2. The CPU stats on the 11g box are rounded to centiseconds while on 10g they are
    inmicroseconds:

    Example:
    In 11g: select value from v$sys_time_model where stat_name = 'DB CPU'; VALUE
    -----------
    27089090000

    In 10g: select value from v$sys_time_model where stat_name = 'DB CPU'; VALUE
    -------------
    1373214613234

    It is the same in v$sess_time_model and I suspect it is an OS setting that isat the root of the issue.


    Regards

    Gerry Miller

    --
    http://www.freelists.org/webpage/oracle-l


    --
    http://www.freelists.org/webpage/oracle-l
  • Gerry Miller at Oct 21, 2011 at 11:43 pm
    Hi Mark.
    My apologies. I have not made myself clear: the problem is that the CPU
    times *always* end with 4 zeroes, for example:
    select sid,stat_name, value from v$sess_time_model
    where stat_name like 'DB%'
    order by sid,stat_name
    ;
    SID STAT_NAME VALUE
    ------ ---------- ----------------
    10 DB CPU 1696440000
    10 DB time 1989368043
    27 DB CPU 31910000
    27 DB time 35288836
    405 DB CPU 100000
    405 DB time 98050
    414 DB CPU 210000
    414 DB time 196500
    602 DB CPU 50000
    602 DB time 32566
    ......

    If you retrieve a value that is represented in microseconds and it has 4
    zeroes on the end then it is effectively in centiseconds: 1696440000
    microseconds=169644 centiseconds. I was not comparing the values of the 10g
    database with that of the 11g database, but rather demonstrating that CPU is
    accurate to the microsecond in the 10g database.

    Note in particular sessions 405, 414 and 602, in which the DB CPU figure is
    higher than DB Time, which is what led me to the conclusion that the CPU
    figure is being rounded rather than truncated. This is the real problem
    caused by this: when one is trying to determine resource usage and DB Time
    isless than DB CPU, which is one its components, it is quite hard to produce
    sensible information.

    Regards

    Gerry


    Powell, Mark wrote: Gerry, the Reference manual says the value is measured
    inmicroseconds so what makes you think the value is in centi-seconds? Since
    v$ views are generally since instance startup the comparison between the two
    version would only be relative if the two instance have the same start time.
    Is this true? -----Original Message----- From:
    oracle-l-bounce@freelists.org[1] On
    Behalf Of Gerry Miller Sent: Friday, October 21, 2011 3:14 AM To: Oracle-L
    Group Subject: CPU rounding Hi, Can any one help me get to the bottom of
    this? We have two Solaris servers one hosting Oracle 10.1 and the other
    11.2.The CPU stats on the 11g box are rounded to centiseconds while on 10g
    they are inmicroseconds: Example: In 11g: select value from v$sys_time_model
    where stat_name = 'DB CPU'; VALUE ----------- 27089090000 In 10g: select
    value from v$sys_time_model where stat_name = 'DB CPU'; VALUE -------------
    1373214613234 It is the same in v$sess_time_model and I suspect it is an OS
    setting that isat the root of the issue. Regards Gerry Miller --
    http://www.freelists.org/webpage/oracle-l[3] --
    http://www.freelists.org/webpage/oracle-l[4]


    --- Links ---
    1 mailto:oracle-l-bounce@freelists.org
    2 mailto:oracle-l-bounce@freelists.org
    3 http://www.freelists.org/webpage/oracle-l
    4 http://www.freelists.org/webpage/oracle-l
  • Bobak, Mark at Oct 21, 2011 at 11:26 pm
    Been a while since I worked on Solaris regularly, however, what version of Solaris?

    If Solaris version is < 10, perhaps microstate accounting is enabled on one server, but not on the other? (As of Solaris 10, microstate accounting is always enabled.)

    -Mark
    ________________________________________
    From: oracle-l-bounce@freelists.org [oracle-l-bounce@freelists.org] On Behalf Of Gerry Miller [gerry@millerandbowman.com]
    Sent: Friday, October 21, 2011 3:14
    To: Oracle-L Group
    Subject: CPU rounding

    Hi,
    Can any one help me get to the bottom of this?

    We have two Solaris servers one hosting Oracle 10.1 and the other 11.2. The
    CPU stats on the 11g box are rounded to centiseconds while on 10g they are
    inmicroseconds:

    Example:
    In 11g: select value from v$sys_time_model where stat_name = 'DB CPU';
    VALUE
    -----------
    27089090000

    In 10g: select value from v$sys_time_model where stat_name = 'DB CPU';
    VALUE
    -------------
    1373214613234

    It is the same in v$sess_time_model and I suspect it is an OS setting that
    isat the root of the issue.


    Regards

    Gerry Miller
  • Gerry Miller at Oct 21, 2011 at 11:49 pm
    Hi Mark

    I thought it may be something to do with the OS versions but the 10g
    database, which is accurate to the microsecond is on Solaris 9;
    the 11g database, which is not, is on Solaris 10.

    Is there any command or utility that I can run to determine whether
    microstate accounting is enabled? Although if it is always enabled on
    Solaris 10 then I guess that is not going to be the problem.

    Regards

    Gerry



    Bobak, Mark wrote:
    Been a while since I worked on Solaris regularly, however, what version of Solaris?

    If Solaris version is < 10, perhaps microstate accounting is enabled on one server, but not on the other? (As of Solaris 10, microstate accounting is always enabled.)

    -Mark
    ________________________________________
    From: oracle-l-bounce@freelists.org [oracle-l-bounce@freelists.org] On Behalf Of Gerry Miller [gerry@millerandbowman.com]
    Sent: Friday, October 21, 2011 3:14
    To: Oracle-L Group
    Subject: CPU rounding

    Hi,
    Can any one help me get to the bottom of this?

    We have two Solaris servers one hosting Oracle 10.1 and the other 11.2. The
    CPU stats on the 11g box are rounded to centiseconds while on 10g they are
    inmicroseconds:

    Example:
    In 11g: select value from v$sys_time_model where stat_name = 'DB CPU';
    VALUE
    -----------
    27089090000

    In 10g: select value from v$sys_time_model where stat_name = 'DB CPU';
    VALUE
    -------------
    1373214613234

    It is the same in v$sess_time_model and I suspect it is an OS setting that
    isat the root of the issue.


    Regards

    Gerry Miller

    --
    http://www.freelists.org/webpage/oracle-l




    --
    http://www.freelists.org/webpage/oracle-l


    --
    http://www.freelists.org/webpage/oracle-l
  • Gerry Miller at Nov 17, 2011 at 9:14 am
    Hi

    I hate to bang on about this but I am no nearer to a solution.

    We have 3 Solaris 10 boxes running 11.2.0.2 Enterprise Edition and the
    problem exists on all 3 and I would like to ask anyone with the same
    configuration out there to run:
    SELECT value FROM v$sess_time_model WHERE stat_name = 'DB CPU';
    and tell me if the results are rounded to centiseconds, that is, all end
    with 4 zeroes.

    Thanks

    Gerry





    Gerry Miller wrote:
    Hi,
    Can any one help me get to the bottom of this?

    We have two Solaris servers one hosting Oracle 10.1 and the other 11.2. The
    CPU stats on the 11g box are rounded to centiseconds while on 10g they are
    inmicroseconds:

    Example:
    In 11g: select value from v$sys_time_model where stat_name = 'DB CPU';
    VALUE
    -----------
    27089090000

    In 10g: select value from v$sys_time_model where stat_name = 'DB CPU';
    VALUE
    -------------
    1373214613234

    It is the same in v$sess_time_model and I suspect it is an OS setting that
    isat the root of the issue.


    Regards

    Gerry Miller

    --
    http://www.freelists.org/webpage/oracle-l


    --
    http://www.freelists.org/webpage/oracle-l
  • Joel Patterson at Nov 17, 2011 at 12:28 pm
    column value format 999999999999.999999999999

    I get 4 zeroes before the decimal on 11.2.0.1
    251290000.000000000000

    I get 0 zeroes before decimal on 10.2.0.4
    56729894.000000000000





    Joel Patterson
    Database Administrator
    904 727-2546
    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of Gerry Miller
    Sent: Thursday, November 17, 2011 4:13 AM
    To: gerry@millerandbowman.com
    Cc: Oracle-L Group
    Subject: Re: CPU rounding

    Hi

    I hate to bang on about this but I am no nearer to a solution.

    We have 3 Solaris 10 boxes running 11.2.0.2 Enterprise Edition and the
    problem exists on all 3 and I would like to ask anyone with the same
    configuration out there to run:
    SELECT value FROM v$sess_time_model WHERE stat_name = 'DB CPU';
    and tell me if the results are rounded to centiseconds, that is, all end
    with 4 zeroes.

    Thanks

    Gerry





    Gerry Miller wrote:
    Hi,
    Can any one help me get to the bottom of this?

    We have two Solaris servers one hosting Oracle 10.1 and the other 11.2. The
    CPU stats on the 11g box are rounded to centiseconds while on 10g they are
    inmicroseconds:

    Example:
    In 11g: select value from v$sys_time_model where stat_name = 'DB CPU';
    VALUE
    -----------
    27089090000

    In 10g: select value from v$sys_time_model where stat_name = 'DB CPU';
    VALUE
    -------------
    1373214613234

    It is the same in v$sess_time_model and I suspect it is an OS setting that
    isat the root of the issue.


    Regards

    Gerry Miller

    --
    http://www.freelists.org/webpage/oracle-l


    --
    http://www.freelists.org/webpage/oracle-l


    --
    http://www.freelists.org/webpage/oracle-l
  • Gerry Miller at Nov 17, 2011 at 8:24 pm
    Thanks Joel. Are those databases on the same server, or at least on the same
    Solaris version? It could be either the database release or the OS version
    that is at fault.

    Joel.Patterson@crowley.com[1] wrote: column value format
    999999999999.999999999999 I get 4 zeroes before the decimal on 11.2.0.1
    251290000.000000000000 I get 0 zeroes before decimal on 10.2.0.4
    56729894.000000000000 Joel Patterson Database Administrator 904 727-2546
    -----Original Message----- From: oracle-l-bounce@freelists.org[2]
    On Behalf Of Gerry Miller Sent:
    Thursday, November 17, 2011 4:13 AM To: gerry@millerandbowman.com[4] Cc:
    Oracle-L Group Subject: Re: CPU rounding Hi I hate to bang on about this but
    I am no nearer to a solution. We have 3 Solaris 10 boxes running 11.2.0.2
    Enterprise Edition and the problem exists on all 3 and I would like to ask
    anyone with the same configuration out there to run: SELECT value FROM
    v$sess_time_model WHERE stat_name = 'DB CPU'; and tell me if the results are
    rounded to centiseconds, that is, all end with 4 zeroes. Thanks Gerry Gerry
    Miller wrote: Hi, Can any one help me get to the bottom of this? We have two
    Solaris servers one hosting Oracle 10.1 and the other 11.2. The CPU stats on
    the 11g box are rounded to centiseconds while on 10g they are
    inmicroseconds:Example: In 11g: select value from v$sys_time_model where
    stat_name = 'DB CPU'; VALUE ----------- 27089090000 In 10g: select value
    fromv$sys_time_model where stat_name = 'DB CPU'; VALUE -------------
    1373214613234 It is the same in v$sess_time_model and I suspect it is an OS
    setting that isat the root of the issue. Regards Gerry Miller --
    http://www.freelists.org/webpage/oracle-l[5] --
    http://www.freelists.org/webpage/oracle-l[6] --
    http://www.freelists.org/webpage/oracle-l[7]


    --- Links ---
    1 mailto:Joel.Patterson@crowley.com
    2 mailto:oracle-l-bounce@freelists.org
    3 mailto:oracle-l-bounce@freelists.org
    4 mailto:gerry@millerandbowman.com
    5 http://www.freelists.org/webpage/oracle-l
    6 http://www.freelists.org/webpage/oracle-l
    7 http://www.freelists.org/webpage/oracle-l
  • Gerry Miller at Nov 17, 2011 at 9:24 pm
    Niall,
    Thanks for that. It is the sort of information I have been looking for. I
    will be back on site on Tuesday and will try the prstat command then.

    It seems to me that Oracle must have been using different system calls in
    10gas CPU is reported in microseconds in that release, or at least it is on
    Solaris 9.

    Regards

    Gerry


    Niall Litchfield wrote: Gerry
    As promised I did ask around.. I got the following response which looks
    pretty accurate to me;
    Although Solaris 10 supports CPU timing data in microsecond increments,
    Oracle does not appear to use this. Oracle makes calls to the times()
    function which is based on the old style sampling performed at each tick,
    normally every 10 milliseconds. The behavior Gerry is seeing is expected,
    based on the system calls Oracle is making. It is possible to modify the
    systemwide clock tick rate to be faster, however this can have some
    side-effects. The following blog sheds some light on the considerations:
    http://blogs.oracle.com/jtc/entry/overhead_in_increasing_the_solaris[1]
    It would be nice if Oracle changed the system calls they use on Solaris, to
    retrieve CPU usage. Unfortunately times() is a standard Unix function while
    microstate accounting is a Solaris specific implementation. It may not seem
    worth the cost for a Solaris specific change like this.
    Solaris 10 uses microstate accounting for most statistics reported through
    prstat, vmstat and other OS utilities. I recommend using something like "$
    prstat -Lma 5" or "$prstat -Lma -p 12345 5" to monitor the OS process of
    concern. This will provide very detailed statistics based on CPU state
    changes, not sampling. For example:
    -bash-3.00$ prstat -Lma -p 24859 5
    PID USERNAME USR SYS TRP TFL DFL LCK SLP LAT VCX ICX SCL SIG
    PROCESS/LWPID
    24859 oracle 8.6 9.3 0.1 0.0 0.0 0.0 78 3.7 7K 1K 21K 0 oracle/1
    24859 oracle 0.0 0.0 0.0 0.0 0.0 0.0 100 0.0 0 0 0 0 oracle/2
    In this example, the process has two lightweight threads so both are
    displayed. The columns show various types of CPU usage and idle times.
    These various CPU states are all defined in man pages for prstat.

    On Thu, Nov 17, 2011 at 8:22 PM, Gerry Miller wrote:

    Thanks Joel. Are those databases on the same server, or at least on the same
    Solaris version? It could be either the database release or the OS version
    that is at fault.

    Joel.Patterson@crowley.com[3][1] wrote: column value format
    999999999999.999999999999 I get 4 zeroes before the decimal on 11.2.0.1
    251290000.000000000000 I get 0 zeroes before decimal on 10.2.0.4
    56729894.000000000000 Joel Patterson Database Administrator 904 727-2546[4]
    -----Original Message----- From: oracle-l-bounce@freelists.org[5][2]
    On Behalf Of Gerry Miller Sent:
    Thursday, November 17, 2011 4:13 AM To: gerry@millerandbowman.com[7][4] Cc:
    Oracle-L Group Subject: Re: CPU rounding Hi I hate to bang on about this but
    I am no nearer to a solution. We have 3 Solaris 10 boxes running 11.2.0.2
    Enterprise Edition and the problem exists on all 3 and I would like to ask
    anyone with the same configuration out there to run: SELECT value FROM
    v$sess_time_model WHERE stat_name = 'DB CPU'; and tell me if the results are
    rounded to centiseconds, that is, all end with 4 zeroes. Thanks Gerry Gerry
    Miller wrote: Hi, Can any one help me get to the bottom of this? We have two
    Solaris servers one hosting Oracle 10.1 and the other 11.2. The CPU stats on
    the 11g box are rounded to centiseconds while on 10g they are
    inmicroseconds:Example: In 11g: select value from v$sys_time_model where
    stat_name = 'DB CPU'; VALUE ----------- 27089090000 In 10g: select value
    fromv$sys_time_model where stat_name = 'DB CPU'; VALUE -------------
    1373214613234 It is the same in v$sess_time_model and I suspect it is an OS
    setting that isat the root of the issue. Regards Gerry Miller --
    http://www.freelists.org/webpage/oracle-l[5][8] --
    http://www.freelists.org/webpage/oracle-l[6][9] --
    http://www.freelists.org/webpage/oracle-l[7][10]


    --- Links ---
    1 mailto:Joel.Patterson@crowley.com[11]
    2 mailto:oracle-l-bounce@freelists.org[12]
    3 mailto:oracle-l-bounce@freelists.org[13]
    4 mailto:gerry@millerandbowman.com[14]
    5 http://www.freelists.org/webpage/oracle-l[15]
    6 http://www.freelists.org/webpage/oracle-l[16]
    7 http://www.freelists.org/webpage/oracle-l[17]
    --
    http://www.freelists.org/webpage/oracle-l[18]





    --
    Niall Litchfield
    Oracle DBA
    http://www.orawin.info[19]



    --- Links ---
    1 http://blogs.oracle.com/jtc/entry/overhead_in_increasing_the_solaris
    2 mailto:gerry@millerandbowman.com
    3 mailto:Joel.Patterson@crowley.com
    4 tel:904%20727-2546
    5 mailto:oracle-l-bounce@freelists.org
    6 mailto:oracle-l-bounce@freelists.org
    7 mailto:gerry@millerandbowman.com
    8 http://www.freelists.org/webpage/oracle-l%5B5%5D
    9 http://www.freelists.org/webpage/oracle-l%5B6%5D
    10 http://www.freelists.org/webpage/oracle-l%5B7%5D
    11 mailto:Joel.Patterson@crowley.com
    12 mailto:oracle-l-bounce@freelists.org
    13 mailto:oracle-l-bounce@freelists.org
    14 mailto:gerry@millerandbowman.com
    15 http://www.freelists.org/webpage/oracle-l
    16 http://www.freelists.org/webpage/oracle-l
    17 http://www.freelists.org/webpage/oracle-l
    18 http://www.freelists.org/webpage/oracle-l
    19 http://www.orawin.info
    --
    http://www.freelists.org/webpage/oracle-l
  • Joel Patterson at Nov 18, 2011 at 12:11 pm
    Sorry Gerry, I didn't see your post for some reason.
    Yes, both were on same os version of 5.10

    Joel Patterson
    Database Administrator
    904 727-2546
    From: Niall Litchfield
    Sent: Thursday, November 17, 2011 4:06 PM
    To: gerry@millerandbowman.com
    Cc: Patterson, Joel; oracle-l@freelists.org
    Subject: Re: CPU rounding

    Gerry

    As promised I did ask around.. I got the following response which looks pretty accurate to me;

    Although Solaris 10 supports CPU timing data in microsecond increments, Oracle does not appear to use this. Oracle makes calls to the times() function which is based on the old style sampling performed at each tick, normally every 10 milliseconds. The behavior Gerry is seeing is expected, based on the system calls Oracle is making. It is possible to modify the systemwide clock tick rate to be faster, however this can have some side-effects. The following blog sheds some light on the considerations: http://blogs.oracle.com/jtc/entry/overhead_in_increasing_the_solaris
    It would be nice if Oracle changed the system calls they use on Solaris, to retrieve CPU usage. Unfortunately times() is a standard Unix function while microstate accounting is a Solaris specific implementation. It may not seem worth the cost for a Solaris specific change like this.
    Solaris 10 uses microstate accounting for most statistics reported through prstat, vmstat and other OS utilities. I recommend using something like "$ prstat -Lma 5" or "$prstat -Lma -p 12345 5" to monitor the OS process of concern. This will provide very detailed statistics based on CPU state changes, not sampling. For example:
    -bash-3.00$ prstat -Lma -p 24859 5
    PID USERNAME USR SYS TRP TFL DFL LCK SLP LAT VCX ICX SCL SIG PROCESS/LWPID
    24859 oracle 8.6 9.3 0.1 0.0 0.0 0.0 78 3.7 7K 1K 21K 0 oracle/1
    24859 oracle 0.0 0.0 0.0 0.0 0.0 0.0 100 0.0 0 0 0 0 oracle/2
    In this example, the process has two lightweight threads so both are displayed. The columns show various types of CPU usage and idle times. These various CPU states are all defined in man pages for prstat.
    On Thu, Nov 17, 2011 at 8:22 PM, Gerry Miller wrote:

    Thanks Joel. Are those databases on the same server, or at least on the same
    Solaris version? It could be either the database release or the OS version
    that is at fault.

    Joel.Patterson@crowley.com [1] wrote: column value format
    999999999999.999999999999 I get 4 zeroes before the decimal on 11.2.0.1
    251290000.000000000000 I get 0 zeroes before decimal on 10.2.0.4
    56729894.000000000000 Joel Patterson Database Administrator 904 727-2546<tel:904%20727-2546>
    -----Original Message----- From: oracle-l-bounce@freelists.org [2]
    [mailto:oracle-l-bounce@freelists.org [3]] On Behalf Of Gerry Miller Sent:
    Thursday, November 17, 2011 4:13 AM To: gerry@millerandbowman.com [4] Cc:
    Oracle-L Group Subject: Re: CPU rounding Hi I hate to bang on about this but
    I am no nearer to a solution. We have 3 Solaris 10 boxes running 11.2.0.2
    Enterprise Edition and the problem exists on all 3 and I would like to ask
    anyone with the same configuration out there to run: SELECT value FROM
    v$sess_time_model WHERE stat_name = 'DB CPU'; and tell me if the results are
    rounded to centiseconds, that is, all end with 4 zeroes. Thanks Gerry Gerry
    Miller wrote: Hi, Can any one help me get to the bottom of this? We have two
    Solaris servers one hosting Oracle 10.1 and the other 11.2. The CPU stats on
    the 11g box are rounded to centiseconds while on 10g they are
    inmicroseconds:Example: In 11g: select value from v$sys_time_model where
    stat_name = 'DB CPU'; VALUE ----------- 27089090000 In 10g: select value
    fromv$sys_time_model where stat_name = 'DB CPU'; VALUE -------------
    1373214613234 It is the same in v$sess_time_model and I suspect it is an OS
    setting that isat the root of the issue. Regards Gerry Miller --
    http://www.freelists.org/webpage/oracle-l[5]<http://www.freelists.org/webpage/oracle-l%5b5%5d> --
    http://www.freelists.org/webpage/oracle-l[6]<http://www.freelists.org/webpage/oracle-l%5b6%5d> --
    http://www.freelists.org/webpage/oracle-l[7]<http://www.freelists.org/webpage/oracle-l%5b7%5d>


    --- Links ---
    1 mailto:Joel.Patterson@crowley.com
    2 mailto:oracle-l-bounce@freelists.org
    3 mailto:oracle-l-bounce@freelists.org
    4 mailto:gerry@millerandbowman.com
    5 http://www.freelists.org/webpage/oracle-l
    6 http://www.freelists.org/webpage/oracle-l
    7 http://www.freelists.org/webpage/oracle-l
  • Gerry Miller at Nov 18, 2011 at 12:28 pm
    Hmm, that would suggest that it is an Oracle problem.
    You may be interested to know that Oracle Support finally tried it on
    Solarisand got the same outcome. They have now filed bug#13405779.

    Gerry




    Joel.Patterson@crowley.com[1] wrote:

    Sorry Gerry, I didn&#8217;t see your post for some reason.



    Yes, both were on same os version of 5.10



    Joel Patterson
    Database Administrator
    904 727-2546

    From: Niall Litchfield
    Sent: Thursday, November 17, 2011 4:06 PM
    To: gerry@millerandbowman.com[3]
    Cc: Patterson, Joel; oracle-l@freelists.org[4]
    Subject: Re: CPU rounding



    Gerry



    As promised I did ask around.. I got the following response which looks
    pretty accurate to me;



    Although Solaris 10 supports CPU timing data in microsecond increments,
    Oracle does not appear to use this. Oracle makes calls to the times()
    function which is based on the old style sampling performed at each tick,
    normally every 10 milliseconds. The behavior Gerry is seeing is expected,
    based on the system calls Oracle is making. It is possible to modify the
    systemwide clock tick rate to be faster, however this can have some
    side-effects. The following blog sheds some light on the considerations:
    http://blogs.oracle.com/jtc/entry/overhead_in_increasing_the_solaris[5]

    It would be nice if Oracle changed the system calls they use on Solaris, to
    retrieve CPU usage. Unfortunately times() is a standard Unix function while
    microstate accounting is a Solaris specific implementation. It may not seem
    worth the cost for a Solaris specific change like this.

    Solaris 10 uses microstate accounting for most statistics reported through
    prstat, vmstat and other OS utilities. I recommend using something like "$
    prstat -Lma 5" or "$prstat -Lma -p 12345 5" to monitor the OS process of
    concern. This will provide very detailed statistics based on CPU state
    changes, not sampling. For example:
    -bash-3.00$ prstat -Lma -p 24859 5
    PID USERNAME USR SYS TRP TFL DFL LCK SLP LAT VCX ICX SCL SIG
    PROCESS/LWPID
    24859 oracle 8.6 9.3 0.1 0.0 0.0 0.0 78 3.7 7K 1K 21K 0 oracle/1
    24859 oracle 0.0 0.0 0.0 0.0 0.0 0.0 100 0.0 0 0 0 0 oracle/2
    In this example, the process has two lightweight threads so both are
    displayed. The columns show various types of CPU usage and idle times.
    These various CPU states are all defined in man pages for prstat.

    On Thu, Nov 17, 2011 at 8:22 PM, Gerry Miller wrote:


    Thanks Joel. Are those databases on the same server, or at least on the same
    Solaris version? It could be either the database release or the OS version
    that is at fault.

    Joel.Patterson@crowley.com[7][1] wrote: column value format

    999999999999.999999999999 I get 4 zeroes before the decimal on 11.2.0.1
    251290000.000000000000 I get 0 zeroes before decimal on 10.2.0.4
    56729894.000000000000 Joel Patterson Database Administrator 904 727-2546[8]

    -----Original Message----- From: oracle-l-bounce@freelists.org[9][2]
    On Behalf Of Gerry Miller
    Sent:
    Thursday, November 17, 2011 4:13 AM To: gerry@millerandbowman.com[11][4] Cc:


    Oracle-L Group Subject: Re: CPU rounding Hi I hate to bang on about this but
    I am no nearer to a solution. We have 3 Solaris 10 boxes running 11.2.0.2
    Enterprise Edition and the problem exists on all 3 and I would like to ask
    anyone with the same configuration out there to run: SELECT value FROM
    v$sess_time_model WHERE stat_name = 'DB CPU'; and tell me if the results are
    rounded to centiseconds, that is, all end with 4 zeroes. Thanks Gerry Gerry
    Miller wrote: Hi, Can any one help me get to the bottom of this? We have two
    Solaris servers one hosting Oracle 10.1 and the other 11.2. The CPU stats on
    the 11g box are rounded to centiseconds while on 10g they are
    inmicroseconds:Example: In 11g: select value from v$sys_time_model where
    stat_name = 'DB CPU'; VALUE ----------- 27089090000 In 10g: select value

    fromv$sys_time_model where stat_name = 'DB CPU'; VALUE -------------

    1373214613234 It is the same in v$sess_time_model and I suspect it is an OS
    setting that isat the root of the issue. Regards Gerry Miller --

    http://www.freelists.org/webpage/oracle-l[5][12] --
    http://www.freelists.org/webpage/oracle-l[6][13] --
    http://www.freelists.org/webpage/oracle-l[7][14]


    --- Links ---
    1 mailto:Joel.Patterson@crowley.com[15]
    2 mailto:oracle-l-bounce@freelists.org[16]
    3 mailto:oracle-l-bounce@freelists.org[17]
    4 mailto:gerry@millerandbowman.com[18]
    5 http://www.freelists.org/webpage/oracle-l[19]
    6 http://www.freelists.org/webpage/oracle-l[20]
    7 http://www.freelists.org/webpage/oracle-l[21]
    --
    http://www.freelists.org/webpage/oracle-l[22]









    --
    Niall Litchfield
    Oracle DBA
    http://www.orawin.info[23]


    --- Links ---
    1 mailto:Joel.Patterson@crowley.com
    2 mailto:niall.litchfield@gmail.com
    3 mailto:gerry@millerandbowman.com
    4 mailto:oracle-l@freelists.org
    5 http://blogs.oracle.com/jtc/entry/overhead_in_increasing_the_solaris
    6 mailto:gerry@millerandbowman.com
    7 mailto:Joel.Patterson@crowley.com
    8 tel:904%20727-2546
    9 mailto:oracle-l-bounce@freelists.org
    10 mailto:oracle-l-bounce@freelists.org
    11 mailto:gerry@millerandbowman.com
    12 http://www.freelists.org/webpage/oracle-l%5b5%5d
    13 http://www.freelists.org/webpage/oracle-l%5b6%5d
    14 http://www.freelists.org/webpage/oracle-l%5b7%5d
    15 mailto:Joel.Patterson@crowley.com
    16 mailto:oracle-l-bounce@freelists.org
    17 mailto:oracle-l-bounce@freelists.org
    18 mailto:gerry@millerandbowman.com
    19 http://www.freelists.org/webpage/oracle-l
    20 http://www.freelists.org/webpage/oracle-l
    21 http://www.freelists.org/webpage/oracle-l
    22 http://www.freelists.org/webpage/oracle-l
    23 http://www.orawin.info
    --
    http://www.freelists.org/webpage/oracle-l
  • Gerry Miller at Nov 17, 2011 at 9:24 pm
    Thanks to those who replied. I have been assuming all along that it was a
    local problem, perhaps something to do with the OS configuration, but now it
    seems to be something to do with the OS version or is an "undocumented
    feature" in 11.2.
    This is more of a problem than it may appear as it substantially
    overestimates the CPU contribution to total resource usage, as this excerpt
    from an AWR report shows:
    Top 5 Timed Foreground Events
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    Avg
    wait % DB
    Event Waits Time(s) (ms) time Wait
    Class
    ------------------------------ ------------ ----------- ------ ------
    ----------
    DB CPU 2 140.4
    log file sync 52 0 2 4.9 Commit
    direct path read 207 0 0 .7 User
    I/O
    SQL*Net break/reset to client 40 0 0 .3
    Applicatio
    Disk file operations I/O 29 0 0 .2 User
    I/O

    I don't think management would be impressed with either me or Oracle if I
    told them that CPU accounted for 140% of total database resource usage
    duringa period.

    Andrew, regarding your suggestion that I try MOS, I submitted an SR on this
    on September 21st. The first analyst assured me on two occasions that the
    values were expressed in microseconds because it said so in the Oracle
    documentation. He also tested the query on his 11.2.0.2 environment and did
    not strike the problem but I have since found that it does not occur on
    Windows installations and he didn't say what OS he was on. After a few
    exchanges I asked for another analyst on September 28th and after a few
    "Anyone there?" updates from me I was appointed one on October 13th. On
    October 18th he asked me to upload some output files, which I did and have
    not heard from him since. I had the SR escalated to Severity 3 on November
    14th and have received a reply from yet another analyst asking me to upload
    an RDA, which I will do. In summary, I did go through MOS and am quite
    unimpressed. I was going to say singularly unimpressed but I have been
    unimpressed with Oracle Support more than once.

    Thanks again

    Gerry
  • Joel Patterson at Nov 17, 2011 at 1:10 pm
    P.S. This has probably been reviewed more times, but here is a place to start.

    http://www.freelists.org/post/oracle-l/CPU-rounding,3


    Joel Patterson
    Database Administrator
    904 727-2546

    -----Original Message-----
    From: Patterson, Joel
    Sent: Thursday, November 17, 2011 7:27 AM
    To: 'gerry@millerandbowman.com'
    Cc: Oracle-L Group
    Subject: RE: CPU rounding


    column value format 999999999999.999999999999

    I get 4 zeroes before the decimal on 11.2.0.1
    251290000.000000000000

    I get 0 zeroes before decimal on 10.2.0.4
    56729894.000000000000





    Joel Patterson
    Database Administrator
    904 727-2546
    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of Gerry Miller
    Sent: Thursday, November 17, 2011 4:13 AM
    To: gerry@millerandbowman.com
    Cc: Oracle-L Group
    Subject: Re: CPU rounding

    Hi

    I hate to bang on about this but I am no nearer to a solution.

    We have 3 Solaris 10 boxes running 11.2.0.2 Enterprise Edition and the
    problem exists on all 3 and I would like to ask anyone with the same
    configuration out there to run:
    SELECT value FROM v$sess_time_model WHERE stat_name = 'DB CPU';
    and tell me if the results are rounded to centiseconds, that is, all end
    with 4 zeroes.

    Thanks

    Gerry





    Gerry Miller wrote:
    Hi,
    Can any one help me get to the bottom of this?

    We have two Solaris servers one hosting Oracle 10.1 and the other 11.2. The
    CPU stats on the 11g box are rounded to centiseconds while on 10g they are
    inmicroseconds:

    Example:
    In 11g: select value from v$sys_time_model where stat_name = 'DB CPU';
    VALUE
    -----------
    27089090000

    In 10g: select value from v$sys_time_model where stat_name = 'DB CPU';
    VALUE
    -------------
    1373214613234

    It is the same in v$sess_time_model and I suspect it is an OS setting that
    isat the root of the issue.


    Regards

    Gerry Miller

    --
    http://www.freelists.org/webpage/oracle-l


    --
    http://www.freelists.org/webpage/oracle-l


    --
    http://www.freelists.org/webpage/oracle-l
  • Jorgensen, Finn at Nov 17, 2011 at 7:24 pm
    I have your exact setup. Solaris 10 with EE 11.2.0.2. I get 4 zeroes at the end of all values.

    Thanks,
    Finn


    -----Original Message-----
    From: oracle-l-bounce@freelists.org On Behalf Of Gerry Miller
    Sent: Thursday, November 17, 2011 4:13 AM
    To: gerry@millerandbowman.com
    Cc: Oracle-L Group
    Subject: Re: CPU rounding

    Hi

    I hate to bang on about this but I am no nearer to a solution.

    We have 3 Solaris 10 boxes running 11.2.0.2 Enterprise Edition and the problem exists on all 3 and I would like to ask anyone with the same configuration out there to run:
    SELECT value FROM v$sess_time_model WHERE stat_name = 'DB CPU'; and tell me if the results are rounded to centiseconds, that is, all end with 4 zeroes.

    Thanks

    Gerry





    Gerry Miller wrote:
    Hi,
    Can any one help me get to the bottom of this?

    We have two Solaris servers one hosting Oracle 10.1 and the other
    11.2. The CPU stats on the 11g box are rounded to centiseconds while
    on 10g they are
    inmicroseconds:

    Example:
    In 11g: select value from v$sys_time_model where stat_name = 'DB CPU';
    VALUE
    -----------
    27089090000

    In 10g: select value from v$sys_time_model where stat_name = 'DB CPU';
    VALUE
    -------------
    1373214613234

    It is the same in v$sess_time_model and I suspect it is an OS setting
    that isat the root of the issue.


    Regards

    Gerry Miller

    --
    http://www.freelists.org/webpage/oracle-l


    --
    http://www.freelists.org/webpage/oracle-l

    This e-mail and any attachments are confidential, may contain legal, professional or other privileged information, and are intended solely for the addressee. If you are not the intended recipient, do not use the information in this e-mail in any way, delete this e-mail and notify the sender. CEG-IP1
    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedOct 21, '11 at 7:14a
activeNov 18, '11 at 12:28p
posts14
users5
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase