Re: Query Using 20 Parallel Sessions

On Feb 8, 7:50 am, "Dereck L. Dietz" <diet..._at_ameritech.net> wrote:
> <fitzjarr..._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.- Hide quoted text -
>
> - Show quoted text -

Oracle is allocating parallel query slaves in accordance with

1. the demand

or

2) the available server resources

within the confines of the parallel_min_servers and parallel_max_servers settings (unless, of course, the degree is set to 20 for that particular table). My guess (and it is a guess) is that Oracle has 'decided' that 20 slaves is sufficient to perform this task and no real problem exists, except in the mind of he or she who made that comment. As I said in my previous post simply explain that Oracle is behaving properly. Should this person still insist such an execution path is a problem have him or her explain exactly why this is so.

David Fitzjarrell Received on Fri Feb 08 2008 - 08:57:08 CST