Oracle: V$SQL

v$sql_monitor provides a subset of the performance statistics (such as elapsed and CPU time, number of IO reads and writes, wait times etc.) that are also recorded in v$sql. Unlike v$sql, which accumulates those statistics over the time that an SQL statement is in the library cache, a record in v$sql_monitor contains the figures of one execution of an SQL statement.

«primary key»

The column key acts like a «virtual» primary key and identifies records in v$sql_monitor.

The value of key can be used to select the monitored statistics of the execution of an SQL statement from v$sql_plan_monitor.

select
   count(*),
   count(distinct key)
from
   v$sql_monitor;

Finding a monitor key

Most recent monitored SQL statements

The following query finds a session's most recent record in v$sql_monitor:

--
-- Simple select statement using the monitor hint for demonstration purposes.
--
select /*+ monitor */ * from dual;
 
--
-- Find record in v$sql_monitor that corresponds to most recent execution of an SQL statement in the session
--
select
   mon.*
from
   v$sql_monitor mon
where
   (mon.sid, mon.session_serial#,      mon.sql_id,  mon.sql_exec_id) = (select
    ses.sid,         ses.serial#, ses.prev_sql_id, ses.prev_exec_id
        from
           v$session ses
        where
           ses.sid = sys_context('userenv', 'sid')
   );

Find a monitor key for a given SQL statement

The following query joins v$session and v$sqlarea with v$sql_monitor in order to hopefully find a monitor key from a given SQL statement:

select
    mon.key              mon_key,
    mon.sql_exec_start   sql_exec_start,
    mon.status           mon_st,
    mon.sql_exec_id      sql_exec_id,
    ses.sid,
    ses.serial#,
    ses.osuser,
    sql.sql_text
from
    v$session               ses                                join
    v$sql_monitor           mon    on
            ses.sid              = mon.sid               and
            ses.serial#          = mon.session_serial#         join
    v$sqlarea               sql    on
            sql.sql_id           = mon.sql_id
where
    sql.sql_text = q'[select /*+ monitor */ * from …]'
order by
    mon.sql_exec_start desc;

Columns

REPORT_ID

report_id, if not 0, can be joined to dba_hist_reports.

Select statement

select
   key,           -- Use value of KEY to query records from v$sql_plan_monitor
   report_id,
   sql_id,
   refresh_count, first_refresh_time, last_refresh_time,
-- plsql_exec_time,
-- java_exec_time,
   sql_exec_start,
   status,
   username, -- user#
   elapsed_time,
   queuing_time,
   cpu_time,
   fetches,
   buffer_gets,
   disk_reads,
   direct_writes,
   io_interconnect_bytes,
   physical_read_requests,
   physical_read_bytes,
   physical_write_requests,
   physical_write_bytes,
   application_wait_time,
   concurrency_wait_time,
   cluster_wait_time,
   user_io_wait_time,
   module,
-- action,
-- service_name,
-- client_identifier, client_info,
   program,
   plsql_entry_object_id,
   plsql_entry_subprogram_id,
   plsql_object_id,
   plsql_subprogram_id,
   dbop_exec_id,
   dbop_name,
-- sid, session_serial#,
-- process_name,
   sql_text,
   is_full_sqltext,
   sql_exec_id,
   sql_plan_hash_value,
   sql_full_plan_hash_value,
   exact_matching_signature,
   force_matching_signature,
   sql_child_address,
   is_adaptive_plan,
   is_final_plan,
   px_is_cross_instance,
   px_maxdop,
   px_maxdop_instances,
   px_servers_requested,
   px_servers_allocated,
   px_server#,
   px_server_group,
   px_server_set,
   px_qcinst_id,
   px_qcsid,
   error_number,
   error_facility,
   error_message,
   binds_xml,                         -- Information about bind variables used in the statement
   other_xml,
   rm_last_action,
   rm_last_action_reason,
   rm_last_action_time,
   rm_consumer_group,
   con_id,
   con_name,
   ecid,
   in_dbop_name,
   in_dbop_exec_id,
   io_cell_uncompressed_bytes,
   io_cell_offload_eligible_bytes,
   io_cell_offload_returned_bytes,
   current_username   -- current_user#
from
   v$sql_monitor
where
   sid = 78
order by
   last_refresh_time desc;

Github repository oracle-patterns, path: /Installed/dynamic-performance-views/sql/monitor/select.sql

Oracle: V$SQL_MONITOR