Release 8.0
A58242-01
 Library |
 Product |
 Contents |
 Index |
| Oracle8 Reference Release 8.0 A58242-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.
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" on page 3-89, "V$STATNAME" on page 3-103, and "V$SYSSTAT" on page 3-107.
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.
A buffer in the buffer cache was cloned. The most common reason for cloning is that the buffer is held in a incompatible mode.
A CURRENT buffer (shared or exclusive) is made CR before it can be used.
The DBWR didn't find any dirty buffers for an object that was flushed from the cache.
The number of times DBWR received a flush by object number cross instance call (from a remote instance). This includes both checkpoint and invalidate object.
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.
Number of times the DBWR was asked to scan the cache and write all blocks marked for a checkpoint.
The total number of blocks written for other instances so that they can access the buffers.
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 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 times that the DBWR has been idle since the last timeout. These are the times that the DBWR looked for buffers to idle write.
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 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 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 times this instance made a cross instance call to write a particular block due to timeout on an instance lock get. The call allowed the block to be read CR rather than CURRENT.
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 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" on page C-7.
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 number of times that the buffer cache hash chain latch couldn't be acquired immediately, when processing a lock element.
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 elapsed time of all synchronous global lock releases.
The total number of synchronous global lock releases.
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 number of times a lock element was busy.
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.
The number of SCNs (System Change Numbers) obtained without going to the DLM.
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.
The total number of parse calls that didn't result in a hard parse.
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.
This statistic stores the number of physical blocks when the operating system retrieves 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 are used to calculate the buffer cache hit ratio.
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.
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 total number of entries of size <= REDO_ENTRY_PREBUILD_THRESHOLD. Building these entries increase CPU time, but may increase concurrency on a multi-processor system.
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" on page 1-67.
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 times this instance requested undo from another instance so it could be read CR.
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. For more information, see "parse count (soft)" on page C-10.
This statistic is basically db block gets + consistent gets. For more information, see "db block gets" on page C-7 and "consistent gets" on page C-6.
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" on page 1-114.
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. For more information, see "write requests" on page -18.
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" on page C-6.
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.
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.
This statistic stores the number of times DBWR takes a batch of dirty buffers and writes them to disk.
