V$system_Wait_Class

From: Milen Kulev <makulev_at_gmx.net>
Date: Wed, 17 Jun 2009 22:25:22 +0200
Message-ID: <009101c9ef89$bdc08a80$39419f80$_at_net>

Hello Listers,
I am trying to write a script that should give me the same information as V$active_session_history (data should be grouped only on the wait classes), but in absolute values (in milliseconds/seconds, not just counters as in V$active_session_history).

I intent to take samples of v$sys_time_model to get 'DB Time' and 'CPU Time' and then
Calculate the delta values. I want to take samples of V$system_Wait_Class too in order
To find where 'DB Time' (in which wait_class) is spend on. My problem is that the sum of V$system_Wait_Class.time_waited is nowhere near to
the value 'DB Time' - 'DB CPU', which I have got from v$sys_time_model. I believe V$system_Wait_Class.time_waited is in microseconds (Oracle documentation is pretty unclear about this)

Sample output of the script below:
ACTIVE 1042
ALL 24680
DB CPU 1396689
DB time 6916260

The system is I/O bound. I was expecting ACTIVE =~ DB time - DB CPU , but they differ by orders of magnitude...

Can someone sched some light on this topic ?

Many thanks in advance.
Milen

The rudimentary script that I am using is:

select
name , TIME_WAITED_MICRO_DELTA
from
(

  select
  c.name ,

        c.value -
            lag (c.VALUE, 1, 0) over
            ( partition by c.name order by c.ID asc )  as
TIME_WAITED_MICRO_DELTA,
          lag (c.VALUE, 1, 0) over
            ( partition by c.name order by c.ID asc )  as  flag 

  from
  (
    select 1 as id, a.*

      from
      (
      select 'ALL' as name, sum(time_waited) as value from
V$system_Wait_Class
      where 1=1 --- WAIT_CLASS <>'Idle'
      union all
      select 'ACTIVE' as name , sum(time_waited) as value from
V$system_Wait_Class
      where  WAIT_CLASS <>'Idle'
      union all
      select STAT_NAME as name, round(VALUE,0) as value from
V$sys_Time_Model where stat_name ='DB CPU'
      union all
      select STAT_NAME as name, round(VALUE,0) as value from
V$sys_Time_Model where stat_name ='DB time'
      )a
      union all
      select -1 as id , 'XX' as name,  f_numeric_wait(5) as value from dual
where  rownum > 0
      union all
      select 2 as id, b.*
      from
      (
      select 'ALL' as name , sum(time_waited) as value from
V$system_Wait_Class
      where 1=1 --- WAIT_CLASS <>'Idle'
      union all
      select 'ACTIVE' as name , sum(time_waited) as value from
V$system_Wait_Class
      where  WAIT_CLASS <>'Idle'
      union all
      select STAT_NAME as name, round(VALUE,0) as value from
V$sys_Time_Model where stat_name ='DB CPU'
      union all
      select STAT_NAME as name, round(VALUE,0) as value from
V$sys_Time_Model where stat_name ='DB time'
      )b

  ) c
   where c.id > -1
)d where d.flag !=0    

I have borrowed the f_numeric_wait function from Jonathan Lewis site

create or replace function f_numeric_wait
(

	p_seconds in number,
	p_value in number default 0

) return number
deterministic
as
begin

	 dbms_lock.sleep(SECONDS => p_seconds);
	return p_value;

end f_numeric_wait;
/

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

Received on Wed Jun 17 2009 - 15:25:22 CDT