ITPub博客

首页 > 数据库 > Oracle > [转载]关于v$sysstat

[转载]关于v$sysstat

原创 Oracle 作者:lifewise 时间:2007-09-26 13:48:29 0 删除 编辑
转载]关于v$sysstat

本文为Oracle的重要视图v$sysstat提供了一个完整的解释列表,原文发表在:

http://www.mercury.com/us/products/performance-center/loadrunner/monitors/oracle.html

[@more@]转载]关于v$sysstat
发表人:www.wwf.co | 发表时间: 2005年七月14日, 14:39

本文为Oracle的重要视图v$sysstat提供了一个完整的解释列表,原文发表在:

http://www.mercury.com/us/products/performance-center/loadrunner/monitors/oracle.html

  • CPU used by this session - 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.
  • CPU used when call started - The CPU time used when the call is started.
  • CR blocks created - A buffer in the buffer cache was cloned. The most common reason for cloning is that the buffer is held in an incompatible mode.
  • Cached Commit SCN referenced - The number of times cached Commit SCN is referenced.
  • Commit SCN cached - The number of times Commit SCN is cached.
  • Current blocks converted for CR - A CURRENT buffer (shared or exclusive) is made CR before it can be used.
  • DBWR Flush object call found no dirty buffers - The DBWR did not find any dirty buffers for an object that was flushed from the cache.
  • DBWR Flush object cross instance calls - 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.
  • DBWR buffers scanned - 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.
  • DBWR checkpoint buffers written - The number of buffers that were written for checkpoints.
  • DBWR checkpoints - Number of times the DBWR was asked to scan the cache and write all blocks marked for a checkpoint.
  • DBWR cross instance writes - The total number of blocks written for other instances so that they can access the buffers.
  • DBWR free buffers found - The number of buffers that DBWR found 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.
  • DBWR lru scans - 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.
  • DBWR make free requests - Number of messages received requesting DBWR to make some more free buffers for the LRU.
  • DBWR revisited being-written buffer - 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.
  • DBWR skip hot writes - The number of times DBWR skipped writing "hot" buffers.
  • DBWR summed scan depth - 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.
  • DBWR timeouts - 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.
  • DBWR undo block writes - 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.
  • DDL statements parallelized - The number of DDL statements that were parallelized.
  • DML statements parallelized - The number of DML statements that were parallelized.
  • PX local messages received - The number of local messages received for Parallel Executions.
  • PX local messages sent - The number of local messages sent for Parallel Executions.
  • PX remote messages received - The number of remote messages received for Parallel Executions.
  • PX remote messages sent - The number of remote messages sent for Parallel Executions.
  • SQL*Net roundtrips to/from client - Total number of Net8 messages sent to and received from the client.
  • SQL*Net roundtrips to/from dblink - Total number of Net8 messages sent over and received from a database link.
  • Unnecessary process cleanup for SCN batching - 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.
  • Background checkpoints completed - The number of checkpoints completed by the background. This statistic is incremented when the background successfully advances the thread checkpoint.
  • Background checkpoints started - 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.
  • Bytes received via SQL*Net from client - The total number of bytes received from the client over Net8.
  • Bytes received via SQL*Net from dblink - The total number of bytes received from a database link over Net8.
  • Bytes sent via SQL*Net to client - The total number of bytes sent to the client from the foreground process(es).
  • Bytes sent via SQL*Net to dblink - The total number of bytes sent over a database link.
  • Calls to get snapshot SCN: kcmgss - The number of times a snap System Change Number (SCN) was allocated. The SCN is allocated at the start of a transaction.
  • Change write time - The elapsed time for redo write for changes made to CURRENT blocks in 10s of milliseconds.
  • Cleanouts and rollbacks - consistent read gets - The number of times CR gets require both block rollbacks, and block cleanouts.
  • Cleanouts only - consistent read gets - The number of times CR gets require only block cleanouts, no rollbacks.
  • Cluster key scan block gets - The number of blocks obtained in a cluster scan.
  • Cluster key scans - The number of cluster scans that were started.
  • Commit cleanout failures: block lost - 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.
  • Commit cleanout failures: buffer being written - The number of times a cleanout at commit was attempted but the buffer was currently being written.
  • Commit cleanout failures: callback failure - The number of times the cleanout callback function returns FALSE.
  • Commit cleanout failures: cannot pin - The total number of times a commit cleanout was performed but failed because the block could not be pinned.
  • Commit cleanout failures: hot backup in progress - 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.
  • Commit cleanout failures: write disabled - The number of times that a cleanout at commit time was performed but the writes to the database had been temporarily disabled.
  • Commit cleanouts - The total number of times the cleanout block at commit time function was performed.
  • Commit cleanouts successfully completed - The number of times the cleanout block at commit time function successfully completed.
  • Consistent changes - The number of times a database block has applied rollback entries 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 - The number of times a consistent read was requested for a block. See also consistent changes above.
  • Cross instance CR read - 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.
  • Data blocks consistent reads - undo records applied - The number of undo records applied to CR rollback data blocks.
  • DB block changes - Closely related to consistent changes, this statistic 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 tracks the number of blocks obtained in CURRENT mode.
  • Deferred (CURRENT) block cleanout applications - The number of times cleanout records are deferred, piggyback with changes, always current get.
  • Dirty buffers inspected - The number of dirty buffers found by the foreground while the foreground is looking for a buffer to reuse.
  • Enqueue conversions - The total number of enqueue converts.
  • Enqueue deadlocks - The total number of enqueue deadlocks between different sessions.
  • Enqueue releases - The total number of enqueue releases.
  • Enqueue requests - The total number of enqueue gets.
  • Enqueue timeouts - The total number of enqueue operations (get and convert) that timed out before they could complete.
  • Enqueue waits - The total number of waits that happened during an enqueue convert or get because the enqueue could not be granted right away.
  • Exchange deadlocks - 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.
  • Execute count - The total number of calls (user and recursive) that execute SQL statements.
  • Free buffer inspected - 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."
  • Free buffer requested - The count of the number of times a reusable buffer or a free buffer was requested to create or load a block.
  • Global cache defers - The number of times a ping request was deferred until later.
  • Global cache freelist waits - The number of pings for free lock elements (when all releasable locks are in use).
  • Global lock async converts - The total number of asynchronous global lock converts.
  • Global lock async gets - The total number of asynchronous global lock gets.
  • Global lock convert time - The total elapsed time of all synchronous (non-asynchronous) global lock converts in 10s of milliseconds.
  • Global lock get time - The total elapsed time of all synchronous (non-asynchronous) global lock gets in 10s of milliseconds.
  • Global lock releases - The total number of synchronous global lock releases.
  • Global lock sync converts - The total number of synchronous global lock converts.
  • Global lock sync gets - The total number of synchronous global lock gets.
  • Immediate (CR) block cleanout applications - The number of times cleanout records are applied immediately during CR gets.
  • Immediate (CURRENT) block cleanout applications - The number of times cleanout records are applied immediately during current gets.
  • Kcmccs called get current SCN - The number of times the kernel got the CURRENT SCN when there was a need to casually confirm the SCN.
  • Kcmgss read SCN without going to DLM - The number of times the kernel casually confirmed the SCN without going to the LM.
  • Kcmgss waited for batching - The number of times the kernel waited on a snapshot SCN.
  • Logons cumulative - 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.
  • Logons current - The total number of current logons. This statistic is useful only in V$SYSSTAT.
  • Next SCNs gotten without going to DLM - The number of SCNs (System Change Numbers) obtained without going to the DLM.
  • No work - consistent read gets - The number of times CR gets require no block cleanouts nor rollbacks.
  • Opened cursors cumulative - 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.
  • Opened cursors current - The total number of current open cursors.
  • Opens of replaced files - The total number of files that needed to be reopened because they were no longer in the process file cache.
  • Opens requiring cache replacement - The total number of file opens that caused a current file to be closed in the process file cache.
  • Parse count (hard) - 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.
  • Parse count (total) - 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.
  • Parse time CPU - The total CPU time used for parsing (hard and soft) in 10s of milliseconds.
  • Parse time elapsed - The total elapsed time for parsing in 10s of milliseconds. By subtracting parse time CPU from this statistic, the total waiting time for parse resources is determined. For more information, see parse time CPU above.
  • Physical reads - The total number of data blocks read from disk. This equals the number of physical reads direct plus all reads into buffer cache.
  • Physical writes - The total number of data blocks written to disk. This equals the number of "physical writes direct" plus all writes from buffer cache.
  • Queries parallelized - The number of SELECT statements that got parallelized.
  • Recovery array read time - The elapsed time of I/O while doing recovery.
  • Recovery array reads - The number of reads performed during recovery.
  • Recovery blocks read - The number of blocks read during recovery.
  • Recursive calls - 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.
  • Recursive CPU usage - 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.
  • Redo entries - This statistic increments each time redo entries are copied into the redo log buffer.
  • Redo entries linearized -The total number of entries of size <= REDO_ENTRY_PREBUILD_THRESHOLD. Building these entries increases CPU time, but may also increase concurrency on a multi-processor system.
  • Redo log space requests - 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 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 and log file space switch wait events in V$SESSION_WAIT.
  • Redo log space wait time - The total elapsed time of waiting for redo log space request in 10s of milliseconds.
  • Redo log switch interrupts - The number of times that another instance asked this instance to advance to the next log file.
  • Redo ordering marks - The number of times that an SCN had to be allocated to force a redo record to have a higher SCN than a record generated in another thread using the same block.
  • Redo size - The total amount of redo generated in bytes.
  • Redo synch time - The elapsed time of all redo sync write calls in 10s of milliseconds.
  • Redo synch writes - 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.
  • Redo wastage - 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.
  • Redo write time - The total elapsed time of the write from the redo log buffer to the current redo log file in 10s of milliseconds.
  • Redo writer latching time - 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".
  • Redo writes - Count of the total number of writes by LGWR to the redo log files.
  • Remote instance undo block writes - The number of times this instance wrote a dirty undo block so that another instance could read it.
  • Remote instance undo header writes - The number of times this instance wrote a dirty undo header block so that another instance could read it.
  • Remote instance undo requests - The number of times this instance requested undo from another instance so it could be read CR.
  • Rollback changes - undo records applied - The number of undo records applied to rollback (real) changes.
  • Rollbacks only - consistent read gets - The number of times CR gets require only block rollbacks, no block cleanouts.
  • Serializable aborts - The number of times an SQL statement in serializable isolation level had to abort.
  • Session connect time - 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.
  • Session cursor cache count - The total number of cursors 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.
  • Session cursor cache hits -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.
  • Session logical reads - This statistic is basically DB block gets + consistent gets. For more information, see "DB block gets" and "consistent gets."
  • Session PGA memory - 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.
  • Session PGA memory max - 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.
  • Session stored procedure space - This statistic shows the amount of memory that this session is using for stored procedures.
  • Session UGA memory - 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.
  • Session UGA memory max - 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.
  • Sorts (disk) - 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".
  • Sorts (memory) - If the number of disk writes is zero, then the sort was performed completely in memory and this statistic is incremented. This is further indication of sorting activity in the application workload. 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.
  • Sorts (rows) - The total number of rows sorted.
  • Summed dirty queue length - The sum of the dirty LRU queue length after every write request. Divide by writes requests to get the average queue length after write completion.
  • Table fetch by ROWID - 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.
  • Table fetch continued row -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).
  • Table scan blocks gotten - 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".
  • Table scan rows gotten - 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 (cache partitions) - Count of range scans on tables that have the CACHE option enabled.
  • Table scans (direct read) - Count of table scans performed with direct read (bypassing the buffer cache).
  • Table scans (long tables) - 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.
  • Table scans (rowid ranges) - Count of table scans with specified ROWID endpoints. This is performed for Parallel Query.
  • Table scans (short tables) - 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.
  • Total file opens - 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.
  • Transaction rollbacks - The number of transactions being successfully rolled back.
  • Transaction tables consistent read rollbacks - The number of times transaction tables are CR rolled back.
  • Transaction tables consistent reads -undo records applied - The number of undo records applied to CR rollback transaction tables.
  • User calls - 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, indicates how much internal work gets generated as a result of the type of requests the user is sending to Oracle.
  • User commits - 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 stores the number of times users manually issue the ROLLBACK statement or an error occurs during users' transactions.
  • Write requests - This statistic stores the number of times DBWR takes a batch of dirty buffers and writes them to disk.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/620862/viewspace-972479/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-01-07

  • 博文量
    52
  • 访问量
    488412