Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: DBMS_STATS vs. manual ANALYZE ... desupported?

Re: DBMS_STATS vs. manual ANALYZE ... desupported?

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Fri, 17 Sep 2004 08:11:18 +1000
Message-Id: <414a0f82$0$23896$afc38c87@news.optusnet.com.au>


Niall Litchfield wrote:

> "Domenic" <domenicg_at_hotmail.com> wrote in message
> news:c7e08a19.0409161253.8235233_at_posting.google.com...

>> Does anyone know *why* Oracle is saying we shouldn't do manual
>> analyzing via our own scripts with or without dynamic PL/SQL loops?

>
> Because it is more sensible to have one standard flexible way of
> collecting stats?

I didn't see the original post, I'm sorry... but whilst I agree with Niall's answer, I'll offer a slightly different one:

Because you can't parallelise the manual analysing (dbms_stats can) Because you can't dynamically and automatically work out whether you should collect histograms for a column suffering from data skew or not (dbms_stats can).

Because, in short, dbms_stats is more functional.

>> Instead, they're saying if we don't use dbms_stats, we're going
>> straight to hell.

>
> I'd like to see the reference.

It *is* strongly recommended to stop using 'analyze table EMP' statements. And for the sort of good reasons I mentioned.

>>  I find using my own scripts allows me to create
>> histograms where needed, to analyze more active tables and partitions
>> more frequently, etc.

Then you are highly unusual, because most people don't know their data that well to be able to say with confidence 'this needs a histogram, this doesn't'. So they end up either over- or under-doing it. Dbms_stats has the smarts to be able to work it out for them, mostly pretty accurately.

Even if I accepted you were that unusual, though: does your manual script consider histograms are needed just because there is a data skew? Or does it additionally take account of the contents of the library cache and only consider a histogram is needed where there is a data skew AND some evidence of the fact that the affected column has been the target of several queries?

Because that's what dbms_stats can do, and if your manual script isn't equally as subtle, then you've been collecting too many histograms, and slowing your database down as a result. What's the point of collecting a histogram for a skewed column that is never used as a WHERE predicate??

[Snip the rest of Niall's excellent advice]

Anyway, Domenic: this is the new world, of automated and self-managing everything. The days of home-brew scripts for all sorts of things are numbered. You, like the rest of us, are just going to have to grit our teeth and get used to it. :-)

But it's not just Oracle playing big brother in this particular instance: there is a real reason for the shift.

Regards
HJR Received on Thu Sep 16 2004 - 17:11:18 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US