degree=>16
Rajeshwaran Jeyabal, April 20, 2016 - 5:33 am UTC
Team,
setting degree=>16, why does oracle database go for non-parallel execution of stats gathering?
is there any test case/directions to reproduce this?
April 20, 2016 - 10:40 am UTC
The most common cause, is if we think the object is not "worth" working in parallel (ie, its small), then we might ignore the setting.
See 1408464.1 for some information on that.
There are other more niche examples, eg, you might have a virtual column with a function, or function based index, and the function is not parallel-enabled.
Hope this helps.
Mike, April 20, 2016 - 1:52 pm UTC
Thank you for your response. Per the white paper you've given:
"You should disable the
PARALLEL_ADAPTIVE_MULTI_USER initialization parameter to prevent the parallel jobs from being
down graded to serial."
However, I checked the setting of that parameter in our database, and it is already set to FALSE:
SQL> sho param parallel_adaptive_multi_user
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_adaptive_multi_user boolean FALSE
Concurrent stats gathering seems to be turned off for us:
SQL> select dbms_stats.get_prefs(pname => 'CONCURRENT') from dual;
DBMS_STATS.GET_PREFS(PNAME=>'C
--------------------------------------------------------------------------------
FALSE
So I understand that we would not be getting inter object parallelism, however I'm still confused why we're not getting intra object parallelism. The tables we're trying to analyze have 100M+ rows, which seems like it'd be enough to benefit from parallel execution.
April 27, 2016 - 9:49 am UTC
It will most likely be due to your estimate_percent. Change it to AUTO (which is a good thing to do anyway).
For example, on my system,
begin
dbms_stats.gather_table_stats(ownname=>'',
tabname=>'T',estimate_percent=>0.1,block_sample=>true,
method_opt=>'FOR ALL INDEXED COLUMNS SIZE AUTO',cascade=>false,granularity=>'ALL',degree=>4);
end;
did not use parallel, whereas
begin
dbms_stats.gather_table_stats(ownname=>'',
tabname=>'T',block_sample=>true,
method_opt=>'FOR ALL INDEXED COLUMNS SIZE AUTO',cascade=>false,granularity=>'ALL',degree=>4);
end;
/
did use parallel.
Mike, April 28, 2016 - 12:02 am UTC
Removing the estimate_percent did the trick.
Thanks!
April 28, 2016 - 6:29 am UTC
glad we could help
Mechanism for parallel execution
Rajeshwaran Jeyabal, April 28, 2016 - 4:09 am UTC
begin
dbms_stats.gather_table_stats(ownname=>'',
tabname=>'T',estimate_percent=>0.1,block_sample=>true,
method_opt=>'FOR ALL INDEXED COLUMNS SIZE AUTO',cascade=>false,granularity=>'ALL',degree=>4);
end;
did not use parallel,
Connor - Could you show us the mechanism (or scripts) that you used to validate the parallel execution ?
April 28, 2016 - 6:30 am UTC
1) alter session set sql_trace = true;
2) exec dbms_stats...
Look at the trace files, you can see the parallel (or no_parallel) hints in the generated SQL's
Krishna, August 14, 2018 - 7:11 pm UTC
Hi ,
Currently I am also facing similar issue however our environment is of 12c
Sample size is auto .
From session wait we observed it went into row cache lock , and tried to identify sql it was not executing select statement which we see internally gets executed , sql was of dbms gather table stats .........
Can you help me what ti check on this
August 16, 2018 - 12:14 am UTC
Run a full trace, ie,
alter session set events = '10046 trace name context forever, level 8';
and then your stats gathering.
If the row cache locks are in there, then its in your query coordinator.
If not, then it might be in the slaves - you could activate a similar trace on the slaves using a login trigger.
This will let you drill down into exactly the query.
I've seen examples of this in the past where dbms_stats creates/loads some temporary tables during operations, which need row cache locks to control their usage. But a full trace should reveal that.
Krishna, August 14, 2018 - 7:13 pm UTC
Hi ,
Currently I am also facing similar issue however our environment is of 12c
Sample size is auto .
From session wait we observed it went into row cache lock , and tried to identify sql it was not executing select statement which we see internally gets executed , sql was of dbms gather table stats .........
Can you help me what ti check on this
Bug where table refs not taken into account
Vengata Guruswamy, December 09, 2022 - 8:14 pm UTC
Hi Connor,
I found a bug which might be related :
Bug 16475397 - DBMS_STATS ignores degree value set via set_table_prefs (Doc ID 16475397.8)
description :
When using DBMS_STATS gather stats procedures, they ignores the degree
value set via set_table_prefs; instead, they use the global setting or the
table default degree.
December 12, 2022 - 3:41 am UTC
Nice info - thanks for passing this along