Getting better performance is depending on different conditions. But performance tuning in oracle Is one major condition. Hardware problem, Network problem, Configuration problem and bad Coding problems also hit the performance of the Application. Here we hope to discuss 10 different ways of performance tuning in Oracle.
Oracle optimizes things from their end and gives a lot of improvement through the optimizer, engine, statistics handling, and a lot of other ways. Here we are not discussing those, but we can improve our coding when writing Queries, making tables, comparing different keywords, or thinking of the best keyword according to the problem and different things like the below.
Let’s simply discuss each in a summarized way but if you are happy to see complete behaviors and examples click on each link. Performance never ends but you can have a happy ending So let’s move.
1. Create an Index in Oracle (19 Ways)
how to create an index in Oracle and the Types of indexes making considerable performance improvements. Here I found roughly 19 ways to create an index. There are a lot of ways to create and index with Oracle using keywords.
you have to identify the performance issues and figure out which columns need to be added to the index
-- Syntax -- CREATE INDEX index_name ON table_name (column_name); --
- index_name is the name of an index. Good to have some standard way of naming
- table_name is the table name and your column is contained inside this table
- column_name is the name of a column
Oracle Types Of Indexes
- I’m not going to mention all the indexes. Indexes surely improve the performance but have to add it to the most valuable columns or columns. This is some of those but you can find everything and how to create each index on our Create an Index in Oracle (19 Ways) article
- 1. B-Tree Index :
- 2. Bitmap Index:
- 3. Function-Based Index:
- 4. Reverse Key Index:
- 5. Bitmap Join Index
- 6. Domain Index:
- 7. Clustered Index
- 8. Descending Index
- 9. Unique Index:
- 10. Composite Index:
Users need to add the best place according to statistics in the database. But this is a great help to performance tuning in Oracle by adding different types of indexes according to different algorithms. Index should be added according to data load and the behavior of data spread in the database
2. Oracle COALESCE vs NVL Performance
Once comparing the Oracle COALESCE vs NVL performance most of the searches say those are mostly equal.
NVL: evaluates both arguments
--Syntax: NVL(expr, default) SELECT NVL(NULL, 'A') AS result FROM dual; -- Output: 'A'
COALESCE: Looking for the arguments until find the NON-NULL value
The COALESCE function in Oracle looks for the first non-null value from the expression. Once the oracle reaches non null value stop the traverse through the remaining expressions. Therefore, it is a more versatile function because it handles multiple values and also its not evaluate all full expressions.
Syntax: COALESCE (expr1, expr2, ..., expr_n) SELECT COALESCE(NULL, 'A', 'B') AS result FROM dual; -- Output: 'A'
When considering performance tuning in Oracle NVL is a much old function but there are a lot of benefits COALESCE doing the same thing and in addition, there are some extra benefits. If you doing some queries then considering performance there is not much difference with those.
But if you have complex functions as a default value in NVL then that may be a concern in performance. COALESCE is good if you have a lot of possible columns as your expressions. You can have a complete example here for Oracle COALESCE vs NVL Performance including explain plan and sample codes
3. Check running queries in Oracle
If you have a problem with how to check running queries in Oracle? The answer is using System views like V$SESSION, V$SQLTEXT_WITH_NEWLINES, and V$SQL to retrieve information about running queries now. Use active sessions to retrieve the required information.
V$SESSION : The view is a dynamic performance view for providing information about sessions that are connected with the Oracle database. This is available in ACTIVE and INACTIVE sessions that are currently used.
V$SQL: This view includes all the SQL statements currently available in the shared SQL pool. This view contains information like elapsed time, executions, and many other information.
V$SESSION_LONGOPS: this is more on a performance-related view. This contains long-running operations like table creations, table rebuilding, large data lord, and heavy operations. This helps to check the progress of some operations and their related information. This contains some information below
--
SELECT
s.username,
s.sid,
s.serial#,
s.machine,
s.program,
s.status,
s.sql_id,
s.sql_child_number,
s.sql_exec_start,
q.sql_text
FROM
v$session s
JOIN
v$sql q ON s.sql_id = q.sql_id
WHERE
s.status = 'ACTIVE'
AND s.sql_exec_start IS NOT NULL;
--Above is only one query to find running queries that help to improve performance tuning in Oracle database. Sometimes queries have more execution time in the database in this case we can use How To Check Running Queries In Oracle | Performance Tips
With a lot of Sample queries and Oracle views find more details about running queries in the database.
4. DISTINCT vs GROUP BY Performance Oracle
Oracle Both DISTINCT and GROUP BY clauses return unique values from the result set. Performance can be different because of context of usability is different for each.
DISTINCT : The DISTINCT a keyword returns unique values on a single or multiple columns without any aggregate functions.
-- Syntax SELECT DISTINCT column_name FROM table; --
Performance Considerations:
- Objectives: Distinct is well suited to simple queries. Most of the time DISTINCT is used for single columns. DISTINCT can be easily used for such cases. But it’s a bit hard to use in complex queries.
- Performance: Performance is nice on simple cases as we described earlier. In complex cases, GROUP BY will perform well.
GROUP BY: The GROUP BY a clause is used to take unique values BUT with some aggregate values such as COUNT, SUM, AVG, etc.
-- Syntax SELECT column_name, COUNT(*) FROM able GROUP BY column_name; --
Performance Considerations:
- Aggregation: GROUP BY is suited to some complex queries such as taking some values using aggregate functions. This is used to calculate output in each group.
- Multiple Columns: This is flexible compared to DISTINCT.
- Performance: Aggregate function calculates efficiently when using GROUP BY clause. This is performed in complex queries compared to DISTINCT.
DISTINCT vs GROUP BY performance tuning in Oracle is according to the context of usability. If you are happy to see an example with explain plans and business case with simple code looking at How To Change DISTINCT Vs GROUP BY Performance Oracle
5. Oracle Order By Performance (10 Tips)
Oracle order by performance is considered more often when doing performance improvements. There are lots of techniques are help in tuning and sorting queries First, I would like to add when Oracle doing sorting. That will help you when reading the remainder of this article. ORDER BY clause is the only time to sort the rows in a table. Below are the automatically performing sort of the rows inside the process.
- SQL ORDER BY clause
- SQL GROUP BY clause
- CREATE INDEX in Oracle
- When use of UNION, INTERSECTION, or MINUS clauses
- SQL DISTINCT clause
Below are listed common ways of order by performance tuning in Oracle. click each link for examples and sample codes.
- 1. Indexing for Sorting:
- 2. Limit the Result Set
- 3. Use Parallel Processing
- 4. Partitioning
- 5. Memory Allocation
- 6. Materialized Views
- 7. Caching Results:
- 8. Review Execution Plans:
6. Case vs Decode in Oracle Performance
When To Use CASE Vs. DECODE:
- CASE is designed for complex situations that have a lot of conditions and complex functional queries in logic. Also, it also improves the readability and clean code SELECT (e.g., WHERE, HAVING).
-- select case order_state when 'Pending' then '1' when 'Confirmed' then '1' when 'Closed' then '0' when 'Cancel' then '0' when 'partialComplete' then '1' when 'Completed' then '1' when 'New' then '1' end from purchase_order_table; --
DECODE is designed with one-to-one mapping conditions and simple conditions in select statements, but good to ignore complex conditions and multiple conditions. Readability is low with decode.
-- select decode( order_state, 'Pending','1', 'Confirmed','1', 'Closed','0', 'Cancel','0', 'partialComplete','1','Completed','1','New','1' ) --
I would be happy to say they are about the same… at least From a performance wise seems they are about the same, According to my investigations How To Change Case Vs Decode In Oracle Performance
we can say case vs decode in Oracle performance is nearly the same. There can be differences between the location where we need to use them and the intention of these keywords.
7. CONNECT BY PRIOR in Oracle Performance Issue
CONNECT BY PRIOR clause is Oracle provided a great solution for hierarchical queries but connect by prior in Oracle performance issues are headache. It’s because of large dataset misuse of keywords, less focus, or keywords not in the correct order. And there are many causes for connecting prior in Oracle performance.
If the table does not create an index for PRIOR related column, then there can be performance issues. It’s because if there are millions of records in the table every row tries to map with all the rows according to PRIOR condition. Other than that there are a lot of issues and solutions for them to move to CONNECT BY PRIOR In Oracle Performance Issue for all the problems and solution sample codes
Performance Issues With CONNECT BY PRIOR In Oracle
- Full Table Scans Issue:
- Excessive Recursion Issue:
- Apply filtering conditions as early
Tips For Mitigating Performance Issues:
- Indexing:
- Limit the Depth:
- Filter Early:
- Materialized Views:
- Add inner query
The below examples show how to add an inner query if required. This is to filter out the CONNECT BY dataset. Query explicitly reduces the set of data set who work in Sales, which Oracle can then do before performing the CONNECT BY.
-- SELECT * FROM ( SELECT * FROM employees WHERE dept_id = 'SALE' ) START WITH manager_id is null CONNECT BY PRIOR employee_id = manager_id --
CONNECT BY and START WITH are doing related to hierarchical things. Therefore writing queries efficiently is good for performance
8. Oracle Long Running Queries
Oracle databases have great ways of taking Oracle long-running queries. I hope to add some examples of queries below. I would like to use some Oracle views for performance like V$SESSION, v$session_longops, and V$SQL. Oracle System view having such good records for details about long-running queries in the database.
Find long-running SQL Queries
To find out the long-running queries use V$SESSION_LONGOPS oracle view. As we describe above this is contains details about big operations running on a database.
-- SELECT SID,TARGET||OPNAME TARGET, TIME_REMAINING/60 Mins_Remaining,ELAPSED_SECONDS,SQL_ID , SERIAL#,OPNAME, CONTEXT, SOFAR, TOTALWORK,ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE" FROM V$SESSION_LONGOPS WHERE OPNAME NOT LIKE '%aggregate%' AND TOTALWORK != 0 AND SOFAR <> TOTALWORK; --
This contains Oracle long-running queries and if you would interested in how to check running queries in Oracle there are separate ways. This part may help to identify some performance problems. There are more queries to check long-running queries by Oracle Long Running Queries
I thought to add another to performance tuning in Oracle tips but I hope you can easily move to the below 2 posts below that describe examples on each because those are not simple to explain in online
9. Oracle ORDER BY Index
10. SQL Index Usage And How to Optimize Query | Poor Habits
Summary
These are a few performance improvement tips If you can search “Performance” in our search box you can have a lot of ideas on how to performance tuning in Oracle. But I here thought to add valuable tips and use in your cases.
