I'd argue that you should be scared of socalled best practices on this
and other issues.
How can people seriously write about best practices, when in reality
we're talking practices that worked for a few sites that will never,
ever resemble your site anyway?
Better to document more and more worst practices, as Cary Millsap
suggested several years ago - that way you raise the bar instead of just
placing it at a certain position.
By the way: Note the vagueness: "...the same interval" (what interval?),
"...after a sufficient amount of DDL operations have occurred" (how
much?). Can't be otherwise, but that doesn't make it more usable.
It's like the recommendation from Microsoft regarding SQL Server: Always
run automatic stats gathering. Always set parallel to automatic. Always....
Two schools inside Oracle are interesting to study (and we're talking
the real experts, not the document writers):
Benchmark guys: Collect 1% estimate after initial load, then never again.
Real World Performance group: Collect compute stats once, then never again.
But there's a better way, I think:
Analyze if it's neccessary!
Don't do it if you don't have to. Monitor the things that matter.
Mogens
Hallas, John, Tech Dev wrote:
Oracle suggests the following best practices for collecting statistics. http://www.oracle.com/technology/books/pdfs/book_10g_chap9_ce1.pdf
· Collect statistics for normal data dictionary objects using the same interval that you would analyze objects in your schemas. In addition, you need to analyze the dictionary objects after a sufficient amount of DDL operations have occurred.
· Use the procedures gather_database_stats or gather_schema_stats with options set to GATHER AUTO. With this feature, only the objects that need to be re-analyzed are processed every time.
· For fixed objects, the initial collection of statistics is usually sufficient. A subsequent collection is not usually needed, unless workload characteristics have changed dramatically. In the next section, we will examine the changes introduced
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Juan Carlos Reyes Pacheco
Sent: 17 November 2005 19:30
To: oracle-l@xxxxxxxxxxxxx
Subject: Which is the frequency to execute GATHER_DICTIONARY_STATS & GATHER_FIXED_OBJECTS_STATS
Hi, please can some body share some rules about running this
dbms_stats procedures.
In documentation says, you have to run....
but I still don't got, how frequently, or if some background process
does it periodically so the only thing you have to do is for example
ste statistics level to typical or all.
Thank you
--
Oracle Certified Profesional 9i 10g
Orace Certified Professional Developer 6i
8 years of experience in Oracle 7,8i,9i,10g and developer 6i
--
//www.freelists.org/webpage/oracle-l
-- //www.freelists.org/webpage/oracle-l