Printable View
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 ?
Is the new partition analyzed?
Does the query include the partitioning key as a result object, or as a predicate, or at all?
Is the table analyzed as a whole, as well as the individual partitions?
Is the table analyzed before any data is put into it?
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 !
Yes, the query does have the partition key in the where clause.Quote:
Is the table analyzed as a whole, as well as the individual partitions?
you might try the global analyze to see if it changes things for the better.
you might also consider saving the execution plan for the query,once it's running well, for comparison in the future if the report does slow down again.
do you analyze the table/indexes/partition before running the report?
considering it is loading data into the new partition on monday without being analyzed ...
Just stop running the reports, everyone knows they are a load of horse sh*t anyway. Save a tree
:)
MH