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

Ah, thanks.

That explains it.

Jonathan Lewis schrieb am 06.12.2017 um 09:16:
>
> It's a known feature - at least for parallel queries - that a process that is inactive for more than 30 minutes is assumed crashed.
> MoS Doc ID: 1900001 applies - no workaround suggested, and no obvious parameters to try tweaking.
>
>
> Regards
> Jonathan Lewis
>
>
> ________________________________________
> From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Thomas Kellerer <thomas.kellerer_at_mgm-tp.com>
> Sent: 06 December 2017 08:00:37
> To: oracle-l_at_freelists.org
> Subject: Oracle aborts monitoring a long running statement - how to find the reason
>
> 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