Prev: Simple Hack To Get $2500 To Your PayPal Account.
Next: what is the maximum size stored in a column
From: Mladen Gogala on 21 Jul 2010 11:51
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
From: Charles Hooper on 21 Jul 2010 13:48
On Jul 21, 11:51 am, Mladen Gogala <n...(a)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:
>
(Snip)
> 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.

Just a couple of quick calculations:
IDLE_TIME: 80163480
BUSY_TIME: 42444869
Total Time: 122608349

If the total available CPU time is 122,608,349:
Busy Percent = 42444869/(42444869+80163480)
Busy Percent = 42444869/122608349

Since the database was brought online:
34.62 Busy
65.38 Idle

sar output:
72.852% for 15 second time period

The problem is that the current average CPU usage does not match the
historical CPU usage.

Charles Hooper
Co-author of "Expert Oracle Practices: Oracle Database Administration
from the Oak Table"
http://hoopercharles.wordpress.com/
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
From: joel garry on 21 Jul 2010 14:22
On Jul 21, 8:51 am, Mladen Gogala <n...(a)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

Should it be busy_time / ( busy_time + idle_time )?

select 42444869/(42444869+80163480) from dual;

42444869/(42444869+80163480)
----------------------------
.346182534

(from http://blog.tanelpoder.com/seminar/seminar-files/#comments , I
could be totally misunderstanding, of course.)

jg
--
@home.com is bogus. So California requires big electricity providers
to get 20% of the power from renewable resources. SDGE finances a
wind farm in Montana that sells the power to Canada, and gets 45% of
its investment as a write-off in tax credits and accelerated
depreciation, and can count it as part of the 20%. Meanwhile, I have
to worry about rolling blackouts power-spiking all my devices with
computers in them, including prophylactic blackouts because the power
grid sparks fires. Because they don't keep their database of trees
that need to be trimmed up to date.
From: Steve Howard on 21 Jul 2010 15:40
On Jul 21, 11:51 am, Mladen Gogala <n...(a)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>
From: Mladen Gogala on 21 Jul 2010 17:04
On Wed, 21 Jul 2010 11:22:49 -0700, joel garry wrote:


> Should it be busy_time / ( busy_time + idle_time )?

Yes, it should. My mistake, I was a bit too hasty.



--
http://mgogala.byethost5.com
 |  Next  |  Last
Pages: 1 2
Prev: Simple Hack To Get $2500 To Your PayPal Account.
Next: what is the maximum size stored in a column