Re: Significant discrepancy in V$OSSTAT since upgrading to 10.2.0.5

From: Steve Howard <stevedhoward_at_gmail.com>
Date: Wed, 21 Jul 2010 12:40:21 -0700 (PDT)
Message-ID: <d900f3a2-774e-4e0f-8ae8-23306361de78_at_y11g2000yqm.googlegroups.com>

On Jul 21, 11:51 am, Mladen Gogala <n..._at_email.here.invalid> wrote:
> I used V$OSSTAT for monitoring in 10.2.0.4 and it was much more accurate
> than is the case with 10.2.0.5. Look at this:
>
> SQL> column stat_name format a20
> SQL> select stat_name,value from v$osstat
>   2  where stat_name like '%TIME';
>
> STAT_NAME                 VALUE
> -------------------- ----------
> IDLE_TIME              80163480
> BUSY_TIME              42444869
> USER_TIME              32543474
> SYS_TIME                7838644
> IOWAIT_TIME             8463912
> NICE_TIME                     1
> RSRC_MGR_CPU_WAIT_TI          0
> ME
>
> SQL> select 42444869/80163480 from dual;
>
> 42444869/80163480
> -----------------
>        .529478872
>
> SQL> !sar -u 3 5
> Linux 2.6.9-22.ELsmp (oracle13)         07/21/2010
>
> 11:45:27 AM       CPU     %user     %nice   %system   %iowait     %idle
> 11:45:30 AM       all     17.83      0.00      8.08      6.00     68.08
> 11:45:33 AM       all     19.23      0.00      8.83      2.66     69.28
> 11:45:36 AM       all     14.51      0.00      6.67      0.83     77.98
> 11:45:39 AM       all     19.67      0.00      8.25      1.58     70.50
> 11:45:42 AM       all     13.33      0.00      7.00      1.25     78.42
> Average:          all     16.92      0.00      7.77      2.47     72.85
>
> SQL>
>
> So, V$OSSTAT tells me that my CPU resources are 52.9% busy while sar
> tells me that those very same resources are 70% idle. The "top" monitor
> confirms that "sar" is right and not Oracle.
> --http://mgogala.byethost5.com

V$OSSTAT is cumulative, so wouldn't you have to...

SQL> declare
  2 l_secs number;
  3 begin
  4 for i in 1..5 loop
  5 select value into l_secs from v$osstat where stat_name = 'USER_TIME';

  6      dbms_output.put_Line(l_secs);
  7      dbms_Lock.sleep(3);

  8 end loop;
  9 end;
 10 /
30191
30200
30205
30211
30216

PL/SQL procedure successfully completed.

SQL> Received on Wed Jul 21 2010 - 14:40:21 CDT