Re: 11G Statistics Collection

From: Mladen Gogala <>
Date: Mon, 16 Apr 2012 22:47:22 +0000 (UTC)
Message-ID: <jmi7hq$vcm$>

On Mon, 16 Apr 2012 22:15:49 +0200, Robert Klemme wrote:

> I don't understand: if data distribution at time of "nailing" favors one
> index while at other times access via this index is slower than FTS how
> then would you call that a "working approach" - other than the query
> will eventually return (modulo snapshot too old or other issues)?
> Kind regards
> robert

Robert, the greatest problem in the system room is called "instability". If one nice morning end users discover that what used to perform well the last night is now horribly slow, the DBA is in trouble and his credibility is undermined.
On the other hand, the change because of the volume is never drastic, the change because of data volume is always gradual: there are few blocks more, the query will be a few milliseconds slower. That I can live with. When things slow down over night because of the new statistics, the change is very sudden, as in "sudden death". That I cannot live with.

When the performance begins to cause the concern, I will see what I can do, first on the development system, then QA system, then UAT system, using Swingbench, and eventually, the change will get into production. Controlling the change to the production database is one of the two major duties of any DBA. The second is to ensure data availability and never to lose even one bit of data. The change is not good, as PHB would like you believe. The change is only good if its result is predictable. That is not the case with gathering the database statistics. I've seen many situations where insufficiently thought of statistics collection has caused performance deterioration. Collecting statistics is a big change that has potentially an enormous impact on performance. I don't want to collect statistics automatically any more that I want to give my development department access to production.
I don't understand your reasoning. Would you allow a duhveloper with a cunning plan of the Baldrick type to implement it in production without the proper QA? If the answer to that question is a resounding "no", why would you allow a potentially disastrous thing like stats collection to happen without QA, automatically?
This is something that every DBA should have in his cubicle, to resist an impulse to change things with the weather or new technology:

Received on Mon Apr 16 2012 - 17:47:22 CDT

Original text of this message