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

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Which is the frequency to execute GATHER_DICTIONARY_STATS & GATHER_FIXED_OBJECTS_STATS

Re: Which is the frequency to execute GATHER_DICTIONARY_STATS & GATHER_FIXED_OBJECTS_STATS

From: Mogens Nørrgaard <mln_at_miracleas.dk>
Date: Tue, 22 Nov 2005 11:50:43 +0100
Message-ID: <4382F803.9020507@miracleas.dk>


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!

It's only neccessary if the response time for a SQL statement becomes worse. So monitor the elapsed_time and the hash_plan_value (or plan_hash_value) to see if something goes South. If yes, one possible reason could be stats that are no longer representative for the data (more employees in department 42, much more data due to aquisition of a new company, whatever). THEN it's time to analyze.

Don't do it if you don't have to. Monitor the things that matter.

The reason you can't find any useful rules is that ... you can't find any useful rules.

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_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Juan Carlos Reyes
> Pacheco
> Sent: 17 November 2005 19:30
> To: oracle-l_at_freelists.org
> 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
>
> --
>
> http://www.freelists.org/webpage/oracle-l
>
>
>
>
>
>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Nov 22 2005 - 06:48:08 CST

Original text of this message

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