Skip to content

Oracle Database

OracleDB Logo

Visualize Oracle Database performance metrics in real-time during load tests. OctoPerf lets you visualize and analyze advanced metrics for your Oracle databases:

  • Capture and analyze Oracle DB metrics and statistics,
  • Correlate Oracle DB metrics with your other systems,
  • Create effective alerts on database throughput, latency, or errors.

Oracle DB is the leading enterprise database being used by most of the Fortune 500 companies.

Supported versions

We support Oracle Database server versions: 8i, 9i, 10g, 11g and 12c.

Requirements

A dedicated Oracle Database account is required with the rights to execute SQL requests on the Oracle Views dedicated to performance monitoring:

  • V$SYSSTAT,
  • V$SYSTEM_EVENT,
  • V$SGASTAT,
  • V$SESSION,
  • V$ROWCACHE,
  • V$LIBRARYCACHE,
  • V$LATCH,
  • V$LATCHNAME,
  • V$WAITSTAT,
  • V$SQLAREA,
  • And V$VERSION.

Configuration

The Oracle Database monitor can be configured in two different ways. The simple mode suits best for most configurations. The advanced mode is great for specifying advanced connection settings.

The database name is required unless no specific database is monitored. This monitor executes SQL queries to get system and status variables. OctoPerf requires an account with authorization:

  • to connect to the database,
  • executes SQL requests on the Oracle Views dedicated to performance monitoring.

Simple Configuration

  • Hostname: hostname or IP of the Oracle Database database,
  • Port: database connection port, 1521 by default,
  • Database: database to connect to.

In simple configuration, the JDBC connection url is generated automatically.

Advanced Configuration

  • URL: JDBC connection url.

In advanced configuration, a specific OracleDB JDBC connection URL with extra connection settings to the JDBC driver, such as timeout, cluster handling can be configured.

Example: jdbc:oracle:thin:@(description=(address_list=(address=(protocol=TCP)(port=1521)(host=magnum))(source_route=yes)(connect_data=(INSTANCE_NAME=PETSTORE)))

Credentials

OracleDB username and password are required in both simple and advanced configuration.

Monitored Counters

The OracleDB monitoring module collects the following metrics:

Call Rates

  • Parses/sec: The calls per second for Parse (hard and soft). A soft parse is a check on an object already in the shared pool, to verify that the permissions on the underlying object have not changed. A hard parse is a very expensive operation in terms of memory use, because it requires Oracle to allocate a workheap and other memory structures and then build a parse tree,

  • Execute: The calls (user and recursive) per second that executed SQL statements,

  • Commits/sec: The calls per second for a commit. 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,

  • Rollbacks/sec: The calls per second for rollback. Rollbacks occur when users manually issue the ROLLBACK statement or an error occurs during a user's transactions,

Event Waits: These counters display the seconds waited per second on a particular usage. The waits can be parallel and this number may exceed 1 second.

  • Control File Parallel Writes,

  • Control File Sequential Reads,

  • Direct Path Read,

  • Log Parallel File Write,

  • SQLNet Message From Client,

  • SQLNet Message To Client,

  • Buffer Busy Waits.

SGA Memory: These counters return the size in kilobytes of SGA (System Global Area) memory allocated to the pools or buffers.

  • Fixed SGA,

  • Buffer Cache,

  • Log Buffer,

  • Shared Pool: Free memory allocated to the shared pool,

  • Large Pool: Free memory allocated to the large pool,

  • Java Pool: Free memory allocated to the Java pool.

Hit Rates

  • Buffer Cache Hit: Percentage of missed buffer cache. The buffer cache miss ratio is a measure of the proportion of requests for data which is unsatisfied by data already in the buffer cache. Lower ratios are better as access to data in memory is speedier than an IO operation to disk,

  • Willing to Wait Latch Gets: Percentage of latch get after a wait. Latches are simple, low-level serialization mechanisms to protect shared data structures in the SGA. When attempting to get a latch, a process may have to wait and then retry,

Indexed Queries

  • % Index Use: The percentage of requests to the database using indexed queries,

Logical IO

  • DB Block Changes/sec: The blocks per second for Block Changes. This statistic counts the total number of changes that were part of an update or delete operation that were made to all blocks in the SGA. Such changes generate redo log entries and hence become permanent changes to the database if the transaction is committed. This approximates total database work,

  • DB Block Gets/sec: The blocks per second for Current Reads. It counts the number of times a CURRENT block was requested per second,

  • DB Consistent Reads/sec: The blocks per second for Consistent Reads. It counts the number of times a consistent read was requested for a block. Transactions running alone in a database always see the same state of data, plus any changes they make themselves. That state is called "consistent read" if a transaction reads the same record twice. It sees the same data unless it changed the data itself. If a transaction running alone in a database reads all the records in a table once, it will see exactly the same number of records with the same contents the next time it reads the table, give or take changes it makes itself. Write and read locks alone do not produce consistent reads,

Miscellaneous

  • Direct Reads Ratio: Ratio of direct physical reads compared to all the direct reads. Direct reads are performed for parallel scans, and reads from temporary table spaces. Blocks are read directly into private buffers in the PGA, rather than into the database buffer cache in the SGA. There are no cache hits, because blocks are not searched for in the cache before being read. And there are no subsequent cache hits, because the blocks are just discarded after use, rather than cached,

  • Dictionary Cache Hit Ratio: The dictionary cache hit ratio is a measure of the proportion of requests for information from the data dictionary, the collection of database tables and views containing reference information about the database, its structures, and its users,

  • Library Cache Get Hit Ratio: The proportion of requests for a lock on an object which were satisfied by finding that object handle already in memory,

  • Library Cache Pin Hit Ratio: The proportion of attempts to pin an object which were satisfied by finding all the pieces of that object already in memory,

  • Shared Pool Free Ratio: The percentage of the shared pool not currently in use,

  • Recursive Calls Ratio: Ratio of recursive calls to total calls.

  • CPU Parse Overhead: The CPU parse overhead is the proportion of database CPU time being spent in parsing SQL and PL/SQL code. High values of this figure indicate that either a large amount of once-only code is being used by the database or that the shared SQL area is too small.

  • Free List Contention: Free list contention occurs when more than one process is attempting to insert data into a given table. The table header structure maintains one or more lists of blocks which have free space for insertion. If more processes are attempting to make insert than there are free lists some will have to wait for access to a free list.

  • Chained Fetch Ratio: This is a proportion of all rows fetched which resulted in a chained row continuation. Such a continuation means that data for the row is spread across two blocks, which can occur in either of two ways:

    • Row Migration: This occurs when an update to a row cannot fit within the current block. In this case, the data for the row is migrated to a new block leaving a pointer to the new location in the original block,

    • Row Chaining: This occurs when a row cannot fit into a single data block, e.g. due to having large or many fields. In this case, the row is spread over two or more blocks.

  • Sorts In Memory: This is a measure of the proportion of data sorts which occur within memory rather than on disk,

  • Shared Pool Reloads: This is similar to a Library Cache Miss Ratio, but is specific to SQL and PL/SQL blocks,

  • Rollback Segment Contention Ratio: This figure is an indication of whether a process had to wait to get access to a rollback segment,

  • Short/Total Table Scans: This is the proportion of full table scans which are occurring on short tables,

  • Immediate Latch Gets: % of attempts to obtain a latch which a process is not allowed to wait for that will timeout,

  • Cursor Authentications: Number of privilege checks conducted during execution of an operation

  • Opened Cursors: Total number of current open cursors,

Physical IO

  • Physical Reads/sec: Blocks of data read from the disk per second. This number equals the value of reads directly from the disk plus all reads into buffer cache,

  • Physical Writes/sec: Blocks of data written to the disk per second. This number equals the value of writes directly to the disk plus all writes from buffer cache,

  • Redo Writes/sec: Redo blocks written per second by LGWR (Log writer process) to the redo log files.

Redo Activity

  • Redo Space Wait Ratio: This ratio gives an indication of the level of contention occuring for redos,

  • Redo Allocation Latch Ratio: The redo allocation latch controls the allocation of space for redo entries in the redo log buffer. To allocate space in the buffer, an Oracle user process must obtain the redo allocation latch. Since there is only one redo allocation latch, only one user process can allocate space in the buffer at a time. The single redo allocation latch enforces the sequential nature of the entries in the buffer,

  • Redo Copy Latches Ratio: The user process first obtains the copy latch. Then it obtains the allocation latch, performs allocation, and releases the allocation latch,

Sessions

  • Active Sessions: sessions currently executing SQL on the database. The System sessions are excluded,

  • % Active Sessions: % of sessions currently executing SQL on the database compared to the total ones. The System sessions are excluded,

  • Inactive Sessions: Sessions pending on the database. The System sessions are excluded,

  • System Sessions: Sessions used by the System for management,

  • Killed Sessions: Sessions marked to be killed,

  • Cached Sessions: Sessions temporarily cached for use by Oracle XA,

  • Sniped Sessions: Inactive session, waiting on the client,

Top SQL Statements: Top SQL Statements related to a resource consumption during the test. These counters are only available at the end of the test, not in real-time while the test is running. SQL requests made by the system itself (SYS and SYSTEM based schemas) are excluded,

  • Top CPU: SQL Statements with the highest CPU resource consumption,

  • Physical Reads: SQL Statements with the highest number of physical reads,

  • Logical Reads: SQL Statements that hits the most the buffer,

  • Rows Processed: SQL Statements that with the highest total number of rows processed,

  • Sorts: SQL Statements with highest number of sorts that were done for all the child cursors.

  • Parse Calls: SQL Statements with the highest number of parse calls to all the child cursors under this parent,

  • Executed: SQL Statements with the highest number of executions, totaled over all the child cursors,

  • CPU per Execution: SQL Statements with the highest average of CPU resource consumption per execution of the statement.

  • Physical Reads per Execution: SQL Statements with the highest average of disk reads per execution. Logical Reads per Execution. SQL Statements that hits, in average, the most the buffer per execution.

  • Rows Processed per Execution: SQL Statements with the highest average number of rows processed per execution.

  • Sorts per Execution: SQL Statements that causes the highest average of sorts that were done for all the child cursors, per execution.

  • Parse Calls per Execution: SQL Statements that causes the highest average of parse calls to all the child cursors under this parent, per execution.

Description: Textual description about the server.

  • Database version: The version of the Oracle database.

Going Further

Buffer Cache Hit Ratio

The buffer cache hit ratio is a measure of the proportion of requests for data which is satisfied by data already in the buffer cache. Higher ratios are better as access to data in memory is speedier than an IO operation to disk. There comes a point of diminishing returns when increasing the size of the database buffer. Also, remember that this is part of the SGA and it may be more important to use additional memory for other parts of the SGA. It is vital that the whole SGA fits within main memory, as paging of the SGA is disastrous for performance.

Optimum init.ora parameter
High DB_BLOCK_BUFFERS

Dictionary Cache Hit Ratio

On instance startup, the data dictionary cache contains no data, so any SQL statement issued is likely to result in cache misses. As more data is read into the cache, the likelihood of cache misses should decrease. Eventually the database should reach a "steady state" in which the most frequently used dictionary data is in the cache.

The dictionary cache resides within the Shared Pool, part of the SGA, so increasing the shared pool size should improve the dictionary cache hit ratio.

Optimum init.ora parameter
High SHARED_POOL_SIZE

Sorts In Memory

This is a measure of the proportion of data sorts which occur within memory rather than on disk. Sorts on disk make use of the user's temporary table space. The maximum size of sort which will occur in memory is defined by the sort area size, which is the size within the PGA which will be used. Each Oracle process which sorts will allocate this much memory, though it may not need all of it. Use of memory for this purpose reduces that available to the SGA.

Optimum init.ora parameter
High SORT_AREA_SIZE

Shared Pool Free

The percentage of the shared pool not currently in use. If a large proportion of the shared pool is always free, it is likely that the size of the shared pool can be reduced. Low free values are not a cause for concern unless other factors also indicate problems, e.g. a poor dictionary cache hit ratio or large proportion of reloads occurring.

Optimum init.ora parameter
Small but non-zero SHARED_POOL_SIZE

Shared Pool Reloads

Shared pool reloads occur when Oracle has to implicitly reparse SQL or PL/SQL at the point when it attempts to execute it. A larger shared pool wil reduce the number of times that code needs to be reloaded. Also, ensuring that similar pieces of SQL are written identically will increase sharing of code.

To take advantage of additional memory available for shared SQL areas, you may also need to increase the number of cursors permitted for a session. You can do this by increasing the value of the initialization parameter OPEN_CURSORS.

Optimum init.ora parameter
Low SHARED_POOL_SIZE, OPEN_CURSORS

Willing To Wait Latch Gets

An attempt by a process to obtain a latch which is willing to wait will sleep and retry until it obtains the latch.

Optimum init.ora parameter
High None

% Index Use

A value of 90 percent or higher for this ratio is recommended. A lower value might be acceptable in a data-warehousing or decision-support system where the full table scans are frequently used.

Optimum init.ora parameter
High None

Datafile Reads

In high bandwidth, data-intensive operations such as parallel query, reads of disk blocks bypass the buffer cache to maximize transfer rates and to prevent the premature aging of shared data blocks resident in the buffer cache.

Library Cache Get Hit Ratio

The proportion of requests for a lock on an object which were satisfied by finding that object's handle already in memory.

Optimum init.ora parameter
High SHARED_POOL_SIZE, OPEN_CURSORS

Library Cache Get Hit Ratio

The proportion of attempts to pin an object which were satisfied by finding all the pieces of that object already in memory.

Optimum init.ora parameter
High SHARED_POOL_SIZE, OPEN_CURSORS

CPU Parse Overhead

The CPU parse overhead is the proportion of database CPU time being spent in parsing SQL and PL/SQL code. High values of this figure indicate that either a large amount of once-only code is being used by the database or that the shared sql area is too small.

Optimum init.ora parameter
Low SORT_AREA_SIZE

Free List Contention

Free list contention occurs when more than one process is attempting to insert data into a given table. The table header structure maintains one or more lists of blocks which have free space for insertion. If more processes are attempting to make insert than there are free lists some will have to wait for access to a free list.

Optimum init.ora parameter
Very Low None

Chained Fetch Ratio

This is a proportion of all rows fetched which resulted in a chained row continuation.

Optimum init.ora parameter
Very Low None

Rollback Segment Contention Ratio

This figure is an indication of whether a process had to wait to get access to a rollback segment. To improve figures, increase the number of rollback segments available.

Optimum init.ora parameter
Low None

Recursive Calls Ratio

A high ratio of recursive calls to total calls may indicate any of the following:

  • Dynamic extension of tables due to poor sizing
  • Growing and shrinking of rollback segments due to unsuitable OPTIMAL settings
  • Large amounts of sort to disk resulting in creation and deletion of temporary segments
  • Data dictionary misses
  • Complex triggers, integrity constraints, procedures, functions and/or packages
Optimum init.ora parameter
Low None

Short/Total Table Scans

Short tables may be scanned by Oracle when this is quicker than using an index. Full table scans of long tables is generally bad for overall performance. Low figures for this graph may indicate lack of indexes on large tables or poorly written SQL which fails to use existing indexes or is returning a large percentage of the table.

Optimum init.ora parameter
High None

Redo Space Wait Ratio

A redo space wait is when there is insufficient space in the redo buffer for a transaction to write redo information. It is an indication that the redo buffer is too small given the rate of transactions occurring in relation to the rate at which the log writer is writing data to the redo logs.

Optimum init.ora parameter
Very Low LOG_BUFFER

Redo Copy Latches Ratio

The user process first obtains the copy latch. Then it obtains the allocation latch, performs allocation, and releases the allocation latch. Next the process performs the copy under the copy latch, and releases the copy latch. The allocation latch is thus held for only a very short period of time, as the user process does not try to obtain the copy latch while holding the allocation latch. If the redo entry is too large to copy on the redo allocation latch, the user process must obtain a redo copy latch before copying the entry into the buffer. While holding a redo copy latch, the user process copies the redo entry into its allocated space in the buffer and then releases the redo copy latch.

With multiple CPUs the redo log buffer can have multiple redo copy latches. These allow multiple processes to copy entries to the redo log buffer concurrently. The number of redo copy latches is determined by the parameter LOG_SIMULTANEOUS_COPIES.

Optimum init.ora parameter
Very Low LOG_SMALL_ENTRY_MAX_SIZE, LOG_SIMULTANEOUS_COPIES

Immediate Latch Gets

An attempt to obtain a latch which a process is not allowed to wait for will timeout.

Optimum init.ora parameter
High None