RE: Identifying sessions using high amounts of CPU

Joe,

SELECT CPU_TIME FROM v$sqlarea;
Not user it that works either???

Just a guess here but is this not a case where CPU time (according to oracle db) is not equal to human time? I see to remember looking into this stuff (CPU time conversion) only to find at the end an Oracle reference that said so. Or simply a Linux CPU cycle is not the same (time) as an Oracle cpu cycle?

Chris Marquez
Oracle DBA

-----Original Message-----

From: oracle-l-bounce_at_freelists.org on behalf of Cirillo, Joe Sent: Mon 10/24/2005 10:00 PM
To: oracle-l_at_freelists.org
Subject: 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 Tue Oct 25 2005 - 09:21:45 CDT