Finding and Monitoring SQLs in Execution

 Dictionary view V$SQL_MONITOR is used to find out SQLs currently in execution and a little history of SQLs executed in the recent past. There are several columns in this view that can be useful in different scenarios. Most common use it to find out the elapsed time, CPU time etc. For parallel executions, each parallel slave would appear in this view with column PX_QCSID showing SID of the query coordinator session (session that executed the SQL). For example, if an SQL is executing with 8 parallel servers, there would be 9 rows in this view (WHERE sql_id = <SQL_ID> AND STATUS = ‘EXECUTION’).

Following is a sample query to fetch details of an SQL that is executed several times, and current in execution. SQL_EXEC_ID would differentiate each instance of this serially executed SQL.

SQL> select SQL_EXEC_ID,status,elapsed_time/1000000 EXECUTION_SECONDS from gv$sql_monitor where sql_id='f45x6hre2s9ka' order by inst_id,sql_exec_id;     SQL_EXEC_ID    STATUS                         EXECUTION_SECONDS ---------- ----------- ------------------- ------------------ ------- 33554500       DONE (ALL ROWS)                      149 33554501       DONE (ALL ROWS)                      151 33554502       DONE (ALL ROWS)                      151 33554503       DONE (ALL ROWS)                      148 33554504       DONE (ALL ROWS)                      151 33554505       DONE (ALL ROWS)                      152 33554506       DONE (ALL ROWS)                      154 33554507       DONE (ALL ROWS)                      157 33554508       DONE (ALL ROWS)                      156 33554509       DONE (ALL ROWS)                      156 33554510       DONE (ALL ROWS)                      150 33554511       DONE (ALL ROWS)                      167 33554512       DONE (ALL ROWS)                      161 33554513       EXECUTING                            28