Re: detect parallel queries that have been serialized

I probably should have been a bit more specific - if you are only interested in finding out whether a query was supposed to be a Parallel Execution but was executed serialized, then you can't tell this from ASH alone since it doesn't have the "requested DOP" information, only the "actual DOP".

If on the other hand you know the SQLs (and the corresponding SQL_IDs) of those queries that were supposed to use Parallel Execution then you can query ASH for the PX_FLAGS / actual DOP (or the QC_SESSION_ID column for that matter as it should be sufficient) - if these are blank then you know these didn't use PX.

For a more generic query based on ASH you could try to come with some clever expressions that try to find PX related operation names (like HASH JOIN BUFFERED) having PX_FLAGS / QC_SESSION_ID null, but since PX executions downgraded to serial don't make use of all the fancy PX operators (PX SEND / RECEIVE etc.) this can easily miss serialized executions.

You could probably get the generic information from Real-Time SQL Monitoring (see other conversation with Jonathan), but since by default that is only available for a rather short period of time past execution very likely that won't be too helpful either.

Randolf

> 11.2.0.2 linux 64 bit.
> I have a problem with some parallel queries being serialized. I am trying to determine which queries where serialized after the fact. Short of catching the query in the act via v$px_session, is there a way to identify queries that have been serialized over a longer period of time? Perhaps a query against the AWR. I have been wracking my brain on this one.

--
http://www.freelists.org/webpage/oracle-l