Sunday 30 March 2014

Performance Issues – Get SQL and session details with OS process ID

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