Re: When Parallel Doesn't Happen
- From: Daniel Fink <daniel.fink@xxxxxxxxxxxxxx>
- To: kjped1313@xxxxxxxxx
- Date: Thu, 01 Oct 2009 12:47:16 -0600
Kellyn,
I've been working a similar problem. One 'working hypothesis' is that
when a session remains connected with an open cursor, the parallel
server associated with that session is not released. In this case, some
of these sessions have been idle for hours. Unfortunately, since we
first came up with this hypothesis and a workaround (temporarily raise
parallel_max_servers for a stats gathering job) was implemented, the
problem has not come back. One friend, who is very knowledgeable about
PQ, has seen the same behaviour (he's Scottish...so the spelling is
correct for what he sees) coming from Toad.
PQ sounds like a great idea, but the actual user implementation and
ability to monitor what is actually happening is woefully inadequate. I
do enjoy the responses when I turn off parallelism and the full table
scan with 1 process runs significantly faster than the previous version
with 8 slaves. One issue that I often see is that a small degree of
parallelism is used in dev/test with only that process running. And it
works just fine. But you take it to production with 40 processes all
runing a degree of 8 and things slow to a crawl.
Regards,
Daniel Fink
--
Daniel Fink
OptimalDBA http://www.optimaldba.com
Oracle Blog http://optimaldba.blogspot.com
Lost Data? http://www.ora600.be/
Kellyn Pedersen wrote:
I have just started for a company that applies to the philosophy of
too much of a good thing is really a good thing, so bear with me...
The code has parallel hints everywhere- degrees often set to 8 in
multiple hints in one DML or DDL set, (parallel_threads_per_cpu=2, so
start doing the math...) The parallel_server is set anywhere from 96
to 168 and someone had the idea that as long as they set the threshold
high enough, everything would run.
I have never seen parallel used the way it is here and I've come
across some very interesting challenges. The amount of slaves being
allocated to a process are being downgraded as resources are becoming
limited by the poor choices made in some of these simultaneous requests.
queries parallelized 10443
DDL statements parallelized 106
DFO trees parallelized 10549
Parallel operations not downgraded 10457
Parallel operations downgraded to serial 19
Parallel operations downgraded 75 to 99 pct 0
Parallel operations downgraded 50 to 75 pct 0
Parallel operations downgraded 25 to 50 pct 92
Parallel operations downgraded 1 to 25 pct 0
PX local messages sent 205011362
PX local messages recv'd 230002397
PX remote messages sent 0
PX remote messages recv'd 0
Other related posts:
