Re: detect parallel queries that have been serialized

  • From: Randolf Geist <info@xxxxxxxxxxxxxxxxxxxxxxxxx>
  • To: Josh Collier <Josh.Collier@xxxxxxxxxxxx>, oracle-l@xxxxxxxxxxxxx
  • Date: Sat, 22 Jun 2013 22:38:44 +0200

Hi Josh,

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.

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


Other related posts: