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.

  • execute count , Total number of calls that executed SQL statements
  • calls to get snapshot scn: kcmgss , Number of times a snapshot system change number was allocated. This is allocated at the start of every transaction.
  • session logical reads , the sum of db_block_gets and consistent gets.
  • db block changes , total number of changes that are made to all blocks in the SGA. This approximates total database work.
  • user commits , Number of user commits. Commits often represent the closest thing to a user transaction rate.
Figure 3.

Resource Usage

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 4.

Click for larger image

Wait Times

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.

Figure 5.


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.

» See All Articles by Columnist James Koopmann

The Network for Technology Professionals


About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers