-
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('','Table1');
---DBMS_STATS.EXPORT_SCHEMA_STATS ('','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('','Table2');
---DBMS_STATS.EXPORT_SCHEMA_STATS ('','Table2',NULL,NULL);
- We restored the 'original' stats with:
--- A script that did the following for every table:
------ANALYZE TABLE DELETE STATISTICS;
---We then ran:
------DBMS_STATS.IMPORT_SCHEMA_STATS ('','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 DELETE STATISTICS;
---We then ran:
------DBMS_STATS.IMPORT_SCHEMA_STATS ('','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
-
Okay, so here I am pinging my own post again.
I made the question too long, I know :)
Basically, there is an issue with Histograms working properly when exported and imported via DBMS_STATS.
Does *anyone* have *any* info on this????
Thanks,
- Chris
-
do you *really* need histograms (during my carrer I only heard about some experimental usage of them but not for real)?
-
may I explain my point: as much as I looked into oracle's metalink - quite little information was available on histograms. So make inference yourself.
(yet another example is SDO: we were quite happy to use it in a project untill it came out a memory leak issue. The symptoms were the same: no answers on dbasupport and very little articles on metalink, etc. So we just droped and replaced SDO with 3'd party product which is really good on that task.)
