DBMS_STATS slow for large Partitioned/Non Partition Table with Bitmap Indexes
Last updated on MARCH 27, 2014
Applies to:Oracle Database - Enterprise Edition - Version 188.8.131.52 to 10.2.0.4 [Release 9.2 to 10.2]
Information in this document applies to any platform.
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:
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;
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