Re: Query Using 20 Parallel Sessions

"joel garry" <joel-garry_at_home.com> wrote in message news:cb09c543-a416-471f-949b-3d39d60aafd5_at_d4g2000prg.googlegroups.com... On Feb 8, 5: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

Are you using shared server? ASM?

It's my understanding (which may be out of date or wrong) that when using shared server and parallel, the shared server will not be sharable while the parallel transactions are running on it and locks are held. Don't know any details since I don't do that.

Stuff about ASM in 10.2.0.4 patch list.

jg

--
@home.com is bogus.
"Mankind has a perfect record in aviation; we never left one up
there!"


Yes this query was run in shared server mode.  You make it sound like in 
dedicated server that locks wouldn't be held?