Re: Determing requested parallel slaves

"Rob Cowell" <rjc4687_at_hotmail.com> wrote in message news:2bfe6b56-60aa-481c-867c-8ff74ddde946_at_b14g2000yqd.googlegroups.com...
> 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?
>
> 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:

    http://www.jlcomp.demon.co.uk/pqcosts.html

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.html