Re: Stale statistics
Date: Tue, 30 Jun 2009 18:26:00 +0000 (UTC)
On Tue, 30 Jun 2009 06:04:16 -0700, Mark D Powell wrote:
> By default on 10g histograms are collected. I do not think a 1% sample
> will result in very good histograms.
That depends. If the table is huge, 1% sample may be sufficient. One can even use larger sample but use block sampling, which will speed up the collection process. Unfortunately, in 10g one cannot set BLOCK_SAMPLE as a parameter. One cannot set is as table preference in 11g, either.
A trick that can be used to combat stale statistics is to set OPTIMIZER_DYNAMIC_SAMPLING to 6 or higher. If you take a look at the 10053 traces, level 1, you will see that the decisions reached after setting the dynamic sampling to high value will be similar to the decisions reached after analyzing the table. Of course, dynamic sampling also introduces an element of instability to the system, but I do find it useful.Received on Tue Jun 30 2009 - 13:26:00 CDT