0

Our production database tables are range partitioned based on date.

Some of the tables and indexes have hundreds of partitions. And some of partitions which are old ( based upon partition key ) have stale statistics. Some of these partitions are most likely not being accessed by our queries.

Sometimes the server exhibits performance problems but we cannot be sure whether the problem is due to those old partitions or not.

Is it a good idea to PROACTIVELY update the statistics in those old partitions ?

Is there a possibility that the performance degrade if we simply update stats on all such partitions ?

Improve this question

1 Answer 1

Reset to default
2

Normally it does not hurt to make sure that your statistics are up to date. Statistics are up to date if since the last gathering there have been no significant number of updates/deletes/inserts/truncates in the partition.

For large partitioned tables it would be good to use incremental statistics and have the granularity setup by partition. Doing so prevents full table gatherings, which is not very sensible to do if you can prevent this using the table statistics settings.

There is always a chance that your performance degrades if you updated the statistics. In that case it is quite easy to restore the previous version. If that is the case, there is something strange happening. If your table has a regular distribution of data and all rows have about the same length, there will not quickly be a problem.

If your performance degrades after updating the old partitions, it might be wise to restore the previous version and to lock the stats. This is a signal that new statistics are not welcome.

so normally it is ok.

Improve this answer
6
  • Can you clarify "have the granularity setup by partition"? If you mean the granularity parameter for dbms_stats.gather_table_stats, my understanding is only AUTO maintains synopses and rolls partition stats up to the table level. However, this is risky because dbms_stats may decide it needs to rescan the entire table taking many hours, so instead you specify PARTITION, but I am not sure whether this maintains synopses.
    – William Robertson
    Aug 6, 2021 at 22:33
  • The trick here is not to have stats on the global level and to always make sure you put statistics on newly created partitions. This makes that we see the values also on the global level.
    – user953
    Aug 7, 2021 at 6:00
  • Newly created partitions are empty so I don't see any value in gathering stats at that point. Also, global statistics are the whole point of incremental stats and are used for example when the query involves multiple partitions, so I'm not sure what you are suggesting by "The trick here is not to have stats on the global level".
    – William Robertson
    Aug 7, 2021 at 9:07
  • If you delete the global stats and gather stats on all partitions, you see the values appear at table level too. Oracle is a bit funny about partitions without statistics. It will manage the stats but not create them. It does not harm to tell the optimizer that a partition is empty. This works from v11 and up. (Did not check v19)
    – user953
    Aug 7, 2021 at 10:14
  • It does not harm to tell the optimizer that a partition is empty. It does if they are not empty, for example if you gathered stats "on newly created partitions" that were later populated by a batch load. Also, by "The trick here is not to have stats on the global level" it sounds like you're saying to let Oracle manage global stats using the incremental statistics feature by gathering stats using granularity AUTO.
    – William Robertson
    Aug 7, 2021 at 10:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.