Re: Oracle aborts monitoring a long running statement - how to find the reason

From: Stefan Koehler <>
Date: Wed, 6 Dec 2017 09:31:52 +0100 (CET)
Message-ID: <>

Hello Thomas,
as Jonathan pointed out - this is a known behavior of SQL Monitoring Report and works as designed:

However you might want to have a look at xplan_ash by Randolf Geist - a great tool for PX analysis based on ASH:

Best Regards
Stefan Koehler

Independent Oracle performance consultant and researcher Website:
Twitter: _at_OracleSK

> Thomas Kellerer hat am 6. Dezember 2017 um 09:00 geschrieben:
> Hello list,
> we are trying to troubleshoot a complicated query that used to run in under an hour and started to get extremely slow for unknown reasons ("extremely slow" means, it still isn't finished after 8 hours)
> Oracle did not monitor the statement even though it was running very long and was using parallel execution.
> In MOS we found that only statements with a plan shorter then 300 lines are monitored.
> We were able to change the parameter "_sqlmon_max_planlines" to 500 as suggested in DocID: 1613163.1.
> Now Oracle indeed _starts_ to monitor the statement.
> But after an hour or so, the status of the session(s) turns to "DONE (ERROR)" in v$sql_monitor, but the query keeps on running, so I am confused what the "ERROR" means there.
> My idea was to use the information provided by the real time monitoring to find the bottleneck of the statement but if it stops, this isn't really helpful.
> Is there any view where I can find the _reason_ for the "(ERROR)" or the reason why Oracle stops monitoring the statement?
> v$sql_monitor.error_message and v$sql_monitor.error_number are null for the sessions of that statement
> This is on a EE running on Linux
> (please don't comment on the old version - this is a hosted environment of one of our customers. At least an an upgrade to is planned).
> Thanks
> Thomas
> --

Received on Wed Dec 06 2017 - 09:31:52 CET

Original text of this message