Oracle Performance Tuning: Root Cause, Problems, And Solutions

The poorly designed database is quite dangerous! It impacts the speed, slows down queries, and affects the user’s productivity. If the issue of database performance is not identified, quality of service is sure to be compromised.

The only way to ensure that the database functions speedily without any hurdles is performing: Oracle Performing Tuning.

Implementing performance tuning on your database is one of the most common and yet the most important activity that every database administrator (DBA) must perform.

Let’s have a glance at Oracle Performance Tuning.

What Is Performance Tuning In Oracle?

In a simple manner, Performance Tuning in Oracle or Oracle Performance Tuning is a practice to improve SQL queries in a way it runs as fast as possible and streamlines the database performance.

No matter how complex SQL statements are, Oracle performance Tuning simplifies the process and improves the response times. The goal is to meet specific, measurable, and achievable goals.

Performance Tuning in Oracle is done by finding significant bottlenecks and addressing them with suitable changes to reduce performance degradation.

However, several issues can affect server performance. Let’s see some of them.

Database Server Performance Troubleshooting – Common Causes of Issues

Monitoring SQL Server performance is a complex task, as performance depends on many hardware and software parameters.

Some of the common performance degradation issues include:

• When the size of the database grows, it will need more memory to process the data from the tables. The increasing size of the existing table can impact server performance.
• Complex queries put the biggest impact on database performance. Finding out which application needs heavy queries on your database can help to diagnose the underlying issue.
• Indexed content can generally get scattered when data is modified, deleted, or removed. This fragmented database can result in performance degradation.
• Hardware performance of the server like memory, cache, and paging impacts the most on database performance. Thus it should be monitored in order to reduce the bottlenecks or slowdowns.

Inline to optimize the oracle database, the right operations are necessary to improve end-user’s productivity.

Let’s understand that in a simple way.

Oracle Performance Tuning: Operations Performed to Improve Performance

Basic operations to be performed are:

• DML
• DQL

While performing DML if a table is to be loaded with huge data, you can get good performance with BULK operation.
For Instance: BULK insert – Use optimizer hint ‘APPEND’ and ‘NO LOGGING’, if redo buffer information is not required. This makes the insert operation faster.

But the major performance hazard is a BAD SELECT Query.

Most of the time DBA’s create an index to speed up the execution, but they have limitations to create an index for all sorts of scenarios. If the number of indexes are more than it will impact performance during DML operations.

In such a scenario, there is a need to find a way to write a query in a format that helps speed up the execution and provide a fast response.

Application performance can be defined in terms of throughput/efficiency. To get high throughput and avoid poor performance DBA’s must write the code in a way to:

1. Avoid overhead on CPU
2. Avoid overhead on Memory

To avoid CPU Overhead it is necessary to have efficient SQL statements:

• Check for appropriate indexes on the table
• Statistics are up-to-date (Gather Stats timely)
• Analyze the performance of SQL statements using the explain plan or trace facility and then rewrite the Query if necessary.
• Implement BULK binding (FORALL and BULK COLLECT), in a situation where a table has a huge amount of data and there is frequent SQL statement execution from a PL/SQL block (i.e. LOOP)
• Short-circuit evaluation whenever there is a logical expression or multiple conditions separated by AND or OR, always use the least expensive one at first as PL/SQL stops evaluation as soon as the result can be determined. This applies to all extraction clauses including DECODE, CASE, etc

DECLARE
var1 INTEGER := 0;
var2 INTEGER := 100;
BEGIN
IF (var1 = 0) OR ((var2 / var1) < 5) THEN
DBMS_OUTPUT.PUT_LINE(' Var1 value is 0');
END IF;
END;

• Nesting a Query to Improve Performance
  Whenever required reframe the query in a way that inner query filters the results to a small number of rows, and then outer query calls the function only a few times.

FOR i IN (SELECT DISTINCT(UPPER(cust_name)) name FROM customer)
LOOP
DBMS_OUTPUT.PUT_LINE(i.name);
END LOOP;


// Better performance
FOR i IN ( SELECT UPPER(cust_name) name FROM ( SELECT DISTINCT cust_name FROM
customer)) LOOP
DBMS_OUTPUT.PUT_LINE(i.name);
END LOOP;

• Choose data types carefully while creating a table to minimize implicit conversions. e.g. VARCHAR to NUMBER conversion.
• If in joining condition one column is having CHAR datatype and corresponding joining column is having VARCHAR2 datatype then use PAD function to improve query performance. Using of TRIM function, slow down the query performance.
• Avoid having complex queries manipulation into HAVING Clause
  Rather make an inline view and do all manipulation in it and make a comparison against manipulated value.

SELECT SUM(total_amt),order_id,route_id
FROM orders
WHERE country_code = '302'
GROUP BY order_id,route_id
HAVING SUM(total_amt) < (SELECT MIN(amount)
FROM inv_order
WHERE country_code = '302'
AND inv_order_id = order_id
AND inv_route_id = route_id
GROUP BY inv_order_id,inv_route_id );


Better to have it like,
SELECT sum_amt,order_id,route_id
FROM (SELECT SUM(total_amt) sum_amt,order_id,route_id
FROM orders
WHERE country_code = '302'
GROUP BY order_id,route_id) x
,(SELECT MIN(amount) min_amt ,inv_order_id ,inv_route_id
FROM inv_order
WHERE country_code = '302'
GROUP BY inv_order_id,inv_route_id ) y
WHERE inv_order_id = order_id
AND inv_route_id = route_id
AND sum_amt < min_amt;


• Avoid adding unnecessary conditions to where clause
  Adding extra joins on columns helps improves the performance only if the column is part of index being used. Having joins on unnecessary columns doesn’t always help get the data faster.
• WITH clause and MATERIALIZE hint WITH clause enables us to write our logic as a block and the data extracted in this block act as a temporary table and is available in the cache. So next time when the query is referenced, oracle doesn’t need to execute it rather it directly refers the data extracted earlier. Thus, adds on to performance improvement.  Always try to create a block of ‘WITH’ for the query, if the query result is to reference again n again.  Adding MATERIALIZE Hint to the select statement of WITH block gives another advantage and helps fast retrieval of data.

WITH order_data
AS
( SELECT /*+ MATERIALIZE */ order_id
FROM orders
WHERE country_code = '302')
SELECT inv_route_id,amount
FROM inv_order,order_data
WHERE inv_order_id = order_id;


• Do not use DISTINCT and UNION unless necessary.
  These operators cause sorting which adds unnecessary overhead. Use UNION ALL instead of UNION where ever possible.
• Do not use outer join unless necessary. Try to make inner join instead of outer join if possible.
• Avoid fetching unnecessary columns in SELECT clause. It places extra loads on the database.
• Use of wildcard (%) should be avoided at the beginning of predicates
  e.g. LIKE ‘%AL’ causes full table scan.
• Avoid using functions (like UPPER , LOWER) in predicates. Rather check for correct data format and compare with the value if possible. e.g. UPPER(t.name)=UPPER(t1.ename)
  This adds the extra overhead of conversion and then comparison, adding to performance degradation.

To avoid Memory Overhead:

• Be Generous When declaring Sizes for VARCHAR2 Variables-

We can actually conserve memory by declaring VARCHAR2 variables with large sizes. PLSQL allocates as much storage as needed after the value being assigned.

• Group Related Subprograms into Packages-

This is always beneficial to call a packaged subprogram instead of standalone.

• Pin Packages in the Shared Memory Pool-

Frequently accessed packages can be pinned in the shared memory pool, using the DBMS_SHARED_POOL package. This lets the package remain in memory no matter how full.

Implementing the above-mentioned operation, analyzing the database server’s performance, and monitoring their uptime helps the database server to run smoothly and efficiently.

Done using appropriate tools can improve the end user’s experience.

Top Tools For Oracle Database Performance Analysis

• Solar Winds Database Performance Analyzer
• Idera Diagnostic Manager
• Manage Engine Application Manager
• eG Enterprise
• Idera Precise Platform

However, the list is infinite!

Organizations that use these tools can avoid performance degradation and minimize the amount of time required to resolve issues.

Are you still confused?

To optimize the performance problem, you need to find queries that impact the most. The goal is to address the issues by understanding the oracle database stack.

Does your database have performance issues? Do you want to know more about it?

Connect with our experts to know how we follow the process of Performance Tuning in Oracle