Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Which version[s] is this about and how do you export/import? –  ik_zelf Nov 30 '12 at 21:21
    
expdp/impdp on Oracle 11 –  MK01 Nov 30 '12 at 21:58
add comment

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer
    
Thanks, interesting points. The versions are the same and configuration is (close to) identical. We were not able to collect stats on one of the executions because it never finishes and gets to hundreds of Gb of temp space. Re-analyzing the table after import fixes the issue. –  MK01 Dec 1 '12 at 4:13
    
@MK0 I've update my response. You may be importing only part of the statistics. Look at the DBA_STATS package. Check the statistics view after the import. –  BillThor Dec 1 '12 at 4:37
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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