Re: How often should statistics be generated?

badri_at_cc.gatech.edu (badri) wrote:

>Hi:
 

> I read about the phasing out of rule-based optiization.
>I would like to know how often statistics need to be generated so that
>we can use cost-based optimiation by default.

You can make cost based optimization the default by setting OPTIMIZER_MODE=first_rows in the parameter file. Statistics are required at all then.

> We are using rule-based optimization currently and it seems
>fine. When we do use cost-based optimization, things get
>extremely slow. The obvious explanation is that the statistics that
>we have are not "recent". Well, how often should I generate statistics
>to get the optimizer to perform at its best?

Depending on which version of Oracle you're using, your optimization is probably set to "choose" which uses rule based when no stats are present, and cost based when they are. But there are two cost based optimizations available: first_rows and all_rows. It's probably selecting all_rows. Try setting it to first_rows for a couple of your slow queries and see if it makes a difference. I've yet to find an instance where ALL_ROWS worked faster for me than FIRST_ROWS.

ALTER SESSION SET OPTIMIZER_GOAL=FIRST_ROWS; If this helps, you can make it a permanent setting for all sessions with teh OPTIMIZER_MODE parameter.

--
Chuck Hamilton
chuckh_at_ix.netcom.com

Never share a foxhole with anyone braver than yourself!