Introduction

---

Let's begin by taking a general look at what we need to examine in order to tune Oracle installations.

A Tuning Environment

What is a tuning environment? A tuning environment is an environment in which your tuning efforts can be productive.

What is Required when Tuning Oracle Database?

• Good software tools.

• Skilled personnel.

• Staging (testing) environments.

• A realistic duplication of the production environment.

  • Actual and expected production environments. These can often be different if growth is rapid or requirements change.

  • If possible databases should be of the same size and content. If this is impractical then at least development and testing databases should be proportionate to production.

  • Are the statistics the same? Statistics can be copied or executed using the same time intervals as production.

What Tools are Available?

Excellent software tools for tuning and monitoring Oracle databases are numerous. Oracle Enterprise Manager has many very useful bells and whistles. Spotlight is excellent for visual and informative real- time monitoring of busy production systems. Both Oracle Enterprise Manager and Spotlight are very useful as tuning aids for both physical and SQL code performance analysis.

There are many other tools available. The most important tools in the tuning process are the developers and the administrators. That is why you are reading this book. The best software tools are usually the most expensive but that does not mean that the less expensive tools are useless. In general, the more expensive tools tend to do more for you. However, when something is being done for you automatically and you do not understand the internals, it is unlikely that your tools set can do better than well-trained, experienced database administrators and developers.

Skilled Personnel

Good skills have their correct places. Database administrators tend to have roots as either systems administrators or developers. Each skills set has its pros and cons. Developers tend to know a lot more about coding SQL and building data models. Systems administrators have extensive knowledge of operating systems such as Unix and tend to concentrate tuning efforts on the physical aspects of Oracle Database. Developers tend to concentrate on tuning the data model and building efficiently performing SQL code. Unfortunately this is not always the case because there is sometimes a tendency for developers to place the burden of tuning SQL code and the data model into the area of responsibility of the database administrators. Confusion can be the result and perhaps nothing gets done.

Staging (Testing) Environments

You need as many testing and staging environments as you can get. As the resident DBA you should not be expected to perform database tuning on an active production database which is required to be up and usable 24 × 7 × 365. Tuning on a production database in this situation will limit your scope and could cost you your job! Insist on at least one extra machine and always test anything you do, no matter how trivial. This is the most important difference between production and development. Developers do everything quickly because they have to. Production database administrators are expected to get their tasks done just as fast but additionally everything must be perfect all the time. So make sure you insist on extra hardware and extra time.

Duplicating Production Databases for Effective Tuning

It is absolutely essential to have the most recent and most realistic copy of a production database for tuning purposes. Tuning on a production database is risky and using a development database for tuning can be completely useless. It is extremely rare that development and production databases are alike. Testing databases can be useful when development and production databases cannot be made the same.

Statistics are also important. In a production environment such as an online transactional database the data in that production database could be changing constantly. Even if your database is not changing too much statistics could change or rapidly become out-of-date. The more dynamic the data is the more often statistics should be updated. The SQL code Optimizer utilizes statistics to compile the most efficient methods of executing SQL statements. Statistics are measurements of the data itself, such as how large a table is and how useful an index is. When an SQL statement accesses a table both the table and index states are important. States of database objects such as tables and indexes are contained within statistics. If statistics are out-of-date the Optimizer is not functioning realistically. Out-of-date statistics would have the same effect on all types of databases. It is very important to duplicate statistics from production to tuning environments, either by copying or by executing statistics gathering on a tuning database, consistent with the production database.

Making a copy of a production database to a development database is not an issue when the production database is small. When the production database is large continuous copying to a development database could be very time consuming. Be aware that using the database import utility for even single schema imports on even a small database can take a much longer time than the production database export.

Tip 

The DBMS_STATS package can be used to copy statistics between databases.

---