Oracle Database - V$SQL_MONITOR

> Database > Oracle Database

1 - About

V$SQL_MONITOR displays SQL statements whose execution have been (or are being) monitored by Oracle ( > 5 sec)

3 - 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)
Advertising

4 - 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;