RE: Specifying DOP at object level in an OLTP Production DB.
- From: FmHabash <fmhabash@xxxxxxxxx>
- To: Andrew Kerber <andrew.kerber@xxxxxxxxx>
- Date: Sun, 15 Mar 2009 11:28:22 -0400
Just to follow up. Our db has, historically, posted enq: ps contention as one
of top 5 events. Am not used to seeing this and was not happy with it. We
hunted down the top xplans showing PE, and disabled it by resetting DOP to 1 on
all underlying objects.
This wait event is now all gone and application throughput increased from 15k
to 26k records per hr.
If you rebuild indexes on a regular basis for whatever reason, make sure the
DOP is reset to 1 unless you have some other reason not to.
Thanks
-----Original Message-----
From: Andrew Kerber <andrew.kerber@xxxxxxxxx>
Sent: Tuesday, February 24, 2009 10:05 AM
To: fmhabash@xxxxxxxxx
Cc: nigel.cl.thomas@xxxxxxxxxxxxxx; oracle-l <oracle-l@xxxxxxxxxxxxx>
Subject: Re: Specifying DOP at object level in an OLTP Production DB.
I agree to a certain extent, and disagree to a certain extent. Well tuned
PQ can work wonders when your primary query type is a range scan. PQ can
kill a database if you have multiple large table scans running in an OLTP
environment. If you have full control of the queries running in your
environment, then it is not necessarily a problem. Admittedly, try as you
might few, DBA's have full control of all the queries running on their
database.
On Tue, Feb 24, 2009 at 7:46 AM, FMHabash <fmhabash@xxxxxxxxx> wrote:
> Thanks for the reply for an appearantly unpopular inquiry. I have a
> situation where 2 quries executing simultaneously at 2000/hr always do in
> parallel. When they do, cpu idle goes near 0. We took out the parallel hint,
> but the xplan continues to show PE. This db has 30% of RT in PE: slave
> enqueue waits. The table themselves have default DOP, but eventually we
> narrowed it down to the indexes which have it set at 6. Our plan, is to
> reset DOP to default for all objects. These are the ROT that I can list
> - For an OLTP db, no objects should have a non-default DOP.
> - high execution count quries in an oltp, should not go parallel
> - if PE is needed for some reason, can be done at session level.
>
> Do you agree/disagree with this logic?
> Thanks again
>
>
> -----Original Message-----
> From: Nigel Thomas <nigel.cl.thomas@xxxxxxxxxxxxxx>
> Sent: Tuesday, February 24, 2009 3:39 AM
> To: fmhabash@xxxxxxxxx; oracle-l <oracle-l@xxxxxxxxxxxxx>
> Subject: Re: Specifying DOP at object level in an OLTP Production DB.
>
> One reason tables/indexes may have a DOP > 1 is that they have been
> created (or at some point reorganised) using parallel DML (not a bad
> thing in itself), and then the DOP on the table has never been reset.
> In many OLTP systems this will have little or no effect, because the
> type of queries (small result sets, keyed access etc) make a parallel
> execution plan unlikely to be chosen.
>
> The DOP "bomb" goes off later, either when some serious reporting
> takes place, or when a developer lobs a particularly gruesome query
> into the mix.
>
> Regards Nigel
>
> On 23/02/2009, FMHabash <fmhabash@xxxxxxxxx> wrote:
> > Hi all,
> > Every one in a while, I see some clients who opt to specify a non-default
> > DOP at tables and indexes up to 6.
> > I have not, however, understood why would you want to do that in an OLTP
> > system for quries executing 1000's of times.
> > Is anyone here set DOP as described in an OLTP production DB and why?
> > Thanks.
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
Andrew W. Kerber
'If at first you dont succeed, dont take up skydiving.'
--
//www.freelists.org/webpage/oracle-l
Other related posts: