Collecting statistics with compute or estimate

[Lily_Liu_2004]

Hi DBAs,

The scenario is like this:
We got a trouble 9I OLTP database. We try to decide the statistics collection strategy.
The data size is not too big. around 20g right now.
I was thinking:weekly such as every Saturday collect the statistics with compute method.
But our consultant thinks use estimate is okay.
Never do more unnecessary statistics collection work.

Does the method -compute or estimate really matter?
How oracle samples the 10% when it does the computing statistics?

Thanks,

Lily

[slimdave]

When estimating statistics Oracle uses the SAMPLE clause on the select statements, which is documented in the SQL reference.

Compare the results you get with compute and estimate to see how accurate the estimation is. You can probably go down to 5% or less and still get good stats.

[Kamkan]

Hi
Use DBMS_STATS package. Its more accurate than ANALYZE. Anyway ANALYZE will not be there in future releases. DBMS_STATS package is good.

[fraze]

Turn monitoring on for your tables then use dbms_stats with the GATHER STALE option to compute stats on any tables that have stale stats (ones with > 10% of DML on the rows - see DBA_TAB_MODIFICATIONS).
Set up a job to run at appropriate times for your system e.g. before online day, before batch window etc.
You now will only run stats on the tables that need them
HTH

[marist89]

http://marist89.blogspot.com/2005/06...sstats_14.html