Page 727
Page 728
We stated in Chapter 3, "Physical Database Design, Hardware, and Related Issues," that physical (and logical) designs are the first steps in tuning a database. This is indeed true. One of the first steps in many tuning books is to tune the design. Well, following proper logical and physical design, you have done just that. If all has gone well, your design should allow your database to perform at more than an acceptable level to begin with. However, there are two things that always creep up with database systems: growth and changing application requirements.
If your database is relatively static, is made up mostly of lookup tables, or for any other reason experiences very little or very slow growth, this factor is not a problem. If, on the other hand, your database is like most others, it will grow over time by some substantial percentage, such as 10 percent or more. Then, despite initially good logical and physical designs, you might periodically need to tune the database to accommodate or otherwise compensate for this growth. In addition, all database systems have fallen prey to the phenomenon of applications, which are in perpetual redefinition. Here is an example you might have come across:
Manager: "The application doesn't work. It's running too slowly. What's wrong with the database?"
Programmer: "The code hasn't changed."
DBA: "The database hasn't changed."
What could be the problem, then? In this instance, the usage has probably changed. This is really only another fancy way of saying that the application requirements have changed. For example, suppose the application originally was intended to allow read-only access to seven tables individually. However, if the application is now being used to join four more tables with some or all of these seven tables to gather more information than originally requested, some tuning might be necessary. In fact, some physical redesign, also known as physical restructuring, might be required.
You can restructure a database physically to improve performance and still permit the application to function as desired. This is possible with Oracle, as with other vendors, because Oracle obeys Codd's rule number 8: physical data independence. So the answer might have been found by consulting with the user:
User: "The application doesn't work? Oh yeah. We're using some different tables now."
There is one other thing, aside from growth and changing application requirements, that will require a DBA's performance tuning skills: the poorly designed database.
With growth and changing application requirements, we had assumed the database was well-designed. Unfortunately, as many DBAs can attest, this is often not the case. Many times a DBA takes over a database system that another DBA has left, is called in to fix the performance of someone else's database system, or is confronted with a database system that is the result of a legacy migration. In cases such as these, what is needed before performance tuning is a logical and physical analysis and redesign. It might be that the database doesn't need much redesign, or it might be that it needs a complete overhaul.
Page 729
In either case, once that design has been ironed out, the DBA can move on to performance tuning proper. However, after the redesign, it might be best to simply put the application to use and do some performance monitoring first. After all, with proper design (or redesign) comes initially good performance. It might very well be that no new additional tuning is required for some time to come. (In practice, I have personally found this to be the case.)
Why tune? We have just answered this question. The current database system is not performing acceptably, based on user-defined criteria, for one of the following reasons:
When might database tuning efforts not be fully effective? When components that are external to the database, yet vital to the entire client/server application performance, fail to perform acceptably, database tuning might not help without the corresponding tuning of these other application infrastructure pieces. Except for the most isolated, stand-alone, batch production database applications, most modern database systems are client/server based.
The main components external to the back-end database are the back-end operating system (or OS), the network, and the client OS. The major examples are:
Clients (PCs) are weak in the sense that they have older CPUs (486 or less), relatively little memory (<= 16MB), and/or little storage (<= 1GB). This especially holds true if the application is "heavyweighted toward the client" and thus requires a fat client. A fat client is one in which a large portion of the application processing is segmented onto the client. A thin client is obviously the opposite of this, with most of the processing taking place at the server end. So, unless an application requires only dumb terminal access or thin clients, weak clients will not suffice.
A network is considered to be saturated by varying definitions. To be saturated means that a system component has reached maximum possible throughput. A 10 Mbps ethernet is considered saturated when it reaches 30 percent of its bandwidth. Networking specialists might handle this in different ways, including resegmenting the network locally, adding more network segments, or changing to a broader-band network media type, such as a 100 Mbps Fiber Distributed Data Interface (FDDI). In any case, we have gone well off the beaten path of database tuning.
An OS might be very weak, just like a client. It is only weak relative to the applications it must support. But in general, modern database servers that have 32-bit processors, 4 processors, 256MB memory, and 10GB of disk space might be considered weak. An OS can be saturated in