Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: RE: DBMS_STATS vs. analyze

Re: RE: DBMS_STATS vs. analyze

From: Stephen Andert <>
Date: Thu, 08 May 2003 12:57:16 -0800
Message-ID: <>

Stale works if you have enabled monitoring for an object. If enabled, it keeps an approximate count of insert/update/delete activity. Analyze Stale only analyzes objects where there has been more than 10% (I think) change. This means on a 100 million row table, if there are 100,000 inserts/updates/deletes, you will not analyze that.

Saves time because you only analyze things that have changed a statistically significant amount.

HTH Stephen

>>> 05/08/03 12:12PM >>>
dont quite follow the 'gather stale' option. does this mean it only gathers stats on blocks that have changed so its alot faster,, correct?

> From: "Jamadagni, Rajendra" <>
> Date: 2003/05/08 Thu PM 02:47:47 EDT
> To: Multiple recipients of list ORACLE-L <>
> Subject: RE: DBMS_STATS vs. analyze
> dbms_stats
> 1. stats can be gathered in parallel
> 2. stats can be exported/imported
> 3. gather stale option
> 4. gather auto option
> analyze
> ???
> dbms_stats Vs Analyze 4-0 ...
> Raj

> ----
> Rajendra dot Jamadagni at nospamespn dot com
> All Views expressed in this email are strictly personal.
> QOTD: Any clod can have facts, having an opinion is an art !
> -----Original Message-----
> Sent: Thursday, May 08, 2003 1:57 PM
> To: Multiple recipients of list ORACLE-L
> this issue was discussed during the tuning round-table last week (
just to
> keep Paula jealous;)) and at some point someone (Anjo, as far as I
> in the room sad that on some situations the only thing that
dbms_stats does
> is - analyze compute ........ . But I guess that besides that you are
on the
> right track , it works better
> rgds
> -----Original Message-----
> Sent: Tuesday, May 06, 2003 10:52 AM
> To: Multiple recipients of list ORACLE-L
> This is an old and probably stupid question, but Im on a new project
> they are using Analyze. Im trying to get them to switch to DBMS_STATS
> let me know if my argument is correct. Need to be able to articulate
> adequately.
> 1. DBMS_STATS is faster
> 2. the statistics gathered are better
> 3. you can use histograms and I believe I may have identified some
> where they would be appropriate.
> anything I missed? Ive pointed them to asktom also. Any other good
> to point them to?
e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank

Please see the official ORACLE-L FAQ:
Author: Stephen Andert

Fat City Network Services    -- 858-538-5051
San Diego, California        -- Mailing list and web hosting services
To REMOVE yourself from this mailing list, send an E-Mail message
to: (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Thu May 08 2003 - 15:57:16 CDT

Original text of this message