Oracle Performance Tuning

Oracle performance tuning is a very vast area. When we talk about Oracle performance tuning, we will come across many questions like what is tuning, why we need to tune and what is the importance of tuning, etc. In Oracle, we have many types of tuning like system tuning, SQL tuning, and more. System tuning consists of tuning instance, I/O subsystem tuning, etc.

‍

What Is Performance Tuning? Why Should You Do It?

Performance tuning is the process of administering a database to improve performance. Performance tuning in Oracle databases includes optimizing Instance and optimizing SQL statements.

‍

Poorly optimized SQL statements will force the database to work harder to retrieve information and resulting in more system resources usage. The more system resources are used the greater the chance it will affect the experience of users on connected applications.

‍

Users or developers will report a slow application to a database administrator who will then attempt to pinpoint the root cause of the problem. The administrator analyzes the code and searches for database bottlenecks. The process is extensive, as the administrator must diagnose the root cause of the problem before it can be addressed.

‍

Why Is Performance Tuning Important & Essential To Your Organization?

Performance tuning may involve database optimization, indexing to tune your SQL database, and making other efforts to make data retrieval easier and query responses timelier and more comprehensive.  

‍

An efficient database is one where data accessing and retrieving is fast. Below is a list of some common causes for the poor performance:

• Bad database design
• Poor server optimization
• Bad disk I/O configuration
• Poor optimizer statistics
• Improper initialization parameters
• Object contention
• Excessive nested loop joins

‍

Regular database monitoring and tuning will prevent these issues from creeping up and bog down the system. Typically, DBAs undertake performance tuning when the developers or users complain about slow queries.  

‍

The performance tuning process centers around four main steps:

• Identify: Pick the correct SQL statement to tune and avoid wasting your time.
• Gather: Gather the proper information that will help you make the best tuning decisions.
• Tune: Tune the SQL statement based on gathered information.
• Monitor: Ensure the SQL statement is tuned and stays tuned.

‍

We should note that there are two definitions of performance and goals which are very different and sometimes mutually exclusive: minimizing throughput and minimizing response time.

‍

Minimizing Throughput

This approach of Oracle tuning will maximize throughput and minimize the use of hardware resources. This approach is associated with the SQL Optimizer goal of all_rows

‍

Minimizing Response Time

This tuning approach is concerned with delivering data to the end users quickly, even if it means that the instance will consume additional I/O resources. It can take more I/O to use the index than to read a table via full table scan and this approach is associated with the SQL Optimizer goal of first_rows_n.

‍

Proactive Tuning Vs. Reactive Tuning

Proactive and reactive Oracle tuning modes are very different. Reactive Oracle tuning treats the symptoms, not the actual disease. Reactive tuning involves performance tuning and troubleshooting within boundaries of existing architecture. While there is a methodology associated with proactive Oracle tuning, reactive tuning usually happens during performance emergencies. In an emergency, the end users are complaining that Oracle is slow, and we must rely on tools like Oracle Enterprise manager, performance insight for cloud and diagnostic scripts to quickly diagnose and solve acute performance problems.

‍

Tuning aims to increase and optimize Oracle’s throughput. Time-based proactive tuning is a proven approach to long term success. In proactive tuning, the goal is to set a baseline for immutable database settings, finding the best tablespace, table settings and initialization parameters for each workload. Once the baselines and repeating patterns are identified, time-based tuning becomes easy. Many Oracle performance problems can be traced to external factors, some of which may be beyond our control.

‍

Proactive Tuning

• Planned
• Low time pressure
• No constraints on the methods and tools used
• No specific scope and target

‍

Reactive Tuning

• Cannot be planned
• High time pressure
• Analysis and tuning measures are constrained by dealing with a packaged application and live production data
• Scope usually limited to the problem at hand; target loosely defined maybe but always present

‍

As explained above, reactive tuning is almost always associated with high time pressure. It is therefore very important that the tuner has a clear and simple idea of how to address the problem.

‍

Oracle Tuning Hierarchy

Let’s look at the Oracle tuning Hierarchy as shown in the above pyramid diagram. A top-down approach to tune is very mandatory. This approach to Oracle tuning is very successful for running Oracle systems, whether they are large or small.‍

‍

Review the external environment: This will contain review of the server, disk and network environment to ascertain if hardware components are over stressed or under-utilized.‍

‍

Review the instance metrics: This is the review of specific events and metrics within AWR over a period to understand repeating patterns and exact nature of performance problems.

‍

Perform the Instance tuning: This is related to system level parameters that affect the behavior of the whole database. While designing the database we must make sure that we are setting initialization parameters properly.

‍

Perform the application tuning: Application tuning includes fixing design issues. We can use partitioning, bulk operations and pipelined functions. The very common application layer issue is a single online screen that makes multiple trips to Oracle to populate the screen values. By encapsulating the SQL into a package and consolidating the screen data into a single request, SQL *Net round trips are reduced, and response time is improved.

‍

Perform the object tuning: This is a review of specific wait events that are closely tied to data files, table spaces, tables and indexes. Object tuning improves the execution time of more SQL statements.  If the reorg routines are scripted and scheduled, they will keep your applications running quickly and help avoid locking and waits once implemented.  Object tuning allows you to get ahead of the curve, giving you time to elevate to the next level.

‍

Perform the SQL tuning:  In tuning there are lot of challenges particularly with SQL tuning.  Yes, SQL tuning is a very difficult challenge. To do it correctly you or the team members need to be very familiar with many aspects of the application. From a technical point of view, you need to understand the execution plans or how oracle is executing the SQL and how the data is being accessed. This is the most time consuming and intensive process of tuning tasks. There are a few dictionary tables that are used to extract suboptimal SQL and search for suboptimal table join order, unnecessary large table full scan or inefficient execution plan. With the help of tuning advisor, we can force to use hints, SQL profiles or baselines to alter execution plans.

‍

Conclusion

A poor database design will have an impact on performance in Oracle. DBAs routinely perform reactive tuning under enormous time pressure in live production environment. Proactive analysis and a top-down approach as shown in the pyramid diagram will make a database well designed and perform better which will make end users happy and less stress on DBA’s.