Oracle not trusting statistics after an import?

Question

We are seing some strange behavior in the way Oracle is handling statistics of an imported schema.
We build a schema on the dev system, analyze if and import into the production with the stats. For the most part everything works fine, but some query seems to select different plans than in dev. After re-analyzing them in production, the plan becomes the same as on dev. The schema is not changed on prod (pretty much read-only). So there must be some kind of difference between how Oracle handles stats built locally vs imported?

Answer 1

A number of factors can change on import.

• Fill factor for data blocks is likely more optimal. Partially filled data blocks from the development system will be filled on import. This can change over time if data is updated and added, but does not appear to be a factor in this case.
• Changes in block placement of rows may alter the number of block required to retrieve by index. This can have positive or negative impacts on the data.
• Cost parameter for data and or index blocks may be different on the two databases resulting in different cost weights for plans.
• Block sizes may be different on the two databases resulting in different costs.
• Newer versions of Oracle will recalculate statistics automatically as data changes. The exported statistics are normally a subset of the full statistics. There is a DBA_STATS package which can be used to export and import the full set of statistics.
• Histogram sizes may be different on the two systems resulting in differences in level of detail. (Its been a while since I tuned this kind of thing.) If the histograms are missing, Oracle will sample the data and estimate. Different sample sizes can cause very different behavior.
• Oracle may decide to use bitmap indexes in some cases. This can require regenerating the indexes on each query. You can add bitmap indexes for the required columns. This option can be disabled at the database level (it may be an undocumented setting).

Try generating cost statistics for the queries you are using on both the Development and Production systems. You may also want to examine the Statistics on both systems.

You may get more consistent results using transportable tablespaces for the tables and indexes. This will eliminate the import/export step.

EDIT: Added bitmap, statistics, and histogram info above.

It is possible to just run an explain plan without running the queries. There are various tools which assist in this. I have used Aqua Data Studio, but SQL Developer should work as well. For parsed queries (running or not) there is a view available from which the plan can be extracted.

There are available views which can be used to view the current statistics for a table. Comparing used blocks on both systems will give you a quick idea how much statistics should have changed. Histogram data is can result in different plans.