Re: Significant discrepancy in V$OSSTAT since upgrading to 10.2.0.5
From: Mladen Gogala <no_at_email.here.invalid>
Date: Wed, 21 Jul 2010 21:07:18 +0000 (UTC)
Message-ID: <pan.2010.07.21.21.07.17_at_email.here.invalid>
On Wed, 21 Jul 2010 12:40:21 -0700, Steve Howard 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
Date: Wed, 21 Jul 2010 21:07:18 +0000 (UTC)
Message-ID: <pan.2010.07.21.21.07.17_at_email.here.invalid>
On Wed, 21 Jul 2010 12:40:21 -0700, Steve Howard wrote:
> 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>
Hmmm, I'll wait for the "METRIC" table to appear. In case someone was wondering, there is no such table in 11.2 ... yet.
-- http://mgogala.byethost5.comReceived on Wed Jul 21 2010 - 16:07:18 CDT