learning v$ view->v$sysstat

 

V$SYSSTAT

V$SYSSTAT stores instance-wide statistics on resource usage, cumulative since the instance was started.

Similar to V$SESSTAT, this view stores the following types of statistics:

• A count of the number of times an action occurred (usercommits)
• A running total of volumes of data generated, accessed, or manipulated (redosize)
• If TIMED_STATISTICS is true, then the cumulative time spent performing some actions (CPUused by this session)

Useful Columns in V$SYSSTAT

• STATISTIC#: Identifier for the statistic
• NAME: Statistic name
• VALUE: Resource usage

The value for each statistic stores the resource usage for that statistic since instance startup. The following are sample column values for the statisticexecute count.

Table 24-29 Useful Columns in V$SYSSTAT

Statistic#	Name	Value
215	execute count	19,003,070

Note: The STATISTIC# for a statistic can change between releases. Do not rely onSTATISTIC# to remain constant. Instead, use the statistic NAME column to query theVALUE.

Uses for V$SYSSTAT Data

The data in this view is used for monitoring system performance. Derived statistics, such as the buffer cache hit ratio and soft parse ratio, are computed fromV$SYSSTAT data.

Data in this view is also used for monitoring system resource usage and how the system's resource usage changes over time. As with most performance data, examine the system's resource usage over an interval. To do this, take a snapshot of the data within the view at the beginning of the interval and another at the end. The difference in the values (end value - begin value) for each statistic is the resource used during the interval. This is the methodology used by Oracle tools such as Statspack and BSTAT/ESTAT.

In order to compare one interval's data with another, the data can be normalized (for each transaction, for each execution, for each second, or for each logon). Normalizing the data on both workloads makes identifying the variances between the two workloads easier. This type of comparison is especially useful after patches have been applied, applications have been upgraded, or simply over time to see how increases in user population or data growth affects the resource usage.

You can also use V$SYSSTAT data to examine the resource consumption of contended-for resources that were identified by querying theV$SYSTEM_EVENT view.

Useful Statistics for V$SYSSTAT

This section describes some of the V$SYSSTAT statistics that are most useful during tuning, along with an explanation of the statistic. This list is in alphabetical order.

See Also: Oracle9i Database Reference for a complete list of statistics and their description

Key Database Usage Indicators

• CPU usedby this session: The total amount of CPU used by all sessions, excluding background processes. This unit for this statistic is hundredths of a second. Calls that complete in less than 10ms are rounded up to this unit.
• db blockchanges: The number of changes made to database blocks in the SGA that were part of an insert, update, or delete operation. This statistic is a rough indication of total database work. On a for each transaction level, this statistic indicates the rate at which buffers are being dirtied.
• execute count: The total number of SQL statement executions (including recursive SQL).
• logons current: Sessions currently connected to the instance. When using two snapshots across an interval, an average value (rather than the difference) should be used.
• logons cumulative: The total number of logons since the instance started. To determine the number of logons in a particular period, subtract the end value from the begin value. A useful derived statistic is to divide the number of connections between a begin and end time, and divide this by the number of seconds the interval covered. This gives the logon rate. Optimally, there should be no more than two logons each second. To contrast, a logon rate of 50 a second is considered very high. Applications that continually connect and disconnect from the database (for example, once for each transaction) do not scale well.
• parse count(hard): The number of parse calls that resulted in a miss in the shared pool. A hard parse occurs when a SQL statement is executed and the SQL statement is either not in the shared pool, or it is in the shared pool but it cannot be shared because part of the metadata for the two SQL statements is different. This can happen if a SQL statement is textually identical to a preexisting SQL statement, but the tables referred to in the two statements resolve to physically different tables. A hard parse is a very expensive operation in terms of CPU and resource use (for example, latches), because it requires Oracle to allocate memory within the shared pool, then determine the execution plan before the statement can be executed.
• parse count(total): The total number of parse calls, both hard and soft. A soft parse occurs when a session executes a SQL statement, and the statement is already in the shared pool and can be used. For a statement to be used (that is, shared) all data pertaining to the existing SQL statement (including data such as the optimizer execution plan) must be equally applicable to the current statement being issued. These two statistics are used to calculate the soft-parse ratio.
• parse timecpu: Total CPU time spent parsing in hundredths of a second. This includes both hard and soft parses.
• parse timeelapsed: The total elapsed time for the parse call to complete.
• physical reads: The number of blocks read from the operating system. It includes physical reads into the SGA buffer cache (a buffer cache miss) and direct physical reads into the PGA (for example, during direct sort operations). This statistic is not the number of I/O requests.
• physical writes: The number of database blocks written from the SGA buffer cache to disk by DBWR and from the PGA by processes performing direct writes.
• redo logspace requests: The number of times a server process waited for space in the redo logs, typically because a log switch is needed.
• redo size: The total amount of redo generated (and hence written to the log buffer), in bytes. This statistic (normalized over seconds or over transactions) is a good indicator of update activity.
• session logicalreads: The number of logical read requests that can be satisfied in the buffer cache or by a physical read.
• sorts (memory) andsorts (disk): sorts (memory) is the number of sort operations that fit inside theSORT_AREA_SIZE (and hence did not require an on disk sort). sorts(disk) is the number of sort operations that were larger than SORT_AREA_SIZE and had to use space on disk to complete the sort. These two statistics are used to compute the in-memory sort ratio.
• sorts (rows): The total number of rows sorted. This statistic can be divided by the 'sorts (total)' statistic to determine rows for each sort. It is an indicator of data volumes and application characteristics.
• table fetchby rowid: The number of rows returned using ROWID (due to index access or because a SQL statement of the form "where rowid = &rowid" was issued).
• table scans(rows gotten): The total number of rows processed during full table scans.
• table scans(blocks gotten): The number of blocks scanned during full table scans, excluding those for split rows.
• user commits +user rollbacks: This provides the total number of transactions on the system. This number is used as the divisor when calculating the ratios for each transaction for other statistics. For example, to calculate the number of logical reads for each transaction, use the following formula: session logical reads / (user commits + user rollbacks).

Notes on Physical I/O

A physical read as reported by Oracle might not result in an actual physical disk I/O operation. This is possible because most operating systems have an operating system files system cache where the block might be present. Alternatively, the block might also be present in disk or controller level cache, again avoiding an actual I/O. A physical read as reported by Oracle merely indicates that the required block was not in the buffer cache (or in the case of a direct read operation, was required to be read into private memory).

Instance Efficiency Ratios From V$SYSSTAT Statistics

The following are typical instance efficiency ratios calculated fromV$SYSSTAT data. Each ratio's computed value should all be as close as possible to 1:

Buffer cache hit ratio: This is a good indicator of whether the buffer cache is too small.

1 - ((physical reads - physical reads direct - physical reads direct (lob)) / 
session logical reads)

Soft parse ratio: This shows whether there are many hard parses on the system. The ratio should be compared to the raw statistics to ensure accuracy. For example, a soft parse ratio of 0.2 typically indicates a high hard parse rate. However, if the total number of parses is low, then the ratio should be disregarded.

1 - ( parse count (hard) / parse count (total) )

In-memory sort ratio: This shows the proportion of sorts that are performed in memory. Optimally, in an operational (OLTP) system, most sorts are small and can be performed solely as in-memory sorts.

sorts (memory) / ( sorts (memory) + sorts (disk) )

Parse to execute ratio: In an operational environment, optimally a SQL statement should be parsed once and executed many times.

1 - (parse count/execute count)

Parse CPU to total CPU ratio: This shows how much of the total CPU time used was spent on activities other than parsing. When this ratio is low, the system is performing too many parses.

1 - (parse time cpu / CPU used by this session)

Parse time CPU to parse time elapsed: Often, this can indicate latch contention. The ratio calculates whether the time spent parsing is allocated to CPU cycles (that is, productive work) or whether the time spent parsing was not spent on CPU cycles. Time spent parsing not on CPU cycles usually indicates that the time was spent sleeping due to latch contention.

parse time cpu / parse time elapsed

Load Profile Data from V$SYSSTAT Statistics

To determine the load profile of the system, normalize the following statistics over seconds and over transactions:logons cumulative, parse count(total), parse count (hard), executes, physical reads, physical writes, block changes, and redo size.

The normalized data can be examined to see if the 'rates' are high, or it can be compared to another baseline data set to identify how the system profile is changing over time. For example, block changes for each transaction is calculated by the following:

db block changes / ( user commits + user rollbacks )

Additional computed statistics that measure load include the following:

• Blocks changed for each read:

  This shows the proportion of block changes to block reads. It is an indication of whether the system is predominantly read only or whether the system performs many data changes (inserts/updates/deletes).

  db block changes / session logical reads
  
  

• Rows for each sort:

  sorts (rows) / ( sorts (memory) + sorts (disk) )
  

Join Columns for V$SYSSTAT

Table 24-30 lists the join columns forV$SYSSTAT.

Table 24-30 Join Columns for V$SYSSTAT

Column	View	Joined Column(s)
STATISTIC#	V$STATNAME	STATISTIC#

V$SYSTEM_EVENT