Re: Determing requested parallel slaves

From: Jonathan Lewis <>
Date: Tue, 30 Jun 2009 20:24:56 +0100
Message-ID: <>

"Rob Cowell" <> wrote in message
> I'm on EE
> As part of some capacity management work I'm trying to ensure I have
> enough parallel slaves free at certain times to run my big ETL
> processes without any downgrades. I determine how much I have been
> using from ASH - yes I know it's not perfect, but it'll do me for now.
> I do need to determine how many slaves my SQL is going to ask for.
> Even with a raft of experienced developers I can appreciate people
> will make mistakes working this out from the code and plan. I'd like
> to just log the required slaves during testing. I can see that info in
> v$px_session, and I assume in a px_trace, but that requires either
> another session running and then tying the info back to the sql, or
> some disection of trace files. Both possible but not very elegant -
> and I've got to write the code to do it.
> Is there any way to see this information after the SQL has run in the
> session the SQL has run from?
> Ta

Two possible strategies if you can wrap the SQL that you want to monitor.

v$mystat (joined to v$statname) - check the statistics with names like %DFO% and %downgraded% before and after running the query. This will give you some idea about how succesful the query was with getting the slaves it wanted.

After the query completes, query v$pq_tqstat, which gives you the information about how PX slaves were used. There's a note about this I wrote a few years ago on my website:

You won't want the output I've demonstrated there, but a summary of some of the stats should be relevant. Most queries will result in only 1 DFO - but if you've checked stat for %DFO%, you'll know how many DFO sets to look for in v$pq_tqstat.


Jonathan Lewis

Author: Cost Based Oracle: Fundamentals

The Co-operative Oracle Users' FAQ
Received on Tue Jun 30 2009 - 14:24:56 CDT

Original text of this message