One of the best new features to come out in recent years (well, since 11g) is SQL Monitor. I’m going assume that you’re basically familiar with what SQL monitor is and how it works.
Like many Oracle technologies, SQL monitor has a graphic interface – which you can access via OEM – and it has a set of underlying views in the data dictionary.
I’m not going to get into a discussion about whether graphic or text based interfaces are better. I think it’s obvious that both are useful in different ways. I like the SQL Monitor interface in OEM, and I use it pretty much every day.
But there are times when it’s handy to access the same SQL monitor info through SQL developer or SQLplus. Sometimes the database you’re after isn’t in OEM. And of course the great thing about writing your own reports or queries is that you can customise it to whatever degree you wish. Perhaps adding or removing columns to create a view of the SQL running that makes sense to you.
And of course, not everyone has access to OEM. I was talking to a developer the other day who didn’t have access to OEM. He asked me to check on how some SELECT statements he’d written were progressing. I was happy to help, but afterwards it occurred to me that every developer – regardless of whether they have access to OEM or not – ought to be able to see basic information about SQL that they’ve written. The statistics i’m talking about are things like Logical IO, physical reads and writes, and temp space usage.
So, as long as you have access to V$SQL_MONITOR and V$SQL_PLAN_MONITOR, you can run the following query. There’s nothing particularly complicated about it. The main thing you need to know when writing queries for V$SQL_MONITOR is that it includes information on each parallel process for a SQL. If you want to look at the SQL_ID level, you need to sum the stats for the parallel processes, and that’s why I’ve included a couple of summary sub-queries at the start.
So, here is the SQL monitor query. If you click on the github link here sql_monitor.sql, you’ll find a version of this which is parameterised to be run as a SQL script, complete with default parameters. I generally run it with SQL_ID=% and STATUS=EXECUTING, as I’ve indicated here.
define SQL_ID=%
define STATUS=EXECUTING
prompt
prompt
prompt SQL Monitor :
prompt
prompt SQL_ID : &SQL_ID
prompt Status : &STATUS
prompt
prompt
COLUMN INST_ID FORMAT 99
COLUMN module FORMAT A20 TRUNCATE
COLUMN sid FORMAT 99999
COLUMN STATUS FORMAT a8
COLUMN buffer_gets FORMAT 999,999,999,999
COLUMN username FORMAT A15
COLUMN sql_text FORMAT A200 TRUNCATE
COLUMN program FORMAT A20 TRUNCATE
COLUMN osuser FORMAT A10 TRUNCATE
COLUMN status FORMAT A20 TRUNCATE;
COLUMN sql_id FORMAT A14
COLUMN current_workarea_mem_mb FORMAT 999,999,999
COLUMN current_temp_mb FORMAT 999,999,999
COLUMN max_temp_mb FORMAT 999,999,999
COLUMN max_workarea_mem_mb FORMAT 999,999,999
COLUMN sql_exec_start FORMAT A20
COLUMN total_px_buffers FORMAT 999,999,999,999
COLUMN TOTAL_PX_PHYS_READ_MB FORMAT 999,999,999,999
COLUMN TOTAL_PX_PHYS_WRITE_MB FORMAT 999,999,999,999
COLUMN sql_PLAN_HASH_VALUE FORMAT 999999999999999
COLUMN etime_mins FORMAT 999,999.9
COLUMN px_servers_allocated FORMAT 999
COLUMN sql_exec_id FORMAT 999999999
COLUMN rows_process FORMAT 999,999,999,999
WITH
sql_monitor_plan_summary AS
(
SELECT
sql_id,
sql_exec_id,
SUM(workarea_mem) /(1024*1024) current_workarea_mem_mb ,
SUM(workarea_tempseg) /(1024*1024) current_temp_mb ,
SUM(workarea_max_tempseg)/(1024*1024) max_temp_mb,
SUM(workarea_max_mem) /(1024*1024) max_workarea_mem_mb,
SUM(output_rows) rows_processed
FROM
gv$sql_plan_monitor
WHERE
sql_id LIKE '&SQL_ID'
GROUP BY
sql_id,
sql_exec_id
)
,
sql_monitor_summary AS
(
SELECT
sql_id,
sql_exec_id,
SUM(buffer_gets) total_px_buffers,
SUM(physical_read_bytes) /(1024*1024) total_px_phys_read_mb,
SUM(physical_write_bytes)/(1024*1024) total_px_phys_write_mb,
COUNT(px_server#) total_px_servers,
( max(last_refresh_time) - min(first_refresh_time) )*60*24 px_etime_mins
FROM
gv$sql_monitor
WHERE
sql_id LIKE '&SQL_ID'
GROUP BY
sql_id,
sql_exec_id
)
SELECT
SM.inst_id,
SM.sid,
SM.session_serial# serial#,
SM.username,
SM.program,
SM.module,
SESS.osuser,
SM.sql_id,
SM.sql_exec_id,
DECODE(SQL.command_type, 1,'CRE TAB', 2,'INSERT', 3,'SELECT', 6,'UPDATE', 7,'DELETE', 9,'CRE INDEX',
12,'DROP TABLE', 15,'ALT TABLE',39,'CRE TBLSPC', 42, 'DDL', 44,'COMMIT',
45,'ROLLBACK', 47,'PL/SQL EXEC', 48,'SET XACTN', 62, 'ANALYZE TAB', 63,'ANALYZE IX',
71,'CREATE MLOG', 74,'CREATE SNAP',79, 'ALTER ROLE', 85,'TRUNC TAB' ) type,
SM.status,
SM.sql_exec_start,
SMS.px_etime_mins etime_mins,
SM.sql_plan_hash_value,
SM.px_servers_allocated,
SMS.total_px_buffers,
SMS.total_px_phys_read_mb,
SMS.total_px_phys_write_mb,
SPS.current_temp_mb ,
SPS.max_temp_mb,
SPS.current_workarea_mem_mb,
SPS.max_workarea_mem_mb,
SPS.rows_processed
FROM
gv$sql_monitor SM
INNER JOIN gv$session SESS ON SM.sid = SESS.sid AND SM.inst_id = SESS.inst_id and SM.SESSION_SERIAL# = SESS.SERIAL#
INNER JOIN gv$sql SQL ON SM.sql_child_address = SQL.child_address AND SM.inst_id = SQL.inst_id
INNER JOIN sql_monitor_plan_summary SPS on SM.sql_id = SPS.sql_id and SM.sql_exec_id = SPS.sql_exec_id
INNER JOIN sql_monitor_summary SMS on SM.sql_id = SMS.sql_id and SM.sql_exec_id = SMS.sql_exec_id
WHERE SM.sql_id like '&SQL_ID'
AND SM.status like '%&STATUS%'
and (SM.process_name = 'ora' or SM.process_name like 'j%')
and SM.elapsed_time > 0
ORDER BY SM.inst_id, SM.sql_exec_start;
And here is the kind of output you can expect.
INST_ID SID SERIAL# USERNAME PROGRAM MODULE OSUSER SQL_ID SQL_EXEC_ID TYPE STATUS SQL_EXEC_START ETIME_MINS SQL_PLAN_HASH_VALUE PX_SERVERS_ALLOCATED TOTAL_PX_BUFFERS TOTAL_PX_PHYS_READ_MB TOTAL_PX_PHYS_WRITE_MB CURRENT_TEMP_MB MAX_TEMP_MB CURRENT_WORKAREA_MEM_MB MAX_WORKAREA_MEM_MB
------- ------ ---------- --------------- -------------------- -------------------- ---------- -------------- ----------- ----------- -------------------- -------------------- ---------- ------------------- -------------------- ---------------- --------------------- ---------------------- --------------- ------------ ----------------------- -------------------
1 1268 48935 APP_USR perl@server-0021.yyy perl@server-0021.yyy appluser 8wwntpk60d124 16777275 PL/SQL EXEC EXECUTING SAT 01-01-2015 08:49 149.0 0 217,164,787 121,441 111,877
1 1268 48935 APP_USR perl@server-0021.yyy perl@server-0021.yyy appluser 8fvwfk50sx86b 16777216 CRE TAB EXECUTING SAT 01-01-2015 08:50 148.4 3844313563 216,870,638 120,626 111,062 14,419 116,393 78 2,500
1 1833 1077 APP_USER_TEST perl@server-0021.yyy perl@server-0021.yyy appluser 9w6m14g6wj5jv 16777236 PL/SQL EXEC EXECUTING SAT 01-01-2015 09:49 88.7 0 166,652,767 1,513,061 2,111
1 325 22647 APP_TOP_ perl@server-0021.yyy perl@server-0021.yyy appluser 9n62njadtbctd 16777276 PL/SQL EXEC EXECUTING SAT 01-01-2015 11:04 .2 0 237,306 3,393 27
1 453 15331 APP_USR perl@server-0021.yyy perl@server-0021.yyy appluser 8wwntpk60d124 16777277 PL/SQL EXEC EXECUTING SAT 01-01-2015 11:09 9.4 0 64,832,175 17,710 19,998
1 2146 3765 APP_USR perl@server-0021.yyy perl@server-0021.yyy appluser dx1n6h21xcnyc 16777258 PL/SQL EXEC EXECUTING SAT 01-01-2015 11:09 9.4 0 46,937,787 19,528 18,650
1 320 44201 APP_USR perl@server-0021.yyy perl@server-0021.yyy appluser 51ajz82mxknwy 16777249 PL/SQL EXEC EXECUTING SAT 01-01-2015 11:09 9.4 0 45,039,079 18,369 19,986
1 2146 3765 APP_USR perl@server-0021.yyy perl@server-0021.yyy appluser gwddvrfk2mqa7 16777216 CRE TAB EXECUTING SAT 01-01-2015 11:09 8.8 446988090 46,732,020 18,713 17,836 6,057 18,377 1,072 3,984
1 453 15331 APP_USR perl@server-0021.yyy perl@server-0021.yyy appluser avaqpptzs7hbw 16777216 CRE TAB EXECUTING SAT 01-01-2015 11:09 8.8 690084832 64,609,821 16,895 19,183 10,933 19,654 488 1,517
1 320 44201 APP_USR perl@server-0021.yyy perl@server-0021.yyy appluser 59h31z9sakfsa 16777216 CRE TAB EXECUTING SAT 01-01-2015 11:09 8.8 2810720600 44,822,192 17,545 19,172 8,418 19,834 122 1,614
1 2338 38579 APP_STREET JDBC Thin Client JDBC Thin Client cbesp g69575z5yd5up 16778421 SELECT EXECUTING SAT 01-01-2015 11:18 .4 1207590130 106,922 1,626 0 111 112
1 1833 1077 APP_USER_TEST perl@server-0021.yyy perl@server-0021.yyy appluser 5zfwgwsnjczxc 16777231 INSERT EXECUTING SAT 01-01-2015 11:18 .0 1810679145 418,412 0 25 19 19
2 258 2147 APP_USER_TEST busapp.exe busapp.exe offffdd 1pxp78r03c4nr 33554432 SELECT EXECUTING SAT 01-01-2015 10:08 70.6 436484469 4 5,166,324 278,780 236,298 144,843 251,500 1,464 9,747
2 2397 18299 APP_LA busapp.exe busapp.exe gggdddd cs5y231skk8b4 33554432 SELECT EXECUTING SAT 01-01-2015 10:43 35.5 682809495 4 14,489,427 215,622 188,228 191,231 191,231 1,753 1,852
2 2711 9249 APP_USER_TEST busapp.exe busapp.exe obadfba f3gr4haqzdr3g 33554432 SELECT EXECUTING SAT 01-01-2015 10:44 33.9 2561476696 4 4,657,369 119,783 85,022 31,704 90,691 796 3,998
2 890 3243 APP_USER_TEST busapp.exe busapp.exe dfdfbdf 1rrdacgjm6xw0 33554432 SELECT EXECUTING SAT 01-01-2015 10:54 24.3 1807896284 4 5,154,317 129,149 114,345 98,533 120,748 1,837 6,467
2 2338 7733 APP_LA busapp.exe busapp.exe bbbbsdd 8rt27b96fbnnt 33554432 SELECT EXECUTING SAT 01-01-2015 10:54 24.3 4070286322 4 2,855,592 84,963 87,832 57,620 93,694 1,623 6,809
2 1014 2417 APP_USER_TEST boe_jobcd@xzur2551vp boe_jobcd@xzur2551vp tstuser gwcxff3jmu3k1 33554432 SELECT EXECUTING SAT 01-01-2015 11:00 18.0 1116075569 4 384,492,336 51,134 76,931 76,351 78,130 1,710 3,951
2 444 5817 APP_USER_TEST busapp.exe busapp.exe bbbdddd 08gggs3zv3zda 33554432 SELECT EXECUTING SAT 01-01-2015 11:08 10.5 1222214853 4 7,076,391 104,073 54,039 54,904 54,904 1,774 1,791
2 822 30271 APP_LA busapp.exe busapp.exe aeeeddd 47wwvtk53f51x 33554432 SELECT EXECUTING SAT 01-01-2015 11:09 9.3 2778410910 4 12,393,726 32,093 18,050 330 18,427 1,072 6,801
Paul Stuart's Oracle Blog 