Thursday, August 21, 2008

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.

Post a Comment