Category Archives: Oracle Tuning

Oracle Tuning notes

Use UNION ALL instead of UNION:

CPU consumption are more when there is any SORT operation, so they are very expensive. The UNOIN operation sorts the result which eliminate the rows within the sub-query or removes the duplicates from the result set. Unless it is required to eliminate duplicate rows from the result set then it is preferred to use UNION […]
Read More »

WHERE clause instead of HAVING clause

In general we should avoid using HAVING clause in SELECT statements because it filters selected rows only after all the records are been fetched where it might include sorting, summing etc… It is only used to filter when we have a SELECT statement which contains group functions on it. For example let us take EMP […]
Read More »

Check Indexes in WHERE conditions

I have taken “HZ_LOCATIONS” table from Oracle ERP to explain where the indexes are been used or not if we have the index on column COUNTRY and STATE. So now we have index called HZ_LOCATIONS_N5 ON HZ_LOCATIONS(COUNTRY, STATE), but we will see where indexes are been used based on the where condition and how we […]
Read More »

Oracle Apps Query Optimization

Oracle Applications and Query Optimization: The cost-base optimization is used in Oracle Application Release 2 version for choosing the most proficient execution plan for the SQL statement. By using this plan the optimizer conclude the most proficient execution plan based on the costing and distributed notification statistics for the schema objects which are accessed by […]
Read More »

Using JOINS in place of EXISTS

It is always preferred to use JOINS in place of EXISTS clause for unique scan indexes and also to small tables. Below example will show the difference for the Unique indexes based on the explain plan, so rather than using sub-queries we can use JOIN conditions for getting more efficient: Less effective query by using […]
Read More »

Using “NOT EXISTS” clause instead of “NOT IN” clause

When we used “NOT IN” clause in a sub-query statements it always causes an internal sort/merge. Which cause or result an expensive query. To avoid this we can use “NOT EXISTS” clause. Below example with explain plan using “NOT EXISTS” and “NOT IN” clause : Below is the explain plan for the above SQL statement: […]
Read More »

How to Tune a Join in Oracle database

There are various factors which can be considered for tuning Join conditions, below are few of them: We should use “=” (equality) as first after where condition. We should only use the range operators (like “Between” )where equality is not applicable. We should avoid negatives like != or NOT as much as possible. If possible then avoid matching patens i.e. […]
Read More »

Quick introduction on Oracle Tuning:

Persons who can tune : DBA Application Designers Application Developers System Administrators Why tuning is really required ? The best way for tuning is all about the careful design of systems and applications. The most of the performance are gained by tuning the application. If any wrong decisions were made in beginning , or if […]
Read More »