Re: gather stats slows performance?

"Christian Antognini" <christian.antognini_at_trivadis.com> wrote in message news:<4115ef4c$1_at_post.usenet.com>...

> With defualt options no index statistics are generated! Therefore set
> CASCADE=>TRUE.
Ah-ha. I did not make that assumption -- my reading of the explanation of dbms_stats.gather_schema_stats() was that it gathered stats on all the objects in the schema, which I took to include indexes. I will try your suggestion.

> You should gather system statistics as well.
>
> If you migrate from RBO, a first row optimization is usually better (per
> default OPTIMIZER_MODE = CHOOSE, i.e. ALL_ROWS). Since you are working with
> 9.2, I suggest you to use the new first row optimizer, i.e. FIRST_ROWS_n.
>
> > After stats were gathered, queries now seem to be using the CBO. A
> > few particularly slow queries are now much faster, but overall,
> > application responsiveness has slowed. To confirm this, I deleted
> > stats and the overall performance picked back up while the few
> > troublesome queries are slow again.
>
>
> You should compare execution plans, just saying that it is slower doesn't
> give the necessary information....

Yes this was just a "first impression" observation, nothing quantitative. It was just unexpected.

> My guess is that they aren't the same, i.e. with your current statistics and
> configuration the CBO isn't performing well => it is not generating the
> optimal execution plan.
>
> > My guess as to why this is, is that the CBO is slower than the RBO,
> > even though it may come up with a better plan. And since we are not
> > using bind variables, virtually every query has to be parsed from
> > scratch. Is this a reasonable conjecture?
>
> If you enable SQL trace you are able to confirm/deny your conjecture.
>
> > Until we can get our applictation refactored to use more stored procs
> > and bind variables, are we likely to be better off without using
> > statistics and the CBO?
>
> To workaround the bind variables problem I suggest you to set
> CURSOR_SHARING=FORCE. This setting must be carefully tested!

I recall this from Tom Kyte's book -- however I would rather fix things properly than enable a potentially troublesome option.

Thanks for the suggestions -- I don't have much experience with Oracle tuning so this is good info for me.

Allan Received on Sun Aug 08 2004 - 20:41:59 CDT