Re: Determing requested parallel slaves

From: Mark D Powell <>
Date: Tue, 30 Jun 2009 06:13:24 -0700 (PDT)
Message-ID: <>

On Jun 30, 8:04 am, Rob Cowell <> wrote:
> 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

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

Original text of this message