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.

Next

[1] [2] Next