Statistics Analysis on Tables that are often empty
Right now I'm dealing with a user application that was originally developed in Ora10g. Recently the database was upgraded to Ora11g, and the schema and data was imported successfully.
However, since the user started using Ora11, some of their applications have been running slower and slower. I'm just wondering if the problem could be due to statistics.
The application has several tables which contains temporary data. Usually these tables are empty, although when a user application runs they are populated, and queried against, and then at the end the data is deleted. (Its this program that's running slower and slower.)
I'm just wondering if the problem could be due to a problem with user statistics.
When I look at the 'last_analyzed' field in user_tables, the date goes back to the date of last import. I know Oracle regularly updates statistics, so what I suspect is happening is that, by luck, Oracle has only been gathering statistics when the tables are empty. (And since the tables are empty, the statistics are of no help in optimizing the DB.)
Am I on the right track?
And if so, is there a way to automatically trigger a statistics gather job when a table gets above a certain size?
System details:
Oracle: 11gR2 (64 bit) Standard version
File System: ASM (GRID infrastructure)