Identifying sessions using high amounts of CPU

Sun box, Database 9.2.0.4 soon to be 9.2.0.6    

When I issue a TOP command I can identify a Unix session that has consumed hours of CPU cycles.  

I then use the Unix process ID to identify the database SID that relates to it by querying v$process  

However - I cannot seem to relate all the CPU hours I see associated to the process ay the Os level to the information stored in v$sesstat.  

Can anyone help with this ?  

I would like to identify the sessions using the most CPU cycles directly using SQL without having to issue Unix commands if possible, and then be able to tie it to OS values if asked.  

In the example below I only account for 14 of 17 hours.      

Output of TOP -

           PID USERNAME THR PRI NICE SIZE RES STATE TIME CPU COMMAND
         15791 oracle 14 59 0 749M 717M sleep 17.0H 0.06% oracle  

Query using V$PROCESS / V$SESSION -

        SQL> select a.spid ,b.SID, b.SERIAL#, b.USERNAME, b.MACHINE, b.logon_time

	  2  from v$process a, v$session b
	  3  where a.addr = b.paddr
	  4* and a.spid in ('15791')
	SQL> /
	 
	SPID                SID    SERIAL# USERNAME
MACHINE                        LOGON_TIM
	------------ ---------- ----------


------------------------------ ------------------------------ ---------

	15791                60       8697 VAULT
ustusow001                     05-OCT-05
	

	
	
	  1  select ss.sid, ss.STATISTIC#, sn.name,
round(((ss.VALUE*.01)/3600),2) CPU_Hours
	  2  from v$sesstat ss , v$statname sn
	  3  where sn.name like( '%CPU%')
	  4   and ss.sid = 60
	  5* and ss.STATISTIC# = sn.STATISTIC#
	SQL> /
	 
	       SID STATISTIC# NAME
CPU_HOURS
	---------- ----------


----------------------------------------------------------------

----------

	        60         11 CPU used when call started
14.07
	        60         12 CPU used by this session
14.07
	        60        248 OS User level CPU time
0
	        60        249 OS System call CPU time
0
	        60        250 OS Other system trap CPU time

--

http://www.freelists.org/webpage/oracle-l Received on Mon Oct 24 2005 - 21:03:11 CDT