One of the sessions having slow performance when compared to other sessions

Mar 3, 2011

—

by

If one of the sessions is having a slow performance while compared to other sessions, then this is because, this session is waiting for some event. Foremost, 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 types 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 many DML operations. 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 hours. At 12 AM, another user connected to the database tried to update some objects held 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;

We could not identify 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.

Tags:

Edward Ramamoorthy

I work for one of the top 10 tech companies in India. In my spare time, I write for PrimeInspiration.com.

Help Us Grow

If you like this post, please share it with your friends.

You are free to copy and redistribute this article in any medium or format, as long as you keep the links in the article or provide a link back to this page.