If one of the session is having a slow performance while compared to other sessions, then this is because, this session is waiting for some event. First of all we go to tune the Query using EXPLAIN PLAN / EXECUTION PLAN.
If we face the same problem even after that, then we approach it with the below steps.
Steps:
- We will check any objects are locked by particular sessions?
Select * from dba_waiters;Select * from dba_blockers;
- Respective query return value, we know easily particular session holding & waiting for what type of objects?
- Pass the holding Sid, to find out what are the queries running by this Sid?
SELECT sql_text
FROM v$sqltext_with_newlines
WHERE address = (SELECT prev_sql_addr
FROM v$session
WHERE username = &uname AND SID = &snum)
ORDER BY piece;
Note: Pass the Username & holding Sid value.
- Now we decide, “Kill the holding session or waiting session”? (Based on our business needs.)
Example: I have run the one EOD application. In this EOD application contains large number of DML operation. At the end of the EOD we have given the COMMIT. So due to COMMIT, while running the EOD application large number of Objects are locked. Once EOD completed objects are released. (Mostly EOD applications are running on non-peak hours).Now I have executed the EOD application at 10 PM. It took 5 hour. At 12 AM another user connected to database try to update the some objects holded by EOD application session. (Now user session is hanging)At that time I have checked any objects are locked. I have found out the Sid & decided which sessions to be killed. (EOD session or another session). I have checked out the EOD application will complete within 1 hour. Already it took 4 hours. So I have decided to kill the user session. (Remember do this, depending upon your business needs).
- No locked objects belong to our query objects. So we find out how many sessions access my objects?
Example: I have executed one procedure PROC_TEST. How many users to access the PROC_TEST?
Using the below to find out the user.
Select
V$S.LOGON_TIME,
V$S.SID,V$S.SERIAL#,
V$S.USERNAME,
V$S.PROCESS,
V$P.SPID,
V$S.STATUS,
V$S.MACHINE,
V$S.CLIENT_INFO
from v$session V$S,
v$process V$P
where V$S.PADDR = V$P.ADDR
and V$S.SID in
(Select distinct sid from v$access where object like (‘%&obj%’))
order byV$S.PROCESS,V$S.SID;
- Wecouldn’tidentify the potential cause root of the query so we will go to the SQL TRACE.
- Generate the Trace file of the query.
- Using TKPROF to interpret the output for readability.
