Identifying sessions using high amounts of CPU
- From: "Cirillo, Joe" <joe.cirillo@xxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 24 Oct 2005 21:00:17 -0500
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:
