Oracle Database Performance Tuning

Sumit Kumar

Follow SQL Best Practices Next>> How to read AWR report ?

1. Oracle hints

You should avoid, if possible using Oracle Hints, especially the /*+ RULE */ hint. Almost all the cases Optimizer should be allowed to pick the best plan for particular SQL.
This is because if the data volume increases or changes to a greater extent, the hint which has helped in the past may not help.

2. Large queries

Many a times large queries can be broken down to smaller queries leading to a better performance. Write a pl/sql block to reduce the back and forth database calls. Also, packages reduces I/O since all related functions and procedures are cached together.

3. Tune Sub-quries

If the SQL contains subqueries, optimize them first. If a join will provide you with the functionality of the subquery, try the join method first before trying the subquery method.
Factor Out Sub-queries with Temporary Tables or WITH Clause. Queries like below having subqueries, should be re-written using WITH Clause which is recommended to be the best practice SQL.

SELECT emp_id
FROM employee, department
WHERE emp_id IN (SELECT emp_id FROM emp_small WHERE emp_dept < 'D')
AND dpt_manager IN (SELECT emp_id FROM emp_small WHERE emp_dept < 'D')
AND dpt_id = emp_dept;

Rewrite to use WITH Clause

WITH temp_emp AS (SELECT emp_id empid FROM emp_small WHERE emp_dept < 'D')
SELECT emp_id
FROM employee, department
WHERE emp_id IN (SELECT empid FROM temp_emp)
AND dpt_manager IN (SELECT empid FROM temp_emp)
AND dpt_id = emp_dept;

Although best practice says to use WITH clause, but you should always look for the execution plan to confirm on the best cost of the SQL execution. There will be times when SQL without WITH clause will have better cost and will be faster.

4. Use Aliases

If an alias is not present, the engine must resolve which tables own the specified columns.
A short alias is parsed more quickly than a long table name or alias. If possible, reduce the alias to a single letter.
The following is an example:

Bad Statement
SELECT first_name, last_name, country FROM employee,countries
WHERE country_id = id
AND last_name = ‘HALL’;

Good Statement
SELECT e.first_name, e.last_name, c.country FROM employee e, countries c
WHERE e.country_id = c.id
AND e.last_name = ‘HALL’;

5. Choose the right Driving Tables

You should always place your driving table at the end of the FROM clause. Always choose the table with less number of records as the driving table.
If three tables are being joined, select the intersection tables as the driving table. Here in this below example DEPARTMENT table will be considered as Driving table by RULE based optimizer.
It is interesting to note that, with Cost based optimizer the sequencing of tables in From clause doesn't matter because CBO always considers costs for choosing the right plan for SQL.

SELECT E.EMP_ID,D.DEPT_NAME FROM EMPLOYEE E, DEPARTMENT D WHERE E.DEPT_NO = D.DEPT_NO;

6. Use MINUS instead of EXISTS subqueries

7. Use SQL analytic functions

8. Indexed column

Never do a calculation on an indexed column unless you have a matching function-based index.

9. Avoid the LIKE predicate

Always replace a "like" with "=" when appropriate.

10. Never mix data types

11. Use decode and case

Performing complex aggregation with the decode or case functions can minimize the number of times a table has to be seleced.

12. Remove unnecessary large-table full-table scans

Full table scan causes huge amount of I/O and ca drag down an entire database.
And it's a SQL best practice to identify the index required to avoid full table scan.

13.Use UNION ALL instead of UNION

UNION eliminates all duplicated records from a SELECT list. But UNION ALL returns all records without the elimination process, so it runs faster than UNION.
Please note between these two, you should also consider receiving the right result for your application.