Sunday, 25 June 2017

Monitor Oracle Database Sessions using SQL Developer

Oracle maintains database runtime statistics related to performance in Dynamic Performance Views i.e v$ and gv$ views. Use Views like v$session, v$sql, v$sqltext to see sessions, SQL statement executing in the database.

These views are good for DBA, as a developer you shouldn’t worry about it. Sometimes you may want to see,

  • What is happening in Database?
  • Where is the program stuck?
  • Which is current executing SQL?

Read:- Query to get current executing statement in concurrent program

SQL Developer or TOAD provides a graphical user interface to see the Database Session, SQL, Explain Plan, Locks.

Let’s see how to use SQL Developer to Monitor Oracle Database Session.

SQL Developer Session Dashboard

Open SQL Developer and Connect to Oracle Database. Click on menu Tools->Monitor Session…

It opens a dashboard as shown below which displays all required information about the session like INST_iD, SID, SERIAL, SQL_ID, Username, Seconds in Wait, Machine, OS User, Status, Module.

sql developer monitor session

Above image highlights some key buttons. Click on Arrow(1) to refresh dashboard on demand, You can also specify refresh interval (2) in seconds. There is filter(3) option available for each column to filter respective records.

The grid at the end displays additional information like Active SQL, Explain Plan, Waits, Server, Client, Application, Contention and Long Opsq.


Leave a Reply

%d bloggers like this: