Re: Determing requested parallel slaves
From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Tue, 30 Jun 2009 06:13:24 -0700 (PDT)
Message-ID: <9036366f-a4a9-4bea-94df-87b4ab529884_at_k26g2000vbp.googlegroups.com>
On Jun 30, 8:04 am, Rob Cowell <rjc4..._at_hotmail.com> wrote:
> 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
Date: Tue, 30 Jun 2009 06:13:24 -0700 (PDT)
Message-ID: <9036366f-a4a9-4bea-94df-87b4ab529884_at_k26g2000vbp.googlegroups.com>
On Jun 30, 8:04 am, Rob Cowell <rjc4..._at_hotmail.com> wrote:
> 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
It sounds like all you really want is to count the number of associated pqo sessions in use for a session at a given point in time. What about just joining v$px_session back to v$session to v$sql and sampleing this every minute or two throughout the day.
Personally I think using hints to limit the PQO sessions applied on a statement level is a fairly good idea. Overuse of PQO can kill performance.
HTH -- Mark D Powell -- Received on Tue Jun 30 2009 - 08:13:24 CDT