Oracle DB

Supported versions

NeoLoad supports most common Oracle Database server versions: 8i, 9i, 10g, 11g and 12c.

Connection settings

Oracle monitors allow monitoring Oracle database servers. The counters are sorted by category: memory, call rates, cache ratio, and so on.

This monitor executes SQL requests on the Oracle Views dedicated to performance monitoring: V$SYSSTAT, V$SYSTEM_EVENT, V$SGASTAT, V$SESSION, V$LIBRARYCACHE, V$LATCH, V$WAITSTAT, V$SQLAREA, V$VERSION.

To carry out the monitoring, NeoLoad requires an account with authorization to connect to the database and to read all these views.

NeoLoad monitors the Oracle server using the JDBC protocol:

• Classic edit mode: The JDBC driver is included in the NeoLoad software package and requires no particular installation or settings. The default connection port set by NeoLoad is 1521, the standard JDBC port.
• Advanced edit mode:
  • Using a SID: A specific SID connection provides extra access settings to the JDBC driver, such as timeout, cluster handling, and so on. The connection URL complies with the pattern jdbc:oracle:thin:@<host>:<port>:<SID>

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

  • Using a Service Name: The alias to an instance may be used to connect the Oracle database. The connection URL complies with the pattern jdbc:oracle:thin:@//<host>:<port>/<service_name>

Create an Oracle monitor

NeoLoad makes it possible to create a new monitor either using the monitored machine creation wizard, as described in Create and configure a monitored machine, or from an existing monitored machine, as described in Create and configure a monitor.

The Additional properties area makes it possible to set the number of SQL requests displayed in NeoLoad which use database resources the most—for all the indicators of the Top SQL Statements category.

Available counters

• Sessions. 
  • Active. The number of sessions currently executing SQL on the database. The System sessions are excluded.
  • % Active. The percentage of sessions currently executing SQL on the database compared to the total ones. The System sessions are excluded.
  • Inactive. The number of sessions pending on the database. The System sessions are excluded.
  • System. The number of sessions used by the System for management.
  • Idle. The number of sessions that have not executed SQL since the last time the performance Monitor looked at the database.
• Call Rates. 
  • Parse. 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.
  • Commit. 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.
  • Rollback. The calls per second for rollback. Rollbacks occur when users manually issue the ROLLBACK statement or an error occurs during a user's transactions.
• Miss Rates. 
  • Buffer Cache. 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.

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

    Information: Performance is optimum when this value is low.

    Information: Oracle parameters that can be modified to improve this statistic: DB_BLOCK_BUFFER.

  • SQL Area. Percentage of missed SQL cache. SQL shared pool reloads occur when Oracle has to implicitly re-parse SQL or PL/SQL at the point when it attempts to execute it.

    Tip: Larger shared pool will 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, the number of cursors permitted for a session can be also increased.

    Information: Performance is optimum when this value is low.

    Information: Oracle parameters that can be modified to improve this statistic: SHARED_POOL_SIZE, OPEN_CURSORS.

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

    Information: Performance is optimum when this value is low.

• Indexed Queries. 
  • Percentage. The percentage of requests to the database using indexed queries.

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

• Logical IO. 
  • Block Changes. 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.
  • Current Reads. The blocks per second for Current Reads. It counts the number of times a CURRENT block was requested per second.
  • Consistent Reads. 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.
• Physical IO. 
  • Datafile Reads. The 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.

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

  • Datafile Writes. The 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. The redo blocks written per second by LGWR (Log writer process) to the redo log files.
• 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 IO.
  • DB File IO.
  • Direct Path read.
  • Log File write.
  • SQL*Net.
  • Buffer busy.
• SGA Memory. These counters display 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.
• Miscellaneous. 
  • Direct Reads Ratio. The 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.
  • 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.

    Information: Performance is optimum when this value is high.

    Information: Oracle parameters that can be modified to improve this statistic: SHARED_POOL_SIZE, OPEN_CURSORS.

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

    Information: Performance is optimum when this value is high.

    Information: Oracle parameters that can be modified to improve this statistic: SHARED_POOL_SIZE, OPEN_CURSORS.

  • 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

    Information: Performance is optimum when this value is low.

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

    Information: Performance is optimum when this value is low.

    Information: Oracle parameters that can be modified to improve this statistic: 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.

    Information: Performance is optimum when this value is very low.

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

    Information: Performance is optimum when this value is very low.

  • Cursor Authentications. Number of privilege checks conducted during execution of an operation
  • Opened Cursors. Total number of current open cursors.
• 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.
  • CPU. SQL Statements that cause the highest CPU resource consumption.
  • Physical Reads. SQL Statements that cause the highest number disk reads.
  • Logical Reads. SQL Statements that hits the most the buffer.
  • Rows Processed. SQL Statements that causes the highest total number of rows processed.
  • Sorts. SQL Statements that causes the highest number of sorts that were done for all the child cursors.
  • Parse Calls. SQL Statements that causes the highest number of parse calls to all the child cursors under this parent.
  • Executed. SQL Statements that causes the highest number of executions, totaled over all the child cursors.
  • CPU per Execution. SQL Statements that cause the highest average of CPU resource consumption per execution of the statement.
  • Physical Reads per Execution. SQL Statements that cause 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 that causes 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 Components version. The version of the Oracle database.

Home