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@xxxxxxxxxxx> 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@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> On
Behalf Of Lothar Flatz
Sent: Friday, August 14, 2020 7:26 AM
To: dimensional.dba@xxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: Re: Auto DOP
Hi ,
there are some with DEFAULT.
Thanks.
Lothar
Am 14.08.2020 um 16:02 schrieb dimensional.dba@xxxxxxxxxxx:
Do all the tables in the execution plan have a degree set or are someset 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?Behalf Of Lothar Flatz
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> On
Sent: Friday, August 14, 2020 5:05 AMdegree limit.
To: oracle-l@xxxxxxxxxxxxx
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
However parallel_degree_policy is set to MANUAL and the query runsparallel 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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l