Hi,
I learned about the SQL Monitor feature recently and have been using it to find steps in the execution plan where most of the time is being spent. I've found it to be very informative.
However, when I query gv$sql_monitor for some sqls that have been executed in the last 12 hours, I don't see them. From this whitepaper, I understand there is a data retention in SQL Monitoring.
The SQL Monitoring data in the SGA is not vulnerable to cursor age-outs from V$SQL; however it exists in a size-constrained in-memory buffer and will eventually be overwritten by statistics coming from new statements that are monitored. So, if the system has a lot of long-running SQL statements and Parallel Queries, not all SQL statements are guaranteed to be monitored because of two factors: space limitation of the in-memory buffer and a 5-minute retention guarantee for the SQL statements that made the monitored list. However, practically this should never happen.
The sql I am trying to find in gv$sql_monitor was executed very recently (in the last 12 hours). It used parallelism and the total CPU time was greater than 5 seconds. I see "older" sqls in the dictionary view.
--the sql_id that I can't find in gv$sql_monitor.
SQL> set echo on;
SQL> select sysdate, a.last_active_time,a.px_servers_executions,a.cpu_time,a.elapsed_time from gv$sql a where sql_id = 'gtv02b91ag1nw';
SYSDATE LAST_ACTIVE_TIME PX_SERVERS_EXECUTIONS CPU_TIME ELAPSED_TIME
-------------------- -------------------- --------------------- ---------- ------------
21-MAY-2019 08:08:38 20-MAY-2019 22:19:17 39 85281871 110961056
-- I see the dictionary view has entries for sqls that were executed almost 3 weeks ago.
SQL> select min(SQL_EXEC_START) from gv$sql_monitor;
MIN(SQL_EXEC_START)
--------------------
25-APR-2019 15:20:13
What am I missing?
Thanks!