My Oracle Support Banner

DBMS_STATS PARALLEL DEGREE IGNORED IN 10.2 (Doc ID 389851.1)

Last updated on JULY 05, 2021

Applies to:

Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A 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

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Cause
Solution
References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.