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.

1 comment:

Smarty said...

i require answer for the below .please let me know if u have any idea on this..the same problem i am facing here..this query i took it from google

I have a view where I am doing a UNION on 2 tables. I have an application that joins that view to another table based on certain fields. I have an index on those fields on all tables involved. However, the indexes are not used when I join to the view.
Ex:
CREATE VIEW MYVIEW AS
SELECT Field1, Field2, Field3
FROM TABLE1
UNION
SELECT Field1, Field2, Field3
FROM TABLE2

And then:
SELECT *
FROM TABLE3, MYVIEW
WHERE TABLE3.Field1 = MYVIEW.FIeld1
AND TABLE3.Field2 = MYVIEW.FIeld2
AND TABLE3.Field3 = MYVIEW.FIeld3
AND TABLE3.RecID = 123

I have seen Oracle optimize this by doing a PUSH_PRED so that the join to the view is pushed down to the tables within the view. This is ideal, but I am not sure how to force the optimizer to do this. I cannot specify query hints in the query, so I have to rely on how the optimizer works behind the scenes to do this. I can run some queries, but the optimizer will only create an optimized plan for the based on certain values specified in the where clause and not a general case. For example it would create an explain plan for TABLE3.RecID = 123 and not TABLE3.RecID = 456.

Does anyone know how to force the optimizer to use PUSH_PRED or tune my query more generically? Again I do not have much control over how the application structures the queries to use Query hints or parameters.