Re: Re[2]: Oracle 911 Article

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 5 Mar 2004 15:08:54 -0000
Message-ID: <01af01c402c3$ccf05fc0$7002a8c0@Primary>

It's not entirely clear from the article, but it looks as if the 'silver bullet' in this case was a two-step process that

deleted the customer statistics using 'analyze'
generated all the statistics for the system using dbms_stats.

There is no indication that step (a) was used as the immediate fix, with step (b) used as a follow-up after intelligent consideration and risk-analysis.

Given that "The problem started when she wanted to know the average row length for a table." I think we can assume that the immediate fix to the problem was to get rid of the stats from the one table, so the database would go back to using RBO across the board. Changing to CBO is something that most sites would consider to be a significant step, to be taken only after testing.

Your questions about "what would I have done" are perfectly valid, of course. Given the necessary brevity of the case study, I think we have to assume that Don Burleson went through those steps before making any change. The alternative would have been for him to keep repeating the phrase "after I had made sure that the client was telling me the truth..".

As far as the due diligence is concerned - I think you also have to remember that the preamble for the article stressed:

    lives at stake
    millions of dollars to be lost
    panic-struck managers
    hundreds of desparate users
So if the user explains that there is one anomaly in the system, and that they created the anomaly, and that the problem started just after they did, then it's "walking like a duck" time.

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

March 2004 Hotsos Symposium - The Burden of Proof Dynamic Sampling - an investigation
March 2004 Charlotte OUG (www.cltoug.org) CBO Tutorial April 2004 Iceland
June 2004 UK - Optimising Oracle Seminar

Original Message ----- From: "Jonathan Gennick" <jonathan_at_gennick.com> To: "Michael Thomas" <oracle-l_at_freelists.org> Sent: Friday, March 05, 2004 2:24 PM Subject: Re[2]: Oracle 911 Article

: Friday, March 5, 2004, 4:19:21 AM, Michael Thomas (mhthomas_at_yahoo.com)
wrote:
: MT> I don't know if this is agreeing or disagreeing with
: MT> you'all, but I did not like the article.
:
: I read Don's article, and did not like his comments about
: people who take a scientific approach. On the other hand, we
: are sometimes not so kind to him here on this list.
:
: One thing I did agree with from Don's article: sometimes you
: do need to fix something twice. Sometimes you need a
: quick-fix of symptoms from a problem, which you can and
: should follow up later by fixing the problem's root cause.
: Having said this, you still need some diagnostic method,
: some process to follow that leads you to a quick-fix that
: will work.
:
: Reading Don's first case-study, the one about the single
: table with statistics and all the others without, I gather
: that his diagnostic process seems to have gone something
: like this:
:
: 1. He realized the client had just moved a new system into
: production, which led him to ask questions relating to
: common mistakes (e.g. not collecting statistics).
:
: 2. Further discussion uncovered a recent change: the DBA had
: analyzed a single table in order to find out the average row
: length.
:
: 3. He undid the change.
:
: The above does not strike me as a particularly bad approach.
: Here's a question to think about: would you, even in the
: face of knowing that the client had recently analyzed just
: one table, persist in applying a method such as Cary's? Or,
: given the pressure you and the client would be under, would
: you have a go at undoing that change first?
:
: The problem, of course, is that you could all too easily end
: up in one of those try-this-try-that-try-another-thing
: loops, and those are the sorts of loops that methods like
: Cary's avoid.
:
: I'm sitting here thinking about it, trying to answer my own
: question. What would I do? I don't like to apply a fix
: without doing some due-diligence to be sure it will have the
: desired effect. If I had faith in what I was told by the
: client (you won't always have this), and if I could strongly
: correlate the beginning of the poor performance with the
: analyzing of that one table, and if I could be reasonably
: certain that a whole bunch of other changes hadn't been made
: that might muddy the diagnostic waters, I *might* go down
: the unscientific path of quickly unanalyzing the table, to
: see whether I could get a quick hit.
:
: Best regards,
:
: Jonathan Gennick --- Brighten the corner where you are
:

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Fri Mar 05 2004 - 09:38:20 CST