Real time performance tuning using Oracle SQL Developer
SQL developer is free tool can be downloaded from Oracle .I use it frequently as my performance tuning day to day arsenal.It is great tool to analyze both overall system performance as well as particular SQL tuning.
Let me go thorugh how we can use it to leverage it’s utility to dig down performance bottleneck.
1.Let us simulate the same test case which I explained in following link
2.SQL developer with system/power user.Let me navigate to Tools-> Monitor sessions.The session monitor console shows you all real time active and in-active sessions details
3.Now I will filter to only ‘ACTIVE’ sessions IN SQL DEVELOPER.
4.All active sessions are filtered.We can get various information IN SQL DEVELOPER.
.
INST_ID:-Instance id in case of RAC
SID=Session identifier
Username:-Schema name who is executing the query.
Seconds in wait:-Very important column to monitor how long the sessions are waiting.If it is small,means very short time it is executed which is good.Long time sometimes indicate problems.
Machine:-From which terminal the query is being executed.
Status:-Active means the session is actively executing the query.
Module:-From which module the query is being executed.
5.Now select the sessions individually and check Active SQL tab to check full SQL and SQL_ID IN SQL DEVELOPER.
.
6.You can check the execution plan as well from “Explain plan” tab.
7.This tab “Waits” is very important to check real time wait.We can see Event as “Enq:TX – row lock contention” IN SQL DEVELOPER.
8.This section “Server” shows various details like “Logon_time”,SPID details InSQL developer.
9.This tab “Client” informs from which machine and terminal user is connected.
10.”Application” tab shows what is command type and program responsible for the query execution.
11.The “Contention” session shows for what reason and who is blocking the transactions.This is very important section to identify waiting sessions IN SQL DEVELOPER.
12.This “Long Opsq” session in sql developer is also important to monitor and estimate long running sessions.There is a dynamic performance view v$session_longops that is populated for many long running operations in Oracle. The primary criterion for any operation to appear in v$session_longops is to run more than 6 seconds.
For more details on session long ops refer below:-
http://www.gplivna.eu/papers/v$session_longops.htm
