RE: When Parallel Doesn't Happen
- From: "Herring Dave - dherri" <Dave.Herring@xxxxxxxxxx>
- To: <kjped1313@xxxxxxxxx>, "oracle-l" <oracle-l@xxxxxxxxxxxxx>, "Daniel Fink" <daniel.fink@xxxxxxxxxxxxxx>
- Date: Mon, 5 Oct 2009 14:51:10 -0500
Kellyn,
I've had excellent performance with Oracle parallelism, where it makes sense.
Smaller tables with full scans can indeed be slower than having x number of PX
slaves, but it all depends on the size of the object in question and the amount
of concurrent access on that object.
If you're seeing specific queries having either their parallelism dropped x
amount or completely ignored, check the xplan and compare against what's really
being used. I've had situations where a query should have used 8 or 16 PX
slaves, but at runtime it ran serially (made obvious by db file sequential read
waits on the only session for the query). In my case the query was calling a
function which was missing the "parallel_enable" clause, which invalidated
parallelism at runtime.
David C. Herring | DBA, Acxiom Database Services
630-944-4762 office | 630-430-5988 cell | 630-944-4989 fax
1501 Opus Pl | Downers Grove, IL, 60515 | U.S.A. | www.acxiom.com
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Kellyn Pedersen
Sent: Thursday, October 01, 2009 2:22 PM
To: oracle-l; Daniel Fink
Subject: Re: When Parallel Doesn't Happen
Hi Dan,
That makes sense now- as I was not seeing any of these get close to the
highwater mark, but knew that the database seemed unable or unwilling to
allocate any more slaves.
I'm not far from pulling out all their d*&$ hints and making them start over
and validating the hints are actually functional in a production environment.
Then I can get them to start looking at their partitioning logic and those
silly indexes they got going!
I'll let you know how it goes and try it out in one of the "older" marts...
Thanks!
Kellyn
--- On Thu, 10/1/09, Daniel Fink <daniel.fink@xxxxxxxxxxxxxx> wrote:
From: Daniel Fink <daniel.fink@xxxxxxxxxxxxxx>
Subject: Re: When Parallel Doesn't Happen
To: "Kellyn Pedersen" <kjped1313@xxxxxxxxx>, "oracle-l" <oracle-l@xxxxxxxxxxxxx>
Date: Thursday, October 1, 2009, 1:14 PM
Kellyn,
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.
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:
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...
Thoughts?
Kellyn
--- On Thu, 10/1/09, Daniel Fink <daniel.fink@xxxxxxxxxxxxxx> wrote:
From: Daniel Fink <daniel.fink@xxxxxxxxxxxxxx>
Subject: Re: When Parallel Doesn't Happen
To: kjped1313@xxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Date: Thursday, October 1, 2009, 12:47 PM
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
***************************************************************************
The information contained in this communication is confidential, is
intended only for the use of the recipient named above, and may be legally
privileged.
If the reader of this message is not the intended recipient, you are
hereby notified that any dissemination, distribution or copying of this
communication is strictly prohibited.
If you have received this communication in error, please resend this
communication to the sender and delete the original message or any copy
of it from your computer system.
Thank You.
****************************************************************************
--
//www.freelists.org/webpage/oracle-l
Other related posts:
