Oracle Database - VSQL_MONITOR

About

apps/search/search.jsp displays SQL statements whose execution have been (or are being) monitored by Oracle ( > 5 sec)

Articles Related

Status

SQL execution status:

• QUEUED - SQL statement is queued
• EXECUTING - SQL statement is still executing
  • DONE (ERROR) - Execution terminated with an error
  • DONE (FIRST N ROWS) - Execution terminated by the application before all rows were fetched
• DONE (ALL ROWS) - Execution terminated and all rows were fetched
• DONE - Execution terminated (parallel execution)

Normally, you have this status lifecycle: * QUEUED - SQL statement is queued

• EXECUTING - SQL statement is still executing
• DONE (ALL ROWS) - Execution terminated and all rows were fetched
• DONE - Execution terminated (parallel execution)

Sql

select 
  sql_exec_id, -- execution id
  sql_plan_hash_value, -- hash plan id
  mon.process_name, -- process name (if px_maddop = 2, two times the same process names
  mon.status, 
  mon.sid, -- Id
  mon.px_qcsid, -- Parent Id
  round((LAST_REFRESH_TIME - FIRST_REFRESH_TIME)*24*60) as elapsed_min, -- elapsed time
  FIRST_REFRESH_TIME,
  LAST_REFRESH_TIME,
  px_maxdop,
  px_servers_requested, -- total number of threads ? per process
  mon.sql_text, -- sql_text
  mon.* 
from v$SQL_MONITOR mon where sql_id = 'fj7xst7nvxjn3' -- sql_text is also possible
order by 
mon.sql_exec_id,
mon.sql_plan_hash_value,
mon.process_name;