DBMS_STATS slow for large Partitioned/Non Partition Table with Bitmap Indexes (Doc ID 1330418.1)

Last updated on MARCH 27, 2014

Applies to:

Oracle Database - Enterprise Edition - Version 9.2.0.8 to 10.2.0.4 [Release 9.2 to 10.2]
Information in this document applies to any platform.

Symptoms

DBMS_STATS very slow for large Partitioned/Non Partition table with Bitmap indexes;irrespective of Degree of parallelism.

Much time spent could be seen in the statement like below:

select /*+ gather_plan_statistics * / count(rep)
from (select /*+ leading(v1) use_nl(v2) */ count(v1.val) rep
from (select /*+ no_merge noparallel(t) noparallel_index(t)
cursor_sharing_exact dynamic_sampling(0) no_monitoring */
"Column from the table" val
from "Table where stats are gathered" t
where "Column from the table" is not null
group by "Column from the table"
having count("Column from the table") = 1 ) v1,
(select /*+ index(t2) */ "Column from the table" val
from "Table where stats are gathered" t2 ) v2
where v2.val = v1.val
group by v1.val
having count(v1.val) <= 2;

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