Oracle performance tuning

by jamuna

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

Oracle performance tuning is a very 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. However when using PL/SQL don't fall into the trap of performing an operation one row at a a time when you could perform the same operation in SQL on the whole set of data in one go and much more simply and more efficiently. Also beware of excessive context switching when mixing PL/SQL and SQL.

For more help with performance tuning see the following questions and answers:
You might also be interested in our Oracle SQL tutorials. Also see our Oracle training page for information about formal training courses both on-line and in person.

Click here to post comments

Join in and write your own page! It's easy to do. How? Simply click here to return to Oracle Questions.