Re: Determing requested parallel slaves
Date: Tue, 30 Jun 2009 20:24:56 +0100
"Rob Cowell" <rjc4687_at_hotmail.com> wrote in message
> I'm on 10.2.0.2 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?
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.
-- Regards Jonathan Lewis http://jonathanlewis.wordpress.com Author: Cost Based Oracle: Fundamentals http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.htmlReceived on Tue Jun 30 2009 - 14:24:56 CDT