What is Oracle Performance Tuning?

by navee
(Hyderabad)

Please explain about Oracle Performance Tuning

Oracle Performance Tuning is a very big subject (whole books have been written on it) so we can't possibly cover it all here. What we can do though is provide you with a few pointers.

Essentially there are two areas of your system that might need tuning - your applications and your database. You might think that it's better to start with tuning the database to ensure that all the applications benefit but this is not the case. Usually it is far better to start with a part of the systen that is having performance problems and drill down from there into the application and eventually into teh database if you need to.

The sort of things that might need tuning in your application are your SQL statements and your PL/SQL programs and you might be tuning either memory use or execution time. Things to consider would include:

1. use of indexes versus full table scans
2. using PL/SQL instead of SQL or vice versa
3. the use of views
4. the use of correlated sub queries versus non-correlated sub queries.

There is already quite extensive coverage of these issuse on our site and other sites so we won't repeat them here, just point you in the right direction.

Good places to start are our SQL tutorials which specifically talk about tuning SQL queries against your databases and our Oracle pl/sql tutorials which cover best practices and pl/sql performance tuning and this guide to Oracle performance tuning. Oracle also provide a 2 day + perfomance tuning guide.