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):
USERNAME | FIRSTNAME | LASTNAME |
---|---|---|
WINDOWS10VM\williamr | William | Robertson |
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;
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 thelist
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 joiningv$session
tov$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.