Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Apr 11, 2003

Database Impact of Collecting Statistics in Oracle - Page 2

By James Koopmann


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.

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

Oracle Archives

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM