Oracle performance tuning best practices

Oracle, formally known as Oracle Database, is a leading Relational Database Management System (RDBMS). It rests at the heart of several modern and legacy IT infrastructures, enabling business operations like data storage, processing, reporting, and analytics.

In a database-driven architecture, it’s crucial to ensure that the database is always available and delivers peak performance. Administrators and developers should use recommended configurations and optimization techniques to avoid bottlenecks and disruptions.

In the following article, we will take a deep dive into Oracle Database’s performance tuning. We’ll discuss why it is important, explore bottlenecks and how to eliminate them, and share best practices for fine-tuning an Oracle database.

What is Oracle?

Oracle is a multi-model RDBMS that is widely used at the enterprise level. Whether you want to perform real-time transaction processing, build a data store for your analytics platform, or perform basic database tasks, Oracle does it all.

Oracle Corporation was the first vendor to release a commercial RDBMS in 1979. Today, it’s available in five different flavors: Standard Edition 1, Standard Edition 2, Enterprise Edition, Express Edition, and Personal Edition.

Oracle is a relational database system. It allows users to group related information as rows and columns and store it in structured tables. Users may also enforce uniqueness and integrity constraints to guarantee data accuracy.

Oracle is architected on top of Structured Query Language (SQL), the predominant language used to store, process, and fetch data from databases. A typical Oracle server consists of a listener, a database instance, and a database. The listener acts as the interface between clients and the database instance. The database instance represents the processing layer of the architecture. The database stores the data on the file system.

Oracle is a highly scalable database. It offers different features to scale infrastructures, both horizontally and vertically. Users can achieve virtual scaling by moving Oracle to resources with greater computing power. An Oracle instance can run inside containers, virtual machines, and large bare-metal servers. For horizontal scaling, you can choose from three approaches: database sharding, data replication, and compute clusters.

Oracle offers several features and guidelines to build a highly available database infrastructure. Use Oracle Real Application Clusters (RAC) to distribute a database across multiple nodes. Oracle Active Data Guard enables access to various security and data recovery features. Oracle Secure Backup can be used to automate the generation and protection of backups.

What is Oracle performance tuning, and why is it important?

Performance tuning refers to the configurations, best practices, and techniques used to enhance a database’s performance. Sometimes, performance tuning caters to specific business use cases, e.g., increasing thread count to allow more concurrent connections. Other times, it can be universal, e.g., caching.

For the smooth running of an IT infrastructure, it’s crucial to tune database performance for the following reasons:

Databases are the backbone of the larger system

A database is typically the primary source of truth for a system. It stores all the mission-critical data that different applications need to execute their business logic. In many ways, it acts as the backbone of the system.

Ensuring that the system’s backbone remains intact is the primary purpose of performance tuning. It defines the recommended configurations and querying techniques that allow a database to deliver optimal performance consistently. If these configurations and techniques aren’t applied, a database system can crumble under load.

Increase throughput

Some performance optimization techniques can directly lead to increased throughput. It’s natural for multiple applications to write to and read from a database simultaneously. Performance tuning ensures a database’s throughput isn’t significantly impacted, even during peak hours.

Build low-latency applications

In today’s fast-paced world, you can’t afford latency in data retrieval, storage, or processing. Building low-latency applications is crucial if you operate, for instance, in the financial, healthcare, or IoT space.

For example, in the mobile banking industry, you may need to run tens of SQL queries against a single transaction. All these queries must be executed in less than a second to ensure the best customer experience. Only a performance-tuned database can guarantee this.

Write better queries

Performance tuning includes coding practices and techniques to write better queries. No amount of configurational tweaks can enhance performance if queries are sub-optimal. For example, your database may be able to handle thousands of concurrent connections, but if one query creates a deadlock, all clients will suffer.

Spend less time resolving bottlenecks

A bottleneck represents a performance degradation that often affects multiple applications. It can be challenging for administrators to detect and resolve bottlenecks, especially in production environments. A poorly tuned database is highly susceptible to bottlenecks, which can threaten business continuity.

With appropriate performance-tuning practices in place, administrators can spend less time fixing bottlenecks and focus their energies on more productive tasks.

What causes bottlenecks, and how do we eliminate them?

• Coding errors: A logical error in a SQL query can lead to a bottleneck. You may, for example, write a query that applies joins on multiple tables in a loop. Such a query can cause the entire database to slow down.
• Misconfigurations: Bottlenecks can also occur because of misconfigurations. For example, an administrator may mistakenly configure a production instance with the settings of a staging instance. This may lead to several bottlenecks during peak hours.
• Poor connection management: If database clients continue to open new connections with the database without releasing any, it can overburden the database. An overburdened database may halt or slow down, resulting in a bottleneck.
• Less than necessary resources: A database running on a server with low hardware resources (less memory or CPU cores) may regularly cause bottlenecks. This is because the database instance may need more memory or CPU cycles to perform its operations efficiently.
• Not using indexes: Indexes are a great tool to optimize query performance. Unindexed tables can lead to long-running queries, which can cause performance bottlenecks.
• Poor database design: A poorly designed database will often run into bottlenecks. For example, if you store all your data inside one table, all applications will query the same table. This can lead to contentions and bottlenecks.

Bottlenecks can be the bane of an administrator’s existence. Here are a few tips to eliminate them:

• Use performance tuning: Use recommended tuning techniques (shared in the next section) to provision your infrastructure, configure your database, and write your queries. This will drastically reduce the number of bottlenecks.
• Use proactive monitoring: Proactive monitoring is an approach in which administrators monitor a database for early detection of bottlenecks. They track health and performance metrics to predict and avoid bottlenecks. For example, an administrator may see that too many requests are waiting in the database queue. This may indicate that the database is about to be unresponsive. Or they may notice that the average response time is slowly creeping up. This may also be an indicator of a database with degrading performance.
• Use database normalization: Use normalization to divide large tables into smaller, interlinked tables. This enhances scalability and performance and reduces the likelihood of contentions and bottlenecks. Moreover, strive to keep data duplication to a minimum. Other than primary keys, no data element should be stored more than once in a database.
• Perform capacity planning: Before deploying a database application, understand your capacity needs by performing load testing activities. Provision an infrastructure that enables your database to deliver peak performance.

Best practices for tuning Oracle Database performance

Use the following best practices to optimize the performance of your Oracle database:

Use indexes where applicable

In the database world, an index is a data structure that allows faster data retrieval. You can create an index on one or more columns of a table. An index stores data in a sorted order, which means that the database engine can apply binary searches to fetch data quickly.

Creating indexes on different table columns is a common performance-tuning technique. It decreases query execution time and improves overall database performance. It’s worth mentioning that indexes take up additional space on the file system, so use them cautiously.

Ensure that you are neither over-indexing nor under-indexing your database. Don’t index a table that receives excessive INSERT or UPDATE requests, as indexing can slow data writes. On the other hand, ensure that all your read-intensive tables are indexed.

Identify and optimize intensive queries and stored procedures

Another way to improve Oracle database performance is by identifying and optimizing long-running queries and stored procedures. You can, for example, use the following query to filter queries that have been running for more than two minutes:

select a.username,a.sid,a.last_call_et/120 mins_running,b.sql_text from v$session a
join v$sqltext_with_newlines b
on a.sql_address = b.address
where status='ACTIVE'
and type <>'BACKGROUND'
and last_call_et> 120
order by sid,b.piece

After you have identified the queries or stored procedures that are taking too long to execute, analyze them for optimization avenues. For example:

• If the queried table is unindexed, index it.
• If the queried table is too large, consider dividing it into smaller tables.
• If the query needs to access a large amount of data, consider parallelizing it.
• Don’t use SQL functions in WHERE clauses. If a function has a column name as an argument, the optimizer doesn’t use an index for that column.
• Review the query execution plan to ensure that the best execution strategy is being used.
• Don’t use a single SQL statement to perform more than one task. Instead, write a separate statement for each task.
• When writing subqueries, it is important to carefully choose between the "IN" and "EXISTS" keywords. As a general guideline, use "EXISTS" when the parent query contains the selective predicate, i.e. the condition that determines which rows to be included. On the other hand, use "IN" when the selective predicate is inside the subquery.
• When you want to fetch the first n rows of an ORDER BY query, use the FIRST_ROWS (n) hint to optimize response time.
• Defining local partitioned indexes on partitioned data can significantly boost query performance. A partitioned table contains multiple indexes. Results from each index are compiled to form the result. You can, for example, use the following query to create a local partitioned index:

CREATE INDEX my_index ON my_table (my_column)
INDEXTYPE IS ctxsys.context
PARAMETERS ('...')
LOCAL

Check out Site24x7's Oracle Cloud Infrastructure monitoring suite that ensures OCI-based environments are optimized for performance, security, and cost-efficiency, providing businesses with the necessary tools to maintain a high level of service reliability and operational agility in the cloud.

Use efficient connection pooling techniques

Connection pooling is a technique that allows database clients to maintain and use a pool of open connections with the database. Clients initialize a pool of database connections at startup. Whenever they need a connection, they obtain one from the pool and release it once they are done.

This significantly decreases the performance overhead of opening and closing connections, both at the application and database levels.

Minimize the amount of scanned data

While writing SQL queries, strive to minimize the amount of data to be scanned. Avoid full-table scans as much as possible. They utilize excessive memory and CPU resources. Use different SQL keywords to drill down on only the data you need. For example:

• Religiously use filters in your WHERE clause to limit the data that should be scanned.
• Avoid SELECT * queries as much as possible. Instead, SELECT only the columns that you need.
• Don’t join tables unless necessary. Remove any unused joins from your queries.
• Use equijoins wherever possible.

Use optimizer statistics

Optimizer statistics represent information related to different database objects, including tables. The Oracle Database engine uses these statistics to calculate the optimal execution plan of a query. It’s important to keep the statistics of a database table up to date with its current structure.

The recommended approach is to automate the collection of statistics using the Automated Maintenance Tasks Infrastructure (AutoTask) utility. The alternative is to generate statistics and keep them current manually. For example, use the following query to generate statistics on the table “SAMPLE”:

ANALYZE TABLE SAMPLE COMPUTE STATISTICS;

Modify or disable triggers

Triggers are a great way to perform tasks when certain conditions are met. However, when creating triggers, remember that they consume resources. Too many triggers can impact performance.

Use Attribute-clustered tables

Attribute clustering is a table-level directive that stores data close to each other on the file system, depending on specific column values. Attribute clustering only applies to bulk insert operations, like INSERT/*+APPEND*/. It’s not considered for traditional Data Manipulation Language (DML).

Storing data in this manner reduces I/O costs and increases performance. To specify a table’s attribute clustering during creation, use the CLUSTERING clause of the CREATE TABLE statement. To set it after creation, use the ALTER TABLE ... ADD CLUSTERING statement.

Use appropriate configurations

Oracle Database has several configuration settings that must be fine-tuned for maximum performance.

• SGA_TARGET: System Global Area (SGA) is a set of shared memory structures that govern various aspects of the database’s memory. SGA includes the database buffer cache, results cache, redo log buffer, and the streams pool. The SGA_TARGET setting represents the overall size of the SGA. When it is specified, Oracle automatically calculates the size of all SGA components. It’s important to set an optimal SGA_TARGET value to allow Oracle to perform efficient in-memory processing.
• PROCESSES: This parameter indicates the maximum number of processes that can simultaneously connect to the Oracle instance. This includes all the background and user processes. The number of background processes may differ based on the enabled features. Some features require one process, whereas others require multiple. To set the right value for this parameter, add the background and user processes you expect to run.
• SESSIONS: This setting indicates the maximum number of sessions the database instance can create. Oracle creates a new session for every login, which means that this setting essentially governs the maximum number of concurrent users. Set this value after carefully evaluating your concurrency needs. Oracle recommends the following formula to calculate an appropriate SESSIONS value:

  Estimated max concurrent users + number of background processes + an additional 10% for recursive sessions

• UNDO_MANAGEMENT: In Oracle terminology, “Undo” is a space reserved for storing data used to undo changes to a database. Among other things, undo records are used to roll back a transaction, recover the database, and provide read consistency. The accepted values for the UNDO_MANAGEMENT setting are AUTO and MANUAL. It’s highly recommended to use AUTO, which instructs Oracle to perform undo space management and tuning automatically. According to Oracle, the MANUAL mode is only supported for backward compatibility and should be avoided.
• Redo log file size: The redo log consists of two or more files that record all the changes made to a database instance. If an instance goes down, the redo log files can be used to recover it. The size of the redo log files can impact database performance. An adequately sized redo log file delivers better performance than an undersized one. Oracle doesn’t offer a specific size recommendation, but they do suggest a range of 100 MB to a few GBs. The wide range indicates that you should size your redo log file based on the amount of redo your database generates.
• Advanced table compression: Oracle’s advanced table compression allows you to reduce disk storage and increase performance.

Conclusion

Oracle is an enterprise-level RDBMS that has been supporting IT infrastructures for decades. It is multi-purpose, scalable, performant, fault-tolerant, and secure. The tuning best practices shared above can enable administrators and developers to leverage Oracle Database’s maximum potential.