Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Ingimundur.

Asked: July 08, 2025 - 8:32 am UTC

Last updated: July 10, 2025 - 2:56 am UTC

Version: 19.27

Viewed 100+ times

You Asked

Hi Team,

Recently, while reading articles by Maria Colgan, I found out that I was able to generate reports from the v$sql_monitor info that help me alot when I am trying to find out what is happening to the performance on our systems. I am also using the historic view DBA_HIST_REPORTS and this has been a big help to me.

I am working with a number of different database and I have seen different behaviour when it comes to the retention of the data in v$sql_monitor and then as a consequence their transfer to the DBA_HIST_REPORTS.

Most of the DBs I work on seem to have a 8-12 hour retention which seems fair, that way I am able to see the nightly runs when I arrive in the morning. But I have one PBD that seems to only hold the data for 15 minutes and it transfers less than 5% to the DBA_HIST_REPORTS (others are transferring 30-40%).

Where can I see how this is setup for the particular PBD? Other PBDs on the same CDB are not having the same problems.

Thanks in advance,
Ingimundur K. Gudmundsson

and Connor said...

If things are disappearing fast in particular PDB, it typically means a *lot* is being monitored (eg lots of MONITOR hints, stats level set to all, or lots of long running, hence auto-monitored, SQL statements).

v$sql_monitor retention is governed by SGA size and some internal parameters.

I can't officially endorse the use of hidden parameters, but you might find the following blog post "interesting" :-)

https://chandlerdba.com/2019/01/22/oracle-sql-monitor-not-monitoring-my-sql/


Rating

  (1 rating)

Comments

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.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Multitenant

Need more information on Multitenant? Check out the Multitenant docs for the Oracle Database