Oracle Performance Tuning v$session_wait, v$sysstat

Oracle Performance Tuning

Statistic Descriptions

Various statistic definitions for columns found in the V$SESSION_WAIT and V$SYSSTAT dynamic performance tables.


buffer busy waits	This statistic is stored in V$SESSION_WAIT. In single-instance mode, concurrent I/O on a database block causes this statistic to increment. High buffer busy waits usually indicates a predominately I/O bound application. In an Oracle Parallel Server, it is common to wait for the buffer as the Distributed File System (DFS) lock gets escalated to Exclusive Mode. High buffer busy waits in an Oracle Parallel Server usually indicates competition between nodes for database blocks. P1 in the V$SESSION_WAIT table for this statistic is the database file number. P2 is the database block offset into that file. P3 uniquely identifies where in the RDBMS the event was triggered.
consistent changes	This statistic is stored in V$SYSSTAT. This statistic indicates the number of times a database block has rollback entries applied to perform a consistent read on the block. Workloads that produce a great deal of consistent changes can consume a great deal of resources.
consistent gets	This statistic is stored in V$SYSSTAT. This statistic indicates the number of times a consistent read was requested for a block. See also "consistent changes".
db block changes	This statistic is stored in V$SYSSTAT. Closely related to Consistent changes, this statistics counts the total number of changes that were made to all blocks in the SGA that were part of an update or delete operation. These are changes that are generating redo log entries and hence will be permanent changes to the database if the transaction is committed. This statistic is a rough indication of total database work. This statistic indicates (possibly on a per transaction level) the rate at which buffers are being dirtied.
db block gets	This statistic is stored in V$SYSSTAT. This statistic tracks the number of blocks gotten in current mode.
free buffer waits	This statistic is stored in V$SYSSTAT. This statistic stores the number of times a free buffer was requested in the SGA, but none were available. Free buffers are buffers that are not currently being used by other database users. If the SGA is full of dirty buffers and DBWR can't write them to disk, them then free buffer waits will increase. Update-intensive applications that use small indexes or hash clusters may run the risk of having an entire SGA full of dirty buffers that DBWR cannot keep up with.
parse count	This statistic is stored in V$SYSSTAT. Independant of any cache benefits that may occur in the library cache, this statistic counts the number of times the user called 'parse' function from either OCI or the Oracle Precompilers. This statistic is used mostly as a denominator in conjunction with the V$LIBRARY_CACHE table to determine actual hit ratios in the shared SQL area.
physical reads	This statistic is stored in V$SYSSTAT. This statistic stores the number of I/O requests to the operating system to retrieve a database block from the disk subsystem. This is a buffer cache miss. Logical reads is consistent gets + database block gets. Logical reads and physical reads is used to calculate the buffer cache hit ratio.
physical writes	This statistic is stored in V$SYSSTAT. This statistic stores the number of I/O requests to the operating system to write a database block to the disk subsystem. The bulk of the writes are performed either by DBWR or LGWR.
recursive calls	This statistic is stored in V$SYSSTAT. Oracle maintains tables used for internal processing. When Oracle needs to make a change to these tables, it internally generates a SQL statement. These internal SQL statements generate recursive calls.
redo entries	This statistic is stored in V$SYSSTAT. This statistic increments each time redo entries are copied into the redo log buffer.
redo log space requests	This statistic is stored in V$SYSSTAT. The active log file has filed up and Oracle is waiting for disk space to be allocated for the redo log entries. Space is created by performing a log switch. Small Log files in relation to the size of the SGA or the commit rate of the workload can cause problems. When the log switch occurs, Oracle must ensure that all committed dirty buffers are written to disk before switching to a new log file. If you have a large SGA full of dirty buffers and small redo log files, a log switch must wait for DBWR to write dirty buffers to disk before continuing. Also examine the 'log file space/switch' wait event in V$SESSION_WAIT.
redo sync writes	This statistic is stored in V$SYSSTAT. Normally, redo that is generated and copied into the log buffer need not be flushed out to disk immediately. The log buffer is a circular buffer that LGWR periodically flushes. Redo sync writes increments when changes being applied must be written out to disk due to a commit.
sorts (disk)	This statistic is stored in V$SYSSTAT. If the number of disk writes is non-zero for a given sort operation, then this statistic is incremented. Sorts that require I/O to disk are quite resource intensive. Try increasing the initialization parameter SORT_AREA_SIZE.
sorts (memory)	This statistic is stored in V$SYSSTAT. If the number of disk writes is zero, then the sort was performed completely in memory and this statistic is incremented. This is more an indication of sorting activity in the application workload. You can't do much better than memory sorts, except maybe no sorts at all. Sorting is usually caused by selection criteria specifications within table join SQL operations.
table fetch rowid	This statistic is stored in V$SYSSTAT. When rows are fetched using a rowid (usually recovered from an index), each row returned increments this counter. This statistic is an indication of row fetch operations being performed with the aid of an index. Because doing table scans usually either indicates non-optimal queries or tables without indexes, this statistic should increase as the above issues have been addressed in the application.
table fetch continued row	This statistic is stored in V$SYSSTAT. When a row that spans more than one block is encountered during a fetch, this statistic is incremented. Retrieving rows that span more than one block increases the logical I/O by factor that corresponds to the number of blocks than need to be accessed. Exporting and re-Importing may eliminate this problem. Taking a closer look at the STORAGE parameters PCT_FREE and PCT_USED. This problem cannot be fixed if rows are larger than database blocks (for example, if the LONG datatype is used and the rows are extremely large).
table scan blocks	This statistic is stored in V$SYSSTAT. During scanning operations, each row is retrieved sequentially by Oracle and every each block encountered during the scan increments this statistic. Informs you the number of database blocks you had to get from the buffer cache for the purpose of scanning. Compare this to consistent gets to get a feeling for how much of the consistent read activity can be attributed to scanning.
table scan rows	This statistic is stored in V$SYSSTAT. This statistic is collected during a scan operation, but instead of counting the number of database blocks, it counts the rows being processed.
table scans (long tables)	This statistic is stored in V$SYSSTAT. Long (or conversely short) tables can be defined as tables that don't meet the short table criteria as described in "table scans (short)".
table scans (short tables)	This statistic is stored in V$SYSSTAT. Long (or conversely short) tables can be defined by optimizer hints coming down into the row source access layer of Oracle. The table must be below the initialization parameter SMALL_TABLE_THRESHOLD. In Parallel Server environments that support parallel query servers across instances, it can be determined if the cache partition will fit in the current SGA. If it does, then the table is considered small for scanning purposes. Users with a lot of small tables that qualify under the above conditions benefit greatly by having these tables cached in the SGA. For parallel query operations, the merge phase of the query will improve as the pieces to be merged have been cached.
user calls	This statistic is stored in V$SYSSTAT. Oracle allocates resources (Call State Ojbects) to keep track of relevant user call data structures every time you login, parse, or execute. When determining activity, the ratio of user calls to RPI calls, give you an indication of how much internal work gets generated as a result of the type of requests the user is sending to Oracle.
user commits	This statistic is stored in V$SYSSTAT. When a user commits a transaction, the redo generated that reflects the changes made to database blocks must be written to disk. Commits often represent the closest thing to a user transaction rate.
user rollbacks	This statistic is stored in V$SYSSTAT. This statistic stores the number of times users manually issue the ROLLBACK statement or an error occurs during users' transactions.
write requests	This statistic is stored in V$SYSSTAT. This statistic stores the number of time DBWR takes a batch of dirty buffers and writes them to disk.