Database Impact of Collecting Statistics in Oracle - Page 2
April 11, 2003
For determining the amount of workload that was able to go through my system, I zeroed in on five statistics that I feel give a good indication of workload. I have included in figure 3 the counts I extracted from the system for each of the three test scenarios. I have included a definition for each of the statistics. I hope you can plainly see that the workload I was able to put through the system decreased as I upped the level of statistics collected. The decrease from BASIC to TYPICAL was around 3% and from TYPICAL to ALL was another 1%. The impact of switching from BASIC to ALL equates to a 4% reduction in workload.
For a subset of the resources used and how they are affected, look at figure 4. While there is a bit of erratic behavior of resources used, I think that you can still see that the decrease in the amount of workload put through the system will result in the amount of resources consumed. If you look closely, you will notice that a few of the percentages from switching to ALL from TYPICAL have a negative percentage. This means that there was actually an increase in the resources used even though there was a decrease in the workload put through the system. I can only equate this to the fact that possibly through the mix of transactions and the sliding of the window of execution for certain transactions, there were more resources required to construct the transactions and produce the desired results.
Figure 5 show the total amounts of waits for the three test scenarios. As you can see these correlate nicely back to our workload percentages. They basically tell me that for the reduced amount of workload that was seen for each of the three tests, there was an equally reduced amount of wait time in the system to produce the results for the transactions. If I would have seen a larger amount of waiting for the three tests, I could have concluded that the reduced workload incurred more contention. Likewise if these wait time percentages where lower I could have concluded that the reduced workload actually would have had less contention. This would have been nice and would also have indicated that I could have some tuning opportunities at higher workload rates.
These tests where run on an OLTP system. While your system may vary from this outcome, I hope that I have at least taken away some of the mysticism surrounding what happens to your database system when collecting statistics. You can, I hope, plainly see that increasing the level of statistical collection has an impact on the amount of workload you can place on your system. In my particular scenario, it reduce about 3% when switched to 'TYPICAL' and another 1% when switched to 'ALL'. Just remember that your mileage may vary.