Re: Analyse takes a long time

Tweetie Pooh wrote:
>
> 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.

A number of things to look at:

1. COMPUTE is rarely required, look at sampling a smaller set of rows
2. You should be using DBMS_STATS
3. Typically its the column level stats (which invoke a big sort) that hurt the most - maybe run a lower level of stats generation on these (or not at all)
4. Look at parallel-ising the process
5. Partitions are numbered in user_tab_partitions - just chose the highest one to analyze if that's your requirement.

hth
connor

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."