Want to monitor or browse the sessions running on your database? You could query v or gv$session and get an idea of what’s happening. But often you want and need to join into waits, locks, performance data, plans, etc. to get a full picture of what your users and applications are doing in the database.

Enter the Tools – Monitor Sessions… screen.

Browse or kill peop…sessions!

This is actually a Master/Detail report! I know, shocking – right? You can find the same screen in SQL Developer under the Reports – Database Administration – Sessions. Folks had a hard time finding that screen so we copied this particular report up to the Tools menu.

Is it perfect? Not quite, but what software solution is? Fortunately since it’s a report, we can customize it to fit your needs. And by ‘we,’ I mean ‘you.’ Let’s work through a quick example.

My Monitor Sessions Enhancement Requests

  • No v$session_longops – how much time is left on my backup or index creation?
  • No execution plan for Active SQL
  • No bind variables
  • It queries v$ instead of gv$
  • SQL is hard to read, no carriage returns, bunch of extra spaces

How to Customize Monitor Sessions


To get started you need to find the report and copy and paste it under the ‘User Defined Reports’ section.

Once the report is in the User section, it’s yours to control. That means you can edit it to your heart’s delight. Now, before you spend a lot of time writing your own queries, be sure to check out the other Sessions reports. The ‘Active Sessions’ gives me most of what I was wanting above. It queries the GV$ view so I can see sessions across my RAC instance, it includes the plan, bind variables, and v$session_longops. So if that satisfies your needs, then feel free to stop now.

But if you want a combination of both Sessions and Active Sessions, then you’ll need to copy and edit one or the other.

I chose to start with Active Sessions and work in what I liked from Sessions and add my own stuff too.

What I Did

  • modified the master WHERE to include ALL sessions, not just ACTIVE ones
  • added SID and USERNAME to the master SELECT
  • renamed the Active SQL child report to Performance & Query
  • renamed the Explain Plan child report to V$SQL_PLAN
  • added a new child report – XPLAN
  • added a new child report – SQL

I didn’t do anything TOO crazy. For the XPLAN report, I wanted to see the FULL DBMS_XPLAN output for the specified SQL_ID. So I wrote this:

SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR((:SQL_ID)))

Remember, use the :COL_NAME syntax to tie the Child Report to the active data selected in the Master Report. So when I am looking at a session and I click on XPLAN, it automatically takes the SQL_ID from the master report and feeds it to the query in the child report.

The result looks like this:

DBMS_XPLAN results for current query executing

I did a little crazy work for the SQL child report though. Something that drives me nuts is the unformatted SQL in the Sessions and Active Sessions reports. Looks a little something like this:

The query text actually running right now

What I want to see is some text that’s a bit easier to read. Of course I could copy it out to the worksheet and format it, but remember – I’m lazy. So instead I spent about 40 minutes figuring out how to get those spaces replaced with something like a CR LF pair so it would display properly in my report.

Instead of boring you with the entire trial and error process, I’ll just show you the finished product and then share the code so you can borrow it if you’d like.

A little easier to read maybe?

This is an DBMS_OUTPUT (HTML) report. I can run a anonymous PLSQL block and write to DBMS_OUTPUT. If I include HTML tags, SQL Developer will render them in the report. I used this trick recently in a blog post for @martinberx.

So I queried the text INTO a character variable using a regular expression to replace multiple space characters with a single BR HTML tag, then printed that variable using a call to DBMS_OUTPUT.PUT_LINE(). Here’s the code:

declare
 code clob;
 
begin
select regexp_replace(sql_text, '( ){2,}', '<br>') into code
    from gv$sql
    where sql_id = :SQL_ID;
                                

dbms_output.put_line(code);
end;

Remember you have to set the Child Report type to ‘PLSQL DBMS Output.’

Now, some caveats. Please don’t blindly copy and paste my code. In other words, please test to make sure it does what I say it does. I’m grabbing sql_text, which is ONLY the first 1,000 characters of the query. You could query SQL_FULLTEXT instead.

If you build something that you think is extra-awesome and want to share it, you don’t need to start your own blog. Instead, you can submit it to the SQL Developer Exchange. If I were smart, I would have browsed the previously submitted Session based reports before I wrote my own, but that wouldn’t nearly have been as fun!