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

From: Stefan Koehler <contact_at_soocs.de>
Date: Wed, 6 Dec 2017 09:31:52 +0100 (CET)
Message-ID: <2012196122.13151.1512549112663_at_ox.hosteurope.de>


Hello Thomas,
as Jonathan pointed out - this is a known behavior of SQL Monitoring Report and works as designed: https://hourim.wordpress.com/2015/06/23/real-time-sql-monitoring-oddity/

However you might want to have a look at xplan_ash by Randolf Geist - a great tool for PX analysis based on ASH: https://github.com/randolfgeist/oracle_scripts/blob/master/xplan_ash.sql

Best Regards
Stefan Koehler

Independent Oracle performance consultant and researcher Website: http://www.soocs.de
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 11.2.0.3 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 11.2.0.4 is planned).
>
> Thanks
> Thomas
>
> --
> http://www.freelists.org/webpage/oracle-l

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Dec 06 2017 - 09:31:52 CET

Original text of this message