chapter 1

Chapter 1: The Craft of Performance Tuning

Database administrators perform a wide variety of tasks—some exciting, and others more routine. On the down side, not too many DBAs get really excited about setting up backups, monitoring disk space, or checking to see if the exports ran okay last night. For most DBAs, these tasks are not especially challenging, nor do they call for much creativity.

In stark contrast to these more mundane tasks, performance tuning is actually a very fascinating part of database work; however, many DBAs do not realize how rewarding it can be. This is unfortunate, because performance tuning is one of the DBA’s greatest challenges, with both tangible and intangible rewards. A truly competent performance-tuning specialist at times looks like a magician performing the impossible, or helping a client through some emergency.

How many jobs provide the opportunity to be an instant “hero” who saves the day by fixing critical systems for a large corporation? Yet that is exactly what a skilled performance expert can accomplish.

Let me share an example that illustrates the dynamics of performance tuning.

The Otrace performance problem was quickly solved, not because I was so smart, or knew some clever “tricks,” but because I was aware of some fundamental problems with the database, and recognized symptoms that matched a particular problem. In fact, that particular problem was

- 1 - 4/18/2023

Case Study # 1: Winning the Interview, Losing the Job

Years ago, I went to an interview at a firm that creates public safety programs for police departments. The firm’s IT manager wanted to see how I would approach some difficult database issues, so he demonstrated some performance problems the firm was experiencing with its Oracle 7.3 databases. Specifically, he remarked that the time to establish a connection with Sql*Plus seemed to take a long time.

As I listened to the manager, I recalled that the symptoms he described perfectly matched a well-documented “feature” with an Oracle utility called “Otrace.” With Otrace active, connections to Sql*plus will take longer and longer over a period of months. This happens because Otrace scans a special data file, which grows larger and larger with each new connection.

The amazing thing about this problem was that I had just solved an identical Otrace problem the week beforehand! Not believing my good fortune, I asked the manager if this problem were real, or was this some type of “joke” to test me. Of course, it was not a joke at all, and the manager was very serious about solving the problem. After I double-checked the documentation on Otrace, I showed the manager how to disable it. He followed my recommendation, deactivating Otrace, and one of the firm’s biggest performance problems was solved during the interview! Needless to say, I felt pretty good about my showing in the interview.


well documented with an Oracle “Alert.” Anyone who had read the alert and had a good understanding of database operations would have also found the root cause rapidly.

Besides showing how exciting performance tuning can be, this real-life example also illustrates the importance of a thorough understanding of basic principles of database operation. Without a sound grasp of the fundamentals, the DBA must resort to guessing, or inappropriate measures such as trying to eke-out the highest possible “hit ratio,” or setting some init.ora parameters to ridiculously high values.

The importance of understanding database fundamentals is the subject of our first “Magician’s Trick:”

Throughout the book, we will be providing Magician’s Tricks that emphasize critical points in performance tuning—points that are frequently overlooked or misunderstood. Some of these axioms might seem obvious at first, but many of these “common sense” tips are not so commonly practiced.

Magician’s Trick #1 might appear to be obvious, but experience indicates the opposite. Perhaps it is just ignorance, but many analysts seem to dread the hard work necessary to truly understand how an Oracle database operates. Instead, these misguided analysts continually look for the “silver bullet” that will magically solve the tough performance problem.

There is no Silver Bullet

It is very common for administrators or managers to try to solve a thorny performance problem without attempting any real understanding of database principles! This scenario often manifests itself with some type of magic “tool” that will supposedly analyze the database and crank-out the solution. Trick #1 suggests that trying to use some sort of “tool” to solve performance problems will likely be futile, if the analyst lacks a solid grounding in how the database really works.

It’s not that tools are designed badly; in fact, in the hands of a competent analyst, a tool can be very helpful. The point, rather, is that no tool can correct for a lack of understanding of the user. In other words, the wrong problem is being addressed. Instead of focusing on achieving true understanding that will lead to the best solution, the focus is shifted to a vain search for ways to avoid the labor that is required to achieve true understanding.

- 2 - 4/18/2023

Nothing can substitute for a solid understanding of database principles.

Magician’s Trick #1


In our Otrace example, the firm with the performance problem had experienced the degradation for months. They were certainly not stupid people, but they simply did not have anyone who truly understood how an Oracle database worked. Imagine if that firm tried to avoid hiring a competent DBA, but insisted on trying to solve the performance problems via a “silver bullet” of some performance tool. This would have only led to frustration and embarrassment.

In solving the Otrace problem, note that I did not waste time fooling around with the init.ora parameters—such as doubling the database buffers, increasing shared pool size, or trying more parallel processes. Why not? Because the symptoms of the problem indicated that these steps would have been completely irrelevant to the problem at hand. Note that solving the problem involved no guesswork. Rather, a solution followed directly from listening to the exact symptoms.

In future chapters we will discuss the performance tuning process in detail. For now, just remember that the first step for the DBA was to listen to the user as he described the problem.

Admittedly, it is a bit unusual to solve performance problems during the initial interview1; but this scenario illustrates another important theme—that is, the importance of the approach that the DBA uses to solve performance problems. This is the subject of another “Magician’s Trick:”

In other words, the successful performance analyst is no smarter than other DBAs; he has just learned an overall process that has proven successful.

The “Big” Questions

Since you are reading this book, I assume you are interested in becoming an accomplished performance-tuning analyst. You know that there will be new things to learn and obstacles to overcome. In particular, you probably would like to know the answer to these questions:

“Can I achieve remarkable performance tuning results?” And

“Is performance tuning really that complex?”

1 Since my experience with solving the Otrace problem during the interview, I give other DBAs the chance to do the same. When I interview prospective DBAs, I sometimes include a perplexing performance problem that I have been unable to solve. So far, no candidate has ever provided a quick solution, but I keep trying.

- 3 - 4/18/2023

The key to successful performance tuning is not technical sophistry, but rather, a sound approach based on good understanding of basic principles.



Fortunately, the answers are:

“Yes, you too can do these things!” and

“No, it isn’t really that hard. Let me explain why.”

Let me divulge a big “secret.” With a sound approach to performance tuning, many performance bottlenecks are actually very simple to correct. As we have seen with the Otrace problem, some performance tuning problems are incredibly simple to solve, using the right approach.

Solutions to the vast majority of performance problems do not require consultation with Oracle Corporation on how to change some “undocumented” init.ora parameter that begins with a funny character. Just like the Otrace example at the start of this chapter, it is not unusual for a good performance-tuning expert to make just a few changes that lead to markedly improved performance.

This path to tuning expertise is not based on clever equations, or “silver bullets,” but an understanding of what steps must be performed to succeed in the performance tuning process. From experience, I know that few performance problems are technically difficult to solve. With the proper approach and understanding, many performance problems are very simple to correct.

The true way to become a performance-tuning expert is not through technical sophistication, but rather a good understanding of the overall approach. In future chapters we will address questions such as,

How do I approach this problem?

Where do I start?

Is the information I have correct?”

As we will see, truly challenging problems sometimes mean separating the real from the illusion, or even just correctly defining which problem should be solved. Plus, as always, there are many aspects of human interaction that should be considered.

The More Oracle Changes . . .

Faced with a bewildering assortment of performance problems, many DBAs are unsure of themselves when it comes to performance tuning. Without a proven approach, the whole process can be intimidating. After all, how can anyone keep up with all the new features? And what about all those cryptic “Sql hints”?

The apparent obstacle posed by the enormous breadth of Oracle options is exploited by the marketers of some Sql performance tuning tools. In their marketing literature, they emphasize

- 4 - 4/18/2023


the impossibility of the DBA fully understanding the many forms of Sql. Following this line of reasoning, the DBA must surely buy a tool (their tool) that can rapidly formulate and test the myriads of different Sql combinations.

The huge flaw in this line of reasoning is the assumption that effective performance tuning requires that the DBA understand all the features available in the most recent database version. In truth, the DBA who has a good grasp of database principles can easily account for any new “horns and whistles” in the latest database release. As the first two Magician’s Tricks explained, sound understanding is the best long-term strategy to win in the game of performance tuning.

Consider the game of chess. In a chess match, does a good player actually consider every possible move? Of course not. It is only the poorest of players (or the crudest of computer programs) that use that strategy. In reality, the master chess player knows the principles of the game, and only considers the options that show some promise, or are relevant.

The performance DBA can use the same reasoning. He doesn’t need to try every option, because he knows that 99% of irrelevant. Experience shows that nearly all performance problems can be solved by applying sound principles of database operation. In other words, in spite of the bewildering set of new options that Oracle Corporation dazzles us with, there are some things that stay the same, even in a world of rapid database releases:

To illustrate Trick #3, consider how the Oracle optimizer joins two tables. The way of performing table joins has stayed nearly constant, even as database “horns and whistles” have been added. This means that, regardless of database version, the performance specialist must understand the difference between a nested loop and sort/merge join. Armed with this understanding, a large number of Sql performance problems can be solved, regardless of the exact database version.

Of course, as technology advances, there are some changes that must be noted. In the case of table joins, for instance, the DBA should be aware of the “hash joins” method that is now often used. This addition is an easy adjustment for a DBA who already was well acquainted with the other two methods. The DBA who already possesses a good understanding of performance tuning principles will have little trouble assimilating additional features such as these.

Art or Science?

- 5 - 4/18/2023

The principles of sound performance tuning do not change from version to version.



Some specialists have argued that performance tuning is purely a science—simply the application of sound mathematics to a given problem. This notion is very appealing, because it suggests a nice, clean, deterministic way to solve any problem. Attractive as this idea is, does it really match reality?

In reality, performance tuning is rarely 100% pure science. Rarely are performance problems handed to the DBA on an index card. In practice, performance problems take place in complex, even “whacky” organizations, filled with misinformation and human egos.

Even when the root cause of a performance problem can be determined through a heuristic, the solution often calls for creative thinking. For instance, creative thinking (and tact) is often needed when the root cause of a performance difficulty is traced to the application. The designers may resist calls for changing their code, or argue that certain changes are too expensive, or simply not feasible. The professional performance expert cannot walk away at this point, but has an obligation to work with the designers to synthesize an acceptable solution. At this point, the DBA is more of an artist than a scientist.

Thus, solving serious performance problems is not merely a mechanistic process, such as solving an equation in a school textbook. There are frequently other points to consider, such as determining what problem to solve, wading through misinformation, or dealing with the human factor. Of course, good analytic skills are necessary, but so are good creative skills. To ignore either aspect is to misunderstand how technical problems are solved in practice.

In the next chapter, we will go “ backstage” to explore the steps followed by performance tuning experts. We will see that the ideal performance analyst is not just a scientist, but a man or woman who wears “many hats,” depending on the occasion.

- 6 - 4/18/2023

chapter 1

Documents