Hello,
Everyday a batch process is executed at 8pm. Monitoring the activity of this process I can see that exists a lot of database activity, mainly SQL queries in large partitioned tables.
I found that statistics are not updated before the process execution (only at 1am) and I'm defining incremental gather statistics for the largest tables (to update also de global statistic) to run before the process execution (during the day there are huge number of new records).
The issue that I have is related to the first execution. It takes a lot of time with high number of db file sequential read. P.e. a duration time for the first execution is 40 minutes. After that, I execute the same query with the same explain plan and it takes 10 seconds! I'm trying to understand why i getting this behaviour, because database has a buffer hit ratio of 99% (however I think that could be related with buffer cache).
Do you have any idea about this strange behaviour?
Thanks a lot