DBMS_STATS PARALLEL DEGREE IGNORED IN 10.2 (Doc ID 389851.1)

Last updated on OCTOBER 05, 2010

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.2 and later   [Release: 10.2 and later ]
Information in this document applies to any platform.

Symptoms

You have specified a DEGREE > 1 with which the DBMS_STATS should collect the statistics, but you find that the dbms_stats did not utilize parallel execution.

SQL> select version from sys.v_$instance;
.
VERSION

-----------------

10.2.0.2.0

.
SQL> begin
2 DBMS_STATS.GATHER_TABLE_STATS(
3 OWNNAME => 'owner',
4 TABNAME => 'tablename'
5 ,ESTIMATE_PERCENT => 20
6 ,DEGREE => 8
7 ,cascade=>false
8 ,GRANULARITY => 'PARTITION'
9 );
10 end;
11 /
.
Elapsed: 00:05:30.09
SQL> SELECT n.NAME, s.value
2 FROM sys.v_$statname n, sys.v_$sesstat s,
3 sys.v_$session ss
4 WHERE n.STATISTIC# = s.STATISTIC#
5 AND (n.NAME LIKE '%sort%disk%' or
6 n.name like '%physical reads%' or n.name like '%arallelized%' or
lower(n.name) like '%temp%' or
7 n.name like '%consistent gets%')
8 AND ss.AUDSID = USERENV('SESSIONID')
9 AND s.SID = ss.SID;
.
NAME VALUE
--------------------------- ----------
consistent gets 116903
consistent gets from cache 116903
queries parallelized 0
DFO trees parallelized 0
.
if we trace the dbms_stats using 10046 event, we see a sql

select /*+ no_parallel(t) no_parallel_index(t) dbms_stats
cursor_sharing_exact use_weak_name_resl dynamic_sampling(
.
it does a no_parallel(t)

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms