Basically, we were trying to swap statistics within a schema:

First, understand that I have a test set of SQL statements. I have a utility that runs them and captures all the statistics and the plan used for every statement, so I know precisely what is happening to each statement.

- We started with a given set of stats *with no histograms*.
- We saved them off with
---DBMS_STATS.CREATE_STAT_TABLE('<schema>','Table1');
---DBMS_STATS.EXPORT_SCHEMA_STATS ('<schema>','Table1',NULL,NULL);

- We then made lots of changes, including creating histograms
- We then saved of the 'new' stats with
---DBMS_STATS.CREATE_STAT_TABLE('<schema>','Table2');
---DBMS_STATS.EXPORT_SCHEMA_STATS ('<schema>','Table2',NULL,NULL);

- We restored the 'original' stats with:
--- A script that did the following for every table:
------ANALYZE TABLE <table> DELETE STATISTICS;
---We then ran:
------DBMS_STATS.IMPORT_SCHEMA_STATS ('<schema>','Table1',NULL,NULL);

-We ran the test *and everything was perfect*. All the statements performed as they had originally performed with these 'original' statistics

- We then restored the 'new' stats with:
--- A script that did the following for every table:
------ANALYZE TABLE <table> DELETE STATISTICS;
---We then ran:
------DBMS_STATS.IMPORT_SCHEMA_STATS ('<schema>','Table2',NULL,NULL);

- We ran the test and a *lot* of statements were now different. Obviously, I'm not talking about different from the 'old' stats test run - I'm talking about different from the last time we ran with the 'new' stats.

I spent some time trying to figure out what went wrong. My best guess was that the histograms had not restored correctly. This was the biggest difference between the sets of stats. I looked in the database and the histogram data *was* there, but it was impossible to tell if it was correct. I re-ran one of my histograms and the histogram data changed!

So, I re-ran *all* the histograms, re-ran the test, and *all* the statements came back in line.

This tells me that there is some issue with either saving or restoring histograms via DBMS_STATS.

Has anybody else run into this issue or have any ideas about it?

Thanks in advance,

- Chris