From V$SQL_Monitor to DBA_HIST_REPORTS
Narendra, July 10, 2025 - 10:51 am UTC
I had a SR that went on for over a year where one of the questions that I asked, and remained unanswered, was "As long as sql is captured in v$sql_monitor and is retained for a reasonable time (few minutes) then why is it not stored in DBA_HIST_REPORTS?"
I would like to think that v$sql_monitor has been "designed" to hold data longer than "Cursor Cache" from the beginning and except from extreme circumstances, Historical SQL Monitoring should be able to store ALL sqls captured by Real-Time SQL Monitoring...or at least there should be a configuration option to make that happen.
Right now, my experience is data captured in DBA_HIST_REPORTS is "random" and many executions don't get stored even thogh they are captured by Real-Time SQL Monitoring.
This certainly reduces the usefulness of Historical SQL Monitoring feature, which is much more important in the ever-increasing cloudy world.