Oracle8 Server Reference
Release 8.0

A54645_01

Library

Product

Contents

Index

Prev Next

C
Statistics Descriptions

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.

For more information on these views, see "V$SESSTAT" on page 3-91, "V$STATNAME" on page 3-105, and "V$SYSSTAT" on page 3-108.

Statistics Descriptions

This section describes the statistics stored in the V$SESSTAT and V$SYSSTAT views.

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.

CR blocks created

A buffer in the buffer cache was cloned. The most common reason for cloning is that the buffer is held in a incompatible mode.

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 didn't 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 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 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.

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 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.

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 recv'd

The number of local messages received for Parallel Executions.

PX local messages sent

The number of local messages send for Parallel Executions.

PX remote messages recv'd

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.

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.

Work loads 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.

db block changes

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 tracks the number of blocks obtained in CURRENT mode.

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" on page C-7.

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 defered until later.

global cache freelist waits

The number of pings for free lock elements (when all releasable locks are inuse)

global cache hash latch waits

The number of times that the buffer cache hash chain latch couldn't be acquired immediately, when processing a lock element.

global lock convert time

The total elapsed time of all synchronous (non-asynchronous) global lock converts in 10s of milliseconds.

global lock converts (async)

The total number of asynchronous global lock converts.

global lock converts (non async)

The total number of synchronous global lock converts.

global lock get time

The total elapsed time of all synchronous (non-asynchronous) global lock gets in 10s of milliseconds.

global lock gets (async)

The total number of asynchronous global lock gets.

global lock gets (non async)

The total number of synchronous global lock gets.

global lock release time

The elapsed time of all synchronous global lock releases.

global lock releases

The total number of synchronous global lock releases.

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.

kcmccs 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.

lock element waits

The number of times a lock element was busy.

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.

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 (soft)

The total number of parse calls that didn't result in a hard parse.

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 the this statistic, the total waiting time for parse resources is determined. For more information see "parse time cpu" above.

physical reads

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 are used to calculate the buffer cache hit ratio.

physical writes

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.

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 increase CPU time, but may 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 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.

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 an 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 writes calls in 10s of milliseconds.

redo sync 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" on page 1-65.

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.

serializable aborts

The number of times a 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 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.

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. For more information, see "parse count (soft)" on page C-11.

session logical reads

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-7.

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" on page 1-106.

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 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.

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 write requests to get the average queue length after write completion. For more information, see "write requests" on page C-19.

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" on page C-7.

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.

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, 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

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.




Prev

Next
Oracle
Copyright © 1997 Oracle Corporation.

All Rights Reserved.

Library

Product

Contents

Index