CPU Time in Top 5 Timed Events on Statspack/AWR Report

When I first take a look at a statspack or Automatic Workload Repository (AWR) report, and there have been no reported performance  problems, I home in on the Top 5 Timed Events section toward the top of the AWR report. It is from here that you are able to get clues as to which direction you should go in, and therefore what areas of the report you should look at next.

Top 5 Timed Events

The Top 5 Timed Events section within the statspack/AWR  report, gives an overview of the 5 largest wait events across your database  instance. It is common to see among the top 5 timed events, the CPU Time wait event.  Here is the output from an example system:

Top  5 Timed Events
							                Avg      %DB
Event 			     Wait Class     Waits 	Time(s)       Time(ms)  Time
---------------------------  -----------    ---------   ------------  --------- --------
CPU time     					         134,571.20   	        55.99
db file sequential read      User I/O 	     6,798,700 	  39,003.81 	5.74    16.23
log file sync 		     Commit 	     8,854,354 	  16,754.67 	1.89     6.97
log file parallel write      System I/O      5,183,339 	   4,631.02 	0.89     1.93
SQL*Net message from dblink  Network 	     3,098,485 	   4,390.32 	1.42     1.83

A lot of people interpret this event as a wait event, in that the database instance was waiting for 721 seconds out of the statspack report period on CPU. In a way this is true, because it means that the database has spent 721 seconds worth of the available CPU time on the box processing during this period. However, you have to take into consideration the total amount of CPU time available to you.

CPU Top Timed Event? No Problem

For example, you have an 8 CPU server and you want to know how many CPU seconds you have available to you per hour. That works out at (60 seconds * 60 minutes) * 8 cores = 28,800 CPU seconds. So, unless you use more than 28,800 CPU seconds within the hour, you are under your 100% CPU limit. There will always be waits on the CPU within a system, probably more so if you have a larger cache and therefore don’t have to retrieve the data from disk as much. Just remember that it is not necessarily a problem unless you are at 85% or more of available CPU.

An Ideal World

In an ideal world the top timed event would be the “CPU” event and that would be optimal. That’s assuming that your database, code and processes were as efficient as they possibly could be. The reason for this? There always has to be a wait event to process and return data to a user, there is no escaping that fact. Out of all of them, such as disk I/O, contention, locking, etc, CPU is the fastest and most efficient event to be waiting for.

Other Wait Events in Top 5 Timed Events

If you see the control file parallel write wait event in your top 5 timed events in your database, you can read my article control file parallel write.

You might find that the numbers on your AWR report look completely wrong, and the percentage figures don’t add up to 100% when they should do. Your AWR Report Shows High % and Wait Times then read the article I have written about that when running on a Windows server.

Like it, share it...

Category: Database Tuning

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *