Re: When Parallel Doesn't Happen
Date: Thu, 01 Oct 2009 13:14:30 -0600
That is one of the frustrations...it appears that there are servers available (max - in_use > degree for job). If we look at servers highwater, it is way below max (172 v. 320). I'm a late comer to the issue and the client needed a quick workaround, so this was a temporary fix and there are other issues to be addressed before I could to a 'soak test' to confirm the working hypothesis. I do see varying 'in use' and 'available' statuses that don't quite look right.
I'm not sure about if resource groups are the answer. Parallelism is not changed during the execution of a statement, so a bunch of low priority jobs could grab all the servers because they are available. A second later, a high priority job needs them, but they are all in use.
As I told my friend, my experience with PQ is 99.999% telling people to turn it off and watching the performance improve.
-- Daniel Fink OptimalDBA http://www.optimaldba.com Oracle Blog http://optimaldba.blogspot.com Lost Data? http://www.ora600.be/ Kellyn Pedersen wrote:Received on Thu Oct 01 2009 - 14:14:30 CDT
> Hey Dan,
> Now I could see raising parallel_max_servers if I was hitting the
> highwater or lowering it if someone hadn't calculated the amount vs.
> number of CPU's, etc. What I'm seeing is I have a setting of 168 for
> parallel_max_servers and yet a total of 48 parallel servers are in
> use- when you look at the queries involved, it should have allocated
> 96, (and at no time was it at the ceiling!)
> parallel_automatic_tuning=false on these systems and the amount of
> parallel servers allocated seem to vary depending on the load on the
> database. I am working on a resource plan to put the higher priority
> jobs into a resource group that would get "first dibs" on the
> available slaves, but I didn't understand how dependent, (although it
> does make perfect sense to me in my head...) parallel allocation is on
> resources. Not just CPU, but I/O, especially when sequential reads
> are involved on indexes and although it shows sequential reads for
> these- partitions.
> I'm considering taking many of these from ASSM to manage the freelists
> manually, giving the heavier hit partitioned tables a little more
> allocated from the start...
> --- On *Thu, 10/1/09, Daniel Fink /<daniel.fink_at_optimaldba.com>/* wrote:
> From: Daniel Fink <daniel.fink_at_optimaldba.com>
> Subject: Re: When Parallel Doesn't Happen
> To: kjped1313_at_yahoo.com
> Cc: oracle-l_at_freelists.org
> Date: Thursday, October 1, 2009, 12:47 PM
> 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.
> 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
>> 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