Re: Query Using 20 Parallel Sessions

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 Received on Fri Feb 08 2008 - 07:17:33 CST