Monitoring Database Sessions using SQL Developer

SQL Developer or TOAD provides a graphical user interface for monitoring database sessions. It shows Active SQL, Explain Plan, Locks, Wait and lot more information. This feature is very much useful for Database Administrator and even developers.

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.

This feature is useful to check

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

Read: –¬†Query to get current executing statement in concurrent program

In this article, I will show 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.

  • Active SQL – shows current active SQL executing in the database for selected session.
  • Explain Plan – shows explain plan for active SQL.
  • Waits – shows wait event information.
  • Server – shows information like session status, username, server information.
  • Client – shows connected client information.
  • Application – shows application information.
  • Contention – shows contention information in database.
  • Long Opsq – shows long operations



Related Post