Workloads
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.
Conclusion
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.
Previous
Prev [1] [2]
