Troubleshooting Oracle Performance Problems!!

 Author: J. Hunter

Page: 1 2

It feels very frustrating when problems peep into your database and make it run slow. Well no need to worry any more as today we will discuss some efficient tips to determine the problems that makes your database run slow.

Find Session Waiting:

Oracle V$SESSION view lists the session information for all current sessions. First of all we will query V$SESSION view in order to find out the session waiting without Idle event.

SELECT SID, USERNAME, EVENT, BLOCKING_SESSION, SECONDS_IN_WAIT, WAIT_TIME
FROM V$ SESSION WHERE STATE = 'WAITING' AND WAIT_CLASS != 'IDLE';

We get the output as follows that shows that our user HR is waiting on ENQ: TX - ROW LOCK CONTENTION event that is blocked by Session 11.

SID USERNAME EVENT BLOCKING_SESSION SECONDS_IN_WAIT WAIT_TIME
------ ------------------ ---------------------------------------------------- ------------------------------- --------------------------- -----------------
27 HR ENQ: TX - ROW LOCK CONTENTION 11 111 0

Find SQL Statement:

Now we will query the V$SESSION and V$SQL views so as to find out the SQL statements.

SELECT SID, SQL_TEXT
FROM V$SESSION S, V$SQL Q
WHERE SID IN (27,11) AND (Q.SQL_ID = S.SQL_ID OR Q.SQL_ID = S.PREV_SQL_ID);

The output will be as follows. Here we can see that our Session 11 has blocked Session 27. Therefore if we COMMIT or ROLL BACK Session 11 then session 27 will continue waiting for the lock.

SID SQL_TEXT
---------- -------------------------------------------------------------
11 SELECT * FROM BIG_TABLE FOR UPDATE

27 SELECT DECODE(‘VAR','VAR','3','4') FROM DUAL

27 SELECT DECODE(‘VAR','VAR','3','4') FROM DUAL

27 DELETE FROM BIG_TABLE

Kill Session:

Now we will kill the session 11 and apply commit or roll back on session 11.

SELECT SID, USERNAME, EVENT, BLOCKING_SESSION, SECONDS_IN_WAIT, WAIT_TIME
FROM V$ SESSION WHERE STATE = 'WAITING'
AND WAIT_CLASS != 'IDLE';
no rows selected

Find Waiting Session:

In other case we find that now no session is blocking another session but this time our session 227 is waiting for DB FILE SEQUENTIAL READ event.

SELECT SID, USERNAME, EVENT, BLOCKING_SESSION,SECONDS_IN_WAIT, WAIT_TIME
FROM V$ SESSION WHERE STATE = 'WAITING'
AND WAIT_CLASS != 'IDLE';

We find the output as follows

SID USERNAME EVENT BLOCKING_SESSION SECONDS_IN_WAIT WAIT_TIME
--------------------------------------------------------------------------------------------------------------
227 MANAGER      DB FILE SEQUENTIAL READ         0          0 Continued...

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   RMAN Recovery Made Easy!!
   The OS debate: Oracle on Linux - Oracle on Windows
   Important Concerns related to Oracle Compression!!
   The power of Oracle table index rebuilding!!