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@xxxxxxxxxxxxx on behalf of Cirillo, Joe
Sent: Mon 10/24/2005 10:00 PM
To: oracle-l@xxxxxxxxxxxxx
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
0

Other related posts: