I have a table that is partitioned by date range - weekly partitions. The new partition starts every Monday. Data is loaded into the table during the day. Then a report is run nightly; that takes about an hour.
But recently, the report has started to take 4 hrs each Monday ! It runs in just 1 hour on the other weekdays. IT IS NOT A SCHEDULING ISSUE. The only thing I could think of was a fresh partition was being queried - has anyone else run into something like it ? Does it makes sense ?
It's been our practice to analyze the table/index partition as well as the global table/indicies as soon as the partitions are added. So the stats are always zero for those partitions. In the past we have hardly ever analyzed the partitions or global tables/indicies after the partitions are loaded with data - I know we probably should, but since we don't get any complaints of performance, we stick by the "if it ain't broke ..." rule !
Is the table analyzed as a whole, as well as the individual partitions?
Yes, the query does have the partition key in the where clause.