Re: Auto DOP

From: k.hadd <kouss.hd_at_gmail.com>
Date: Fri, 14 Aug 2020 15:56:57 -0400
Message-ID: <CAF+xsmTMm2gU0JmoYFEN7vkoi2=V_GZDbJxww+3wvS4OT2B5rg_at_mail.gmail.com>

I remember having a bad performing query (12c) everytime it used the parallelism.

Disabling parallelism wasn't enough. We had to change the instance level parallelism for all tables/indexes even if it wasn't RAC DB ( I know weird).
in your case it's the other way around I guess. You can check the value for each involved object in your sql by adapting this query ( you can replace 0 and 1 with your default value) .

SELECT i.table_name,
       i.index_name,
       t.degree    table_degree,
       t.instances tab_instance,
       i.degree    index_degree,
       i.instances index_instance
FROM   dba_indexes i,
       dba_tables t

where t.owner='your_woner'
--- AND t.table in ( list of tables)

AND    i.table_name=t.table_name and (i.degree NOT IN('1','0')
OR     i.instances NOT IN ('1','0')
OR     t.degree NOT IN ('1','0')
OR     t.instances NOT IN ('1','0') ) ;

Thanks
Kosseila

On Fri, Aug 14, 2020 at 12:44 PM <dimensional.dba_at_comcast.net> wrote:

> If you want the parallelism to be match the higher values of the actual
> table settings you would need to change the DEFAULT ones to an actual value
> equally to what you need.
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> On
> Behalf Of Lothar Flatz
> Sent: Friday, August 14, 2020 7:26 AM
> To: dimensional.dba_at_comcast.net; oracle-l_at_freelists.org
> Subject: Re: Auto DOP
>
> Hi ,
>
> there are some with DEFAULT.
>
> Thanks.
>
> Lothar
>
>
> Am 14.08.2020 um 16:02 schrieb dimensional.dba_at_comcast.net:
> > Do all the tables in the execution plan have a degree set or are some
> set to DEFAULT? If any are set to DEFAULT then AUTO DOP can come into play
> and depending on which table drives the joins it sets the DOP for the whole
> plan 12.x-20.x.
> > What is the DOP set on the tables in your execution plan?
> >
> > -----Original Message-----
> > From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> On
> Behalf Of Lothar Flatz
> > Sent: Friday, August 14, 2020 5:05 AM
> > To: oracle-l_at_freelists.org
> > Subject: Auto DOP
> >
> > Hi,
> >
> > In the plan note of one of my customers I see on regular terms:
> > - automatic DOP: Computed Degree of Parallelism is 8 because of
> degree limit.
> > However parallel_degree_policy is set to MANUAL and the query runs
> parallel because the tables have a degree set.
> > Why does the note say "automatic DOP"?
> > Version is 19.3 Hardware is Exadata Cloud at Customer.
> >
> > regards
> >
> > Lothar
> > --
> > http://www.freelists.org/webpage/oracle-l
> >
> >
> >
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

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

Received on Fri Aug 14 2020 - 21:56:57 CEST