Skip to Main Content
  • Questions
  • Unable to gather table stats in parallel

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Mike.

Asked: April 19, 2016 - 5:28 pm UTC

Last updated: December 12, 2022 - 3:41 am UTC

Version: 11.2.0.3.0

Viewed 10K+ times! This question is

You Asked

Hi,

We're running gather_table_stats with the following arguments:

dbms_stats.gather_table_stats(ownname=>'&owner',
tabname=>'&table',estimate_percent=>0.1,block_sample=>true,
method_opt=>'FOR ALL INDEXED COLUMNS SIZE AUTO',cascade=>false,granularity=>'ALL',degree=>16);

Even though we're specifying a parallel degree of 16, the process ends up running in a single session. In fact, when I check the SQL that's running, Oracle seems to be explicitly preventing parallel execution with the noparallel hint:

select /*+ no_parallel(t) no_parallel_index(t) dbms_stats cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring no_substrb_pad */count(*), ...

I've tried this with several different tables and got the same result. Can you please tell me why this is happening and what we can do to achieve parallel execution?

Thank you.

and Connor said...

Some good info on parallel stats gathering in this whitepaper:

http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-bp-optimizer-stats-04042012-1577139.pdf

plus a few things to check for if you are not getting parallel operations.

Let us know how you go.

btw, From 11.2 onwards, estimate percent of "auto" (the default) is what you want. See http://structureddata.org/2007/09/17/oracle-11g-enhancements-to-dbms_stats/ for details

Rating

  (7 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

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?
Connor McDonald
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.


Connor McDonald
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!
Connor McDonald
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 ?
Connor McDonald
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
Connor McDonald
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.


Connor McDonald
December 12, 2022 - 3:41 am UTC

Nice info - thanks for passing this along

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.