Search notes:

Oracle: V$SQL_PLAN_MONITOR

v$sql_plan_monitor displays the recorded SQL execution statistics on plan operation level.

The statistics that correspond to one SQL Statement execution are identified by the column key whose value might be obtained from v$sql_monitor.

In order to gather data for SQL statements whose execution duration is less than five seconds, the /*+ monitor */ hint must be used.

select
   spm.starts,
   lpad(' ', spm.plan_depth * 2, ' ') || spm.plan_operation,
   spm.plan_options,
   spm.plan_object_owner,
   spm.plan_object_name,
   spm.output_rows,
   spm.physical_read_requests,
   spm.physical_read_bytes,
   spm.physical_write_requests,
   spm.physical_write_bytes,
   spm.workarea_mem,
   spm.workarea_max_mem,
   spm.plan_cost, spm.plan_cpu_cost, spm.plan_io_cost,
   spm.plan_time,
   spm.plan_bytes,
   spm.plan_temp_space,
   spm.*
from
   v$sql_plan_monitor spm
--where
  -- spm.key = 463856468142
order by
   spm.sql_exec_start desc,
   spm.plan_line_id;

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

TODO

What is the difference between v$sql_plan_monitor and v$all_sql_plan_monitor? Compare with v$sql_plan vs v$all_sql_plan.

Oracle: V$SQL_PLAN_MONITOR

TODO

See also