Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: DBMS_STATS vs. manual ANALYZE ... desupported?
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?
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.
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