Best 13 Simple Tips to Performance Tuning in Oracle

This is related to simple tips for Performance Tuning in Oracle. But those tips may have a huge effect on big databases that have millions of records in a table. This article is the second post focusing on Performance Tuning in Oracle, but there are more articles to visit related to performance or the best Oracle keywords according to the situation. For your knowledge, I will list some of them below.

I want to note that performance can change according to the data load, behavior of the dataset, hardware of the machine, way of query writing, and many more factors. Here, just explain about Improving SQL Query Performance and Optimizing SQL Queries for Faster Performance.

Return the Data You Need

Out of Memory exception or reduce the memory once you fetch unwanted data from a select statement. This improves the performance, and examples below show a bad and good way of sql statements.

-- LESS AFFECTIVE 
SELECT * FROM customers
-- GOOD
SELECT customer_id, first_name, last_name FROM customers

Avoid DISTINCT Use GROUP BY

For Performance Tuning in Oracle, the DISTINCT requires sorting and FILTERING to remove duplicates. This means there can be a FULL TABLE SCAN, which can huge performance drawback for huge tables. Rather than using DISTINCT, we can use GROUP BY can be more efficient, special with indexes. If possible, avoid DISTINCT.

--
-- LESS AFFECTIVE 
SELECT DISTINCT department FROM employees;
-- GOOD
SELECT department FROM employees GROUP BY department;

--

Avoid PL/SQL Calls in WHERE Clauses

PL/SQL to SQL context switching is a big hit when you’re working with a huge database. If there are millions of records in the table, where condition is used to filter the dataset from the table.

In such a situation, context switching for each and every record is very costly. So, try to avoid PLSQL blocks in WHERE conditions.

--
-- LESS AFFECTIVE 
select id, date_time, value 
from a_table_view
where fn_change_timezone (date_time, 'UTC', 'US/Central') 
--

SELECT FROM DUAL or Assigning a variable

As you know, Dual is a one-column, one-row table created automatically. This table is mostly used to fetch SYSDATE, USER, HOSTNAME, Etc. This is a faster way to fetch a few records, but considering a big table takes more time than assigning variables in the first place.

Nb of executions	SELECT FROM DUAL	Assigning variable
100	1 ms	0 ms
1000	1 ms	0 ms
10000	8 ms	1 ms
100000	85 ms	14 ms
1000000	8.34 sec	1.28 sec

“SELECT FROM DUAL” is always slower than “Assigning variable.” You can access SYSDATE directly

-- 
-- LESS AFFECTIVE 
SELECT DEPARTMENT_ID ID, (SELECT SYSDATE FROM DUAL) TODAYS_DATE  FROM DEPARTMENTS
-- GOOD
SELECT DEPARTMENT_ID ID, SYSDATE TODAYS_DATE FROM DEPARTMENTS
--

Minimize the use of WILDCARD

Wildcard most of the time uses a FULL TABLE SCAN on queries. So, if possible, avoid wildcards in where statements. Also, the DOMAIN Index helps to improve the performance on wildcard rather than using of full table scan.

GOOD: Trailing Wildcards allow the database to use the index

-- GOOD
SELECT * FROM Customers
WHERE CustomerName LIKE 'abc%';
-- LESS AFFECTIVE 
SELECT * FROM Customers
WHERE CustomerName LIKE '%dfg%';
--

Use Indexes for Faster Retrieval

Performance Tuning in Oracle, Indexing is 99% of the time increases the performance on select queries. So use the Index on where condition nicely. Avoid the Index when there are a lot of modifications happening with the table. Other than that, indexes are getting better performance rather use of full table scans on huge tables.

This is not an article to explain everything related to indexes. But follow the articles below about Indexes, which explain that different types of indexes can be created and how to write queries to use the index.

Good

Index columns that are frequently used with the select query
Avoid creating an index on columns with low cardinality values.
Maintain index – reindex, and evaluate regularly on the indexes

Use appropriate data types

Data type helps to store the relevant values in a table. Different types of data are used to identify the value type. If a Numeric value saved in a TEXT type can decrease the performance. Below are the most commonly used data types and lengths for them.

We can save Numeric values in Varchar, but that is a problem when it is loading.

Data Type	Oracle 11g	Explanation
char(size)	2000 bytes.	Fixed-length string values
varchar2(size)	Maximum size of 4000 bytes.	number of characters Variable-length string values.
varchar2(size)	Maximum size of 32KB in PLSQL.	number of characters Variable-length string values.
long	Value length up to (4 gigabytes -1) * (the value of the CHUNK parameter of LOB storage).	Variable-length string values. (backward also compatible)
number(p,s)	Precision can range from 1 to 38.	p for precision and s for scale.
number(p,s)	Scale can range from -84 to 127.	Example, number(9,1) 8 digits before the decimal and 1 digits after the decimal point.
numeric(p,s)	Precision can range from 1 to 38.	p for precision and s for scale.
numeric(p,s)	Precision can range from 1 to 38.	Maximum size of 2 GB.
float
decimal(p,s)	Precision can range from 1 to 38.	p for precision and s for scale.
decimal(p,s)	Precision can range from 1 to 38.	Example, number(4,1), 3 digits before the decimal and 1 digit after the decimal point.
date	A date between Jan 1, 4712 BC and Dec 31, 9999 AD.
timestamp		contain year, month, day, hour, minute, and seconds.


blob	Value length up to (4 gigabytes -1) * (the value of the CHUNK parameter of LOB storage) of character data.	Fixed-length binary data. rowid is the physical address for a record,
clob	Example, number(4,1) 3 digits before the decimal and 1 digit after the decimal point.	This is single-byte and multi-byte character data.
rowid	Example rowid is: BBBBBBB.RRRR.FFFFF	RRRR is the row in the block.
	BBBBBBB is the block
	This is an unstructured binary large object.
	FFFFF is the database file.

Wildcard over Substring

If there is an index on the column and a wildcard can be used only end of the predicate, then better to use a wildcard over Substring. Because an index scan may happen with a wildcard.

--
-- GOOD
SELECT department_id FROM DEPARTMENTS WHERE department_name LIKE ‘CAPITAL%’;
-- LESS AFFECTIVE 
SELECT department_id FROM DEPARTMENTS WHERE SUBSTR(department_name,1,7)=‘CAPITAL’;
--

Remove unnecessary Calculations

WE can do some unnecessary calculations outside the query. Because that calculation may affect or be ignored index scan. Therefore, take the possible calculation outside from query.

Sometimes this query executes inside the loop, or there are millions of records in the big table. In such a scenario, this calculation might take some additional time for execution. Removing that removable code from a query helps Performance Tuning in Oracle.

--
-- GOOD 
SELECT department_id FROM DEPARTMENTS WHERE amount < 2000;
-- LESS AFFECTIVE 
 SELECT department_id FROM DEPARTMENTS WHERE (amount + 3000) < 5000;

Ignore Concatenations

Some Old queries or because of less knowledge about queries, some can write and add a condition with concatenation. The example below shows two columns concatenated and compared with columns. This is definitely ignoring the index scan.

Also, there is another operation needed to concatenate the two columns. Therefore, try to write smart queries to get the best performance.

-- GOOD 
SELECT department_id FROM DEPARTMENTS WHERE department_name = ‘AMEX’ AND department_type = ‘A’;
-- LESS AFFECTIVE
SELECT department_id FROM DEPARTMENTS WHERE department_name || department_type =‘AMEXA’;
--

Minimize table lookups

If we need to look up the same table multiple times to check a condition like below, then that is an unnecessary extra table lookup. The same query might need to compare different columns in the where condition. Once you are doing such different, make sure how indexes are created.

-- GOOD
SELECT emp_name FROM emp WHERE (emp_cat, emp_type) = (SELECT MAX(category), MAX(emp_type) FROM emp_details) AND emp_dept = 9000;
-- LESS AFFECTIVE
 SELECT emp_name FROM emp WHERE emp_cat=(SELECT MAX(category) FROM emp_details) AND emp_type = (SELECT MAX(emp_type) FROM emp_details) AND emp_dept = 9000; 
--

UNION vs. UNION ALL

The
Main Difference between UNION and UNION ALL is that UNION removes duplicates in the final result set, while UNION ALL preserves all rows, including duplicates, in the final results. Therefore, here we can eliminate sorting and filtering time if you are guaranteed that there are no duplicates in the results, or if you don’t care about duplicates.

-- GOOD
 SELECT department_id FROM DEPARTMENTS WHERE department_type = ‘A’;
 UNION ALL
 SELECT department_id FROM DEPARTMENTS WHERE department_type = ‘B’;

-- LESS AFFECTIVE

 SELECT department_id FROM DEPARTMENTS WHERE department_type = ‘A’;
 UNION
 SELECT department_id FROM DEPARTMENTS WHERE department_type = ‘B’;
--

Use TOP and LIMIT keywords if Possible

This is the last but not least tip for Performance Tuning in Oracle. TOP and LIMIT are used to fetch the first number of records. Showing all the results does not help every time.

Because showing millions of records in a single output is not readable to anyone. Therefore to to take a few records and if needed, take the next records using a query.

-- GOOD
SELECT department_id FROM DEPARTMENTS LIMIT 5;;
SELECT TOP  department_id FROM DEPARTMENTS  ;
-- LESS AFFECTIVE
SELECT department_id FROM DEPARTMENTS
--

I highlighted simple tips for Performance Tuning in Oracle that can easily improve some level of performance. But there are a lot hard to write in a single article.

Generally, I can say you can ignore sometimes below performance impact keywords sometimes, when needed.

NOT IN, !=
Like ‘%pattern’, not exists
Calculations on unindexed columns, or (use union instead)
Having (use a WHERE clause instead when appropriate)

Ref: LinkedIn

Best 13 Simple Tips to Performance Tuning in Oracle

Services

Resources

Contact