RE: Specifying DOP at object level in an OLTP Production DB.

From: FmHabash <fmhabash_at_gmail.com>
Date: Sun, 15 Mar 2009 11:28:22 -0400
Message-ID: <49bd1e97.070fc00a.26dc.ffffef5b_at_mx.google.com>

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_at_gmail.com> Sent: Tuesday, February 24, 2009 10:05 AM To: fmhabash_at_gmail.com
Cc: nigel.cl.thomas_at_googlemail.com; oracle-l <oracle-l_at_freelists.org> 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_at_gmail.com> 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_at_googlemail.com>
> Sent: Tuesday, February 24, 2009 3:39 AM
> To: fmhabash_at_gmail.com; oracle-l <oracle-l_at_freelists.org>
> 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_at_gmail.com> 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.
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
Andrew W. Kerber

'If at first you dont succeed, dont take up skydiving.'

--
http://www.freelists.org/webpage/oracle-l

Received on Sun Mar 15 2009 - 10:28:22 CDT