Oracle performance tuning

by jamuna
(hyderabad)

From Jamuna, Hyderbad - what are the performance tuning steps in Oracle?

Oracle performance tuning is a big subject - there have been whole books written about it - so we can't give a comprehensive answer here. However, we can provide a few general recommendations.

The first thing to do is to gather as much information about the problem as possible. Such as whether it is a new problem or a long standing one. Whether it is limited to a certain application (or part of an application) or is wide spread. Whether it happens at specific times of the day/week/month/year or all the time.

Once you have this information you can direct your efforts towards solving the problem.

Tactics that can help in solving the problem are to use explain plan to ensure the Oracle optimizer is using the most suitable execution plan. To assist the optimizer, ensure your database has accurate statistics on the tables and indexes (use the Oracle PL/SQL package DBMS_STATS to gather statistics).

Looking at the execution plans for your SQL statements will tell you whether or not indexes are being used, what sorting is required and so on. This is essential information once you have identified the SQL statement(s) that you believe are causing the problems.

A few other things you can do at the tactical level are to add indexes (beware though that this will improve query access to your Oracle database but will degrade the performance of updates and inserts). You could also consider index-organized tables, materialized views, caching whole tables or the results of queries or PL/SQL functions, partitioning (depending on your license), using PL/SQL instad of SQL or vice versa.

Those are just a few pointers. If you're looking for a good book on the subject we would recommend you consider Oracle Database 10g Performance Tuning Tips & Techniques (Osborne ORACLE Press Series) which gets 4 stars on Amazon and is the current best seller.

Our partners Smartsoft also a whole series of articles on performance tuning on their web site and Oracle themselves offer a performance tuning guide.