Oracle Database - V$SQL_MONITOR

Card Puncher Data Processing

About

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

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;





Discover More
Card Puncher Data Processing
Oracle Database - (Actual|Final) Execution Plan

The actual or final plan is the execution that was executed in order to retrieve the result of a SQL. optimizerquery planrow source generator V view contains the actual plan for a query that...



Share this page:
Follow us:
Task Runner