Tracking CPU and Other Statistics

We briefly mentioned in Chapter 2 that OWI does not capture any CPU statistics. Fortunately, this, along with myriad other statistics is exposed via the V$SYSSTAT view at the system level and the V$SESSTAT view at the session level. The V$STATNAME view provides a list of statistics that can be used to resolve the statistic name for V$SESSTAT. In short, V$SYSSTAT shows cumulative instance-wide statistics since the instance started, while V$SESSTAT shows cumulative session-wide statistics since the session started. The number and names of these statistics vary from version to version, just as with the wait events. However, the structure and meaning has mostly remained the same through the versions.

The meaning is usually the same whether you are looking at V$SESSTAT for the session-level statistics or whether you are looking at V$SYSSTAT for the system-level statistics. While there are a large number of statistics, we will look into only those that pertain either directly or indirectly to CPU usage and I/O requests . Tables 3-4 and 3-5 list these, with explanations from the Oracle Reference Guide along with our notes.

Table 3-4: CPU Usage

CPU used by this session	Amount of CPU time (in tens of milliseconds) used by a session from the time a user call starts until it ends. If a user call completes within 10 milliseconds, the start and end user -call time are the same for purposes of this statistic, and 0 milliseconds are added.
CPU used when call started	The CPU time used when the call is started. See also CPU used by this session , above.
recursive cpu usage	Total CPU time used by nonuser calls (recursive calls). Subtract this value from CPU used by this session to determine how much CPU time was used by the user calls.
parse time cpu	Total CPU time used for parsing (hard and soft) in tens of milliseconds.
parse time elapsed	Total elapsed time for parsing, in tens of milliseconds. Subtract parse time cpu from this statistic to determine the total waiting time for parse resources.
session logical reads	The sum of db block gets plus consistent gets . Simply put, db block gets is the number of requests for an ‚“unchanged ‚½ lock and consistent gets is the number of requests for a block that was ‚“changed ‚½ and then reconstructed.

Table 3-5: Physical I/O Requests

physical reads	Total number of data blocks read from disk. This number equals the value of physical reads direct plus all reads into buffer cache, where physical reads direct are reads that bypassed the buffer cache.
physical writes	Total number of data blocks written to disk. This number equals the value of physical writes direct plus all writes from buffer cache, where physical writes direct are writes that were written directly to disk.

The name CPU used by this session is probably incorrect when applied to systemwide statistics from V$SYSSTAT. However, the meaning is the same ‚ that is, it is the systemwide CPU usage at that point of time. You will also observe that while the values of CPU used when call started and CPU used by this session will be more or less the same for a particular session in V$SESSTAT, they may differ slightly when looking at V$SYSSTAT, especially on a database that is prone to long running queries. This is because CPU used when call started in V$SYSSTAT is a snapshot of CPU time used before the currently executing calls started , while CPU used by this session records the incremental CPU time of sessions that have already completed.

The CPU usage of a session is mainly accounted under two areas:

• Access to blocks in the buffer cache Requires a complex set of operations including determining the location, computing the address, holding a latch to get to the block, and requesting a physical I/O if the block is not present in the buffer cache. This is termed a logical I/O (LIO) and consumes significant amount of CPU. The session logical reads is thus a very valid statistic that keeps track of this.

• Parsing the query Requires another complex set of operations including checking the syntax and semantics of the query, allocating and loading Data Dictionary and Library cache entries, and so on, which again needs significant latching.

All these actions, of course, precipitate wait time for events to occur, which OWI exposes so well.

The physical reads and physical writes are a measure of physical I/O requested by the session in the case of V$SESSTAT or summed across the database in V$SYSSTAT. Note that this may not directly equate to the actual I/O operations performed, as the OS buffer cache, the volume manager cache and even the disk storage array cache may have satisfied these I/O requests.

To some extent, the value of these statistics as an aid to tuning is in their absolute numbers . However, they fully serve the purpose when you look at the delta values between specific times to fully understand what happened in-between the start and end of those times. This way, they complement the information obtained by using OWI and provide some crosschecking that can be performed.