Performance Counters for Oracle DB

/ Performance engineering / By

      Introduction

Oracle Corporation creates and sells Oracle Database (OracleDB), an object-relational database management system. Given that it is a multi-model database, it can support both the object-oriented and the object-relational models of data. Many of the biggest companies in the world across numerous industries use OracleDB, the most well-liked enterprise database.

The stability, scalability, flexibility, and security of OracleDB are preferred over those of alternative databases. The reliability and security of OracleDB’s database solutions have a long history, which is crucial for mission-critical applications. OracleDB is also renowned for its scalability, which refers to its simplicity in expanding to suit the requirements of any size organization. It is capable of handling a huge volume of users and data and is simple to modify to account for variations in data size, structure, and usage.

Organizations may manage their data with the help of several capabilities offered by OracleDB, such as built-in analytics, data integration, replication, and query optimization. Encryption, authentication, and access control are all built-in, which further increases its level of security. As a result of OracleDB’s exceptional flexibility, businesses may tailor their database solutions to meet their unique needs.

The performance and cheap total cost of ownership of OracleDB are additional selling points. It is intended to enable high levels of performance, and compared to other databases, its cost is frequently lower. With a wide range of tools to assist users in managing their databases, it is also simple to use.

Overall, OracleDB is a great option for businesses that require a secure, dependable, and scalable database. With a variety of capabilities that make it an excellent option for mission-critical applications, it appeals to businesses of all sizes because of its high performance and low total cost of ownership.

The Most Important Points of Oracle DB

  1. High Availability: The Oracle Database offers high availability via technologies like Real Application Clusters, Data Guard, and the Oracle Active Data Guard.
  2. Scalability: The features Oracle Partitioning, Oracle RAC, and Oracle Data Guard offer in Oracle Database allow for scaling.
  3. Security: Oracle Database’s security features, including Oracle Advanced Security, Oracle Database Vault, and Oracle Database Firewall, offer a stable and secure environment.
  4. Performance: To achieve outstanding performance, Oracle Database offers features such as Oracle Real Application Clusters (RAC), which enables the distribution of workload across multiple servers, resulting in enhanced scalability and improved performance.
  5. Manageability: Oracle Database provides a comprehensive set of tools. Oracle Enterprise Manager offers a centralized platform for monitoring, administration, and performance management of Oracle Database instances.

Performance Counters of Oracle DB

Sessions
  • Active: The number of active SQL sessions that are currently using the database. Sessions in the system are not included.
  • % Active: The proportion of sessions that are currently using the database to execute SQL queries. Sessions in the system are not included.
  • Inactive: The number of pending sessions in the database that are inactive. Sessions in the system are not included.
  • System: The total number of management sessions used by the system.
  • Idle: The number of sessions that have not run SQL since the performance monitor’s previous database scan.

Call Rates

  • Parse: The number of hard and soft Parse calls made each second. A soft parse is a check on an object that is already in the shared pool to make sure the underlying object’s permissions haven’t changed. Because Oracle must allocate a workshop and other memory structures before creating a parse tree, a hard parse is a memory-intensive operation.
  • Execute The number of user and recursive calls made per second to carry out SQL statements.
  • Commit: The number of commit calls made each second. The redo created that represents the changes made to database blocks must be written to disc when a user submits a transaction. The closest thing to a user transaction rate is frequent commits.
  • Rollback: The frequency of rollback calls. When users manually issue the ROLLBACK statement or when a problem happens during a user’s transactions, rollbacks take place.

Miss Rates

  • Buffer Cache: The percentage of the missing buffer cache. The proportion of requests for data that are not satisfied by data that is already in the buffer cache is measured by the buffer cache miss ratio. Lower ratios are preferable since IO operations to the disc are slower than accessing data that is in memory. The Oracle option DB_BLOCK_BUFFER can be changed to enhance this statistic.
  • SQL Area: Missing SQL cache percentage. When Oracle must implicitly re-parse SQL or PL/SQL before attempting to execute it, SQL shared pool reloads take place. Oracle settings that can be changed to enhance this statistic include OPEN_CURSORS and SHARED_POOL_SIZE.
  • latches: Percentage of latches obtained following a wait. Simple, low-level serialization mechanisms known as latches are used in the SGA to safeguard shared data structures. A process may need to wait and then retry when trying to obtain a latch.
    When this value is low, performance is at its best.

Indexed Queries

  • Percentage: The proportion of database requests made using indexed queries.
  • Information: It’s advised to set this ratio at 90 percent or higher. In a data-warehousing or decision-support system where full table scans are regularly performed, a lower value can be appropriate.

Logical IO

  • Block Changes: Block Changes’ blocks per second. This statistic tracks the total number of modifications made to all SGA blocks as a result of Update or Delete operations. If the transaction is committed, these changes produce redo log entries and hence become permanent changes to the database. This roughly represents all database work.
  • Current Reads: The number of blocks read each second. It keeps track of how many CURRENT blocks are requested per second.
  • Consistent Reads: Consistent Reads blocks per second. It keeps track of how frequently a consistent read for a block was asked for. The status of the data is always the same for transactions executing independently in a database, along with any modifications they make. When a transaction reads the same record again, it is said to be in a “consistent read” state. Unless it altered the data itself, it sees the same information. If a transaction operating by itself in a database examines every record in a table once, it will see the same number of records with the same contents the following time it reads the table, barring any changes the transaction makes on its own. By itself, write and read locks do not result in reliable reads.

Physical IO

  • Data file: Blocks of data read from the disc each second for a data file. This total includes all reads into the buffer cache as well as reads directly from the disc.
  • Information: To maximize transfer rates and prevent the early aging of shared data blocks residing in the buffer cache, reads of disc blocks skip the buffer cache in high bandwidth, data-intensive processes like parallel query.
  • Data file writes: Blocks of data written to the disc per second via data file writes. This sums up all writes from the buffer cache as well as writing made directly to the disc.
  • Redo Writes The number of redo blocks that the LGWR (Log writer process) writes to the redo log files each second.

Event Delays
These counters show the number of seconds spent waiting for a specific usage. The waits may last longer than a second and may even be parallel.

  • Manage File IO
  • SQL File IO
  • Straight Path read
  • Writable log file
  • SQL*Net
  • busy buffer

SGA Memory
These counters show how much SGA (System Global Area) memory has been allotted to the pools or buffers in kilobytes.

  • Stable EMS
  • Boundary Cache
  • A log buffer
  • Common Pool. how much free memory has been assigned to the shared pool?
  • Big pool. the size of the huge pool’s free memory allocation.
  • Pool of Java. the amount of RAM that is available and assigned to the Java pool.

Miscellaneous

  • Direct Reads Ratio: The ratio of direct physical reads to all direct reads is known as the direct reads ratio. For parallel scans and readings from temporary table areas, direct reads are used. Instead of reading blocks into the database buffer cache in the SGA, blocks are read directly into private buffers in the PGA. Because blocks are not looked up in the cache before being read, there are no cache hits. Additionally, since the blocks are simply thrown after usage rather than cached, there are no further cache hits.
  • 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.
    1. Performance is optimum when this value is high.
    2. Oracle parameters that can be modified to improve this statistic: SHARED_POOL_SIZE, OPEN_CURSORS.
  • Library Cache Pin Hit Ratio: the percentage of times that an effort to pin an item was successful since all the components were already stored in memory.
    • 1. When this value is high, performance is at its best.
    • 2. Oracle settings that can be changed to enhance this statistic include OPEN_CURSORS and SHARED_POOL_SIZE.
  • Recursive Calls Ratio: Performance is best when the ratio of recursive calls is minimal. A high recursive call-to-call ratio could mean any of the following:
    1. Dynamic table extension as a result of inadequate sizing
    2. Due to inappropriate OPTIMAL settings, rollback segments can grow and contract.
    3. large volumes of sorting to disc that generate and remove temporary segments
    4. Missing in the data dictionary
    5. Triggers, integrity restrictions, processes, functions, or complex packages
  • CPU Parse Overhead: Per The percentage of database CPU time used to analyze SQL and PL/SQL code is known as CPU analysis overhead. High values of this number suggest that the shared SQL area is too small or that the database uses a lot of one-time code.
    1. When this value is low, performance is optimal.
    2. SORT_AREA_SIZE is one of the Oracle parameters that can be changed to improve this metric.
  • Free list wrestling: Free list wrestling occurs when multiple processes try to insert data into the same table at the same time. One or more lock lists with space available for insertion are maintained in the header structure of the table. Some processes will have to wait to access a free list if there are more processes trying to insert than there are free lists.
    1. When this value is extremely low, performance is optimal.
  • Chained Fetch Ratio: This is the percentage of all rows that were retrieved and continued in a chained manner. This type of continuation means that the data in the row is split into two blocks, which can be done in two ways:
    1. Driving movement. This happens when a row update doesn’t fit in the current block. In this case, a pointer to the new place is left in the original block while the data in the row is moved to a new block.
    2. Make a row of chains. This happens when a row, for example, because there are many or large fields, does not fit in a single data block. In this case, the row is divided into two or more blocks.
      • When this value is extremely low, performance is optimal.
  • Cursor Authentications: Number of privilege checks performed while performing an operation using a cursor authentication
  • Open Cursors: The total number of active cursors.
  • Description: The textual description of the server.
    1. A version of the database components the version of the Oracle database.

Main SQL statements
Main SQL statements used to consume resources during the test. These counts are not visible in real-time while the test is running; rather, they are only accessible after testing. Not included are schemas based on SYS and SYSTEM, which are SQL requests issued by the system itself.

  • CPU: The SQL statements that consume the most CPU resources are.
  • Physical Reads – The SQL statements that result in the most disk reads are called physical reads.
  • Logical reading. SQL statements that hit most of the buffers are read logically.
  • Rows Processed – The SQL statements that result in the most processed rows are listed under Rows Processed.
  • Sort: SQL statements that resulted in most sorts being performed on all child cursors.
  • Parse calls: SQL statements that most frequently call all child cursors in this parent.
  • Executed: The SQL statements that result in the most executions when all child cursors are combined.
  • CPU Per Execution – SQL statements that, on average, require the most CPU resources per execution are classified as CPU per execution.
  • Physical Reads Per Execution – The SQL statements that result in the largest average number of disk reads per execution are called physical reads per execution.
  • logical reads per execution: SQL statements that on average reach the largest amount of buffering per execution have the highest logical reads per execution.
  • Executed: The SQL statements that result in the most executions when all child cursors are combined.
  • Rows Processed Per Execution – The SQL statements with the highest average number of rows processed per execution are listed under “Rows Processed Per Execution”.
  • Sort By Execution: The SQL statements that result in the largest average number of sorts for all child cursors, per execution, are called classifiers.
  • Parse calls per execution: The SQL statements with the highest average number of parse calls to all child cursors under this parent per execution are called parse calls.
Scroll to Top