Tools for tuning the database

In previous segment, we talked about various aspects of Performance Tuning. There are many tools provided by Oracle to monitor these aspects. These are

1. Oracle Enterprise Manager : This is a web based tool provided by Oracle to manage databases efficiently. OEM helps in all kind of DBA activities including create tables, manage tablespaces etc.. In addition, OEM provides interface for various performance advisors.
2. Oracle Diagnostics Pack : ADDM and AWR are the widely used diagnostic tools apart from many others provided by Oracle in this pack.
3. Oracle Database Tuning Pack : This pack automates the tuning process using SQL Tuning Advisor and SQL Access Advisor.
4. Oracle Real Application Testing : This consists of Database Replay and SQL Performance Analyzer.

Apart from these advanced tools, there are many basic level tools available, such as tkprof. Though tkprof gives you a wide range of information to detect the affected query and bottlenecks, one needs to be well versed with how to read a tkprof report, because the report is text based.

In coming parts we will be knowing about all these tools one by one.

Performance Tuning – Overview

The first and foremost question which comes in mind is what is the need for tuning? This is the world of fast food, fast bikes, fast music, fast cars, in short fast everything, then why not fast database as well? Today everyone is in some kind of a race, and to achieve his/her goal one needs to win that race. To win a race one needs to either run fastest of all or make the horse (or car) run fastest of all. Performance tuning helps applications to run faster.

Is tuning all about running the application faster? No. Time is of course one of the major things we achieve during (and after) tuning, but tuning also means achieving the output in best possible way in least possible time while utilizing optimal resources.

Tuning has many aspects which include (but not restricted to)

1. Database design: This is the first and most important but most ignored part of tuning. Poor database design often results in poor database performance as well.
2. Application tuning: It is a well known fact that the application performance can be improved by 80% just by writing efficient SQLs.
3. Memory tuning: database buffers should be properly sized. As far as possible, use of automated parameters is advised. Pinning large or frequently used objects may also help.
4. Disk I/O tuning: Data files should be properly sized and stored to provide optimum disk throughput. Also, disk sorts, FTS, row chaining, etc need to be checked.
5. Eliminating database contention: Database locks, latches and wait events should be watched carefully.
6. Operating System tuning: OS CPU, I/O and memory usage should also be monitored.

If one takes care of all the above aspects, the good performance of the application will be guaranteed.

In next segment we will check the tools available for tuning purpose.

Performance Tuning

The word itself is vast enough to write a book on it. And many have already been written. Then why am I writing a blog on PT? Answer is simple; for quick check. Books are very tedious for searching something quickly. So I am trying to consolidate things and put it in a place easy to find. You can say it is kind of quick reference when I am in mid of something and do not want to go and search on net or a book. And also easily accessible.

I am not planning on how I am going to write this. I may consolidate multiple topics in one blog or one topic may stretch for 2-3 or more blogs if required.

Let me know if you find anything useful for you, if any topic helps you in your hard time or if you find any topic of no use of yours.