Re: Determing requested parallel slaves
From: sheepish <rjc4687_at_hotmail.com>
Date: Thu, 2 Jul 2009 05:46:16 -0700 (PDT)
Message-ID: <ef8a6e1a-7e94-4fc5-aef0-6991ce3c9881_at_j32g2000yqh.googlegroups.com>
On 30 June, 20:24, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> wrote:
> "Rob Cowell" <rjc4..._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 Lewishttp://jonathanlewis.wordpress.com
>
> Author: Cost Based Oracle: Fundamentalshttp://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
>
> The Co-operative Oracle Users' FAQhttp://www.jlcomp.demon.co.uk/faq/ind_faq.html- Hide quoted text -
>
> - Show quoted text -
Date: Thu, 2 Jul 2009 05:46:16 -0700 (PDT)
Message-ID: <ef8a6e1a-7e94-4fc5-aef0-6991ce3c9881_at_j32g2000yqh.googlegroups.com>
On 30 June, 20:24, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> wrote:
> "Rob Cowell" <rjc4..._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 Lewishttp://jonathanlewis.wordpress.com
>
> Author: Cost Based Oracle: Fundamentalshttp://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
>
> The Co-operative Oracle Users' FAQhttp://www.jlcomp.demon.co.uk/faq/ind_faq.html- Hide quoted text -
>
> - Show quoted text -
Thanks Jonathan.
I'm not sure either will tell me exactly how many slaves were requested though? If I look at downgrades the stats are for 1-25%, 25-50%, etc. So if I know I used 10 slaves, but I downgraded 25-50%, I don't know if I asked for 20 slaves or 13 slaves or somewhere in between.
I think approximate is the best I can do for now - at least relatively simply. And at least I can use that to cross check the figures the developers are estimating.
Regards
Rob Received on Thu Jul 02 2009 - 07:46:16 CDT