Re: Analyse takes a long time

analyze in 9.2 must be done on every table with this command:

dbms_stats.gather_table_stats(user,'TABLE', estimate_percent=>dbms_stats.auto_sample_size, method_opt=>'for all columns size auto ' , cascade=>true );

also gather system statics

switch monitor on for the tables and use gather_stale for gathering stale statistics

read manuals, understand the system or go for ms-crap

"Tweetie Pooh" <tp601553_at_cia.gov> schrieb im Newsbeitrag news:Xns925B96FD225F2TweetiePooh_at_62.253.162.105...
> Database consists of about 8 tables, currently updated daily. An ANALYSE
> COMPUTE for the schema is run twice weekly but now takes over 24 hours to
> complete. (I tried using OEM on a 9.2 box with DBMS_STATS but this took
> nearly 48 hours)
>
> The main table has 25 million rows (rows about 50,000 per day) with 7
indexes
> and is partitioned. There is an isolated table with nearly 70 million
> rows and 3 indices. How can I speed this up a bit?
>
> The database is 816 (moving to 9.2). Can I flag to just analyse the
> partitions with new data in and if so how? (I know I can specify the
> partitions to analyse. How can I, for example, always analyse the latest
> partition?) Does MONITORING take up lots of disk space?
>
> Maybe there is a better way to get data out. I am thinking of keeping
data
> in a small database for the reporting period and then bulk moving to this
> larger database once a month. The ANALYSE then is done less frequently.
>
> The data is static once added to the database.
Received on Tue Jul 30 2002 - 15:07:26 CDT