Recently I was involved in a technical discussion on the necessity to gather fresh statistics after merging data from a database into another. The background of this activity is as under :
1. Data from C Database will be merged into H (I do not want to expose the customer name and the db name, therefore, will use these names).
2. The current size of H database is 800 GB and after the merger, it will be 900 GB.
3. The interesting part of this merger would be the values in some of the critical tables (infact the %age of these tables are 70% of the total production tables).
4. To differentiate the data of C from H, some of the critical columns of C tables would be incremented by 900000000, whereas the last value of H would be (less than ) 200000000. This means, there would be a huge gap in the sequence (900000000-200000000).
5. The database version is 10g Release 2 (10.2.0.3).
6. There was a suggestion from our side to test the application before moving into production and this test requires regathering of statistics after the merger.
Now, in order to save 7-8 hours of Statistics gathering the dba's wanted to import production statistics and test the application. This test would be a flaw and cannot guarantee production performance. Even if after the production merger, the production (old) statistics might have worked, but the performance might have come to standstill after gathering fresh statistics, may be after 10-15 days. Therefore, it was suggested that the test should be on fresh statistics.
With fresh statistics, there were many queries that changed the execution plan and did more logical I/O's as compared to production and this surprised the dba's and the management. They were now curios to know the reason for this.
I have prepared a test case to simulate the issue and will post this in my next blog.
(Since I am busy with my next AIOUG presentation, I will post this in a weeks time).
1. Data from C Database will be merged into H (I do not want to expose the customer name and the db name, therefore, will use these names).
2. The current size of H database is 800 GB and after the merger, it will be 900 GB.
3. The interesting part of this merger would be the values in some of the critical tables (infact the %age of these tables are 70% of the total production tables).
4. To differentiate the data of C from H, some of the critical columns of C tables would be incremented by 900000000, whereas the last value of H would be (less than ) 200000000. This means, there would be a huge gap in the sequence (900000000-200000000).
5. The database version is 10g Release 2 (10.2.0.3).
6. There was a suggestion from our side to test the application before moving into production and this test requires regathering of statistics after the merger.
Now, in order to save 7-8 hours of Statistics gathering the dba's wanted to import production statistics and test the application. This test would be a flaw and cannot guarantee production performance. Even if after the production merger, the production (old) statistics might have worked, but the performance might have come to standstill after gathering fresh statistics, may be after 10-15 days. Therefore, it was suggested that the test should be on fresh statistics.
With fresh statistics, there were many queries that changed the execution plan and did more logical I/O's as compared to production and this surprised the dba's and the management. They were now curios to know the reason for this.
I have prepared a test case to simulate the issue and will post this in my next blog.
(Since I am busy with my next AIOUG presentation, I will post this in a weeks time).
1 comments:
Hi Vivek
very helpful blog... I found you searching google for oracle related blogs, I also run a Oracle Forum and would appreciate some feedback.
Btw I am an oracle apps manufacturing consultant.
Regards,
sadik
Post a Comment