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

From: Lothar Flatz <l.flatz_at_bluewin.ch>
Date: Wed, 6 Dec 2017 16:28:26 +0100
Message-ID: <e81b592a-efc3-fc3a-8204-0c312e25f8fc_at_bluewin.ch>


Hi,

we are just researching a case that looks somehow similar. In our case the PXs are waiting on px messaging, e.g. PX Deq: Table Q Normal, PX Deq Credit: send Block.
Some of the slaves have idled out with "done Error".

it seems be that one of the PX was blocking the others and the QC on PX messaging.

Regards

Lothar

Am 06.12.2017 um 09:00 schrieb Thomas Kellerer:
> 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 - 16:28:26 CET

Original text of this message