Gathering stats very slow
i have a table on a recentlly migrated 10g database (to a new and more powerful server)
Gernerally performance is pretty good however we find that gathering statistics are very very slow using DBMS_STATS.GATHER_TABLE_STATS and estimating only 1% of a 10million row partitioned table takes up to 4 hours - yet updating the table with 25% new data takes about 15 mins.
On the old server it would gather the stats in about 25 mins, so I guess there is some parameter missed
I am not a dba so dont really know what to look at - but my suspicion is that its somethign to do with IO.
Can this be?
Has anyone around here seen anyting like this before?
trace it and see what happens
done that - disk waits are very high - 150ms.
however it only seems that stats are the slow thing on here, so wondered if they can be optimized
index and data are on different mountpoints
Have you gathered system stats after migration to Ora10g?
Ora10g requires work load stats - check dbms_stats.gather_system_stats
Pablo (Paul) Berzukov
Author of Understanding Database Administration
available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
dont think these have been taken, will look into it.
Click Here to Expand Forum to Full Width