Troubleshooting Oracle Performance Problems

Troubleshooting Oracle Performance Problems!!
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
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 (227) AND (Q.SQL_ID = S.SQL_ID OR Q.SQL_ID = S.PREV_SQL_ID);
The output will be as follows.
SID SQL_TEXT ---------- ---------------------------------------------------------------------------------------------------------------------------------- 227 SELECT "VAR1"."CUSID","VAR1"."LOGIN","VAR1"."DOMAIN","VAR1"."UNILOGIN","VAR1"."EMAIL","VAR1"."STATUS","VAR1"."MYPSWRDT","VAR1"."MYPSWRD","VAR1"."VIEWT","VAR1"."MAXCON","VAR1"."CHARGERATE","VAR1"."TIMEREMAIN","VAR1"."CASHREMAIN","VAR1"."EXPIREDATE","VAR1"."LDAP_SERVER","VAR1"."MAIL_MB","VAR1"."CONTROL","VAR1"."TIMEZONE","VAR1"."SPEED","VAR1"."CTRL_SERVICE","VAR1"."PKGID","VAR1"."PPID","VAR1"."GID","VAR1"."MASTERACCT","VAR1"."FROM_WALLET","A1"."LASTACCESS","VAR1"."CREATEDATE","VAR1"."LASTUPDATE","VAR1"."OPR_BY","VAR1"."MYPSWRD2","VAR1"."MYPSWRDT2","VAR1"."VAS","VAR1"."CONTROL2","VAR1". "FIRSTACCESS" FROM "ACM" "VAR1" WHERE "VAR1"."GID"=:B1
227 SELECT "VAR1"."GID","VAR1"."ASSETNUM","VAR1"."BCYCLE","VAR1"."BAN","VAR1"."PKGID","VAR1"."STATUS","VAR1"."CREATEDATE" FROM "GROUPINFO" "VAR1" WHERE "VAR1"."ASSETNUM" IS NOT NULL AND "VAR1"."ASSETNUM"<>'020000000' AND "VAR1"."BCYCLE"=:B1
Oracle V$SESSION_WAIT_CLASS View:
Oracle view can be used to identify whether the session is bound mostly by user I/O and the like. It displays the time spent in various wait event operations on a per-session basis.
SELECT WAIT_CLASS_ID, WAIT_CLASS, TOTAL_WAITS, TIME_WAITED FROM V$SESSION_WAIT_CLASS WHERE SID = 25371; WAIT_CLASS_ID WAIT_CLASS TOTAL_WAITS TIME_WAITED ------------- ---------------------------------------------------------------- ----------- ----------- 1893977003 OTHER 1224 39 3835070507 CONCURRENCY 711 120 27233371728 IDLE 2118450 121891 20071174339 NETWORK 2118520 273 17407277717 USER I/O 1484880 37155715 38713711733 CLUSTER 972791 2102717 71 ROWS SELECTED.
Oracle V$SYSTEM_EVENT view:
You can use V$SYSTEM_EVENT view to get an insight into the system events that are currently happening within your system. For example we can focus on the USER I/O and find timed wait on V$SYSTEM_EVENT view.
SELECT EVENT, TOTAL_WAITS, TIME_WAITED FROM V$SYSTEM_EVENT E, V$EVENT_NAME N WHERE N.EVENT_ID = E.EVENT_ID AND E.WAIT_CLASS_ID = 17407277717 ; EVENT TOTAL_WAITS TIME_WAITED ----------------------------------------- ----------------------- --------------------- DATA FILE INIT WRITE 43 9 LOCAL WRITE WAIT 352 47 READ BY OTHER SESSION 2371087 714739 DB FILE SEQUENTIAL READ 41918354 271039340 DB FILE SCATTERED READ 19110727 7177108712 DB FILE SINGLE WRITE 93971 2739 DB FILE PARALLEL READ 30337183 227114971 DIRECT PATH READ 248340 8235 DIRECT PATH READ TEMP 20547719 122773 DIRECT PATH WRITE 109045 857 DIRECT PATH WRITE TEMP 1137183 22748 11 ROWS SELECTED.
Here we can see that the DB FILE SEQUENTIAL READ event has maximum total wait and maximum time waited. Now what you have to do is to enable the trace file on session 227 and then find out where the database has been waiting.