Search code examples
oracledatabase-performancedatabase-administration

What are the cases where someone would be storing Oracle DB object statistics


Can someone please highlight the reason if there are any cases to export the Oracle Stats to preserve for any reasons.


Solution

  • The classical example is to export the stats from a production database and import into a development database. Even if the dev database has much less data, the query optimizer relies on the stats and plans therefore the queries like in prod.

    For example, in prod there are 1.2 million records, but in dev there are onle 12. If you export/import the stats to dev, the optimizer in dev will still assume that the table has 1.2 million records and plan the queries accordingly.