Oracle Database Performance Tuning
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.
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:
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.