Oracle Performance Tuning and Optimization

With the overwhelming hits from allover the world for my blog on oracle locks, I thought of sharing some performance tuning methods with all.Performance is a parameter, which is critical for any application. Since no users will be willing to compromise on performance of his system, we are subjected to develop a system with less response time and greater throughput.As all distributed systems use database for implementing the business logic, its performance is directly proportional to number of tuned queries and optimized PL/SQL objects in the database.Tuning is not a separate process as such. It should be carried out parallel to the SDLC.

Oracle is one of the widely use database in the world. We will go through some optimization technique that can be implemented to develop  better systems.

Avoid data type mismatch for index columns

Most people, who are novice in database use single quote (in the filter condition) irrespective of the data-type they are querying.This makes oracle to do an internal typecast to the required data-type.

Before Optimization	After Optimization
select name,age,city,state from employee where employee_id='1000';	select name,age,city,state from employee where employee_id=1000;
Time taken : 2.3 sec	Time taken : 0.3 sec

Avoid functions on indexed columns

Normally, we identify the most frequently queried column from table and create an index on it.But while querying we use functions on the indexed column. This will ultimately annul the purpose of creating an index on the column.  

Before Optimization	After Optimization
select name,age,city from employee where substr(employee_name,1,3)='kar';	select name,age,city from employee where employee_name like 'kar%';
Time Taken : 2.8 sec	Time Taken : 0.3 sec

If we cannot avoid using a function in the query. we can create functional index on the specific column

Specify the condition in WHERE instead of HAVING

Before Optimization	After Optimization
select name,          count(1) from employee group by name having name='karthi';	select name,          count(1) from employee where name='karthi' group by name;
Time Taken = 2.2 sec	Time Taken = 0.3 sec

This is an obvious error.If you are not filtering before grouping, then all unnecessary data will be grouped and finally the required data will be filtered. Applying filter before grouping will avoid unnecessary sorting and grouping.

 Use Joins instead of inner query

Before Optimization	After Optimization
select employee_name from employee where employee_id in ( select employee_id from defaulters)	select employee_name from employee e,        defaulters d where e.employee_id=d.employee_id
Time Taken : 14.1 sec	Time Taken : 5.5 sec

 This is actually considered as a bad practice in writing SQL, writing inner query results in querying the outer table for each output of the inner query.I would consider this as potential area where we can improve the system performance.

I feel that, I am overloading every blog with more data.So I will try to present rest of the optimization techniques in next blog.