Database Impact of Collecting Statistics in Oracle

Friday Apr 11th 2003 by James Koopmann

Oracle has given us a new parameter STATISTICS_LEVEL for collecting database statistics. James Koopmann takes a deeper look at what to expect from your database system when this parameter is set and statistics are being gathered.

Oracle has given us a new parameter STATISTICS_LEVEL for collecting database statistics. Lets take a deeper look at what you can expect from your database system when this parameter is set and statistics are being gathered.

It's Required

No, turning on statistics is not required. Although, ever since I have been working with Oracle databases, I have on only one occasion remembered when Oracle has not recommended turning on at least timed_statistics. If you recall it was back in the version 8.1.6 days when there was a problem and everyone scrambled to get on the "stable" 8.1.7 version. Today, the only parameter that I have found, related to statistical collections, that Oracle does not recommend keeping continually turned on is the timed_os_statistics.

The Legend

There is a lot of stigmatism and folklore going around about turning on statistical collections. When doing a quick look through documentation, websites, newsgroups, and asking my local Oracle professionals, I came up with the following responses to statistical collection.

  1. You must set TIMED_STATISTICS to TRUE in the parameter file.
  2. I recommend using timed statistics.
  3. Doing this will have a small negative effect on system performance.
  4. You will incur minimal resource overhead
  5. You cannot afford to be without the information that timed statistics provides.

I don't know about you but these tend to leave me very uneasy and stressed when reading these comments. They range of feelings I have are from 'YOU MUST' to 'YOU SHOULD NOT'. Personally, I tend to get very uneasy when I am told what to do and what not to do, especially when there aren't any benchmarks to support the statements.

Typical Responses

Not only does Oracle themselves not have any benchmarks on what system overhead is incurred when collecting statistics, at least that they want to share, but I have been unable to find any in the Oracle community. Here are the typical responses I have gotten when asked about benchmarks.

  1. Sorry, Oracle Support does not have any specific benchmark information.
  2. Contact ___________, they may have benchmark information.
  3. Can anyone else share their input and experiences.
  4. My Technical Consultant has not seen any specific benchmarks on this.
  5. Take a look at the guide and if necessary we will try to get some development resources

Test Scenarios

In order to reduce the stress I have generated and validate for myself the system impact of statistical collection, I set out to run three simple tests and compare my results. As you recall, the parameter STATISTICS_LEVEL has three settings: BASIC, TYPICAL, and ALL. What I wanted to do was set my STATISTICS_LEVEL to each of these three settings and run the same workload through. As you might also remember, setting STATISTICS_LEVEL to BASIC does no collection so I had to hardwire timed_statistics equal to TRUE in my SPFILE. In figure 1, you can see what statistics and advisories where turned on during each of the three test scenarios. My system simulated an OLTP environment. In addition, I did not want contention to play a big impact so I reduced the transactions to a point where I knew that they would have plenty of resources available to them. This was done so that I could safely say that a reduction of workload and throughput was not caused by contention, but in fact, was caused by the statistic levels I was testing for.

Figure 1.

A Little Background

Let's get a quick level set on what it is I really was concerned with monitoring and comparing, with the three test scenarios. In figure 2, you will see a typical, yet basic database system. At the front of the time slice, users or jobs request work from the database system. This is called the workload. As the workload goes through the system it incurs database resources (v$sysstat) and wait times (v$system_event). The accumulation of resource usage and wait times in the database correlates to database response time. Other factors that contribute to the total throughput of user requests are basically just "other" things that humans do during a normal workday. These range from getting coffee to figuring out what the next transaction looks like.

Figure 2.


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

Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved