And if you Google using : Real Time SQL monitor oddity you will find an
example taken from a running system.
Best regards
Mohamed Houri
Le 6 déc. 2017 09:19, "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx> a
écrit :
Sorry, typo in MoS Doc Id. Should be: 1900060.1
________________________________________
From: Jonathan Lewis
Sent: 06 December 2017 08:16:34
To: oracle-l@xxxxxxxxxxxxx; thomas.kellerer@xxxxxxxxxx
Subject: Re: Oracle aborts monitoring a long running statement - how to
find the reason
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@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on
behalf of Thomas Kellerer <thomas.kellerer@xxxxxxxxxx>
Sent: 06 December 2017 08:00:37
To: oracle-l@xxxxxxxxxxxxx
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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
