Has anybody had problems with gathering statistics?
I have some problems with some tables:
EXECUTE DBMS_STATS.GATHER_TABLE_STATS(ownname=> ,tabname=>' '
,cascade=>true);

Oracle's job is blocked.

This is not working well againt some of the tables...for example tibom010.
Sometimes it hangs and sometimes goes through with hours of runtime.
"old time" analyze with compute is working fine.

gather stats is hanging here:
SELECT COUNT (rep)
FROM (SELECT /*+ leading(v1 v2) use_nl_with_index(v2) */
COUNT (v1.val) rep
FROM (SELECT rn, val
FROM (SELECT ROWNUM rn, val
FROM (SELECT /*+ no_merge no_parallel(t)
no_parallel_index(t) dbms_stats cursor_sharing_exact use_weak_name_resl
dynamic_sampling(0) no_monitoring */
SUBSTRB (sys_ds_alias_43,
1,
32
) val
FROM SYS.ora_temp_1_ds_9491 t
WHERE SUBSTRB (sys_ds_alias_43, 1, 32) IS NOT NULL
GROUP BY SUBSTRB (sys_ds_alias_43, 1, 32)
HAVING COUNT (SUBSTRB (sys_ds_alias_43,
1,
32
)
) = 1))
WHERE ORA_HASH (rn) <= 3680349010) v1,
(SELECT /*+ index(t2) */
NLSSORT ("T$MITM",
'nls_sort=''INFOR_GENERIC_M'''
) val
FROM "ERPLN6A"."TTIBOM010250" t2) v2
WHERE v2.val LIKE v1.val || '%'
GROUP BY v1.val
HAVING COUNT (v1.val) <= 2)

*****
So, we are using infor_generic_m customized sorting. Could that be a reason for this!? It's been a lot of problems,bugs with it.

Are you on the latest patch set for oracle?

10.2.0.4 would be the minimum i'd use.

Yes, it is 10.2.0.4, with nls -corrections for infor_generic_m.

never heard of that...

What is it?

Dave

SolutionID
221481

Yes checkout

Patch : Bug 6399168 - DBMS_STATS can be slow for large table


The DBMS_STATS generate a select like the one below where much of the time is spent.

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;


Fixed in a 10.2.0.4 patch to DBMS_STATS.