Regathering Stats on Tables that have not changed

[djbjr]

OK So I need a point in the right direction.

Every month I load several snapshot tables based on end of month data. They are all stored in partitioned tables.

At the beginning of each month I have several users running thousands of reports from Business Objects using these tables (amoung others)

Last month, these queries started to hang, probably because of larger amounts of data. Because these queries are from Business Objects I cant add hints or re-write the queries.

We tried several things and they didnt work. We finally just dropped all the stats on all the partitions including indexes and regathered them. This worked.

Then the next day, the queries started hanging again. We did load data, but none of these snapshot tables had been touched. Any clue of what may be happening? I am at a total loss.

The biggest thing I notice in the explain plan is that partition elimination is not happening but when we regather the stats on these partitioned tables, it begins to do the partition elimination again and it works fine.

Any suggestion would be great. I dont want to have to regather stats every day because that process takes 2 hours.

Thanks and sorry for the wordy email.

dj

 

[taupirho]

The following links may be of some help:-

http://www.oracle-base.com/articles/8i/RefreshingStaleStatistics8i.php

also

http://www.utexas.edu/its/unix/reference/oracledocs/v92/B10501_01/server.920/a96533/stats.htm