------------------------------------------------------------
-- file		oscpux2.sql
-- desc		OS CPU breakdown DELTA - (report) for Oracle 11
-- author	Craig A. Shallahamer, craig@orapub.com
-- orig		04-April-07
-- lst upt	14-Jan-08 
-- copyright	(c)2007,2008 OraPub, Inc.
------------------------------------------------------------

-- Asumption: past data has been gathered via oscpux1.sql

set echo off
set feedback off
set heading on
set verify off
set termout off

col name		format a35	heading "Category" trunc
col pct			format 9990.00	heading "Percent"

col val1 new_val dur_s 
select	(dbms_utility.get_time-value)/100 val1
from	op_os_cpu_snap
where	stat_name = 'the_time'
/
col val1 new_val cores 
select	value val1
from	v$osstat
where	stat_name = 'NUM_CPUS'
/
col val1 new_val cpu_time_avail_s 
select	&cores*&dur_s val1
from	dual
/
set termout on

select 	decode(t1.stat_name,
	'IDLE_TIME','Idle','IDLE_TICKS','Idle',
	'USER_TIME','User','USER_TICKS','User',
	'SYS_TIME','System','SYS_TICKS','System',
	'IOWAIT_TIME','IO Wait','IOWAIT_TICKS','IO Wait',
	'NICE_TIME','Nice','NICE_TICKS','Nice','DUNNO') name,
	100*(((t1.value-t0.value)/100)/&cpu_time_avail_s) pct
from	v$osstat t1,
		op_os_cpu_snap t0
where	t1.stat_name = t0.stat_name
  and	t1.stat_name in ('USER_TIME','NICE_TIME','IOWAIT_TIME','SYS_TIME','IDLE_TIME',
  						 'USER_TICKS','NICE_TICKS','IOWAIT_TICKS','SYS_TICKS','IDLE_TICKS'
  )
/

--	t1.value,t0.value,

start osmclear
set heading off
set feedback off
select 'Delta is '||&dur_s||' seconds' from dual;
select 'Number of CPU cores is '||&cores from dual;
set heading on
set feedback on

start osmclear