Chapter 13: Performance Tuning -Tools and Utilities

---

13.1 Introduction

In the previous chapter we discussed backup and recovery. We discussed why backup was one of the critical parts of any Information Technology implementation. Hardware redundancy provides availability to the computer system by making hardware components available as backup when the main components fail. In the case of data that is stored on the various hardware devices, such as operating system and database files, copies of these devices are required to be maintained at remote locations. When the main device fails, the data could be restored from these remote copies. We discussed various backup methods, hot, cold, and backups using RMAN. RMAN provides a more real-time backup method where backups could be performed while the database is operational without requiring any part of the database to be down or unusable.

We also looked at the various scenarios when a database, instance, node, and memory structures could fail, how during these various failure situations the various recovery methods are applied, and how Oracle performs the recovery.

Backup is important; equally important is the performance of the database system because there would no requirement for a backup system if the current database system is not usable because of its poor perform ance. User satisfaction is critical to the success of the enterprise system. On slowly performing systems, user satisfaction is seldom achievable.

Performance tuning is a very wide subject. There are several approaches to tuning a system. Tuning could be approached artistically like a violinist who tightens the strings to get the required sound, or where the performance engineer could make changes to the system by trying to improve the performance by trial and error. On the other hand, the performance engineer or DBA could take a more scientific approach to tuning, where he or she would collect statistics:

• From areas of the application that are performing slowly.

• During various times of the day when more users are using the system.

• From heavily used functional areas of the application, etc.

Based on the data collected, the database performance engineer could take a more methodical approach to tuning the system. A methodical approach based on data and evidence is a better method of problem solving, more like a criminal investigation officer. Analysis should be backed by evidence. In this case, the collected data would help to understand the reasons for the slowness or poor performance.

Every reason should be backed by data and a scientific approach should be taken to tune the system, because there is a reason why a system is slow. Slow performance could be due to bad configuration, bad code, bad hardware, bad eyes, or bad anything. Unless there is foolproof evidence of why it is slow, no scientific approach to finding the root cause of the problem is possible. The old ancestral methodology that ''tuning a computer system is an art'' is not completely true, because tuning is not a hit-or-miss situation; it is to be approached in a scientific manner with supporting data.

Performance tuning will be covered in this and the next two chapters. In this chapter, we will look at the various tools for Oracle database tuning, its implementation and configuration and in the subsequent two chapters we will go into the specifics of tuning the database and overall tuning of a RAC system.

---