Enterprise Oracle 12.1.0.2
We have that table that is regularly truncated then reloaded by 10 Java jobs that run in parallel and that load about 7.5M rows total. We have a 30mn window to run that batch. When the batch job starts, statistics are recent and say "O rows" for that table. Then the 10 jobs start running all together. After some time, some of the 10 are still loading our table while others are done and start updating the same table (each jobs updates the data it loaded only, not the data loaded by other jobs). So as more and more jobs finish loading and start updating, one of the indexes of that table is used heavily (index IX2 in the picture). And since the stats think our table and its indexes are empty, the INDEX RANGE SCAN goes berserk (see the timeline and the discrepancy between estimated and actual rows).
So even though the 10 Java jobs do manage to load millions of rows within our windows, the batch job overuns its window because the post-load UPDATEs last hours (we let one run until it finished and it took 6 hours). This is a 2-node RAC database and the top foreground wait events while the UPDATEs run are:
This batch job used to fit in the 30-mn window last March with smaller volumes (3M rows total instead of 7.5M nowadays). I am considering locking the stats of our table (and its indexes) at 8M, but I have no experience with that and would welcome any tip as to statistics management (when tables are regularly emptied and re-loaded) or any other suggestion.