Release 8.1.5
A67790-01
 Library |
 Product |
 Contents |
 Index |
| Oracle8i Reference Release 8.1.5 A67790-01 |
|
This appendix briefly describes some of the statistics stored in the V$SESSTAT and V$SYSSTAT dynamic performance tables. These statistics are useful in identifying and correcting performance problems.
Topics covered in this appendix include:
The V$SESSTAT view contains statistics on a per-session basis and is only valid for the session currently connected. When a session disconnects all statistics for the session are updated in V$SYSSTAT. The values for the statistics are cleared until the next session uses them.
The V$STATNAME view contains all of the statistics for an Oracle release.
Many of these statistics are tied to the internal implementation of Oracle and therefore are subject to change or deletion without notice. This may even happen during a bug fix patch release. Application developers should be aware of this and write their code to tolerate missing or extra statistics.
For more information on these views, see "V$SESSTAT", "V$STATNAME", and "V$SYSSTAT".
This section describes the statistics stored in the V$SESSTAT and V$SYSSTAT views.
This is the amount of CPU time (in 10s of milliseconds) used by a session between when a user call started and ended. Some user calls can complete within 10 milliseconds and as a result, the start and end user-call time can be the same. In this case, 0 milliseconds are added to the statistic.
A similar problem can exist in the reporting by the operating system, especially on systems that suffer from many context switches.
The CPU time used when the call is started.
A buffer in the buffer cache was cloned. The most common reason for cloning is that the buffer is held in a incompatible mode.
The number of times cached Commit SCN is referenced.
The number of times Commit SCN is cached.
A CURRENT buffer (shared or exclusive) is made CR before it can be used.
The total number of buffers looked at when scanning each LRU set for dirty buffers to clean. This count includes both dirty and clean buffers. Divide by DBWR lru scans to find the average number of buffers scanned.
The number of buffers that were written for checkpoints.
Number of times the DBWR was asked to scan the cache and write all blocks marked for a checkpoint.
DFS only: count of the number of blocks forced written.
The number of buffers that DBWR found to be clean when it was requested to make free buffers. Divide by DBWR make free requests to find the average number of reusable buffers at the end of each LRU.
The number of times that DBWR does a scan of the LRU queue looking for buffers to write. This includes times when the scan is to fill a batch being written for another purpose such as a checkpoint. This statistic is always greater than or equal to DBWR make free requests.
Number of messages received requesting DBWR to make some more free buffers for the LRU.
The number of times that dbwr tried to save a buffer for writing and found that it was already in the write batch. This statistic is a measure of the amount of "useless" work that DBWR had to do in trying to fill the batch. This can occur because many sources contribute to a write batch. If the same buffer from different sources is considered for adding to the write batch, then all but the first attempt will be "useless" since the buffer is already marked as being written.
The number of times DBWR skipped writing "hot" buffers.
The current scan depth (number of buffers examined by DBWR) is added to this statistic every time DBWR scans the LRU for dirty buffers. Divide by DBWR lru scans to find the average scan depth.
The number of transaction table blocks written by DBWR. It is an indication of how many "hot" buffers were written, leading to write complete waits.
The number of DDL statements that were parallelized.
The number of DML statements that were parallelized.
The number of local messages received for Parallel Executions.
The number of local messages send for Parallel Executions.
The number of remote messages received for Parallel Executions.
The number of remote messages sent for Parallel Executions.
Total number of Net8 messages sent to and received from the client.
Total number of Net8 messages sent over and received from a database link.
The number of times the current version moved to a different buffer, leaving CR.
The total number of times that the process cleanup was performed unnecessarily because the session/process did not get the next batched SCN. The next batched SCN went to another session instead.
The number of checkpoints completed by the background. This statistic is incremented when the background successfully advances the thread checkpoint.
The number of checkpoints started by the background. It can be larger than the number completed if a new checkpoint overrides an incomplete checkpoint. This only includes checkpoints of the thread, not individual file checkpoints for operations such as offline or begin backup. This statistic does not include the checkpoints performed in the foreground, such as ALTER SYSTEM CHECKPOINT LOCAL.
The total number of bytes received from the client over Net8.
The total number of bytes received from a database link over Net8.
The total number of bytes sent to the client from the foreground process(es).
The total number of bytes sent over a database link.
The number of times a snap System Change Number (SCN) was allocated. The SCN is allocated at the start of a transaction.
The elapsed time for redo write for changes made to CURRENT blocks in 10s of milliseconds.
The number of times CR gets require both block rollbacks, and block cleanouts.
The number of times CR gets require only block cleanouts, no rollbacks.
The number of blocks obtained in a cluster scan.
The number of cluster scans that were started.
The number of times a cleanout at commit was attempted and could not find the correct block due to forced write, replacement, or switch CURRENT.
The number of times a cleanout at commit was attempted but the buffer was currently being written.
The number of times the cleanout callback function returns FALSE.
The total number of times a commit cleanout was performed but failed because the block could not be pinned.
The number of times cleanout at commit was attempted during hot backup. The image of the block needs to be logged before the buffer can be made dirty.
The number of times that a cleanout at commit time was performed but the writes to the database had been temporarily disabled.
The total number of times the cleanout block at commit time function was performed.
The number of times the cleanout block at commit time function successfully completed.
The number of times a database block has applied rollback entries to perform a consistent read on the block.
Work loads that produce a great deal of consistent changes can consume a great deal of resources.
The number of times a consistent read was requested for a block. See also consistent changes above.
The number of undo records applied to CR rollback data blocks.
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.
This statistic tracks the number of blocks obtained in CURRENT mode.
The number of times cleanout records are deferred, piggyback with changes, always current get.
The number of dirty buffers found by the foreground while the foreground is looking for a buffer to reuse.
The total number of enqueue converts.
The total number of enqueue deadlocks between different sessions.
The total number of enqueue releases.
The total number of enqueue gets.
The total number of enqueue operations (get and convert) that timed out before they could complete.
The total number of waits that happened during an enqueue convert or get because the enqueue could not be granted right away.
The number of times that a process detected a potential deadlock when exchanging two buffers and raised an internal, restartable error. Index scans are currently the only operations which perform exchanges.
The total number of calls (user and recursive) that execute SQL statements.
The number of buffers skipped over from the end of an LRU queue in order to find a reusable buffer. The difference between this statistic and dirty buffers inspected is the number of buffers that could not be used because they were busy, needed to be written after rapid aging out, or they have a user, a waiter, or are being read/written. For more information, see "dirty buffers inspected".
The count of the number of times a reusable buffer or a free buffer was requested to create or load a block.
The number of times a ping request was deferred until later.
The number of pings for free lock elements (when all releasable locks are in use)
The total elapsed time of all synchronous (non-asynchronous) global lock converts in 10s of milliseconds.
The total number of asynchronous global lock converts.
The total number of synchronous global lock converts.
The total elapsed time of all synchronous (non-asynchronous) global lock gets in 10s of milliseconds.
The total number of asynchronous global lock gets.
The total number of synchronous global lock gets.
The total number of synchronous global lock releases.
When a hot buffer reaches the tail of its replacement list, it is moved back to the head of the list. This is what keeps hot buffers from being reused. This statistic indicates how often that happens.
The number of times cleanout records are applied immediately during CR gets.
The number of times cleanout records are applied immediately during current gets.
The number of times the kernel got the CURRENT SCN when there was a need to casually confirm the SCN.
The number of times the kernel casually confirmed the SCN without going to the LM.
The number of times the kernel waited on a snapshot SCN.
The total number of logons since the instance started. This statistic is useful only in V$SYSSTAT. It gives an instance overview of all processes that logged on.
The total number of current logons. This statistic is useful only in V$SYSSTAT.
Incremented when the native arithmetic runtime engine is invoked.
Incremented when the runtime engine encounters an overflow condition.
The number of SCNs (System Change Numbers) obtained without going to the DLM.
The number of times CR gets require no block cleanouts nor rollbacks.
The total number of opened cursors since the instance has started (in V$SYSSTAT). In V$SESSTAT, this statistic shows the total number of cursors opened since the start of the session.
The total number of current open cursors.
The total number of files that needed to be reopened because they were no longer in the process file cache.
The total number of file opens that caused a current file to be closed in the process file cache.
The total number of parse calls (real parses). A hard parse means allocating a workheap and other memory structures, and then building a parse tree. A hard parse is a very expensive operation in terms of memory use.
Total number of parse calls (hard and soft). A soft parse is a check to make sure that the permissions on the underlying object have not changed.
The total CPU time used for parsing (hard and soft) in 10s of milliseconds.
The total elapsed time for parsing in 10s of milliseconds. By subtracting parse time cpu from the this statistic, the total waiting time for parse resources is determined. For more information see parse time cpu above.
The total number of data blocks read from disk. This equals the number of "physical reads direct" plus all reads into buffer cache.
The number of reads directly read from disk bypassing the buffer cache. For example, in high bandwidth, data-intensive operations such as parallel query, reads of disk blocks bypass the buffer cache to maximize transfer rates and to prevent the premature aging of shared data blocks resident in the buffer cache.
The total number of data blocks written to disk. This equals the number of "physical writes direct" plus all writes from buffer cache.
The number of writes directly written to disk bypassing the buffer cache (as in a direct load operation).
The number of writes that would occur were checkpointing turned off. Note that this is a theoretical number because checkpointing will always be required for log switches.
The number of times a foreground encountered a cold buffer that was pinned or had a waiter that was about to pin it when the foreground is scanning the tail of the replacement list looking for a buffer to reuse. It should be uncommon because a cold buffer should not be pinned very often.
The number of SELECT statements that got parallelized.
The elapsed time of I/O while doing recovery.
The number of reads performed during recovery.
The number of blocks read during recovery.
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.
The total CPU time used by non-user calls (recursive calls). Subtract this value from CPU used by this session to determine how much CPU time was used by the user calls.
This statistic increments each time redo entries are copied into the redo log buffer.
The active log file is full 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 work load 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 and log file space switch wait events in V$SESSION_WAIT.
The total elapsed time of waiting for redo log space request in 10s of milliseconds.
The number of times that another instance asked this instance to advance to the next log file.
The number of times that an SCN had to be allocated to force a redo record to have an higher SCN than a record generated in another thread using the same block.
The total amount of redo generated in bytes.
The elapsed time of all redo sync writes calls in 10s of milliseconds.
Usually, 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.
Number of bytes wasted because redo blocks needed to be written before they are completely full. Early writing may be needed to commit transactions, to be able to write a database buffer or to switch logs.
The total elapsed time of the write from the redo log buffer to the current redo log file in 10s of milliseconds.
The elapsed time need by LWGR to obtain and release each copy latch in 10s of milliseconds. This is only used if the initialization parameter LOG_SIMULTANEOUS_COPIES > 0. For more information, see "LOG_SIMULTANEOUS_COPIES".
Count of the total number of writes by LGWR to the redo log files.
The number of times this instance wrote a dirty undo block so that another instance could read it.
The number of times this instance wrote a dirty undo header block so that another instance could read it.
The number of undo records applied to rollback (real) changes.
The number of times CR gets require only block rollbacks, no block cleanouts.
The number of times a SQL statement in serializable isolation level had to abort.
The connect time for the session in 1/100 seconds. This value is useful only in V$SESSTAT. It is the wall clock time of when the logon to this session occurred.
The total number of cursor cached. This is only incremented if SESSION_CACHED_CURSORS > 0. This statistic is the most useful in V$SESSTAT. If the value for this statistic in V$SESSTAT is close to the setting of the initialization parameter SESSION_CACHED_CURSORS, the value of the initialization parameter should be increased.
The count of the number of hits in the session cursor cache. A hit means that the SQL statement did not have to be reparsed. By subtracting this statistic from parse count (total) one can determine the real number of parses that happened.
This statistic is basically db block gets + consistent gets. For more information, see "db block gets" and "consistent gets".
This statistic shows the current PGA size for a session. This statistic is useful only in V$SESSTAT; it has no meaning in V$SYSSTAT.
This statistic shows the peak PGA size for a session. This statistic is useful only in V$SESSTAT; it has no meaning in V$SYSSTAT.
This statistic shows the amount of memory that this session is using for stored procedures.
This statistic shows the current UGA size for a session. This statistic is useful only in V$SESSTAT; it has no meaning in V$SYSSTAT.
This statistic shows the peak UGA size for a session. This statistic is useful only in V$SESSTAT; it has no meaning 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 size of the initialization parameter SORT_AREA_SIZE. For more information, see "SORT_AREA_SIZE".
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 work load. You cannot 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.
The total number of rows sorted.
The sum of the dirty LRU queue length after every write request. Divide by write requests to get the average queue length after write completion.
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 indicates either non-optimal queries or tables without indexes, this statistic should increase as the above issues have been addressed in the application.
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 a 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).
During scanning operations, each row is retrieved sequentially by Oracle. Each block encountered during the scan increments this statistic.
This statistic informs you of the number of database blocks that you had to get from the buffer cache for the purpose of scanning. Compare the value of this parameter to the value of consistent gets to get a feeling for how much of the consistent read activity can be attributed to scanning. For more information, see "consistent gets".
This statistic is collected during a scan operation, but instead of counting the number of database blocks, it counts the rows being processed.
Count of range scans on tables that have the CACHE option enabled.
Count of table scans performed with direct read (bypassing the buffer cache).
Long (or conversely short) tables can be defined as tables that do not meet the short table criteria as described in table scans (short tables) below.
Count of table scans with specified ROWID endpoints. This is performed for Parallel Query.
Long (or conversely short) tables can be defined by optimizer hints coming down into the row source access layer of Oracle. The table must have the CACHE option set.
The total number of file opens being performed by the instance. Each process needs a number of files (control file, log file, database file) in order to work against the database.
The number of transactions being successfully rolled back.
The number of times transaction tables are CR rolled back.
The number of undo records applied to CR rollback transaction tables.
Oracle allocates resources (Call State Objects) to keep track of relevant user call data structures every time you log in, 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.
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.
This statistic stores the number of times users manually issue the ROLLBACK statement or an error occurs during users' transactions.
