4:05 AM
Deep Dey
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 packageDBMS_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.
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 packageDBMS_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.
0 comments:
Post a Comment