Oracle DB Performance Counters

Introduction

Oracle Database (OracleDB) is an object-relational database management system produced and marketed by Oracle Corporation. It is a multi-model database, meaning it can support multiple data models, including the object-relational model and the object-oriented model. OracleDB is the world’s most popular enterprise database, used by many of the world’s largest organizations in various industries.

Oracle DB Performance Counters

OracleDB is chosen over other databases for its reliability, scalability, flexibility, and security. OracleDB has a long history of providing reliable and secure database solutions, which is extremely important in mission-critical applications. OracleDB is also known for its scalability, meaning it can easily scale to meet the needs of an organization, regardless of size. It can support a large number of users and data, and can easily be adjusted to accommodate changes in data size, structure, and usage.

OracleDB provides a range of features to help organizations manage their data, including built-in analytics, data integration, data replication, and query optimization. It is also highly secure, with encryption, authentication, and access control built-in. OracleDB is also highly flexible, allowing organizations to customize their database solutions to fit their specific requirements.

OracleDB is also known for its performance and low total cost of ownership. It is designed to support high levels of performance, and its cost is often lower than that of other databases. It is also easy to use, with a comprehensive set of tools to help users manage their databases.

Overall, OracleDB is an excellent choice for organizations in need of a secure, reliable, and scalable database. It offers a range of features that make it a great choice for mission-critical applications, and its low total cost of ownership and high performance make it an attractive choice for companies of all sizes.

Key Features of Oracle DB

  1. High Availability: Oracle Database provides high availability through features such as Real Application Clusters, Data Guard and the Oracle Active Data Guard.
  2. Scalability: Oracle Database provides scalability with features such as Oracle Partitioning, Oracle RAC, and Oracle Data Guard.
  3. Security: Oracle Database provides a secure and robust environment through features such as Oracle Advanced Security, Oracle Database Vault, and Oracle Database Firewall.
  4. Performance: Oracle Database provides high performance through features such as Oracle Real Application Clusters, Oracle In-Memory Database Cache, and Oracle Database In-Memory and Oracle Database Result Cache.
  5. Manageability: Oracle Database provides manageability through features such as Oracle Enterprise Manager, Oracle Database Configuration Assistant, Oracle Database Configuration Advisor, and Oracle Database Tuning Advisor.

Performance Counters of Oracle DB

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 the 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. 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. 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.
    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 was 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 the 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 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 the 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 ageing 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. The amount of free memory which is allocated to the shared pool.
  • Large Pool. The amount of free memory which is allocated to the large pool.
  • Java Pool. The amount of free memory which is 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.
    • Performance is optimum when this value is high.
    • 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.
    • Performance is optimum when this value is high.
    • Oracle parameters that can be modified to improve this statistic: SHARED_POOL_SIZE, OPEN_CURSORS.
  • Recursive Calls Ratio: Performance is optimum when this value is low. 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 the creation and deletion of temporary segments
    • Data dictionary misses
    • Complex triggers, integrity constraints, procedures, functions and/or packages
  • 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.
    • Performance is optimum when this value is low.
    • 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.
    • 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.
      • Performance is optimum when this value is very low.
  • Cursor Authentications: Number of privilege checks conducted during the execution of an operation
  • Opened Cursors: Total number of current open cursors.
  • Description: Textual description of the server.
    • Database Components version: The version of the Oracle database.

Top SQL Statements

Top SQL Statements related to 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 of disk reads.
  • Logical Reads: SQL Statements that hits the most buffer.
  • Rows Processed: SQL Statements that cause the highest total number of rows processed.
  • Sorts: SQL Statements that cause the highest number of sorts that were done for all the child cursors.
  • Parse Calls: SQL Statements that cause the highest number of parse calls to all the child cursors under this parent.
  • Executed: SQL Statements that cause the highest number of executions, totalled 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 buffer per execution.
  • Rows Processed per Execution: SQL Statements that cause the highest average number of rows processed per execution.
  • Sorts per Execution: SQL Statements that cause the highest average of sorts that were done for all the child cursors, per execution.
  • Parse Calls per Execution: SQL Statements that cause the highest average of parse calls to all the child cursors under this parent, per execution.


You may be interested:


Leave a Comment