Here are some handy queries, which are helpful to find out the performance killing sessions.
It will be easy for DBA's to get the culprit process with a single query. Here are few for you.
-- Get SID with PID
set lines 200
pages 5000
col sid format
999999
col username
format a20
col osuser format
a15
select
b.spid,a.sid, a.serial#,a.username, a.osuser
from v$session a,
v$process b
where a.paddr=
b.addr
and
b.spid='&spid'
order by b.spid;
SPID SID SERIAL# USERNAME OSUSER
------------
------- ---------- -------------------- ---------------
7887 1518 31396 TEST_USER SYSTEM
-- Get Details for SID
set lines 200
pages 5000
col program for a25
col event for a40
select a.sid,
a.serial#, a.status, a.program, b.event,to_char(a.logon_time, 'dd-mon-yy
hh24:mi') LOGON_TIME,
to_char(Sysdate,
'dd-mon-yy--hh24:mi') CURRENT_TIME, (a.last_call_et/3600) "Hrs
connected" from v$session a,
v$session_wait b where
a.sid=&sid and a.sid=b.sid;
SID
SERIAL# STATUS PROGRAM EVENT LOGON_TIME CURRENT_TIME Hrs connected
-------
---------- -------- --------------------- -------------------------- ---------------------
---------------------- -------------
1518
31396 ACTIVE JDBC Thin
Client latch: library cache 14-mar-14 15:31 19-mar-14--11:43 110.82
-- Identifying
Sever Machine and Client Machine names of a session
col
server_machine for a15
col
client_machine for a15
select p.spid
server_pid, s.sid oracle_sid, s.machine client_machine,i.host_name
server_machine
from gv$session
s, gv$process p, gv$instance I
where
p.inst_id =
s.inst_id and
p.addr = s.paddr
and
i.inst_id =
s.inst_id and
p.spid =
'&server_pid';
SERVER_PID ORACLE_SID CLIENT_MACHINE SERVER_MACHINE
------------
---------- --------------- ---------------
7887 1518 prodweb20b prodinst5
-- Identifying Sever Machine and Client Machine names,
SQL details of a session
set head on
set lin 700
col "SQL
TEXT" for a100
col module format
a20
SELECT s.sid,
p.spid "OS Pid", s.module, s.process, s.username
"Username",
s.osuser "OS
User", s.program "Program", a.sql_id, substr(a.sql_text,1,550)
"SQL Text"
FROM v$session s,
v$sqlarea a, v$process p
WHERE
s.sql_hash_value = a.hash_value (+)
AND s.sql_address
= a.address (+)
AND s.paddr =
p.addr
and s.sid =
(select s.sid from v$session s, v$process p where s.paddr = p.addr and p.spid =
&p);
SID OS Pid MODULE PROCESS Username OS User Program SQL_ID SQL Text
-------
------------ -------------------- ------------ -------------------- -----------
--------------------- -------------
------------------------------------------------------------------------------------------------------------------------------------------------------
1518 7887 JDBC Thin Client 1234 TEST_USER SYSTEM JDBC Thin Client aqdb7xr4cm72h DELETE TEST_RECORDS WHERE
EVENTID = :B3 AND ACTID = :B2 AND INVDATE = :B1
