Metric Listing
Unless stated otherwise, time and resource usage metrics are denominated in seconds and bytes, respectively.
A B C D E F G I L M N O P R S T U
|
The amount of time that the StealthCollect agent was inactive during the current interval. | ||||
|
Sum of all the active waits, equal to the session total activity within the current interval. | ||||
|
Any activity which generates LGWR for archiving the redo logs to the target location. Additionally, user sessions can also experience archival waits when the log that the LGWR will be switching into has not yet been archived and the session is issuing DMLs. | ||||
|
The number of collisions (number of times when two machines send packets at the same time on the network and the packets "collide" so that both packets need to be retransmitted) occurred during the current interval divided by the number of packets transmitted during the current interval. | ||||
|
The average time spent (ms) a resource was locked (held in contention). Lock waits occur when a session attempts to acquire a lock that is already held by another session. One session will be blocked until the blocking session (the first to acquire a lock on the resource) releases the lock. High values of lock duration may indicate aggressive lock incidence which may be improved upon tuning. The metric is calculated as follows:
| ||||
|
Average packet size sent by the system on all network interfaces. | ||||
|
The average time (ms) spent reading a data block from the disk into the database buffer cache. On most disks this metric should not exceed 20 milliseconds and readings above 40 milliseconds may indicate either a device bottleneck, or poorly configured disks or storage sub-systems. The metric is calculated as follows:
| ||||
|
The average time spent (ms) writing a redo log entry to the log files. User sessions experience log write waits when, upon commits, an individual session waits for the LGWR to flush its redo information to the redo log file. Unlike most other Oracle write I/Os, Oracle sessions must wait for redo log writes to complete before they can continue processing. On most disks this metric should not exceed 20 milliseconds and readings above 40 milliseconds may indicate log files are not placed on devices with sufficient write speeds. The metric is calculated as follows:
| ||||
|
Average duration of the SQL statements, executed during the current interval | ||||
|
The gross response time for a given SQL cursor; response time is measured beginning with cursor creation and continuing until the cursor is closed. This value tends to be higher than the value of average Active Time Per Execution, since Active Time measures the net SQL activity; net SQL activity ignores any pauses in SQL execution such as sporadic application processing between distinct Fetch operations. | ||||
|
The average time spent (ms) writing a data block from the database buffer to the disk. The DBWR writes data blocks to datafiles (for example, flushing dirty data buffers from the SGA to the datafiles). On most disks
this metric should not exceed 20 milliseconds and readings above 40 milliseconds
may indicate either a device bottleneck, or poorly configured disks
or storage sub-systems. The metric is calculated as follows:
| ||||
|
When archiving is not performed fast enough, database operations can be suspended temporarily whenever the log writer is forced to wait for an inactive group to become available for reuse. | ||||
|
IntelliProfile is the new adaptive configurable (both agent and display) baseline profiling technology introduced in the Performance Analysis agent. IntelliProfile learns the instance signature behavior characteristics via sophisticated algorithms to identify the various cyclical and periodic behaviors and to neutralize the random noise distortion (for example, an employee absent due to illness). Its adaptive element grows smarter and more accurate with increases in the sampling period. Armed with this knowledge, IntelliProfile can project the acceptable/normal ranges of activity per each point in time. | ||||
|
This ratio measures the amount of DML work that each transaction performs by computing the average blocks changed per single user call. Creating or dropping indexes affects this value, because changes to index blocks increment it. The ratio is calculated as follows:
| ||||
|
This rate determines the block get rate, which is a basic measure of the rate at which the application system references the database. Change in metric size may indicate that the database workload has materially increased or that the referenced segment sizes increased. | ||||
|
The session wants to access a data block that is either:
| ||||
|
Buffer waits occur because of a contention in accessing database block buffers. For example, when a session wants to access a data block that is in an incompatible mode (buffer busy) or wants to read a data block into the buffer, but it has no available space (free buffer). | ||||
|
Buffer waits occur because of a contention in accessing database block buffers. For example, when a session wants to access a data block that is in an incompatible mode (buffer busy) or wants to read a data block into the buffer, but it has no available space (free buffer). The ratio is calculated as follows:
| ||||
|
Total number of bytes sent to the client from the foreground processes. | ||||
|
Total number of bytes sent over a database link. | ||||
|
The effectiveness of the buffer cache, computed by comparing the number of block requests, which found the block in the buffer cache with the entire block requests (including requests which performed disk reads).
This ratio is calculated as follows:
| ||||
|
This rate measures the workload being placed on the
instance from all work sources. It should be noted, however, that this
rate might not be directly comparable across application system version
changes where row-at-a-time loop constructs have been re-coded as set
operations or vice versa. Use of an array interface will also affect this
rate. The rate is calculated as follows: | ||||
|
This ratio measures the percentage of chained or migrated rows encountered during a fetch as a percentage of the entire fetched rows.
The ratio is calculated as follows:
| ||||
|
Resource coordination within Real Application Clusters occurs at both an instance level and at a cluster database level. Waits under this category refer to activities that of a global nature such as waits encountered while movement of blocks between instances participating in the cluster or waits encountered by cluster specific background processes. | ||||
|
|
Common cluster-wide membership node view encompassing cluster resources (such as interconnect hardware, shared disks, and Oracle instances) | |||
|
This ratio measures the extent to which applications must exercise the read consistency mechanism. It is important to realize that the query processing parts of the UPDATE and DELETE operations are subject to read consistency. The ratio is calculated as follows:
| ||||
|
Number of times a user process 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. The value of this statistic should be small in relation to the "consistent gets" statistic. | ||||
|
The number of data block reads in CONSISTENT mode (i.e. consistent reads). A CONSISTENT read retrieves the block version as at the time the query has started. Consistent mode reads are usually associated with query activity. Also known as: consistent gets | ||||
|
The session has issued I/O requests to control file blocks, and is waiting for all of the reads and writes to complete. | ||||
|
When SQL statements and other types of calls are made to Oracle, an amount of CPU time is necessary to process the call. Average calls require a small amount of CPU time. However, a SQL statement involving a large amount of data or a runaway query can potentially consume a large amount of CPU time, reducing CPU time available for other processing. CPU utilization is a key operating system statistic in the tuning process. Excessive CPU could be caused by an inadequately-sized system, by untuned SQL statements, or by inefficient application programs. | ||||
|
Wait Time until the CPU resource is available. Time spent by the session waiting in the system's run queue to be granted for CPU cycles. The amount of time is dependent upon the number of concurrent processes and threads requesting CPU time. The metric value should be inspected in conjunction with the value of the "Run Queue Length" metric. | ||||
|
The number of data block reads in CURRENT mode (i.e. current reads). A CURRENT read retrieves the latest version of the block as at the time of reading. Current mode reads are usually associated with DML activity. AKA: db block gets. | ||||
|
The DBWR is performing a write to files and blocks (for example, flushing dirty data buffers from the SGA to the data files). The db file writes are not experienced by user sessions. | ||||
|
The relative amount of time that the Oracle instance was running during the current interval. (The percentage of time during which the database was available.) If the instance is down, no data collection occurs. Thus, if this metric value is less than the interval length, the value of this statistic reflects only the active portion of the interval. The three states are as follows:
| ||||
|
The number of database blocks in memory (SGA) that were modified by the session as a result of DML activity. Such changes generate redo log entries and hence become permanent changes to the database if the transaction is committed. Because each logical change to a block typically involves more than one physical change to the block, the value of this statistic will always be considerably greater than the number of blocks dirtied in the cache. | ||||
|
The session has issued multiple I/O requests in parallel to read blocks from data files into memory and is waiting for all requests to complete. This may occur during recovery or during regular activity when a session batches many single block I/O requests together and issues them in parallel. | ||||
|
The wait time it takes for messages, data or break reset to be transmitted over the network interface to another server process on a remote database. | ||||
|
The relative amount of time that the Oracle instance was unavailable during the current interval. (The percentage of time during which the database was unavailable.) If the instance is down, no data collection occurs. Thus, if this metric value is less than the interval length, the value of this statistic reflects only the inactive portion of the interval. The three states are as follows:
| ||||
|
The total number of server processes (master and slaves) assigned to handle a single operation. | ||||
|
The session has issued asynchronous I/O requests that bypass the buffer cache and is waiting for them to complete. Direct Path I/O usually reflects disk sorts and bulk loads. | ||||
|
The percentage of time the busiest device spent serving system-wide I/O requests. This metric serves as a measure for the system I/O load. High values may indicate device bottleneck. | ||||
|
Elapsed Time |
The amount of time (in microseconds) used by the active cursor for parsing/executing/fetching. | |||
|
Total number of table or row locks obtained by the session. | ||||
|
Enqueue waits occur when a session waits to obtain a lock. In most cases, this occurs because of a lock on a table or row that the waiting session needs to lock or modify. In some circumstances, the lock involved may be an Oracle internal lock. If the database is well tuned and the application design sound, enqueue waits should be negligible. | ||||
|
Wait state out enqueue operations as a percentage of the entire enqueue operations.
The ratio is calculated as follows:
| ||||
|
Number of statements whose activity finished during the current interval. | ||||
|
Number of statements issued during the current interval. | ||||
|
Session waits because of an I/O request on an external large object (LOB), such as a BFILE. | ||||
|
The session needs a free buffer so it can bring a data block into the buffer cache and is waiting for a buffer that is not dirty to become available. This can occur if DBWR is not writing dirty buffers to disk fast enough. This wait usually indicates a non-optimal physical I/O configuration. Free buffer waits can be minimized by increasing the checkpoint frequency, using more DBWR processes, or increasing the number of physical disks. | ||||
|
Global Cache Service (GCS) Operations tracks the locations, modes, and roles of data blocks. The GCS therefore also manages the access privileges of (and imposes waits on) various instances in relation to resources. Oracle uses the GCS for cache coherency when the current version of a data block is in one instance's buffer cache and another instance requests that block for modification. Apart from the regular locking at the instance level, Oracle maintains another lock structure and a set of lock escalation rules to manage data blocks between instances. This lock structure is a three character representation consisting of a Role, Mode and indicator:
| ||||
|
Time spent waiting for disk input/output operations to complete. Input/output (I/O) is one of the most expensive operations in a database system. SQL statements that are I/O intensive can monopolize memory and disk use and cause other database operations to compete for these resources. Generally, I/O Wait is caused by poorly-tuned SQLs or applications which generate a significant amount of logical I/O translating into excessive physical disk usage. In this case, SQL/Application tuning can reduce the logical I/O- induced load. However, it could also be caused by poorly-configured disks or storage sub-systems. | ||||
| Idle events are events that spend their time idle or waiting for an action. The time keeps incrementing for the event until an action occurs. An action can be a database process, user or application process. These wait events can be ignored most of the time. | ||||
|
The latch miss ratio defines the number of times a process obtained a willing-to-wait latch vs. missing the attempt. Latches protect the many memory structures in Oracle's SGA. They ensure that one and only one process at a time runs or modifies any memory structure at the same instant. Latches are much more restrictive than locks, which at least allow for some collective user interaction. They have no queuing mechanism, and therefore, you either get the latch or you are forced to continually retry. A high value for this ratio indicates a latching problem, whereas a low value is generally good. However, as the data is rolled up over all latches, a low latch miss ratio can artificially mask a low get rate on a specific latch. If the latch miss ratio exceeds 1%, you should take action to resolve the amount of latch contention occurring. | ||||
|
Latch waits are the waits that occur when a session needs to acquire a latch that is held by another session. Latches are simple, low-level serialization mechanisms to protect shared SGA data structures and shared code segments from simultaneous session access. They are similar in purpose to locks: Latches protect internal memory structures while locks protect data structures. They are designed to be very quickly acquired and freed. The implementation of latches is operating system dependent, particularly in regard to whether a process will wait for a latch and for how long. For example, latches protect the list of users currently accessing the database and protect the data structures describing the blocks in the buffer cache. A server or background process acquires a latch for a very short time while manipulating or looking at one of these structures. Rarely is latch contention tunable by reconfiguring the instance. Rather, latch contention usually is resolved through application changes. Latch waits can be eliminated by either tuning the instance or the application. Tuning latch contention is dependent on the type of latch wait encountered. | ||||
|
Latch waits are the waits that occur when a session needs to acquire a latch that is held by another session. Latches are simple, low-level serialization mechanisms to protect shared SGA data structures and shared code segments from simultaneous session access. They are similar in purpose to locks: Latches protect internal memory structures while locks protect data structures. They are designed to be very quickly acquired and freed. The implementation of latches is operating system dependent, particularly in regard to whether a process will wait for a latch and for how long. For example, latches protect the list of users currently accessing the database and protect the data structures describing the blocks in the buffer cache. A server or background process acquires a latch for a very short time while manipulating or looking at one of these structures. Rarely is latch contention tunable by reconfiguring the instance. Rather, latch contention usually is resolved through application changes. Latch waits can be eliminated by either tuning the instance or the application. Tuning latch contention is dependent on the type of latch wait encountered. The ratio is calculated as follows:
| ||||
|
Lock wait occur when a session attempts to acquire a lock that is already held by another session. A session will be blocked until the blocking session releases the lock. Locks are designed to ensure data integrity by limiting simultaneous data access. Multi-user database locking generally consists of two levels: exclusive locks and share locks. You want to watch out for exclusive locks (that is, TX) as they prohibit resource sharing. For example, the first transaction that exclusively locks a resource is the only one that can alter the resource (except for the DBA) until the exclusive lock is released. Share locks, unlike exclusive locks, allow a resource to be shared. Deadlocking is commonly seen in multi-user systems. It typically occurs when all the hung users are waiting to access a table that another user has locked. This situation causes a deadlock, because each user (transaction) is waiting for resources to be freed by the other user (the blocker). Often, many developers attempt to update the same table and many users attempting to update or select from the same table. Most locking issues are application-specific and can be addressed by tuning the concurrency logic in the application. | ||||
|
Lock wait occur when a session attempts to acquire a lock that is already held by another session. A session will be blocked until the blocking session releases the lock. Locks are designed to ensure data integrity by limiting simultaneous data access. Multi-user database locking generally consists of two levels: exclusive locks and share locks. You want to watch out for exclusive locks (that is, TX) as they prohibit resource sharing. For example, the first transaction that exclusively locks a resource is the only one that can alter the resource (except for the DBA) until the exclusive lock is released. Share locks, unlike exclusive locks, allow a resource to be shared. Deadlocking is commonly seen in multi-user systems. It typically occurs when all the hung users are waiting to access a table that another user has locked. This situation causes a deadlock, because each user (transaction) is waiting for resources to be freed by the other user (the blocker). Often, many developers attempt to update the same table and many users attempting to update or select from the same table. Most locking issues are application-specific and can be addressed by tuning the concurrency logic in the application. The ratio is calculated as follows:
| ||||
|
Waits that occur when copying redo entries into the log buffer. A frequent cause of log buffer wait is if redo entries are written into the log buffer faster than LGWR can write it to the redo logs, and user sessions must therefore wait because of log write latency. | ||||
|
The session waits for a redo log switch to be completed. | ||||
|
This ratio measures the LGWR waits for log switches, relative to total instance activity. A log switch occurs when LGWR stops writing to one online redo log group and starts writing to another. By default, a log switch occurs automatically when the current online redo log file group becomes full. Log Switch Wait time portion should not be greater than five percent of the total workload activity, however it can become critical under the following circumstances: The next redo log to be used is not available because the checkpoint started when the log switch is not complete, or the redo log has not yet been archived. The ratio is calculated as follows:
| ||||
|
Waiting for the write (physical I/O) to a logfile to complete. LGWR experience log write waits when it periodically flushes the contents of the log buffer into the active log file. User sessions experience log write waits when, upon commits, it waits for the LGWR to flush its redo information to the redo log file. The user session will post the LGWR to write the log buffer to the redo log file. | ||||
|
The number of data blocks read by Oracle, which are already present in the buffer cache, thus requiring no I/O read operations. (The total of "Current Reads" plus "Consistent Reads"). AKA: session logical reads. | ||||
|
Amount of machine RAM available. | ||||
|
The amount of physical memory consumed by the various processes. High memory utilization along with high swapping and paging rates indicates that the amount of physical RAM should be increased. | ||||
|
Sporadic I/O activity, such as opening a data file. Usually negligible. | ||||
|
Assorted independent waits. Usually negligible. | ||||
|
The session is reading multiple data blocks from the data files into the SGA buffer. Multi-Block Read waits usually reflects full table or index scans. Generally full scans of large segments should be avoided, although they are sometimes unavoidable, as in data warehouse environments. | ||||
|
Interconnect Wait (Cluster) |
Apart from the regular network activity between the client and the database servers, in a RAC environment Oracle uses a private interconnect for transfer of data between instances to satisfy user requests. Performance of the interconnect is critical to the overall performance of the database server. This categorization represents waits encountered with respect to the interconnect activity. Significant network interconnect wait may indicate that excessive cache transfer because of a non-optimal application cluster affinity or a too-slow network link. In general, the network interconnect is responsible for:
| |||
|
Number of times when two computers send packets at the same time on the network and the packets "collide" so that both packets must be re-transmitted. | ||||
|
Number of errors reported on the various network interfaces. | ||||
|
Time spent waiting for messages to be sent or received over the network interface. Network performance is measured in number (per second) of packets sent and received. SQL*Net is the Oracle network interface that allows Oracle tools running on network workstations and servers to access, modify, share, and store data on other servers. SQL*Net is considered part of the program interface in network communications. Network statistics can be used in much the same way as disk statistics to determine if a network or network interface is overloaded or not performing optimally. Excessive network wait can be caused by either:
| ||||
|
Overall CPU consumption not associated with the monitored Oracle instance (background and foreground processes). | ||||
|
Amount of physical RAM consumed by processes other than that of the monitored Oracle instance. The statistic is calculated as follows: System Resident Memory Usage - Resident SGA Size - Session Resident Memory Usage | ||||
|
Amount of memory consumed by the monitored Oracle instance background processes, both physical and swap memory. | ||||
|
Amount of physical memory consumed by Oracle background processes. | ||||
|
Amount of physical memory consumed by Oracle background processes. | ||||
|
Overall monitored Oracle instance processes CPU usage. | ||||
|
Total number of I/O operations (both physical and logical) performed by the Oracle session. | ||||
|
CPU time consumed by the monitored Oracle instance user (session) processes. | ||||
|
Amount of physical memory consumed by the monitored Oracle instance foreground (session) processes. | ||||
|
Amount of physical memory consumed by the monitored Oracle instance foreground (session) processes. | ||||
|
Total number of physical I/O operations (read, writes and redo) performed by the monitored Oracle instance processes. Note: A physical I/O issued by Oracle can be satisfied by the operating system's file cache. | ||||
|
The amount of physical memory consumed by the various monitored Oracle instance processes (including the SGA). | ||||
|
The amount of CPU consumed by the entire system detailed into monitored Oracle instance activity and all other activities.
| ||||
|
The ratio of monitored Oracle instance I/O operations to total system I/O operations. This ratio ignores logical I/O operations performed by Oracle (only operations which were translated to actual system calls are included). It should be used when identifying specific disk I/O problems.
The ratio is calculated as follows:
| ||||
|
Total time spent waiting for miscellaneous operations to complete. None of the underlying operations (wait events) can be classified into any other wait categories. Usually negligible. | ||||
|
Sporadic buffer waits caused by buffer contention, such as buffer write wait. Usually negligible. | ||||
|
Total time spent waiting for miscellaneous operations to complete. None of the underlying operations (wait events) can be classified into any other wait categories. Usually negligible. The ratio is calculated as follows:
| ||||
|
Other DB Buffer Wait |
Sporadic buffer waits caused by buffer contention, such as buffer write wait. Usually negligible. | |||
|
Activity attributable to dimension entities other than the selected one. | ||||
|
The amount of time Oracle spent both waiting for CPU and using CPU during current interval. | ||||
|
Total number of packets transmitted and received by the operating system on the various network interfaces. Packets are blocks of information sent over the network each time a connection or data transfer is requested. The information contained in packets depends on the type of packet: connect, accept, redirect, data, and so on. | ||||
|
Number of packets received by all network interfaces. | ||||
|
Number of packets sent on all network interfaces. | ||||
|
Total number of page-in operations performed by the operating system. | ||||
|
Total number of page-out operations performed by the operating system. | ||||
|
Total number of page operations (Page-in and Page-outs) performed by the operating system. | ||||
|
Parse Count indicates the number of times a SQL statement was "hard" parsed. Hard parses (as opposed to soft parses which are satisfied by using already-cached objects) are real parse calls and, as such, are a very expensive operation in terms of memory use, because they require Oracle to allocate a work heap and other memory structures and then build a parse tree. | ||||
|
Amount of physical memory consumed by entire operating system processes (including Oracle) as a percentage of the total available. | ||||
|
The number of data blocks read by Oracle, which are not present in the buffer cache, thus requiring I/O read operations, i.e. the total number of data blocks read from disk. | ||||
|
Number of reads directly from disk into the PGA, bypassing the buffer cache. Generally, these are reads from temporary segments. | ||||
|
Total number of data blocks written to disk. This metric represents the rate at which the monitored Oracle instance generates system I/O calls. | ||||
|
The number of writes directly written to disk bypassing the buffer cache (as in a direct load operation). | ||||
|
Number of recursive calls generated at both the user and system level. When Oracle needs to make a change to the tables maintained for internal processing, it internally generates an internal SQL statement, which in turn generates a recursive call. They occur because of cache misses and dynamic storage extension. If the dictionary data is found in cache, a recursive call is not made and the data is read from cache directly. A high value for recursive calls is caused by improper cursor usage, excessive dynamic space management actions, and or excessive statement re-parses. You need to determine the cause and correct it By either relinking applications to hold cursors, use proper space management techniques (proper storage and sizing) or ensure repeat queries are placed in packages for proper reuse. | ||||
|
The number of redo records created (copied into the redo log buffer). | ||||
|
Number of times the active log file is full and the session must wait for a log switch to be performed. Once the log switch completes, the redo log entries can be allocated, and the session resumed. Log files that are small in relation to the size of the SGA or the commit rate of the workload can cause performance problems. | ||||
|
This ratio measures the redo memory allocation. If it is greater than 1/5000, then the redo log buffer should be increased until the redo log space wait ratio stops failing. The ratio is calculated as follows:
| ||||
|
All DML activity occurring in the database is recorded in the redo log. Redo waits include the contention resulting from: User sessions writing redo entries to the redo log buffer. LGWR writing the log buffer contents into the active redo log file. ARCH archiving redo logs to the destination location. | ||||
|
Total number of write operations issued by LGWR to the redo log files. | ||||
|
This ratio measures the relative SGA portion, which is RAM resident.
The ratio is calculated as follows:
| ||||
|
Amount of SGA resident in physical memory. In a properly working environment, all SGA is RAM resident. When portions of SGA are being swapped, Oracle performance is significantly degraded, as previously cached data now requires I/O to be accessed. Specify LOCK_SGA=TRUE to ensure that the entire SGA is RAM resident. | ||||
|
Waits experienced by sessions when exceeding thresholds/quotas permitted them by the resource manager. Resource Manager (10g and later) can be used to provide more fine-grained control of resource allocations to Services in both RAC and single instance environments. These two facilities are integrated so that Services can be mapped to Resource Manager consumer groups. Resource Manager waits are thus controls placed on Service workloads to limit the execution times of long running operations, the number of active sessions that are allowed to execute concurrently, the resources consumed by long running resource intensive sessions, and other resource usages. | ||||
|
This ratio measures the percentage of the total rows retrieved, which came from full table scans as a percentage of total retrieved rows.
The ratio is calculated as follows:
| ||||
|
The total number of rows returned by your SELECT or modified by your INSERT, UPDATE, or DELETE statement. | ||||
|
System average run queue. The CPU run queue is a holding area for threads and processes that require the CPU when the CPU is busy serving other processes. The run queue length is an indicator of whether the system has sufficient CPU resources for all the processes it executes.
| ||||
|
Number of Collector SGA samples performed during the interval. | ||||
|
Seconds per second is a measure of activity occurring in the database. It can also be described as the total amount of active time consumed by all sessions during a given second. Example: If during a given second, two sessions were active throughout the entire second, the total active time would be 2 seconds, therefore displayed as 2 seconds per second. If one session was active for only half a second, while the second was active throughout the entire second, the total active time would be 1.5 seconds, therefore displayed as 1.5 seconds per second. Note: This figure also equals to the average number of active sessions within a given second. | ||||
|
Number of sessions that performed a logon during the current interval. | ||||
|
Number of sessions logged out during the current interval. | ||||
|
Entire session process memory consumption, both physical and swap. | ||||
|
Amount of PGA memory allocated for the session. | ||||
|
Amount of physical memory consumed by the session process. | ||||
|
Number of sessions that were logged on during the current interval. | ||||
|
Amount of swap memory consumed by the session. | ||||
|
Amount of UGA memory allocated for the session. | ||||
|
Amount of memory allocated for SGA of the monitored Oracle instance. Not a V$SESSTAT statistic. | ||||
|
Amount of swapped SGA. In a properly working environment, this metric's value should be zero. When portions of SGA are being swapped, Oracle performance is significantly degraded, as previously cached data now requires I/O to be accessed. Specify LOCK_SGA=TRUE to ensure that the entire SGA is RAM resident. | ||||
|
The session is reading a single block from a data file into the SGA buffer. Large values can be acceptable, reflecting a high-transaction, well-tuned system with significant index utilization. However, a large number of waits here could also indicate poor joining orders of tables, or unselective indexing. | ||||
|
This ratio measures the portion of sorts that could not fit into a session's sort area and therefore used temporary segments. High values might indicate that the value of SORT_AREA_SIZE parameter (in the initialization files) should be increased to allow more memory space for sort operations. However, if the application requires sorting of vast number of rows, the use of disk sorting and temporary segments might not be avoided. The ratio is calculated as follows:
| ||||
|
Sort rows is the total number of rows sorted. AKA: sorts (rows) | ||||
|
Number of sort operations that were performed completely in memory and did not require any disk writes. The sort operation can take place in memory if the sorted data can be fit into the memory block specified by the SORT_AREA_SIZE parameter. AKA: sorts (memory) | ||||
|
Sorts that require creation of temporary segments on disk to store the intermediate sort results. This occurs if the data being sorted cannot be fit into memory block specified by the SORT_AREA_SIZE parameter. Obviously, disk sorts are several times slower than memory sorts and hence high values indicate that SORT_AREA_SIZE parameter should be increased. AKA: sorts (disk) | ||||
|
The wait time it takes for messages, data or break reset to be transmitted over the network interface to the client. | ||||
|
Total number of SQL*Net messages sent to and received from the client. | ||||
|
Number of statements executed during the current interval. | ||||
|
Number of system-wide physical I/O operations (both read and write operations). Physical operations require disk access. | ||||
|
Number of rows that are fetched using a ROWID (usually recovered from an index lookup). Generally, table scans indicate either non-optimal queries or lack of proper indexing. Therefore, this metric should increase (while the value of TABLE SCANS ROWS GOTTEN metric decreases) as queries are optimized and schemas are tuned using indexes. | ||||
|
Number of times a chained or migrated row is encountered during a fetch. Retrieving rows that span more than one block increase the logical I/O by a factor that corresponds to the number of blocks that need to be accessed, a problem which can be eliminated by exporting and re-importing. To avoid or minimize the problem of chained or migrated rows, evaluate the settings for the storage parameters PCTFREE and PCTUSED in relation to the average row length. This problem cannot be avoided if rows are larger than database blocks (for example, if the LONG datatype is used and the rows are extremely large). | ||||
|
Number of times Oracle has completely scanned a long table. A table is defined as long if: The table's high water mark is greater than five blocks or 2% of the db block buffer cache, or The table's high water mark is higher than CACHE_SIZE_THRESHOLD (v8.0 or earlier), or The table is defined without the CACHE option set (v8i and later). Generally, an excess of long table scans may indicate non-optimal table organization. | ||||
|
During parallel query, the number of table scans conducted by the query slaves with the specified ROWID ranges. | ||||
|
The number of rows scanned during full table scans. High values may indicate non-optimal queries and/or the absence of proper indexing. | ||||
|
Number of times Oracle has completely scanned a short table. A table is defined as short if: The table's high water mark is less than five blocks or 2% of the db block buffer cache, or The table's high water mark is less than CACHE_SIZE_THRESHOLD (v8.0 or earlier), or The table is defined with the CACHE option set (v8i and later). | ||||
|
Overall operating system CPU Usage (including Oracle). | ||||
|
Amount of free RAM in the system. | ||||
|
Overall amount of I/O write operations performed by the entire operating system per time unit. | ||||
|
Percentage of CPU time consumed by the operating system's processes (kernel mode activities). | ||||
|
Number of system-wide logical reads. The metric counts both physical I/O reads which require disk access and reads which were satisfied entirely by the operating system's file cache. | ||||
|
Number of system-wide logical writes. The metric counts both Physical I/O writes that require disk access and writes performed entirely in the operating system's file cache. | ||||
|
Amount of memory consumed by entire operating system processes (including Oracle), both physical and swap. | ||||
|
Total number of disk operations (both read and write) performed by the operating system. This metric does not include operations that were satisfied using the file system cache. | ||||
|
Number of system-wide physical reads. The metric counts Physical I/O reads that require disk access. Physical reads occur when the requested data is not present in the file system cache. | ||||
|
Number of system-wide physical writes. The metric counts Physical I/O writes that require disk access. | ||||
|
Percentage of CPU time consumed by the operating system's processes (user mode activities). | ||||
|
Amount of physical memory consumed by entire operating system processes (including Oracle). | ||||
|
Overall amount of I/O read operations performed by the entire operating system. | ||||
|
The user rollback ratio indicates the rate at which application transactions are failing. Rolling back a transaction uses significant resources, and indicates that all the resources expended in executing the transactions have been wasted. The ratio is calculated as follows:
| ||||
|
Number of user calls such as logon, parse, fetch, or execute. This metric value represents the rate at which the user application addresses Oracle. | ||||
|
This rate indicates how well the application is managing its context areas. It measures the average amount of calls in relation to statement parses. If it changes, then application change is the most likely explanation; but it may also indicate that usage patterns are changing and users are moving from one module to another, either more frequently or less frequently. An increasing value of this metric indicates that the effectiveness of the shared pool has improved, as fewer parses are needed. The ratio is calculated as follows:
| ||||
|
Number of transactions committed during the current interval. Once a transaction is committed, the redo generated by that transaction reflects the changes made to database blocks and they must be written to disk. | ||||
|
Number of transactions that were rolled back. Rollbacks occur either because of manually issuing a ROLLBACK command or in the event of an error. | ||||
|
Workload is also referred to as Active Time. Workload is a measurement of the Oracle instance's net processing time, measured in seconds. Workload is calculated by aggregating the net activity (non-idle time) for all Oracle processes. Performance Analysis considers an Oracle process active when it is either:
The Workload indication is used in various contexts to reflect application load; such contexts can be the overall Oracle instance load, individual session activity, program load over time, etc. and its interpretation may vary among the various totaling methods:
|