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

From: FMHabash <fmhabash_at_gmail.com>
Date: Tue, 24 Feb 2009 08:46:19 -0500
Message-ID: <49a3fa26.073e400a.4db5.ffffab54_at_mx.google.com>

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

Received on Tue Feb 24 2009 - 07:46:19 CST