Re: Query Using 20 Parallel Sessions

<fitzjarrell_at_cox.net> wrote in message
news:e95fc771-9146-4b43-bcb0-f003a70d053e_at_n20g2000hsh.googlegroups.com... On Feb 8, 7:05 am, "Dereck L. Dietz" <diet..._at_ameritech.net> wrote:
> Oracle 10.2.0.3.0
> Windows Server 2003
>
> We have a query going against a 100 million plus row partitioned table
> using
> a bitmap index on a column with only 3 distinct values. When the query
> runs
> it only takes about 2 minutes and uses about 20 parallel sessions. This
> query is trying to retrieve specific rows where a value in a column is 0
> so
> the number of rows returned will not be huge.
>
> The person who runs this query has been informed that this is causing a
> "problem" because of it using 20 parallel sessions and has come to me to
> resolve the problem.
>
> I personally believe it is just Oracle working as designed and chosing the
> most efficient access path and method.
>
> Any comment/suggestions as to what my response should be?
>
> Thanks

My first question would be 'What specific problem is this creating?' I can see no issue unless it's taxing the server resources by spawning that many parallel query slaves.

Of course you can always alter the parallel_max_servers parameter to be a number less than 20 and govern the parallel query slave count. But, if it isn't causing a real problem I can't see any reason to meddle with the current settings.

I would respond as you believe, unless this someone can prove it's a real issue on the server.

David Fitzjarrell

The parallel_max_servers parameter in the database is currently set to 160. Received on Fri Feb 08 2008 - 07:50:31 CST