Re: Should we stop analyzing?

From: Don Burleson <don_at_burleson.cc>
Date: Wed, 07 Jan 2004 17:14:26 -0800
Message-ID: <F001.005DC06E.20040107171426@fatcity.com>

Jonathan,

Good point about the "change-control" issue.

When we consider that re-analyzing stats can cause huge changes to data access patterns I’m continuously amazed at the number of shops that re-analyze on a schedule and have the “Monday Morning” syndrome.

I have worked for shops where they must “certify” every change, no matter how trivial. Mostly banks and medical systems.

These “certified” shops are stuck. On one hand, they are obligated to follow the best-practices of their vendor, yet obligated not to make any untested changes in production.

Even Oracle is schizophrenic on the issue; my contacts in the real-world performance group are zealously in favor of the “take one deep sample” approach, while the 10g developers are pissed that the CBO has been getting a bum-rap because of crappy statistics.

Personally, I love the automatic histogram generation “skewonly” and the “auto” option in dbms_stats, and use it for all my 9ir2 clients.

However, I remain skeptical about the benefits of “dynamic sampling” and “workload analysis” automation tools for most shops.

In my experience, the vast majority of shops DO NOT benefit from re-analysis, and I’ve got shops where re-analysis NEVER results in CBO changes.

Regards,

Donald K. Burleson
www.dba-oracle.com
www.remote-dba.net

----- Original Message -----

From: "Jonathan Lewis" <jonathan@jlcomp.demon.co.uk>

To: "Multiple recipients of list ORACLE-L" <ORACLE-L@fatcity.com>

Sent: Wednesday, December 31, 2003 1:34 AM

Subject: Re: Should we stop analyzing?

>
> This makes Oracle's position with 10g interesting,
> given that the default behaviour is to collect statistics
> all over the place automatically. If it's built in by
> the supplier, does it count as a change ?
>
> Jared's point is valid - in theory, if you keep statistics
> up to date, then the CBO should produce the
> optimum plan; if you fail to keep statistics up to
> date, the CBO plans can cease to be optimal, or
> may change to become sub-optimal. Moreover,
> in theory, if a plan changes on a change of statistics,
> it will be a better, or at worst equal cost, plan with
> at worst no change in performance. Of course, in
> the real world, we know that there are various
> reasons why things go wrong at the boundary points
> between plans, which is why we like to stick the
> statistics down well within our preferred boundary.
>
> Of course, following your argument about change
> control to its logical conclusion, since a change in the
> data may change execution plans, which may introduce
> untested portions of Oracle code, any data change
> should also be subject to change control.
>
> Despite any whimsical arguments, though, your basic
> premise is the important one. You need to know the
> application to do the job correctly. If you know
> how the data evolves, you will know how to get
> the minimum amount of work done that allows the
> optimizer to do its job well.
>
>
> Regards
>
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> The educated person is not the person
> who can answer the questions, but the
> person who can question the answers -- T. Schick Jr
>
>
> One-day tutorials:
> http://www.jlcomp.demon.co.uk/tutorial.html
>
>
> Three-day seminar:
> see http://www.jlcomp.demon.co.uk/seminar.html
> ____UK___November
>
>
> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
>
> ----- Original Message -----
> To: "Multiple recipients of list ORACLE-L" <ORACLE-L@fatcity.com>
> Sent: Tuesday, December 30, 2003 11:44 PM
>
>
> >
> > At 03:29 PM 12/30/2003, you wrote:
> > >But then again, if re-collecting statistics causes your database
> performance
> > >to suddenly become very bad, it seems at first cut there are only two
> > >conclusions
> > >you can come to.
> > >
> > >1) CBO is broke if fresh statistics result in poor performance
> >
> > That a plan changes due to changes in the statistics doesn't mean that the
> > CBO is broke. That's the whole name of the game. The optimizer uses
> > statistics - together with initialization parameters, heuristics and
> rules
> > - to develop the anticipated best access path. If you change any of these,
> > statistics by analyzing, initialization parameters by changes to the
> > init.ora, or heuristics and rule by upgrading to a new version or applying
> > a patch. I regard any of these changes as serious changes to the database
> > which should go through a test and acceptance cycle. And that includes
> > refreshing statistics. I am constantly amazed how nonchalantly most shops
> > schedule daily, weekly, or whatever analyze jobs even if they batten down
> > the hatches against changes to the application (Don Burleson alluded to
> > that as well). Most of the time the changed statistics do not cause a
> > change in access plans ( which immediately begs the question why do it
> then
> > ), but ever so often the changed statistics cross a threshold to make a
> > different plan appears to be better. It may be better, or it may turn out
> > to be horrible. My point is: shouldn't that be tested first?
> >
> >
> > Wolfgang Breitling
> > Oracle7, 8, 8i, 9i OCP DBA
> > Centrex Consulting Corporation
> > http://www.centrexcc.com
> >
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Jonathan Lewis
> INET: jonathan@jlcomp.demon.co.uk
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Don Burleson
  INET: don_at_burleson.cc

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Wed Jan 07 2004 - 19:14:26 CST