Re: Oracle statistics

devnull_at_ronr.nl (Ronald) wrote in message news:<67ce88e7.0209130407.14d39765_at_posting.google.com>...
> evoradba_at_yahoo.ca (Maria) wrote in message news:<351fd9d6.0209110532.28ad59f5_at_posting.google.com>...
> > Hi Richard
> > Thanks for the info
> > We have to drop and create tables indexes etc in order to clean the
> > database, etc, at least that is what I have been told, I'm new at this
> > place, and they have this for 1 year, where then run a ctas every day,
> > is like a export import of the database, I just don't understand what
> > update STATITISCS does, why to do we have to do this all the time,
> > where does it store this infomartion, the statistics takes a long time
> > to run which is not acceptable (7 hours at times)
> > Do you have any other suggestions
> > Thanks
> > Maria
>
> Maria,
>
> the data that replaces the old data, is that similar ?
> I mean: are big tables always big ? small tables always small ? are
> the indexes always as selective as before ?
> If that is the case, why don't you store the statistics in a table
> before dropping your tables and put them back when you recreated the
> tables ? That saves many hours. By the time yoy notice that the
> execution plans are wrong, you can always refresh the statistics for
> the tables that have large deviations from the statistics.
>
> Ronald.
> -----------------------
> http://ronr.nl/unix-dba

Hi
the data that gets replaced is not the same, its always something new, this procedure was suggested by a Oracle consultant -:) at this place they were using a exp/imp before but was very long, then they come up with the ideia of ctas which is suposed to be faster then exp/imp, but whithn last month I beleive has been giving lots of troubles, this is for a client of ours which wants it like this the ctas dropping all the tables and then recreating new indexes, tables etc .. which I beleive is a waste of time. I also notice that when the ctas is running it scans all the tables which takes time as well ... I have suggested to do a partition tables, do to same of then been very big, but no nes so far ...
Thanks
Maria Received on Sun Sep 15 2002 - 18:34:24 CDT