Re: dbms_stats.gather_schema_stats

In fact, 10gR2 will automatcally gather stats on objects it thinks (meaning the DB keeps track of deletes, inserts, updates, new objects, ...) are stale. This is the default behavor.

And like above, the size of the tables as well as the size of the indexes and the parameters given in the statement all effect how long the stats will take to gather. I have some tables that would take in the order of days to do a full gather of table and index stats. (we have the default gather stale stats off ;-)

On Oct 10, 2:39 pm, "Matthias Hoys" <a..._at_spam.com> wrote:
> "klabu" <nosuchuser_at_gmail_dot_com> wrote in messagenews:12ini8ekve2oe88_at_corp.supernews.com...
>
>
>
> > I have 15 tables, about 7 mil rows in total
>
> > SQL> exec dbms_stats.gather_schema_stats(user);
>
> > Executed in 469.305 seconds
>
> > Is it "normal" that dbms_stats.gather_schema_stats takes that long ?Depends on your hardware, number of columns, indexes, ...
>
> So yes, it can take this long. But you don't need to execute it each day,
> only when there are significant data changes.
Received on Tue Oct 10 2006 - 15:28:00 CDT