Interactive SQL Monitor for PL/SQL Developer

This is a script you can run in a PL/SQL Developer SQL Window, where it will pop up a list of currently active sessions, and then list all the monitored executions for the selected session. It's for PL/SQL Developer specifically, because it uses PL/SQL Dev's extended substitution variable syntax which can pop up a list of options based on a SQL query.

For the pop-up list I have used a made-up table APP_USERS containing firstname and lastname for each OS user, so for example if I'm logged in as WINDOWS10VM\williamr then joining v$session to app_users lets me show the session user as 'William Robertson' in the pop-up. I've done this because the application I work on has a table like this that's really handy in performance monitoring reports, where we can show people's full names rather than a system ID, so if you have a table like that, replace app_users in my query with your equivalent table. If not, you can remove it and just use osuser.

My demo app_users table (change the script to use your own):

Now I'll start a long-running query:

select count(*) from bigtable cross join bigtable;

Running the SQL Monitor query gives this: (if there were other sessions running you could pick the one you want from the list)

Selecting it gives this:

and opening the CLOB gives an HTML report like this in your browser:

If PL/SQL Developer doesn't open the CLOB in your web preferred browser, set it up using something like this:

Here's the script (download link at the bottom):

--tab=SQL Monitor
select replace(dbms_sql_monitor.report_sql_monitor(sql_id => m.sql_id, session_id => m.sid, session_serial => m.session_serial#, sql_exec_id => m.sql_exec_id, sql_exec_start => m.sql_exec_start, type => 'ACTIVE', report_level => 'ALL' ), 'http:','https:') as report, 
       m.sql_id, m.sql_exec_id, m.sql_plan_hash_value 
     , m.sql_exec_start --, m.first_refresh_time 
     , m.last_refresh_time 
     , m.status 
     , cast(numtodsinterval(m.last_refresh_time - m.sql_exec_start,'DAY') as interval day(1) to second(0)) as monitor_elapsed  -- Time calculated from last_refresh_time - last_refresh_time 
     , rpad('|', 100* ratio_to_report(m.last_refresh_time - m.sql_exec_start) over (), '|') as monitor_elapsed_graph 
     --, cast(numtodsinterval(m.elapsed_time/1e6,'SECOND') as interval day(1) to second(0)) as elapsed_time  -- V$SQL_MONITOR.ELAPSED_TIME: Elapsed time (in microseconds); updated as the statement executes 
     --, rpad('|', 100* ratio_to_report(m.elapsed_time) over (), '|') as elapsed_time_graph 
     --, cast(numtodsinterval(m.cpu_time/1e6,'SECOND') as interval day(1) to second(0)) as elapsed_cpu 
     --, m.buffer_gets 
     , o.object_name as plsql_unit 
     , s.program_line# as plsql_line# 
     , m.sql_text 
from   v$sql_monitor m 
       left join v$sql s on s.sql_id = m.sql_id and s.child_address = m.sql_child_address 
       left join all_objects o on o.object_id = s.program_id 
       --join v$session s on s.sid = m.sid and s.serial# = m.session_serial#  -- Currently active sessions only
where  (m.sid, m.session_serial#) in
       ((&< name="SID, Serial#" description="yes" list="select distinct 
               nvl2(px.qcsid, px.qcsid||', '||nvl(px.qcserial#,px.serial#), se.sid||','||se.serial#) 
             , nvl2(px.qcsid, px.qcsid||', '||nvl(px.qcserial#,px.serial#), se.sid||','||se.serial#)||'  '||initcap(u.firstname||' '||u.lastname) 
        from   v$session se 
               join v$sql_monitor mm on mm.sid = se.sid and mm.session_serial# = se.serial# 
               left join v$px_session px on px.sid = se.sid 
               left join app_users u on u.username = se.osuser 
        where  se.type = 'USER' and se.status = 'ACTIVE' and se.sql_id is not null 
        and    se.audsid != sys_context('userenv','sessionid') 
        and    se.osuser <> 'oracle' 
        and    se.sql_id is not null order by 1">)) 
--and    lower(substr(ltrim(m.sql_text),1,5)) not in ('begin','decla')  -- exclude top-level calling block 
and    not regexp_like(m.sql_text, '^\s*(begin|declare)', 'i') 
order by m.sql_exec_start desc;

-- Or if you know the SQL ID:
select replace(dbms_sql_monitor.report_sql_monitor 
       ( sql_id => '&sql_id', type => 'ACTIVE', report_level => 'ALL' ) 
       , 'http:','https:') as report 
from dual;

Source code: //www.williamrobertson.net/code/SQL-Monitor.sql  

A note about the pop-up syntax

This really deserves a separate article, but for now notice that where classic SQL*Plus substitution variables consist of a simple one-word name, for example &SID, PL/SQL Developer provides a whole set of attributes of which name is just one. As the attribute values can be enclosed in double quotes, the names can include spaces, for example &"SID, Serial#". The user manual gives the full list of options (see Chapter 12, 'Reports' - scroll down till you come to 'Variables'), but for this script the other attributes I have used are:

&<
name="SID, Serial#"
description="yes"
list="select code, description from ... where ..."
>

• name: The name of the substitution variable, which appears in the pop-up prompt.
• description: This modifies the list attribute below, and lets it have two columns. The second column is what's displayed to the user as a list of values, but the actual value to be used will be taken from the first column.
• list: this can be a hardcoded list separated by commas, or (as I've done here) a SQL query. I've used a query joining v$session to v$sql_monitor to list currrently active sessions with monitoring information. In my query, the second column is what will be displayed in the popup, while the value in the first column is the sid/serial# combination that will get plugged into the enclosing query.